Creating data files in Azure Blob Storage for SQL Server database with Powershell

Storing SQL Server database files in Azure blob storage is a great solution for all the databases that are often migrated between instances, servers, virtual machines, or would have been divided between instances. This scenario also has the positive aspect to it, since the ability to create snapshot backups to Azure is seamless.

Following the steps, we will create a Azure Blob storage, where MSSQL Server database files will reside with MSSQL Server running on-prem. Assuming, that you already have the Azure account (if not, you can get a free Azure account), let’s proceed by opening the Windows Terminal in PowerShell mode.

Create resource group

First we will login using Powershell to your account, following by creating a Resource group, so all of the resources will be grouped together.

Connect-AzureRmAccount -SubscriptionId 'yyyyy-yyyyy-yyyyyy-yyyyyyyy'

After connecting, you will receive a return message on your subscription, tenantID and environment:

Once you are connected, you can proceed with creating a resource group:

New-AzureRmResourceGroup -Name DataFilesSQLServer -Location "North Europe" -Tag @{Name="DataFilesSQLServer"; Purpose="UploadingMSSQLDataFiles"; Author="Tomaz xxxx"}

You will be prompted on creation of the resource group:

You can always check your Azure Portal to check on the progress.

Create storage account

Quick powershell script to create a Azure blob storage account with a) selected and previously created resource group, b) location in north Europe c) Standard_LRS as locally redundant storage, d) cool access tier and some tags for the service.

New-AzureRmStorageAccount -ResourceGroupName 'DataFilesSQLServer' -AccountName storaccdatafilesmssql  -Location 'northeurope'  -SkuName Standard_LRS  -Kind BlobStorage  -AccessTier Cool -Tag @{Name="StorageaccountBlob"; Purpose="StoringDatabaseFiles"; Author="Tomaz xxxx"}

Receiving back the response from Azure:

Getting credentials for the blob container

Within the blob storage, we will create a blob container and for this container get the credentials as a “Shared access signature”. Create The blob container

New-AzureRmStorageContainer -ResourceGroupName 'DataFilesSQLServer' -AccountName storaccdatafilesmssql2 -ContainerName mymssqlfiles -Tag @{Name="Blob Storage container";Purpose="Storing mdf and ldf files";Author="Tomaz Kastrun"}

Checking the storage in Microsoft Azure Storage explorer, you can see in left pane, how the storage is progressing:

Getting the Shared access signature using Powershell. We need to provide an Azure Storage Context in order to receive the SAS token:

$storageAccountName = storaccdatafilesmssql2
$accountKeys = Get-AzureRmStorageAccountKey -ResourceGroupName DataFilesSQLServer -Name $storageAccountName
$storageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $accountKeys[0].Value 

$now=get-date
New-AzureStorageContainerSASToken -Name mymssqlfiles -Context $storageContext -Permission rwdl -StartTime $now.AddHours(-1) -ExpiryTime $now.AddMonths(1)

And the output will be the secret key, that will be valid for one month and it will be used for the connecting on-prem SQL Server to Azure blob storage database files.

With SAS token generated, we can now proceed to SQL Server Management studio and create the credentials, new database and store files in Azure. Mark in red are timestamps and permissions (rwdl) as described in PowerShell code.

Creating SQL Server database on on-prem SQL Server

A simple T-SQL script will create a credentials and a database with a sample table:

CREATE CREDENTIAL [https://nameofyourstorage.blob.core.windows.net/mymssqlfiles] 
WITH
IDENTITY = 'SHARED ACCESS SIGNATURE', 
SECRET = 'sv=xxxxxxxxxxxxinputyoursecretxxxxxxxxxx'
GO


CREATE DATABASE [myMSSQLinAzure]
ON (
NAME = 'mymssqlinazuredata',
FILENAME = 'https://nameofyourstorage.blob.core.windows.net/mymssqlfiles/mymssqlinazure.mdf'
)
LOG ON (
NAME = 'mymssqlinazurelog',
FILENAME = 'https://nameofyourstorage.blob.core.windows.net/mymssqlfiles/mymssqlinazure.ldf'
);
GO



USE [myMSSQLinAzure]
GO

CREATE TABLE dbo.sampleData
(ID INT IDENTITY(1,1)
,someText VARCHAR(200)
,someNmr INT
)

INSERT INTO dbo.sampleData (someText, someNmr)
 		  SELECT 'adding Table to Azure Storage', 10
UNION ALL SELECT 'adding files to Blob storage', 100

SELECT * FROM dbo.sampleData

You can query the database as if all the data files would have have been on-prem. By cheching the Azure Storage explorer we can see the files:

Upon checking the properties of the database, we can see that the files reside in Azure blob storage:

As always, code is available on Github.

 

‹ Quine with R

Little useless-useful R function ›

Tagged with: Azure, azure blob storage, Powershell, SQL, SQL Server
Posted in Uncategorized