Migrating SQL databases to Azure - Microsoft Cloud Workshop

Microsoft Cloud Workshop Microsoft Cloud Workshop on Jun 01, 2019

In this hands-on lab, you will implement a proof-of-concept (PoC) for migrating an on-premises SQL Server 2008 R2 database into Azure SQL Database Managed Instance (SQL MI). You will perform assessments to reveal any feature parity and compatibility issues between the on-premises SQL Server 2008 R2 database and the managed database offerings in Azure. You will then migrate the customer's on-premises gamer information web application and database into Azure, with minimal to no down-time. Finally, you will enable some of the advanced SQL features available in SQL MI to improve security and performance in the customer's application.
At the end of this hands-on lab, you will be better able to implement a cloud migration solution for business-critical applications and databases.

Before the Hands-on Lab

Microsoft Cloud Workshops

Migrating SQL databases to Azure
Before the hands-on lab setup guide
June 2019

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example 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. 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.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any 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.

The names of manufacturers, products, or URLs are provided for informational purposes only and Microsoft makes no representations and warranties, either expressed, implied, or statutory, regarding these manufacturers or the use of the products with any Microsoft technologies. The inclusion of a manufacturer or product does not imply endorsement of Microsoft of the manufacturer or product. Links may be provided to third party sites. Such sites are not under the control of Microsoft and Microsoft is not responsible for the contents of any linked site or any link contained in a linked site, or any changes or updates to such sites. Microsoft is not responsible for webcasting or any other form of transmission received from any linked site. Microsoft is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement of Microsoft of the site or the products contained therein.

© 2019 Microsoft Corporation. All rights reserved.

Microsoft and the trademarks listed at https://www.microsoft.com/en-us/legal/intellectualproperty/Trademarks/Usage/General.aspx are trademarks of the Microsoft group of companies. All other trademarks are property of their respective owners.

Contents

Migrating SQL databases to Azure before the hands-on lab setup guide

Requirements

  1. Microsoft Azure subscription must be pay-as-you-go or MSDN.
    • Trial subscriptions will not work.
    • Rights to create an Azure Active Directory application and service principal and assign roles on your subscription.

Before the hands-on lab

Duration: 30 minutes

In this exercise, you will set up your environment for use in the rest of the hands-on lab. You should follow all steps provided before attending the Hands-on lab.

IMPORTANT: Many Azure resources require unique names. Throughout these steps you will see the word "SUFFIX" as part of resource names. You should replace this with your Microsoft alias, initials, or another value to ensure resources are uniquely named.

Task 1: Provision a resource group

In this task, you will create an Azure resource group which will serve as a container for the resources used throughout this lab.

  1. In the Azure portal, select Resource groups, select +Add, then enter the following in the Create an empty resource group blade:

    • Subscription: Select the subscription you are using for this hands-on lab.
    • Resource group: Enter hands-on-lab-SUFFIX.
    • Region: Select the region you would like to use for resources in this hands-on lab. Remember this location so you can use it for the other resources you'll provision throughout this lab.

    Add Resource group Resource groups is highlighted in the navigation pane of the Azure portal, +Add is highlighted in the Resource groups blade, and

  2. Select Review + Create.

  3. On the Review + Create tab, select Create to provision the resource group.

Task 2: Register the Microsoft DataMigration resource provider

In this task, you will register the Microsoft.DataMigration resource provider with your subscription in Azure.

  1. In the Azure portal, select All services from the Azure navigation pane, and then select Subscriptions.

    All services is highlighted in the Azure navigation pane, and Subscriptions is highlighted in the All services blade.

  2. Select the subscription you are using for this hands-on lab from the list, select Resource providers, enter "migration" into the filter box, and then select Register next to Microsoft.DataMigration.

    The Subscription blade is displayed, with Resource providers selected and highlighted under Settings. On the Resource providers blade, migration is entered into the filter box, and Register is highlighted next to Microsoft.DataMigration.

  3. It can take a couple of minutes for the registration to complete. Make sure you see a status of Registered before moving on.

    Registered is highlighted next to the Microsoft.DataMigration resource provider.

Task 3: Run ARM template to provision lab resources

In this task, you will run an Azure Resource Manager (ARM) template to deploy the resources required for this hands-on lab. The components will be deployed inside a new virtual network (VNet) to facilitate communication between the VMs and SQL MI. The ARM template also adds inbound and outbound security rules to the network security groups associated with SQL MI and the VMs, including opening port 3389 to allow RDP connections to the JumpBox. In addition to the provisioning of resources, the ARM template will also execute PowerShell scripts on each of the VMs to install software and configure the servers. The resources created by the ARM template include:

  • A virtual network with three subnets, ManagedInstance, Management, and a Gateway subnet
  • A virtual network gateway, associated with the Gateway subnet
  • A route table
  • Azure SQL Database Managed Instance (SQL MI), added to the ManagedInstance subnet
  • A JumpBox with Visual Studio 2019 Community edition and SQL Server Management Studio (SSMS installed, added to the Management subnet)
  • A SQL Server 2008 R2 VM with the Data Migration Assistant (DMA) installed, added to the Management subnet
  • Azure Database Migration Service (DMS)
  • Azure App Service Plan and App Service (Web App)
  • Azure Blob Storage account

Note: You can review the steps to manually provision the lab resources in Appendix A.

  1. Before running the ARM template, it is beneficial to quickly verify that you will be able to provision SQL MI in your subscription. In the Azure portal, select +Create a resource, enter "sql managed instance" into the Search the Marketplace box, and then select Azure SQL Managed Instance from the results.

    +Create a resource is selected in the Azure navigation pane, and

  2. Select Create on the Azure SQL Managed Instance blade.

    The Create button is highlighted on the Azure SQL Managed Instance blade.

  3. On the SQL managed instance blade, look for a message stating that "Managed instance creation is not available for the chosen subscription type...", which will be displayed near the bottom of the SQL managed instance blade.

    A message is displayed stating that SQL MI creation not available in the selected subscription.

    Note: If you see the message stating that Managed Instance creation is not available for the chosen subscription type, follow the instructions for obtaining a larger quota for SQL Managed Instance before proceeding with the following steps.

  4. You are now ready to begin the ARM template deployment. To open a custom deployment screen in the Azure portal, select the Deploy to Azure button below:

  5. On the custom deployment screen in the Azure portal, enter the following:

    • Subscription: Select the subscription you are using for this hands-on lab.
    • Resource group: Select the hands-on-lab-SUFFIX resource group from the dropdown list.
    • Location: Select the location you used for the hands-on-lab-SUFFIX resource group.
    • Managed Instance Name: Accept the default value, sqlmi. Note: The actual name must be globally unique, so a unique string will be generated from your Resource Group and appended to the name during provisioning.
    • Admin Username: Accept the default value, sqlmiuser.
    • Admin Password: Accept the default value, Password.1234567890.
    • V Cores: Accept the default value, 16.
    • Storage Size in GB: Accept the default value, 32.
    • Check the box to agree to the Azure Marketplace terms and conditions.

    The Custom deployment blade displays, and the information above is entered on the Custom deployment blade.

  6. Select Purchase to start provisioning the JumpBox VM and SQL Managed Instance.

    Note: The deployment of the custom ARM template can take over 6 hours due to the inclusion of SQL MI. However, the deployment of most of the resources will complete within a few minutes. The JumpBox and SQL Server 2008 R2 VMs should complete in about 15 minutes.

  7. You can monitor the progress of the deployment by navigating to the hands-on-lab-SUFFIX resource group in the Azure portal, and then selecting Deployments from the left-hand menu. The deployment will be named Microsoft.Template. Select that to view the progress of each item in the template.

    The Deployments menu item is selected in the left-hand menu of the hands-on-lab-SUFFIX resource group and the Microsoft.Template deployment is highlighted.

You have now completed the before the hands-on lab. Check back in a few hours to monitor the progress of your SQL MI provisioning. If the provisioning goes on for longer than 7 hours, you may need to issue a support ticket in the Azure portal to request the provisioning process be unblocked by Microsoft support.

You should follow all steps provided before attending the Hands-on lab.

Hands-on Lab Guide

Microsoft Cloud Workshops

Migrating SQL databases to Azure
Hands-on lab step-by-step guide
June 2019

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example 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. 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.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any 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.

The names of manufacturers, products, or URLs are provided for informational purposes only and Microsoft makes no representations and warranties, either expressed, implied, or statutory, regarding these manufacturers or the use of the products with any Microsoft technologies. The inclusion of a manufacturer or product does not imply endorsement of Microsoft of the manufacturer or product. Links may be provided to third party sites. Such sites are not under the control of Microsoft and Microsoft is not responsible for the contents of any linked site or any link contained in a linked site, or any changes or updates to such sites. Microsoft is not responsible for webcasting or any other form of transmission received from any linked site. Microsoft is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement of Microsoft of the site or the products contained therein.

© 2019 Microsoft Corporation. All rights reserved.

Microsoft and the trademarks listed at https://www.microsoft.com/en-us/legal/intellectualproperty/Trademarks/Usage/General.aspx are trademarks of the Microsoft group of companies. All other trademarks are property of their respective owners.

Contents

