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.

 

Tagged with: , , , ,
Posted in Uncategorized
2 comments on “Creating data files in Azure Blob Storage for SQL Server database with Powershell
  1. Eitan Blumin says:

    Thanks for this!

    It would probably be useful to also have some kind of key rotation script in order to renew the SAS key in Azure and then update the credentials in SQL Server.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories
Follow TomazTsql on WordPress.com
Programs I Use: SQL Search
Programs I Use: R Studio
Programs I Use: Plan Explorer
Programs I use: Scraper API
Rdeči Noski – Charity

Rdeči noski

100% of donations made here go to charity, no deductions, no fees. For CLOWNDOCTORS - encouraging more joy and happiness to children staying in hospitals (http://www.rednoses.eu/red-noses-organisations/slovenia/)

€2.00

Top SQL Server Bloggers 2018
TomazTsql

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Discover

A daily selection of the best content published on WordPress, collected for you by humans who love to read.

Revolutions

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

tenbulls.co.uk

tenbulls.co.uk - attaining enlightenment with the Microsoft Data and Cloud Platforms with a sprinkling of Open Source and supporting technologies!

SQL DBA with A Beard

He's a SQL DBA and he has a beard

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Search Msdn

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

R-bloggers

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World

R-bloggers

R news and tutorials contributed by hundreds of R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

DevOps could be the disease you die with, but don’t die of.

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.

%d bloggers like this: