SQL Managed Instances – Cross Instance?

There is a setting / feature in Managed Instance worth talking about, it is called SQL Trust Groups.

What is this? At a basic level it’s a way to get 2 separate instances to communicate with each other which could be in different regions and naturally different Vnets / subnets via a certificate-based trust. Why? If you need those cross instance scenarios – I am thinking distributed transactions as per below: Distributed transactions across cloud databases (preview) – Azure SQL Database | Microsoft Docs

Let’s build one.

Then click add and then add the relevant instances.

Another part of the puzzle is the need to link the entities with linked server.

    EXEC sp_addlinkedserver
        @server='RemoteServer', -- Linked server name
        @srvproduct='',
        @provider='sqlncli', -- SQL Server Native Client
        @datasrc='sqldevseeepaceny.b5367f63eec8024.database.windows.net' -- SQL Managed Instance endpoint

    -- Add credentials and options to this Linked Server
    EXEC sp_addlinkedsrvlogin
        @rmtsrvname = 'RemoteServer', -- Linked server name
        @useself = 'false',
        @rmtuser = 'yourlogin',         -- login
        @rmtpassword = 'whatever' – password

So, a server trust group + Linked server = Distributed transactions.

    SET XACT_ABORT ON;
    GO
    BEGIN DISTRIBUTED TRANSACTION;
    -- Delete candidate from local instance.
    DELETE [AKSDWH[.[SalesLT].[Address]
        WHERE AddressID = 13;
    -- Delete candidate from remote instance.
    DELETE [AKSDWH[.[SalesLT].[Address]
        WHERE AddressID = 1;
    COMMIT TRANSACTION;
    GO

3 thoughts on “SQL Managed Instances – Cross Instance?

  1. Pingback: Dew Drop – December 15, 2021 (#3579) – Morning Dew by Alvin Ashcraft

  2. Pingback: ➧Dew Drop – December 15, 2021 (#3579) • Softbranchdevelopers

  3. Pingback: Dew Drop – December 15, 2021 (#3579) - Online Code Generator

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s