Friday, October 10, 2014

1 SQL Server 2014 Cloud Enhancements (3) : Database file in Azure

SQL 2014 allow us to host the database file on the Azure. This is interestingly feature that basically allow database not restricted on the local storage size.

Generate signature

To store the data in the Azure storage, we need to get the signature first.

We also need to setup the actions to allow write access.

image

The signature is the string after the question mark (?)

Create credentials & Database

Use the signature to create the credentials .

-- Create the credentials to access the Blob location
CREATE CREDENTIAL [https://sqlpanda001.blob.core.windows.net/data] 
WITH IDENTITY='SHARED ACCESS SIGNATURE', 
SECRET = 'sv=2014-02-14&sr=c&sig=2CBINvq5%2FsGlKDpRzzPXDuSM%2Fu5RqRrPOtIiKdtM5T0%3D&st=2014-10-10T16%3A00%3A00Z&se=2014-10-18T16%3A00%3A00Z&sp=rw'
 
--use master
--go
--select * from sys.credentials
--go
--DROP CREDENTIAL [https://sqlpanda001.blob.core.windows.net/data] 
 
-- Let us create the database
 
CREATE DATABASE AzureDB2 
ON 
(NAME = AzureDB2_Data, FILENAME = 'https://sqlpanda001.blob.core.windows.net/data/AzureDB2.mdf') 
LOG ON 
(NAME = AzureDB2_log,     FILENAME = 'https://sqlpanda001.blob.core.windows.net/data/AzureDB2.ldf') 
 GO 

image

image

Error

If you don’t have credentials created, you will get below error.

Msg 5123, Level 16, State 10, Line 230
CREATE FILE encountered operating system error 86(The specified network password is not correct.)
while attempting to open or create the physical file '
https://sqlpanda001.blob.core.windows.net/data/AzureDB2.mdf'.
Msg 1802, Level 16, State 4, Line 230
CREATE DATABASE failed. Some file names listed could not be created. Check related errors

image

If the policy didn’t allow the write, it will get below error

image

Msg 5123, Level 16, State 10, Line 257
CREATE FILE encountered operating system error 1117
(The request could not be performed because of an I/O device error.) while attempting to open or create the physical file '
https://sqlpanda001.blob.core.windows.net/data/AzureDB2.mdf'.
Msg 1802, Level 16, State 4, Line 257
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

image

1 comments:

  1. nice information well done your information is helping alot thanks for valuable windows azure training in hyderabad

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates