close

Azure Backup

Microsoft Azure Backup

Azure Backup

Step by Step Azure Backup and Recovery for the SQL Server VM’s Part I

Azure Backup and Recovery for the SQL Server VM

Estimated Reading Time: 7 minutes

Recently I got a chance to work on the Azure Backup of the SQL Server VM’s. In this post, I am going to share my experience with the Azure Backup of SQL Server VM’s. In Part II of this post, I will share my experience with Recovery of the Backup databases. Please note that this feature of the SQL Server VM backup is still in public preview, while I am writing this post.

I would also like to share with you all that this is one of the features for which we are waiting for last one year since we have huge SQL server footprints in Azure and it’s very difficult to manage without an enterprise level capability. However, while testing this solution we have found that Azure Backup for the SQL server is a right fit solution if you have SQL server versions above SQL Server 2008.

Azure Backup provides an Enterprise class backup capability for SQL Server running in Azure VMs. All backups are stored and managed in a Recovery Services vault. There are several advantages that this solution provides, especially for Enterprises.

As per MS, the following are the advantages of the Azure Backup of the SQL Server VM’s

  • Zero-infrastructure backup: You do not have to manage backup servers or storage locations.
  • Scale: Protect many SQL VMs and thousands of databases.
  • Pay-As-You-Go: This capability is a separate service provided by Azure Backup, but as with all Azure services, you only pay for what you use.
  • Central management and monitoring: Centrally manage all of your backups, including other workloads that Azure Backup supports, from a single dashboard in Azure.
  • Policy-driven backup and retention: Create standard backup policies for regular backups. Establish retention policies to maintain backups for years.
  • Support for SQL Always On: Detect and protect a SQL Server Always On configuration and honor the backup Availability Group backup preference.
  • 15-minute Recovery Point Objective (RPO): Configure SQL transaction log backups up to every 15 minutes.
  • Point in time restores: Use the portal to recover databases to a specific point in time without having to manually restore multiple full, differential, and log backups.
  • Consolidated email alerts for failures: Configure consolidated email notifications for any failures.
  • Role-based access control: Determine who can manage backup and restore operations through the portal.

Please find the architecture diagram of this solution.

Fig: Azure Backup of SQL Server VM’s

Now let’s see the supportability.

Supported operating systems

  • Windows Server 2012
  • Windows Server 2012 R2
  • Windows Server 2016

Linux is currently not supported. We have tested with SQL server 2008 R2 and it’s working fine.

Supported versions/editions of SQL Server

  • SQL 2012 Enterprise, Standard, Web, Developer, Express
  • SQL 2014 Enterprise, Standard, Web, Developer, Express
  • SQL 2016 Enterprise, Standard, Web, Developer, Express
  • SQL 2017 Enterprise, Standard, Web, Developer, Express

There is a workaround for SQL Server 2008 which I will discuss in Part II.

Prerequisite

Before you can back up your SQL Server database, check the following conditions. :

Important Note

If the virtual machine hosting your SQL databases was not created from the Azure marketplace, complete the following section to install the extension and set appropriate permissions. In addition to the AzureBackupWindowsWorkload extension, Azure Backup requires SQL sysadmin privileges to protect SQL databases. While discovering databases on the virtual machine, Azure Backup creates an account, NT Service\AzureWLBackupPluginSvc. For Azure Backup to discover SQL databases, the NT Service\AzureWLBackupPluginSvc account must have SQL login and SQL sysadmin permissions.

Step by step solution

Now I directly jump into the solution. First, we should go to the Recovery Services Vault. And select a particular recovery services vault where I would like to store the backup as you can see below.

Once you open the Recovery Services Vault it will show the below dashboard.

In the next step, you need to click on the backup.

In the dropdown list please select the SQL Server in Azure VM (Preview) and click on discovery.

Now you can view a screen like above where it will list the SQL Server instances which it able to discover.

I think you have noticed that for some of the instances it’s showing error. The reason behind that those instances are SQL server 2008 or SQL Server 2008 R2, rest of the instances are either SQL Server 2012 or above.

If you click on the error details you can see something like this

Now for the servers for which the database discovery is successful, we can view the databases.

Now if you click on configure backup you can see the list of the instances – the Ready one are the ones where you can take the backup as well they are ready for the recovery.

Now once the discovery of the DB’s is completed you can see the following message.

Now, what does it do to discover the DB actually it will add a service account NT Service\AzureWLBackupPluginSvc in the Server and gives the SQL login and SQL sysadmin permissions.