Migrating SQL databases to Azure hands-on lab step-by-step

Abstract and learning objectives

In this hands-on lab, you will implement a proof-of-concept (PoC) for migrating an on-premises SQL Server 2008 R2 database into Azure SQL Database Managed Instance (SQL MI). You will perform assessments to reveal any feature parity and compatibility issues between the on-premises SQL Server 2008 R2 database and the managed database offerings in Azure. You will then migrate the customer's on-premises gamer information web application and database into Azure, with minimal to no down-time. Finally, you will enable some of the advanced SQL features available in SQL MI to improve security and performance in the customer's application.

At the end of this hands-on lab, you will be better able to implement a cloud migration solution for business-critical applications and databases.

Overview

Tailspin Toys is the developer of several popular online video games. Founded in 2010, the company has experienced exponential growth since releasing the first installment of their most popular game franchise to include online multiplayer gameplay. They have since built upon this success by adding online capabilities to the majority of their game portfolio.

Adding online gameplay has greatly increased popularity of their games, but the rapid increase in demand for their services has made supporting the current setup problematic. To facilitate online gameplay, they host gaming services on-premises using rented hardware. For each game, their gaming services setup consists of three virtual machines running the gaming software and five game databases hosted on a single SQL Server 2008 R2 instance. In addition to the dedicated gaming VMs and databases, they also host authentication and gateway VMs and databases, which are shared by all their games. At its foundation, Tailspin Toys is a game development company, made up primarily of software developers. The few dedicated database and infrastructure resources they do have are struggling to keep up with their ever-increasing workload.

Tailspin Toys is hoping that migrating their services from on-premises to the cloud can help to alleviate some of their infrastructure management issues, while simultaneously helping them to refocus their efforts on delivering business value by releasing new and improved games. They are looking for a proof-of-concept (PoC) for migrating their gamer information web application and database into the cloud. They maintain their gamer information database, TailspinToys, on an on-premises SQL Server 2008 R2 database. This system is used by gamers to update their profiles, view leader boards, purchase game add-ons and more. Since this system helps to drive revenue, it is considered a business-critical application, and needs to be highly-available. They are aware that SQL Server 2008 R2 is approaching end of support, and are looking at options for migrating this database into Azure. They have read about some of the advanced security and performance tuning options that are available only in Azure and would prefer to a migrate the database into a platform-as-a-service (PaaS) offering, if possible. Tailspin Toys is using the Service Broker feature of SQL Server for messaging within the TailspinToys database. This functionality is being used for several critical processes, and they cannot afford to lose this capability when migrating their operations database to the cloud. They have also stated that, at this time, they do not have the resources to rearchitect the solution to use an alternative message broker.

Solution architecture

Below is a diagram of the solution architecture you will build in this lab. Please study this carefully, so you understand the whole of the solution as you are working on the various components.

This solution diagram includes a virtual network containing SQL MI in a isolated subnet, along with a JumpBox VM and Database Migration Service in a management subnet. The MI Subnet displays both the primary managed instance, along with a read-only replica, which is accessed by reports from the web app. The web app connects to SQL MI via a subnet gateway and point-to-site VPN. The web app is published to App Services using Visual Studio 2019. An online data migration is conducted from the on-premises SQL Server to SQL MI using the Azure Database Migration Service, which reads backup files from an SMB network share.

The solution begins with using the Microsoft Data Migration Assistant to perform assessments of feature parity and compatibility of the on-premises SQL Server 2008 R2 database against both Azure SQL Database (Azure SQL DB) and Azure SQL Database Managed Instance (SQL MI), with the goal of migrating the TailspinToys database into an Azure PaaS offering with minimal or no changes. After completing the assessments and reviewing the findings, the SQL Server 2008 R2 database is migrated into SQL MI using the Azure Database Migration Service's online data migration option. This allows the database to be migrated with little to no downtime, by using a backup and transaction logs stored in an SMB network share.

The web app is deployed to an Azure App Service Web App using Visual Studio 2019. Once the database has been migrated and cutover, the TailspinToysWeb application is configured to talk to the SQL MI VNet through a virtual network gateway using point-to-site VPN, and its connection strings are updated to point to the new SQL MI database.

In SQL MI, several features of Azure SQL Database are examined. Advanced Data Security (ADS) is enabled and Data Discovery and Classification is used to better understand the data and potential compliance issues with data in the database. The ADS Vulnerability Assessment is used to identify potential security vulnerabilities and issues in the database, and those finding are used to mitigate one finding by enabling Transparent Data Encryption in the database. Dynamic Data Masking (DDM) is used to prevent sensitive data from appearing when querying the database. Finally, Read Scale-out is used to point reports on the Tailspin Toys web app to a read-only secondary, allowing reporting to occur without impacting the performance of the primary database.

Requirements

  • Microsoft Azure subscription must be pay-as-you-go or MSDN.
    • Trial subscriptions will not work.
    • Rights to create an Azure Active Directory application and service principal and assign roles on your subscription.
  • A virtual machine configured with Visual Studio Community 2019 or higher (setup in the Before the hands-on lab exercises).

Exercise 1: Perform database assessments

Duration: 30 minutes

In this exercise, you will use the Microsoft Data Migration Assistant (DMA) to perform assessments on the TailspinToys database. You will create two assessments, one for a migration to Azure SQL Database, and then a second for SQL MI. These assessments will provide reports about any feature parity and compatibility issues between the on-premises database and the Azure managed SQL database service options.

DMA helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server. To learn more, read the Data Migration Assistant documentation.

Task 1: Restore the TailspinToys database on the SqlServer2008 VM

Before you begin the assessments, you need to restore a copy of the TailspinToys database in your SQL Server 2008 R2 instance. In this task, you will create an RDP connection to the SqlServer2008 VM and then restore the TailspinToys database onto the SQL Server 2008 R2 instance using a backup provided by Tailspin Toys.

  1. In the Azure portal, navigate to your SqlServer2008 VM by selecting Resource groups from the left-hand navigation menu, selecting the hands-on-lab-SUFFIX resource group, and selecting the SqlServer2008 VM from the list of resources. On the SqlServer2008 Virtual Machine's Overview blade, select Connect on the top menu.

    The SqlServer2008 VM blade is displayed, with the Connect button highlighted in the top menu.

  2. On the Connect to virtual machine blade, select Download RDP File, then open the downloaded RDP file.

  3. Select Connect on the Remote Desktop Connection dialog.

    In the Remote Desktop Connection Dialog Box, the Connect button is highlighted.

  4. Enter the following credentials when prompted, and then select OK:

    • Username: sqlmiuser
    • Password: Password.1234567890

    The credentials specified above are entered into the Enter your credentials dialog.

  5. Select Yes to connect, if prompted that the identity of the remote computer cannot be verified.

    In the Remote Desktop Connection dialog box, a warning states that the identity of the remote computer cannot be verified, and asks if you want to continue anyway. At the bottom, the Yes button is circled.

  6. Once logged into the SqlServer2008 VM, download a backup of the TailspinToys database, and save it to the C:\ of the VM.

  7. Next, open Microsoft SQL Server Management Studio 17 by entering "sql server" into the search bar in the Windows Start menu.

    SQL Server is entered into the Windows Start menu search box, and Microsoft SQL Server Management Studio 17 is highlighted in the search results.

  8. In the SSMS Connect to Server dialog, enter SQLSERVER2008 into the Server name box, ensure Windows Authentication is selected, and then select Connect.

    The SQL Server Connect to Search dialog is displayed, with SQLSERVER2008 entered into the Server name and Windows Authentication selected.

  9. Once connected, right-click Databases under SQLSERVER2008 in the Object Explorer, and then select Restore Database from the context menu.

    In the SSMS Object Explorer, the context menu for Databases is displayed and Restore Database is highlighted.

  10. You will now restore the TailspinToys database using the downloaded TailspinToys.bak file. On the General page of the Restore Database dialog, select Device under Source, and then select the Browse (...) button to the right of the Device box.

    Under Source in the Restore Database dialog, Device is selected and highlighted, and the Browse button is highlighted.

  11. In the Select backup devices dialog that appears, select Add.

    In the Select backup devices dialog, the Add button is highlighted.

  12. In the Locate Backup File dialog, browse to the location you saved the downloaded TailspinToys.bak file, select that file, and then select OK.

    In the Location Backup File dialog, the TailspinToys.bak file is selected and highlighted.

  13. Select OK on the Select backup devices dialog. This will return you to the Restore Database dialog. The dialog will now contain the information required to restore the TailspinToys database.

    The completed Restore Database dialog is displayed, with the TailSpinToys database specified as the target.

  14. Select OK to start the restore.

  15. Select OK in the dialog when the database restore is complete.

    A dialog is displayed with a message that the database TailspinToys was restored successfully.

  16. Next, you will execute a script in SSMS, which will reset the sa password, enable mixed mode authentication, enable Service broker, create the WorkshopUser account, and change the database recovery model to FULL. To create the script, open a new query window in SSMS by selecting New Query in the SSMS toolbar.

    The New Query button is highlighted in the SSMS toolbar.

  17. Copy and paste the SQL script below into the new query window:

    USE master;
    GO
    
    -- SET the sa password
    ALTER LOGIN [sa] WITH PASSWORD=N'Password.1234567980';
    GO
    
    -- Enable Service Broker on the database
    ALTER DATABASE TailspinToys SET ENABLE_BROKER WITH ROLLBACK immediate;
    GO
    
    -- Enable Mixed Mode Authentication
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2;
    GO
    
    -- Create a login and user named WorkshopUser
    CREATE LOGIN WorkshopUser WITH PASSWORD = N'Password.1234567890';
    GO
    
    EXEC sp_addsrvrolemember
        @loginame = N'WorkshopUser',
        @rolename = N'sysadmin';
    GO
    
    USE TailspinToys;
    GO
    
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'WorkshopUser')
    BEGIN
        CREATE USER [WorkshopUser] FOR LOGIN [WorkshopUser]
        EXEC sp_addrolemember N'db_datareader', N'WorkshopUser'
    END;
    GO
    
    -- Update the recovery model of the database to FULL
    ALTER DATABASE TailspinToys SET RECOVERY FULL;
    GO
    
  18. To run the script, select Execute from the SSMS toolbar.

    The Execute button is highlighted in the SSMS toolbar.

  19. For Mixed Mode Authentication and the new sa password to take effect, you must restart the SQL Server (MSSQLSERVER) Service on the SqlServer2008 VM. To do this, you can use SSMS. Right-click the SQLSERVER2008 instance in the SSMS Object Explorer, and then select Restart from the context menu.

    In the SSMS Object Explorer, the context menu for the SQLSERVER2008 instance is displayed, and Restart is highlighted.

  20. When prompted about restarting the MSSQLSERVER service, select Yes. The service will take a few seconds to restart.

    The Yes button is highlighted on the dialog asking if you are sure you want to restart the MSSQLSERVER service.

