Azure SQL Boot Camp Day 2 Key Takeaways

Hello dear reader.  This is the second of a four part series on a recent online event hosted by Microsoft on their Microsoft Developer page over on YouTube.  From August 17th to August 20th Ann Hoffman and Bob Ward hosted a live boot camp for Azure SQL.  This post provides you with key takeaways from day 2 of that event.  The topic title for Day 2 was Azure security and performance.  Below are my takeaways. If you’re looking for Day 1 takeaways, look here.

I. Four methods discussed for handling network security in Azure for Azure SQL database.

    • The setting “Allow Access to Azure Services.” This setting allows any Azure resource to communicate with other Azure resources. So, this allows wide open communication between all your Azure resources. It is not recommended to leave this enabled.
    • Firewall Rules : This is what it sounds like – a firewall service hosted by Azure that allows you to build rules about access within and to and from Azure services, including traffic related to on-premises resources.
    • Virtual Network Rules : These are rules are a feature of the Azure firewall.  They are applied at the server level and not the database level.
    • Private Link : This is the creation of a private endpoint for network communication.  This is the most secure method.

II. Understanding RBAC is important.

RBAC stands for Role Based Access Control and is a means to provide granular controls in your Azure environment.  This feature isn’t just about SQL.  RBAC is applicable to your total Azure environment. This method of security involves assigning a role, which is just a named collection of permissions, to a security principal and making that assignment at a certain scope.

Security Principals

A security principal can be a user, group, service principal or a managed identity.  A user is an individual account with access in Azure Active Directory. The “Group” service principal is the same concept as a Windows AD group. It is a collection of users created in azure Active Directory. A Service Principal can be thought of as a user account for an application.  A Managed Identity is an identity managed by Azure that provides access to Azure resources, like Azure Key Vault, for example. A managed identity might be used to access Azure Key Vault to retrieve other secrets that are needed. Managed identities are a feature of Azure Active Directory and are included in subscriptions to Azure AD.

Remember, I said that RBAC is about assigning collections of permissions, called Roles, to security principals and making that assignment at a certain scope. Well, we’ve talked about security principals So let’s discuss roles.

RBAC Roles

There are four categories of roles that are applied to various resource types in Azure.  Those roles are:

  1. Owner – Has full access to all resources within a subscription.  Service principles in this role can also delegate access to other people.
  2. Contributor – This role allows a service principle to create and manage access to resources, but can’t grant access to others.
  3. Reader – This role only allows view access.
  4. User Access Administrator – Manages user access to resources.

These categories of roles appear in various forms and with various names within categories of resources like Computer, Networking, Storage, Integration, Security, etc.

Scopes Within Azure

A scope is a set of resources that access applies to. Scope has a hierarchy with parent-child inheritance. Grant access at the very top, and you’ve granted that access to everything below it.T he hierarchy is Management Group > Subscriptions > Resource Group > Resources.

What are some examples of things RBAC can be used for?

  • Allow a person to manage virtual networks but not VMs.
  • Segment access for Development DBAs just to non-prod databases. They can be in a group with the Contributor role so they can create and manage databases within a subscription that hosts only non-prod databases.
  • Allow a person to view endpoints, but not change them in any way.

III. Authentication to Azure SQL

  • Mixed mode authentication to SQL is forced.
  • SQL auth login is required during deployment and is called “server admin.” This account is a server level principal and is effectively in the “sysadmin” role for the server and databases.
  • If you need Windows authentication then you need to use Azure Active Directory.
  • There are some syntax differences with the CREATE LOGIN scripting for managed instance and Azure SQL Database. This is most notable when Azure AD authentication is involved.
  • There are a couple of less privileged database roles that can be utilized to limit the need for using the server admin SQL auth login. LoginManager is a database level role in the master database of the logical server. People in this role can create logins for the database server. The DBManager role is also located in the master of the logical server. This role can create and delete databases on the database server.

IV. Azure SQL Performance Capabilities

There is a lot that could be said about performance for Azure SQL. I am only giving you some key takeaways.

  1. Large scale capacity in Azure is absolutely available.  You get up to 128 vCores, 4 TB of memory and 4 TB’s of space for a database in Azure SQL. In Hyperscale you have a 100TB max database size.
  2. TempDB is on local SSD and the MIXED_PAGE_ALLOCATION setting is off.
  3. Only full recovery is supported so minimal logging in bulk operations is not available.
  4. MAXDOP is controllable at the database level.
  5. Optimize for ad hoc workloads, a great feature for managing plan cache bloat, is supported for SQL Managed Instance but not for SQL Database.

V. Azure SQL Monitoring

There are a lot of options for monitoring in Azure.  The portal provides access to a lot of different options.  You get things like Azure Monitor Metrics, alerts and logs that can be reviewed.  Performance overview and query performance insights in the portal are provided via Query Store, which is on by default.

Extended events is available in Azure Managed Instance and Azure SQL DB. The one thing to be aware of is that you don’t have access to the underlying file system for Azure SQL DB so the only way to have a file target for an extended events session is to use Azure Blob storage.

Dynamic Management Views are available in Azure Managed Instance and SQL DB. For managed instance all SQL Servers DMVs are available.  For Azure SQL DB a common subset of DMVs are on hand.

Remember that for Managed Instance there is no access to the underlying operating system so things like perfmon are not available.  Despite that, you do have access to the perfmon counters via sys.dm_os_performance_counters.

There are some Azure specific DMVs.  Sys.server_resource_stats in Azure Managed Instance  returns aggregated data concerning CPU usage, IO and storage data.  It can also return things like the resource SKU, meaning whether you are using General Purpose or the Business Critical tier. The counterpart to this DMV for Azure SQL Database is called sys.dm_db_resource_stats.

