CODEWITHSUNDEEP
azureazure-sql-databaselinked-serverazure-sql-server
Priyanka2304
Priyanka2304

Reputation: 210

Query tables from different servers in Azure SQL

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

Upvotes: 0

Views: 1434

Answers (1)

Aswin
Aswin

Reputation: 7146

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. enter image description here

  • Then, Table is added in both dbs. table name is same in both. enter image description here

  • 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. enter image description here

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

Upvotes: 1

Related Questions