The first mention of Azure SQL, referred to as “Microsoft SQL Services” at the time, was when Microsoft announced “Project Red Dog” (Microsoft Azure) at Microsoft’s Professional Developer Conference (PDC) in 2008. This cloud-hosted version of SQL Server was one of the first services available on Azure. From the beginning, it was meant to provide a Platform as a Service (PaaS) offering of SQL Server, where you don’t have to provision hardware or patch software and you can enjoy benefits that come with running in the cloud – availability, performance, security, and scale – without deploying complex and expensive systems on premises. Since then, Azure SQL has continued to grow and evolve with the Azure platform. As you read this book, it’s important to remember that things are always changing. While some of the “little rocks” or details throughout the book may change slightly as the platform evolves, the “big rocks” you will learn in this book will provide a solid foundation for you to grow in the space of developing with databases.
For readers who are new to Azure or new to Azure SQL, this chapter will serve as a kickstart to get you ramped up and ready to start building applications with Azure SQL. You’ll learn about the flexibility in deployment that exists today in Azure SQL, as well as some common scenarios that can help you decide what deployment option, service tier, and other options you should choose. You’ll also learn about some of the tools and samples that are available for working with and managing Azure SQL.
While (or after) you read this chapter (and the chapters that follow), we recommend you leverage the code samples that are provided with the book, so you can get some hands-on experience with the topics.
The Azure SQL family of offers
OS-level access means that Azure handles everything until you get to the OS level. The data center, hardware, virtualization, and so on are managed by Microsoft, but the OS, SQL Server, and database(s) are managed by you. You essentially get an Azure Virtual Machine with SQL Server installed. This is often referred to as an Infrastructure as a Service (IaaS) type of offering. With IaaS, while you are responsible for managing and patching the OS, SQL Server, and database(s), you also get access to all the features contained in those levels. For example, if you want to run a third-party application on the same machine as SQL Server, you can do that with IaaS. Additionally, there are a few features from SQL Server 2019 (latest release at time of this book) that are not yet available in the other deployment options, like PolyBase. If you want 100% parity with SQL Server, an Azure SQL virtual machine may be your best option. For more information, see https://aka.ms/azuresqlvm.
As you move away from scenarios that require OS-level access, and you begin looking for more managed offerings, there are a few Azure SQL options that fall within the Platform as a Service (PaaS) type of offering, and these options will be the focus of this book. With a PaaS service, you get things like automated backups, patching of both the database engine and OS, a service-level agreement (SLA) for availability, advanced security, monitoring, and scalability out of the box. For developers, this means you can get a connection string, stop worrying about maintenance, and spend less time troubleshooting.
For example, let’s imagine you don’t want to have to worry about OS patches and SQL Server upgrades. You just want an instance of SQL Server. You might opt for Azure SQL managed instance, which abstracts the OS away from you, leaving you with an evergreen managed instance of SQL Server. Here, you can get almost all of the instance-level features; for example, SQL Server Agent, Service Broker, Linked Servers, and Common Language Runtime are supported. This deployment option is ideal for companies that want to move to a PaaS offering, but still need to control and access many of the server-level pieces. Azure SQL managed instance also offers native virtual network support, which allows you to integrate the service into your on-premises network if desired.
Now, consider a scenario where your company is in the process of transforming into a cloud-native entity or one where you are working in a cloud-born company. Perhaps you are not interested in managing SQL Server at an instance level and do not need the server-level features. Even more, you may not even know what SQL Server is, as you may never had the chance or the need to use it on premises. What you are looking for, then, is the next layer of abstraction that Azure SQL can offer, which is Azure SQL Database. In this offering, you get all the PaaS benefits that Azure SQL managed instances give you, but you just get a database and the database-level capabilities. For this deployment option, you’ll have a logical server for organizational and administrative purposes, but no access to instance-level features or views.
When choosing between Azure SQL managed instance and Azure SQL Database, there are a few additional trade-offs you can consider determining what is best for an application’s requirements. One way to think about Azure SQL managed instance vs. Azure SQL Database is like the way you think about virtual machine vs. a container. If you use a virtual machine (VM) to deploy an application, you have more power, disks, and security services, but you can only have 2–10 VMs per host. On the other hand, if you use containers, you get lighter and faster with greater elasticity and hundreds of containers per host. If you apply that train of thought to Azure SQL, with Azure SQL managed instance, you get more features and control, but with Azure SQL Database, you get faster, smaller, and with potentially lower costs. Let’s consider the trade-offs.
First, you may consider costs. While Azure SQL Database and Azure SQL managed instance are priced the same for storage and compute, if you are going to have just a few databases that consume less than the minimum configuration for Azure SQL managed instance, then Azure SQL Database may be cheaper, as it allows a smaller minimum configuration (lower cost). Azure SQL Database is also able to guarantee resources for a specific database because no sharing is occurring with other databases as in Azure SQL managed instances. You might also consider speed and elasticity. Since Azure SQL Database is deployed in the secure public cloud, Microsoft can provide provisioning and scaling in a matter of minutes or less. An Azure SQL managed instance is deployed in complete isolation (which has security benefits), but the result is that provisioning and scaling can take a matter of hours. From a feature perspective, Azure SQL managed instance allows access to instance-level features, which, if you are not using today, you may want the option to use later, and allows customizations for time zone and collations (covered later in this chapter) that you don’t get with Azure SQL Database.
Moving forward, let’s look at a scenario where you have many databases or many instances of SQL Server. Perhaps you are a Software as a Service (SaaS) provider where your software serves multiple customers, meaning you need multi-tenancy. Or perhaps you have many different SQL Servers that you want to move into the PaaS offering. So far, we’ve looked at scenarios for single databases or single instances, but for these scenarios, you may consider the “pools” options, which allow you to have multiple databases or servers that are collocated and share resources. This can allow for cost optimization and reduced overhead. For multiple databases, you may consider Elastic Pools , and for multiple instances, you may consider Instance Pools .
While there are a lot of choices, the Azure SQL platform has the flexibility to meet the requirements that you have. Throughout the book, the capabilities and nuances that exist between different PaaS deployment options will be explored at a deeper level, and it will be done so from the perspective of developers for developers.
If you’re completely new to Azure SQL, all these options could scare you, but don’t fear. If you want to start easy with something that in 90% of the cases will give you exactly what you need, you can start with Azure SQL Database. But rest assured that all the other options are there for you to make sure that even the most exotic and demanding edge cases you will encounter as a developer will be covered.
Creating Azure SQL Database
In order to create a single database (or managed instance, Elastic Pool, or Instance Pool), you first need a few basics with respect to Azure. First, you’ll need an Azure account and a way to pay for services. If you haven’t created a free account before with Azure, you can go to https://azure.com/free and get a one-time credit. This should be enough for you to complete most, if not all, the hands-on exercises in the book. If you have already used your free account, you may have an Azure credit with a set monetary value each month if you have a Visual Studio subscription that supports that. If neither of those are options, you’ll need to use or obtain an Azure account where you have access to create and manage resources.
Set up Azure CLI and create a resource group
Listing 2-2.
Create an Azure SQL Database server
Create an Azure SQL Database
Create a database with a sample
This will take a few minutes to deploy, but once it’s finished, you’ll have an Azure SQL Database with the AdvenureWorksLT sample restored on it.
Create firewall rules
You should now be able to connect to your Azure SQL Database from an allowed IP address from your tool of choice (more on tools soon).
The process and commands for creating an Azure SQL managed instance vary slightly, because with a managed instance, you create a “managed” SQL Server (not a logical server) and then you create “managed” databases.
Before deploying, you’ll need to address new parameters for the VNetName and SubnetName. This is because Managed Instance is required to be deployed in an Azure virtual network in its own subnet or in a subnet with other managed instances only. The easiest way to set this up is by using the Azure Resource Manager (ARM) deployment template available here: https://aka.ms/sqmicvs. An ARM template is a JSON file that uses declarative syntax to define infrastructure and configuration to be deployed. There are many ARM templates available to get started building simple or complex solutions in Azure (https://aka.ms/armtemplates). Using the ARM template, you can simply select the “Deploy to Azure” button and fill in your subscription and resource group information, and it will deploy the virtual network and subnet that is required for a managed instance.
Azure CLI commands to create Azure SQL managed instances
Tools to use and manage Azure SQL Database
In the previous section, you saw how you can use the Azure CLI to deploy and manage Azure SQL. Depending on what you’re trying to accomplish and your preferences, there are other tools available for you to leverage. For example, if you’re more familiar with PowerShell, you can get about the same functionality as with the Azure CLI. Since SQL Server and Azure SQL have been around for 25+ and 10+ years respectively, there are also many command-line tools available. And if you’re looking for a tool that provides a graphical user interface (GUI), there are several for you to choose from. In this section, you’ll get an overview of what’s available from GUI to CLI.
There are about five main tools that provide a GUI, the most popular being SQL Server Management Studio (SSMS). This tool has been around for 20+ years and can be used to access, manage, and develop components of SQL Server and Azure SQL. It contains graphical tools and script editors.
Azure Data Studio (ADS) is a newer, source-open tool that also provides a GUI and much of the functionality that SSMS has. ADS was built on top on Visual Studio Code, so if you’re familiar with that, you’ll feel comfortable in ADS. ADS is also cross-platform and supported on Windows, macOS, and Linux. When choosing between SSMS and ADS, it’s often a preference or a matter of functionality. For example, if you want to leverage Jupyter to create SQL, PowerShell, Python, and so on notebooks (a notebook is most simply explained as a mixture of code cells that can be run with results saved and formatted text cells), then ADS is the only tool that currently supports that. Similarly, there are things that you’ll find in SSMS and not ADS. Microsoft is actively investing in both tools, so expect them to continue to evolve.
There are also two extensions directly linked to Visual Studio, SQL Server Data Tools (SSDT) and mssql. SSDT is a tool for designing and deploying many things related to SQL, including Azure SQL, and it gives a similar experience to developing applications with Visual Studio. Similarly, the mssql extension for Visual Studio Code allows you to leverage the lightweight editor to connect to databases and instances with a good editing experience.
The fifth tool that provides a GUI for management and development is the Azure portal. From deployment to integration with other applications and services, the Azure portal is an easy, user-friendly way to build applications with little coding required.
mssql-cli is an open source command-line tool specifically for querying, with interactivity and helpful features like IntelliSense and syntax highlighting (https://aka.ms/mssqlcli).
The sqlcmd utility allows you to use the command prompt for T-SQL statements, script files, and system procedures.
The bulk copy program utility (bcp) is used specifically for bulk copying data.
mssql-scripter can be used to help with creating, editing, and storing scripts (http://aka.ms/mssqlscripter).
sqlpackage automates tasks related to extracting, publishing, exporting, and importing live databases, database schemas, and user data (http://aka.ms/sqlpackage).
Some of the tools are built for more broad usage. For example, the Azure CLI and the SQL Server PowerShell snap-in provide commands for accessing, deploying, and managing Azure SQL. It’s almost certain that you will not just use one GUI tool or one CLI tool, but a combination of both (and even multiple within the categories). As you’ll see in other chapters, there are things that will be easier to do with varying tools.
Throughout the book, you’ll be exposed to the different tools more in depth. If you ever want the reference materials for any of the tools, here’s a useful link that will guide you: https://aka.ms/sqltools.
Choices, choices, choices
Once you have an idea of which deployment option of Azure SQL is best for your scenario (Azure SQL Database single database or Elastic pool, or Azure SQL Managed Instance single instance or Instance pool), there are many other choices that you can make to tailor the solution to meet your needs. In an earlier section, you saw how to deploy an Azure SQL Database using Azure CLI, but many of the defaults were selected. In this section, you’ll learn about all the choices and configurations you can make during deployment and some guidance on how to choose. This section follows the order and flow presented currently in the Azure portal, but the order and specifics may change depending on your deployment tool of choice (Azure portal, Azure PowerShell, Azure CLI).
A quick recap
In the first section of this chapter, “Creating Azure SQL Database,” some of the Azure basics were covered, including Azure subscriptions, resource groups, regions, and logical servers for Azure SQL Database. To review, you’ll need an Azure subscription to do anything in Azure (even if it’s free), resource groups are logical containers or metadata entities for the resources or services you deploy in Azure, the region is where your services will be deployed, and the logical server acts as a central administrative point for multiple single or pooled databases and other settings/configurations associated with that logical server. For Azure SQL managed instance, the server name you select is not a logical server, but the actual service, which you can then deploy databases into (sometimes referred to as “managed databases”). In all deployment options, you’ll then have to provide an admin login and password. If you’re familiar with SQL Server, this is the equivalent to the system admin in SQL Server. This account will connect using a username and password that you provide, and only one of these accounts can exist. If you’re familiar with Azure Active Directory, you can also choose to configure an individual or security group account here or after deployment (optional).
DTU or vCores
After you’ve set up the admin login, when deploying Azure SQL Database, your next choice is if you want the database being deployed to be part of a new or existing Elastic Pool. If you do, you’ll then have to configure the service tier, storage, and compute options for the Elastic Pool. Note that in Elastic Pools, you share resources between all the databases in the pool. You can also resource govern among databases in the pool if you want to limit certain databases. Similarly, if you’re deploying an Instance Pool, you’ll need to use a different Azure portal flow or set of commands, as you first need to deploy the Instance Pool, before you can deploy Managed Instances inside of it.
Next, you’ll select what’s referred to as the purchasing model , and there are two options: virtual core (vCore)-based or database transaction unit (DTU) -based. The DTU model is not available in Azure SQL managed instances. The DTU-based model is an early purchasing model within Azure SQL Database. The idea is that you get a bundled measure of compute, store, and I/O resources (that is a DTU), and you pick the DTU level that meets your needs. One of the challenges with this model is you cannot scale independently between the three, and it’s hard to determine what you are getting. The vCore-based model was introduced due to customer requirements to independently choose compute and storage resources. This is the recommended model from Microsoft. In addition, using the vCore model allows you to leverage Azure Hybrid Benefit for SQL Server to gain significant cost savings. This benefit allows you to apply existing unused licenses you may own already in return for big discounts. In the vCore model, you pay for compute resources (based on the service tier, number of vCores, amount of memory, and the hardware generation), the type and amount of data and log storage, and the backup storage.
In this book, we’ll mostly focus on the vCore purchasing model since it’s the recommended and modern model from Microsoft, but if you’re interested in further comparing vCores and DTUs, you can refer here: https://aka.ms/sdpm.
Service tiers
Once you select the purchasing model, you have options underneath that are called service tiers . These service tiers represent different tiers of performance and availability. The tiers available include General Purpose, Business Critical, and Hyperscale. It’s important to carefully consider your options, but a general recommendation would be to start with the General Purpose tier and adjust as needed.
The General Purpose tier offers a budget-oriented, balanced, and scalable set of compute and storage options. Within the General Purpose service tier for an Azure SQL Database (single database), there currently exists two options for selecting compute: provisioned and serverless. Provisioned compute is meant for more regular usage patterns with higher average compute utilization over time or multiple databases using elastic pools. This is the tier you may want to start with. Serverless compute is meant for intermittent, unpredictable usage with lower average compute utilization over time.
For example, consider an expense-reporting internal business application that typically only gets used during business hours, but occasionally sees spikes in usage after big internal conferences or at the end of the month. This means that every night, between 5PM and 8AM, there is little to no activity happening. However, after big conferences and at the end of the month, significant activity occurs. You then must make the choice of overprovisioning to meet the spikes in demand or underprovisioning to keep costs low for the relatively quiet application. With serverless, you can leverage auto-pause and resume capabilities , with a customizable time delay. This means that when your database is inactive for a certain amount of time, your database will pause. During this time, you only pay for storage, and your compute costs go to zero. Once someone tries to access the application again, SQL will turn back on, and your compute will scale on a per-second basis between the minimum and maximum vCores that you set. In this example, when demands are high, Azure SQL Database can autoscale up to what’s required and then use an interesting cache reclamation system to scale back down once the demand decreases. Remember, serverless is currently only available in Azure SQL Database single databases, but check the documentation for most up-to-date information at https://aka.ms/sdserverless.
Currently, apart from the Azure SQL Database General Purpose tier, all other tiers and deployment options only have the choice of provisioned compute. Once you choose a service tier, you can scale up and down as needed and even switch between service tiers. The next service tier that’s available in all deployment options is Business Critical. This is meant specifically for business applications with low-latency response requirements. This tier also offers the highest resiliency to failures by leveraging several isolated replicas (one of which you can also use as a read-only replica). Some industries that tend to leverage this tier include gaming, emergency, and financial. While Azure SQL Database generally has a high service-level agreement that sets the expectation for uptime and performance (SLA), Business Critical can guarantee the highest SLA at 99.995% uptime. For more information about the Azure SQL SLA, refer here: https://aka.ms/sdsla.
The Business Critical tier is also the only service tier within Azure SQL that supports in-memory technologies. In-memory technologies will be covered at a much deeper level in Chapter 9. At a high level, in-memory technologies can help improve the performance of various SQL workloads (analytic and transactional), and the Business Critical tier supports in-memory OLTP, clustered columnstore indexes, nonclustered columnstore indexes, and memory-optimized clustered columnstore indexes.
The General Purpose and Business Critical tiers currently support database size of maximum 4–8TB (depending on deployment option and service tier). The final service tier available within Azure SQL Database is Hyperscale, which was built to take advantage of cloud elasticity and to allow a database to grow almost without limits, freeing the developer from all encumbrances of managing huge databases. Microsoft rearchitected Azure SQL to be able to run in a Hyperscale fashion by refactoring the original SQL Server engine into different, smaller, independent, distributed services that work together as one entity, but much more elastic and scalable than the original. This service tier is meant for most business workloads that may need in future highly scalable storage, up to 100TB at the moment. Hyperscale also supports deploying multiple read replicas for read-intensive workloads, where data is served by all replicas, leveraging a scale-out strategy to increase concurrency and performances. Because the file snapshots are stored in Azure Blob storage, Hyperscale supports “nearly instantaneous” database backups and restores in minutes, because it’s no longer a size of data operation. As you can guess, this feature is topical in the management of very large databases. Hyperscale can provide higher overall performance than the General Purpose tier due to higher log throughput and faster transaction commit times (regardless of the volume of data). If you’re interested in how Microsoft architected this and what the architecture changes enabled, you can review additional details here: https://aka.ms/sdsth.
To summarize, the recommendation from Microsoft has been to start with General Purpose and adjust as needed. With the elasticity of the cloud, you can then scale up or down the resources as needed, and if the performance is not good enough, you can move to Business Critical or Hyperscale. Remember that performance is higher in Hyperscale but highest in Business Critical, and cost typically follows the same pattern. For a detailed, current comparison between the service tiers, refer here: https://aka.ms/sdbsth.
Hardware generations
Once you’ve selected a service tier, depending on the deployment option, the vCore model provides multiple choices for the hardware generation . The hardware generation selection allows you to select which physical hardware you want to be used for compute and memory. The most common hardware generations used today are Gen4 and Gen5. Without getting too much into the specs, Gen4 hardware allows substantially more memory per vCore, but Gen5 hardware allows you to scale compute resources much higher. It’s important to note that hardware generations available will constantly change and evolve as the technology improves. Gen4, for example, has been around for a long time and is no longer available for new databases in certain regions. Additionally, Fsv2-series (compute-optimized) and M-series (memory-optimized) hardware options recently became available for public usage. Another example is that Azure SQL Database serverless is currently only available on Gen5 hardware. Your requirements for cost and performance may make a certain generation of hardware a better choice. You can review the most current choices and specs of hardware here: https://aka.ms/sdstvhg.
If you’re looking for other compute cost-savings opportunities, one option to consider is pre-paying for compute resources at a discount with Azure SQL Database reserved capacity (RI). For more information, see https://aka.ms/sdri.
If you are building a new application and starting from an empty database or instance, Azure SQL’s elasticity allows you to grow and scale your compute and storage as needed. If you happen to be migrating an existing workload to Azure SQL Database or managed instance, translating that workload to a service tier, compute level, and max data size can be complicated. Fortunately, Microsoft has built a few tools that can help with this. First, you can leverage the Data Migration Assistant (DMA) tool to identify any blockers and determine which deployment option is best. Specifically related to the deployment choices, the DMA tool also has a feature called SKU Recommender , which analyzes your existing workload and recommends the service tier, compute level, and max data size. This tool provides an estimated cost per month and creates a PowerShell script you can use to bulk provision after analysis.
Networking
After you’ve selected your starting points for compute and storage, the next step is to configure the network connectivity. Choices for networking for Azure SQL Database (single database and Elastic pool) and Azure SQL managed instance (single instance and Instance pool) are different. When you deploy an Azure SQL Database, currently the default in the portal is that the "Allow Azure services and resources to access this server" blade is set to yes, meaning that other Azure services (e.g., Azure Data Factory or an Azure VM) can access the database if you set up the connection. Additionally, after deployment, you can alter the Azure SQL Database firewall rules to meet the requirements of your application.
With Azure SQL managed instances, you deploy the service inside an Azure virtual network (VNet) and a subnet that is dedicated to managed instances. VNets are like networks you’d deploy to your on-premises environments, but specific to Azure. A subnet is simply a way to create segments within a VNet. By deploying Azure SQL Managed Instance into a managed instance-only subnet within a VNet you own, you’re enabled to have a completely secure and private IP address. This ability allows you to connect your on-premises network or other on-premises data stores to Azure SQL managed instance (e.g., using linked servers is supported). You can optionally enable a public endpoint so you can connect to a managed instance from the Internet without VPN, but this access is disabled by default.
The principle of private endpoints through VNet isolation in Azure SQL managed instance has made its way to Azure SQL Database in the form of Azure Private Link: https://aka.ms/aplo.
Connection methods
During deployment, in Azure SQL managed instance, you're also able to choose the connection type. In Azure SQL Database, you can also choose the connection type, but only after deployment. You can keep the default (combination of Proxy and Redirect) or change to solely Redirect or Proxy. At the highest level, in Proxy mode, all connections are proxied through the Azure SQL gateways, but in Redirect mode, after the connection is established leveraging the gateway, the connection goes directly to the database or managed instance. The direct connection (Redirect) allows for reduced latency and improved throughput, but also requires opening additional ports for services external to Azure, which is why it is not entirely enabled right away; opening additional ports is something that needs to be evaluated with respect to security risks that decision implies, and therefore Azure resorts to the most secure option by default.
As discussed in an earlier section, during deployment of a database (of Azure SQL Database or within an Azure SQL managed instance), you have the option to set up a data source using an existing backup from another Azure SQL Database or managed database in Azure SQL managed instance (they must match; you cannot use a managed database to create a new Azure SQL Database). In Azure SQL Database only, you also have the option to choose an existing sample database (AdventureWorksLT).
Collations
Another choice that you should carefully consider when deploying Azure SQL is the collation. Collations in Azure SQL provide the sorting rules, case, and accent sensitivity properties for the data in your database(s). The collation set will affect the characteristics of many operations in instance or database, so should be set carefully. In Azure SQL managed instance, you can set the server collation upon creation of the instance, but you cannot change it later. This sets the default collation for all the databases in that instance, but you can modify the collations on a database and column level later. In Azure SQL Database, you cannot set the server collation; it’s set at “SQL_Latin1_General_CP1_CI_AS” which is the most common collation. Every collation name is formatted similarly: “SQL” means it is a SQL Server collation (as opposed to a Windows or Binary collation), “Latin1_General” specifies the alphabet and language to use when sorting, “CP1” references the code page used by the collation, “CI” means it should be case insensitive, “AS” means it should be accent sensitive, and there are other options available related to widths, UTF-8, and so on (https://aka.ms/srdc).
Time zones
Time zone setting, like collation setting, is a seemingly small decision that can have a big impact on your application. The Microsoft recommendation is to use Coordinated Universal Time (UTC) for all instances and databases. In Azure SQL Database, you cannot change the time zone; you just get UTC (but no fear, there are plenty of built-in features in any Azure SQL database to manipulate time zones if needed and handle conversion to local time). However, for Azure SQL Managed Instance, choice of time zone during deployment was introduced to address the needs of many customers whose existing applications store and call date and time values with a context of a specific time zone.
Advanced data security
The final choice that is available from the Azure portal deployment (but can also be done with PowerShell) is Advanced data security (ADS) . In the portal, you’re prompted if you want to start a free trial and enable ADS, which provides functionality related to data discovery and classification, vulnerability alerts, and threat detection. For Azure SQL managed instance, ADS can be configured after deployment. You can learn more about ADS here: https://aka.ms/sdads.
As you can see, there are a lot of options available for you to make Azure SQL meet your needs in an efficient and cost-effective manner. Throughout the book, features available in different options will be explored further, giving you additional insights in selecting the best options for any scenario at hand.
Using sample databases
Whether you’re getting started with Azure SQL and T-SQL or you’re a well-seasoned professional, there are always new things to explore. Exploring some new or nuanced features and scenarios requires a database, and leveraging some of Microsoft’s sample databases can help tremendously. Additionally, the Azure SQL and SQL Server community is huge (just search on Twitter for #sqlfamily), and members of the community are constantly contributing new content and samples to explore the latest feature or explain complex topics. Oftentimes, the examples you find online or in books will be tied to one of the sample databases from Microsoft, because they are rich samples that are easy to find and use.
In the first section of this chapter, you learned how to create an Azure SQL Database with a sample database preloaded. In this section, you’ll learn more about the sample databases available and how to get started with them on Azure SQL Database and Azure SQL managed instance.
There are two main sample databases used most frequently, AdventureWorks and WideWorldImporters. The AdventureWorks sample is based around a fictional retail company AdventureWorks Cycles, and WideWorldImporters is a fictitious wholesale company with global trade operations. The AdventureWorks sample database was originally published to work with SQL Server 2008 and the WideWorldImporters with SQL Server 2016. As you saw in the first section of this chapter, using the Azure CLI, you can deploy a “light” version of the AdventureWorks database “AdventureWorksLT”. There are additional database samples: the WideWorldImporters database for the features in standard edition of SQL Server “WideWorldImportersStd” and the WideWorldImporters database for the features in the enterprise edition of SQL Server “WideWorldImportersFull”. Over the years, the samples are generally updated to go with the latest features. Those databases need to be deployed manually, just like for any other existing database that you might already have. Let’s see how to do it.
There are different ways of getting the sample databases into Azure SQL Database vs. Azure SQL managed instance. For Azure SQL Database, you can do that by leveraging a bacpac file (a Windows file that has the extension “.bacpac” and contains a database’s data and schema). You can download the WideWorldImporters file from here: https://aka.ms/wwi10.
If you are not running in the service tier of Business Critical (or Premium in the DTU model), there are some dependencies in the sample that will require you to use the samples ending in “Standard” or “std”, since the General Purpose tier doesn’t support memory-optimized tables. If you are running in Business Critical (or Premium), then you can use all samples (including those ending in “Full”).
Import bacpac file into Azure SQL Database
There is similar functionality available in PowerShell as well as a GUI experience in the Azure portal. This method can work well for migrating databases to Azure SQL Database in cases where the bak file can be exported to a bacpac file.
Create a credential to a backup
View the files in a backup
Restore the database from a URL
This restore process is asynchronous and retriable, meaning that Azure SQL managed instance will keep trying to restore the database in the background even if the connection breaks or some timeout occurs. While this example was tied to WideWorldImporters, the process for restoring a database in Azure SQL managed instance with T-SQL will be the same for other backup files. For more information, refer here: https://aka.ms/sdmirsdq.
If you want to know more
Creating an Azure SQL database or managed instance is a simple operation, compared to on premises, but still requires some choices to be made to make sure you don’t waste your money on things you don’t need. This is more or less the same concept you apply on premises. Big difference is that scaling up is much easier. Keep in mind that in the cloud, resources can be managed just like salt: you can always add more.
Choose the right deployment option in Azure SQL – https://docs.microsoft.com/azure/sql-database/sql-database-paas-vs-sql-server-iaas
What is PaaS? – https://azure.microsoft.com/overview/what-is-paas/
Azure SQL service tiers – https://docs.microsoft.com/azure/sql-database/sql-database-service-tiers-general-purpose-business-critical
Azure SQL Database servers and their management – https://docs.microsoft.com/azure/sql-database/sql-database-servers
Azure SQL Features – https://docs.microsoft.com/azure/sql-database/sql-database-features
Azure SQL Fundamentals – https://aka.ms/azuresqlfundamentals