Professional Azure SQL Managed Database Administration E-Book

Ahmad Osama

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 563

Veröffentlichungsjahr: 2021

Beschreibung

Despite being the cloud version of SQL Server, Azure SQL Database and Azure SQL Managed Instance stands out in various aspects when it comes to management, maintenance, and administration. Updated with the latest Azure features, Professional Azure SQL Managed Database Administration continues to be a comprehensive guide for becoming proficient in data management.
The book begins by introducing you to the Azure SQL managed databases (Azure SQL Database and Azure SQL Managed Instance), explaining their architecture, and how they differ from an on-premises SQL server. You will then learn how to perform common tasks, such as migrating, backing up, and restoring a SQL Server database to an Azure database.
As you progress, you will study how you can save costs and manage and scale multiple SQL databases using elastic pools. You will also implement a disaster recovery solution using standard and active geo-replication. Finally, you will explore the monitoring and tuning of databases, the key features of databases, and the phenomenon of app modernization.
By the end of this book, you will have mastered the key aspects of an Azure SQL database and Azure SQL managed instance, including migration, backup restorations, performance optimization, high availability, and disaster recovery.

Leseprobe

Professional Azure SQL Managed Database Administration – Third Edition

Efficiently manage and modernize data in the cloud using Azure SQL

Ahmad Osama and Shashikant Shakya

Professional Azure SQL Managed Database Administration – Third Edition

Copyright © 2021 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Authors: Ahmad Osama and Shashikant Shakya

Technical Reviewers: John Martin and Aaditya Pokkunuri

Managing Editors: Aditya Datar and Mamta Yadav

Technical Editor: Neha Pande

Acquisitions Editor: Ben Renow-Clarke

Production Editor: Deepak Chavan

Editorial Board: Vishal Bodwani, Ben Renow-Clarke, Arijit Sarkar, Dominic Shakeshaft, and Lucy Wan

First Published: July 2018

Second Published: July 2019

Third Published: January 2021

Production Reference: 3220221

ISBN: 978-1-80107-652-4

Published by Packt Publishing Ltd.

Livery Place, 35 Livery Street

Birmingham B3 2PB, UK

Table of Contents

Preface   i

1. Introduction to Azure SQL managed databases   1

Who manages what?   2

The Azure SQL Database architecture   3

The Client Layer   5

The Service Layer   5

The Platform Layer   5

The Infrastructure Layer   5

The Azure SQL Database request flow   6

Provisioning an Azure SQL Database   7

Connecting and querying the SQL Database from the Azure portal   14

Connecting to and querying the SQL Database from SQL Server Management Studio   16

Deleting resources   21

Introduction to Azure SQL Managed Instance   24

Connecting to Azure SQL Managed Instance   25

Virtual cluster connectivity architecture   27

Network requirements   28

Differences between SQL Database, SQL Managed Instance, and SQL Server   28

Backup and restore   29

Recovery model   29

SQL Server Agent   30

Change Data Capture   30

Auditing   31

Mirroring   31

Table partitioning   31

Replication   31

Multi-part names   32

SQL Server Browser   32

FileStream   32

Common Language Runtime (SQL CLR)   32

Resource Governor   32

Global temporary tables   33

Log shipping   33

SQL Trace and Profiler   33

Trace flags   33

System stored procedures   33

The USE statement   34

Exercise: Provisioning an Azure SQL Managed Instance using the Azure portal   34

Activity: Provisioning Azure SQL Server and SQL Database using PowerShell   42

Exercise: Provisioning an Azure SQL Managed Instance   50

Summary   57

2. Service tiers   59

The DTU model   60

DTU service tiers   60

The vCore model   64

vCore service tiers   64

The General Purpose service tier   64

Azure Premium Storage characteristics   66

The Business Critical service tier   67

The Hyperscale service tier   67

vCore hardware generations   72

Determining an appropriate performance tier   73

DMA SKU recommendation   73

Azure SQL Database compute tiers   77

Scaling up the Azure SQL Database service tier   77

Changing a service tier   81

Exercise: Provisioning a Hyperscale SQL database using PowerShell   81

Choosing between vCore and DTU-based purchasing options   83

Licensing   83

Flexibility   83

Summary   85

3. Migration   87

Migration methodology   88

Determining the migration benefits   88

Selecting a service model   88

Selecting a service tier   89

Selecting the primary region and disaster recovery region   89

Determining compatibility issues   89

Selecting a migration tool   89

Choosing between Azure SQL Database and SQL Managed Instance   90

Features   90

Migration   91

Time to develop and market   91

Tools for determining compatibility issues   92

Data Migration Assistant   92

SQL Server Data Tools (SSDT) for Visual Studio   92

SQL Server Management Studio (SSMS)   92

SQLPackage.exe   93

Azure Database Migration Services   93

Choosing a migration tool and performing migration   94

Activity: Migrating an on-premises SQL database to Azure SQL Database using DMA   95

Activity: Migrating an SQL Server database on an Azure virtual machine to an Azure SQL database using Azure DMS   106

Activity: Migrating an on-premises SQL Server database to Azure SQL Database using SSMS   117

Activity: Migrating an SQL Server database to an Azure SQL database using transactional replication   125

Activity: Migrating an on-premises SQL Server to Azure SQL Managed Instance using the native backup and restore method (offline approach)   138

Activity: Migrating an SQL Server on an Azure Virtual Machine to SQL Managed Instance using Azure DMS (online approach)   141

Summary   153

4. Backups   155

Automatic backups   156

Backup storage   157

Backup retention period   157

Optimize backup storage costs for Azure SQL Database and Azure SQL Managed Instance   158

Choose the right backup storage type   159

Optimize the database backup retention period   159

Maximize your free backup storage space   163

Configure LTR backups   164

Use Azure Policy   165

Configure long-term backup retention for Azure SQL Database and Azure SQL Managed Instance   165

Long-term retention configuration on Azure SQL Managed Instance   167

Activity: Configure LTR Backups for Azure SQL Managed Instance using PowerShell   167

Manual Backups for Azure SQL Database   173

DACPAC and BACPAC   173

Backing up an Azure SQL Database Using SQL Server Management Studio (SSMS)   175

Manual versus Automated Backups   178

Activity: Perform Manual Backups Using PowerShell   179

Perform native COPY_ONLY backup on Azure SQL Managed Instance   184

Perform a manual COPY_ONLY backup using T-SQL commands   185

Summary   189

5. Restoration   191

Restore types   192

Point-in-time restore   193

Performing a PITR on an Azure SQL Database using the Azure portal   194

Performing a PITR for an SQL Managed Instance using the Azure portal   199

Long-term database restore   201

Performing an LTDR on an Azure SQL Database using the Azure portal   202

Performing an LTDR for SQL Managed Instance using PowerShell   205

Restoring deleted databases   206

Restoring a deleted database on Azure SQL Database using the Azure portal   207

Restoring a deleted database on SQL Managed Instance using the Azure portal   209

Geo-restoring databases   211

Performing a geo-restore on an SQL Database using the Azure portal   212

Performing a geo-restore on an SQL Managed Instance using the Azure portal   216

Importing a database (Azure SQL Database only)   217

Activity: Performing a PITR for an Azure SQL Database with PowerShell   221

Activity: Performing a geo-restore of an Azure SQL Database with PowerShell   224

