Backup Azure SQL Databases

You have a good reason to use Microsoft Azure SQL Databases; but, you are wondering how you can backup the Database locally. Can you include the Azure Databases protection in your company’s backup strategy? What does it take to back up the Azure Databases?

In this blog post, I am going to share with you a solution I used for one of our Azure Database customers who wanted to backup the Azure SQL Database locally. The solution I came up with consists of the following:

• Azure Databases – SQL Database
• VM/Physical Server with local SQL server installed
• An Empty SQL Database
• Configure Azure: Sync to other Databases
• Veeam Agent & Veeam Backup & Replication (depends on the deployment)

The following diagram illustrates the solution I am describing on this blog post:

Solution Overview

There are several ways to backup Azure SQL databases. Of the two ways, one is to use the Veeam Backup product, and you use the Veeam Agent in the event you choose to deploy your SQL Server on a physical server, or on a Virtual Machine, inside the Azure Cloud. The other way is to deploy the Veeam Backup solution on premises on a VM inside your Hypervisor infrastructure. In that case, you use the Veeam Backup and Replication product; or, you can also use a combination of both ways.

After the SQL Server is deployed,  the solution requires the creation of an empty SQL database, and then the synchronization between the two databases must be configured. No worries, I will take you through the steps.

Preparing your Azure SQL Databases for Sync

In this first step, I will discuss the preparations of the existent Azure SQL databases to be synchronised. You must follow these steps:

1. Create a Sync Group

From Azure Portal, select SQL Databases. Click on the Database you are going to work with. In the following example, I created a temporary SQL Database and then filled it with temporary data for testing.

After you have moved to the database properties screen, select the Sync to other databases option:

Then select New Sync Group. Complete the following entries:

a. Fill in the name of the group. In this example, it is GlobalSync,

b. The database you want to sync. We have used the AzureSqlDb on Server: depsqlsrv.

c. Select Automatic Sync on or off as desired. Off in this example.

d. Select the conflict resolution option. Here the Hub is to win.

In the next step, you are going to configure the On-Premises Database Sync members. That is, the Azure Database and the Sync Agent, as shown below.

Note: You must copy the key for later use.

Next, download the Client Sync Agent from the provided link and install it on the SQL Server. Press OK. The next step is to Select the On-Premises Database.

Prepare the SQL Server

After completing the steps above, switch on, then login to your SQL server and create an empty SQL database.

Next, start the installation of the Client Sync Agent. Run it after the installation.  After the Client Sync Agent has started running, press Submit Agent Key. Provide the key you copied from the previous step and your Azure SQL Database username and password:

After pressing the OK button, press Register, then provide your local SQL server name and the name of the temporary database you just created.

The steps above should run without connection issues. If you encounter problems, go back over the steps and setting and correct the errors.

You are now set up with your SQL Server preparation and your server is waiting for the synchronised data to be received. Before you get excited, we must switch back to the Azure Portal to continue with the final steps before we test the solution.

Back To Azure Portal

From the previous steps, we now continue with the selected database configuration. The Client Sync Agent communicates with the Azure Portal and updates on the local SQL Server database. Now we can complete the configuration below.

Press OK three times to return to the last step on the Sync Group configuration process. Here we must select the Hub Database tables to sync with the local database.

Press Save to finish.

Testing the Solution

To test our solution, let’s first browse to the SQL server and check to see if we can find any tables inside the database we just created.

We should not find any at this stage:

Now let’s initiate a manual sync. Remember, we must configure the solution to run manually from the Sync Group we just created. To sync the database with the local SQL, you must press the Sync button at the top of the GlobalSync screen. See below.

The Sync should complete successfully. If it doesn’t, check your settings and try again. Now it is time to check the local SQL. See the screenshot below.

Conclusion

With that procedure we have just demonstrated, I have been able to sync the Azure SQL Databases to the local SQL database where I run a frequent Veeam backup using the Veeam Backup and Replication product. This way, I achieved my customer’s objective of saving the Azure SQL Databases locally. If necessary, I can use Veeam SQL Explorer to recover the database and tables to the local server. From there, I can sync it back to the Azure SQL databases.

This time, I have demonstrated a manual sync process; but you can automate the sync to seconds, minutes, or hours. I hope you found this blog post useful.