Course Title



Fast Start for Microsoft Azure - SQL Server IaaS Workshop Section 6 Provision and Manage SQL Server Azure VMStudent Lab Manual Instructor Edition (Book Title Hidden Style)Version 1.0Conditions and Terms of UseThe 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. ? 2015 Microsoft Corporation. All rights reserved.Copyright and Trademarks? 2015 Microsoft Corporation. All rights reserved.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 plying 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?, 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.Contents TOC \o "1-3" \h \z \u HYPERLINK \l "_Toc464154324" Lab 1: SQL Server Deployment using ARM Model PAGEREF _Toc464154324 \h 6Exercise 1: Deploy SQL Server Azure VM from Template PAGEREF _Toc464154325 \h 6Lab 2: Connect to SQL Server IaaS instance from public internet PAGEREF _Toc464154326 \h 15Configure the firewall PAGEREF _Toc464154327 \h 15Create a SQL Login Test with sysadmin rights PAGEREF _Toc464154328 \h 15Configure Network Security Group inbound rule for VM PAGEREF _Toc464154329 \h 15Configure a DNS Label for the public IP address PAGEREF _Toc464154330 \h 17Configure TDE using Azure Vault PAGEREF _Toc464154331 \h 18Install Azure Powershell on Workstation PAGEREF _Toc464154332 \h 18Create an Azure Vault and Key PAGEREF _Toc464154333 \h 18Create an Application in Azure AD and configure the ClientID PAGEREF _Toc464154334 \h 19Map the Client with the Vault PAGEREF _Toc464154335 \h 19Install and Enable EKM PAGEREF _Toc464154336 \h 19Configure Transparent Data Encryption Using TDE PAGEREF _Toc464154337 \h 20Lab 1: SQL Server Deployment using ARM Model Introduction This lab provides guidance on how to perform how to provision an Azure virtual machine in the portal using Azure Resource Manager model and configure SQL Server from a template in the Azure gallery.Objectives After completing this lab, you will be able to:Deploy SQL Server VM using ARM ModelEstimated time to complete this lab 45 minutesExercise 1: Deploy SQL Server Azure VM from Template Objectives In this exercise, a SQL Server VM from the Azure gallery using the latest ARM model.Task 1: Create Azure Microsoft Storage ObjectsAccess the Microsoft Azure portal()Log in to the Microsoft Azure Management Portal using your account. If you do not have a Microsoft Azure account, visit Microsoft Azure 3-Month free trial.The next step is to spin a new Azure Virtual Machine There are various template that is available in marketplace. You can use one of the templates from the gallery and build the SQL Server VM.Go to New Compute and Select SQL Server 2014 Enteprise Edition on Windows Server 2012 R2.Ensure that Resouce Manager is selected under the Select a deployment model section and click create. In the portal, there are several confirguration options as show in the image below.The option are related to size, type , server and SQL Server configutaion.The first option is basic and it has few mandatory fields. The following configuration paramaters that can passed are Name : A unique server\VM name. Let’s name the server TestVMUser Name: Enter Testadmin in the Username section. This will be a administrator account in the VMPassword : A strong password for authentication for the admin accountSubscription : The azure subscription that will be used for this VM. The billing and charges will be applied to this subscription. Note: One account can have multiple subscriptionResource Group: Provide the resource group name as TESTRG1.You can specify a resource group name or use an existing one.Resource group is a collection of related services in Azure. Location : The region or data center that will host the VM. Select the location that is closet to your region.Under Size, choose a virtual machine size. The Azure portal will display recommended sizes. You can select “View All” to choose a VM size apart from the recommended sizes.The portal displays the capacity of each of the listed VM such as CPU, Memory etc., features that are supported and estimates the monthly cost to run the VM. Select “View All” and choose DS2 size.On the Create Virtual Machine blade under Settings, configure Azure storage, networking and monitoring for the virtual machine.Storage: Specify a disk type. Premium storage is recommended for production workloads. So, go ahead and select Premium Storage account: You can either accept the automatically provisioned storage account name, or you can click on Storage account to choose an existing account and configure the storage account type. In this lab, provide a unique storage account name.By default, Azure creates a new storage account with locally redundant storage and it maintains three copy in the local site. It is recommended to name the storage account that can be identifiableNetwork: Use the default values provided by AzureYou can accept the automatically populated values for features or click on each feature to configure the Virtual network, Subnet, Public IP address, and Network Security Group. Monitoring: Azure enables monitoring by default with the same storage account designated for the VM. Currently, the monitoring data can be stored in standard storage only. Continue with a new storage account that is provided in the portal.Availability set specify an availability set. In this lab, we will set to none.Configure SQL Server SQL Connectivity: Choose the option Private (within Virtual Network)There are three possible options to configure SQL ConnectivityPublic: Specify Public (internet) to allow connections to SQL Server from machines or services on the internet. Local (inside VM only): To allow connections to SQL Server only from within the VM. Private (within Virtual Network) to allow connections to SQL Server from machines or services in the same virtual network.For SQL Server, usually the application \web server will connect to the SQL Server and it is not directly connected to the internet. Port: Continue with the default 1433. Authentication: The azure VM is configured with windows authentication only. You can enable SQL Authentication and provide a SQL Login and its password that will be created with sysadmin rights. Click Enabled and it will automatically populate the same user name and password that was provided in the Basic section.Storage Configuration Select your desired performance, storage size, and workload to optimize the storage on your virtual machine.Select the default Azure optimizes the storage for 5000 IOPs, 200 MBs, and 1 TB of storage space. Play with the sliders to understand how SQL Azure automatically dynamically calculates the values related to IOPS, Throughput and size.Under Storage optimized for, select Select Transactional workloadGeneral is the default setting and supports most workloads.Transactional processing optimizes the storage for traditional database OLTP workloads.Data warehousing optimizes the storage for analytic and reporting workloads.PatchingSQL automated patching is enabled by default. Automated patching allows Azure to automatically patch SQL Server and the operating system. Specify a day of the week, time, and duration for a maintenance window. Azure will perform patching in the maintenance window. The maintenance window schedule uses the VM locale for time.Backup:Enable automatic database backups for all databases under SQL automated backup. Set the backup retention to 7 days and rest to default.Key Vault IntegrationTo store security secrets in Azure for encryption. select disable for now. Review the settings and click OK to create the VM.It will take around around 15 minutes to create the Azure VM using the Resouce Model. Once the VM is created. You can connect using RDP to the Azure VM Lab 2: Connect to SQL Server IaaS instance from public internet Configure the firewallOpen windows firewall (wf.msc)Create an inbound windows firewall rule and allow 1433 port accessClick on Inbound Rules New RuleSelect the following settingsRule Type = PortProtocol and Ports: Protocol = TCP and Specific Local Port = 1433Action = Allow the ConnectionProfile = Domain, Public, PrivateName = SQLServerCreate a SQL Login Test with sysadmin rightsConfigure Network Security Group inbound rule for VMIn the portal, select?Virtual machines, and then select the SQL Server VM.Click the?All settings?link.Expand?Network interfaces.Then select the Network Interface for VM.1381125-2479675Select the network Select Network Security Group select Settings Inbound Security rulesAdd Inbound security rule 1009650115570Name: SQLServerPriority: 1500Source: AnyProtocol: TCPSource Port Range: *Destination: AnyDestination Port Range: 1433Action: Allow12954001238250Configure a DNS Label for the public IP addressIn the virtual machine blade, select your?Public IP address.In the properties for your Public IP address, expand?Configuration.127635013970Set the following DNS configurationAssignment: StaticDNS Name: Unique DNS Name140017520193000Connect to SQL Instance using <DNSLabel>.<DCname>.cloudapp.,1433 using the SQL Login with sysadmin rightsConfigure TDE using Azure VaultInstall Azure Powershell on Workstation From laptop\workstation, open the powershell ISE with run as administrator option and run the following command# Install the Azure Resource Manager modules from the PowerShell GalleryInstall-Module AzureRMInstall-AzureRM# Install the Azure Service Management module from the PowerShell GalleryInstall-Module Azure# Import AzureRM modules for the given version manifest in the AzureRM moduleImport-AzureRM# Import Azure Service Management moduleImport-Module AzureThis will take few minutes to complete the installation. Good time to take a break if you haven’t installed the module previously.Create an Azure Vault and Key Connect to Azure subscriptionLogin-AzureRmAccount Copy the SubscriptionID and replace the subscriptionIDSet-AzureRmContext -SubscriptionId XXXXXXNew-AzureRmKeyVault -VaultName 'TestVault123' -ResourceGroupName 'TestVault' -Location 'East US' This will create a AzureRM key Vault Testvault123. You can change vaultName, RG Name and Location as per your choice$key = Add-AzureKeyVaultKey -VaultName '<vault Name>' -Name 'TestKey123' -Destination 'Software'This will create a Key with name TestKey123. Get-AzureKeyVaultKey –VaultName '<vaultName>' Notedown the Vault URICreate an Application in Azure AD and configure the ClientIDLogin to Classic portal Go to Active Directory and select the default Azure Active DirectoryGo to the applications and click AddProvide the followingName: AzureVaultTestType: Web Application and/or Web APISIGN ID URL : Provide the Vault URIApp ID URI: Provide the vault URISelect the application AzureVaultTest and click the Configure TabUnder keys section, select duration key= 1 year and click saveNote down the clientID and keyMap the Client with the VaultMap the ClientID with vault Set-AzureRmKeyVaultAccessPolicy -VaultName 'testvault123' -ServicePrincipalName <CLientID> -PermissionsToKeys decrypt,sign,wrapKey,unwrapKey,get,list,create Replace the vault with name and ServicePrincipalName with ClientiDInstall and Enable EKM Install the EKM Connector Download the EKM Connector and install it on the Azure VM EKM in SQL Server USE master;GOsp_configure 'show advanced options', 1 ;GORECONFIGURE ;GO--Enable EKM providersp_configure 'EKM provider enabled', 1 ;GORECONFIGURE ;GOCreate the provider CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';Configure Transparent Data Encryption Using TDECreate a database Test Create Credential USE master;CREATE CREDENTIAL sysadmin_ekm_cred WITH IDENTITY = '<VaultName>', SECRET = ‘<Secret>' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;The secret is the clientID appended with key without the hypen Add the credential to the SQL Server administrator's domain login ALTER LOGIN [<installation account>]ADD CREDENTIAL sysadmin_ekm_cred;GO Create Asymmetric Key CREATE ASYMMETRIC KEY CONTOSO_KEY FROM PROVIDER [AzureKeyVault_EKM_Prov]WITH PROVIDER_KEY_NAME = 'TestKey123',CREATION_DISPOSITION = OPEN_EXISTING;Enable Transparent data encryptionUSE master;CREATE CREDENTIAL Azure_EKM_TDE_cred WITH IDENTITY = 'testvault123', SECRET = ‘<Secret>' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;The secret is the clientID appended with key without the hypenUSE master;-- Create a SQL Server login associated with the asymmetric key -- for the Database engine to use when it loads a database -- encrypted by TDE.CREATE LOGIN TDE_Login FROM ASYMMETRIC KEY CONTOSO_KEY;GO -- Alter the TDE Login to add the credential for use by the -- Database Engine to access the key vaultALTER LOGIN TDE_Login ADD CREDENTIAL Azure_EKM_TDE_cred ;GOCREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER ASYMMETRIC KEY CONTOSO_KEY;GO-- Alter the database to enable transparent data encryption.ALTER DATABASE TestSET ENCRYPTION ON ;GOselect * from sys.dm_database_encryption_keys ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download