Task 2: Perform assessment for migration to Azure SQL Database

In this task, you will use the Microsoft Data Migration Assistant (DMA) to perform an assessment of the TailspinToys database against Azure SQL Database (Azure SQL DB). The assessment will provide a report about any feature parity and compatibility issues between the on-premises database and the Azure SQL DB service.

  1. On the SqlServer2008 VM, launch DMA from the Windows Start menu by typing "data migration" into the search bar, and then selecting Microsoft Data Migration Assistant in the search results.

    In the Windows Start menu,

  2. In the DMA dialog, select + from the left-hand menu to create a new project.

    The new project icon is highlighted in DMA.

  3. In the New project pane, set the following:

    • Project type: Select Assessment.
    • Project name: Enter ToAzureSqlDb.
    • Source server type: Select SQL Server.
    • Target server type: Select Azure SQL Database.

    New project settings for doing an assessment of a migration from SQL Server to Azure SQL Database.

  4. Select Create.

  5. On the Options screen, ensure Check database compatibility and Check feature parity are both checked, and then select Next.

    Check database compatibility and check feature parity are checked on the Options screen.

  6. On the Sources screen, enter the following into the Connect to a server dialog that appears on the right-hand side:

    • Server name: Enter SQLSERVER2008.
    • Authentication type: Select SQL Server Authentication.
    • Username: Enter WorkshopUser
    • Password: Enter Password.1234567890
    • Encrypt connection: Check this box.
    • Trust server certificate: Check this box.

    In the Connect to a server dialog, the values specified above are entered into the appropriate fields.

  7. Select Connect.

  8. On the Add sources dialog that appears next, check the box for TailspinToys and select Add.

    The TailspinToys box is checked on the Add sources dialog.

  9. Select Start Assessment.

    Start assessment

  10. Review the assessment of ability to migrate to Azure SQL DB.

    For a target platform of Azure SQL DB, feature parity shows two features which are not supported in Azure SQL DB. The Service broker feature is selected on the left and on the right Service Broker feature is not supported in Azure SQL Database is highlighted.

    The DMA assessment for a migrating the TailspinToys database to a target platform of Azure SQL DB shows two features in use which are not supported. These features, cross-database references and Service broker, will prevent TailspinToys from being able to migrate to the Azure SQL DB PaaS offering without first making changes to their database.

Task 3: Perform assessment for migration to Azure SQL Database Managed Instance

With one PaaS offering ruled out due to feature parity, you will now perform a second assessment. In this task, you will use DMA to perform an assessment of the TailspinToys database against Azure SQL Database Managed Instance (SQL MI). The assessment will provide a report about any feature parity and compatibility issues between the on-premises database and the SQL MI service.

  1. To get started, select + on the left-hand menu in DMA to create another new project.

    The new project icon is highlighted in DMA.

  2. In the New project pane, set the following:

    • Project type: Select Assessment.
    • Project name: Enter ToSqlMi.
    • Source server type: Select SQL Server.
    • Target server type: Select Azure SQL Database Managed Instance.

    New project settings for doing an assessment of a migration from SQL Server to Azure SQL Database Managed Instance.

  3. Select Create.

  4. On the Options screen, ensure Check database compatibility and Check feature parity are both checked, and then select Next.

    Check database compatibility and check feature parity are checked on the Options screen.

  5. On the Sources screen, enter the following into the Connect to a server dialog that appears on the right-hand side:

    • Server name: Enter SQLSERVER2008.
    • Authentication type: Select SQL Server Authentication.
    • Username: Enter WorkshopUser.
    • Password: Enter Password.1234567890.
    • Encrypt connection: Check this box.
    • Trust server certificate: Check this box.

    In the Connect to a server dialog, the values specified above are entered into the appropriate fields.

  6. Select Connect.

  7. On the Add sources dialog that appears next, check the box for TailspinToys and select Add.

    The TailspinToys box is checked on the Add sources dialog.

  8. Select Start Assessment.

    Start assessment

  9. Review the assessment of ability to migrate to Azure SQL Database Managed Instance.

    For a target platform of Azure SQL Database Managed Instance, feature parity with PowerShell job step is listed.

    Note: The assessment report for a migrating the TailspinToys database to a target platform of Azure SQL Database Managed Instance shows feature parity only with a PowerShell job step. The step listed is associated with a built-in SQL Server Agent Job, and it will not impact the migration of the TailspinToys database to SQL MI.

  10. The database, including the cross-database references and Service broker features, can be migrated as is, providing the opportunity for TailspinToys to have a fully managed PaaS database running in Azure. Previously, their options for migrating a database using features, such as Service Broker, incompatible with Azure SQL Database, were to deploy the database to a virtual machine running in Azure (IaaS) or modify their database and applications to not use the unsupported features. The introduction of Azure SQL MI, however, provides the ability to migrate databases into a managed Azure SQL database service with near 100% compatibility, including the features that prevented them from using Azure SQL Database.

Exercise 2: Migrate the database to SQL MI

Duration: 60 minutes

In this exercise, you will use the Azure Database Migration Service (DMS) to migrate the TailspinToys database from the on-premises SQL 2008 R2 database to SQL MI. Tailspin Toys mentioned the importance of their gamer information web application in driving revenue, so for this migration you will target the Business Critical service tier.

The Business Critical service tier is designed for business applications with the highest performance and high-availability (HA) requirements. To learn more, read the Managed Instance service tiers documentation.

Task 1: Create an SMB network share on the SqlServer2008 VM

In this task, you will create a new SMB network share on the SqlServer2008 VM. This will be the folder used by DMS for retrieving backups of the TailspinToys database during the database migration process.

  1. On the SqlServer2008 VM, open Windows Explorer by selecting its icon on the Windows Task bar.

    The Windows Explorer icon is highlighted in the Windows Task Bar.

  2. In the Windows Explorer window, expand Computer in the tree view, select Windows (C:), and then select New folder in the top menu.

    In Windows Explorer, Windows (C:) is selected under Computer in the left-hand tree view, and New folder is highlighted in the top menu.

  3. Name the new folder dms-backups, then right-click the folder and select Share with and Specific people in the context menu.

    In Windows Explorer, the context menu for the dms-backups folder is displayed, with Share with and Specific people highlighted.

  4. In the File Sharing dialog, ensure the sqlmiuser is listed with a Read/Write permission level, and then select Share.

    In the File Sharing dialog, the sqlmiuser is highlighted and assigned a permission level of Read/Write.

  5. In the Network discovery and file sharing dialog, select the default value of No, make the network that I am connected to a private network.

    In the Network discovery and file sharing dialog, No, make the network that I am connected to a private network is highlighted.

  6. Back on the File Sharing dialog, note the path of the shared folder, \\SQLSERVER2008\dms-backups, and select Done to complete the sharing process.

    The Done button is highlighted on the File Sharing dialog.

Task 2: Change MSSQLSERVER service to run under sqlmiuser account

