Maintain Custom Indexes on Replication Subscriber Tables via Pre and Post Scripts

By: Kun Lee   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Replication

Problem

We are using replicated Subscriber databases for reporting and other purposes in our environment. As such, we have differing index needs based on the user activity on the publisher tables vs. the subscriber tables. Unfortunately, whenever we reinitialize replication, we have missing indexes on the tables. Since the index needs are different between the publisher and subscriber, I do not also want to replicate all indexes from the publisher using the schema options that I wrote previously. How can I address these issues? Check out this tip for a detailed explanation.

Solution

SQL Server Replication has a feature at the snapshot level called "Run additional scripts" which can be setup before and/or after the snapshot is refreshed. These options provide an opportunity to address our differing index needs between the publisher and subscriber. In these files, I use a little different approach to maintain indexes by calling a SQL Server Agent Job which calls one or more store procedures rather than hard coding all of the logic in the single pre and post files. Let's walk through an example to see this approach in action.

Define Additional Scripts (SQL Server Management Studio)

In SQL Server Management Studio, navigate to root, "Replication" folder, "Local Publications" folder and then navigate to the Publication. Right click the Publication and select "Properties" then choose the "Snapshot" option in the left pane. You will see an interface similar to the one below:

For the pre and/or post files, you will need to create a file on any file server that can be accessed by the SQL Server. I usually keep the file in the same location as snapshot folder. I typically create a "Scripts" folder as a child folder to the snapshot folder. In this example, I created a file in the "Scripts" folder called "MSSQLTips_Publication_PostScript.sql" because "MSSQLTips_Publication" is my publication name.

Post Script File (MSSQLTips_Publication_PostScript.sql)

Inside of the "MSSQLTips_Publication_PostScript.sql" file, it only contains the T-SQL below only to execute a SQL Server Agent Job by the name of "Replication Snapshot MSSQLTipsDB - Post". This SQL Server Agent Job name is based on "MSSQLTipsDB" being my database name and this script being the post script.

EXECUTE msdb.dbo.sp_start_job N'Replication Snapshot MSSQLTipsDB - Post'

Replication Snapshot SQL Server Agent Job on the Subscriber

The post restore script references a SQL Server Agent Job on the subscriber SQL Server and the job will call the "prdPOSTREPLCreateIndexes" stored procedure with this logic: "exec prdPOSTREPLCreateIndexes".

Store Procedure on the Subscriber

The stored procedure will contain a script like the one shown below to manage the needed indexes.

CREATE PROC [dbo].[prdPOSTREPLCreateIndexes]
BEGIN
IF NOT EXISTS (
 SELECT * 
 FROM sys.indexes 
 WHERE [object_id] = OBJECT_ID(N'[dbo].[SQLTips]')
 AND name = N'ndxSQLTipsCode'
 )
 CREATE INDEX ndxSQLTipsCode 
 ON SQLTips(SQLTIpsCode) Include (SQLTipsID)
END

Why are you doing this the complicated way?

The purpose of the pre and post scripts calling a SQL Server Agent Job rather than hard coding the logic in the individual file is to give our Developers more flexibility. In our environment, we can let the Developers decide which indexes are needed on the Subscriber and they can update the post script index stored procedure our Source Safe version control system. Then when we deploy the code to production, the updated stored procedure will be deployed and we do not need to make any replication changes because the pre and\or post file as well as the SQL Server Agent Job are static.

Overview - General Process

Define Additional Scripts - Optional

If you want to manage the Pre and/or post scripts via T-SQL, as I do, the highlighted portion of the code is where the configurations would be established.

use [MSSQLTipsDB]exec sp_addpublication @publication = N'MSSQLTipsDB', 
@description = N'Transactional publication of database ''MSSQLTipsDB''', 
@s@sync_method = N'concurrent', @retention = 0, @allow_push = N'true', 
@allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', 
@snapshot_in_defaultfolder = N'true', 
-- Begin Here is Post Script --
@post_snapshot_script = 
N'\\MyFileServer\ReplData\Scripts\MMSQLTips_Publication_PostScript.sql', 
-- End Here is Post Script --
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', 
@allow_subscription_copy = N'false', @add_to_active_directory = N'false', 
@repl_freq = N'continuous', @status = N'active', 
@independent_agent = N'true', @immediate_sync = N'false', 
@allow_sync_tran = N'false', @autogen_sync_procs = N'false', 
@allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, 
@allow_initialize_from_backup = N'false', 
@enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

Next Steps

• As you have a need to manage differing indexes in a replicated environment, refer back to this tip for ideas on streamlining the process.
• Be sure to maintain the replication scripts in your Source Control system such as TFS, SourceSafe, Vault, etc. to make sure the correct code is deployed.
• Check out all of the replication tips.

About the author

Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips