question

tdwhite avatar image
tdwhite asked

Best Practice for SQL Server Replication

I'm reviewing replication on a SQL Server instance that was set up by another dba and have been asked to make recommendations for improvements (if any). Just wondering if anyone has any advice about the things to look out for that are no no's so should be avoided. Any best practice tips for replication?
replicationsql serverdbabest practices
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The answer will very much depend on the purpose of the replication. Can you post back with some details about why it's been set up. Also how many subscribers etc.
0 Likes 0 ·

1 Answer

·
Cyborg avatar image
Cyborg answered
What kind of replication type you running, is it merge, transactional, snapshot or peer to peer? What is your current setup? as @GPO asked how many subscribers? How often do you need to synchronize the publisher and subscribers? Following are general recommendations :- - Use pull subscription if you are have lot of subscribers, so agents will run at subscriber. - Isolate distribution database from publisher (if your publisher is overloaded) - Configure alerts for replication agents, like failures, performance, stopped, completed etc. - Monitor replication performance regularly. - Don't share snapshot folder with any database or log files. - Plan for replication availability if your Publisher or distributor goes down. - Monitor Identity Range allotment (Merge replication). - Plan for Backups on master, distribution, msdb, publisher and subscriber databases. - Save scripts for publisher, subscribers. - Monitor Conflict (Merge, peer-peer). - Use alternate snapshot folder while applying initial or reinitializing subscribers for better performance. -
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Many thanks for your response! It's mainly Merge replication with 1 snapshot replication. There are 12 subscribers. The synchronization needs to happen all throughout the day so publications run at intervals between 20min - 1hr.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.