Azure SQL Boot Camp Day 3 Takeaways

Hello reader!  Here we are with the much anticipated post for day 3 of the recent Azure SQL Boot Camp. Here are the previous posts for days one and two .

If you want to get started with Azure  SQL, the boot camp is a great way to do it.  You’ll get Azure experts like Anna Hoffman and Bob Ward, who is also a long time SQL Server expert, teaching you how to leverage the cloud for SQL Server. On day three, Anna does a couple of demos on the subject of HA that demonstrate the capabilities of the Azure platform.  Other days have demos of other concepts as well.

Not only will you learn specifically about SQL Server in Azure, but this series is going to teach you other foundational concepts about Azure that are applicable no matter what you’re trying to do in Microsoft’s cloud. Things like cloud networking and the security aspect that needs to go into that networking, as well as how to give people access to resources in a secure way, are relevant for every part of Azure. With that, let’s dig into what day 3 had to offer.

Day 3 of the Azure SQL Boot Camp was all about availability.  When done right, availability solutions keep your environment up and running when things don’t go right. Here are the key takeaways for Day 3 regarding high availability:

  • Azure has you covered in terms of backup/restore.
  1. A full backup is taken weekly.
  2. A differential backup is taken ever 12 hours.
  3. Log Backups are taken every 5 to 10 minutes.
  4. Backups are stored on Read Access Geo-Redundant Storage, which means there are multiple copies of backups in differing locations.  This protects you from losing the backups.
  5. Point in time restore is available.
  6. Backup integrity checks are performed and there is even automated, random testing of those backups via automatically restoring some of them from time to time.
  7. Backup retention is by default at 7 days.  You can go up to 35 days or even use the Long Term Retention service and keep backups for as long as 10 years.
  8. With Managed Instance a copy only backup option is available.
  9. On premises backups can be used to restore to a Managed Instance as part of a migration strategy.

Let’s be honest, how many of you have a backup strategy that is this solid? Some of you have databases that are in full recovery model with no log backups happening. Yikes! Don’t believe me? Check into your backup history information in msdb with a little T-SQL and you can verify for yourself whether your environment is up to snuff in this regard.

How many of you are writing backups to the same location as the data? And, the big question is this: How many of you are testing whether you can use your backups to perform a restore? Remember, just because you have backups doesn’t mean you can restore.

If you don’t have these sorts of things in place, you might be doing a lot of mopping up after a disastrous situation. “Clean up on aisle 4!”

  • Azure has you covered when it comes to High Availability
  1. In the general purpose tier, Azure customers get a service that behaves like a SQL Server Failover Cluster Instance.
  2. Auto failover is possible and is handled by SQL Server and the Azure Service fabric.
  3. There is local SSD and local Tempdb with data files in premium storage.
  4. Backup files are geo-redundant.
  5. In the Business critical tier, Azure customers get Availability Groups behind the scenes – set up and configured for you automatically. With this service, you get 3 secondaries, only one of which has to harden transactions in order for the primary to commit transactions, and one replica for read only routing.
  6. In the hyperscale tier, you get snapshot backups taken by Azure, up to 4 secondaries and they can all be readable, as well as redundant data and log files through the log service and long term storage on Azure Standard Storage.
  7. Services like Zone Redundant Configuration, or Availability Zones, provide additional HA services for Azure SQL DB, but not in all regions.  This method of HA is currently not available for Azure Managed Instance.
  8. Geo-replication and auto-failover groups are a central piece to HA in Azure. Geo-replication has multiple replicas and supports read scale out. With this service offering, you do have to update connection strings after a manual failover is initiated.  It is asynchronous but you can use a system stored procedure to force synchronous commit prior to a failover. Geo-replication does not allow for failing over multiple databases together, and is available only for Azure SQL DB. Auto-failover groups can failover multiple databases at once with no changes to the application connection string.  It supports read scale out and is available only in Managed Instance.

Again, let’s do a gut check. How many of you are running important or business critical SQL Servers on stand alone servers and your only real option for recovery if the machine has an issue is to create a new SQL Server and restore all your backups? In this scenario, that important or business critical SQL Server doesn’t have a second copy via log shipping or some sort of replication, mirroring or an Availability Group.

Go on…. think about it.  List your important and critical business servers in a Word doc. Now examine the list and ask yourself what would happen if any of those servers died and couldn’t be recovered.  Would some part of your business stop while you tried to build a new SQL Server and restore? If so, I think you just found an urgent action item to put at the top of your to do list.  As you determine your solution, think about whether some form of SQL Server in Azure can give you what you need.

This is an important feature that is on by default in Azure.  It is available in SQL Server 2019 on-premises, but it is not on by default. It uses a persisted version store to house old data rows.  Those old data rows, and the most recent checkpoint, are used for a faster rollback.  This addresses the problem of a long rollback from a long running transaction that was killed.

Well, that is the summary for Day 3 of the Azure SQL Boot Camp. There is more that was discussed, but remember, I am only trying to give you a summary of key takeaways. Enjoy as you dig into SQL Server in the Azure cloud!

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.