You can also view the plugin in the VM extension as shown below.

During the installation process, if you see the error, UserErrorSQLNoSysadminMembership, sign into SQL Server Management Studio (SSMS) with an account that has SQL sysadmin permission. Unless you require special permissions, you should be able to use Windows authentication to recognize the account.

Now when the databases were discovered the next step is to configure the backup. For the testing, I have chosen a test database called AdventureWorks2014.

In the next step, you have to choose the backup policy as shown below. A backup policy defines a matrix of when the backups are taken, and how long the backups are retained. You can use Azure Backup to schedule three types of backup for SQL databases:

  • Full backup – A full database backup backs up the entire database. A full backup contains all the data in a specific database or set of filegroups or files, and enough log to recover that data. At most, you can trigger one full backup per day. You can choose to take a full backup on a daily or weekly interval.
  • Differential backup – A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since the full backup. At most, you can trigger one differential backup per day. You cannot configure a full backup and a differential backup on the same day.
  • Transaction log backup – a log backup enables point-in-time restoration up to a specific second. At most, you can configure transactional log backups every 15 minutes.

The policy is created at the Recovery Services vault level. If you have multiple vaults, the vaults can use the same backup policy, but you must apply the backup policy to each vault. When creating a backup policy, the daily, Full Backup is the default. You can add a Differential Backup, but only if you switch Full Backups to occur Weekly. The following procedure explains how to create a backup policy for a SQL server in an Azure virtual machine

In our case, I have chosen the default policy which is already present by default.

The next step is to enable the backup.

Once you click on enable backup button you can see something like below.

Now you can also start the backup manually by clicking the backup item button.

It will show you the list of the SQL VM backups which is currently configured as shown below

If you to the backup items you can see the items listed over there.

Once you take the first backup you can something like below.

You can also manually trigger the backup by clicking on Backup now button.

It will show the following screen.

Clicking on OK button will trigger the backup of the database.

You can see the status of the backup in Backup Jobs

A report may look like the below screen. You can also filter the report and can go back to a previous date.

That’s all for today about the SQL server VM backup in Azure. In my next post (Part II) I’ll write more on the recovery of various size databases and possible ways of recovery. I hope you will like this post and it will help you in planning the backup of your SQL server environment.

Thanks for your time like our page and follow us on below social media have a great day ahead.

read more
Azure Backup

How to check the status of Azure Backups in the backup dashboard built with the Microsoft Power BI

no thumb

Recently we have seen blue screen error in one of the Azure Windows VM and after lot’s of troubleshooting steps when we not able to rectify the issue and (before opening a case with Microsoft) we have thought to check with the backup team so that we can restore the VM from the Azure backup vault but unfortunately this VM backup job was failing from day one and no one has noticed the issue, which is not an isolated case and it may happens to any enterprises who are aggressively moving to Azure.

Azure backup is one of the important task once the VM build is over and generally this is taken care by the backup team in large organisations. In recent days we have seen hundreds of subscriptions and thousands of VM’s so it’s becoming a very tedious job for the Azure backup administrators to check and maintain the staus of the backup of Azure VM’s everyday.

And regular backup of the of the Azure VM is also very important.

One way to check the status of the VM is to write a custom powershell script which you can run against the Azure Recovery Services Vault to check the backup status and can schedule that in a Azure Runbook and the second option is to configure the Azure Backup reports to be viewd in Power BI. In this blog post I am going to show you how to configure Azure Backup Reports with Power BI.

To configure the backup reports and dashboard first go the Recovery Services vaults which we have configured for our backup. As you can see we have got many recovery services vaults in my subscription so we will choose some of them where we need the backup reporting need to be configured.

Once we select the backup vault the next step is click on the Backup Reports

In the next step we need to select the Diagnostics settings, here we can turn on the diagnostics for the backup reports

This diagnostics settings are very important where we can configure the storage account and the logs which we want to capture in our report. I have selected the Azure backup report logs which will helps me determining the status of the Azure VM.

You need to select a storage account where we can archive the storage logs and if we have any plans to view the data in OMS, we need to select the checkbox of Send the data to Log Analytics.

Now in this step we will need to configure the Power BI dashboard. The first step here is to login to Power BI which is a part of the O365 offering and the license is part of your O365 license pack.

To do this we need to login to this URL

https://powerbi.microsoft.com/

The website will look like this.

