SQL Server Transactional Replication – Deep Dive

As a Database Administrator, we all are normally involved in Setting up SQL Server Transactional Replication on our environment.Apart from setting up Transactional Replication, performing a proper maintainence of the same is also very important. In this exclusive article, Satnam Singh, a Senior SQL Server DBA from Larsen&Toubro Infotech,Mumbai,India discusses one such Real World scenario which he used on his Production Servers to ensure that the performance of the database on the Reporting Server which was receiving data via Transactional Replication from the OLTP Server is excellent even after the Replication is re-configured.

Scenario:

Every year, we perform an activity named HotFix promotion wherein the vendor who has developed the Application performs some enhancement on the database. In order to perform this HotFix promotion, the Pre-Requisite is to break the Transactional Replication between the OLTP and the Reporting Server and once everything is promoted well, the same needs to be re-configured again. On the Reporting Server, we have build plenty of Indexes for better performance of the SSRS reports. Just FYI, the OLTP and the Reporting Server were in the same Data Centre in the United States of America.On the Reporting Server, we have kept SQL Server Indexes On a Secondary Data File kept on a Seperate Dedicated Drive of 200 GB, RAID 10 for better performance. The size of the Production OLTP Database is around 91 GB whereas the size of the Reporting Database is more than that i.e. around 130 GB, this is due to indexes contributing to an increase in size.

Since the Database Size is huge, therefore I would never recommend using the GUI for setting up the Transactional Replication, instead I would go for Replication without a Snapshot method wherein I would use the Database Backups to configure the Replication.Once the Transactional Replication is configured using the OLTP Database backup, I would loose all the indexes which were created manually against the Reporting Database for better performance. In order to ensure that I have all the indexes and statistics on the Reporting Server exactly the same as before the HotFix promotion, I performed the following steps:

• A day before the HotFix promotion started, I took the backup of all the SQL Server Indexes. In order to take the backup of all the SQL Server Indexes, I used the below T-SQL script

SELECT
ixz.object_id,
tablename = QUOTENAME(scmz.name) + '.' + QUOTENAME((OBJECT_NAME(ixz.object_id))),
tableid = ixz.object_id,
indexid = ixz.index_id,
indexname = ixz.name,
isunique = INDEXPROPERTY (ixz.object_id,ixz.name,'isunique'),
isclustered = INDEXPROPERTY (ixz.object_id,ixz.name,'isclustered'),
indexfillfactor = INDEXPROPERTY (ixz.object_id,ixz.name,'indexfillfactor')
INTO #tmp_indexes
FROM sys.indexes ixz
INNER JOIN sys.objects obz
ON ixz.object_id = obz.object_id 
INNER JOIN sys.schemas scmz
ON obz.schema_id = scmz.schema_id 
WHERE ixz.index_id > 0 
AND ixz.index_id < 255 ---- 0 = HEAP index, 255 = TEXT columns index
--AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISUNIQUE') = 0 -- comment out to include unique and
AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISCLUSTERED') = 0 -- comment out to include PK's
	 
ALTER TABLE #tmp_indexes ADD keycolumns VARCHAR(4000), includes VARCHAR(4000)
GO
 
DECLARE @isql_key VARCHAR(4000), 
@isql_incl VARCHAR(4000),
@tableid INT, 
@indexid INT
 
DECLARE index_cursor CURSOR
FOR
SELECT
tableid, 
indexid 
FROM #tmp_indexes 
 
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @tableid, @indexid
	 
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @isql_key = '', @isql_incl = ''
SELECT 
@isql_key = CASE ixcolz.is_included_column 
WHEN 0 
THEN CASE ixcolz.is_descending_key 
WHEN 1 
THEN @isql_key + COALESCE(colz.name,'') + ' DESC, '
ELSE @isql_key + COALESCE(colz.name,'') + ' ASC, '
END
ELSE @isql_key 
END,
	 
@isql_incl = CASE ixcolz.is_included_column 
WHEN 1 
THEN CASE ixcolz.is_descending_key 
WHEN 1 
THEN @isql_incl + COALESCE(colz.name,'') + ', ' 
ELSE @isql_incl + COALESCE(colz.name,'') + ', ' 
END
ELSE @isql_incl 
END
FROM sysindexes ixz
INNER JOIN sys.index_columns AS ixcolz 
ON (ixcolz.column_id > 0 
AND ( ixcolz.key_ordinal > 0 
OR ixcolz.partition_ordinal = 0 
OR ixcolz.is_included_column != 0)
) 
AND ( ixcolz.index_id=CAST(ixz.indid AS INT) 
AND ixcolz.object_id=ixz.id
)
INNER JOIN sys.columns AS colz 
ON colz.object_id = ixcolz.object_id 
AND colz.column_id = ixcolz.column_id
WHERE ixz.indid > 0 AND ixz.indid < 255
AND (ixz.status & 64) = 0
AND ixz.id = @tableid 
AND ixz.indid = @indexid
ORDER BY 
ixz.name, 
CASE ixcolz.is_included_column 
WHEN 1 
THEN ixcolz.index_column_id 
ELSE ixcolz.key_ordinal 
END
	 