Activity: Performing Point-In-Time restore for SQL Managed Instance with PowerShell   227

Part 1: Restoring a database to a point in time using PowerShell on one managed instance   228

Part 2: Performing a cross-instance point-in-time restore from an existing database   231

Activity: Geo-restoring a database hosted on SQL Managed Instance using the Az PowerShell module   236

Activity: Restoring a deleted database on SQL Managed Instance using PowerShell   236

Summary   237

6. Security   239

Network security   240

Firewall rules   240

Managing server-level firewall rules using the Azure portal   242

Managing server-level firewall rules using Transact-SQL   245

Managing database-level firewall rules using Transact-SQL   247

Service endpoints   249

Configuring service endpoints for SQL Database   250

Private endpoint   254

Authentication   260

SQL authentication   260

Azure AD authentication   261

Azure AD   261

Active Directory - Password   262

Using Active Directory - Password to authenticate to a SQL database   264

SQL Database authentication structure   269

SQL Database and SQL Managed Instance authentication considerations   270

Authorization   270

Server-level administrative roles   271

Non-administrative users   271

Creating contained database users for Azure AD authentication   273

Groups and roles   277

Row-level security   277

Dynamic data masking   279

Data Discovery & Classification   280

Exercise: Configuring Data Discovery & Classification for SQL Database   281

Auditing   285

Exercise: Configuring SQL Database auditing   286

Exercise: Configuring auditing for SQL Managed Instance   293

Activity: Audit COPY_ONLY backup events on SQL Managed Instance using audit logs   298

Steps to configure an audit for backup and restore events   299

Transparent Data Encryption   302

Azure Defender for SQL   302

Securing data traffic    305

Enforcing a minimal TLS version for SQL Database and SQL Managed Instance   305

Activity: Setting a minimum TLS version using the Azure portal and PowerShell for SQL Managed Instance   305

Using the Azure portal   306

Using PowerShell   307

Configuring and securing public endpoints in SQL Managed Instance   309

Securing SQL Managed Instance public endpoints   315

Locking traffic flow down using NSG or firewall rules   316

Activity: Implementing RLS   317

Activity: Implementing DDM   322

Activity: Implementing Azure Defender for SQL to detect SQL injection and brute-force attacks   325

Summary   335

7. Scalability   337

Vertical scaling   338

Scale-up or scale-down service tiers   338

Vertical partitioning   342

Activity: Creating alerts   343

Horizontal scaling   364

Shard map manager   367

Sharding data models   369

Activity: Creating shards   371

Activity: Splitting data between shards   389

Activity: Using elastic database queries   400

Scaling a managed instance   403

Duration of scale-up/down operations   404

Activity: Scaling up SQL Managed Instance using the Azure portal   406

Activity: Scaling a managed instance using the Az.sql PowerShell module   408

Alternate ways of scaling SQL Managed Instance   410

Activity: Connecting to the SQL Managed Instance internal read replica using SSMS   411

Summary   413

8. Elastic and instance pools   415

Introducing elastic database pools in SQL Database   416

When should you consider elastic database pools?   417

Sizing an elastic database pool   419

Creating an elastic database pool and adding toystore shards to the elastic database pool   421

Geo-replication considerations for elastic database pools   426

Auto-failover group considerations for elastic database pools   426

Activity: Exploring elastic database pools   426

Elastic database jobs   434

Elastic job agent   435

Job database   435

Target group   435

Jobs   435

Use cases   436

Exercise: Configuring an elastic database job using T-SQL   436

Introducing instance pools in SQL Managed Instance   444

Key differences between an instance pool and a single managed instance   445

Architecture differences between an instance pool and a single SQL managed instance   446

Resource limits   447

Public preview limitations   447

Performance and security considerations for instance pools   448

Deploying an instance pool using PowerShell commands   448

Activity: Deploying and managing a managed instance in an instance pool   452

Summary   457

9. High availability and disaster recovery   459

High availability   460

The basic, standard, and general-purpose service tier locally redundant availability model   460

General-purpose service tier zone-redundant configuration   462

The premium/business-critical tier locally redundant availability model   464

The premium/business critical service tier zone-redundant configuration   465

Accelerated database recovery (ADR)   467

The standard database recovery process   467

The ADR process   469

Activity: Evaluating ADR   471

Disaster recovery   475

Active geo-replication   475

Auto-failover groups   477

Activity: Configuring active geo-replication and performing manual failover using the Azure portal   481

Activity: Configuring an Azure SQL Database auto-failover group using Azure portal   491

Activity: Configuring active geo-replication for Azure SQL Database using PowerShell   495

Activity: Configuring auto-failover groups for Azure SQL Database using PowerShell   504

Activity: Configuring an auto-failover group for SQL Managed Instance   514

Summary   527

10. Monitoring and tuning   529

Monitoring an Azure SQL Database and SQL Managed Instance using the Azure portal   530

Monitoring database metrics   532

Alert rules, database size, and diagnostic settings   536

Diagnostic settings and logs   543

Intelligent Performance   548

Query Performance Insight   549

Analyzing diagnostic logs using Azure SQL Analytics   550

Creating a Log Analytics workspace   550

Creating an Azure SQL Analytics solution   556

Generating a workload and reviewing insights   557

Activity: Monitoring Azure SQL Database with Log Analytics and Power BI   567

Monitoring queries using the Query Performance Insight pane   578

Monitoring an Azure SQL Database and SQL Managed Instance using DMVs    584

Monitoring database metrics   584

Monitoring connections   586

Monitoring query performance   588

Monitoring blocking   589

Extended events   591

Examining queries using extended events   592

Tuning an Azure SQL database   597

Automatic tuning   597

In-memory technologies   599

In-memory OLTP   599

Memory-optimized tables   599

Natively compiled procedures   600

Columnstore indexes   601

Monitoring cost   606

Activity: Exploring the in-memory OLTP feature   607

Monitoring and tuning an Azure SQL Managed Instance   610

General Purpose instance I/O characteristics   611

Monitoring the first run with the default file configuration of the TPC-C database   618

Summary   621

11. Database features   623

Azure SQL Data Sync   624

Activity: Configuring Data Sync between two Azure SQL databases using PowerShell   627

Online and resumable DDL operations   635

SQL Graph queries and improvements   637

Graph database integrity using edge constraints   640

Machine Learning Services   643

Differences between Machine Learning Services in SQL Server and Azure SQL Managed Instance   644

Activity: Run basic Python scripts   645

Activity: Using Machine Learning Services in Azure SQL Managed Instance to forecast monthly sales for the toystore database   647

Distributed transactions in Azure SQL Managed Instance   653

Server Trust Group   654

Activity: Creating a Server Trust Group using the Azure portal   655

Activity: Running distributed transactions using T-SQL   657

Summary   660

12. App modernization   663

Migrating an SQL Server workload to SQL Managed Instance   665

Backup and restore   666

SQL installation and patches   666

Scaling   667

High availability and disaster recovery   667

Newly introduced features   668

Support for hosting SSRS catalog databases   668

Azure Machine Learning   669

Distributed transaction support   670

SQL Database serverless   671

Serverless use cases   671

Creating a serverless database   672

Auto-scaling in serverless   675

Cache Reclamation   676

Auto-pausing in serverless   676

Auto-resuming in serverless   676

