Wednesday, March 26, 2014

How to perform SSRS 2005 to SSRS 2008 R2 upgrade



How to perform SSRS 2005 to SSRS 2008 R2 upgrade

1. Generate full backup for ReportServer Databases in 2005 Instance
2. Backup the SSRS Encryption Key using 2005 Reporting Services Configuration Manager
3. Backup ReportServer .config files from 2005 instance (If there are any customized configurations)
4. Export ReportServer.dbo.Keys table information into a plain text file from 2005 instance.


Now on new 2008 R2 SSRS instance

1. Install SSRS Engine and configure the SSRS using Report Service Configuration Manager
2. Connect to Reporting Services using SSMS to verify everything seems good
3. Stop SSRS Engine and Restore the 2005 ReportServer database on 2008 R2 instance then start it again.
4. Copy the SSRS encryption backup key file to 2008 R2 machine and restore using Reporting Services Configuration Manager
5. If you query the ReportServer.dbo.keys table then you will find machine names of old server name along with new server name, you need to delete the old servers record
6. Restart the Reporting services and verify the ReportManager / Reports URL to view the reports

Thursday, March 13, 2014

Moving Tables and Index between filegroups in sql server

Moving Tables & Indexes between Filegroups

In my previous posts I discussed about Filegroups, we will see very brief summary of filegroup again in this post.

Filegroup - A filegroup is a logical structure to group objects in the database. We can create multiple filegroups in the database, the default filegroup is PRIMARY for any database. We can create filegroups for two main reasons 1) Performance and 2) Recovery
Performance - For suppose if a filegroup exists on D:\drive has very large table that is getting frequently accessed and you are receiving high IO, in that case we can move the large table to new drive where it has high IO handling capacity for better performance.

Recovery – Filegroups can be backed up and recovery individually, this can enable faster recovery in case of disaster. In disaster we can recover the primary filegroup first and make database accessible and then we can recover archived data filegroups which is not frequently used by users.

Sometimes we may need to move objects between filegroups to achieve performance or moving data to another drives or to archive the unused tables into different drive in a separate filegroup.
Select * from sys.filegroups -- Shows how many files groups are associated to the database

If there are more than one filegroup exists then we need to identify default database by seeing Is_default = 1 value.

Below query helps to identify the object name and associated file group to it.

SELECT 'Object Name' = OBJECT_NAME(IDX.object_id), 'Index Name' = IDX.name,
 'FG Name' = FG.name FROM sys.indexes IDX INNER JOIN sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

Now we will add new filegroup to database
Alter database KalyanDB add FileGroup SecondaryFG
Select * from sys.filegroups

ALTER DATABASE KalyanDB  ADD FILE (NAME = 'Secondary_File',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\secondary.ndf')  TO FILEGROUP SECONDARYFG

Below query displays file group and associated physical file to it
select fg.name as filegroup, (case when fg.is_read_only =0 then 'Write Mode' else 'Read Mode' end),
f.name, f.physical_name from sys.filegroups fg, sys.database_files f
where f.data_space_id = fg.data_space_id

We will create two tables, One in default file group and other in secondary file group

CREATE TABLE Table1_PrimaryFG( [ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL,  [ProductNumber] [nvarchar](25) NOT NULL ) ON PRIMARY

CREATE TABLE Table2_SecondaryFG([ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL, [ProductNumber] [nvarchar](25) NOT NULL       
) ON SECONDARYFG
Run the below query to identify the table1 and table2 in which file group

SELECT 'Object Name' = OBJECT_NAME(IDX.object_id), 'Index Name' = IDX.name,
'FG Name' = FG.name FROM sys.indexes IDX
INNER JOIN sys.filegroups FG ON IDX.data_space_id = FG.data_space_id
where OBJECT_NAME(idx.object_id) in ('Table2_SecondaryFG','Table1_PrimaryFG')
To achieve moving objects while creating clustered index on the table we can specify the filegroup so the table will automatically moved to the other filegroup.
Below create commands will move table to SecondaryFG file group

Create clustered index idx_table1 on Table1_PrimaryFG(ProductID) on SecondaryFG
Create clustered index idx_table2 on Table2_SecondaryFG(ProductID)

Below syntax is used to move table when already index is exists on the table.


CREATE CLUSTERED INDEX [idx_table1] ON [dbo].[Table1_PrimaryFG] ([ProductID] ASC )WITH (DROP_EXISTING = ON, ONLINE = ON) ON [PRIMARY] 

CREATE UNIQUE INDEX / NONCLUSTERED INDEX ux_id ON [dbo].Table3_PrimaryFG ( id ) WITH (DROP_EXISTING = ON) ON [SecondaryFG]

 Another query to identify objects with file group information.

  SELECT object_name(i.[object_id]) as Name_of_Object, i.name as Index_Name,
  i.type_desc as Index_Type, f.name as Name_of_Filegroup, a.type as Object_Type,
  f.type, f.type_desc FROM sys.filegroups as f  INNER JOIN sys.indexes as i 
  ON f.data_space_id = i.data_space_id INNER JOIN sys.all_objects as a  
  ON i.object_id =     a.object_id WHERE a.type ='U'
   -- User defined tables only
   AND object_name(i.[object_id]) in ('Table2_SecondaryFG','Table1_PrimaryFG')

 

Wednesday, March 12, 2014

Get-Help examples in Powershell

get-help - This displays information and examples about the powershell cmdlets

PS C:\> get-help             -- Displays help about get-help

TOPIC
    Windows PowerShell Help System


PS C:\> get-help get-help -Online        -- Displays online versin of get-help command, it displays help in browser

PS C:\> \get-help get-process               -- Displays help about the get-process cmdlet

PS C:\> \get-help get-process -full       -- Full switch helps to display complete information along with switches, descirptions and examples.


PS C:\> \get-help get-process -examples -- Displays only examples of the get-process cmdlet

PS C:\> get-help get*                               -- Displays all cmdlets starts with get

PS C:\> get-help about_split | more         -- Displays information about the split command, usage and examples

TOPIC
    about_Split
SHORT DESCRIPTION
    Explains how to use the Split operator to split one or more strings into
    substrings.

PS C:\> get-help dir -examples                -- Displays examples of the dir commnad

 

How to identify Powershell Version

Below are the various ways to identify the powershell version

Start - Run - Powershell

PS > get-host

<output>
Name             : ConsoleHost
Version          : 3.0
'
'
PS > $psversiontable

Name                           Value
----                           -----
WSManStackVersion              3.0

PS C:\> $host.version
Major  Minor  Build  Revision
-----  -----  -----  --------
3      0      -1     -1

PS C:\> $psversiontable.psversion
Major  Minor  Build  Revision
-----  -----  -----  --------
3      0      -1     -1

PS C:\> host

Name             : ConsoleHost
Version          : 3.0
PS C:\> get-host | select-object Version
Version
-------
3.0

Tuesday, March 11, 2014

How to delete duplicate records in SQL Server


To delete any duplicate records in SQL Server

create table t1 (col1 int, col2 int)

insert into t1 values ( 1, 70)
insert into t1 values ( 1, 70)
insert into t1 values ( 1, 71)
insert into t1 values ( 1, 72)
insert into t1 values ( 1, 72)
insert into t1 values ( 1, 73)
insert into t1 values ( 1, 74)
insert into t1 values ( 1, 75)

select * from t1

with t1dups (col1, coldups)
AS (
select col2, ROW_NUMBER() Over (Partition by col1, col2 order by col2) as dups from t1 )
delete from t1dups where coldups > 1

select * from t1