1

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

  • Azure SQL Database doesn't support three part name queries (nor four). You'll need need to remove the database name from all of your queries: dbo.MasterResults – Lamak Jul 8 at 16:35
  • What is your actual mechanism for moving the data? pure TSQL? Are you using a linked server? Using an SSIS package would be more appropriate for this considering you are moving the data to the cloud. – Jonathan Fite Jul 8 at 16:45
  • I was indeed using T-SQL - I'll look at SSIS, thanks for the advice – Jack Whittaker Jul 9 at 8:21
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.