This is already a long post and there is a lot I didn’t share or summarize.  I encourage you to research performance features like Intelligent Query Processing, Automatic Plan Correction and Automatic Tuning for Azure SQL Database.


I hope these takeaways have been informative and will inspire you to watch the videos and do some of your own research.

Azure SQL Boot Camp Day 1 Key Takeaways

Recently Microsoft hosted an Azure SQL Boot Camp over on the Microsoft Developer page on YouTube.  The boot camp consisted of four days of two hour sessions describing the different features of Azure SQL.  The hosts were Anna Hoffman and Bob Ward.

This will be the first of a four part series offering you key takeaways from each of the sessions.  These takeaway posts by their nature can’t possibly include everything from each session so keep that in mind as you read.

Why Would You Use Azure SQL VMs?

Azure VMs are Infrastructure as a Service. If your on-prem SQL Servers are working just fine, why would you switch to Azure SQL VM?

  1. You’re committed to migrating at least some part of your servers to the cloud and you want an easy entry point that feels most like your on-prem SQL Servers.  Azure Virtual Machines are just what they sound like. You get a VM in Azure’s cloud. Once you install SQL Server on the VM, it looks and feels like the SQL Server of old, because it is. You still have to do some Azure networking and configure access to it. This gets you a gentle introduction to Azure without changing your whole world all at once.
  2. You are committed to migrating at least some part of your servers to the cloud and you need on-prem, native SQL Serve features such as SSAS, SSRS or SSIS.  Possibly you need things like Distributed Transaction Coordinator, Linked Servers or FileStream. If you are using these features on-prem, and you don’t want to change anything about how your environment works, then you need Azure VMs.
  3. You are committed to migrating at least some part of your servers to the cloud and you need access to the operating system. If your application using your SQL Server needs to do something at the Windows file or other OS level then the Azure VMs offering is your only option.
  4. Your databases are on the Simple Recovery Model and you don’t want to change that. The other flavors of Azure SQL, namely Managed Instance and SQL DB, require the databases be in Full Recovery Model. Keep in mind though, that the Simple Recovery Model inhibits your ability to recover from a data disaster.So, think long and hard about whether you really want to keep things on that recovery model.
  5. You have SQL Server 2008 or 2008 R2 in your environment. These versions of SQL Server are out of support on prem.  This means no more security updates of any kind. No more adding features to the platform (non-security related updates).  If you break it you pay for it. Meaning, if you call Microsoft for help, they won’t help you for free. They may just tell you that you need to upgrade. But, maybe you have legacy apps that are tied to one of these versions of SQL Servers and you still want to migrate them to the cloud.  In that case, you have to use Azure SQL VMs. Good news, in Azure you can still get free updates to SQL Server 2008 and 2008R2.
  6. You can have automated backups and security updates handled by Azure.  With Azure handling this, your team has more time for other things.
  7. Accelerated storage performance with Azure BLOB Caching.
  8. Virtual networks to integrate with on-prem resources
  9. Azure VM built-in HA and Azure Storage built-in DR
  10. Failover Cluster Instance with Azure Premium file share.

How do you migrate to this. One way is to use Azure Site Recovery.

There is more about Azure VMs, but remember, I promised you only key takeways.


What Is Azure SQL Managed Instance All About?

Azure SQL Managed Instance is the next progression toward Platform as a Service. As you progress toward Platform as a Service, more and more features are managed by Azure, leaving you with more time for other things.

Here are some takeaways.

  1. You want to use this if your company wants to move to the cloud while removing more management and administrative overhead, but you need instance scoped features like SQL Server Agent, Database Mail, CLR, Linked Servers, Service broker, etc.
  2. Management overhead related to business continuity, high availability and DR are handled by Azure for you.  The nature of these features in Azure Managed Instance are determined by things like your service tier (General Purpose or Business Critical).
  3. The General Purpose Tier offers remote storage, involves the least money, but has only a single instance.
  4. The Business Critical Tier offers lower latency storage, a readable secondary, and has higher costs.

How do you migrate to this.  One way is near zero down time using log shipping.

What is Azure SQL Database All About?

Azure SQL Database is at the far end of the continuum of IaaS versus PaaS.  As a PaaS offering, basically everything but the database is stripped away. There is no access to file level structures or the OS.  Both server and instance level options to make changes are removed. So what are some characteristics of this offering?

  1. Fully managed service, meaning that the Azure service handles all SQL upgrades, patching, backups, high availability and monitoring. Thus, almost all administrative functions are handled by Azure. This provides you with the latest stable version of the SQL Server database engine.
  2. There is no ability to make configuration changes that require a server restart because Azure SQL Database does not provide access to the underlying server.
  3. Offers access to a single database with its own guaranteed amount of computer, memory and storage resources.
  4. Elastic Pools  in Azure SQL Database allow for multiple, single databases to be managed together, providing predictable behavior for multi-tenant scenarios.
  5. SQL Database offers General Purpose, Business Critical and Hyperscale  service tiers.  HyperScale provides storage for large databases (100TB+).
  6. You can mix a single database deployment with an elastic pool.

How do you migrate to Azure SQL Database? One way to accomplish a migration is discussed here.  You can also use the Azure Database Migration Service.

For any of these offerings, I didn’t cover things like the monitoring available with each of these in Azure portal, deployment options or connectivity architecture.

Azure has a lot to offer.  There are caveats and usage scenarios for each type of platform within Azure.  Please be sure to check out what is and isn’t available from a SQL Server features and management perspective within each offering.

Stay tuned for part 2 of Key Takeaways from the Azure SQL Boot Camp.