Task 03: Configure link with SSMS

With SQL Server as your initial primary, you’ll need to set its Recovery Model to Full, then run a full backup to meet requirements.

1. In SSMS’s left Object Explorer, right-click the Adatum database, then select Properties

   

2. In the new window, select Options on the left.

   

3. Select the dropdown next to Recovery model, select Full, then select OK.

   

4. Right-click the Adatum database again, select Tasks, then select Back Up…

   

5. Backup type should be set to Full, then select OK.

   

6. Select OK on the completion dialog.

The link supports replicating user databases only. Replication of system databases is not supported. To replicate instance-level objects (stored in master or msdb), script them out and run T-SQL scripts on the destination instance.

In this task, you’ll use the New Managed Instance link wizard in SSMS to create a link between your initial primary and your secondary replica.

1. Right-click the Adatum database again, select Azure SQL Managed Instance link, then select New.

   

2. On the Introduction page, select Next.

3. Enter AdatumLink in the Name field, then select Next.

   

4. On SQL Server requirements, everything under the Server readiness tab will be Ready.

   

5. Select the Availability group readiness tab.

6. If you select the warnings under the Result column, you’ll see that both will automatically be created, so these are safe to ignore.

   

   

7. Select Next to proceed.

8. Select the checkbox next to Adatum, then select Next.

   

9. On Specify Secondary Replica, select Add secondary replica…

   

10. Select Sign In…, then sign in with your lab’s Azure credentials:

    Item	Value
    Username	Your Azure portal username
    Password	Your Azure portal password

    If Internet Explorer opens, change your default browser to Edge and try again. Select this box to expand for details.

    1. Select the Windows Start menu.
    2. Enter and select Default apps.

    3. Under Web browser, select Internet Explorer, then select Microsoft Edge.

       

11. Once authenticated, close the browser window.

12. SSMS should automatically select:

    • your Azure subscription name
    • your resource group name
    • the NSG name attached to your MI

    If it does not find the SQL Managed Instance, you may have to restart the Managed Instance from the Azure portal.

13. Select Sign in…, under Sign in to selected SQL Managed Instance.

14. Use the following on the Connect to Server window:

    Item	Value
    Authentication	SQL Server Authentication
    Login	MILab
    Password	your Azure password (used for SQL/MI authentication

15. Select Connect.

    

16. Select OK on the Sign in window to close it.

17. You’ll leave the settings on the Specify Secondary Replica step as is. You can check the other tabs for Endpoints, Backup, and Link Endpoint, then select Next to proceed.

18. All validation results should show Ready. Select Next.

    

    If you receive any errors, try selecting Re-run validation near the bottom right.

19. Select Finish to create the link.

20. All entries should show Success. Select Close.

    

After the link is created, your database is replicated to the secondary replica. Depending on database size and network speed, the database might initially be in a Restoring… state on the secondary replica. After initial seeding finishes, the database is restored to the secondary replica and ready for read-only workloads.

1. In SSMS’s Object Explorer, select SQLVM1, then select the refresh icon on the top controls of that pane.

   

2. In Object Explorer, expand Databases. The Adatum database will now be appended with (Synchronized).

   

3. In Object Explorer, expand Always On High Availability, then expand Availability Groups to view the distributed availability group for your link.

   

4. Right-click AdatumLink (Distributed), then select Show Dashboard.

   

   

   You can view the dashboard from either replica, which shows the status of the linked database in the distributed availability group.

5. Under Availability replica on the dashboard, the Synchronization state column should show:

   • Synchronized for AG_Adatum.
   • Synchronizing for AG_Adatum_MI.

   

If SQL Server is your initial primary, it’s important to take the first transaction log backup on SQL Server after initial seeding completes, when the database is no longer in the Restoring… state on Azure SQL Managed Instance.

You should take SQL Server transaction log backups regularly to minimize excessive log growth while SQL Server is in the primary role.

1. In Object Explorer, under Databases, right-click the Adatum (Synchronized) database, select Tasks, then select Back Up….

2. In the new window, next to Backup type, select Transaction Log from the dropdown.

   

3. Under Destination, select Add….

   

4. Select the ellipses for File name.

5. Enter initial_transaction_log for File name, then select OK.

   

6. Select OK on the Select Backup Destination window.

7. Feel free to review the other pages for Media Options and Backup Options on the leftmost menu. You’ll leave everything as default in this lab.

8. Select OK to run the backup.

   

9. Select OK on the backup completion dialog.

Congratulations! You’ve successfully completed this task.