Introduction & Overview
The course is part of these learning paths
This course explores how to plan and implement data platform resources specifically with regards to Azure SQL offerings. In particular, we will explore the benefits and features of the SQL PaaS offerings along with billing models, service tiers, and high availability options. We'll also cover migration scenarios and provide a demo that migrates a sample database from an on-premise SQL Server to an Azure SQL managed instance using DMS.
If you have any feedback relating to this course, feel free to contact us at email@example.com.
- Get a general understanding of Azure SQL databases
- Learn how to deploy Azure SQL databases
- Understand business continuity and security tie in with SQL databases
- Learn how to scale, upgrade, and partition your databases
- Learn how to migrate a database from an on-premise SQL Server to an Azure SQL instance
- Anyone who wants to learn about Azure SQL Offerings
- Those preparing for Microsoft’s DP-300 exam
To get the most out of this course, you have should a general understanding of the fundamentals of Microsoft Azure. Experience using databases — especially SQL Server — would also be beneficial.
In this first demonstration, I'm going to create an Azure SQL database through the Azure portal. If the Azure SQL icon is on your homepage, as it is here for me, then click that. Else click also on creator resource and then search for Azure SQL database.
I don't have any databases at present, so I'll click on create Azure SQL resource. We are presented with three deployment options. From left to right, SQL databases are the fully managed platform as a service option, which includes serverless. Next is SQL managed instance, and on the far right is running SQL Server on a virtual machine.
The virtual machine or infrastructure as a service deployment has many operating systems and SQL Server options to choose from. It supports several versions of Windows Server and Linux distributions, as well as SQL Server versions starting at 2008.
BYOL stands for bring your own license, and means you can save on costs if you already have an SQL Server license you can use. SQL managed instance lets you deploy a fully managed instance of SQL server. This deployment option can be thought of as halfway between Azure SQL platform as a service and virtual machines. The server aspect is fully managed but you have all the features of SQL server that may not be present in the pairs offering.
We can deploy a single database within SQL databases an elastic pool of multiple databases or configure a database server. I'm going to create a single database. I'll select an existing resource group, give my database an imaginative name of SingleDB, and create a new server. The server name has to be unique within the databases.windows.net domain. And I'll create a server admin user with a password and select West US 2 as the location for my database server. We can also create an elastic poll at the stage but I'm gonna leave that as no as this is a basic deployment.
The configured database link under compute and storage allows you to choose the virtual power and size of the hardware your database server will run on. Under general purpose, we can choose between provisioned and serverless as well as specifying the number of virtual CPU cores and how much storage space is required for our data. Alternately, sit compute power in terms of DTUs with the maximum number increasing as we move from the pre-configured workloads of basic through to standard and then premium.
In terms of accessing the server and database, we have several options under networking. We can choose public or private end points that will allow anyone to access the database via a URL or limit access to a VPN. No access doesn't actually mean no access at all. If you allow Azure services and resources to access the server, like Azure app service, you will still be able to connect to the database.
Add current client IP address will automatically add your IP address that you are currently accessing the portal from to the database firewall roles, so you can connect to the server and database from client software like SQL Server Management Studio.
Under additional settings, we can pre-populate the database with a backup or sample data. We can also set the databases collation, but we can't change the collation or sample data.
Advanced data security is an umbrella term for a set of features that automatically enhance your data protection. You can use tags to help you determine and aggregate the cost of resources on your billing statement.
Here we have a summary of the database instance we will deploy that we can review before hitting the create button. At this point, you can also download a JSON definition of your server and database deployment in the form of an Azure Resource Manager template.
After the deployment has validated, it begins and we can watch the deployment progress in real time, viewing each resource's status as it is first accepted and then created. When we first go into the database resource, the summary page shows basic facts like pricing tier, the server name, location, and resource group.
Down the left-hand side of the page is a menu which allows us to configure most aspects of the database. The overview page of the database shows a performance dashboard that initially displays basic metrics like CPU utilization as well as data and log IO metrics as a percentage of the maximum.
Below is a graphic depicting how much disk space has been used and on the right a list of important security and performance settings that can be configured. Let's now connect to the database using SQL Server Management Studio and the server URL that I'll copy from the portals overview page.
I'll log in with the server admin user I created in the portal using SQL server authentication. As we would expect, we have our enqueue DB server with a single DB as its only user database. We can also connect with Visual Studio Code.
Before we can connect with this code we need to install the MS SQL extension. Press Control + Shift + P to bring up the command palette and search for and select extensions, install extensions. Search for MS SQL in the marketplace and install the SQL server extension. Once the extension is installed, we can now see the SQL server icon at the bottom of the left hand tool palette. At this point, we can specify the host and instance that we want to connect to.
As with SQL Management Studio, we can now see the single DB database with its sales tables. Azure Data Studio is another client that can be used to navigate your database. The connection experience is very much like that of SQL Server Management Studio. You specify the server URL, the type of authentication, in this case, an SQL login, and the username and password. The user interface is also similar, with a server navigation pane on the left-hand side displaying servers and databases. Again, we can see our single DB with its sales tables.
We have created a fully managed database and server in this demonstration, and connected to it via SQL Management Studio, Visual Studio Code, and Azure Data Studio.
Course Introduction - Azure SQL Databases Overview - Deployment Options - ARM Templates Deployment - Business Continuity - Security Database Services - Scale and Performance - DEMO: Scaling Azure SQL Databases - Partitioning Data - Migrating to Azure - Migration Scenarios - DEMO: DMS Azure SQL Database Migration Process - Upgrade Scenarios - Summary
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.