Resilient Replication

Introduction

Resilient Replication improves the resiliency of the transactional replication process employed by Anthology Student for scaling out SQL Server reporting workloads to a separate SQL Server Instance. These improvements to the replication process should negate the need to break and rebuild replication for upgrades to Anthology Student, FAA, and Regulatory.

Preinstallation Steps

Note: Try to execute the preinstallation steps during non-peak hours. Execute the script against the Production OLTP and reporting databases at a minimum of 48 hours prior to the actual upgrade to release 17.0 and above.

If the current Anthology Student database version is 15.x.x or 16.0.x and Replication has been configured, perform the following steps for a resilient upgrade without breaking the replication:

  1. In the Microsoft SQL Server Management Studio window, connect to the Publisher server. In the Object Explorer, click the (+) sign to expand the Replication folder, then click the (+) sign to expand the Local Publications folder.

    1. Right-click the Publication(s), then click Properties.

    2. On the Subscription Options page of the Publication Properties dialog box, set the Replicate schema changes option to True.

    3. Click OK to close the window.

  2. Execute the following preinstallation script on the Publisher database and the Subscriber database:

    ..\17.1 - Pre-Installation Files\TFS155940-00-PreUpgradeScript-CreateResilientReplicationObjects.sql

  3. Execute the script below on the Publisher database. Specify the value of the @publication parameter with the name of the publication to add the new table ‘SyReplTablesAltered’ as an article. If there are multiple publications with the same Subscriber dbs, then add the article to any one of the publications.


    PRINT 'Adding SyReplTablesAltered table as Article'

EXEC sp_addarticle @publication = 'Name Of Publication'
	,@article = N'SyReplTablesAltered'
	,@source_owner = N'dbo'
	,@source_object = N'SyReplTablesAltered'
	,@type = N'logbased'
	,@description = N''
	,@creation_script = NULL
	,@pre_creation_cmd = N'drop'
	,@schema_option = 0x00000000084359DF
	,@identityrangemanagementoption = N'manual'
	,@destination_table = N'SyReplTablesAltered'
	,@destination_owner = N'dbo'
	,@vertical_partition = N'false'
	,@ins_cmd = N'CALL sp_MSins_dboSyReplTablesAltered'
	,@del_cmd = N'CALL sp_MSdel_dboSyReplTablesAltered'
	,@upd_cmd = N'SCALL sp_MSupd_dboSyReplTablesAltered'
GO

exec sp_refreshsubscriptions @publication = N' Name Of Publication'
GO
--  Test the repl cmd
INSERT INTO dbo.SyReplTablesAltered (tablename, ColumnAltered)
SELECT 'Test', 'Test'

  4. Run the following statement in the Subscriber database to confirm the table is replicating.

    SELECT * FROM dbo.SyReplTablesAltered WHERE TableName = 'Test'

  5. Execution of the above script should be a non- empty result set.

  6. Once the ‘SyReplTablesAltered’ table is replicated, the below mentioned objects (created using the preinstallation script) exist in the Publisher and Subscriber database, the CampusVue database can be upgraded without breaking the Replication.

    1. Tables:

      (1) dbo.SyReplTablesAltered (article)

      (2) dbo.SyIndexRecreatePostUpgradeWork

    2. Stored Procedures:

      (1) dbo.cmc_PrepTableForAlter

      (2) dbo.cmc_PrepTableColumnForAlter

    3. Trigger:

      (1) dbo.SyReplTablesAltered_Insert_trg ON dbo.SyReplTablesAltered

Preinstallation Steps – 16.1

If the current Anthology Student database version is 16.1.x and Replication has been configured, perform the following additional step before the upgrade:

Execute the following preinstallation script on the Subscriber database.


IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'Nk_SyNexusOrganization_Code' and object_id = object_id('SyNexusOrganization'))
BEGIN
    DROP INDEX [Nk_SyNexusOrganization_Code] ON [dbo].[SyNexusOrganization]
END
      
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_SyNexusOrganizationSyCampusGrpId_SyCampusGrpSyCampusGrpId' and parent_object_id = object_id('SyNexusOrganization'))
BEGIN
    ALTER TABLE dbo.SyNexusOrganization 
        DROP CONSTRAINT FK_SyNexusOrganizationSyCampusGrpId_SyCampusGrpSyCampusGrpId
END   



GO
Exec [dbo].[cmc_DropConstraintsAndTriggersOnSubscriber]
GO
IF EXISTS (SELECT 1 FROM sys.columns WHERE is_identity = 1
AND COLUMNPROPERTY(OBJECT_ID('dbo.SyIndexRecreatePostUpgradeWork'), 'SyIndexRecreatePostUpgradeWorkID', 'IsIdNotForRepl') = 1
)
BEGIN
       ALTER TABLE dbo.SyIndexRecreatePostUpgradeWork ALTER COLUMN SyIndexRecreatePostUpgradeWorkID DROP NOT FOR REPLICATION
END
GO

Preinstallation Steps – Regulatory 8.x and 9.x

If the current Regulatory version applied to the Anthology Student database is lower than 8.x and Replication has been configured, the following error could occur while upgrading the Regulatory version:

  • Cannot truncate table 'dbo.FaShoppingSheetConsumer' because it is published for replication or enabled for Change Data Capture. Script fragment that caused an error: TFS0324456-00-FaShoppingSheetConsumer_Insert_2016_17_ShoppingSheetData.sql

  • Cannot truncate table 'dbo.FaCODDisbAdjQueue' because it is published for replication or enabled for Change Data Capture.Script fragment that caused an error: TFS0325072-FaCODDisbAdjQueue_Truncate.sql

Perform the following steps before upgrading:

  1. In the Microsoft SQL Server Management Studio window, connect to the SQL Server instance where the Anthology Student database exists.

  2. Click the (+) sign to expand the Replication folder, and expand the Local Publications folder.

  3. In the list of publications, right-click the publication belonging to the Anthology Student database being upgraded, and click Properties.

    Local Publications > Properties

  4. On the Articles page of the Publication Properties dialog box, locate the following two tables and clear the check marks to remove them from the article list.

    1. FaCODDisbAdjQueue (dbo)
    2. FaShoppingSheetConsumer (dbo)

    Publication Properties

    Note: If multiple publications exist for the same Anthology Student database that is being upgraded, find the publication that contains the above mentioned tables as articles and clear the check marks to remove them from replication.

  5. Now upgrade the Anthology Student database with a Regulatory version greater than 8.x.

  6. Once upgrade is complete and the post installation/upgrade steps are complete, repeat the above steps 1 to 3 and check the “FaShoppingSheetConsumer (dbo)” table to include it back to the replication.