How To Transfer Data Between Databases In Azure SQL

Cross-Database Query in Azure SQL Database

Written by: Eric Rouach - Madeira Data Solutions - 2021

In this demo, we will demonstrate how to perform cross-database

queries in Azure SQL Server.

1) How to transfer data between two databases in Azure SQL?

2) Build a "One-Action-Transfer-Archive-Delete" process using the sp_execute_remote stored procedure.

For this demo, we have created a Microsoft Azure Subscription at https://portal.azure.com/.

Within this account, we have created two databases:

ProdDB

ArchiveDB

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Important tip!!

Do not create a new database from SSMS: you would be automatically charged

according to Azure's default storage pricing tier while you may only need the Basic

2GB storage which is much cheaper.

Therefore, we recommend creating a new Azure SQL database from the Azure Portal.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

In our first scenario we will demonstrate how to transfer data between two databases in Azure SQL:

1) We have connected to our Azure SQL Server using SSMS.

We are connected as system admin.

2) In object explorer, we can see the two databases we previously created:

ProdDB

ArchiveDB

3) Let's create a table in our ProdDB: (Make sure you are in the right DB)

*/

IF OBJECT_ID('dbo.ProdTable', 'U') IS NOT NULL

DROP TABLE dbo.ProdTable;

CREATE TABLE dbo.ProdTable

(

Id INT PRIMARY KEY,

Name VARCHAR(20) NOT NULL,

LastName VARCHAR(30) NOT NULL,

Cell VARCHAR(12) NOT NULL,

Email VARCHAR(60) NOT NULL,

UserId INT

);

GO

--Let's insert some records in our ProdTable

INSERT INTO ProdTable

(

Id,

Name,

LastName,

Cell,

Email,

UserId

)

VALUES

(1,'Vlad', 'Borvski', '91551234567', 'email3@contoso.com', 1),

(2,'Juan', 'Galvin', '95551234568', 'email2@contoso.com', 2),

(3,'Julio', 'Calderon', '95551234569', 'email1@contoso.net',3),

(4,'Fernando', 'Cobo', '86168999', 'email0@email.com', 4);

--Let's check the dbo.ProdTable content:

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

dbo.ProdTable

4) Let's suppose we have been requested to archive some of the records from the

dbo.ProdTable table to the ArchiveDB for organization goals;

Our primary intuition will be to create a table in

ArchiveDB to store the archived records:

First, switch to the ArchiveDB using SSMS; the "USE" statement is not supported in

Azure SQL to switch between databases!

(You will have to switch to the master database first)

IF OBJECT_ID('dbo.ArchiveTable', 'U') IS NOT NULL

DROP TABLE dbo.ArchiveTable;

CREATE TABLE dbo.ArchiveTable

(

Id INT PRIMARY KEY ,

Name VARCHAR(20) NOT NULL,

LastName VARCHAR(30) NOT NULL,

Cell VARCHAR(12) NOT NULL,

Email VARCHAR(60) NOT NULL,

UserId INT

);

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ArchiveTable]

5) Now let's insert a record we wish to archive into the newly created ArchiveTable:

Our primary intuition is to run an "INSERT INTO SELECT" command as following:

INSERT INTO [ArchiveDB].[dbo].[ArchiveTable]

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[ProdDB].[dbo].[ProdTable]

WHERE

[Id] = 4

This command fails! We get the following error:

Reference to database and/or server name in 'ArchiveDB.dbo.ArchiveTable'

is not supported in this version of SQL Server.

The databases can't physically "see" each other in Azure SQL Server.

Therefore, the process for transferring data from one database to another in this case

is not the same as if our SQL Server was On-Premises.

We need to create the following objects in our ArchiveDB:

-master key

-database scoped credential

-external data source

-external table

6) In ArchiveDB, let's create a Master Key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strongpassword123'

--Let's check our Master Key has been created:

SELECT * FROM sys.symmetric_keys