If you need to replicate your SQL Server data to a server located in a different data center you have several options. In this post I am making the following assumptions:
- Your SQL Servers are not running in an active directory
- You are either running SQL Server 2008R2 or SQL Server 2012.
Database Mirroring
After using transactional replication for many years I was ecstatic when Microsoft released database mirroring as part of SQL Server 2005 because it requires very little management overhead.
Pros
- Fail-over and fail-back at the click of a button
- Complete replication of all data and objects – including changes to objects
- Data is replicated instantly
- Automatic failover (if using a witness server)
- Transparent connect by client after failover
Cons
- In SQL Server standard edition only synchronous mirroring is available so performance can suffer
- If principal server goes down you need break the mirror and set mirroring up again
- Security can be complex to set up
- Data can’t be read at the standby server unless you use database snapshots (see below)
Database mirroring is my number one choice for replicating data between two data centers. The only reservation I have about it is if your database performance is already poor, network latency between your servers is high, or your database shows heavy transaction log use. In this case, database mirroring can cause an unacceptable performance impact. For more information about this see the following post from Microsoft:
http://technet.microsoft.com/en-us/library/cc917681.aspx
On SQL Server enterprise edition you have a choice to run mirroring in High Performance mode so it isn’t an issue. To find out what edition of SQL Server you are running run the following command in SQL Server Management Studio:
SELECT @@version
A nice added benefit if you are running enterprise edition is that you can take database snapshots of the mirrored database. This allows you to use the database as a read-only copy to offload reporting queries. Enterprise edition also has an even solution that is based on database mirroring. It is called AlwaysOn. But AlwaysOn requires that your servers be running in the same active directory.
Log Shipping
Log shipping is an older technology where the transaction log is backed up on regular intervals, copied to the standby server and then restored to the standby database.
Pros
- Lower performance impact than synchronous database mirroring
- Available on SQL Server Web edition
Cons
- Data is not copied instantly to the standby server. Potential higher amount of data loss in the event of a failover
- Fail-over is easy but fail-back not
- More difficult to manage. Transaction log is first backed up, then copied, then restored and more things could go wrong. Need a fail-safe mechanism for copying the files.
- Failing back requires setting it up again.
- Data can’t be read on the standby server
Transactional and peer-to-peer Replication
Transactional replication is not set up at the database level. It is set up at the object level and this is one of its main weaknesses because it means that a change to a table or any other database object cannot be made without modifying the replication.
Pros
- Lower performance impact than synchronous database mirroring
- Available on SQL Server
- Good if you are looking to replicate only a few tables
- Data is replicated semi-instantly
- Data can be read at the standby server.
Cons
- If you change a table or other object in the database you have to set up the publication from scratch.
- Web edition Complex to manage because it is done at the object level.
- More difficult to manage. Transaction log is first backed up, then copied, then restored and more things could go wrong.
- Fail-back is difficult unless you are using Peer-to-Peer replication.
- Not everything can be replicated. Database security for example. There are also restrictions on large data types like image and text.
Replication is a good solution if you are only interested in making some tables available on your standby server but if you want to replicate the entire database then it isn’t an ideal solution because of the difficulty you will face when making changes to tables or other objects in your database.
Extra Azure option
There is one option which only applies to Azure. At the time that I am writing this blog there is a feature called SQL Data Sync in preview on Azure. It allows you to synchronize selected data across multiple SQL Servers and SQL Database instances. More details about this feature can be found here: http://msdn.microsoft.com/en-us/library/windowsazure/hh456371.aspx