Connection resiliency is used to describe a scenario where some resource becomes unavailable for short period of time, if we try to connect that resource like SQL Server during that time our connection should not throw exception instead it should retry.
Sometime we need to fetch the information about the tables defined in an SQL Server database, we can fetch this information ...
- Download and install the latest version of Microsoft SQL Server Management Studio, old version can give unexpected errors as now Azure SQL databases have almost same set of features as on-premises databases can have.
- Right click on
- Connect to master database
We planned to use uniqueidentifier as data type for ID columns instead of int (auto identity).
But when we looked at reviews a lot of people were against this idea, they said if we use uniqueidentifier column as primary key (and clustered index) then there will be lot of fragmentation in our database, also the extra size of key.
Some people suggested that use the int type (auto identity) as primary key and add another column of uniqueidentifier type for this purpose. We decided to go with this idea and added a uniqueidentifier column in each table.
We don’t see any issue in this design right now and hopefully it will work for long term.
In this article we will see how to take Microsoft SQL Server database backup to Azure storage. At high level, we have to do the following to achieve this:
- Create storage account in Microsoft Azure
- Create container in Azure storage account
- Create SQL Server credential using information from Microsoft Azure storage account
- Taking backup using Microsoft SQL Server Management Studio
- Taking backup using SQL code
1. Create storage account in Microsoft Azure
Login to Azure Portal. Create a storage account. To add a storage account, click on the Storage accounts on left and then click on +Add option on top.
It will show the following options, fill in all the details but remember to select General purpose under Account kind, it is circled in the image, otherwise it will give error while taking backup.
2. Create container in Azure storage account
Once storage account is created, we need to create a Container which will keep the database backup files. To add a container, click on Storage accounts, all the storage accounts in your Azure account will be shown. Click on the newly created storage account to open its properties. Click on Containers then on +Container, give the name and click OK as shown in the image below:
3. Create SQL Server credential using information from Microsoft Azure storage account
Use the following SQL code to create credential to use while taking backup.
use master; CREATE CREDENTIAL AzureBlobStorageCredential WITH IDENTITY = 'storageaccountname' , SECRET = 'storage account access key';
AzureBlobStorageCredential: Name of this credential, you can name it any.
IDENTITY: It is the Azure storage account’s name as shown in the image below.
SECRET: It is the storage account’s access key as shown in the image below.
4. Taking backup using Microsoft SQL Server Management Studio
Right click on the desired database, click on Tasks and the click on Back Up…
Make the selections/changes on Back Up Database dialog as shown below:
5. Taking backup using SQL code
Use the following SQL code to take database back up to Azure Storage
Declare @BackupUrl AS NVARCHAR(max) = N'https://stoarageaccountname.blob.core.windows.net/containername/backupfilename.bak';