Establish networking connectivity between SQL Server and Azure SQL Managed Instance using Azure networking and database mirroring (dbm) endpoints.

Create an availability group (AG) on SQL Server 2022 and database mirroring (dbm) endpoint if one does not already exist. Build the AG as clusterless or CLUSTER_TYPE = NONE. We won’t use any clustering technology for the AG because this is not an automatic failover-based solution. An existing AG could have a secondary replica already. It turns out that an interesting but not well-known feature is that if you build your own AG, you don’t have to have a secondary replica (availability mode is not used in this case). But you must create an AG so you can create a DAG.

Create a Distributed Availability Group (DAG) including the SQL Server 2022 AG and your Managed Instance name.

Create a link to Azure SQL Managed Instance (PowerShell cmdlet). This establishes an AG for a General Purpose service tier Managed Instance or uses an existing AG from a Business Critical service tier (which already has a secondary replica). Note again that an AG doesn’t have to have a secondary replica, which is why this solution can work very nicely for a General Purpose service tier. Creating the link also initiates a copy or seeding of the database to Azure SQL Managed Instance. Seeding uses the dbm endpoint to stream a copy of the database to Managed Instance. You can read more about how seeding works at https://docs.microsoft.com/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group.

Changes now made on the primary will be transmitted automatically as log changes to Azure SQL Managed Instance.

Users can connect to Azure SQL Managed Instance and access the database as read-only and also access secondary replicas for Business Critical service tiers.

Note When the final version of the link feature for Azure SQL Managed Instance is released, it is possible we may offer an option for you to declare your Managed Instance for disaster recovery (DR) purposes. The concept would be to make Managed Instance in this scenario license-free so you would only pay for compute and storage. If this option becomes available, you would not be able to read from the Azure SQL Managed Instance database since you are only using this for DR purposes.

Launch SSMS and connect to SQL Server 2022. Right-click your database and select the option to replicate the database like in Figure 3-4.

You will now go through a series of steps in the Replicate database wizard. Select Next. The first screen validates you have met the requirements to use the link feature.

Now choose your database to replicate and select Next.

Now you need to provide information for the Managed Instance you deployed. This will require you to log in to Azure and choose the subscription, resource group, and Managed Instance. You will also need to select Login to connect to the selected Managed Instance. Once this is done, your screen should look like Figure 3-5.

The next screen shows options to create the Distributed Availability Group (DAG). Leave these defaults and select Next.

The next screen is the final step. Select Finish. Note that the time it takes to complete the link creation depends on the size of your source database because database seeding takes place here. There is also a button to generate a script for what the wizard does. When this is finished, your screen should look like Figure 3-6.

Navigate in the Azure portal to your Managed Instance. You should see your database is replicated in Azure and has a status of Online as seen in Figure 3-7.

Use Object Explorer in SSMS connected to SQL Server to see the status of the database as Synchronized (you will need to refresh to see this) and a detailed list of the AG and DAG created for the link. Your screen should look similar to Figure 3-8.

You will see the status of READ_WRITE and a database version that is the locked-in version for SQL Server 2022.

Connect with SSMS to the Managed Instance you have deployed. Execute the checkstatus.sql script. You should see the Updateability is READ_ONLY, and the database version should match SQL Server 2022.

Execute the script populatedata.sql to add data to these tables. Note from the script that each vehicle will get one piece of cargo.

You should see the same results for both SQL Server and Managed Instance.

Using Object Explorer in SSMS, select Failover database using the same option as you did to replicate the database as seen in Figure 3-9.

Select Next, and you will be presented with a screen to log in to Azure. Choose your subscription for your Managed Instance deployment and select Next.

You have a choice for a planned or forced failover. Select Planned manual failover and select the option you have stopped your workload (we don’t have one running doing any writes). Your screen should look like Figure 3-10.

You now have a choice to clean up the AG and/or DAG that was created earlier. You could choose to keep these and recreate the link later, but for purposes of this exercise, I’ll check both options and select Next.

