Let’s say you are connected to an Azure SQL server, and you want to make a query to compare the content of two tables on different servers (for instance, to compare the data after a migration).
This is how you can do it.
First step : prepare the remote table definition
Connect to the remote database, and generate the table definition. You may remove the unnecessary columns. Do not copy the index or primary key part, just the table and column part.
In Notepad++ (or any other text editor with regexp capabilities), remove the “DEFAULT” constraints :
Second step : create the remote table reference
Now, connect to the server from which you want to query, prepare and run the following script :
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P#rj5++yFIjAy##*'; -- random CREATE DATABASE SCOPED CREDENTIAL AxSqlCredentials WITH IDENTITY = 'sqladmin', SECRET = 'e*XXXXXIBmz'; -- the password CREATE EXTERNAL DATA SOURCE RemoteReferenceData WITH ( TYPE=RDBMS, LOCATION='fxdhcgvhjssf.database.windows.net', DATABASE_NAME='axdb_0abhjgjvgjfz1108', CREDENTIAL= AxSqlCredentials ); CREATE EXTERNAL TABLE [dbo].[InventTable]( [ITEMID] [nvarchar](20) NOT NULL , [ABCCONTRIBUTIONMARGIN] [int] NOT NULL , [ABCREVENUE] [int] NOT NULL , ... [CREATEDBY] [nvarchar](20) NOT NULL ) WITH ( DATA_SOURCE = RemoteReferenceData );
Now that the reference to the table is ready, you can query the table as if it was local :
select * from dbo.InventTable
This technique can be used to distribute heavy processing accross serveral SQL servers.