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

Question

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.

Answer 1

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.