In this task, you will use the SQL Server Configuration Manager to update the service account used by the SQL Server (MSSQLSERVER) to the sqlmiuser account. This is done to ensure the SQL Server service has the appropriate permissions to write backups to the shared folder.

  1. On your SqlServer2008 VM, select the Start menu, enter "sql configuration" into the search bar, and then select SQL Server Configuration Managed from the search results.

    In the Windows Start menu,

    Note: Be sure to choose SQL Server Configuration Manager, and not SQL Server 2017 Configuration Manager, which will not work for the installed SQL Server 2008 R2 database.

  2. In the SQL Server Configuration Managed dialog, select SQL Server Services from the tree view on the left, then right-click SQL Server (MSSQLSERVER) in the list of services and select Properties from the context menu.

    SQL Server Services is selected and highlighted in the tree view of SQL Server Configuration Manager. In the Services pane, SQL Server (MSSQLSERVER) is selected and highlighted. Properties is highlighted in the context menu.

  3. In the SQL Server (MSSQLSERVER) Properties dialog, select This account under Log on as, and enter the following:

    • Account name: sqlmiuser
    • Password: Password.1234567890

    In the SQL Server (MSSQLSERVER) Properties dialog, This account is selected under Log on as and the sqlmiuser account name and password are entered.

  4. Select OK.

  5. Select Yes in the Confirm Account Change dialog.

    The Yes button is highlighted in the Confirm Account Change dialog.

  6. You will now see the Log On As value for the SQL Server (MSSQLSERVER) service changed to ./sqlmiuser.

    In the list of SQL Server Services, the SQL Server (MSSQLSERVER) service is highlighted.

Task 3: Create backup of TailspinToys database

To perform online data migrations, DMS looks for backups and logs in the SMB shared backup folder on the source database server. In this task, you will create a backup of the TailspinToys database using SSMS, and write it to the SMB network share you created in the previous task. The backup file needs to include a checksum, so you will add that during the backup steps.

  1. On the SqlServer2008 VM, open Microsoft SQL Server Management Studio 17 by entering "sql server" into the search bar in the Windows Start menu.

    SQL Server is entered into the Windows Start menu search box, and Microsoft SQL Server Management Studio 17 is highlighted in the search results.

  2. In the SSMS Connect to Server dialog, enter SQLSERVER2008 into the Server name box, ensure Windows Authentication is selected, and then select Connect.

    The SQL Server Connect to Search dialog is displayed, with SQLSERVER2008 entered into the Server name and Windows Authentication selected.

  3. Once connected, expand Databases under SQLSERVER2008 in the Object Explorer, and then right-click the TailspinToys database. In the context menu, select Tasks and then Back Up.

    In the SSMS Object Explorer, the context menu for the TailspinToys database is displayed, with Tasks and Back Up... highlighted.

  4. In the Back UP Database dialog, you will see C:\TailspinToys.bak listed in the Destinations box. This is no longer needed, so select it, and then select Remove.

    In the General tab of the Back Up Database dialog, C:\TailspinToys.bak is selected and the Remove button is highlighted under destinations.

  5. Next, select Add to add the SMB network share as a backup destination.

    In the General tab of the Back Up Database dialog, the Add button is highlighted under destinations.

  6. In the Select Backup Destination dialog, select the Browse (...) button.

    The Browse button is highlighted in the Select Backup Destination dialog.

  7. In the Location Database Files dialog, select the C:\dma-backups folder, enter TailspinToys.bak into the File name field, and then select OK.

    In the Select the file pane, the C:\dms-backups folder is selected and highlighted and TailspinToys.bak is entered into the File name field.

  8. Select OK to close the Select Backup Destination dialog.

  9. In the Back Up Database dialog, select the Media Options in the Select a page pane, and then set the following:

    • Select Back up to the existing media set and then select Overwrite all existing backup sets
    • Under Reliability, check the box for Perform checksum before writing to media. This is require by DMS when using the backup to restore the database to SQL MI.

    In the Back Up Database dialog, the Media Options page is selected, and Overwrite all existing backup sets and Perform checksum before writing to media are selected and highlighted.

  10. Select OK to perform the backup.

  11. You will receive a message when the backup is complete. Select OK.

    Dialog displayed a message that the database backup was completed successfully.

Task 4: Retrieve SQL MI and SQL Server 2008 VM connection information

In this task, you will use the Azure Cloud shell to retrieve the information necessary to connect to your SQL MI and SqlServer2008 VM from DMS.

  1. In the Azure portal, select the Azure Cloud Shell icon from the top menu.

    The Azure Cloud Shell icon is highlighted in the Azure portal's top menu.

  2. In the Cloud Shell window that opens at the bottom of your browser window, select PowerShell.

    In the Welcome to Azure Cloud Shell window, PowerShell is highlighted.

  3. If prompted that you have no storage mounted, select the subscription you are using for this hands-on lab and select Create storage.

    In the You have no storage mounted dialog, a subscription has been selected, and the Create Storage button is highlighted.

    Note: If creation fails, you may need to select Advanced settings and specify the subscription, region and resource group for the new storage account.

  4. After a moment, you will receive a message that you have successfully requested a Cloud Shell, and be presented with a PS Azure prompt.

    In the Azure Cloud Shell dialog, a message is displayed that requesting a Cloud Shell succeeded, and the PS Azure prompt is displayed.

  5. At the prompt, you will retrieve information about SQL MI in the hands-on-lab-SUFFIX resource group by entering the following PowerShell command, replacing SUFFIX with your unique identifier:

    az sql mi list --resource-group hands-on-lab-SUFFIX
    
  6. Within the output of the above command, locate and copy the value of the fullyQualifiedDomainName property. Paste the value into a text editor, such as Notepad.exe, for later reference.

    The output from the az sql mi list command is displayed in the Cloud Shell, and the fullyQualifiedDomainName property and value are highlighted.

  7. Next, you will enter a second command to retrieve the public IP address of the SqlSerer2008 VM, which you will use to connect to the database on that server. Enter the following PowerShell command, replacing SUFFIX with your unique identifier:

    az vm list-ip-addresses -g hands-on-lab-SUFFIX -n SqlServer2008
    
  8. Within the output of the command above, locate and copy the value of the ipAddress property within the publicIpAddresses object. Paste the value into a text editor, such as Notepad.exe, for later reference.

    The output from the az vm list-ip-addresses command is displayed in the Cloud Shell, and the publicIpAddress for the SqlServer2008 VM is highlighted.

  9. Leave the Azure Cloud Shell open for the next task.

Task 5: Create a service principal

In this task, you will use the Azure Cloud Shell to create an Azure Active Directory (Azure AD) application and service principal (SP) that will provide DMS access to Azure SQL MI. You will grant the SP permissions to the hands-on-lab-SUFFIX resource group.

IMPORTANT: You must have rights within your Azure AD tenant to create applications and assign roles to complete this task.

  1. Next, you will issue a command to create a service principal named tailspin-toys and assign it contributor permissions to your hands-on-lab-SUFFIX resource group.

  2. First, you need to retrieve your subscription ID. Enter the following at the Cloud Shell prompt:

    az account list --output table
    
  3. In the output table, locate the subscription you are using for this hands-on lab, and copy the SubscriptionId value into a text editor, such as Notepad, for use below.

  4. Next, enter the following az ad sp create-for-rbac command at the Cloud Shell prompt, replacing {SubscriptionID} with the value you copied above and {ResourceGroupName} with the name of your hands-on-lab-SUFFIX resource group, and then press Enter to run the command.

    az ad sp create-for-rbac -n "tailspin-toys" --role owner --scopes subscriptions/{SubscriptionID}/resourceGroups/{ResourceGroupName}
    

    The az ad sp create-for-rbac command is entered into the Cloud Shell, and the output of the command is displayed.

  5. Copy the output from the command into a text editor, as you will need the appId and password in the next task. The output should be similar to:

    {
        "appId": "aeab3b83-9080-426c-94a3-4828db8532e9",
        "displayName": "tailspin-toys",
        "name": "http://tailspin-toys",
        "password": "76ff5bae-8d25-469a-a74b-4a33ad868585",
        "tenant": "d280491c-b27a-XXXX-XXXX-XXXXXXXXXXXX"
    }
    
  6. To verify the role assignment, select Access control (IAM) from the left-hand menu of the hands-on-lab-SUFFIX resource group blade, and then select the Role assignments tab and locate tailspin-toys under the OWNER role.

    The Role assignments tab is displayed, with tailspin-toys highlighted under OWNER in the list.

  7. Next, you will issue another command to grant the CONTRIBUTOR role at the subscription level to the newly created service principal. At the Cloud Shell prompt, run the following command:

    az role assignment create --assignee http://tailspin-toys --role contributor
    

Task 6: Create and run an online data migration project

