Running SQL Server database locally when using Azure-specific functionality?

Question

I'm looking into migrating a project using on-premises SQL Server to SQL Azure. The database has cross-database dependencies, where some views reference another database on the same server, which is not supported by SQL Azure.

I've successfully modified these to use external tables that use an external data source which is created using the following:

CREATE EXTERNAL DATA SOURCE [MyExternalDataSource] WITH
(
    TYPE = RDBMS,
    LOCATION = 'SqlServerName',
    DATABASE_NAME = 'SqlServerDatabaseName',
    CREDENTIAL = cred
);

However, the RDBMS type used by this type of data source appears to be supported by SQL Azure only and not on-premises SQL Server. Running this script against the former works correctly, while the latter causes the following error to be thrown:

Incorrect syntax near 'RDBMS'

As a result, it means that the database project in the solution cannot be used to target both Azure SQL and a local SQL Server installation.

As a workaround, I'm considering the following options, though each have their drawbacks:

Have multiple database projects in the solution, with one targeting SQL Azure and other targeting SQL Server - though this will mean duplicating any database updates to both projects.
Move away from using external tables to the data sync functionality offered by Azure instead, and duplicate the tables and data in both databases (removing the need to use the external data source causing the problem) - though this has a minimum 5 minute delay (may be acceptable in this specific case, but certainly not for everyone)

How are other developers handling this sort of scenario where SQL Azure specific features like this need to be used on your project but you also need to be able to run a local copy of the database for development as well?

score 0 · Answer 1 · answered May 15 '19 at 18:09

You can try to create 3 projects:
1. Project contains only Azure specific references defined as a views.
2. Project contains only SQL Server specific references defined as a views. Structure of the views should be the same.
3. Your primary project. In this project you will reference one of the previous ones as a "composite project" reference.
https://learn.microsoft.com/en-us/sql/ssdt/add-database-reference-dialog-box?view=sql-server-2017

score 0 · Accepted Answer · answered May 16 '19 at 17:01

Several approaches are outlined here: Conditional logic in PostDeployment.sql script using SQLCMD

After trying all the options, the one that worked for me was using a build event.

Using this method, I setup the project to use an empty script as the predeployment script, and then replace this with the correct script before building (Debug for On-Premises and Release for Azure). After building, the placeholder script is copied back over it to avoid the changed file being committed to source control.

Here is the folder structure:

In the project file, I added the following BeforeBuild and AfterBuild targets:

<Target Name="BeforeBuild">
    <Copy Condition=" '$(Configuration)' == 'Debug' " SourceFiles="Pre-Deployment\OnPremises.PreDeploymentScript.sql" DestinationFiles="Pre-Deployment\PreDeploymentScript.sql" OverwriteReadOnlyFiles="true" />
    <Copy Condition=" '$(Configuration)' == 'Release' " SourceFiles="Pre-Deployment\Azure.PreDeploymentScript.sql" DestinationFiles="Pre-Deployment\PreDeploymentScript.sql" OverwriteReadOnlyFiles="true" />
  </Target>
  <Target Name="AfterBuild">
    <Copy SourceFiles="Pre-Deployment\Empty.PreDeploymentScript.sql" DestinationFiles="Pre-Deployment\PreDeploymentScript.sql" OverwriteReadOnlyFiles="true" />
  </Target>

The Azure pre-deployment script creates the external data source (in a separate script, not shown here) and external tables:

IF (NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = 'MyExternalTable' AND is_external = 1))
    BEGIN
        PRINT 'Creating external table: MyExternalTable'

        CREATE EXTERNAL TABLE [MySchema].[MyExternalTable](
            [Id] [int],
            [Name] [nvarchar](3000) NOT NULL
        )
        WITH
        (
            DATA_SOURCE = [ExternalDataSourceName]
        );

        PRINT 'Created external table: MyExternalTable'
    END
ELSE
    BEGIN
        PRINT 'External table ''MyExternalTable'' is already created'
    END
GO

While the on-premises simply creates some synonyms:

IF NOT EXISTS(SELECT * FROM sys.synonyms WHERE name = 'MyExternalTable')
BEGIN
    CREATE SYNONYM [MySchema].[MyExternalTable] FOR [OtherDb].[OtherSchema].[OtherTable]
END

The views can then seamlessly use this without having to know whether they are getting the data from an external table using an external data source, or a synonym referencing another database on the same server:

CREATE VIEW [dbo].[MyView] AS SELECT * FROM [Myschema].[MyTable]

Although it feels slightly clunky, it seems to work fairly well and allows a single database project to be used for deploying to both Azure and On-Premises (local) SQL Server.

Running SQL Server database locally when using Azure-specific functionality?

2 Answers2