Section 2 SQL Server and Microsoft Azure VMs

Section 2: SQL Server and Microsoft Azure VMs (Iaa. S) Lesson 1: Deploying SQL Server in Azure VMs 1

Different ways of deploying SQL Server in Azure VM Gallery • Using images available in Azure • Specialized images available Bring your Own Server/Virtual Hard Disk (VHD) • Capture Cloud Images • SQL Server licensing • Pay by the hour or migrate your own license via Software Assurance* * Windows Azure Compute and Storage charges also apply 3

Azure VM Sizes 4

Azure VM Sizes 5

Azure VM Sizes 6

Azure VM Sizes 7

Using Gallery to host SQL Server in Azure VM Supported SQL Server and Windows Server versions • SQL Server 2008 R 2 – Windows Server 2008 R 2 • SQL Server 2012 – Windows Server 2012 and Windows Server 2012 R 2 • SQL Server 2014 – Windows Server 2012 R 2 • Includes SQL Server Integration Services, Reporting Services, and Analysis Services Optimized images available for OLTP and Data Warehouse workloads 8

Bring your own image of SQL Server in Azure VM Install Windows Server in a virtual machine (on-premises or in Azure) Run SQL Server setup to prepare an image-based installation • /Action=Prepare. Image In order to trigger completion of the setup once a new virtual machine based on the image is deployed • /ACTION=Complete. Image in setupcomplete 2. cmd file Execute the sysprep command line utility with the generalize and shutdown switches Capture the image and transfer it to Azure blob storage to be used as your personal template Use this custom image in Azure • During its initialization this image will complete the SQL Server setup process in the desired manner 9

Lesson Knowledge Check Question: What are the different ways to deploy SQL Server in Azure VMs? Answer: Use Gallery images or BYOD Question: What versions of SQL Server can be created on Windows Azure ? Answer: SQL Server 2008 R 2 and later 10

Section 2: SQL Server and Microsoft Azure VMs (Iaa. S) Lesson 2: New Deployment enhancements Deploy SQL Virtual Machine under Azure Resource Manager 11

Deployment Model • Two Different deployment model for creating and managing resource • Classic • Resource Manager • Microsoft recommends that you use Resource Manager for new resources, and, if possible, re-deploy existing resources through Resource Manager. 12

Deploy SQL VM 14

Deploy SQL VM – Basics and Sizes Show all VM sizes 15

Deploy SQL VM – Features and Settings 16

SQL Server Setting – SQL Connectivity • Public : Allow connections to SQL Server from machines or services on the internet. Azure will automatically configure the firewall and the network security group to allow traffic on port 1433 • Local : To allow connections to SQL Server only from within the VM. • Private : To allow connections to SQL Server from machines or services in the same virtual network. 17

SQL Server Setting – SQL Port 18

SQL Server Setting – SQL Authentication Enable/Disable SQL Authentication 19

SQL Server Setting – Storage Configuration Storage optimized for: • General 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. 20

SQL Server Setting – Automated Patching is on by default • Simplify DBA maintenance activity • The maintenance window schedule uses the VM locale for time. 21

SQL Server Setting – Automated Backup is off by default 22

SQL Server Setting – Azure Vault Integration Store security secrets in Azure for encryption • Key Vault URL: The location of the key vault. • AKV Principal Name: Azure Active Directory service principal name. This is also referred to as the Client ID. • AKV Principal Secret: AKV Integration creates a credential within SQL Server, allowing the VM to have access to the key vault. Choose a name for this credential • Credential name: Choose a name to identify this credential. 23

Demonstration: Provision SQL Server from Gallery 24

Lesson Knowledge Check Question: What is the risk of using public connectivity? Answer: DB Server is exposed via web server and it is only accessible through app/web server. Also, internet facing DB server will incur egress charges Question: What are the pre-requisites for configuring automated backup The database should be full recovery model A standard storage account should be provided 25

Section 2: SQL Server and Microsoft Azure VMs (Iaa. S) Lesson 3: Different scenarios for using SQL Server in Azure VM SQL Server Workloads 26

Develop and test new apps Low TCO for Existing Apps TEST Full SQL Server Capability Virtual Machine DEVELOP SQL Server Data Tools Flexibility & Control DEPLOY On-Prem 27 Managed Infrastructure 27

Develop and test new apps (contd. ) PROVISION Provision new Microsoft Azure VM based on SQL Server template using Microsoft Azure Portal: DEVELOP Develop new applications using SQL Server Data Tools and Visual Studio Create deployment scripts using SQL Server Data Tools and Visual Studio DEPLOY Configure access using Microsoft Azure Portal and Windows Firewall in the VM MANAGE Monitor application over time using Microsoft Azure Portal and SQL Server Management Studio Deploy and test package to Microsoft Azure VM using SQL Server Data Tools and Visual Studio 28 28