You are now on the final screen to complete the failover. Select Finish. This step changes the DAG to synchronize mode, checks that all log changes are synchronized by comparing LSN values on both systems, and then removes the link. When done, your screen should look like Figure 3-11.

Using SSMS execute the script checkstatus.sql again against Managed Instance to see the status is now READ_WRITE.

You can also see from SSMS on SQL Server the AG and DAG are removed.

Stop all writes to your workload for Azure SQL Managed Instance if you want the failback to include all changes to SQL Server.

Create a COPY_ONLY backup of your database from Azure SQL Managed Instance. Use the documentation to learn how to back up a database to Azure storage using SSMS connected to Managed Instance. (There are options for T-SQL as well if you want those. Start at the step https://docs.microsoft.com/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service#create-credential.)

Restore the database from Azure storage connected to SQL Server using SSMS (there are also T-SQL options). Use the steps in the documentation starting at https://docs.microsoft.com/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service#restore-database. You will need to choose a different database name on SQL Server if the original database for the link feature exists.

You will also need to change the application again to point to SQL Server instead of Managed Instance. In addition, you will need to migrate instance objects such as SQL Agent jobs.

You now have the option to recreate the link to Managed Instance to reestablish your disaster recovery site. Before you do this, you will need to drop the database linked before on Managed Instance.

Create an Azure Synapse Analytics workspace. The following is a quick-start guide on how to create a workspace: https://docs.microsoft.com/azure/synapse-analytics/get-started-create-workspace.

In my experience Synapse workspaces don’t take longer than 5–10 minutes to deploy.

Figure 3-13 shows a portal view of my Synapse workspace after it was created.

Next, we need a place to host our data, which for Synapse Link is called a dedicated SQL pool. Think of this as a database within Synapse to host SQL-based tables from the source SQL Server. There are a few ways to do this. One simple way is from the Azure portal. Go to the resource menu on the left side of the screen on your workspace and select SQL pools under Analytics pools. Select + New. For this exercise put in a pool name (I chose wwisqlpool) and leave the defaults. For a production system, you may want to choose different options here. See the section later in this chapter titled “More Details About Synapse Link” for more details.

Create a new Azure storage account to be used for Azure Data Lake Storage Gen2, which is the landing zone. I used the instructions at this documentation page to create my storage account: https://docs.microsoft.com/azure/storage/blobs/create-data-lake-storage-account. For this exercise I used the same region and resource group as my Synapse workspace (not required), and I chose the Standard option for Performance (for production workloads, you may want Premium). For everything else I chose the defaults except on the Advanced blade, you MUST choose Enabled for Hierarchical namespace. Figure 3-14 shows my storage account after creation.

A storage account is not enough to store files from SQL Server. You need a folder or container. On the resource menu for the storage account on the left-hand side of the screen under Data storage, select Containers. Then on the new screen, select + Container. Type in the name of your choice (you will need it later). I called mine wwidata. Leave the default and select Create. This should only take seconds.

You now need to grant access for the Synapse workspace to the landing zone storage account. Follow steps 1 and 2 at this link in the documentation to assign the Managed Identity access to the landing zone: https://docs.microsoft.com/azure/synapse-analytics/synapse-link/connect-synapse-link-sql-server-2022#create-linked-service-to-connect-to-your-landing-zone-on-azure-data-lake-storage-gen2.

My role assignment looked like Figure 3-15.

Populate data into these tables by executing the script populatedata.sql against SQL Server.

The WideWorldImporters database includes some features and data types that are not supported by Synapse Link. Therefore, execute the script alterwwi.sql against SQL Server to remove some of these features (e.g., temporal tables) and columns with unsupported data types.

Note It is possible that some of these data types can be supported by the time SQL Server 2022 is released, but to be safe for the purposes of this exercise, I removed all features or types that could cause an issue.

We are now ready to create the linked service to SQL Server 2022 from Synapse Studio. In Synapse Studio click the Manage icon on the left-hand menu (the last one), Linked services, and then + New as seen in Figure 3-18.

Now we need to create another linked service for the landing zone. On the Linked services page, select + New and choose Azure Data Lake Storage Gen2.

Give the linked service a name. Leave Connect via integration runtime to the default. Choose Authentication type as System-Assigned Managed Identity, which will choose your Synapse workspace Managed Identity, which was automatically created when you created the workspace. This was the Managed Identity you gave access to the landing zone earlier.

Choose your Azure subscription and the storage account name for the landing zone you created. Select Test connection at the bottom of the screen. If all goes well, your screen should look like Figure 3-26.

Now that we have linked services in place, it is time to sync data from SQL Server to Synapse by creating a linked connection based on linked services. Choose all the tables. In Synapse Studio click the Integrate icon on the left side of the screen and then click + like Figure 3-28.

While this is still starting, you can go back to SSMS and view your XEvent Profiler session. Look for events from the client_app_name field called AzureDataMovement. These are queries from SHIR.

Note At the time of the writing of this book, these procedures and T-SQL are not documented or supported. Synapse Link must be configured through Synapse Studio, which communicates with SHIR.

Use Synapse Studio to monitor the linked connection as seen in Figure 3-31.

Now let’s see the tables in the SQL pool by using Synapse Studio like in Figure 3-33.

The results should look like Figure 3-34, which matches the initial population of data in these tables.

We don’t support copying or modifying files in the landing zone, but it is worth taking a look at files in the container for learning purposes. If you got to your container in the landing zone account and drill into the container, your results will look similar to Figure 3-35.

Let’s make changes and see them show up in Synapse. Add random amounts of cargo in SQL Server 2022 using the script modifyvehicledata.sql running against your SQL Server instance.

Look at the landing zone to see CSV files by looking at the container and drilling into the indexWorkingDir folder instead of Tables like in Figure 3-37.

Go back to Synapse Studio and run the same query from getcargocounts.sql to see the changes have been applied like in Figure 3-38.

With your permissions properly configured, create a new Azure Key Vault resource. Azure Key Vault is an Azure service to store and protect keys, secrets, and certificates. Use this quick-start guide to create a new key vault at https://docs.microsoft.com/azure/key-vault/general/quick-create-portal. Use the same resource group and region from the SQL Server Azure Arc registration. Add your AAD admin account in the Contributor role for the key vault you created.

Set up access for the SQL Server 2022 instance to the Azure Key Vault. Use the Access policies option on the resource menu for the key vault. Select Add access policy. Keep 0 selected for Key permissions. Add the Get and List permissions for Secret and Certificate. Then for Select principal, use the name of the host of your SQL Server (the name of the Azure Arc SQL Server). Your screen should look like Figure 3-40.

Now grant access to the Azure Key Vault to the AAD account you would like to make the AAD admin for SQL Server. This is a similar process to the preceding step except for Key permissions you need Get, List, and Create. You also need Get, List, and Set for Secret and Get, List, and Create for Certificate. It is possible you don’t need this step if you used the AAD admin account to create the Azure Key Vault.

Now we will set up the AAD admin for SQL Server using the Azure portal.

Note There are other options for set this including az CLI, PowerShell, and an ARM template. To learn more see the documentation at https://docs.microsoft.com/sql/relational-databases/security/authentication-access/azure-ad-authentication-sql-server-automation-setup-tutorial#setting-up-the-azure-ad-admin-for-the-sql-server.

This leads right into the last step for setup. SQL Server has to use an Azure application as part of communicating with AAD. The app name you saw in the last step was created automatically for you. Now you must grant an admin consent for the Azure application. In the Azure portal, search for Azure Active Directory and choose your organization. Select App registrations on the left-hand menu. The application you want to choose was the one created in step 4 (mine is 'bwsql2022-MSSQLSERVER<nnnnn>). Now select API permissions on the left-hand menu. If you have the proper rights, select Grant admin consent…

Note This is the step that requires you to have Global Administrator or Privileged Role Administrator rights with your AAD. These are highly privileged rights, so you may need someone in your organization to perform this step.

The result should be a single login with the AAD credentials you created earlier. The type = E stands for EXTERNAL_LOGIN. The only type of external logins supported today is AAD.

Let’s try to log in with SSMS using the AAD login. From SSMS for Authentication, choose Azure Active Directory – Universal with MFA. Then type in the AAD account. Select Options and make sure Encrypt connection and Trust server certificate are checked. My login screen before I selected Connect looks like Figure 3-43.

Note the new syntax FROM EXTERNAL PROVIDER (which we already have supported with Azure SQL Database and Azure SQL Managed Instance).

You can now connect to SSMS with AAD with MFA or password-based authentication using this new login.

Now when you try to connect using SSMS with this AAD account, you need to use the Connect to database option under Connection Properties and specify this database. This user will only have access to the database and not the complete instance. This is very similar to the capability we provide with Azure SQL Database.

Unfortunately procedures like xp_logininfo don’t work against AAD groups, so to view membership, you will need methods to list AAD groups.

You will first need a Microsoft Purview account. Here is a quick-start guide to create a Purview account: https://docs.microsoft.com/azure/purview/create-catalog-portal.

There are some permissions you will need to set up with Purview to allow you to create and publish policies and enable DUM. Please follow the steps in this documentation carefully to set up these permissions: https://docs.microsoft.com/azure/purview/how-to-policies-data-owner-arc-sql-server#configuration.

Now you need to register your Purview account with the Azure extension for SQL Server through the Azure portal. Find your SQL Server – Azure Arc resource that you created for AAD authentication in the Azure portal. Select on the left-hand menu Azure Active Directory. At the bottom of the screen that is presented, you will see a section called Microsoft Purview access policies. Click Enabled and fill out Microsoft Purview Endpoint by putting in your Purview account name like in Figure 3-45.

You will need some AAD accounts to authorize access in the exercise. I will show you two scenarios for two different AAD accounts. For access to read data, I’ll use the account [email protected] that I created in the exercise earlier in this chapter for AAD authentication. For the exercise on DevOps for performance monitoring, I’ll show an example of a guest AAD account. A guest is an account that is not part of your AAD but you are inviting them to access resources in Azure. You can read more about how to invite a guest account in AAD at https://docs.microsoft.com/azure/active-directory/external-identities/b2b-quickstart-add-guest-users-portal.

Learn the basics of how to launch Purview Studio from the Azure portal at https://docs.microsoft.com/azure/purview/use-azure-purview-studio. Use the AAD admin account you created earlier in this chapter for the exercises to enable AAD authentication for SQL Server 2022.

The first thing we need to do is register the SQL Server 2022 instance as a data source. Launch Purview Studio from the Azure portal. Choose the Data map icon A screenshot shows the data map icon in Microsoft Purview. It is a rhombus-shaped icon featuring 2 dots connected to a circle on top. on the left-hand menu (hover over the icons to see Data map). Your Purview account may have other data sources already registered shown in a visual tree or in a table format. Choose Register at the top of the screen. On the Register sources screen, type in Arc in the Filter by keyword field. Select SQL Server on Azure Arc–enabled servers and select Continue.

You can now choose and fill out fields on the Register sources (SQL Server on Azure Arc–enabled servers) screen. Choose a name (I recommend the same name as your registered server), subscription, and server name (your registered server). Type in your registered server name in the Server endpoint field and leave the (Root) collection as the default.

Select the Enabled option for Data Use Management, which should auto-populate the Application ID (this is the App registration ID that is listed on the Azure Active Directory screen from SQL Server – Azure Arc). Your screen should look similar to Figure 3-48.

Let’s now create a data policy for another AAD account. I’ll use [email protected] to be granted access to read data for our SQL Server instance. For me, since I created this account as a login in the exercises for AAD, I first deleted the login to avoid any confusion. Turns out that if a login exists for the same AAD account as a policy, we use the union of the permissions of the login and the policy.

Note At the time of the writing of this book, we were looking to add more granular read access for individual objects like specific databases and/or tables in the future. This will require the SQL Server instance to be scanned using the data catalog feature of Microsoft Purview.

For the policy to take affect, we need to publish it. Select your policy and select Publish on the right side of the screen. Choose your data source and select Publish at the bottom of the new screen. You should now see a Published On date and time on the screen for your policy.

Let’s create a database and data for the new account to read. Execute the script howboutthemcowboys.sql against your SQL Server 2022 instance as your default sysadmin.

Note On a server start when SQL Server obtains information from Microsoft Purview for policies, you might see messages like the following in the ERRORLOG:

IMDS resource information. Subscription ID: 4fe118d0-bc41-4d7b-869f-0820c87df124, Resource Group: aad-auth-demo-env, Name: SQLSERVER2022.

[JSONWebTokenService::GetCertificateFromCertificateStoreBySubjectName] [AADAuthThumbprint] Thumbprint being used for AAD authentication aRgwszCertificateThumbprint 26289598dfe34092c141edc8a319505bdf0882b6

[CBabylonConfigSubscriber] Purview frequency setting changed. New value: 300

[CBabylonConfigSubscriber] Purview policy expiration time changed to 480 mins

[CBabylonConfigSubscriber] Received update to the config settings

[CBabylonConfigSubscriber] Purview frequency setting changed. New value: 300

[CBabylonConfigSubscriber] Purview policy expiration time changed to 480 mins

[CBabylonConfigSubscriber] Registered for setting updates successfully

Fun fact Babylon is the original project name for Microsoft Purview.

You should get one row back (I hope this is our year<g>). The permission to read data includes system catalog views and limited Dynamic Management Views (DMVs).

The results of dm_server_external_policy_actions are all the possible types of actions a policy can be applied to. Think of these as the detailed types of permissions the Data and DevOps policies apply to. This list will expand as we provide more capabilities. The DMV sys.dm_server_external_policy_roles lists out the detailed roles that apply to Data and DevOps policies including reading, performance monitoring, and security auditing. You will see others in here not implemented yet and roles for Connect, which apply to all roles. This list will also expand as we add more capabilities. The DMV dm_server_external_policy_role_actions is a join of what actions are allowed for specific predefined roles. These DMVs will always be populated even if no policies have been created.

Let’s break down the results from these DMVs. dm_server_external_policy_principals will list any AAD account you have granted access through a policy. You will only see one row for an account even if it has been granted access through more than one policy. The aad_object_id column maps to the unique ID in Azure Active Directory for this account. dm_server_external_policy_role_members shows which roles from dm_server_external_policy_roles the AAD account is a member of. Then finally dm_server_external_policy_principal_assigned_actions shows all the permissions for an AAD account assigned through any policy.

Let’s show an example of a DevOps policy. Let’s say you hire a consultant to look at performance metrics for your SQL Server for a period of time to help tune and improve performance. You want to grant an AAD guest account access to your SQL Server (or many) but limit them to only access certain operations that allow them to analyze performance but make no changes or access no user data. In Purview Studio, select Data policy from the left-hand menu as you did in step 2. Select DevOps policies and + New policy. For Data source type choose SQL Server on Arc-enabled servers and pick your registered SQL Server and select Select.

Now choose Add/remove subjects and pick the guest AAD account you created for the prerequisites. Your screen should look like Figure 3-50.

On your SQL Server instance connected as a sysadmin, execute the script policyrefresh.sql.

Execute the scripts policydmvs.sql and policyprincipals.sql as a sysadmin to see the additions to the new AAD guest account and the permissions granted for performance monitoring.

Connect with SSMS with the guest ADD account using MFA as you have done before with other AAD accounts in this chapter.

As a refresher my login to SSMS looks like Figure 3-51.

Your results should be the same results as you see from a typical admin who can view this type of information.

Let’s say the contract for the consultant with the guest AAD account is now over. You would like to ensure the system is secure and remove access to SQL Server. Let’s do this through Microsoft Purview. In Purview Studio select the Data policy icon from the left-hand menu. Then select DevOps policies. Select the checkbox next to the policy you created and select Delete at the top of the screen. Select Delete again.

Now go back to SQL Server and try to connect again with the guest AAD account. You should get a login failure error.