SQL Server can also be a remote love: Easily geo-log shipping with rsync

Microsoft has a hobby called binding. I hate it most. A binding is the binding of IIS to Windows-upgrading IIS to upgrade the operating system, the server is not a PC, the system does not want to reload can be re-installed.

When using SQL Server log shipping for offsite backups, the Microsoft binding policy was once again in the pit. Log shipping only supports Windows file sharing in 1 of these 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. Is evidence of Microsoft's binding strategy:

The shared path in \\dbserver\LogBackup is set on the log shipping primary server, which requires access to the backup server for log shipping, and copies the log backup files and restores them.

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

How to solve this problem?

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

We didn't take this as a way to solve the problem with counter switch.

Does the backup server need to access \\dbserver\LogBackup? Then we share one such path on the same network as the backup server (intermediate server), and for the backup server, you can access the \\dbserver\LogBackup as long as it is available, regardless of 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 copy the log backup file. So, the next question is how do I sync the log backup files on the remote master server to this intermediate server?

You might think of FTP, and we started thinking the same thing. But FTP is an upload/download tool, but not a tool for synchronization. Our scenario here is that the intermediary server to synchronize with the log backup files on the remote primary server, such as the remote master automatically deletes more than a few days of log backup files, the primary server is deleted, and the intermediary server is automatically deleted.

And then I thought about the files we're using.--rsync (using the method can refer to a previous post: Windows platform Web site file Synchronization Backup solution--cwrsyn), this is the open source world masterpiece, although things look simple, ugly, but solve the problem is so effective.

We rely on rsync to solve the problem of remote love of SQL Server, easy to achieve a remote automated log shipping.

Share the rsync command used:

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

Thoughts after solving the problem

Before we think of rsync, we intend to do this by running the FTP service on the remote master server and then synchronizing it by writing C # code from the FTP download log backup file on the remote primary server. Even though this part of the code is not complex to write, the whole time from creating a project to publishing a project will be much more than the time to deploy rsync.

So it's not enough for programmers to just write code to solve problems. Real programmers not only pursue the beauty of code writing, but also pursue a more ingenious way to solve problems, even if they do not write a line of code, even if the solution is not a technical problem.

End this post with a sentence: If you treat the problem as fun, the program life will be a pleasure.

Tags: sql Server

SQL Server can also love remote locations: Easily geo-log shipping with rsync