In SQL Server Management Studio (SSMS), it is possible to connect to the Azure Storage. The Azure Storage Account is useful because it creates replicas automatically in the cloud. You only need to upload your file to the Azure Storage Account and the replication is automatic. You can have multiple replications in different regions and Datacenters with few clicks. That is why it is a common practice to Back up your local database to Azure.
Requirements
The following software is required:
- SSMS 17.2
- SQL Server installed
- An Azure Account
Getting started
We will first create an Azure Account and containers and then we will back up a local database in the Azure container and finally, we will restore the database.
Create an Azure Account and containers
We will create an Azure Account first and then we will connect to it. To create an Azure Storage Account, go to the Azure Portal. In the Azure Portal, press the + icon and write storage account. Select the Storage Account -blob file -Table -Queue. Specify a name for the storage account. In the deployment model, use the default one (Resource manager). In “Account kind” specify General purpose. In performance, we will use “Standard” because it is just a demo. In replication, the cheapest option is locally redundant storage. This option is used to replicate the information 3 times in the same region. The Subscription is important if you have multiple subscriptions. You can use an existing group or create a new one. You can also select your location. Press the create button. When the creation is completed, a green message is displayed. To check your Azure Account, go to more services and write storage to find Storage accounts. Click on Storage accounts. Click on the storage account just created. In the Azure Account, go to overview, click on Blobs. Click on the + Container to create containers. Create a container named backups or any name of your preference. Repeat the steps and create a new container named logs. In the Azure Account, in Access keys you will have two keys. Key1 and key2. You can use any of the keys to connect to your Azure Storage Account using SSMS.
Connect to Azure Storage Account using SSMS
In previous steps, we created an Azure Storage Account with 2 containers. Now, we will connect to the Storage Account using SSMS. In the SSMS, go to Connect and select Azure Storage. Specify the Azure Storage Account name created in the Azure Portal and the Account key. The account key can be the key1 or key2 that we saw in the Azure Portal in the Access Keys section. If everything is OK, you will be able to connect to your Azure Account.
Create a backup in the Azure Account
We will create a backup of a database on-premises into the Azure Account. Right click on your database and select Tasks>Back Up. In the General page, select Back Up to URL. This option will let you store your backup in the Azure Storage Account. Press the Add button. We will create a new container. Press the New container button. You will need to Sign in to Azure. Press the Signing In button and specify your Azure credentials. Once connected, you will be able to see your subscription. If you have multiple subscription, select the one used to create the Storage Account. Select the Azure Storage Account created in the portal and the container backups and press OK. You will be able to see the URL destination. Press OK to start the back up. If everything is OK, a successful message will be displayed. You can verify in the Azure Storage Account that the backup was successfully created in Azure.
Restore a database in SQL Server on-premises using a backup stored in an Azure Storage Account
Restoring a local database from the Azure Storage is a straightforward process in SSMS 17.2. To restore, right click the database that you want to restore and select Tasks>Restore>Database. The backups created are already detected. You only need to select the backup that you want to use and press OK. The database will be restored using the Azure Backup.
Conclusion
As you can see, now it is easier to interact with the Azure Storage Account and verify your files. Today, we may have SQL Server on-premises, but we also want to store some information in the Cloud. In this example, we show how to back up our database on-premises to Azure and how to verify the creation using SSMS.