In this task, you will create a new online data migration project in DMS for the TailspinToys database.

  1. In the Azure portal, navigate to the Azure Database Migration Service by selecting Resource groups from the left-hand navigation menu, selecting the hands-on-lab-SUFFIX resource group, and then selecting the tailspin-dms Azure Database Migration Service in the list of resources.

    The tailspin-dms Azure Database Migration Service is highlighted in the list of resources in the hands-on-lab-SUFFIX resource group.

  2. On the Azure Database Migration Service blade, select +New Migration Project.

    On the Azure Database Migration Service blade, +New Migration Project is highlighted in the toolbar.

  3. On the New migration project blade, enter the following:

    • Project name: Enter OnPremToSqlMi.
    • Source server type: Select SQL Server.
    • Target server type: Select Azure SQL Database Managed Instance.
    • Choose type of activity: Select Online data migration and select Save.

    The New migration project blade is displayed, with the values specified above entered into the appropriate fields.

  4. Select Create and run activity.

  5. On the Migration Wizard Select source blade, enter the following:

    • Source SQL Server instance name: Enter the IP address of your SqlServer2008 VM that you copied into a text editor in the previous task. For example, 13.66.228.107.
    • Authentication type: Select SQL Authentication.
    • Username: Enter WorkshopUser.
    • Password: Enter Password.1234567890.
    • Connection properties: Check both Encrypt connection and Trust server certificate.

    The Migration Wizard Select source blade is displayed, with the values specified above entered into the appropriate fields.

  6. Select Save.

  7. On the Migration Wizard Select target blade, enter the following:

    • Application ID: Enter the appId value from the output of the `az ad sp create-for-rbac' command you executed in the last task.
    • Key: Enter the password value from the output of the `az ad sp create-for-rbac' command you executed in the last task.
    • Subscription: Select the subscription you are using for this hand-on lab.
    • Target Azure SQL Managed Instance: Select the sqlmi-UNIQUEID instance.
    • SQL Username: Enter sqlmiuser
    • Password: Enter Password.1234567890

    The Migration Wizard Select target blade is displayed, with the values specified above entered into the appropriate fields.

  8. Select Save.

  9. On the Migration Wizard Select databases blade, select TailspinToys.

    The Migration Wizard Select databases blade is displayed, with the TailspinToys database selected.

  10. Select Save.

  11. On the Migration Wizard Configure migration settings blade, enter the following configuration:

    • Network share location: Enter \\SQLSERVER2008\dms-backups. This is the path of the SMB network share you created during the before the hands-on lab exercises.
    • Windows User Azure Database Migration Service impersonates to upload files to Azure Storage: Enter SQLSERVER2008\sqlmiuser
    • Password: Enter Password.1234567890
    • Subscription containing storage account: Select the subscription you are using for this hands-on lab.
    • Storage account: Select the sqlmistoreUNIQUEID storage account.

    The Migration Wizard Configure migration settings blade is displayed, with the values specified above entered into the appropriate fields.

  12. Select Save on the Configure migration setting blade.

  13. On the Migration Wizard Summary blade, enter the following:

    • Activity name: Enter TailspinToysMigration.

    The Migration Wizard summary blade is displayed, Sql2008ToSqlDatabase is entered into the name field, and Validate my database(s) is selected in the Choose validation option blade, with all three validation options selected.

  14. Select Run migration.

  15. Monitor the migration on the status screen that appears. Select the refresh icon in the toolbar to retrieve the latest status.

    On the Migration job blade, the Refresh button is highlighted, and a status of Full backup uploading is displayed and highlighted.

  16. Continue selecting Refresh every 5-10 seconds, until you see the status change to Log files uploading. When that status appears, move on to the next task.

    In the migration monitoring window, a status of Log files uploading is highlighted.

Task 7: Perform migration cutover

Since you performed the migration as an "online data migration," the migration wizard will continue to monitor the SMB network share for newly added log files. This allows for any updates that happen on the source database to be captured until you cut over to the SQL MI database. In this task, you will add a record to one of the database tables, backup the logs, and complete the migration of the TailspinToys database by cutting over to the SQL MI database.

  1. In the migration status window in the Azure portal and select TailspinToys under database name to view further details about the database migration.

    The TailspinToys database name is highlighted in the migration status window.

  2. On the TailspinToys screen you will see a status of Restored for the TailspinToys.bak file.

    On the TailspinToys blade, a status of Restored is highlighted next to the TailspinToys.bak file in the list of active backup files.

  3. To demonstrate log shipping and how transactions made on the source database during the migration process will be added to the target SQL MI database, you will add a record to one of the database tables.

  4. Return to SSMS on your SqlServer2008 VM and select New Query from the toolbar.

    The New Query button is highlighted in the SSMS toolbar.

  5. Paste the following SQL script, which inserts a record into the Game table, into the new query window:

    USE TailspinToys;
    GO
    
    INSERT [dbo].[Game] (Title, Description, Rating, IsOnlineMultiplayer)
    VALUES ('Space Adventure', 'Explore the universe with are newest online multiplayer gaming experience. Build your own rocket ships, and take off for the stars in an infinite open world adventure.', 'T', 1)
    
  6. Execute the query by selecting Execute in the SSMS toolbar.

    The Execute button is highlighted in the SSMS toolbar.

  7. With the new record added to the Games table, you will now backup the transaction logs, which will be shipped to DMS. Select New Query again in the toolbar, and paste the following script into the new query window:

    USE master;
    GO
    
    BACKUP LOG TailspinToys
    TO DISK = 'c:\dms-backups\TailspinToysLog.trn'
    WITH CHECKSUM
    GO
    
  8. Execute the query by selecting Execute in the SSMS toolbar.

  9. Return to the migration status page in the Azure portal. On the TailspinToys screen, select Refresh you should see the TailspinToysLog.trn file appear, with a status of Uploaded.

    On the TailspinToys blade, the Refresh button is highlighted. A status of Uploaded is highlighted next to the TailspinToysLog.trn file in the list of active backup files.

    Note: If you don't see it the transaction logs entry, continue selecting Refresh every few seconds until it appears.

  10. Once the transaction logs are uploaded, they need to be restored to the database. Select Refresh every 10-15 seconds until you see the status change to Restored, which can take a minute or two.

    A status of Restored is highlighted next to the TailspinToysLog.trn file in the list of active backup files.

  11. After verifying the transaction log status of Restored, select Start Cutover.

    The Start Cutover button is displayed.

  12. On the Complete cutover dialog, verify pending log backups is 0, check Confirm, and select Apply.

    In the Complete cutover dialog, a value of 0 is highlighted next to Pending log backups and the Confirm checkbox is checked.

  13. You will be given a progress bar below the Apply button in the Complete cutover dialog. When the migration is complete, you will see the status as Completed.

    A status of Completed is displayed in the Complete cutover dialog.

  14. Close the Complete cutover dialog by selecting the "X" in the upper right corner of the dialog, and do the same thing for the TailspinToys blade. This will return you to the TailspinToysMigration blade. Select Refresh, and you should see a status of Completed from the TailspinToys database.

    On the Migration job blade, the status of Completed is highlighted

  15. You have now successfully migrated the TailspinToys database to Azure SQL Managed Instance.

Task 8: Verify database and transaction log migration

In this task, you will connect to the SQL MI database using SSMS, and quickly verify the migration.

  1. Return to SSMS on your SqlServer2008 VM, and then select Connect and Database Engine from the Object Explorer menu.

    In the SSMS Object Explorer, Connect is highlighted in the menu and Database Engine is highlighted in the Connect context menu.

  2. In the Connect to Server dialog, enter the following:

    • Server name: Enter the fully qualified domain name of your SQL managed instance, which you copied from the Azure Cloud Shell in a previous task.
    • Authentication: Select SQL Server Authentication.
    • Login: Enter sqlmiuser
    • Password: Enter Password.1234567890
    • Check the Remember password box.

    The SQL managed instance details specified above are entered into the Connect to Server dialog.

  3. Select Connect.

  4. You will see you SQL MI connection appear below the SQLSERVER2008 connection. Expand Databases the SQL MI connection and select the TailspinToys database.

    In the SSMS Object Explorer, the SQL MI connection is expanded and the TailspinToys database is highlighted and selected.

  5. With the TailspinToys database selected, select New Query on the SSMS toolbar to open a new query window.

  6. In the new query window, enter the following SQL script:

    SELECT * FROM Game
    
  7. Select Execute on the SSMS toolbar to run the query. You will see the records contained in the Game table displayed, including the new Space Adventure you added after initiating the migration process.

    In the new query window, the query above has been entered, and in the results pane, the new Space Adventure game is highlighted.

  8. You are now done using the SqlServer2008 VM. Close any open windows and log off of the VM. You will use the JumpBox VM for the remaining tasks of this hands-on lab.

Exercise 3: Update the web application to use the new SQL MI database

Duration: 30 minutes

With the TailspinToys database now running on SQL MI in Azure, the next step is to make the required modifications to the TailspinToys gamer information web application.

Note: SQL Managed Instance has private IP address in its own VNet, so to connect an application you need to configure access to the VNet where Managed Instance is deployed. To learn more, read Connect your application to Azure SQL Database Managed Instance.

Task 1: Deploy the web app to Azure

