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.