SQL Database serverless billing   677

Demonstration of auto-scaling and compute billing in serverless   679

Serverless vs. provisioned compute   682

Scaling to the Hyperscale service tier   683

Considering moving to the Hyperscale service tier   683

Activity: Updating an existing SQL database to the Hyperscale service tier using the Azure portal   684

Activity: Updating an existing SQL database to the Hyperscale service tier using PowerShell commands   686

Read scale-out an SQL Hyperscale database   688

Summary   688

Index   691

Preface

About

This section briefly introduces the authors and reviewers, what this book covers, the technical skills you'll need to get started, and the hardware and software requirements needed to complete all of the activities and exercises.

About Professional Azure SQL Managed Database Administration, Third Edition

Despite being the cloud version of SQL Server, Azure SQL Database and Azure SQL Managed Instance stands out in various aspects when it comes to management, maintenance, and administration. Updated with the latest Azure features, Professional Azure SQL Managed Database Administration continues to be a comprehensive guide for becoming proficient in data management.

The book begins by introducing you to the Azure SQL managed databases (Azure SQL Database and Azure SQL Managed Instance), explaining their architecture, and how they differ from an on-premises SQL server. You will then learn how to perform common tasks, such as migrating, backing up, and restoring a SQL Server database to an Azure database.

As you progress, you will study how you can save costs and manage and scale multiple SQL databases using elastic pools. You will also implement a disaster recovery solution using standard and active geo-replication. Finally, you will explore the monitoring and tuning of databases, the key features of databases, and the phenomenon of app modernization.

By the end of this book, you will have mastered the key aspects of an Azure SQL database and Azure SQL managed instance, including migration, backup restorations, performance optimization, high availability, and disaster recovery.

About the authors

Ahmad Osama works for Pitney Bowes Pvt. Ltd. as a technical architect and is a former Microsoft Data Platform MVP. In his day job, he works on developing and maintaining high performant, on-premises and cloud SQL Server OLTP environments as well as deployment and automating tasks using PowerShell. When not working, Ahmad blogs at DataPlatformLabs and can be found glued to his Xbox.

Shashikant Shakya is a passionate technologist with decades of experience in the sphere of databases. He works for Microsoft as a senior support engineer. In his day job, he works on Azure SQL Database, Azure Database for MySQL, and PostgreSQL. Apart from his work, he is a regular speaker at the SQLBangalore community group.

About the reviewers

Aaditya Pokkunuri is an experienced senior database engineer with a history of working in the information technology and services industry. He has a total of 11 years' experience. He is skilled in performance tuning, MS SQL Database server administration, SSIS, SSRS, Power BI, and SQL development.

He possesses an in-depth knowledge of replication, clustering, SQL Server high availability options, and ITIL processes, as well as expertise in Windows administration tasks, Active Directory, and Microsoft Azure technologies.

He also has expertise in AWS Cloud and is an AWS solution architect associate. Aaditya is a strong information technology professional with a Bachelor of Technology degree focused on computer science and engineering from Sastra University, Tamil Nadu.

John Martin is an experienced data platform professional and Microsoft Data Platform MVP, having spent over a decade working with the Microsoft data and cloud platform technologies. In this time, John has learned how to get the most out of these platforms as well as the key pitfalls that should be avoided.

Learning objectives

Understanding Azure SQL database configuration and pricing optionsProvisioning a new SQL database or migrating an existing on-premises SQL Server database to an Azure SQL databaseBacking up and restoring an Azure SQL databaseSecuring and scaling an Azure SQL databaseMonitoring and tuning an Azure SQL databaseImplementing high availability and disaster recovery with an Azure SQL databaseManaging, maintaining, and securing managed instances

Audience

This book is designed to benefit database administrators, database developers, or application developers who are interested in developing new applications or migrating existing ones with Azure SQL Database.

Prior experience of working with an on-premises SQL server or Azure SQL database, along with a basic understanding of PowerShell scripts and C# code, is necessary to grasp the concepts covered in this book.

Approach

Professional Azure SQL Managed Database Administration is a perfect blend of deep theoretical knowledge and detailed descriptions of implementation techniques and numerous tips that are essential for making its readers ready for real-world challenges. 

Hardware and software requirements

Hardware requirements

For the optimal learning experience, we recommend the following hardware configuration:

Windows 10/Mac/LinuxProcessor: Pentium 4, 1.8 GHz or higher (or equivalent)Memory: 4 GB RAMHard disk: 10 GB free spaceAn internet connection

Software requirements

We also recommend that you have the following software configuration in advance:

PowerShell 7: https://github.com/PowerShell/powershell/releasesRML Utilities: https://www.microsoft.com/download/details.aspx?id=4511SQL Server Management Studio: https://docs.microsoft.com/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15Power BI Desktop (optional): https://powerbi.microsoft.com/downloads/Azure Data Studio: https://docs.microsoft.com/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15Azure Az PowerShell module: https://docs.microsoft.com/powershell/azure/new-azureps-module-az?view=azps-5.2.0

Conventions

Code words in the text, database names, folder names, filenames, and file extensions are shown as follows.

"The query gets the details for the cpu_percent, physical_data_read_percent, log_ write_percent, workers_percent, and sessions_percent metrics."

Here's a sample block of code:

AzureMetrics

| where ResourceProvider=="MICROSOFT.SQL" | where ResourceId contains "/ SERVERS/"

| where ResourceId contains "/DATABASES/" and MetricName in ('cpu_ percent', 'physical_data_read_percent', 'log_write_percent', 'workers_ percent', 'sessions_percent')

Downloading resources

The code bundle for this book is also hosted on GitHub at https://github.com/PacktPublishing/Professional-Azure-SQL-Database-Administration-Third-Edition. Here, you can find the YAML and other files used in this book, which are referred to as relevant instances.

We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!

Acknowledgement

We are grateful to Microsoft and their team of SMEs for reviewing the book and providing suggestions that enhanced this edition. Acknowledging their contribution, we have listed down the names of the experts who contributed to this book.

Anna Hoffman | Shreya Verma | Borko Novakovic | Denzil Ribeiro Venkata Raj Pochiraju | Morgan Oslake | Mladen Andzic | Andreas Wolter Mirek Sztajno | Joachim Hammer | David Trigano | Srini Acharya Uros Milanovic | Emily Lisa | Joe Sack | Mara-Florina Steiu | Davide Mauri

For successful completion of this edition of Professional Azure SQL Managed Database Administration,special thanks our authors, Ahmad Osama for his contribution on Azure SQL Database and Shashikant Shakya for his contribution on Azure SQL Managed Instance.

1. Introduction to Azure SQL managed databases

There are very few relational database systems as established and widely used as Microsoft's SQL Server. SQL Server on Microsoft Azure comes in three different flavors (commonly known as the Azure SQL family): SQL Server on Azure Virtual Machines (VM) (infrastructure as a service, or IaaS), Azure SQL Database (platform as a service, or PaaS), and Azure SQL Managed Instance (PaaS).

Each of these products has specific use cases, which makes it easy for us to move to Azure SQL whether we're starting up with a new application or migrating an existing workload to Azure.

The IaaS offering, SQL Server on Azure VM, is similar to an on-premises service where Microsoft manages the hardware, virtualization, and infrastructure, and database administrators (DBAs) manage every aspect of SQL Server.

