SQL Server Change Tracking vs Replication vs Differential Backup

Question

Ok we have critical transactional database and its in full recovery mode in SQL Server 2008. We have have two different servers in two different data centers on two different timezones. And I am trying to setup best way to make database as upto date as possible using various options. Database is currently only 1.5GB, expected to grow 1GB every 6 months.

We have used a simple solution by using SMO to create FULL Backup at midnight 1 am, and then take differential backup every 15 minutes. And we transfer this data to other servers who are working as slaves and we restore data on slaves. So all slaves are running 15 minutes old compared to current DB, so in case of crash we will have data till last 15 minutes.

Now I want to compare this solution with respect to Replication and Change Tracking.

Both Replication and Change Tracking puts some extra metadata in DB to do everything they are doing and utilize cpu usage little extra. However they will not put more load on CPU (as far as my understanding) compared to Diff Backup. I am assuming that Diff Backup will keep some transactions awaiting or increase some pending queues and that might create delay or loss of information while users are using it.

I need to know will Diff Backup every 15 mins put more load on server? Or its really not adviceable to use Diff Backups every 15 mins, when transactions are processing?

Note: Transactions are only applied at Primary Server and they are applied to slaves using backup restore.. Log Shipping doesnt ship schema changes and somehow if it stops working we are unable to get any error notifications, in our own custom solution we get logs emailed to us that helps us.

Answer 1

Forget about replication or change data tracking. Those don't replicate schema and they add significant overhead. Neither is designed as a High Availability or Disaster Recoverability solution. They may be used as such, but pale in comparison with dedicated solutions like log shipping, database mirroring or hardware mirroring.

Log shipping transfers everything in the database, including schema, as well as users, permissions, indexes, data and so on and so forth. You did not specify when do you transfer the log backups. Doing a differential backup every 15 minutes sounds like overkill. The differential backups are cumulative, they contain every change since the last full backup, so they will increase in size over the course of the day. 15 minutes sounds like a time period for periodic log backups, not differential ones.

Log shipping relies on file copy operations from a SQL Agent job. As such it needs to access file shares and that requires authentication. Over distinct domains you'll need either Direct Access or a VPN of some sort.

Database Mirroring is also creating an identical copy of the database, but its data loss window is up to seconds as opposed to the log backup interval in log shipping. Database Mirroring keeps a special connection open between the two servers and the principal ships every transaction to the mirror, as it happens, in real-time. Because mirroring endpoints support certificate based authentication it can easily be set up cross domains and does not require a VPN. DBM can be synchronous (every transaction on the principal waits for the mirror to confirm it before commit, aka. high safety mode) or asynchronous (the principal will write ahead of the mirror and commit immediately, aka. high performance mode). If connectivity is lost the principal will start running 'exposed', so you do not loose service, but you expose yourself to data loss. As soon as connectivity is regained the principal will feed the mirror the pending queue of transactions (ie. the part of the LDF file that was not shipped over yet) until the mirror is back up to date. All this is automatic and there are monitoring tools in SSMS that can be set up to send notifications when connectivity is lost, when the principal is running exposed, when the unsent-queue is growing over a pre-set size.

Hardware mirroring: you need to talk with a hardware vendor or your data center operators. It costs a fortune.

Overall Database Mirroring is by far your best option.

Answer 2

We found our own solution as follow,

1. Mirroring and Log Shipping both require VPN and High Security So we dumped them.
2. Mirroring and log shipping and almost all synchronization methods of SQL Server really does not care about network bandwidth usage and they dont compress anything.

MSDN Says Differential File Backups are faster, we chosen differential file backups. Yes for 15 min, it looks little overkill but they are fastest and more reliable. And for 24 hours, the accumalated changes are only few MBs.

The backups are taken by custom windows service and they are also compressed to save network transfer. Plus we get proper email notifications of everything.

Plus slave database can be anywhere over internet. Backups are secure and compressed with password. And a small HTTP in built web server transfers data from one machine to another so less configuration overhead is required.

When we have many servers, configuring them is huge pain. Plus every network admin may make a mistake and create disaster.