Lesson Learned #475:Cross-Database Queries Read and Write in Azure SQL Database.

This week, we addressed a service request from a customer who wanted to keep two tables synchronized across different databases on the same Azure SQL Database server. Unfortunately, in Azure SQL Database, it's not possible to directly call the database in the operation, for instance using the command select * from master.sys.sys_databases. For this reason, I'd like to share an alternative to tackle this limitation.

Sync data across two databases:

To synchronize data across two databases, we have two databases named "Source" and "Target." Both of these databases contain a table called LocalTable with the following structure:

CREATE TABLE LocalTable (ID BIGINT PRIMARY KEY, NAME VARCHAR(200))

In Source database we are going to run the following script to update in Target database.  The first thing we are going to create the external data source:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPwd!';

CREATE DATABASE SCOPED CREDENTIAL ElasticQueryCred
   WITH IDENTITY = 'userName',
   SECRET = 'pwd!';

CREATE EXTERNAL DATA SOURCE MyElasticQueryDataTgt WITH
   (TYPE = RDBMS,
   LOCATION = 'servername.database.windows.net',
   DATABASE_NAME = 'target', -- remote database
   CREDENTIAL = ElasticQueryCred,
);

Next we will a store procedure that will receive the parameters and call sp_execute_remote to perform updates in the target database. 

CREATE PROCEDURE SyncOtherTable(@ID BIGINT, @Name Varchar(200))
AS
BEGIN
 BEGIN TRANSACTION
    IF EXISTS( SELECT TOP 1 ID FROM LocalTable WHERE ID = @ID)
	BEGIN
	   UPDATE LocalTable SET Name = @Name WHERE ID = @ID
	END
	ELSE
	BEGIN
	  INSERT INTO LocalTable (ID,NAME) VALUES(@ID,@Name)
	END 
	EXEC sp_execute_remote 
    @data_source = N'MyElasticQueryDataTgt',
    @stmt = N'EXEC SyncOtherTable , @Names',
    @params = N'@IDs BIGINT, @Names VARCHAR(200)',
     = @ID,
    @Names = @Name;
 COMMIT TRANSACTION
END 

In the target database we will create the store procedure:

create PROCEDURE SyncOtherTable(@ID BIGINT, @Name Varchar(200))
AS
BEGIN
 BEGIN TRANSACTION
    IF EXISTS( SELECT TOP 1 ID FROM LocalTable WHERE ID = @ID)
	BEGIN
	   UPDATE LocalTable SET Name = @Name WHERE ID = @ID
	END
	ELSE
	BEGIN
	  INSERT INTO LocalTable (ID,NAME) VALUES(@ID,@Name)
	END 
 COMMIT TRANSACTION
END 

At this point, to synchronize the data in the Target database, we need to run the following T-SQL command in the Source database:

EXEC SyncOtherTable 3,'Demo'

Read data across two databases:

To read data across two databases in a similar situation, consider an example where we have a stored procedure called GetDataFromLocalTableTarget defined in the Source database. The definition of this stored procedure is as follows:

CREATE PROCEDURE GetDataFromLocalTableTarget
    @ID BIGINT,
    @NameOut VARCHAR(200) OUTPUT
AS
BEGIN
    DECLARE @TempTable TABLE (Name VARCHAR(200), [$ShardName] VARCHAR(200));

    INSERT INTO @TempTable
    EXEC sp_execute_remote 
        @data_source = N'MyElasticQueryDataTgt',
        @stmt = N'SELECT Name FROM LocalTable WHERE ID = ',
        @params = N'@IDs BIGINT',
         = @ID;

    -- Selecciona el nombre desde la tabla temporal y lo asigna a la variable de salida
    SELECT TOP 1 @NameOut = Name FROM @TempTable;
END;

Basically, we need to call this store procedure to obtain the data from the target database. 

DECLARE @MyName VARCHAR(200);
EXEC GetDataFromLocalTableTarget @ID = 2, @NameOut = @MyName OUTPUT;
SELECT @MyName AS ResultName;

Other option is create another procedure sending the External Data Source, statement and parameters.

CREATE PROCEDURE GetDataFromLocalTableTargetDynamic
    @ID BIGINT,
	@stmt NVARCHAR(MAX),
	@params NVARCHAR(MAX),
	@DataSource NVARCHAR(MAX),
    @NameOut VARCHAR(200) OUTPUT
AS
BEGIN
    DECLARE @TempTable TABLE (Name VARCHAR(200), [$ShardName] VARCHAR(200));

    INSERT INTO @TempTable
    EXEC sp_execute_remote 
        @data_source = @DataSource,
        @stmt = @stmt,
        @params = @params,
         = @ID;

    -- Selecciona el nombre desde la tabla temporal y lo asigna a la variable de salida
    SELECT TOP 1 @NameOut = Name FROM @TempTable;
END;

and call it 

DECLARE @MyName VARCHAR(200);
EXEC GetDataFromLocalTableTargetDynamic @ID = 2, 
                                        @stmt = N'SELECT Name FROM LocalTable WHERE ID = ', 
										@params = N'@IDs BIGINT',
										@DataSource = N'MyElasticQueryDataTgt',
										@NameOut = @MyName OUTPUT;
SELECT @MyName AS ResultName;