The PaaS offerings, Azure SQL Database and Azure SQL Managed Instance, allow DBAs to focus more on monitoring, capacity planning, and tuning, while Microsoft takes care of areas such as backup, high availability, and more.

This chapter introduces the Azure SQL Database architecture, the Azure SQL Managed Instance connectivity architecture, and the differences between the SQL Database, SQL Managed Instance, and SQL Server (on-premises or using Azure VM) offerings.

In this chapter, we will be covering the following topics:

Describing the architecture of SQL DatabaseIdentifying the differences between an on-premises SQL Server, SQL Database, and SQL Managed InstanceThe connectivity architecture of SQL Managed InstanceProvisioning an Azure SQL Database and Azure SQL Managed Instance using the Azure portal and Windows PowerShell

Who manages what?

Figure 1.1 lists the tasks that you (the DBA) and Microsoft manage for Azure SQL PaaS:

Figure 1.1: Who manages what?

Note

Fixing outages here refers to application outages that arise due to blockages, deadlocks, and broken releases, rather than infrastructure outages.

In an Azure SQL PaaS environment, the DBA works closely with application developers to understand the application and database design, help with the migration (when moving from on-premises to Azure), choose the right performance tier to start with, and then continuously monitor performance for cost optimization.

The DBA also has to work closely with DevOps and often get into DevOps' shoes to automate the release and deployment process and provision the database infrastructure.

This requires learning a new set of skills, such as familiarity with different Azure services, DevOps, and monitoring and management tools.

This chapter introduces the two Azure SQL PaaS offerings, SQL Database and SQL Managed Instance. We'll learn about the SQL Database and SQL Managed Instance architectures, provision SQL Database and SQL Managed Instance, and identify the key differences between SQL Database, SQL Managed Instance, and on-premises SQL Server.

Throughout this book, you will also learn more about the different aspects of managing and administrating SQL Database and SQL Managed Instance, such as provisioning, migration, backup, restore, security, monitoring, and performance.

Note

Azure SQL Database is also commonly referred to as SQL Azure or SQL Database instances.

The Azure SQL Database architecture

Azure SQL Database is a highly scalable, multi-tenant, and highly available Platform-as-a-Service (PaaS) or Database-as-a-Service (DBaaS) offering from Microsoft.

Azure SQL Database, first released on February 1, 2010, is a cloud database service that is based on Microsoft SQL Server.

It is compatible with most SQL Server database-level features and is optimized for Software-as-a-Service (SaaS) applications.

As organizations are adopting cloud computing and moving their applications into the cloud, Azure SQL Database offers everything that DBaaS can offer. Azure SQL Database is a DBaaS option for any organization with applications built on SQL Server databases.

SQL Database uses familiar Transact-SQL programming and a user interface that is well known and easy to adopt. As companies move their workloads to the cloud, it is important for SQL Server DBAs and developers to learn how to use Azure SQL Database for a smooth transition from SQL Server (on-premises or on Azure VM) to SQL Database.

Note

Microsoft takes care of the operating system (OS), storage, networking, virtualization, servers, installation, upgrades, infrastructure management, and maintenance.

Azure SQL Database has the following deployment options:

Single databaseElastic pool

Azure SQL Database allows users to focus only on managing data and is divided into four layers that work together to provide users with relational database functionality, as shown in Figure 1.2:

Figure 1.2: The four layers of Azure SQL Database

Note

If you were to compare SQL Database's architecture to the on-premises SQL Server architecture, other than the service layer, the architecture is pretty similar.

The Client Layer

The client layer acts as an interface for applications to access an SQL Database. It can be either on-premises or on Microsoft Azure. The Tabular Data Stream (TDS) is used to transfer data between an SQL Database and applications. SQL Server also uses TDS to communicate with applications. This allows applications such as .NET, ODBC, ADO.NET, Python, and Java applications to easily connect to Azure SQL Database without any additional requirements.

The Service Layer

The service layer acts as a gateway between the client and platform layers. It is responsible for provisioning an SQL Database, user authentication, SQL Database validation, enforcing security (including firewall rules and denial-of-service attacks), billing and metering for SQL Databases, and routing connections from the client layer to the physical server hosting the SQL Database in the platform layer.

The Platform Layer

The platform layer consists of physical servers hosting SQL Databases in datacenters. Each SQL database is stored on one physical server and is replicated across two different physical servers to provide high availability.

As shown in Figure 1.2, the platform layer has two other components: Azure Service Fabric and Management Service. Azure Service Fabric is responsible for load balancing, automatic failover, and the automatic replication of SQL Databases between physical servers. Management Service takes care of an individual server's health monitoring and patch updates.

The Infrastructure Layer

This layer is responsible for the administration of the physical hardware and the OS.

The Azure SQL Database request flow

Figure 1.3 shows the platform layer:

Figure 1.3: Platform layer – nodes

The application sends a TDS request (login, DML, or DDL queries) to the SQL Database. The TDS request is not directly sent to the platform layer. The request is first validated by the SQL Gateway Service at the service layer.

The Gateway Service validates the login and firewall rules and checks for denial-of-service attacks. It then dynamically determines the physical server on which the SQL Database is hosted and routes the request to that physical server in the platform layer. Dynamic routing allows the SQL Database to be moved across physical servers or SQL instances in the event of hardware failures.

Note

Here, a node is a physical server. A single database is replicated across three physical servers internally by Microsoft to help the system recover from physical server failures. The Azure SQL Server user connects to just a logical name.

Dynamic routing refers to routing the database request to the physical server that hosts an Azure SQL Database. This routing is done internally and is transparent to the user. If one physical server hosting the database fails, dynamic routing will route the requests to the next available physical server hosting the Azure SQL Database.

The internals of dynamic routing are out of the scope of this book.

As shown in Figure 1.3, the platform layer has three nodes: Node 1, Node 2, and Node 3. Each node has a primary replica of an SQL Database and two secondary replicas of two different SQL Databases from two different physical servers. The SQL Database can fail over to the secondary replicas if the primary replica fails. This ensures the high availability of the SQL Database.

Provisioning an Azure SQL Database

Provisioning an Azure SQL Database refers to creating a new and blank Azure SQL Database.

In this section, we'll create a new SQL Database in Azure using the Azure portal:

Open a browser and log in to the Azure portal using your Azure credentials: https://portal.azure.com.In the left-hand navigation pane, select Create a resource:

Figure 1.4: Azure pane

On the New page, under Databases, select SQL Database:

Figure 1.5: Azure panel

On the SQL Database page, under the Project details heading, provide the Subscription and Resource group details. Click the Create new link under the Resource group textbox. In the pop-up box, set the Resource group name as toystore.

Note

A resource group is a logical container that is used to group the Azure resources required to run an application.

For example, the toystore retail web application uses different Azure resources, such as Azure SQL Database, Azure VMs, and Azure Storage. All of these resources can be grouped into a single resource group, say, toystore.

The SQL Database name should be unique across Microsoft Azure and should follow the following naming rules and conventions: https://docs.microsoft.com/azure/cloud-adoption-framework/ready/azure-best-practices/naming-and-tagging:

Figure 1.6: SQL Database panel

Under the Database details heading, enter the database name and server.To create a new server, click on Create new under the Server textbox.

On the New server page, provide the following details and click Select at the bottom of the page: Server name, Server admin login, Password, Confirm password, and Location.

