by: jayanth.kurupPosted on:

Storing the MDF and LDF files of SQL Server on Azure Storage Part 2

In this second part on the series of how to add mdf and ldf files to Azure Storage we need to create a credential in the database in order to allows it access to the Azure storage space. Click here to read part 1

CREATE CREDENTIAL [https://mydatabase122.blob.core.windows.net/sqldata] – name OF the storage account
	WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
		,--- Identity
		SECRET = 'sv=2014-02-14&sQRSTUV0%3D&st=2015-01-21T18%3A30%3A00Z&se=2015-01-29T18%3A30%3A00Z&sp=rwd'

– Shared Signature created from Azure Storage Explorer notice that the entire signature is not used here only the part starting from “sv= ……”

Once the credential is created we simply go ahead and run the create database script only this time instead of pointing to a local path we point to the URI of the Azure blob Storage.

CREATE DATABASE cloudbd ON (
    name = clouddb
    ,Filename = 'https://mydatabase122.blob.core.windows.net/sqldata/clouddb.mdf'
    ) LOG ON (
    name = clouddb_log
    ,Filename = 'https://mydatabase122.blob.core.windows.net/sqldata/clouddb.ldf'
    )

Here is how it looks in SSMS

Here is how it looks in Azure storage explorer

Here is the comparison of execution time to insert 1 row on local and cloud data files

Local

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table ‘abcd’. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

Azure

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table ‘abcd’. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 443 ms.

(1 row(s) affected)

As you can see you won’t be using this feature for performance.

If your network goes down SSMS might still show the database but not all of the tables will be available and queries will just timeout

Please Consider Subscribing

Subscribe

Leave a Reply