Once we login to with our MS account or organisation account we can select on the Services and click on Get button.

Once we click on the get button we can see the list of the Apps of Power BI listed

In next step we need to Select the Azure Backup tile and click on the Get it now button.

Now the next step is to connect to Azure Backup, here we need to select the Azure Storage Account from where we need to pick up the backup data.

Here we hit an error. As you can see below the backup report is not yet ready since it’s not yet 24 hrs. since we have configured the backup reporting. So we have to wait for 24 hrs. to get the back up data.

Next day we able to configure the backup as you can see below. Once it’s configured you can see dash board.

Once it’s done we can see a beautiful dashboard for the backup reports.

If you click on any of the Azure Backup we can see the below screen.

There are multiple backup dashboard available. Mainly storage, Backup Items, Job Health, Job Duration, Alerts

If you click on storage you can see the following screen.

The dashboard related to backup items looks like this

The Job health dashboard will look like this

The Job duration dashboard will look like this

Not only that you can export the data in Excel and play with that as you can see below

Click on export data

By running a filter against the Job Status we can identify the list of failed jobs and we can take necessary actions against the ones which has failed

Not only this, we can share all the backup dashboard with any one you think it’s required.

Also we can directly transfer entire dashboard data into excel including pre-created pivot tables by clicking on the Analyze on Excel Button.

That’s all folks. I hope this post will help the backup administrators to take preventive actions before they really need to know from someone else that the backup has failed.

read more
Azure BackupAzure VM

Should you upgrade to Azure VM Backup Stack V2?

AzureStack

Image result for azure backup

Picture Credit: Royalty Free Pictures from Pexeles.com

The Azure Resource Manager model has come up with the option to upgrade to VM Backup Stack V2.  There are many salient features of the VM Backup Stack V2 , the main price point I believe is the ability to take the snapshot backup of the disks up to 4 TB in Size. As per my experience, I know this is a great ability looking forward to up to 60% failure of the MARS agent backup which is not reliable. The ability to take the snapshot backup will also guarantee 99.99%  recovery of the snapshot disks. In scenarios where large disks were being backed up by MARS agent will definitely be backed by the Azure VM Backup Stack V2 and large disk snapshot backup is possible if you upgrade.

Another feature enhancement as per the MS site is as follows:

  • Ability to see snapshots taken as part of a backup job that’s available for recovery without waiting for data transfer to finish. It reduces the wait time for snapshots to copy to the vault before triggering restore. Also, this ability eliminates the additional storage requirement for backing up premium VMs, except for the first backup.
  • Reduces backup and restore times by retaining snapshots locally, for seven days.
  • Support for disk sizes up to 4 TB.
  • Ability to use an unmanaged VM’s original storage accounts, when restoring. This ability exists even when the VM has disks that are distributed across storage accounts. It speeds up restore operations for a wide variety of VM configurations.

Difference between the Backup Stack V1 and Backup Stack V2

ItemsBackup Stack V1Backup Stack V2
The process of BackupIn two phases, first the VM or disk snapshot has been taken and in next step, the snapshot will be sent to the Azure Recovery Services Vault.In this phase, the snapshot is taken and preserved for 7 days before sending to Azure Recovery Services Vault.
When the Recovery Point is CreatedA recovery point is created once phase 1 and 2 are done.A recovery point is created as soon as the snapshot is taken.
Recovery Point creation SpeedSlowFast
Storage CostNo additional storage costLocal storage cost may increase since snapshot will be stored for 7 days before moving to Recovery Services Vault. According to the current pricing model MS is not charging for storing the managed disks for 7 days.
Impact of the upgrade on Current Backup No impact.

Please note that incremental snapshot will be taken for the un-managed disk but for the managed disks the snapshot will be taken for the full disk. So in case if you planning for 1 TB of managed Disk you need to pay for the snapshot of the full disk.

How to upgrade

Log in to the Azure Portal and Go to the Recovery Services Vault.

Go to properties. In the left side pan you will see the following.

Click on the Upgrade Button

Click on the upgrade to upgrade to Backup Stack V 2.0.

Note: This upgrade is not Vault basis, it is Subscription based. And This change is not reversible too

Conclusion: The Azure VM Backup Stack V 2.0 is a good decision to upgrade if you have the large number of large disk capacity VM’s. You can go for it since there is no additional cost involved at the moment and there will be no additional configuration needed to be done in the recovery services vault and the existing backups will not be impacted.

That’s all for today. You have a great day ahead.

read more