The server name should be unique across Microsoft Azure and should follow the following naming rules and conventions: https://docs.microsoft.com/azure/cloud-adoption-framework/ready/azure-best-practices/naming-and-tagging:

Figure 1.7: Server pane

Under the Want to use SQL elastic pool? option, select No.In Compute + storage, click Configure database and then select Standard:

Figure 1.8: The Configure window

Note that you will have to click the Looking for basic, standard,premium? link for the standard option to be available:

Figure 1.9: The Configure pane

Skip the options under Networking and Additional settings.Click Review+create to continue:

Figure 1.10: SQL pane provisioning panel

On the TERMS page, read through the terms and conditions and the configuration settings made so far:

Figure 1.11: The TERMS page

Click Create to provision the SQL Database.

Provisioning may take 2-5 minutes. Once the resources are provisioned, you'll get a notification, as shown in Figure 1.12:

Figure 1.12: Notification after provision completion

You can click Go to resource to go to the newly created SQL Database.

You have now provisioned your first Azure SQL Database.

Connecting and querying the SQL Database from the Azure portal

In this section, we'll learn how to connect and query the SQL Database from the Azure portal:

From the toystore pane, select Query editor (preview):

Figure 1.13: toystore pane

In the Query editor (preview) pane, select Login, and under SQL server authentication, provide the username and password:

Figure 1.14: The Query Editor (preview) pane

Select OK to authenticate and return to the Query editor (preview) pane:

Open C:\Code\Chapter01\sqlquery.sql in Notepad. Copy and paste the query from Notepad into the Query 1 window in the Query editor in the Azure portal.

The query creates a new table (orders), populates it with sample data, and returns the top 10 rows from the orders table:

-- create a new orders table CREATE TABLE orders

(

orderid INT IDENTITY(1, 1) PRIMARY KEY,

quantity INT, sales MONEY

);

--populate Orders table with sample data

;

WITH t1

AS (SELECT 1 AS a UNION ALL

SELECT 1),

t2

AS (SELECT 1 AS a FROM t1

CROSS JOIN t1 AS b),

t3

AS (SELECT 1 AS a FROM t2

CROSS JOIN t2 AS b),

t4

AS (SELECT 1 AS a FROM t3

CROSS JOIN t3 AS b),

t5

AS (SELECT 1 AS a FROM t4

CROSS JOIN t4 AS b),

nums

AS (SELECT Row_number()

OVER (

ORDER BY (SELECT NULL)) AS n

FROM t5)

INSERT INTO orders SELECT n,

n * 10

FROM nums;

GO

SELECT TOP 10 * from orders;

Select Run to execute the query. You should get the following output:

Figure 1.15: Expected output

The query editor allows us to connect and query from the Azure portal; however, it's not as strong in features as clients such as SQL Server Management Studio and Azure Data Studio.

Connecting to and querying the SQL Database from SQL Server Management Studio

In this section, we'll connect to and query an Azure SQL Database from SQL Server Management Studio (SSMS):

Open SSMS. In the Connect to Server dialog box, set the Server type as Database Engine, if not already selected.Under Server name, provide the Azure SQL Server name. You can find the Azure SQL Server name in the Overview section of the Azure SQL Database pane in the Azure portal:

Figure 1.16: Overview pane of the toystore database

Select SQL Server Authentication as the authentication type.Provide the login and password for Azure SQL Server and select Connect:

Figure 1.17: Login panel of SQL Server

You'll get an error saying Your client IP address does not have access to the server. To connect to Azure SQL Server, you must add the IP of the system you want to connect from under the firewall rule of Azure SQL Server. You can also provide a range of IP addresses to connect from:

Figure 1.18: New Firewall Rule pane

Note

You can also sign in and add a client IP to the Azure SQL Server firewall by using the Sign In button shown in Figure 1.18 and following the instructions.

To add your machine's IP to the Azure SQL Server firewall rule, switch to the Azure portal.

Open the toystore SQL Database Overview pane, if it's not already open. From the Overview pane, select Set server firewall:

Figure 1.19: Setting the server firewall in the Overview pane

In the Firewall settings pane, select Add client IP:

Figure 1.20: The Add client IP option in the Firewall settings pane

The Azure portal will automatically detect the machine's IP and add it to the firewall rule.

If you wish to rename the rule, you can do so by providing a meaningful name in the RULE NAME column.

All machines with IPs between START IP and END IP are allowed to access all of the databases on the toyfactory server:

Note

A virtual network can be used to add an SQL Database in Azure to a given network. A detailed explanation of virtual networks is out of the scope of this book.

Figure 1.21: The Firewall settings pane

Click Save to save the firewall rule.

Switch back to SSMS and click Connect. You should now be able to connect to Azure SQL Server. Press F8 to open Object Explorer, if it's not already open:

Figure 1.22: Object Explorer pane

You can view and modify the firewall settings using T-SQL in the master database. Press Ctrl + N to open a new query window. Make sure that the database is set to master.

Note

To open a new query window in the master database context, in Object Explorer, expand Databases, then expand System Databases. Right-click the master database and select New Query.

Enter the following query to view the existing firewall rules:

SELECT * FROM sys.firewall_rules

You should get the following output:

Figure 1.23: Existing firewall rules

The AllowAllWindowsAzureIps firewall is the default firewall, which allows resources within Microsoft to access Azure SQL Server.

The rest are user-defined firewall rules. The firewall rules for you will be different from what is shown here.

You can use sp_set_firewall_rule to add a new firewall rule and sp_delete_ firewall_rule to delete an existing firewall rule.

To query the toystore SQL Database, change the database context of the SSMS query window to toystore. You can do this by selecting the toystore database from the database dropdown in the menu:

Figure 1.24: Dropdown to select the toystore database

Copy and paste the following query into the query window:

SELECT COUNT(*) AS OrderCount FROM orders;

The query will return the total number of orders from the orders table. You should get the following output:

Figure 1.25: Total number of orders in the orders table

We can connect to and query Azure SQL Server from SSMS as we do for an on-premises SQL Server. However, SSMS doesn't have all of the features or options that are available in Azure SQL Database.

Deleting resources

To delete an Azure SQL Database, an Azure SQL Server instance, and Azure resource groups, perform the following steps:

Note

All resources must be deleted to successfully complete the activity at the end of this chapter.

Switch to the Azure portal and select All resources from the left-hand navigation pane.From the All resources pane, select the checkbox next to the toyfactory Azure SQL Server instance and then select Delete from the top menu:

Figure 1.26: Deleting the toyfactory SQL Server

In the Delete Resources window, type yes in the confirmation box and click the Delete button to delete the Azure SQL Server instance and the Azure SQL Database:

Figure 1.27: Confirming the deletion of the selected resources

Note

To only delete an Azure SQL Database, check the Azure SQL Database checkbox.

To delete the Azure resource group, select Resource groups from the left-hand navigation pane:

Figure 1.28: Resource groups

In the Resource groups pane, click the three dots next to the toystore resource group, and then select Delete resource group from the context menu:

Figure 1.29: Delete resource group option

In the delete confirmation pane, type the resource under the TYPE THE RESOURCE GROUP NAME section, and then click Delete.

We can easily delete resources using the Azure portal. However, note that we may not be able to recover the deleted resource.

