Cross Transaction query - sql managed instance

M, Murugeswari (Cognizant) 456 Reputation points
2022-03-17T08:03:09.33+00:00

Hi,

How to achieve cross-database transaction/queries in azure sql managed instance. I could see many results. Some of them are below:-

I. Azure Managed instance has almost same feature with on-premise SQL server, you could use USE statement to execute cross database query. It's same with local SQL Server


II. Distributed transactions on Managed Instance can easily be setup with two simple steps:

Create Server trust group.

Ensure there is network connectivity between your instances

III. For Managed Instance, you can create linked servers to do a cross database queries.

So, among these three, which one is the suitable method and if any detailed document of "how to implement" is available, it will be much appreciated

Thanks

Azure SQL Database
{count} votes

Accepted answer
  1. Oury Ba-MSFT 16,901 Reputation points Microsoft Employee
    2022-03-18T22:12:50.873+00:00

    @Anonymous In response to the above ask. Please let me know if my understanding is not correct. Which methods is more suitable (Linked servers, distributed transactions)?
    Both ways (Linked Servers & Distributed Transactions) have their merits and their limitations. Distributed Queries and Distributed Transactions | Microsoft Learn
    Are you looking for distributed transactions between multiple SQL Managed Instances, then Server Trust Group needs to be setup, and T-SQL (linked servers), or .NET can be used to implement distributed transactions? or you are looking for distributed transactions between SQL Managed Instance and something else (SQL Server, DB2, Oracle, ...).

    Please share with us a bit more details on the usage scenario.

    Regards,
    Oury


2 additional answers

Sort by: Most helpful
  1. Alberto Morillo 33,001 Reputation points MVP
    2022-03-17T12:07:35.377+00:00

    Please refer to this documentation for Distributed Transactions with Azure Managed Instance. There you will find examples with T-SQL (using linked servers) and with .NET.

    1 person found this answer helpful.
    0 comments No comments

  2. Bert Zhou-msft 3,421 Reputation points
    2022-03-18T01:24:33.113+00:00

    Hi,@Anonymous

    Welcome to Microsoft T-SQL Q&A Forum!

    Azure SQL Database does not support cross-database and cross-instance queries using three-part or four-part names, so depending on your needs, Make sure the source database will be an Azure SQL Managed Instance And The target database will be an Azure SQL Database.

    Steps required for the target database (Azure SQL Database):

    1. Create the required login, user and credentials on Azure SQL Database: --master database
      create login LgTest with password = 'STRONGP@123'
      -- user database
      create user User1 for login LgTest
      alter role [db_owner] add Member [User1]
      -- user database
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STRONGP@123';
      GO
      -- user database
      CREATE DATABASE SCOPED CREDENTIAL User1
      WITH IDENTITY = 'LgTest', SECRET = 'STRONGP@123' -- this user needs to have sufficient rights on your Azure SQL MI.
      GO

    2) Create an external data source that contains a connection to Azure SQL Managed Instance, you can create it with the following script after adding your location (FQDN), database name, and CREDENTIAL created earlier:

    CREATE EXTERNAL DATA SOURCE RemoteReferenceData    -- create the external data  
    WITH  
    ( TYPE=RDBMS,  
    LOCATION='tcp:******.public.******.database.windows.net,3342',  
    DATABASE_NAME='*****',  
    CREDENTIAL= User1, -- the comma is important to work   
    );  
    

    3) Create the external table as follows, note that the external table structure should match the original table in Azure SQL Managed Instance:

    -- the external table should be same as the table in your Azure SQL managed database.   
    CREATE EXTERNAL TABLE [dbo].[DBtable](  
    [col1] [nvarchar](max) NULL,  
    [col2] [nvarchar](max) NULL,  
    [col3] [nvarchar](max) NULL)   
    WITH  
    (  
    DATA_SOURCE= RemoteReferenceData  
    );  
    

    After completing these steps, you should be able to see the created external table and external data source in Azure SQL Database as shown below:
    184326-image.png

    Reminder : When trying the above steps, you must first create and allow required logins and users to access your Azure SQL Managed Instance in the Create and allow required logins and users to access your Azure SQL Managed Instance.

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.