CODEWITHSUNDEEP
sql-serverazureazure-sql-databasedatabase-migration
Akshay Bheda
Akshay Bheda

Reputation: 783

How to migrate on-premises database to azure which has reference to another database?

I am trying to deploy my on-premises database to azure. My database has external references as well. Whenever I try to deploy either through Publish to Azure or DMA, I get the error. This feature is not supported in this version of SQL server.

Upvotes: 1

Views: 377

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15628

One of the options you have is to import those external objects to your database and change the three and four-part name references that SQL Azure does not support.

You can also use CREATE EXTERNAL DATA SOURCE and CREATE EXTERNAL TABLE on SQL Azure to query tables that belong to other databases that you have to migrate to SQL Azure too.

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

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
);

For more information about cross database queries on SQL Azure you can read this article.

Hope this helps.

Upvotes: 4

Related Questions