Task 01: Prepare the environment

1. Open Microsoft Edge, go to Azure Portal, then sign in with your lab credentials:

   Item	Value
   Username	Your Azure username
   Password	Your Azure password

2. In the top global search bar, enter and select Azure SQL.

   

3. Select Create on the top command bar.

   

4. Under SQL managed instances, select Create.

   

5. In the Want to try SQL MI for free? banner, select Apply free offer to use the free trial.

   

6. Use the following information to fill out the Basics tab:

   Item	Value
   Resource group	your resource group name
   Region	the Azure region of your resource group
   Authentication method	Use SQL authentication
   Managed instance admin login	MILab
   Password	your Azure password (used for SQL/MI authentication)

   

   Region Code	Portal UI
   eastus	(US) East US
   eastus2	(US) East US 2
   southcentralus	(US) South Central US
   westus2	(US) West US 2

7. Select Next: Networking > at the bottom.

8. Select the Virtual network / subnet dropdown, then select the existing subnet: SQLMI-VNET/ManagedInstanceSubnet.

   

   You’re using the same virtual network as the VM that’ll be the primary replica for the SQL database.

9. Next to Public endpoint (data), select Disable.

   

10. Select Review + create at the bottom to review the settings, but do not create.

    Do not create. A Managed Instance with these configurations is already being deployed.

    A Managed Instance may take up to 6 hours to deploy, unless it meets the various requirements for fast provisioning, which can take under 30 minutes.

At the start of this lab, an Azure VM with the resources you’ll need for this exercise, began deployment. You’ll need to verify its completion.

1. In the same tab, expand the portal menu by selecting the menu icon in the upper left, then select Resource Groups.

   

2. Select OK to discard the unsaved edits.

3. Select your recently created resource group.

4. Next to Deployments, if it still shows 1 Deploying, select it.

   

5. Select the numbers listed under Deployment name.

   

6. Under the Resource column, wait until SQLVM1/CustomScriptExtension shows a checkmark.

   

   This may take 5-10 minutes to complete. You do not need to wait for the SQL managed instance deployment at this time. You’ll return to this tab at a later step to verify completion.

1. In the upper left, right-click the ResourceGroup1 - Deployments breadcrumb link, then select Open link in new tab to go to your recently created resource group.

   

2. Under your Resources, copy and paste the name of your SQL managed instance in a notepad document for future use.

   It will be formatted free-sql-mi-[random]. This value will continue to be referenced throughout this lab.

   

   Do not enter the value from the screenshot.

3. Select the SQLVM1 Virtual machine.

   

   This is a Windows Server 2019 VM with SQL Server 2019.

4. On the left service menu, select Connect, then select Connect.

   

5. In the Native RDP tile, select Download RDP file.

   

6. Select the file from the Edge Downloads to open. You can access it again from the Windows 11 Downloads folder.

   

7. Select the checkbox for Don’t ask me again…, then select Connect.

   

8. Enter the Azure VM credentials, then select OK to connect.

   Item	Value
   Username	VMLab
   Password	your Azure password (used for SQL/MI authentication)

9. On the warning dialog, select the Don’t ask me again… checkbox, and select Yes.

   

You’ll need to create a database master key as part of the requirements to setup the Managed Instance link.

1. On the SQLVM1 desktop, open SQL Server Management Studio.

   

2. Select the Trust server certificate checkbox, then select Connect.

   

3. Select New Query on the toolbar.

   

4. Create a database master key in the master database by pasting the following query:

    -- Run on SQL Server
    -- Create a master key
    USE master;
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '@lab.VirtualMachine(Workstation1).Password';
   

   Replace ‘@lab.VirtualMachine(Workstation1)’ with the password of the machine you’re working on, be it the local machine or a Virtual Machine.

   Selecting Copy on these code blocks and pasting will be much quicker than using Type.

5. Select Execute on the top toolbar.

   

The Managed Instance link relies on the Always On Availability Groups feature of SQL Server, which is disabled by default.

For more information, see Enable the Always On availability groups feature.

1. Select the Windows start menu, then enter and select SQL Server 2019 Configuration Manager.

   

2. On the left menu, select SQL Server Services.

3. Right-click SQL Server (MSSQLSERVER), then select Properties.

   

4. Select the Always On Availability Groups tab.

5. Select the Enable Always On Availability Groups checkbox, then select Apply.

   

6. Select OK on the Warning dialog. Keep the Properties window open.

To optimize the performance of your link, it’s recommended to enable the following trace flags at startup:

• -T1800: This trace flag optimizes performance when the log files for the primary and secondary replicas in an availability group are hosted on disks with different sector sizes, such as 512 bytes and 4 KB. If both primary and secondary replicas have a disk sector size of 4 KB, this trace flag isn’t required.

  For more information, see KB3009974.

• -T9567: This trace flag enables compression of the data stream for availability groups during automatic seeding. The compression increases the load on the processor but can significantly reduce transfer time during seeding.

1. Select the Startup Parameters tab.

2. Enter -T1800, then select Add.

3. Enter -T9567, then select Add.

   

4. Select OK, then select OK on the Warning dialog.

5. Restart SQL Server by right-clicking SQL Server (MSSQLSERVER), then select Restart.

   

For more information, see the syntax to enable trace flags.

1. Go back to your open SQL Server Management Studio window.

2. Replace your previous query for setting the master key with the following:

    -- Shows if the Always On availability groups feature is enabled
    SELECT SERVERPROPERTY ('IsHadrEnabled') as 'Always On enabled (1 true, 0 false)';
    GO
    -- Lists all trace flags enabled on SQL Server
    DBCC TRACESTATUS;
   

3. Select Execute on the top toolbar.

   

   Always On enabled should be 1.

   1800 and 9567 should be listed.

   If not, restart SQL Server again from SQL Server Configuration Manager.

Congratulations! You’ve successfully completed this task.