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 J

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

Please Consider Subscribing

Subscribe

Leave a Reply