Regularly Archiving Data to Azure

Question

I have a database – call it Jacob. Every day a SQL Agent job copies all records older than 15 days to another database called Jacob_Archive

I would like to move Jacob_Archive to Azure, I’ve created a database with the tables, but when I try to move the data I get:

Msg 40515, Level 15, State 1, Line 16 Reference to database and/or server name in Jacob_Archive.dbo.MasterResults` is not supported in this version of SQL Server.

Please advise

Answer 1

On the Jacob_Archive database you need create an External Data Source that makes reference to the source database.

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='myserver.database.windows.net',
    DATABASE_NAME='ReferenceData',
    CREDENTIAL= SqlUser
);

Then create External Tables for all tables on the source database that you want to archive data.

CREATE EXTERNAL TABLE [dbo].[zipcode](
    [zc_id] int NOT NULL,
    [zc_cityname] nvarchar(256) NULL,
    [zc_zipcode] nvarchar(20) NOT NULL,
    [zc_country] nvarchar(5) NOT NULL
)
WITH
(
    DATA_SOURCE = RemoteReferenceData
);

Then extract all the data you want from the remote table using an elastic query. You can create a stored procedure to extract the data you want to archive and put it on a table located on the Jacob_Archive database.

Once you have extracted the information you can run a stored procedure on the source table to remove the archived data. You can create an schedule task to perform/run both stored procedures using Azure Automation.