In this task, you will create an RDP connection to the JumpBox VM, and then using Visual Studio on the JumpBox, deploy the TailspinToysWeb application into the App Service in Azure.

  1. In the Azure portal, select Resource groups in the Azure navigation pane, and select the hands-on-lab-SUFFIX resource group from the list.

    Resource groups is selected in the Azure navigation pane and the

  2. In the list of resources for your resource group, select the JumpBox VM.

    The list of resources in the hands-on-lab-SUFFIX resource group are displayed, and JumpBox is highlighted.

  3. On your JumpBox VM blade, select Connect from the top menu.

    The JumpBox VM blade is displayed, with the Connect button highlighted in the top menu.

  4. On the Connect to virtual machine blade, select Download RDP File, then open the downloaded RDP file.

    The Connect to virtual machine blade is displayed, and the Download RDP File button is highlighted.

  5. Select Connect on the Remote Desktop Connection dialog.

    In the Remote Desktop Connection Dialog Box, the Connect button is highlighted.

  6. Enter the following credentials when prompted, and then select OK:

    • Username: sqlmiuser
    • Password: Password.1234567890

    The credentials specified above are entered into the Enter your credentials dialog.

  7. Select Yes to connect, if prompted that the identity of the remote computer cannot be verified.

    In the Remote Desktop Connection dialog box, a warning states that the identity of the remote computer cannot be verified, and asks if you want to continue anyway. At the bottom, the Yes button is circled.

  8. Once logged in, download the MCW Migrating SQL databases to Azure GitHub repo.

  9. If you receive a message that downloads are not allowed, select the Tools icon at the top right of the browser window, and then select Internet options from the context menu.

    The Tools icon is highlighted in the Internet Explorer toolbar, and Internet Options is highlighted in the context menu.

  10. In the Internet Options dialog, select Custom level in the Security level for this zone box.

    The Custom level button is highlighted in the Internet Options dialog.

  11. In the Security Settings - Internet Zone dialog, locate the Downloads settings and choose Enable, then select OK.

    The Downloads property is highlighted in the Security Settings dialog, and Enable is selected.

  12. Select OK on the Internet Options dialog, and then attempt the download again.

  13. When prompted, choose to save the file and then select Open folder.

    The download bar is displayed in Internet Explorer, and Open folder is highlighted.

  14. And once it is download, extract the ZIP file to C:\hands-on-lab.

    In the Extract Compressed Zip File dialog, C:\hands-on-lab is entered into the destination field.

    IMPORTANT: Ensure to use the path above, or something similarly short. Failure to do so could result in errors opening some of the files due to a log file path.

  15. Open the C:\hands-on-lab folder, and then drill down to Migrating-SQL-databases-to-Azure-master\Hands-on lab\lab-files. In the lab-files folder, double-click TailspinToysWeb.sln to open the solution in Visual Studio.

    The folder at the path specified above is displayed, and TailspinToys.sln is highlighted.

  16. If prompted about how you want to open the file, select Visual Studio 2019 and then select OK.

    In the Visual Studio version selector, Visual Studio 2019 is selected and highlighted.

  17. Select Sign in and enter your Azure account credentials when prompted.

    On the Visual Studio welcome screen, the Sign in button is highlighted.

  18. At the security warning prompt, uncheck Ask me for every project in this solution, and then select OK.

    A Visual Studio security warning is displayed, and the Ask me for every project in this solution checkbox is unchecked and highlighted.

  19. Once logged into Visual Studio, right-click the TailspinToysWeb project in the SolutionExplorer, and then select Publish.

    In the Solution Explorer, the context menu for the TailspinToysWeb project is displayed and Publish is highlighted.

  20. In the Pick a publish target window, select App Service, choose Select Existing and then select Publish.

    In the Pick a publish target dialog, App Service is selected and Select Existing is selected.

  21. In the Select Existing App Service dialog, select the subscription you are using for this hands-on lab, then expand the hands-on-lab-SUFFIX resource group folder and select the tailspintoysUNIQUEID App Service.

    The tailspintoysUNIQUEID App Service is selected in the list of existing App Services.

  22. Select OK, which will begin the process of publishing the application to Azure.

  23. When the publish completes, you will see a message in the Visual Studio Output page that the publish succeeded.

    The Publish Succeeded message is displayed in the Visual Studio Output pane.

  24. Visual Studio will launch a web browser and attempt to load the site. At this point, an error will be returned because the database connection strings have not been updated to point to the SQL MI database. You will take care of this in the next task.

Task 2: Update App Service configuration

In this task, you will make updates to the TailspinToys gamer info web application to enable it to connect to and utilize the SQL MI database.

  1. In the Azure portal, select Resource groups from the left-hand menu, select the hands-on-lab-SUFFIX resource group, and then select the tailspintoysUNIQUEID App Service from the list of resources.

    The tailspintoys App Service is highlighted in the list of resource group resources.

  2. On the App Service blade, select Configuration under Settings on the left-hand side.

    The Configuration item is selected under Settings.

  3. On the Configuration blade, locate the Connection strings section, and then select the Pencil (Edit) icon to the right of the TailspinToysContext connection string.

    In the Connection string section, the pencil icon is highlighted to the right of the TailspinToysContext connection string.

  4. The value of the connection string should look like:

    Server=tcp:your-sqlmi-host-fqdn-value,1433;Database=TailspinToys;User ID=sqlmiuser;Password=Password.1234567890;Trusted_Connection=False;Encrypt=True;TrustServerCertificate=True;
    
  5. In the Add/Edit connection string dialog, replace your-sqlmi-host-fqdn-value with the fully qualified domain name for your SQL MI that you copied to a text editor earlier from the Azure Cloud Shell.

    The your-sqlmi-host-fqdn-value string is highlighted in the connection string.

  6. The updated value should look similar to the following screenshot.

    The updated connection string is displayed, with the fully qualified domain name of SQL MI highlighted within the string.

  7. Select Update.

  8. Repeat steps 3 - 7, this time for the TailspinToysReadOnlyContext connection string.

  9. Select Save at the top of the Configuration blade.

    The save button on the Configuration blade is highlighted.

  10. Select Overview to the left of the Configuration blade to return to the overview blade of your App Service.

    Overview is highlighted on the left-hand menu for App Service

  11. At this point, selecting the URL for the App Service on the Overview blade will still result in an error being return. This is because SQL Managed Instance has a private IP address in its own VNet. To connect an application, you need to configure access to the VNet where Managed Instance is deployed, which you will do in the next excise.

Exercise 4: Integrate App Service with the virtual network

Duration: 15 minutes

In this exercise, you will Integrate your App Service with the virtual network that was created during the Before the hands-on lab exercises. The ARM template created a Gateway subnet on the VNet, as well as a Virtual Network Gateway. Both of these resources are required to integrate App Service, and connect to SQL MI.

Task 1: Set point-to-site addresses

In this task, you will configure the client address pool. This is a range of private IP addresses that you will specify below. Clients that connect over a Point-to-Site VPN dynamically receive an IP address from this range. You will use a private IP address range that does not overlap with the VNet you will connect to.

  1. Navigate to the hands-on-lab-SUFFIX-vnet-gateway Virtual network gateway in the Azure portal by selecting it from the list of resources in the hands-on-lab-SUFFIX resource group.

    The Virtual network gateway resource is highlighted in the list of resources.

  2. On the virtual network gateway blade, select Point-to-site configuration under Settings in the left-hand menu, and then select Configure now.

    Point-to-site configuration is highlighted and selected in the left-hand menu. On the Point-to-site configuration blade, Configure now is highlighted.

  3. On the Point-to-site configuration page, set the following configuration:

    • Address pool: Add a private IP address range that you want to use. The address space must be in one of the following address blocks, but should not overlap the address space used by the VNet.
      • 10.0.0.0/8 - This means an IP address range from 10.0.0.0 to 10.255.255.255
      • 172.16.0.0/12 - This means an IP address range from 172.16.0.0 to 172.31.255.255
      • 192.168.0.0/16 - This means an IP address range from 192.168.0.0 to 192.168.255.255
    • Tunnel type: Select SSTP (SSL).
    • Authentication type: Choose Azure certificate.

    The values specified above are entered into the Point-to-site configuration form.

  4. Select Save to validate and save the settings. It will take 1 - 2 minutes for the save to finish.

Task 2: Configure VNet integration with App Services

In this task, you will add the networking configuration to your App Service to enable communication with resources in the VNet.

  1. In the Azure portal, select Resource groups from the left-hand menu, select the hands-on-lab-SUFFIX resource group, and then select the tailspintoysUNIQUEID App Service from the list of resources.

    The tailspintoys App Service is highlighted in the list of resource group resources.

  2. On the App Service blade, select Networking from the left-hand menu and then select Click here to configure under VNet Integration.

    On the App Service blade, Networking is selected in the left-hand menu and Click here to configure is highlighted under VNet Integration.

  3. Select Add VNet on the VNet Configuration blade.

    Add VNet is highlighted on the VNet Configuration blade.

  4. Select the hands-on-lab-SUFFIX-vnet in the Virtual Network dialog.

    The hands-on-lab-SUFFIX-vnet** is highlighted.

  5. Within a few minutes, the VNet will be added and your App Service will be restarted to apply the changes. Select Refresh to see the details. You should see that the certificate status is Certificates in sync.

    The details of the VNet Configuration are displayed. The Certificate Status, Certificates in sync, is highlighted.

    Note: In you receive a message adding the Virtual Network to Web App failed, select Disconnect on the VNet Configuration blade, and repeat steps 3 - 5 above.

Task 3: Open the web application

In this task, you will verify your web application now loads, and you can see the home page of the web app.

  1. Select Overview in the left-hand menu of your App Service, and select the URL of your App service to launch the website. This will open the URL in a browser window.

    The App service URL is highlighted.

  2. Verify that the web site and data is loaded correctly. The page should look similar to the following:

    Screenshot of the TailspinToys Operations Web App.

That's it. You were able to successfully connect your application to the new SQL MI database.

