The primary advantage of Azure is the range of services available in the same cloud, which makes this Microsoft solution suitable for multiple environments. Running SQL databases in the cloud and using them to perform simple operations via the Azure portal is a valuable feature available when subscribing to Azure.
Data that organizations store in Azure databases is in most cases an asset that is critical to support the production environment and daily operations. It is necessary to consider creating Azure SQL backups to help your organization keep control of important data in case of any data loss scenario. Always keep in mind that Microsoft’s shared responsibility model supposes that Microsoft is only responsible for the availability of services. Data coherence and safety is the user’s concern.
With SQL Server running locally, hardware failures are the additional factor to consider. With SQL databases on Azure, you don’t have the hardware to worry about, thus excluding one of the risks. Nevertheless, app errors or human mistakes leading to accidental data corruption or loss are possible in the cloud. Thus, setting the proper workflows to back up Azure SQL databases is crucial for organizations that rely on this Microsoft platform. In this post, we explain how to back up an Azure database and restore your data whenever necessary using Microsoft native tools. Alternatively, you can consider integrating NAKIVO backup to Azure into your infrastructure to simplify Azure backup workflow configuration and management.
Why Back Up Azure SQL Database?
Usually, the use case for cloud storage is the provisioning of an additional security level. Whenever things go wrong with an individual’s or organization’s local hardware that lead to backup corruption, the backup copy in the public cloud keeps the critical data safe. Thus, sending your database and SQL Server backup to Azure is a reliable way to enhance data protection, ensure production stability and proper functioning of your organization.
Azure Database Backup: How to Send SQL Database Backups to Cloud
With Azure cloud as a repository for your data backups, you can avoid management difficulties and storage space issues. In addition, Microsoft offers a customizable disaster recovery functionality that you can quickly implement for your databases to achieve a tight RTO.
To send SQL Server backups to Azure, a configured container that you can dedicate as a storage is required.
Requirements for SQL Azure Database Backup
The Azure storage used for storing the SQL database backup should be created and configured as a prerequisite. Here is what you need to do before configuring your Azure SQL backups.
- For starters, find Create a Resource in the Azure management portal.
- After that, choose Storage > Storage account. Enter the required info according to your preferences, including resource group name, storage account name and location. Once the required fields are filled, click Next.
- Configure the settings in the advanced section as well. Optimally, set Secure transfer required as Enabled and Allow access from All networks. Setting Blob soft delete as Enabled helps to ensure additional protection against human errors: you can fix accidental deletion issues in the storage account quickly and easily with this option.
- Add the tags that you find suitable for your infrastructure in the next step.
- Check the settings. Then, click Create to finish creating your new storage account.
After the storage volume creation is done, you should set up a backup data storage container. In the storage account, find and click the + Container tab. There, add a suitable container name and set Public access level to Private (no anonymous access).
This container can become a storage for backup files (.bak) with the use of SaS access authentication tokens or appropriate storage keys.
Azure Database Backup Configuration
Now, you can back up SQL Azure database.
- Connect to your SQL server via SQL Management Studio. Then, right click the database that you need to back up. In the context menu that shows up, find Tasks and then hit Back Up….
- In the Destination part of the window, set the Back up to line to URL. Then, hit New container
- Sign in to Azure and choose the previously created container. Create a credential and click OK to proceed.
- You will get a prompt to sign into your Azure subscription. Select the container we created earlier and create a credential. Hit OK to proceed.
- The wizard now shows the set backup destination URL in the list. To launch the backup workflow, click OK.
- After the backup is completed, you can see the message below:
The backup of database ‘your database name’ completed successfully.
Then, from the Azure portal, you can see the backup file is in the appropriate container.
Note: A slow internet connection can be an issue when you upload your backups directly to Azure or other cloud storage. If that’s the case, consider backing up data to a physical drive first and sending backup copies to the cloud after. This way, you can avoid network bottlenecks that might impact your operation.
Backup Azure Database: Cloud Archiving
Larger databases mean larger backups, thus increasing storage space requirements and extending full backup duration. Regularly deleting unnecessary data is one simple way to get more storage space. Nevertheless, the potential need for particular data in the future along with strict compliance requirements make this option improbable. In this case, archiving can help you keep the data in a database accessible while also saving storage space.
Below we mention a couple of workflows that might be suitable to organize your Azure database archiving. For example, in case you need to keep the data in the same database, consider moving that data to a separate table. Additionally, ensure that the table’s firegroup is separated from the main database. If possible, move that table to a distinguished disk.
You’ll most likely need to provide users with access to that separated data. Creating a view merging the relevant tables and then directing requests to that merged view instead of the original table can be a solution. This allows you to save time on maintenance without sacrificing data accessibility.
In case moving the data to a different database is not an option for your organization, primary database maintenance is another consideration. This recommendation is nearly similar to the previous example, but in this case you should link the SQL Servers or configure applications to access the second server for direct requests. However, this isn’t a well-playing choice as you turn the secondary “backup” SQL database into an equally important production database.
In certain cases, you don’t need to keep the database data available online 24/7 but still have to store that data for corporate or compliance purposes. When that is your situation, simply back up such data, ensure that it is recoverable and then delete that data from the production database. If necessary, you can use the backup to access the required records.
In SQL Server 2016, Microsoft came up with a Stretch Database feature to simplify data management. The feature enables you to back up SQL Server to Azure by sending the data from the hosted database to an Azure SQL Database. Thus, you can save costs on storage while at the same time excluding infrastructure factors.
To implement this data moving workflow in your infrastructure, come up with an appropriate policy, which should define which data records to move, on a hosted server. No changes are required for apps using the main database: SQL Server can fetch the data from the Azure SQL Database independently.
How to Verify and Restore Azure Backups
When you create an SQL Azure database backup, the choice to create that backup WITH CHECKSUMS or without them is available. On the backup workflow completion window, the RESTORE VERIFYONLY command can be used to check the backup file’s recoverability. This step is not obligatory but strongly recommended.
A backup can be restored to a different database to access the data. Although the process seems unnecessary and lengthy, Azure Automation scripts set to run on every backup can solve related issues.
Restoring an Azure SQL Database from a backup to a required point in time takes only several clicks. Still, remember that the retention period depends on your subscription and can reach 7 or 35 days. SQL Server Management Studio is the regular tool to restore backups to your SQL Server.
Conclusion
The development of cloud computing services make creating reliable backups based on a redundant infrastructure much simpler and more affordable compared to on-premise backups. Still, with databases migrating from on-site storage to a public cloud, backup consistency and protection are critical. Azure Blob Storage, Stretch Database and Site Recovery are features that can offload your IT department and streamline SQL Azure database backup workflows. This allows you to save costs and enhance database data resilience.
To push the efficiency of Azure database backup workflows further, consider integrating a specialized data protection solution such as NAKIVO enterprise backup or NAKIVO small business backup solution. With this solution, you can automatically back up physical, virtual, cloud workloads and SaaS data on premises or directly to cloud storage ( Azure Blob, Amazon S3, Wasabi, Backblaze B2). Install the NAKIVO solution Free Edition and access all the basic features for an entire year.
