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.
dbo.MasterResults
– Lamak Jul 8 '19 at 16:35