SQL Server: Offsite log shipping using rsync

Microsoft has a hobby called binding. The one thing I hate most is that IIS is bound to Windows-upgrade IIS to upgrade the operating system, the server is not a PC, the system is not ready to reload.

When using SQL Server log shipping for offsite backups, Microsoft's binding strategy pits it once again. Log shipping only supports Windows file sharing in these 1 and only 1 ways, and the server for offsite backup is not on the same network as the primary server, and Windows file sharing is not available at all. The following figure is evidence of Microsoft's binding strategy:

The shared path \\dbserver\LogBackup in the above figure is set on the log shipping master, and the backup server needs to access the path, copy the log backup files, and recover them when log shipping.

While offsite backup, the backup server is not on the same network as the primary server and cannot be accessed through this shared path to the log file to be transferred.

How to solve this problem?

If you use Windows architecture VPN, you are on the Microsoft binding strategy.

We did not use this as a deceitful act method to solve the problem.

Does the backup server require access to \\dbserver\LogBackup? So we are in the same network with the backup server (intermediate server) to share a path, for the backup server, as long as the access to \\dbserver\LogBackup, no matter who is sharing it.

The problem with the shared path is resolved, but the real purpose of the backup server to access the shared path is to replicate the log backup files. So, the next question is how to sync the log backup files on the remote home server to this intermediate server?

You might think of FTP, and we started thinking the same thing. But FTP is an upload/download of the sharp weapon, but not the synchronization of the weapon. Our scene here is the intermediate server to sync with the log backup files on the remote primary server, such as the remote home server automatically deletes more than a few days of log backup files, the primary server deleted, the intermediary server will be automatically deleted.

Then I thought of the file Synchronization tool we were using--rsync (use the method can refer to a previous post: Windows platform file Synchronization Backup solution--cwrsyn), this is the open source world masterpiece, although it looks simple, ugly, but solve the problem is so effective.

We solved the long-distance love problem of SQL Server by Rsync, and made the remote automated log shipping easy.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

Share the rsync commands you use:

Rsync-avz--delete--no-super rsync://Remote home server/logbackup  /cygdrive/e/logbackup

Feelings after the problem is solved

Before we think of rsync, we intend to run the FTP service on the remote master server and then synchronize the FTP download log backup file from the remote master server by writing C # code. Even though this part of the code is not complex to write, the total added time from the creation of the project to the publishing project will be much longer than the time to deploy rsync.

So, for programmers, just writing code to solve problems is not enough. Real programmers are not only looking for how beautiful the code is, but also the pursuit of a more ingenious way to solve the problem, even if you don't write a line of code, even if it's not a technical problem.

End this blog with a sentence: if the problem is to be fun, the program life will be a joy.