SQL Server Change Tracking vs Replication vs Differential Backup

Question

It’s good that we have a critical transactional database and its full recovery mode in SQL Server 2008. We have two different servers in two different data centers on two different time zones. And I'm trying to set up the best way to make the database as possible as possible using various parameters. The database is currently only 1.5 GB, it is expected to grow by 1 GB every 6 months.

We used a simple solution, using SMO to create a FULL Backup at midnight 1am, and then do a differential backup every 10 minutes. And we transfer this data to other servers that operate as slaves, and we restore the data to the slaves. Thus, all subordinates work for 15 minutes compared to the current database, so in case of failure we will have data up to the last 15 minutes.

Now I want to compare this solution with respect to replication and change tracking.

Both replication and change tracking put some extra metadata in the database to do whatever they do and use the processor a bit more. However, they will no longer load the processor (as I understand it) compared to Diff Backup. I assume that Diff Backup will support some transactions that are waiting or increasing some waiting queues, and this can lead to a delay or loss of information when users use it.

I need to know if Diff Backup will load the server more every 15 minutes? Or is it really not recommended to use Diff Backups every 15 minutes when transactions are processed?

Note. Transactions are applied only on the Primary server, and they are applied to slaves using backup recovery. Delivery journal does not send changes to the scheme, and somehow, if it stops working, we cannot receive any error notifications, in our own solution we will receive logs that will help us.

Answer 1

We found our own solution as follows:

• Mirroring and sending logs require a VPN and high security. So we dumped them.
• Mirroring and sending logs and almost all SQL Server synchronization methods really don't care about network bandwidth usage and they don't compress anything.

MSDN says that differential file backups are faster, we chose differential file backups. Yes, within 15 minutes it looks a bit crowded, but they are faster and more reliable. And within 24 hours, the accumulated changes are only a few MB.

Backups are performed by the Windows user service and they are also compressed to preserve network transmission. Plus we get the right email notifications of everything.

The slave Plus database can be anywhere on the Internet. Backups are protected and password-compressed. And the small HTTP server built into the web server transfers data from one computer to another, so less overhead is required.

When we have many servers, setting them up is a huge pain. In addition, each network administrator can make a mistake and create a disaster.

Answer 2

Forget replicating or changing data tracking. They do not replicate the scheme, and they add significant overhead. None of them are designed as high availability or disaster recovery solutions. They can be used as such, but pale in comparison to dedicated solutions, such as sending logs, mirroring the database or mirroring the equipment.

Log delivery transfers everything in the database, including the schema, as well as users, permissions, indexes, data, etc. etc. You did not specify when you migrate the log backups. Performing a differential backup every 15 minutes seems unnecessary. Differential backups are cumulative, they contain every change since the last full backup, so they will increase in size during the day. 15 minutes sounds like a period of time for periodic log backups, not differential ones.

Log delivery depends on file copy operations from an SQL agent job. Therefore, it must access shared folders and requires authentication. On different domains, you will need either direct access or a VPN server.

Database mirroring also creates an identical copy of the database, but its data loss window takes up to several seconds, rather than the log backup interval. Database mirroring supports a special connection open between two servers, and the main one sends each transaction in the mirror, as it happens, in real time. Because mirrored endpoints support certificate-based authentication , this can be easily configured for cross-domain and does not require a VPN. DBM can be synchronous (each transaction on the main one waits until the mirror confirms it before committing, otherwise a high security mode) or asynchronously (the main one writes in front of the mirror and performs an immediate, for example, high-performance mode). If the connection is lost, the manager will begin to work "exposed", so you will not lose service, but you are subject to data loss. As soon as the connection is restored, the director will submit the mirror to the pending transaction queue (i.e. the part of the LDF file that has not yet been sent) until the mirror is updated to date. All this is automatic, and in SSMS there are monitoring tools that can be configured to send notifications when the connection is lost, when the main participant is working, when the off-line queue grows at a predetermined size.

Hardware Mirroring: You need to speak with your hardware vendor or with your data center operators. It costs a fortune.

Full database mirroring is by far the best option.