Microsoft recently proved its commitment to the Azure Arc model by significantly enhancing the capabilities of the SQL Server mode of Azure Arc enablement. In particular, the SQL Best Practices Assessment and the Patching settings of (in-Azure) SQL virtual machines have now been extended to IaaS instances of SQL Server running on any Windows computer outside of Azure. Azure Arc-enabled SQL Server also delivers some new and convenient functionality involving support for older versions of SQL Server and even a previously unavailable way to license SQL Server outside Azure in an on-demand fashion (this is, more cloudy!)
Where does Azure Arc-enabled SQL Server fit in the Microsoft ecosystem?
Azure Arc is a bridge that extends the Azure platform to your applications and services (running on any server or any cloud), providing the same economy of hyperscale benefits enjoyed by the apps and services in the Azure cloud itself. Underpinning the logic of Azure Arc, as a business and technical concept, are the undeniable economic benefits of doing things in your small(er) world the same way Microsoft does them successfully on a planetary scale. Why use any legacy or proprietary tooling for your estate, when you can use the same modern, cloud-based Azure management tools that successfully and securely govern and operate tens of millions of workloads 7x24x365? Essentially, that inefficiencies and opportunities for disarray have been ruthlessly uncovered by the hyperscale of Azure itself—There is wisdom in embracing proven models.
The Azure Arc paradigm
Azure Arc as a brand is applied to an ever-growing variety of specific software products that achieve the business and technical goals of the Azure Arc model. I’ve written blog articles and a book on Azure Arc-enabled server, which is the product that projects your non-Azure Windows and Linux servers into the Azure management plane, creating a computer resource in Azure that you can interact with via the Azure Portal, Azure PowerShell/CLI , and Azure API that is functionally identical to an Azure VM. For example, consider a customer with 100 servers in Azure and 100 servers not in Azure (divided between physical, private cloud such as VMWare, and multi-cloud such as AWS and GCP). That customer can manage, monitor, and secure all 200 servers from a single point of control using the same governance tools.
A SQL Server anywhere is still a SQL Server
Azure Arc-enabled SQL Server extends the same management experience–previously available only in Azure for SQL virtual machines running on Azure VMs–to SQL Server (IaaS) instances, running on Azure Arc-enabled servers, anywhere in the world. Taking a very holistic approach to database security, Microsoft stretches to Azure Arc-enabled servers running SQL server its deep experience with the other members of the SQL family: Azure SQL Server (PaaS) instances, and SQL IaaS instances on Azure VMs (Azure SQL managed instances and SQL Server on virtual machines). These developments are consistent with the overall Azure Arc roadmap—with a goal of near-100% parity between Azure and non-Azure resources when it comes to management tools and capabilities. Figure 1 displays the Azure portal SQL Server – Azure Arc page of a Windows Server 2022 computer running SQL Server 2019 in a private cloud, with new features indicated.
Figure 1 – The Azure portal page representing an instance of SQL Server running on an Azure Arc-enabled server.
What are the new features of Azure Arc-enabled SQL Server?
The most important component of Azure Arc for SQL Server is Defender for Cloud workload protection for SQL Server. Please see my blog article The Complete Guide to Microsoft Defender for Cloud for SQL Servers on machines for why this is such a high-value security service. That article includes step-by-step instructions to enable and deploy Azure Arc-enabled SQL Server to your Azure Arc-enabled server population.
Microsoft Defender for Cloud was the first major integrated feature when Azure Arc-enabled SQL server was released (safety first!). The initial release of Azure Arc-enabled server did have an Environment Health feature that included a SQL Assessment, but it was rather cumbersome to implement. The new version of Azure Arc-enabled SQL Server:
- Replaces the Environment Health setting with a Best practices assessment setting which is much easier to configure and use.
- Adds a Patching setting which is identical to the same setting found on SQL virtual machines running in Azure.
- Introduces the Host license type setting to specify the SQL Server edition and license type you are using on this machine, including a new way to pay for the IaaS instance of SQL Server directly through Microsoft Azure using a pay-as-you-go model.
- Adds an Extended Security Updates setting for customers running SQL Server 2012. Previously, achieving this capability required you to migrate your on-prem legacy version SQL Server instances to Azure. Now customers running business critical apps on SQL Server 2012 outside Azure have a much longer runway to upgrade or migrate their databases to a modern platform.
Azure Arc-enabled SQL Server currently supports these features:
1. Defender for Cloud workload protection for SQL Server: View the recommendations, incidents, and vulnerability findings on the SQL Server – Azure Arc -> Security -> Microsoft Defender for Cloud blade for each server.
2. Extended Security Updates: Applies only to SQL Server 2012 and earlier versions.
3. Best practices assessment: Configure to run on specific days and times weekly or monthly. Issues are sorted by New and Resolved. Insights tab shows Most recurring issues and Top databases with issues.
4. Patching: This is the same combined OS+SQL updating service available with SQL Managed Instances in Azure.
5. Databases: You can inventory and view SQL Server databases in Azure, including using Azure Resource Graph for multi-cloud SQL Server management.
6. Manage SQL Server license and billing options: Manage your license compliance, including electing to pay for the SQL software usage directly through Microsoft Azure using a pay-as-you-go billing option.
Selecting the SQL Server Host license type
You must define what license type each SQL Server instance is using on your servers. Unless you have configured this setting though Azure policy, new Azure Arc-enabled SQL Server instances will show “Configuration needed” for Host license type. Some Arc-enabled SQL Server features are only available for SQL Servers with Software Assurance (‘Paid’) or with Azure pay-as-you-go (‘PAYG’). Figure 2 shows an Azure Arc-enabled SQL Server configured for License with Software Assurance. Notice you have the option to exclude specific SQL Server instances–when more than one SQL Server instance is installed on the same computer, and some instance(s) have varying license type(s).
Figure 2 – Select the proper SQL Server license type for your Azure Arc-enabled SQL Server.
Here is a ‘cheat sheet’ to help you understand the impact of each setting:
-
Pay-as-you-go: Standard or Enterprise edition with pay-as-you-go billing through Microsoft Azure. All Azure Arc-enabled SQL Server features are enabled, you will pay for your SQL Server software usage through Microsoft Azure.
Note: Feedback from enterprise DBAs is that this is first time Microsoft has enabled a legal way to license SQL Server outside of Azure: on demand and for short periods of time. For some enterprises, this feature alone could be transformative.
-
License with Software Assurance: Standard or Enterprise edition license with Software Assurance (SA) or SQL Subscription. All Azure Arc-enabled SQL Server features are enabled, usage implies that you already have the necessary licenses (honor system). At this time, Microsoft has reserved some of the higher-value features of Azure Arc-enabled SQL Server to Software Assurance customers to preserve the value of SA.
- License only: Developer, Evaluation, Express, Web, Standard or Enterprise edition license only without Software Assurance. These features of Azure Arc-enabled SQL Server are not available without Software Assurance: Best practices assessment, detailed database inventory, and automated patching.
Azure Arc-enabled SQL Server: Evidence
Take a high-level tour of the features available to your on-prem and non-Azure SQL Server IaaS instances.
Microsoft Defender for Cloud (SQL server)
The Microsoft Defender for Cloud blade of your Azure Arc-enabled SQL Server Azure portal page includes counters of recommendations, security alerts, and assessment findings, and confirmation of Defender for Cloud enablement status (see Figure 3). You have one-click access to learn about Defender for Cloud recommendations, security incidents and alerts, and vulnerability assessment findings on that server.
Figure 3 – Microsoft Defender for Cloud summary page in the Azure portal.
Drilling into a specific Security alert occurring on a SQL server database service, you can instantly view all the details of a security incident, including the SQL server name, the client principal name and IP address of the potential attacker, and the exact statement from the SQL Server conversation that attempted the exploit (Figure 4).
Figure 4 – Defender for Cloud security alert of a SQL Injection attempt.
Best practices assessment
A huge improvement in the Azure Arc-enabled SQL Server experience over the first release is the easy in-line configuration and viewing of scheduled best practices assessment (BPA). In addition to the security-focused checks done by Defender for Cloud, Azure Arc-enabled SQL Server elevates and simplifies overall SQL server best practice checks on general database server configuration and management.
SQL Health Check solution replacement
Not only is the in-line BPA significant in the Azure Arc ‘make it cloudy‘ mission, but it also supports Microsoft’s ‘migrate management features out of the legacy Azure Log Analytics Solutions’ goal. For some years, since the release of the legacy “OMS” management product in the Azure portal, a useful tool for SQL admins has been the SQL Health Check solution that could be added to your Log Analytics workspace (Figure 5).
Figure 5 – The SQL Health Check solution from Azure Log Analytics (to be retired in August 2024).
However, after August 2024 when the Microsoft Management Agent (MMA) is retired and replaced by the Azure Monitoring Agent (AMA), these deprecated Azure Log Analytics Solutions will no longer function. Moving the SQL Health Check functionality to Azure Arc-enabled SQL Server is logical, elegant, and proof that Microsoft is fulfilling an implicit commitment to replace legacy MMA-based solutions with modern Azure portal alternatives. (Here’s hoping the AD Replication Status and Active Directory Health Check legacy solutions have worthy replacements as well.)
Best Practice Assessment: Super easy setup
The Best Practice Assessment in Azure Arc-enabled SQL Server is easily configured to run on a schedule as well as launch immediate scans on demand (‘Run assessment’). You have one-click access to all historical BPA reports (Figure 6).
Figure 6 – Configuring the BPA for scheduled and on-demand runs, as well as viewing the results of completed scans.
SQL best practices assessment results
The Assessment results for a scheduled or on-demand SQL BPA scan include a summary and details section comparing the server’s scan results to hundreds of best practice checks. You can instantly export the results to an Excel file for DBA record keeping and evidence presentation to stakeholders by clicking on the three dots at the top right of the Recommendation Summary area (Figure 7).
Figure 7 – SQL best practices assessment results pane highlights new and resolved issues.
Fixing or mitigating the discovered issues is made easy by in-line details on what the recommendation/finding means and a URL link to the procedure document you need to consult (Figure 8)
Figure 8 – Clicking on recommendation details opens a blade with the detailed information you need to fix or mitigate the issue.
Best practices assessment: Trends: Total issues
The point-in-time scan results of the BPA are invaluable for database admins, but they don’t document over-time progress towards better security. The Trends tab in the BPA results page does that, as well as highlight possible moves the opposite direction (!). (Figure 9)
Figure 9 – Spot and document trends in SQL BPA scan results.
Patching
Automated patching for in-Azure SQL servers was always an attractive option because you are essentially subscribing to a no-additional-cost managed update service that includes both Windows Server and SQL Server updates delivered in a homogeneous manner. The selections of updates and the timing of their delivery are vetted at scale by Microsoft, significantly de-risking the SQL Server patching process.
Figure 10 – Simple enablement of combined OS + SQL Server patching on Windows servers running SQL Server anywhere.
Notes on SQL Server automated patching
- Automated patching is not a complete solution for all updating. SQL Server version upgrades are not automatically performed.
- Only Windows and SQL Server updates marked as Important or Critical are installed. Other SQL Server updates, such as service packs and cumulative updates that are not marked as Important or Critical, must be installed manually.
- There is no additional configuration required and no feedback available other than you will observe Windows OS and SQL Server updates being installed as scheduled on participating servers (Figure 11).
Figure 11 – A participating Azure Arc-enabled SQL Server will show “Some settings are managed by your organization” and Windows OS and SQL Server updates start being installed as scheduled.
Databases
The main use of the Azure Arc-enabled SQL Server Databases feature is projecting an inventory of SQL Server databases and their properties into Azure, where you can leverage the Azure control plane for management and governance features.
For an enterprise already comfortable using Azure management artifacts like Azure Tags and role-based access (RBAC) for database owner and DBA identification, it’s convenient that all SQL databases on all SQL Servers can share the same management control plane. Notice the properties of the database running on an Azure Arc-enabled SQL Server in Figure 12.
Figure 12 – Each database on an Azure-Arc enabled SQL Server is projected into Azure where a logical record of the database properties is created.
The properties of the SQL Server-Azure Arc (like Version and Edition of SQL Server) and SQL Server database – Azure Arc resource records seen in Figure 12 are available to query and report in in Azure Resource Graph (ARG). In other words, ARG becomes your ‘in kind’ SQL Server configuration manager, where lots of valuable SQL configuration data is available for management purposes.
Hybrid SQL Server management demonstration
The following Azure Resource Graph KQL query reveals the power of a homogeneous management control layer spanning PaaS and IaaS instances of SQL Server in Azure and on servers in all other locations. Running this query produces the graph seen in Figure 13. This query considers all versions of SQL Server across four (4) different hosting platforms (Azure Arc-enabled SQL Server, Azure SQL Server, SQL Server Managed Instance, and SQL Server on virtual machines).
resources
| extend SQLversion = properties.version
| where type == (“microsoft.azurearcdata/sqlserverinstances”) or
type == (“microsoft.sql/servers”) or
type == (“microsoft.sqlvirtualmachine/sqlvirtualmachines”) or
type == (“microsoft.sql/managedinstances”)
| where SQLversion != “”
| summarize count() by tostring(SQLversion)
Figure 13 – Composite SQL Server versions across four (4) modalities: Azure Arc-enabled SQL Server, Azure SQL Server, SQL Server Managed Instance, and SQL Server on virtual machines.
Figure 13 demonstrates how Azure Arc-enabled SQL Server ‘completes the circle’, which is, providing identical management experiences to any SQL database on any SQL Server in the world. For an organization with licensed instances of SQL Server distributed across clouds and business units, there now exists a way to perform modern, multi-cloud/multi-platform database governance at scale and at no additional cost.
To learn more
I will be presenting a 90-minute deep-dive session at Live! 360 Events November 12-17, 2023 Royal Pacific Resort at Universal Orlando, Florida:
CRW06 Regulatory and Security Compliance from Azure for All Your Servers
We will cover Azure Arc-enabled SQL Server in detail. Read up about this conference, there are a vast number of sessions across the six (6) co-located Live! 360 events.
If you are a stakeholder in a hybrid IT estate, you owe it to yourself to learn about Azure Arc, get a copy of my book: (co-authored by Microsoft’s Steve Buchanan)
Azure Arc-Enabled Kubernetes and Servers: Extending Hyperscale Cloud Management to Your Datacenter
https://www.amazon.com/Azure-Arc-Enabled-Kubernetes-Servers-Hyperscale/dp/1484277678
Tags: #MVPBuzz #HybridCloud #CloudSecurity #DefenderForCloud #SQLServer #AzureArc #AzureHybrid #DefenderforSQL #SqlAtp #DatabaseSecurity