Configuring SQL Server Replication for use across WANs
As discussed in my last blog post, DBAs have several choices on how to replicate SQL Server data across a WAN. How you choose to go about it and what technology tools you choose will make or break the process.
NitroAccelerator can enable real-time native SQL Server replication with extremely low latencies over slow WAN connections. However, to do so the basic configuration of the different SQL Server replication components must be correct.
In other words, all the moving parts of SQL Server replication need to be in the correct locations for the data to flow as quickly as possible. These moving parts include:
- Distributor – Where the data is staged for delivery to the subscribers.
- Publisher – The database to be replicated.
- Subscriber – The destination for replicated data.
- Snapshot Agent – Its role varies based on the type of replication. It’s used to initialize the subscribers with the schema and data from the replicated tables.
- Distribution Agent – Moves transactions from the distribution database to the subscriber. The data movement can either be pulled or pushed to the subscriber depending on where the agent runs.
- Log Reader Agent – Used only in transactional replication. Running on the distributor it reads the Publisher’s transaction log and copies the transactions in batches to the distribution database.
- Merge Agent – In merge replication it detects new data on the subscriber or publisher and handles resolving conflicts to merge the data together.
- Queue Reader Agent – Used in transaction and snapshot replication with updatable subscriptions. It runs on the distributor and moves changes to the subscriber back to the publisher.
The figures above show where each of the components involved in replication should reside for the best performance across a WAN. Also note that the distribution agent and Merge agents can be on either side of the WAN. Note that to speed up the initial snapshot when using NitroAccelerator make sure the snapshot file is written locally to the distributor, then applied from there. Installing NitroAccelerator onto each of these endpoints then reduces your bandwidth used and improves latency by up to 90%!
If you want to try Nitro Accelerator for WAN replication with these simple instructions and full support, give it a try!
Dramatically accelerate and secure your end-to-end SQL Server network traffic for:
- SQL Server Tabular Data Stream (TDS)
- Windows SMB File Transfer
- Database Mirroring
- SQL Server Analysis Services (SSAS)