15 SQL Server replication skills in 15 minutes

15 SQL Server Replication Tips in 15 Minutes

Translated from:

15 SQL Server replication tips in 15 minutes

This list of tips and tricks will help you enhance your replication techniques. It is divided into three sections: Performance, Monitoring and Others.

Remember, transactional replication provides the best performance of all replication types. Most of the time, clients use snapshot or merge replication and transactional replication is the best choice. For example, if the data is not modified frequently, if there is a lot of data modification, or if the client does not need a real-time updated dataset, you can use snapshot replication.

In all other cases, transactional replication is only considered to be a modification transmitted over the network to the client. Snapshots send the entire dataset (most likely GB) at a time, while transactions are sent in MB. If you copy at low bandwidth, the difference will be very noticeable.

Replication Tips: Performance

1. Replicate tables without primary keys

2. Achieve bi-directional replication

3. Replicate stored procedure execution

4. Create lightweight tables on the subscriber

5. Put subscription database in Bulk Logged recovery model

6. Use the independent agent option

7. Change your polling interval

8. Maintain your log

9. Set performance profiles

Replication Technique: Monitoring

1. Check subscriber status with Replication Monitor Group

2. Expire subscribers

Copy Technique: Other

1. Replicate to objects with different schemas

2. Reveal replication commands

3. Check number of commands to be replicated

4. Prevent updates, inserts and deletes from being replicated