Eric RouachHow 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