Ever since we started to develop our Data Science off the Shelf (DOTS) products at Cmotions our usage of Azure SQL databases has increased significantly. Since we value the privacy of our customers, and of the customers of our customers, we strongly believe in privacy-by-design. For us, this also means we end up with quite a few different databases, which sometimes partly need the same information (of course without violating any privacy decisions we’ve made). Being used to working with an on-premise SQL server, we approached this problem as we would normally do: simply use two databases in the same query. Little did we know! Each Azure database might be on the same Azure server from our point of view, but since this is a virtual server, Azure does not view them as being on the same server. We also tried to use the other well-known solution: linked servers, but also without any success. But luckily for us there is an easy solution for this problem; we can reference a database from another database and use the necessary tables anyway.
We start by opening our SQL Server Management Studio and connecting to the database we want to copy/use data from another database in (our target database). All code below will be executed in this target database. If we talk about a source database, we mean the database where the data lives which we want to copy or use in our target database.
In the script below we assume you want to copy data from one database to another, but you could of course also keep the connection and reference table(s) and use them like that in your queries from now on. This could be especially useful if you have some dataset that you need in multiple databases and that changes regularly.
-- TARGET DATABASE: DATABASE YOU ARE CURRENTLY CONNECTED TO
-- SOURCE DATABASE: DATABASE YOU WANT TO CONNECT TO FROM YOUR TARGET DATABASE
------------------------
-- CREATE CREDENTIALS --
------------------------
-- one time only: create a master key and supply the necessary credentials to access the source database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'justsomerandompasswordweonlyneedinthisscript';
CREATE DATABASE SCOPED CREDENTIAL cred
WITH IDENTITY = 'the username to access the source database',
SECRET = 'the password belonging to the username to access the source database';
---------------------------------
-- CREATE EXTERNAL DATA SOURCE --
---------------------------------
-- once for every different source database you want to access
CREATE EXTERNAL DATA SOURCE name_of_source_database
WITH
(
TYPE=RDBMS,
LOCATION='servername.database.windows.net',
DATABASE_NAME='databasename',
CREDENTIAL= cred
);
---------------------------
-- CREATE EXTERNAL TABLE --
---------------------------
-- now we can access the source database
-- in order to do this we create a reference table in our target database
-- this table directly references to the table/view from the source database
-- this external table is a read-only table
-- the column specifications need to exactly match that of the original table in the source database
CREATE EXTERNAL TABLE [dbo].[name_of_temporary_external_reference_table](
[COL1] [varchar](255) NULL,
[COL2] int NOT NULL,
-- ...
)
WITH
(
DATA_SOURCE = name_of_source_database,
SCHEMA_NAME = 'dbo',
OBJECT_NAME = 'name_of_object_you_want_to_reference_to'
)
-----------
-- QUERY --
-----------
-- now we can copy the data we want to our own database
SELECT *
INTO name_of_new_table_in_target_database
FROM name_of_temporary_external_reference_table
--------------
-- CLEAN-UP --
--------------
-- after all data is copied we want to remove everything we created to keep our database clean
-- drop the external table
DROP EXTERNAL TABLE name_of_temporary_external_reference_table
-- drop the reference to the other database
DROP EXTERNAL DATA SOURCE name_of_source_database
-- drop the credentials
DROP DATABASE SCOPED CREDENTIAL cred
-- drop the master key
DROP MASTER KEY