Exercise 5: Improve database security posture with Advanced Data Security

Duration: 30 minutes

In this exercise you will enable Advanced Data Security (ADS) on your SQL MI database, and explore some of the security benefits that come with running your database in Azure. SQL Database Advance Data Security (ADS) provides advanced SQL security capabilities, including functionality for discovering and classifying sensitive data, surfacing and mitigating potential database vulnerabilities, and detecting anomalous activities that could indicate a threat to your database.

Task 1: Enable Advanced Data Security

In this task, you will enable ADS for all databases on the Managed Instance.

  1. In the Azure portal, select Resource groups from the left-hand menu, select the hands-on-lab-SUFFIX resource group, and then select the TailspinToys Managed database resource from the list.

    The TailspinToys Managed Database is highlighted in the resources list.

  2. On the TailspinToys Managed database blade, select Advanced Data Security from the left-hand menu, under Security, and then select Enable Advanced Data Security on the managed instance.

    In the Advanced Data Security blade of the Managed database, the Enable Advanced Data Security on the managed instance button is highlighted.

  3. Within a few minutes, ADS will be enabled for all databases on the Managed Instance. You will see the three tiles on the Advanced Data Security blade become enabled when it has been enabled.

    The enabled tiles on the Advance Data Security blade are displayed.

Task 2: Configure SQL Data Discovery and Classification

In this task, you will look at the SQL Data Discovery and Classification feature of Advanced Data Security. Data Discovery & Classification introduces a new tool for discovering, classifying, labeling & reporting the sensitive data in your databases. It introduces a set of advanced services, forming a new SQL Information Protection paradigm aimed at protecting the data in your database, not just the database. Discovering and classifying your most sensitive data (business, financial, healthcare, etc.) can play a pivotal role in your organizational information protection stature.

Note: This functionality is currently available in Preview for SQL MI through the Azure portal.

  1. On the Advanced Data Security blade, select the Data Discovery & Classification tile.

    The Data Discovery & Classification tile is displayed.

  2. In the Data Discovery & Classification blade, select the info link with the message We have found 40 columns with classification recommendations.

    The recommendations link on the Data Discovery & Classification blade is highlighted.

  3. Look over the list of recommendations to get a better understanding of the types of data and classifications are assigned, based on the built-in classification settings. In the list of classification recommendations, select the recommendation for the Sales - CreditCard - CardNumber field.

    The CreditCard number recommendation is highlighted in the recommendations list.

  4. Due to the risk of exposing credit card information, Tailspin Toys would like a way to classify it as highly confidential, not just Confidential, as the recommendation suggests. To correct this, select + Add classification at the top of the Data Discovery & Classification blade.

    The +Add classification button is highlighted in the toolbar.

  5. Quickly expand the Sensitivity label field, and review the various built-in labels you can choose from. You can also add your own labels, should you desire.

    The list of built-in Sensitivity labels is displayed.

  6. In the Add classification dialog, enter the following:

    • Schema name: Select Sales.
    • Table name: Select CreditCard.
    • Column name: Select CardNumber (nvarchar).
    • Information type: Select Credit Card.
    • Sensitivity level: Select Highly Confidential.

    The values specified above are entered into the Add classification dialog.

  7. Select Add classification.

  8. You will see the Sales - CreditCard - CardNumber field disappear from the recommendations list, and the number of recommendations drop by 1.

  9. Other recommendations you can review are the HumanResources - Employee fields for NationIDNumber and BirthDate. Note that these have been flagged by the recommendation service as Confidential - GDPR. As Tailspin Toys maintains data about gamers from around the world, including Europe, having a tool which helps them discover data which may be relevant to GDPR compliance will be very helpful.

    GDPR information is highlighted in the list of recommendations

  10. Check the Select all check box at the top of the list to select all the remaining recommended classifications, and then select Accept selected recommendations.

    All the recommended classifications are checked and the Accept selected recommendations button is highlighted.

  11. Select Save on the toolbar of the Data Classification window. It may take several minutes for the save to complete.

    Save the updates to the classified columns list.

    Note: This feature is still in preview. If you receive an error when saving, try returning to the Advanced Data Security blade, and selecting the Data Discovery & Classification tile again to see the results.

  12. When the save completes, select the Overview tab on the Data Discovery & Classification blade to view a report with a full summary of the database classification state.

    The View Report button is highlighted on the toolbar.

Task 3: Review Advanced Data Security Vulnerability Assessment

In this task, you will review an assessment report generated by ADS for the TailspinToys database and take action to remediate one of the findings in the TailspinToys database. The SQL Vulnerability Assessment service is a service that provides visibility into your security state, and includes actionable steps to resolve security issues, and enhance your database security.

  1. Return to the Advanced Data Security blade for the TailspinToys Managed database and then select the Vulnerability Assessment tile.

    The Vulnerability tile is displayed.

  2. On the Vulnerability Assessment blade, select Scan on the toolbar.

    Vulnerability assessment scan button.

  3. When the scan completes, you will see a dashboard, displaying the number of failing checks, passing checks, and a breakdown of the risk summary by severity level.

    The Vulnerability Assessment dashboard is displayed.

    Note: Scans are run on a schedule, so if you see a message that no vulnerabilities are found your database may not have been scanned yet. You will need to run a scan manually. To do this, select the Scan button on the toolbar, and follow any prompts to start a scan. This will take a minute or so to complete.

  4. In the scan results, take a few minutes to browse both the Failed and Passed checks, and review the types of checks that are performed. In the Failed the list, locate the security check for Transparent data encryption. This check has an ID of VA1219.

    The VA1219 finding for Transparent data encryption is highlighted.

  5. Select the VA1219 finding to view the detailed description.

    The details of the VA1219 - Transparent data encryption should be enabled finding are displayed with the description, impact, and remediation fields highlighted.

    The details for each finding provide more insight into the reason for the finding. Of note are the fields describing the finding, the impact of the recommended settings, and details on remediation for the finding.

  6. You will now act on the recommendation remediation steps for the finding, and enable Transparent Data Encryption for the TailspinToys database. To accomplish this, you will switch over to using SSMS on your JumpBox VM for the next few steps.

    Note: Transparent data encryption (TDE) needs to be manually enabled for Azure SQL Managed Instance. TDE helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Data Warehouse against the threat of malicious activity. It performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application.

  7. On your JumpBox VM, open Microsoft SQL Server Management Studio 18 from the Start menu, and enter the following information in the Connect to Server dialog.

    • Server name: Enter the fully qualified domain name of your SQL managed instance, which you copied from the Azure Cloud Shell in a previous task.
    • Authentication: Select SQL Server Authentication.
    • Login: Enter sqlmiuser
    • Password: Enter Password.1234567890
    • Check the Remember password box.

    The SQL managed instance details specified above are entered into the Connect to Server dialog.

  8. In SSMS, select New Query from the toolbar, paste the following SQL script into the new query window.

    ALTER DATABASE [TailspinToys] SET ENCRYPTION ON
    

    A new query window is displayed, with the script above pasted into it.

    You turn transparent data encryption on and off on the database level. To enable transparent data encryption on a database in Azure SQL Managed Instance use must use T-SQL.

  9. Select Execute from the SSMS toolbar. After a few seconds, you will see a message that the "Commands completed successfully."

    The Execute button is highlighted on the SSMS toolbar, and the Commands completed successfully message is highlighted in the output window.

  10. You can verify the encryption state and view information the associated encryption keys by using the sys.dm_database_encryption_keys view. Select New Query on the SSMS toolbar again, and paste the following query into the new query window:

    SELECT * FROM sys.dm_database_encryption_keys
    

    The query above is pasted into a new query window in SSMS.

  11. Select Execute from the SSMS toolbar. You will see two records in the Results window, which provide information about the encryption state and keys used for encryption.

    The Execute button on the SSMS toolbar is highlighted, and in the Results pane the two records about the encryption state and keys for the TailspinToys database are highlighted.

    By default, service-managed transparent data encryption is used. A transparent data encryption certificate is automatically generated for the server that contains the database.

  12. Return to the Azure portal and the Advanced Data Security - Vulnerability Assessment blade of the TailspinToys managed database. On the toolbar, select Scan to start a new assessment of the database.

    Vulnerability assessment scan button.

  13. When the scan completes, select the Failed tab, enter VA1219 into the search filter box, and observe that the previous failure is no longer in the Failed list.

    The Failed tab is highlighted and VA1219 is entered into the search filter. The list displays no results.

  14. Now, select the Passed tab, and observe the VA1219 check is listed with a status of PASS.

    The Passed tab is highlighted and VA1219 is entered into the search filter. VA1219 with a status of PASS is highlighted in the results.

    Using the SQL Vulnerability Assessment, it is simple to identify and remediate potential database vulnerabilities, allowing you to proactively improve your database security.

Exercise 6: Enable Dynamic Data Masking

Duration: 15 minutes

In this exercise, you will enable Dynamic Data Masking (DDM) on credit card numbers in the TailspinToys database. DDM limits sensitive data exposure by masking it to non-privileged users. This feature helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.

