A DBA's Approach to Setup Transactional Replication - 4 NonStop Days at Office

Recently, we need to perform a HotFix promotion on our Production environment. In HotFix promotion, the vendor who has developed the application has done some major enhancements to fix the bugs on the Application as well as on the database level. As a pre-requisite, before the HotFix promotion starts, we need to break the LogShipping as well as Transactional Replication. The database on which the HotFix was going to be applied was around 90 GB in size. Setting up the LogShipping again was not a difficult ask but setting up the Transactional Replication again did put us in a deep trouble. In this article, Satnam Singh a Senior SQL Server DBA from Larsen&Toubro Infotech,Mumbai,India discusses in depth the method which he adopted after doing a consecutive research for 4 Consecutive NonStop working days which finally made him successful. 

SQL Server Versions

Publisher: OLTP DB Server, SQL Server 2005, Service Pack 3,32 GB of RAM,8 CPU’s

Subscriber: Reporting DB Server, SQL Server 2005, Service Pack3, 16 GB of RAM, 12 CPU’s

Both the above mentioned servers were located in the same DataCenter in the United States of America. Just FYI, the subscriber is the reporting server which receives data from the OLTP server every 15 minutes via Transactional Replication. Also while setting up the Transactional Replication, it was decided that the distribution database will be hosted on the OLTP Server itself. 

One important point to mention here is that on the Subscriber i.e. the Reporting Server we have created a lot of indexes for better performance of the Select statements. Before I started with the HotFix promotion, I ensured that I have the backup of all the indexes. In order to take the backup of the indexes, I executed the below query against the database named ABC (database which was involved in Replication) and resided on the subscriber (Reporting DB Server).Before starting the HotFix promotion, I decided to take the backup of all the SQL Server Indexes present in the existing database on the Reporting Server. In order to do so, the below T-SQL was executed against the database named ABC on the subscriber i.e Reporting Server.

//
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 
AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISCLUSTERED') = 0 

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 --ixz.name, colz.colid, colz.name, ixcolz.index_id, ixcolz.object_id, *
--key column
@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

--remove any trailing commas from the cursor results
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)
--put the columns collection into our temp table
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 --WHILE
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 was executed, I got the Index Generation script of all the Indexes which I saved onto a particular location on the server itself. Please note that on the Reporting Server we have allocated a seperate drive if RAID 10 and size 200 GB just to hold the SQL Server Indexes itself for better performance.Also note that the Index generation script will not script the Clustered Index present in the tables, this is because we will be moving only the Non Clustered Indexes in the database.If we try to move the Clustered Indexes the table associated with that Index will also get moved because the leaf of the Clustered Index is the data page itself.

After the HotFix got promoted, I started reverting back with the changes. I decided to first setup the Transactional Replication because that was important as most of the Business users heavily relied upon the Reports. Since the database OLTP database size was around 90 GB, I decided to go with "Replication without a Snapshot".

On the OLTP Server, I first created the publisher using GUI.Once the publisher was created, I directed the system that it needs to Initialise the backup from the backup set. 

I then took the Full Backup of the database on the OLTP Server using below T-SQL.   

//
backup database ABC to disk='E:\Microsoft SQL Server\MSSQL\Backup\User\ABCt_Full_Backup_After_HotFix.bak' 
//
The IP Address of the OLTP Server was 10.1.1.1
Once the Full Backup got completed, I started restoring the same on the Reporting Server using the below T-SQL.
//
RESTORE DATABASE ABC
FROM DISK='\\10.1.1.1\User\ABC_Full_Backup_After_HotFix.bak'
WITH MOVE 'ABC' TO 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC.mdf',
MOVE 'ABC_Log' TO 'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC_1.ldf',
stats=10,NORECOVERY 
//

Full Backup was Restored using NORECOVERY mode so that additional backups could be applied over it.

By the time the Full backup was getting Restored onto the Reporting Server, I started taking the Differential Backup of the same on the OLTP Server itself using the below T-SQL.

// 
backup database ABC to disk='E:\Microsoft SQL Server\MSSQL\
   Backup\User\ABC_Differential_Backup_After_HotFix.bak' with differential 
// 

Once the Full Backup got Restored, I started Restoring the Differential Backup onto the database named ABC on the Reporting Server using the below T-SQL.

// 
RESTORE DATABASE ABC
FROM DISK='\\10.1.1.1\User\ABC_Differential_Backup_After_HotFix.bak'
WITH MOVE 'ABC' TO 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC.mdf',
MOVE 'ABC_Log' TO 'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC_1.ldf',
stats=10,NORECOVERY
GO
//

By the time the Differential Backup got restored I started taking the Transactional Log Backup of the database named ABC on the OLTP Server itself using the below T-SQL.

// 
backup log ABC to disk='E:\Microsoft SQL Server\MSSQL\Backup\User\ABC_Transactional_Backup_After_HotFix.bak'  
//

I then Restored the Transactional Log Backup onto the database named ABC on the Reporting Server using the below T-SQL.

// 
RESTORE DATABASE ABC FROM DISK='\\10.1.1.1\User\ABC_Transactional_Backup_After_HotFix.bak'
WITH MOVE 'ABC' TO 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC.mdf',
MOVE 'ABC_Log' TO 'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC_1.ldf',
stats=10
GO 
//

Once the database named ABC on the subscriber was brought ONLINE, i then decided to create the subscriber on the OLTP Server using the below T-SQL.

//  
exec sp_addsubscription 
@publication = N'ABC',
@subscriber = N'KUS1111',
@destination_db = N'ABC',
@subscription_type = N'Push',
@sync_type = N'initialize with backup',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0,
@backupdevicetype ='disk',
@backupdevicename
='\\10.1.1.1\User\ABC_Transactional_Backup_After_HotFix.bak' 
//

Once the above query was executed, I received the below error message:

The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor.

At this stage, I realised that instead of Restoring the Differential and Transactional Log backup over the Full Backup on the Reporting Server, if I create the publisher and then just restore the Full Backup on the Reporting Server, the Log Reader Agent itself would take care of all the DML changes which had happened on the OLTP Server itself. I then took the decision of creating the Publisher on the OLTP Server, instructing it to initialise the backup from the Backup set, Restore the Full Backup on the Reporting Server and then create the subscriber.

Once the above logic was implemented, I was successfully able to create the subscriber but when I viewed the synchronisation status, the system threwed the below error message:

"Violation of Primary Key constraint in the table named abc"

Now at this stage, I was pretty much confused with the unusual behaviour of the technology. Hours passed by but even after doing all the troubleshooting, I couldn't find the culprit.After spending around 12-16 hours investigating the case, I decided to check the T-SQL involved for the triggers in the database and I was very surprised to see the code. The T-SQL for triggers didn't had "Not For Replication" clause at the top of it. I thought that this might be the case with few of the triggers but I was very surprised that none of the triggers in the entire database had "Not For Replication" clause at the top of its code. Due to the presence of triggers duplicate entires were generated for a particular table, one entry was generated by the Log Reader Agent whereas the other entry was generated by the Trigger and hence there was a Violation of Primary Key constraint.

I then took the decision that against the database named ABC on the Reporting Server, all the triggers shall be disabled for the replication process to work properly.

I then changed the strategy for Setting up the Transactional Replication between the OLTP and Reporting Database Servers which is as follows:

• Create the publisher on the OLTP server and directed it to initialize the backup from the backup set.
• On the OLTP Server, take the Full Backup of the database named ABC.
• On the Reporting Server, Restore the Full Backup.
• Generate a script to disable all the triggers in the database named ABC on the Reporting Server using the below T-SQL.

//
Select Distinct 'DISABLE TRIGGER ALL ON [' +object_name(parent_obj) + ']' + char(10) + 'GO'
from sysobjects where parent_obj in (select objidfrom sysarticles) and xtype in ('TR', 'TA')
//

• The output of the above query was executed against the database named ABC on the reporting server and all the triggers got disabled.
• Created the subscription by executing the below T-SQL query against the database named ABC on the OLTP Server by using the below T-SQL query.

exec sp_addsubscription
@publication = N'ABC',
@subscriber = N'Name of the Reporting Server',
@destination_db = N'ABC',
@subscription_type = N'Push',
@sync_type = N'initialize with backup',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0,
@backupdevicetype ='disk',
@backupdevicename = Path and name of the Full Backup taken on the OLTP Server’

This was one such approach which I used to set up Transactional Replication between the OLTP and the Reporting environment. My sincere thanks to all the viewers for providing their valuable time in reading the article. If you have any suggestions then please do let us know.