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


7) Still in ArchiveDB, let's create a DATABASE SCOPED CREDENTIAL:


CREATE DATABASE SCOPED CREDENTIAL DBScopedCredential

WITH

IDENTITY = 'yourlogin', --the IDENTITY is the Login Name we used to login to the Server.

SECRET = 'yourpassword'; --the SECRET is the Password we used to login to the Server.


8) Now, still in ArchiveDB, let's create an External Data Source to determine which

database is going to be our data source:


CREATE EXTERNAL DATA SOURCE ProdDatabase

WITH

(

TYPE = RDBMS,--Relational Database Management System

LOCATION = 'yourserver.database.windows.net', -- Our server name.

DATABASE_NAME = 'ProdDB', -- The database we use as our data source.

CREDENTIAL = DBScopedCredential -- the name we gave to our DATABASE SCOPED CREDENTIAL.

);


9) Let's create an External Table in ArchiveDB; the external table will be used as a

"temporary copy" of the [dbo].[ProdTable] from the ProdDB database:


CREATE EXTERNAL TABLE dbo.ProdTable_Ext

(

Id INT ,

Name VARCHAR(20) NOT NULL,

LastName VARCHAR(30) NOT NULL,

Cell VARCHAR(12) NOT NULL,

Email VARCHAR(60) NOT NULL,

UserId INT

)

WITH

(

DATA_SOURCE = ProdDatabase,

SCHEMA_NAME = 'dbo',

OBJECT_NAME = 'ProdTable'

);


--Let's check the external table dbo.Copy_Of_ProdTable_Ext has been created


SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

dbo.ProdTable_Ext


10) Now we can easilly insert data into the [dbo].[ArchiveTable]:


INSERT INTO [dbo].[ArchiveTable]

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ProdTable_Ext]

WHERE

Id = 4


--Let's check if the specified row has been inserted into [dbo].[ArchiveTable]


SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ArchiveTable]


If necessary, we can use a simple DELETE statement to delete the archived row from

the [dbo].[ProdTable] table.


=====================================================================


Now, we will Build an "Archive & Delete" process using the sp_execute_remote stored procedure

without using an external table.


This very useful object will allow us to remotely execute a stored procedure

in one database from another database.


We still need the previously created objects:

-master key

-database scoped credential

-external data source


Let's have a demo to show this process:

Let's switch to the ProdDB


1) Let's check for the existing records in the [dbo].[ProdTable]


SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ProdTable]

GO


2) In the [ProdDB] database, let's create a stored procedure for selecting

a record from the [dbo].[ProdTable] table:


CREATE OR ALTER PROCEDURE usp_select_from_ProdTable

@Id INT


AS

BEGIN

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ProdTable]

WHERE

Id = @Id