In this demonstration, I will back up SQL Server databases running on a VM using the Azure backup service and then restore one of them. Along with SAP Hana, SQL Server is a natively supported backup source. In this respect, the configuration and processes for backup and restore are very similar. I've got a Windows server 2016 virtual machine running SQL Server 2017 with three user databases, SAPDEV1, SAPDEV2, and SAPQA1. I created the server VM using a pre-configured Microsoft image that allows access from the Internet via RDP and has minimal security and network rules in place. Of course, this won't be the case for servers in a commercial environment. You will need to make sure that the virtual machine can access the backup destination. You can do this by adding a rule to your network security group using the pre-configured Azure Backup tag. Go into the applicable network security group, and under settings, select outbound security rules. Next, add a new rule. Under destination select service tag, then select Azure backup from the destination service tag drop-down. As you can see, there are a whole lot of pre-configured tags for Azure services. Click add to implement the new outbound security rule. Now, let's configure the database backup.

The first thing I need to do is create a vault, and I'll do that by going into the backup center. In the backup center, add a vault with the plus vault button. We want to add a recovery services vault, which, as I said, natively supports SQL Server and SAP Hana running on an Azure VM, as well as the ability to back up a virtual machine. So, I'll just click continue. The vault will be in the same resource group, SAP backup as my server, and virtual network, and the vault will be in the same region. We just need to give the vault a name, and then we can review and create. Once that's finished deploying, we can go to the resource and begin to configure our backup by clicking the backup button at the top of the page. This is a reasonably straightforward process. Azure backup service can also be used with on-premises workloads, but we are dealing with a purely cloud-based solution, so I will leave "where is your workload running?" as Azure. I do have to select what I want to backup. Here we have the list of resource types natively supported by the Azure backup service, and I'll select SQL Server in Azure VM. The discovery process is two-tiered. First, we need to find the virtual machines running SQL Server instances, then the databases hosted on those instances. Clicking start discovery will find the virtual machines that are deployed in the same region as the vault. Sapsqlserver, the only VM has been found, so I can select it and can click discover DBs. To be completely honest, I found the GUI or wizard experience a bit flaky and not flowing as well as it could. The first time I went through this process, after discovering the VM, the wizard took me back to the beginning when I went to discover the databases.

Here we have the server instance with the three system databases and the three user databases. I'll protect and backup all of them. Auto protect will protect all databases, both current and future, that are deployed on an instance. Enabling auto-protect means adding future databases does not require you to modify your backup configuration. With all the databases selected, I'll click okay to continue. Now I will set up the backup policy, or as I'd prefer to call the schedule. Default values are automatically generated for the policy, but I want to change them by clicking on the edit this policy hyperlink and then edit the full backup.

As you can see, the schedule aspect for the full backup is only a small part of the policy, where I can choose the backup frequency and when it should happen. You have a lot of options when it comes to setting your retention range. I'll keep the backup frequency at daily and change the time to 2 AM Pacific time. While I'm in here, I'll just change the monthly Backup Retention from five years to 3 years and the yearly one from 10 to 5 years and click okay to confirm the changes. I'll also change the log backup from two hours to every 30 minutes. Having set all my backup policy parameters, I'll enable the backup. Once the backup policy is deployed, we can see in backup items, under Protected Items, that our six databases are our backup item count.

Now I'll head over to the SQL server VM and delete the SAPQA1 database before restoring it. In Backup Items, we can drill down into the SQL in Azure VM line to see the database backups. Over on the right-hand side in the ellipsis menu, we have the option to initiate an on-demand backup, but I am going to select restore. Restoring a database is relatively straightforward. The original server and instance are already displayed in the drop-down lists, but the lists give you the opportunity to restore to an alternative location. Just for the sake of this demonstration, I am not going to change the restored DB name back to the original, so we can see that it actually has been restored from the backup service. Next, I need to select the restore point, which will be the latest full backup, so I'll select full and differential from the top of a pane. If this were a production system, there would be many backups to choose from, so at the top, we have a starting time filter to make backup selection a little bit easier. Having selected my full backup, I'll click okay, and check advanced configuration where I have the opportunity to change the location of the physical database files and set the No Recovery status. Clicking okay will kick off the restoration process. Back on the database server, the newly restored SAPQA1 database is there.

We've backed up the databases, but what about the virtual machine? The data and log files are on separate drives, so we just need to do a backup of the VM and its operating system drive, which in this case is the C drive of a windows machine. Back in the portal, I'll add another backup to the SQL server backup vault, but this time I will select Virtual machine from the backups source list and click the backup button. I'll add the sapsqlserver VM within backup configuration by selecting it from the virtual machines list and clicking okay. Backing up a database server is obviously a very common scenario, so we can choose to backup only the operating system disc, which I will select. Then it's just a case of clicking enable backup. Going back to backup items, we can see the virtual machine waiting for its initial backup.