Configure SQL Database on premises with DB files on Azure

Requirements

  • SQL Server 2014 or SQL Server 2016 installed on premises
  • Azure Storage Account
  • Azure Storage Container
  • Azure Storage Explorer
  • Cloudbery Explorer for Azure Blob Storage
  • Shared Access Signature
  • Create a credential to Access Azure Storage using Shared Access Signature

Benefits

  • Costs
  • Limitless Storage
  • BCP/DR
  • Security

Steps

Configure Shared Access Signature

  • Download and Install Azure Storage Explorer
  • Open Azure Storage Explorer
  • Click on Add Account
  • Specify the storage account name
  • Specify your storage account key
  • Test access and save your account
  • Connect to Azure Storage using Azure Storage Explorer
  • Expand Blob Containers
  • Select the container where do you want to save your database files
  • Click on Security Icon at the top of Storage Account
  • Select Shared Access Signature
  • Configure Access using a date range. Access permitted from X to Y.
  • Grant List,Delete,Read and Write permissions to Blob Container.
  • Your Shared Access Signature will look as bellow
https://yourstorrageaccount.blob.core.windows.net/yourcontainer?sv=date&sr=c&sig=signature&st=date%3code&se=dateT22%3code&sp=rdl
  • Copy the part from ? till the end.Example
sv=date&sr=c&sig=signature&st=date%3code&se=dateT22%3code&sp=rdl

Create a credential to Access Azure Storage using Shared Access Signature

  • Connect to your SQL Server Instance configured on premises using SQL Server Management Studio.
  • Open a new TSQL query to master database
  • Create a credential using follow line of code
USE [master]
GO
CREATE CREDENTIAL [https://storageaccount.blob.core.windows.net/storagecontainer]
WITH IDENTITY = N’SHARED ACCESS SIGNATURE’,
SECRET = N’sv=date&sr=c&sig=signature&st=date%3code&se=dateT22%3code&sp=rdl’ –use your Shared Access Signature
GO

Create a New Database on SQL Server Instance configured on premises with database files on azure

CREATE DATABASE AzureDB
ON
( NAME = AzureDB_data,
FILENAME = ‘https://storageaccount.blob.core.windows.net/storagecontainer/AzureDB.mdf’ )
LOG ON
( NAME = AzureDB_log,
FILENAME = ‘https://storageaccount.blob.core.windows.net/storagecontainer/AzureDB.ldf’)

Move an existing database from on premises storage to Azure Storage

  • Use alter database TSQL syntax in order to specify a new location for existing database files
ALTER DATABASE PremisesDB MODIFY FILE ( NAME = ‘PremisesDB’, FILENAME = ‘https:// storageaccount.blob.core.windows.net/storagecontainer/PremisesDB.mdf’ );
ALTER DATABASE PremisesDB MODIFY FILE ( NAME = ‘PremisesDB_log’, FILENAME = ‘https:// storageaccount.blob.core.windows.net/storagecontainer/PremisesDB_log.ldf’ );
The file “PremisesDB” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “PremisesDB_log” has been modified in the system catalog. The new path will be used the next time the database is started.
  • Download and Install Cloudbery Explorer for Azure Blob Storage
  • Open Cloudbery Explorer for Azure Blob Storage.
  • Select Azure Blob from Select Cloud Storage list.
  • Specify a name for your connection.
  • Specify you Azure storage account name.
  • Specify you Shared key and select Azure from Account type dropdown.
  • Test connection and click ok to save.
  • Take database offline
  • Upload Database files from on premises storage to Azure Storage by using Cloudbery Explorer for Azure Blob Storage. (Copy as Page Blob option should be used)
  • Bring Database online