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?
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. -
16 People are following this question.
