Query tables from different servers in Azure SQL

Question

I am looking to understand if there is a way to write a Stored Proc that includes columns from different servers(env). I want to write a stored proc to validate a process to check the difference between records count between a test env and prod env.

TIA

Answer 1

To query Azure DBs of different sql server, Elastic query can be used.

• In this repro, two SQL servers are created. One for test and another for prod.

• Then, Table is added in both dbs. table name is same in both.

• Use the following code and change the configuration as per the requirement.

CREATE MASTER KEY; -- create master key
GO
 
-- credential maps to a login or contained user used to connect to remote database 
CREATE DATABASE SCOPED CREDENTIAL CrossDbCred1 -- credential name
WITH IDENTITY = 'username',                    -- login or contained user name
SECRET = '**********';                    -- login or contained user password
GO
 
-- data source to remote Azure SQL Database server and database
CREATE EXTERNAL DATA SOURCE source
WITH
(
    TYPE=RDBMS,                           -- data source type
    LOCATION='server.database.windows.net', -- Azure SQL Database server name
    DATABASE_NAME='database1',         -- database name
    CREDENTIAL=CrossDbCred1                -- credential used to connect to server / database  
);
GO
 
-- external table points to table in an external database with the identical structure
CREATE EXTERNAL TABLE [dbo].[ext_src]
(
    [Id] [int]
)
WITH (DATA_SOURCE = [source],  -- data source 
      SCHEMA_NAME = 'dbo',           -- external table schema
      OBJECT_NAME = 'source'       -- name of table in external database
    );
GO

• SQL query to compare the count between the tables is given for this demo.

Similarly, we can write Stored Procedure to compare the counts.