Introduction to Azure SQL Managed Instance

Azure SQL Managed Instance is a fully managed SQL Server instance offering announced in May 2017 and made generally available on October 1, 2018.

Azure SQL Managed Instance provides nearly 100% surface area compatibility with the latest SQL Server (Enterprise Edition) database engine, providing all the PaaS benefits available with Azure SQL Database, such as automatic patching and version updates, automatic backups, high availability, and so on.

Note that Azure SQL Managed Instance is its own product within the Azure SQL family, rather than being just a deployment option for Azure SQL Database, with near 100% compatibility with on-premises SQL Server instances.

Azure SQL Managed Instance supports most of the instance-scoped features of traditional SQL Server deployment, which were previously not available in Azure SQL Database, since Azure SQL Database is scoped at the database-level. Azure SQL Managed Instance, therefore, provides easy lift-and-shift migration from an on-premises environment to the cloud.

When you migrate to an Azure SQL Managed Instance on Azure, you don't only migrate databases, you can also migrate licenses too.

Note

You can save up to 82% on Azure SQL Managed Instance when migrating from SQL Server Enterprise or Standard edition with software assurance. For more details, please visit https://azure.microsoft.com/pricing/hybrid-benefit/ or contact Azure.

Some of the important features supported by Azure SQL Managed Instance that are not available in Azure SQL Database are as follows:

Native backup and restoreGlobal temporary tablesCross-database queries and transactionsLinked serversCLR modulesSQL agentDatabase mail

Here are some recently added features:

Distributed transactionsInstance poolsInstance-level Azure Active Directory server principals (logins)Transactional replicationThreat detectionLong-term backup retentionMachine learning services (R and Python)

These and other features of Azure SQL Managed Instance make it almost 100% compatible with SQL Server.

Connecting to Azure SQL Managed Instance

Azure SQL Managed Instance is a set of services hosted on one or more isolated virtual machines inside a virtual network subnet.

When we provision an Azure SQL Managed Instance, a virtual cluster is created. A virtual cluster can have one or more SQL Managed Instances.

Applications connect to databases via an endpoint, <mi_name>.<dns_zone>.database. windows.net, and should be inside a virtual network, a peered virtual network, or an on-premises network connected via VPN or Azure ExpressRoute.

Unlike Azure SQL Database, Azure SQL Managed Instance supports Azure Virtual Network (VNet). An Azure VNet is a logical boundary or isolation that groups resources within a specified Azure region and enables secure communication between resources, the internet, and on-premises networks:

Figure 1.30: High-level connectivity architecture for SQL Managed Instances

Figure 1.30 shows a high-level connectivity architecture for SQL Managed Instances. Let's go through it:

SQL Managed Instances are part of a virtual cluster and are in an SQL Managed Instance subnet in a virtual network in the East US region.Web and other applications in the same virtual network connect to the managed instance using a TDS private IP endpoint, for example, sqlinstance.dnszone.database.windows.net.Applications in the West US virtual network connect using the same endpoint; however, the two virtual networks are peered using global virtual network peering to allow connectivity between them. The same regional virtual network can be peered with SQL Managed Instance.

Note

Global virtual network peering support for SQL Managed Instance is new to SQL Managed Instance.

On-premises applications connect using the same endpoint via VPN or an ExpressRoute gateway.To improve the overall experience and availability, Azure applies a network intent policy on virtual network infrastructure elements. The policy plays a major role in preventing network misconfiguration and ensures normal SQL Managed Instance operations.

Virtual cluster connectivity architecture

In the previous example, we saw connectivity to SQL Managed Instance from different networks. Here, we are going to learn about virtual cluster internal communication:

Figure 1.31: High-level architecture of virtual cluster connectivity

In Figure 1.31, applications/client connects to SQL Managed Instance using a fully qualified domain name (FQDN), sqlmi_name.<dns_zone>.database.windows.net. This hostname can only be resolved within a private network. The dnz_zone ID is automatically created when the virtual cluster is deployed. The private IP belongs to the internal load balancer (ILB) of SQL Managed Instance, and the load balancer forwards traffic to the SQL Managed Instance gateway (GW) service. Since multiple instances run inside the virtual cluster, the GW service redirects SQL traffic to the correct instance based on the instance name.

Management and deployment services connect to SQL Managed Instance using a load balancer (LB) that uses a public IP address. A built-in firewall only allows traffic from Microsoft IP addresses on specified management ports. All the communication inside the virtual cluster is encrypted using TLS protocols.

Network requirements

SQL Managed Instance needs to be deployed in a dedicated subnet in a virtual network. The subnet must have these characteristics:

Dedicated subnet: The SQL Managed Instance subnet must be reserved only for managed instances. The subnet can't contain any other Azure services except SQL Managed Instance.Sufficient IP addresses: The SQL Managed Instance subnet must have at least 16 IP addresses and a minimum of 32 IP addresses for deployment. These IP addresses are reserved for virtual cluster resources and may vary depending on the hardware generation and SQL Managed Instance service tier. Visit https://docs.microsoft.com/azure/azure-sql/managed-instance/vnet-subnet-determine-size to determine the SQL Managed Instance subnet size.Subnet delegation: The SQL Managed Instance subnet needs to be delegated to the Microsoft.Sql/managedInstances resource provider.Network security group (NSG): SQL Managed Instance requires port 1433 for TDS traffic and ports in the range 11000-11999 for redirection connection. An NSG must be associated with the SQL Managed Instance subnet.A user-defined route (UDR) table: A prerequisite is to create a route table that will allow SQL Managed Instance to communicate with the Azure Management Service.

These conditions are mandatory for SQL Managed Instance creation and management operations.

Note

A detailed explanation of the networking requirements can be found by visiting https://docs.microsoft.com/azure/azure-sql/database/connectivity-architecture.

Differences between SQL Database, SQL Managed Instance, and SQL Server

SQL Database and SQL Managed Instance are PaaS offerings from the Azure SQL family and therefore some of their features differ from the on-premises SQL Server. Some of the important features that differ are as follows:

Backup and restore

SQL Database

Conventional database backup and restore statements aren't supported on SQL Database. Backups are automatically scheduled and start within a few minutes of the database being provisioned. Backups are consistent, transaction-wise, which means that you can do a point-in-time restore.

There is no additional cost for backup storage until the amount stored goes beyond 100% of the database's size.

You can reduce the backup retention period to manage backup storage costs. You can also use the long-term retention period feature to store backups in a separate Azure blob container for a much lower cost for up to 10 years.

Apart from automatic backups, you can also export the Azure SQL Database bacpac or dacpac file to Azure Storage.

SQL Managed Instance

SQL Managed Instance automatically creates database backups that are kept for the duration of a specified retention period. Native COPY_ONLY backups on Azure blob containers are allowed on SQL Managed Instance.

Backup storage is free as an equal amount of storage is reserved for SQL Managed Instance, regardless of the backup retention period.

The long-term retention period for SQL Managed Instance is a limited preview feature currently (and will be in public preview soon) and is only available for EA and CSP subscriptions.

Recovery model

The default recovery model for SQL Database and SQL Managed Instance is FULL, and it can't be modified to any other recovery model as in on-premises recovery models.

The recovery model is set when the master database is created, meaning when an Azure SQL Server is provisioned, the recovery model can't be modified because the master database is read-only.

