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