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 at: www.www.nitrosphere.com/freetrial.
Have you taken the plunge to host SQL Server in the cloud yet? Every database administrator or architect who is hosting databases or database-driven applications in the cloud needs to move data in and out of the cloud from time to time, across network boundaries and back to local servers. Whether it is for maintenance, reporting or other business needs, it must be as efficient and transparent as possible.
Therein lies the challenge: how can you securely traverse boundaries, ensure data delivery, deal with congestion or latency issues that affect performance, and still meet maintenance windows? These are the “devils’ in the details” of cloud-based SQL Server hosting and application hosting. There are performance, security and cost considerations of moving data in and out of the cloud effectively to keep business running smoothly.
This post provides a view into the options available and makes the case for using NitroAccelerator, a breakthrough compression solution, to address these challenges and make cloud computing easier to manage and support.
First, it is important to consider how up-to-date the data must be in the local instance in order to pick the correct method for moving your data. Can the data be a day or hours old, or does it need to be a real-time copy? The table below shows some of the native methods available and how they are typically used.
Table 1: Common data movement methods
As you can see from the table, there is no native mechanism that performs well over lower bandwidth connections. However, using NitroAccelerator on the end-points, in a proper configuration, can enable real-time replication to remote instances of SQL Server. If the data doesn’t need to be available in real-time and is simply backed up on a regular schedule, NitroAccelerator can be used to shrink the time window used for moving the data.
How are you tackling this challenge? Consider this enlightened approach and check out a free trial of NitroAccelerator at: www.www.nitrosphere.com/freetrial.