IF LEN(@isql_key) > 1 SET @isql_key = LEFT(@isql_key, LEN(@isql_key) -1)
IF LEN(@isql_incl) > 1 SET @isql_incl = LEFT(@isql_incl, LEN(@isql_incl) -1)
	 
UPDATE #tmp_indexes 
SET keycolumns = @isql_key,
includes = @isql_incl
WHERE tableid = @tableid 
AND indexid = @indexid
FETCH NEXT FROM index_cursor INTO @tableid,@indexid
END 
	 
CLOSE index_cursor
DEALLOCATE index_cursor
 
 
DELETE FROM #tmp_indexes WHERE keycolumns = ''
 
SET NOCOUNT ON
SELECT 
'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + ti.TABLENAME + '''' + ') AND name = N' + '''' + ti.INDEXNAME + '''' + ')'  ' ' + 
'CREATE ' 
+ CASE WHEN ti.ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END 
+ CASE WHEN ti.ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END 
+ 'INDEX ' + QUOTENAME(ti.INDEXNAME) 
+ ' ON ' + (ti.TABLENAME) + ' '
+ '(' + ti.keycolumns + ')' 
+ CASE 
WHEN ti.INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 1 AND INCLUDES = '' 
THEN /*ti.Filter_Definition +*/ ' WITH (SORT_IN_TEMPDB = OFF) ON [SECONDARY]'
WHEN INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES = '' 
THEN /*ti.Filter_Definition +*/ ' WITH (ONLINE = OFF, SORT_IN_TEMPDB = OFF) ON [SECONDARY]' 
WHEN INDEXFILLFACTOR <> 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES = '' 
THEN /*ti.Filter_Definition +*/ ' WITH (ONLINE = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR = ' + CONVERT(VARCHAR(10),ti.INDEXFILLFACTOR) + ') ON [SECONDARY]'
WHEN INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES <> '' 
THEN ' INCLUDE ('+ti.INCLUDES+') WITH (ONLINE = OFF, SORT_IN_TEMPDB = OFF) ON [SECONDARY]'
ELSE ' INCLUDE('+ti.INCLUDES+') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),ti.INDEXFILLFACTOR) + ', ONLINE = OFF, SORT_IN_TEMPDB = OFF) ON [SECONDARY]'
END
FROM #tmp_indexes ti
JOIN sys.indexes i ON ti.Object_id = i.object_id and ti.indexname = i.name
JOIN sys.filegroups fg on i.data_space_id = fg.data_space_id
WHERE LEFT(ti.tablename,3) NOT IN ('sys', 'dt_') --exclude system tables
ORDER BY
ti.tablename, 
ti.indexid, 
ti.indexname 
 
DROP TABLE #tmp_indexes

Once the above T-SQL is executed, we will get a T-SQL to create all the indexes in the output. Also on the Reporting Server, since the Clustered Index resides on the Primary Data File itself therefore the above T-SQL has been written in such a manner that only the Non Clustered Indexes will be scripted out.

• Once the indexes has been scripted out,the next step is to generate a script of all the statistics present in the database on the Reporting Server. In order to take the backup of all the SQL Server Statistics, I use the below T-SQL.

SET NOCOUNT ON
 
DECLARE @columnname VARCHAR(MAX) 
DECLARE @tablename SYSNAME 
DECLARE @statsname SYSNAME 
DECLARE @sql NVARCHAR(4000)
DECLARE @NAME VARCHAR(MAX) 
 
declare @i INT
declare @j INT
 

create table #temp
(
tablename varchar(1000),
statsname varchar(1000),
columnname varchar(1000)
)
 
insert #temp(tablename,statsname,columnname)
SELECT DISTINCT
OBJECT_NAME(s.[object_id]),
s.name AS StatName,
COALESCE(@NAME+ ', ', '')+c.name
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1)
 
create table #temp1
(
id int identity(1,1),
tablename varchar(8000),
statsname varchar(8000),
columnname varchar(8000)
)
 
insert #temp1(tablename,statsname,columnname)
select tablename,statsname,stuff(
(
    select ','+ [columnname] from #temp where
statsname = t.statsname for XML path('')
),1,1,'') 
from (select distinct tablename,statsname from #temp )t
 
	 
SELECT @i=1
SELECT @j=MAX(ID) FROM #temp1
	 
WHILE(@I<=@J)
 
BEGIN
 
SELECT @statsname = statsname from #temp1 where id = @i
SELECT @tablename = tablename from #temp1 where id = @i
SELECT @columnname = columnname from #temp1 where id = @i
 
 
SET @sql = N'CREATE STATISTICS '+QUOTENAME(@statsname)+space(1)+'ON'+space(1)+QUOTENAME(@tablename)+'('+QUOTENAME(@columnname) +')'
--  --EXEC sp_executesql @sql   
PRINT @sql 
SET @i = @i+1
 
END
 
 
DROP TABLE #temp
DROP TABLE #temp1

Once the above T-SQL is executed, you will get the script of all the SQL Server statistics present in the database.

• Set up the Transactional Replication between the OLTP and Reporting Servers using the concept of Replication Without a Snapshot. I have already discussed the same concept in one of my articles published a couple of days back on the below link;

• On the Reporting SQL Server, please create a Secondary FILEGROUP which will host the SQL Server Indexes.

• Drop all the SQL Server Non Clustered Indexes on the Reporting Server using the below T-SQL:

<pre class="brush:sql">DECLARE @ownername SYSNAME 
DECLARE @tablename SYSNAME 
DECLARE @indexname SYSNAME 
DECLARE @sql NVARCHAR(4000) 
DECLARE dropindexes CURSOR FOR
 
SELECT ixz.name, objects.name, schemas.name
FROM sys.indexes ixz
JOIN sys.objects ON ixz.OBJECT_ID = objects.OBJECT_ID 
JOIN sys.schemas ON objects.schema_id = schemas.schema_id 
WHERE ixz.index_id > 0 
  AND ixz.index_id < 255 
  AND objects.is_ms_shipped = 0 
  AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = ixz.name) 
AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISCLUSTERED') = 0
ORDER BY objects.OBJECT_ID, ixz.index_id DESC
 
OPEN dropindexes 
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername 
WHILE @@fetch_status = 0 
BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname) 
  PRINT @sql 
  --EXEC sp_executesql @sql   
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername 
END
CLOSE dropindexes 
DEALLOCATE dropindexes 
</pre>

• Run the T-SQL script for Index Creation on the Reporting Server which we have discussed in Point No 1 above.Please note that once the Index Creation script is executed, all the indexes will reside on a Secondary FileGroup hosted on a Seperate Drive of RAID 10 as discussed in the beginning of the article.

• Run the T-SQL script for Statistics Creation which we have just used above.

• Execute the job to perform the Re-Indexing and Update Statistics of the database. The scripts for the same is as follows:

Script to Re-index all the tables of the Database:

DECLARE
@dbname varchar(1000),
@parentname varchar(255),
@SQLSTR VARCHAR (1000),
@ctrl CHAR (2),
@command varchar(1000)
SET @ctrl = CHAR (13) + CHAR (10)
DECLARE DBCUR CURSOR FOR
select [name]
from sysdatabases where name not in
(
'master',
'model',
'tempdb'
)
order by 1
OPEN DBCUR
FETCH NEXT FROM DBCUR INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
select @command =
'
use ['+@dbname+']
Exec sp_MSForEachtable ''DBCC DBREINDEX ("?")''
'
exec (@command)
FETCH NEXT FROM DBCUR INTO @dbname
END
CLOSE DBCUR
DEALLOCATE DBCUR
GO

Script to perform the Update Statistics of all the tables in the Database:

DECLARE
@dbname varchar(1000),
@parentname varchar(255),
@SQLSTR VARCHAR (1000),
@ctrl CHAR (2),
@command varchar(1000)
SET @ctrl = CHAR (13) + CHAR (10)
DECLARE DBCUR CURSOR FOR
select [name]
from sysdatabases where name not in
(
'master',
'model',
'tempdb'
)
order by 1
OPEN DBCUR
FETCH NEXT FROM DBCUR INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
select @command =
'
use ['+@dbname+']
Exec sp_MSForEachtable ''update statistics ? with fullscan''
'
exec (@command)
FETCH NEXT FROM DBCUR INTO @dbname
END
CLOSE DBCUR
DEALLOCATE DBCUR
GO

• Once Re-indexing and Update Statistics is completed,please check the performance of the queries involved in the SSRS Reports.There might be few cases wherein a query might perform poor during the First Iteration but the performance of the same improves drastically when executed twice or thrice.

This was one such approach which I used in my Infrastructure to ensure that even though at any Point of Time, I am required to re-configure the Transactional Replication between the OLTP and the Reporting Server, it’s performance remains the same to the state as it was before.

Many Thanks to all the readers for providing their valuable time in reading the blog post. My Sincere Thanks to my Team Members at L&T Infotech with whom I have spend considerable amount of time in developing this solution.If you have any valuable suggestions which can help us to improve this article, then please do let us know.

Regards

Satnam Singh

Like us on FaceBook  |  Follow us on Twitter

Follow me on FaceBook| Join the fastest growing SQL Server group on FaceBook