3

SQL Managed Instances – Cross Instance? | All About Data

 2 years ago
source link: https://blobeater.blog/2021/12/15/sql-managed-instances-cross-instance/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

SQL Managed Instances – Cross Instance?

Posted on December 15, 2021

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

This entry was posted in Azure, Azure SQL DB, Managed Instances by blobeater. Bookmark the permalink.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK