How to merge Azure SQL Databases ?

Question

Hello everyone,

What would be the easiest/simplest way to migrate data(tables, data) from 6 azure databases to a new azure databases (no duplicate tables) All databases are on the same subscription, resource group, sql server. Short summary I want to merge 6 databases into one.

Thank you

Answer 1

Azure SQL Data Sync can do the job in this scenario. Create the empty tables using scripts on the central (hub) database and configure Azure SQL Data Sync to do the job. It will copy the tables using BULK INSERTS. Scale up the service tier of the databases involved when syncing.

You can also use Azure Data Factory to copy the tables between databases. You can copy the table in BULK as explained on this video. See this documentation for more information.

For small tables you may find easy to script the data and schema as explained here.

You can also use elastic queries on the central database (hub database) to retrieve data from the tables on the other databases. Below a simplified example:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential  
WITH IDENTITY = '<username>',
SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='<server>.database.windows.net',
    DATABASE_NAME='<db>',
    CREDENTIAL= SQL_Credential
);

CREATE EXTERNAL TABLE [dbo].[source_table] (
    [Id] BIGINT NOT NULL,
    ...
)
WITH
(
    DATA_SOURCE = RemoteReferenceData
)

SELECT *
 INTO target_table
FROM source_table