The Devil’s In the Details:
Moving SQL Server Data in and out of the Cloud
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.
Method | Description | Suitable for WAN | Suitable for WAN with NitroAccelerator |
---|---|---|---|
Snapshot Replication | All forms of replication start with a snapshot. If your data changes infrequently and it is acceptable that replicated instances are out of sync for long periods of time, then simply doing a periodic snapshot can suffice. | No | Yes |
Merge Replication | Tracks data and schema changes with triggers and the subscriber will synchronize whenever it is connected to the network. Best for when the subscribers will also be receiving updates. | Somewhat. It is designed to be mobile and disconnected but in practice it performs poorly. | Yes |
Transaction Replication | As the name implies, it replicates on a transaction-by- transaction basis. It is best for keeping instances in sync real- time. | No | Yes, real-time replication is possible even over extremely low bandwidth connections. |
Log Shipping | Works by shipping log backups to another instance and restoring them. Not appropriate for real-time replication. | No | NA – If the files are being sent via SMB, Yes |
SQL Server Integration Services | Used for moving large amounts of data where transformations may also be needed. | Yes, if the maintenance window is large enough. | Yes |
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!
Dramatically accelerate and secure your end-to-end SQL Server network traffic for:
- SQL Server Tabular Data Stream (TDS)
- Windows SMB File Transfer
- Database Replication
- Thick Clients / Client-Server / 2-Tiered Applications