Fast Start for Microsoft Azure - SQL Server Iaas Workshop

Fast Start for Microsoft Azure - SQL Server IaaS Workshop

Lesson 3 SQL Server with data files in Azure

Demo Manual

Instructor Edition (Book Title Hidden Style)

Conditions and Terms of Use

The contents of this package are for informational and training purposes only and are provided "as is" without warranty of any kind, whether express or implied, including but not limited to the implied warranties of merchantability, fitness for a particular purpose, and non-infringement.

Training package content, including URLs and other Internet Web site references, is subject to change without notice. Because Microsoft must respond to changing market conditions, the content should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. Unless otherwise noted, the companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.

Copyright and Trademarks

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

For more information, see Use of Microsoft Copyrighted Content at
https://www.microsoft.com/en-in/legal/Copyright/Default.aspx

Microsoft®, Internet Explorer®, and Windows® are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Other Microsoft products mentioned herein may be either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are property of their respective owners.

<Demo title> 7

Contents

Demo 1: SQL Server with files in Azure Blob storage 6

Exercise 1: Create on-premises SQL Server database with files in Azure Blob storage 6

Demo 1: SQL Server with files in Azure Blob storage

Introduction

This demo provides guidance on how to have On-Premises SQL Server with files in Azure Blob storage.

Objectives

After completing this demo, you will be able to:

· Have an on-premises SQL Server with files in Azure Blob storage.

Scenario

As you learnt in the lesson, there are various advantages for storing files in Azure Blob storage. In this demo we will look at the steps needed to create a on-premises SQL Server database with data and log files in Azure Blob storage.

Exercise 1: Create on-premises SQL Server database with files in Azure Blob storage

1. Create a container for your database with “Private” security access level, use lower case letters with Azure storage names, including blobs:

2. The next step is to create a policy and a Shared Access Signature (SAS) to protect and secure the container. There are several ways to create the policy and the SAS, using C# code or using REST APIs. We will use a GUI for doing this operation using a tool called “Azure Storage Explorer” (found here: http://azurestorageexplorer.codeplex.com). Once you download and open the tool, insert the name and master secret key of your storage account. Now select the container you want to place your database in, then click on the “Security” button:

On the “Shared Access Signatures” tab create a new policy with Read, Write, Delete and List rights and click on “Generate Signature” button. Copy and paste the signature URI string in a secure location for later reuse and exit the tool. Please note that the URI has been modified to xxxx to not display the actual URI for security reasons. The URI generated in your tool will be much longer.

3. Create a credential in your on-premises SQL Server that can access the blob storage. The credential stores the URI path of the storage container and the shared access signature key values. For each storage container used by a data or log file, you must create a SQL Server Credential whose name matches the container path, else SQL Server and Azure will not match the security information. For this demo, path of the container I created is https://sqlresource3364.blob.core.windows.net/dbfiles so that’s the credential name used and secret is the key which exists after the “?” in the signature URI shown in screenshot above. (The key is marked as xxx to avoid showing the actual key.) Notice Identity is always equal to ‘Shared Access Signature’.

CREATE CREDENTIAL [https://sqlresource3364.blob.core.windows.net/dbfiles]

WITH IDENTITY = 'SHARED ACCESS SIGNATURE',

SECRET = 'sv=2014-02-14&srxxxx'

Credential can be created using a GUI as well:

4. Now a database can be created with files placed in the container in blob storage as follows:

CREATE DATABASE DBInAzure

( NAME = testdb_dat,

FILENAME = N'https://sqlresource3364.blob.core.windows.net/dbfiles/Mydbfileaz.mdf')

LOG ON

( NAME = testdb_log,

FILENAME = N'https://sqlresource3364.blob.core.windows.net/dbfiles/Mydbfileaz_log.ldf')

Using these steps we created a SQL Server database on-premises with files residing in Azure Blob storage.