Cross Transaction query - sql managed instance

Question

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

Answer 1

@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

Answer 2

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.

Answer 3

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：

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.