Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2010-09-20   |   Comments   |   Related Tips: 1 | 2 | 3 | 4 | More > 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:

Define Additional Scripts (SQL Server Management Studio)

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".

Replication Snapshot SQL Server Agent Job on the Subscriber


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

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


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''', 
@[email protected]_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.


Last Updated: 2010-09-20


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools