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 peformed 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.

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.