Extend on-prem apps to Microsoft Azure Virtual Machine Microsoft Azure SQL Server in VM Microsoft Azure Virtual Network ON-PREM Note : On-premises business application can access SQL Server instance deployed in Microsoft Azure VMs Connect Business App SQL Server 29

Demonstration: Connect an on-premises business app to SQL Server in Azure VM 30

Move SQL Server and App to Microsoft Azure VM Target Scenarios Resource Management Dynamic Scaling High Availability and Durability High Available Services Microsoft Azure Periodic Workloads Connect Business App SQL Server in VM Unpredictable Growth Workload Spikes Flexibility & Control Low TCO for Existing Apps Managed Infrastructure Full SQL Server Capability Infrastructure Offloading

Lesson Knowledge Check Question: Which feature in Azure helps in extending your datacenter into the cloud? Answer: Virtual Network Question: What does dynamic scaling mean? Answer: Capability to both scale out and scale back your application depending on resource requirements 32

Section 2: SQL Server and Microsoft Azure VMs (Iaa. S) Lesson 4: How to migrate on. Different migration options premises SQL Server to Azure VM 33

Backup and restore in cloud Low TCO for Existing Apps RESTORE IN AZURE VIRTUAL MACHINE Microsoft Azure DIRECT URL BACKUP TO AZURE BLOB STORAGE Full SQL Server Capability SQL Server Management Studio SQL Server in VM On-Prem SQL Server Note : SQL Server 2005, 2008/2008 R 2 backup is done on file system and copied to Azure blob storage. Then the backup is restored within SQL Server instance on Microsoft Azure VM Backup restore will be covered in detail in a later module Flexibility & Control Managed Infrastructure 34

Demonstration: Migrate existing SQL Database to Azure Iaa. S using backup/restore 35

Migrate using bacpac in SSMS

Migrate using bacpac Steps for migration: Export on-premises database as bacpac to Azure blob storage using SSMS Create an Azure VM with SQL Server Import the bacpac file in Azure using SSMS 38

Physical to Virtual and Virtual to Virtual(P 2 V and V 2 V) P 2 V Non-Virtualized Windows Server Hyper-V VHD OPTION 2 Database Virtual Machine Full SQL Server Capability V 2 V VHD OPTION 1 Low TCO for Existing Apps Flexibility & Control Virtualized Managed Infrastructure 39 39

Lesson Knowledge Check Question: From which release of SQL Server backups to Azure were introduced Answer: SQL Server 2012 SP 1 CU 2 Question: What options are available to move existing SQL Servers to Azure? Answer: the database to the Microsoft Azure Virtual Machine or alternatively move the entire on-premises VHD to the Microsoft Azure VM 40

Section 2: SQL Server and Microsoft Azure VMs (Iaa. S) Lesson 5: SQL Server and Microsoft Azure VMs (Iaa. S) Using on-premises SQL Server with Azure 41

SQL Server data files in Azure SQL Server 2014 and above

SQL Server data files in Azure - Advantages • Easy and fast database migration • Easily move data between on-premises and cloud environments without any application changes • Cost and limitless storage benefits • High availability and Disaster recovery benefits • If your server crashes you can re-create your database in a new machine quickly • Security benefits • Data file can be encrypted in Azure and can be decrypted only in your server

Steps for placing SQL Server data files in Azure Step 1: Create credential using following steps • You must create a policy on a container and also generate a shared access signature (SAS) key. • For each container used by a data or a log file, you must create a SQL Server Credential whose name matches the container path. • You must store the information regarding Windows Azure Storage container, its associated policy name, and SAS key in the SQL Server credential store. -- Create a credential CREATE CREDENTIAL [https: //testdb. blob. core. windows. net/data] WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET = 'your SAS key'

Steps for placing SQL Server data files in Azure – contd. Step 2: Create a database with data and log files in Azure container CREATE DATABASE Test. DB 1 ON (NAME = Test. DB 1_data, FILENAME = 'https: //teststorageaccnt. blob. core. windows. net/testcontainer/Test. DB 1 Data. mdf') LOG ON (NAME = Test. DB 1_log, FILENAME = 'https: //teststorageaccnt. blob. core. windows. net/testcontainer/Test. DB 1 Log. ldf') GO

SQL Server data files in Azure: Security When creating a container set the access to private. In such cases, container and blob data can be read only by the Windows Azure account owner. By using a shared access signature, you enable SQL Server to access resources in your storage account without sharing your Windows Azure storage account key. In addition, continue implementing the traditional on-premises security practices for your databases.

Demonstration: Create database with data files in Azure 48

Lesson Knowledge Check Question: Which version of SQL Server supports creating database files on Azure Answer: SQL Server 2014 and above Question: What are the advantages of storing SQL Server data files in Azure Answer: Faster migration, cost and limitless storage, security benefits 49

50