In the world of DevOps, there are some situations where you need to have a way to transfer data from different Azure SQL databases.
For examinationAs an example, Let's say you have a customer who inadvertently deleted a large number of records from a table in the production database. With the help of Backups at a specific time, we could restore the database to an earlier point in time where those records still exist. This procedure will create a new database with the restored content and then we could exchange names between these databases by renaming them. Despite this, there is a caveat to perform this action. The restored database will lose all point-in-time history of the original database.
A better approach would be to restore the deleted records to the original database. Despite this, this is not as simple as regular SQL Server databases. Both databases cannot see each other even if they are on the same server and, as an example, can not use Linked servers in Azure SQL to connect them.
You might have the option to export those records to SQL scripts, but even then, there are some cases where the amount of data to be restored is very large, so creating those scripts would not be the best way to fix this problem.
This is where Elastic queries come to the rescue:
With this technology, we could easily manage the data entering and leaving our databases.
requirements
Tutorial
Let's imagine that the client truncates all the records for the year 2016 of the Dear table in our invoice database.
The first step should be to connect to the Azure Portal and perform a restore at a specific point in time invoices in the cloud database from a date and time when those records still exist. We will restore this to a database called invoices2016.
These would be the steps we must take to restore those records:
Create a database master key and scope credentials
The master key will work as a “vault” to save the credentials to the database and the scoped credentials are the database credentials that will be used to connect to your restored database.
Syntax:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE DATABASE CREDENTIAL
Example:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘p @ ssw0rd’;
CREATE CREDENTIAL SCOPE OF THE dbCredential DATABASE WITH IDENTITY = 'user01', SECRET = ‘p @ ssw0rd’;
Create the external data source
The external data source is where we set the parameters together with the previously created credential to allow the connection between databases.
Syntax:
CREATE EXTERNAL DATA SOURCE
(
TYPE = RDBMS,
LOCATION = ‘
DATABASE_NAME = ‘
CREDENTIAL =
)
Example:
CREATE EXTERNAL DATA SOURCE restored Database WITH
(
TYPE = RDBMS,
LOCATION = ‘cloudinvoices.database.windows.net’,
DATABASE_NAME = ‘facturas2016’,
CREDENTIAL = dbCredential
);
Create the external tables
You should pay attention that external tables are extensions of the external database. Think of them as linked tables. Which means that we cannot have two tables with the same name in our production database, even if one is a normal table and the other is an external table.
In our example, if we want to connect to Dear table in the restored database, we would need rename it first (as an example, Estimates 2016 ) so that it does not conflict with the existing one.
Syntax:
sp_rename ‘object_name’, 'new name';
Example: in the restored database:
sp_rename 'Estimates', ‘Estimates2016’;
Now we can create our outer table:
Syntax:
CREATE EXTERNAL TABLE [table_name] (
{
)
{WITH (
)
Example:
CREATE EXTERNAL TABLE [Estimates2016] (
[EstimateId] unique identifier NOT NULL,
[ClientId] int NO NULO,
[Year] int NO NULO,
[Amount] decimal NOT NULL
)
WITH (
SOURCE_DATOS = database restored
);
After performing the necessary steps to create the connectivity between both databases, we can do the actual work needed to restore the missing records. As an example:
INSERT IN ESTIMATES
SELECT * OF ESTIMATES 2016 WHERE YEAR = 2016;
At the end of this task we must do a cleaning:
DROP EXTERNAL TABLE Estimates2016;
DROP EXTERNAL DATA SOURCE restoreDatabase;
DROP DATABASE SCOPED CREDENCIAL dbCredential;
DROP MASTER KEY;
That's! I hope you find this new technology useful for these types of scenarios.. Please note that this could help you in many more cases: query external databases, cross-database queries for horizontal partitions ( fragmentation ), query multiple databases to generate reports in PowerBI, etc.
More information on elastic queries:
See you in the next post. Never stop learning.
