Advantages of Replication
Users can avail the following advantages by using a replication process:
- Users working in different geographic locations can work with their local copy of data, thus allowing greater autonomy.
- Database replication can also supplement your disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, your applications can switch to the replicated copy of the data and continue operations.
- You can automatically back up a database by keeping a replica on a different computer. Unlike traditional backup methods that prevent users from getting access to a database during backup, replication allows you to continue making changes online.
- You can replicate a database on additional network servers and reassign users to balance the loads across those servers. You can also give users who need constant access to a database their own replica, thereby reducing the total network traffic.
- Database-replication logs the selected database transactions to a set of internal replication-management tables, which can then be synchronized to the source database. Database replication is different from file replication, which essentially copies files.
Replication performance tuning tips
- By distributing partitions of data to different subscribers.
- When running SQL Server replication on a dedicated server, consider setting the minimum memory amount for SQL Server to use from the default value of 0 to a value closer to what SQL Server normally uses.
- Don’t publish more data than you need. Try to use row filter and column filter options wherever possible as explained above.
- Avoid creating triggers on tables that contain subscribed data.
- Applications that are updated frequently are not good candidates for database replication.
- For best performance, avoid replicating columns in your publications that include
TEXT, NTEXT, or IMAGE data types.
