SQL Server Data Center Migration solution

The company has decided to relocate the offsite data center back to the local, the existing architecture is SQLSERVER2012 's AlwaysOn cluster

Optional options are available

1. Add new nodes offsite to transfer synchronization data with AlwaysOn log distribution data. Pros: High-availability solutions, no differences in data, smooth switching, no downtime. Disadvantages: Complex operation, large database, synchronization time is more, and will occupy a large bandwidth, affecting the normal business

2. Full backup after the computer room directly express hard disk, network transmission differential backup and transaction log backup. Advantages: Primary Data backup manually brought back, saving data transfer time. Cons: Downtime required to synchronize data, downtime required to see log backup back and restore time

At present the demand is more urgent, the request does not affect the business situation to carry on, therefore currently uses 2 plan

The implementation of Scenario 2 is a full backup + differential backup + transaction log backup, in which differential backups are made for differential backups 1 days to two days prior to the confirmation switch, after the backup is dragged back via FTP, transaction log backups are made every 1 hours or 4 hours after a differential backup, and the database is continuously restored

If there are multiple full backups, multiple differential backups, and multiple transaction log backups, after all the backup files have been added, the system chooses to be the latest full backup + The first differential backup after this full backup + all transaction log backups after the first differential backup

Full Backup history

Differential backup History

Log backup History

Deleted backup files prior to 201710261430

If all are added to the recovery plan by default, SQL Server chooses the recovery plan and files autonomously

The advantage of this is that the operator does not have to worry about data loss, the downside is that transaction log backups are much larger than differential backups, which can affect recovery speed, and because the transaction log is also transmitted over the network, it also consumes bandwidth and transmission time

Operation Steps:
1. to [dbo]. [Game_one] Multi-batch, write and delete modification operations, transaction log backups between each batch operation, and at least two differential backups between multiple transaction logs
2. Statistics before the last transaction log backup data changes, statistics directory has deleted specific data, new specific data, total data, data value, for integrity verification

The database is then recovered once using the system default recovery scheme and is more complete than the data

Then use the most recent full backup + recent differential backup + all log backup recovery after the most recent differential backup

To the above example, only 1 fully prepared, 1 differential, 2 transaction log backups can be

This allows the actual operating scenario to determine when differential backups are made based on network bandwidth transfer rates and daily transaction log increments, as well as the transaction log backup interval

The specific operation becomes 1 days or two days before the switch, make a differential backup, then make a transaction log backup every 6 hours, switch the day, every 1 hours transaction log backup

The frequency of operation depends on the file size and transmission speed.

SQL Server Data Center Migration solution