Placing the snapshot folder on a separate drive from any database or log files reduces contention among the disks and helps the snapshot process complete faster. When you create the subscription database at the Subscriber, consider specifying a recovery model of simple or bulk-logged.
This allows minimal logging of the bulk inserts performed during the application of the snapshot at the Subscriber. After the snapshot has been applied to the subscription database, you can change to a different recovery model if necessary replicated databases can use any of the recovery models.
Consider using the alternate snapshot folder and compressed snapshots on removable media for low-bandwidth networks. Compressing snapshot files in the alternate snapshot folder can reduce snapshot disk storage requirements and make it easier to transfer snapshot files on removable media. Compressed snapshots can, in some cases, improve the performance of transferring snapshot files across the network. However, compressing the snapshot requires additional processing by the Snapshot Agent when generating the snapshot files, and by the Distribution Agent or Merge Agent when applying the snapshot files.
This may slow down snapshot generation and increase the time it takes to apply a snapshot in some cases. Additionally, compressed snapshots cannot be resumed if a network failure occurs; therefore they are not suitable for unreliable networks. Consider these tradeoffs carefully when using compressed snapshots across a network. For more information, see Modify snapshot options. In some scenarios, such as those involving large initial datasets, it is preferable to initialize a subscription using a method other than a snapshot.
Reduce the verbose levels of replication agents except during initial testing, monitoring, or debugging. This reduces the number of new rows inserted to track agent history and output. Instead, previous history messages with the same status are updated to the new history information.
Increase the verbose levels for testing, monitoring, and debugging so that you have as much information about agent activity as possible. This parameter specifies the number of bulk copy operations that can be performed in parallel when the snapshot is created and applied. Agent parameters can be specified in agent profiles and on the command line.
For more information, see:. Work with Replication Agent Profiles. Replication Agent Executables Concepts. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services. Privacy policy.
Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. Consider adding memory to servers used in replication, particularly the Distributor. Use multiprocessor computers. Use a fast network. Database Design Follow best practices for database design.
Be cautious with application logic in triggers. Business logic in user-defined triggers at the Subscriber can slow down the replication of changes to the Subscriber: For transactional replication, it can be more efficient to include this logic in custom stored procedures used to apply the replicated commands.
Publication Design Publish only the data required. Minimize conflicts through publication design and application behavior. You can split articles or tables. Check the Microsoft SQL replication tuning article for more ideas. I am probably forgetting a few.
Just remember, that SQL replication has lots of customizations available. It is not. Do not use SQL replication for the real-time source of data. Be default, nothing in replication is encrypted! But you can enable SSL communication. Have we missed any?
What improvements did you see when you changed those parameters? Mark Varnas. I love making performance tuning SQL Servers fast and making them more stable. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.
Leave a Reply Cancel reply Your email address will not be published. Call Us Now. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Learn more. What are the performance implications for using SQL Server replication? Ask Question. Asked 12 years, 5 months ago. Active 12 years, 5 months ago. Viewed 9k times. What are adverse affects to using replication What are some examples of when replication is beneficial.
Improve this question. Add a comment. Active Oldest Votes. To add some more about transactional replication: it uses a SQL Agent log reader job to harvest committed transactions from the transaction log of the publication database. This means that the log cannot be cleared until the log records have been read. If the log reader agent periodicity is changed then your log may grow unexpectedly.
The log reader agent could also cause contention on the transaction log on high-volume OLTP systems, depending on your IO subsystem of course. For zero data-loss, look at synchronous database mirroring or synchronous SAN replication peer-to-peer replication is a good way of scaling out a query workload, and can also add some redundancy to your data you have to do some careful schema design with peer-to-peer to avoid collisions caused by similar changes at different nodes.
Don't use partitioned identities for that. Use a composite surrogate key e. The publisher can be mirrored, the subscriber can be mirrored reasonably easily in , not so easily in , but the distributor cannot. It must be clustered to add redundancy. In you don't need to. This means that in a 3-way topology with, say, Seattle, London, Tokyo - if Seattle goes down, London and Tokyo keep going. Pretty neat.
0コメント