For example, a service representative at a call center may identify callers by several digits of their credit card number, but those data items should not be fully exposed to the service representative. A masking rule can be defined that masks all but the last four digits of any credit card number in the result set of any query. As another example, an appropriate data mask can be defined to protect personally identifiable information (PII) data, so that a developer can query production environments for troubleshooting purposes without violating compliance regulations.

Task 1: Enable DDM on credit card numbers

When inspecting the data in the TailspinToys database using the ADS Data Discovery & Classification tool, you set the Sensitivity label for credit card numbers to Highly Confidential. In this task, you will take another step to product this information by enabling DDM on the CardNumber field in the CreditCard table. This will prevent queries against that table from returning the full credit card number.

  1. On your JumpBox VM, return to the SQL Server Management Studio (SSMS) window you opened previously.

  2. Expand Tables under the TailspinToys database and locate the Sales.CreditCard table. Expand the table columns and observe that there is a column named CardNumber. Right-click the table, and choose Select Top 1000 Rows from the context menu.

    The Select Top 1000 Rows item is highlighted in the context menu for the Sales.CreditCard table.

  3. In the query window that opens, review the Results, including the CardNumber field. Notice it is displayed in plain text, making the data available to anyone with access to query the database.

    Plain text credit card numbers are highlighted in the query results.

  4. To be able to test the mask being applied to the CardNumber field, you will first create a user in the database that will be used for testing the masked field. In SSMS, select New Query and paste the following SQL script into the new query window:

    USE [TailspinToys];
    GO
    
    CREATE USER DDMUser WITHOUT LOGIN;
    GRANT SELECT ON [Sales].[CreditCard] TO DDMUser;
    

    The SQL script above creates a new user in the database named DDMUser, and grants that user SELECT rights on the Sales.CreditCard table.

  5. Select Execute from the SSMS toolbar to run the query. You will get a message that the commands completed successfully in the Messages pane.

  6. With the new user created, run a quick query to observe the results. Select New Query again, and paste the following into the new query window.

    USE [TailspinToys];
    GO
    
    EXECUTE AS USER = 'DDMUser';
    SELECT * FROM [Sales].[CreditCard];
    REVERT;
    
  7. Select Execute from the toolbar, and examine the Results pane. Notice the credit card number, as above, is visible in clear text.

    The credit card number is unmasked in the query results.

  8. You will now apply DDM on the CardNumber field to prevent it from being viewed in query results. Select New Query from the SSMS toolbar and paste the following query into the query window to apply a mask to the CardNumber field, and select Execute.

    USE [TailspinToys];
    GO
    
    ALTER TABLE [Sales].[CreditCard]
    ALTER COLUMN [CardNumber] NVARCHAR(25) MASKED WITH (FUNCTION = 'partial(0,"xxx-xxx-xxx-",4)')
    
  9. Run the SELECT query you opened in step 6 above again, and observe the results. Specifically inspect the output in the CardNumber field. For reference the query is below.

    USE [TailspinToys];
    GO
    
    EXECUTE AS USER = 'DDMUser';
    SELECT * FROM [Sales].[CreditCard];
    REVERT;
    

    The credit card number is masked in the query results.

    The CardNumber is now displayed using the mask applied to it, so only the last four digits of the card number are visible. Dynamic Data Masking is a powerful feature that enables you to prevent unauthorized users from viewing sensitive or restricted information. It’s a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.

Task 2: Apply DDM to email addresses

From the findings of the Data Discovery & Classification report in ADS, you saw that email addresses are labeled Confidential. In this task, you will use one of the built-in functions for making email addresses using DDM to help protect this information.

  1. For this, you will target the LoginEmail field in the [dbo].[Gamer] table. Open a new query window and execute the following script:

    USE [TailspinToys];
    GO
    
    SELECT TOP(100) * FROM [dbo].[Gamer]
    

    In the query results, full email addresses are visible.

  2. Now, as you did above, you will grant the DDMUser you created above SELECT rights on the [dbo].[Gamer]. In a new query window and enter the following script, and then select Execute:

    USE [TailspinToys];
    GO
    
    GRANT SELECT ON [dbo].[Gamer] TO DDMUser;
    
  3. Next, apply DDM on the LoginEmail field to prevent it from being viewed in full in query results. Select New Query from the SSMS toolbar and paste the following query into the query window to apply a mask to the LoginEmail field, and then select Execute.

    USE [TailspinToys];
    GO
    
    ALTER TABLE [dbo].[Gamer]
    ALTER COLUMN [LoginEmail] NVARCHAR(250) MASKED WITH (FUNCTION = 'Email()');
    

    Note: Observe the use of the built-in Email() masking function above. This is one of several pre-defined masks available in SQL Server databases.

  4. Run the SELECT query below, and observe the results. Specifically inspect the output in the LoginEmail field. For reference the query is below.

    USE [TailspinToys];
    GO
    
    EXECUTE AS USER = 'DDMUser';
    SELECT * FROM [dbo].[Gamer];
    REVERT;
    

    The email addresses are now masked in the query results.

Exercise 7: Use online secondary for read-only queries

Duration: 15 minutes

In this exercise, you will look at how you can use the automatically created online secondary for reporting, without feeling the impacts of a heavy transactional load on the primary database. Each database in the SQL MI Business Critical tier is automatically provisioned with several AlwaysON replicas to support the availability SLA. Using Read Scale-Out allows you to load balance Azure SQL Database read-only workloads using the capacity of one read-only replica.

Task 1: View Leaderboard report in TailspinToys web application

In this task, you will open a web report using the web application you deployed to your App Service.

  1. In the Azure portal, select Resource groups from the left-hand menu, and then select the resource group named hands-on-lab-SUFFIX.

    The hands-on-lab-SUFFIX resource group is selected.

  2. In the hands-on-lab-SUFFIX resource group, select the tailspintoysUNIQUEID App Service from the list of resources.

    The App Service resource is selected from the list of resources in the hands-on-lab-SUFFIX resource group.

  3. On the App Service overview blade, select the URL to open the web application in a browser window.

    The App service URL is highlighted.

  4. In the TailspinToys web app, select Leaderboard from the menu.

    READ_WRITE is highlighted on the Leaderboard page.

    Note the READ_WRITE string on the page. This is the output from reading the Updateability property associated with the ApplicationIntent option on the target database. This can be retrieved using the SQL query SELECT DATABASEPROPERTYEX(DB_NAME(), "Updateability").

Task 2: Update read only connection string

In this task, you will enable Read Scale-Out for the TailspinToysdatabase, using the ApplicationIntent option in the connection string. This option dictates whether the connection is routed to the write replica or to a read-only replica. Specifically, if the ApplicationIntent value is ReadWrite (the default value), the connection will be directed to the database’s read-write replica. If the ApplicationIntent value is ReadOnly, the connection is routed to a read-only replica.

  1. Return to the App Service blade in the Azure portal and select Configuration under Settings on the left-hand side.

    The Configuration item is selected under Settings.

  2. On the Configuration blade, scroll down and locate the connection string named TailspinToysReadOnlyContext within the Connection strings section, and select the Pencil (edit) icon on the right.

    The edit icon next to the read-only connection string is highlighted.

  3. In the Add/Edit connection string dialog, select the Value for the TailspinToysReadOnlyContext and paste the following parameter to end of the connection string.

    ApplicationIntent=ReadOnly;
    
  4. The TailspinToysReadOnlyContext connection string should now look something like the following:

    Server=tcp:sqlmi-abcmxwzksiqoo.15b8611394c5.database.windows.net,1433;Database=TailspinToys;User ID=sqlmiuser;Password=Password.1234567890;Trusted_Connection=False;Encrypt=True;TrustServerCertificate=True;ApplicationIntent=ReadOnly;
    
  5. Select Update.

  6. Select Save at the top of the Configuration blade.

    The save button on the Application settings blade is highlighted.

Task 3: Reload Leaderboard report in the Tailspin Toys web app

In this task, you will refresh the Leaderboard report in the Tailspin Toys web app, and observe the result.

  1. Return to the TailspinToys gamer information website you opened previously, and refresh the Leaderboard page. The page should now look similar to the following:

    READ_ONLY is highlighted on the Reports page.

    Notice the updateability option is now displaying as READ_ONLY. With a simple addition to your database connection string, you are able to send read-only queries to the online secondary of your SQL MI Business critical database, allowing you to load-balance read-only workloads using the capacity of one read-only replica. The SQL MI Business Critical cluster has built-in Read Scale-Out capability that provides free-of charge built-in read-only node that can be used to run read-only queries that should not affect performance of your primary workload.

After the hands-on lab

Duration: 10 minutes

In this exercise, you will de-provision all Azure resources that were created in support of this hands-on lab.

Task 1: Delete Azure resource groups

  1. In the Azure portal, select Resource groups from the left-hand menu, and locate and delete the hands-on-lab-SUFFIX following resource group.

Task 2: Delete the tailspin-toys service principal

  1. In the Azure portal, select Azure Active Directory and then select App registrations.

  2. Select the tailspin-toys application, and select Delete on the application blade.

You should follow all steps provided after attending the Hands-on lab.

Attribution

This content was originally posted here:
https://github.com/microsoft/MCW-Migrating-SQL-databases-to-Azure

License

This content is licensed with the MIT License license.

MIT License

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE