SQL Server: 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 t
ables
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.

<pre class="brush: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</pre>

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.

<pre class="brush:sql">backup database ABC to disk='E:\Microsoft SQL
Server\MSSQL\Backup\User\ABC_Differential_Backup_After_HotFix.bak' with
differential</pre>
<span>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.</span><strong><span> </span></strong>

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'initializewith 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.

<em><strong>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')</strong></em>

• 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,

<em><strong>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’</strong></em>

Once the above query was executed, Data started flowing properly between the OLTP and the Reporting Server.

This was one such approach which I used to set up Transactional Replication between the OLTP and the Reporting environment, Indeed it was a very unique case which i have faced till date. 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.

Regards

Satnam Singh

Like us on FaceBook  |  Follow us on Twitter

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