To view the recovery model of an Azure SQL Database, execute the following query:

SELECT name, recovery_model_desc FROM sys.databases;

Note

You can use either of the two methods discussed earlier in the chapter to run the query – the Azure portal or SSMS.

You should get the following output:

Figure 1.32: Recovery model of an SQL database

SQL Server Agent

SQL Database doesn't have SQL Server Agent, which is used to schedule jobs and send success/failure notifications. However, you can use the following workarounds:

Create an SQL Agent job on an on-premises SQL server or on an Azure VM to connect and run on the SQL Database.Azure Automation allows users to schedule jobs in Microsoft Azure to automate manual tasks. This topic is covered in detail later in the book.Elastic Jobs is an Azure service that allows the scheduled execution of ad hoc tasks. This topic is covered in detail later in the book.Use PowerShell to automate a task and schedule PowerShell script execution with Windows Scheduler, on-premises, or Azure VM.

Azure SQL Managed Instance has SQL Server Agent and can be used to schedule jobs just like with on-premises SQL Server. However, some of the actions are not allowed, such as enabling and disabling SQL Server Agent, and the SQL Server Agent process is always in the running state.

For more information, please visit https://docs.microsoft.com/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server#sql-server-agent.

Change Data Capture

Change Data Capture (CDC) allows you to capture data modifications to CDC-enabled databases and tables. The CDC feature is important in incremental load scenarios, such as incrementally inserting changed data to a data warehouse from an online transaction processing (OLTP) environment. CDC requires SQL Server Agent and therefore isn't available in SQL Database. However, you can use the temporal table, SQL Server Integration Services (SSIS), or Azure Data Factory to implement CDC. CDC is supported in SQL Managed Instance.

Auditing

Audit logs are available for both SQL Database and SQL Managed Instance but with a few differences from on-premises SQL Server. In PaaS, file system-level access is not granted, hence audit logs need to be captured on Azure Blob Storage.

Mirroring

You can't enable mirroring between two SQL Databases, and the same goes for SQL Managed Instance databases. You can set up a readable secondary for an SQL Database and a failover group for SQL Managed Instance, which is better than mirroring.

Table partitioning

Table partitioning using a partition scheme and partition functions is allowed in SQL Database; however, because of the PaaS nature of the SQL Database, all partitions should be created on a primary filegroup. You won't get a performance improvement by having partitions on different disks (spindles); however, you will get a performance improvement with partition elimination.

In SQL Managed Instance, partitions can be created with different filegroups and files for each partition, meaning better performance by having multiple files per database.

Replication

Conventional replication techniques, such as snapshot, transactional, and merge replication, can't be done between two Azure SQL Databases. However, an SQL Database can be a subscriber to an on-premises or Azure VM SQL Server instance.

However, this too has limitations. It supports one-way transactional replication, not peer-to-peer or bi-directional replication; it supports only push subscription.

Note that you should have SQL Server 2012 or above when on-premises. Replication and distribution agents can't be configured on SQL Database.

SQL Managed Instance supports snapshot, transactional, and bi-directional transactional replication. Merge replication, peer-to-peer replication, and updatable subscriptions are not supported.

The publisher and distributor need to be configured on both SQL Managed Instance and on-premises SQL Server.

Multi-part names

Multi-part names and cross-database queries are supported on SQL Managed Instance.

For SQL Database, three-part names (databasename.schemaname.tablename) are only limited to tempdb, wherein you access a temp table as tempdb.dbo.#temp. For example, if there is a temporary table, say, #temp1, then you can run the following query to select all the values from #temp1:

SELECT * FROM tempdb.dbo.#temp1

You can't access the tables in different SQL Databases in Azure on the same Azure SQL Server using three-part names. Four-part names (ServerName.DatabaseName.SchemaName.TableName) aren't allowed at all.

You can use an elastic query to access tables from different databases from an Azure SQL Server. Elastic queries are covered in detail later in the book. You can access objects in different schemas in the same Azure SQL Database using two-part (Schemaname.Tablename) names.

To explore other T-SQL differences, visit https://docs.microsoft.com/azure/sql-database/sql-database-transact-sql-information.

SQL Server Browser

SQL Server Browser is a Windows service that provides instance and post information to incoming connection requests. This isn't required because SQL Database and SQL Managed Instance listen to port 1433 only.

FileStream

SQL Database and SQL Managed Instance don't support FileStream or FileTable, just because of the PaaS nature of the service. There is a workaround to use Azure Storage; however, that would require a re-working of the application and the database.

Common Language Runtime (SQL CLR)

SQL CLR is supported on SQL Managed Instance with a few differences. SQL CLR allows users to write programmable database objects such as stored procedures, functions, and triggers in managed code. This provides a significant performance improvement in some scenarios. This feature is not available in SQL Database.

Resource Governor

Resource Governor is supported on SQL Managed Instance. Resource Governor allows you to throttle/limit resources (CPU, memory, and I/O) for different SQL Server workloads. This feature is not available in SQL Database.

SQL Database comes with different service tiers, each suitable for different workloads. You should evaluate the performance tier your application workload will fit into and accordingly provision the database for that performance tier.

Global temporary tables

Local and global instance-scoped temporary tables are supported on SQL Managed Instance.

Global temporary tables are defined by ## and are accessible across all sessions. These are not supported in SQL Database.

Local temporary tables are allowed. Global temporary tables created with ## are accessible across all sessions for a particular database. For example, a global temporary table created in database DB1 will be accessible to all sessions connecting to database DB1 only.

Log shipping

Log shipping is the process of taking log backups on a primary server and copying and restoring them on a secondary server. Log shipping is commonly used as a high-availability or disaster-recovery solution, or to migrate a database from one SQL instance to another. SQL Database and SQL Managed Instance have built-in high availability and configurable business continuity features. Log shipping isn't supported by SQL Database and SQL Managed Instance.

SQL Trace and Profiler

SQL Profiler is supported on SQL Managed Instance. SQL Trace and Profiler can't be used to trace events on SQL Database. Currently, there isn't a direct alternative other than using dynamic management views (DMVs), monitoring using the Azure portal, and extended events.

Trace flags

Only a limited set of global traces is supported on SQL Managed Instance. Trace flags are special switches used to enable or disable a particular SQL Server functionality. These are not available in SQL Server.

System stored procedures

SQL Managed Instance supports nearly all system stored procedures. SQL Database doesn't support all the system stored procedures supported in an on-premises SQL Server. System stored procedures such as sp_addmessage, sp_helpuser, and sp_configure aren't supported. In a nutshell, procedures related to features unsupported in SQL Database aren't supported.

The USE statement

The USE statement is used to switch from one database context to another. This isn't supported in SQL Database, but SQL Managed Instance supports the USE statement.

Exercise: Provisioning an Azure SQL Managed Instance using the Azure portal

In this exercise, we'll provision and connect to an SQL Managed Instance. We'll also learn about virtual network support in SQL Managed Instance.

To provision an SQL Managed Instance, perform the following steps:

Log in to https://portal.azure.com using your Azure credentials.Click on +Create a resource:

Figure 1.33: Creating a new resource

Search for Azure SQL in Azure Marketplace:

Figure 1.34: Searching for Azure SQL in Marketplace

Select the Create option; you can see more details by clicking on the Show details option:

