SQL Database Project: build different scripts depe

Question

The problem I want to solve is to build different scripts depending on build configuration.

Say we have two instances of SQL Server:

• Enterprise version with connected Linked servers
• LocalDb version for offline developing and unit tests

Enterprise version has views for Linked servers when LocalDB substitues those views with local tables.

Those Linked Server views and local tables have the same names and set of fields. So they are not included in build by default (Build Action = None). Instead they are included in build in BeforeBuild Target of the project file.

<Target Name="BeforeBuild">

    <ItemGroup Condition=" '$(Configuration)' == 'LocalDb'">
        <Build Include="Local_Tables\*.sql" />
    </ItemGroup> 

    <ItemGroup Condition=" '$(Configuration)' != 'LocalDb' ">
        <Build Include="Linked_Server_Views\*.sql" />
    </ItemGroup>

</Target>

But the problem is that Visual Studio caches the DB Model and if we first build project for LocalDb and then try build project for Enterprise configuration - Visual Studio outputs errors:

Error: SQL71508: The model already has an element that has the same name

If to close and open solution or Unload Project and Reload Project, Visual Studio recreates dbmdl files and Enterprise configuration is being build without errors.

So my assumption is if I refresh dbmdl cache I will get smooth build without error.

---

When you open or reload SQL Server database project in Visual Studio 2012, it creates a file with extension dbmdl, which is a deserialized and cached db model as described here.

In the moment of the dbmdl file recreation Visual Studio outputs the following:

Deserializing the project state for project 'MyProject.sqlproj'...
Detecting file changes for project 'MyProject.sqlproj'...
Deserialization has been completed for project 'MyProject.sqlproj'.

How to force Visual Studio to refresh the dbmdl cache without project reload and without changing of the project xml file?

Is there a way to refresh the the dbmdl cache placing a command into BeforeBuild or AfterBuild targets of the project xml file?

Or the whole approach to the problem is wrong and there is another way to build different scripts depending on build configuration?

Answer 1

I was thinking about this and the best way to handle it with SSDT. I probably don't have a "best" way, but if you can determine the correct version prior to publishing your changes, I'd consider this:

1. Create a publish profile for each edition - with and without linked servers.
2. Create variables to hold your linked server names, possibly including the database as well so something like "[Server].[Database]."
3. Create a post-deploy script for your linked server views. These should include permissions, the variables for the linked server names, and so on.
4. In the Post-Deploy script, query your "edition" variable. If it will use linked servers, drop/recreate the native non-linked server views in the project to use the ones on the linked servers. Alternatively, set the variable to an empty string for the local view and to the server/DB for the linked server and you can probably just use one set of code.

This has the disadvantage of not being able to code-check your views, but would give you one place to store those linked server views and one place from which to deploy them. You'd need to release with a Drop/Create instead of letting SSDT handle the changes which means they would be re-created on each Publish action. I think it might give you the solution you're seeking, though.

Answer 2

You may have another possible option in using composite projects. Jamie Thompson blogged about them here: http://sqlblog.com/blogs/jamie_thomson/archive/2013/03/10/deployment-of-client-specific-database-code-using-ssdt.aspx

This would let you build a main project and tie in additional projects with the environment-specific code. You could deploy the appropriate one by doing some checks in the release scripts.