This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
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;