Figure 1.35: Selecting an SQL deployment option

In the Basic tab, provide the information shown in Figure 1.36:

Figure 1.36: Information required to add the SQL Managed Instance

In the Subscription box, provide your Azure subscription type. SQL Managed Instances currently support the following subscription types: Enterprise Agreement (EA), Pay-As-You-Go, Cloud Service Provider (CSP), Enterprise Dev/Test, Pay-As-You-Go Dev/Test, and subscriptions with monthly Azure credit for Visual Studio subscribers.

If you have a different subscription, you won't be able to create an SQL Managed Instance.

In the Resource Group box, choose to create a new or use an existing resource group for the SQL Managed Instance. A resource group is a logical container for all the resources in Azure.

The Managed instance name box is for the name of the managed instance you plan to create. It can be any valid name, in accordance with the naming rules at https://docs.microsoft.com/azure/architecture/best-practices/naming-conventions.

For the Region box, select the desired Azure region for the SQL Managed Instance deployment. In general, apps and managed instances should be deployed in the same Azure region to avoid network latency.

The Managed instance admin login box is for any login name, as long as it fits the naming conventions at https://docs.microsoft.com/azure/architecture/best-practices/naming-conventions.

The password can be any password that follows these rules:

Figure 1.37: Password requirements

Select Configure Managed Instance to choose compute and storage resources. Use sliders to choose the vCore and storage size.

Select Azure Hybrid Benefits if you already have SQL Server licenses with software assurance. In general, this can be used while migrating from an on-premises SQL Server to SQL Managed Instance.

Select the Backup Storage type; Geo, Zone, and Locally-redundant backup storage options are available. Choose this based on the desired recovery plan as it can't be changed after instance deployment. Read more about backup storage types at https://docs.microsoft.com/azure/azure-sql/database/automated-backups-overview?tabs=single-database.

When you are finished, select Apply to save changes:

Figure 1.38: Compute + storage

After the instance resources configuration selection, move to the Networking tab to configure Virtual network / subnet for the SQL Managed Instance:

Figure 1.39: Moving to the Network tab to set Virtual network / subnet

The Virtual network / subnet box is for setting the virtual network/subnet that the managed instance will be part of. If no network is provided, a new virtual network/subnet is created.

For the Connection type box, SQL Managed Instances support two connection types: Redirect and Proxy. Redirect is the recommended connection type because the client directly connects to the node hosting the database, and therefore it offers low latency and high throughput.

For the Proxy connection type, requests to the database are proxied through the SQL Database gateways.

Enable Public endpoint to allow SQL Managed Instance connectivity over the internet. By default, it's disabled until explicitly enabled.

Choose Minimal TLS Version to enforce a TLS version for the managed instance's inbound connection.

Fill out all the details in the Additional settings tab:

Figure 1.40: The Additional settings tab

Collation is the SQL Server collation that the managed instance will be in.

The Time zone box denotes the time zone of the managed instance. The preferred time zone is UTC; however, this will differ from business to business.

Select Geo-Replication to use this managed instance as a secondary instance in a failover group.

In the Review + create tab, review your selection before you create the managed instance:

Figure 1.41: The Review + create tab

Click Create to validate and provision the SQL Managed Instance.

To monitor the progress, click the Notifications (bell) icon in the top-left corner:

Figure 1.42: Notifications icon in the instance window

As we can see, the deployment is in progress:

Figure 1.43: The Notifications pane

After the deployment is complete, a deployment complete notification will come up in the notification window:

Figure 1.44: Deployment complete notification

Activity: Provisioning Azure SQL Server and SQL Database using PowerShell

This section discusses provisioning Azure SQL Server and SQL Database using PowerShell. To understand the process, let's take the example of Mike, who is the newest member of the data administration team at ToyStore Ltd., a company that manufactures toys for children. ToyStore has an e-commerce web portal that allows customers to purchase toys online. ToyStore has migrated the online e-commerce portal to Microsoft Azure and is therefore moving to Azure SQL Database from an on-premises SQL Server. Mike is asked to provision the Azure SQL Database and other required resources as his initial assignment. This can be achieved by following these steps:

Note

If you are short of time, you can refer to the C:\Code\Chapter01\Provision-AzureSQLDatabase.ps1 file. You can run this file in the PowerShell console instead of typing the code as instructed in the following steps. Open a PowerShell console and enter the full path to execute the PowerShell script. You'll have to change the Azure resource group name, the Azure SQL Server, and the Azure SQL Database name in the script before executing it.

Save the Azure profile details into a file for future reference. Press Windows + R to open the Run command window.In the Run command window, type powershell and then press Enter. This will open a new PowerShell console window:

Figure 1.45: Opening up PowerShell

In the PowerShell console, run the following command:

Add-AzAccount

You'll have to enter your Azure credentials into the pop-up dialog box. After a successful login, the control will return to the PowerShell window.

Run the following command to save the profile details to a file:

Save-AzProfile -Path C:\Code\MyAzureProfile.json

The Azure subscription details will be saved in the MyAzureProfile.json file in JSON format. If you wish to explore the JSON file, you can open it in any editor to review its content:

Figure 1.46: Saving the Azure credentials

Note

Saving the profile in a file allows you to use the file to log in to your Azure account from PowerShell instead of providing your credentials every time in the Azure authentication window.

Press Windows + R to open the Run command window. Type PowerShell_ISE.exe in the Run command window and press Enter. This will open a new PowerShell ISE editor window. This is where you'll write the PowerShell commands:

Figure 1.47: Run command window

In the PowerShell ISE, select File from the top menu, and then click Save. Alternatively, you can press Ctrl + S to save the file. In the Save As dialog box, browse to the C:\Code\Chapter01\ directory. In the File name textbox, type Provision-AzureSQLDatabase.ps1, and then click Save to save the file:

Figure 1.48: Saving the PowerShell ISE file

Copy and paste the following lines in the Provision-AzureSQLDatabase.ps1 file one after another. The code explanation, wherever required, is given in the comments within the code snippet.Copy and paste the following code to define the parameters:

param (

[parameter(Mandatory=$true)] [String] $ResourceGroup, [parameter(Mandatory=$true)] [String] $Location, [parameter(Mandatory=$true)] [String] $SQLServer, [parameter(Mandatory=$true)] [String] $UserName, [parameter(Mandatory=$true)] [String] $Password,

[parameter(Mandatory=$true)] [String] $SQLDatabase, [parameter(Mandatory=$true)] [String] $Edition="Basic", [parameter(Mandatory=$false)] [String] $AzureProfileFilePath

)

The preceding code defines the parameters required by the scripts:

ResourceGroup: The resource group that will host the logical Azure SQL Server and Azure SQL Database.

Location: The resource group location. The default is East US 2.

SQLServer: The logical Azure SQL Server name that will host the Azure SQL Database.

UserName: The Azure SQL Server admin username. The default username is sqladmin. Don't change the username; keep it as the default.

Password: The Azure SQL Server admin password. The default password is Packt@pub2. Don't change the password; keep it as the default.

SQLDatabase: The Azure SQL Database to create.

Edition: The Azure SQL Database edition. This is discussed in detail in Chapter 3, Migration.

AzureProfileFilePath: The full path of the file that contains your Azure profile details. You created this earlier in the activity.

Copy and paste the following code to log in to your Azure account from PowerShell: