How to handle master database reference

[BenjaminAbt]

I have two projects

• MyProject.Database.Mssql - classic Sqlproj project to develop the database
• MyProject.Database.Mssql.Build - to build the DACPAC

My Sqlproj project has a reference to the master database. Otherwise Visual Studio would throw some errors like

Severity Code Description Project File Line Suppression State Error SQL71501 Error validating element [dbo].[ViewName]: SqlView: [dbo].[ViewName] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[TableA].[rows], [dbo].[TableB].[rows] or [sys].[partitions].[rows]. MyProject.Database.Mssql C:\s\MyProject\src\MyProject.Database.Mssql\EXEC 1

But since the building project now imports the SQL scripts, Visual Studio throws the corresponding error messages in the build project, because there is no reference to the master database.

How do you deal with this? I could not find any reference in the documentation.

My build project:

<Project Sdk="MSBuild.Sdk.SqlProj/1.6.0">
    <!-- https://github.com/rr-wfm/MSBuild.Sdk.SqlProj -->

    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlServerVersion>SqlAzure</SqlServerVersion>
        <!-- For additional properties that can be set here,
        please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
    </PropertyGroup>

    <PropertyGroup>
        <!-- Refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#publishing-support for supported publishing options -->
    </PropertyGroup>

    <ItemGroup>
        <Content Include="..\..\db\MyProject.Database.Mssql\dbo\**\*.sql" />
    </ItemGroup>

</Project>

Environment: Microsoft Visual Studio Enterprise 2019 Version 16.8.0 Preview 3.1 .NET 5 RC 1

[jeffrosenberg]

Hi @BenjaminAbt - the 1.6.0 release added support for PackageReferences to external databases. To do this, you add a PackageReference element with the attribute DatabaseVariableLiteralValue set to the name of the target database. For example:

<PackageReference Include="master" Version="130.0.0" DatabaseVariableLiteralValue="master" />

However, there's one caveat to this approach. Per #40, the project currently only supports references to other projects via PackageReference, and the PackageReference is expected to be a valid NuGet package. The way my company has solved this internally is to take the master.dacpac shipped with the DAC Framework, wrap it up as a .nupkg, and publish that to our internal NuGet repository -- which is what you see referenced in the example above. Thus, my example above works for me personally, but won't work for others without some additional legwork to convert the master.dacpac into a NuGet package.

[jeffrosenberg]

@jmezach @ErikEJ I think this is a relatively-common problem for users -- at least, we've heard about this issue several times, including from myself with #51. It would be technically simple for us to wrap up master.dacpac ourselves and publish it to NuGet to avoid the workaround I described above, but I don't know much about the licensing involved. I suspect that might be a problem, do either of you know?

[jmezach]

Yeah, I agree that this will become a problem as we see more adoption. I wouldn't mind to publish the master databases as NuGet packages on NuGet.org, but I'm really not sure if that is something we can do due to licensing.

I remember having a conversation about this with @ErikEJ a while ago about something similar and he mentioned that we're doing Microsoft a favor here, but that was a small thing really. @ErikEJ Do you think they would have a problem with this?

It would be awesome if someone from the Microsoft team could weigh in on this ;)

[ErikEJ]

Maybe it would be better to include as embedded ressource ,- I think there is also version issues.

[jmezach]

@ErikEJ Not sure what you mean. Do you mean including the master.dacpac as part of the DacpacTool as an embedded resource and then always include a reference to that as part of the build pipeline?

I think that'll work, although we would probably have to write the embedded resource to disk somewhere first. I guess we could use the NuGet package cache for that though.

But what version of the master.dacpac would we include? The latest one? Or is that the versioning issue you're referring too?

[ErikEJ]

Yes, just throwing out some ideas 😄

Do you mean including the master.dacpac as part of the DacpacTool as an embedded resource and then always include a reference to that as part of the build pipeline?

Something like that.

Or is that the versioning issue you're referring too?

Indeed, but we have the SqlServerVersion property available.

[jmezach]

Yeah, I think we can add a reference to the proper master.dacpac based on the SqlServerVersion specified in the project file. I'm also thinking we might not have to make it an embedded resource, because that would significantly bloat the package since we're already building and packaging the DacpacTool for multiple target frameworks (netcoreapp2.1, netcoreapp3.1 and net5.0) and we would essentially include 3 copies of all these .dacpac's.

Instead I'm thinking we could add these master.dacpac files to the MSBuild.Sdk.SqlProj package inside of the tools folder with a separate folder for each SqlServerVersion. Then we could probably add a new property to the Sdk, something like ReferenceMasterDatabase which could be a simple boolean flag that indicates whether a reference to the appropriate master.dacpac is added automagically. It shouldn't be too hard to find these relatively from the Sdk.targets file.

Still not sure whether we can redistribute these master.dacpac files though and I'm not sure how we're going to find out. Maybe we should just do it and deal with the consequences later? ;).

[BenjaminAbt]

Hi, thanks for all of your feedback!

I have seen the functionality with NuGet packages, but I was not aware that this was also used or intended for system databases. Personally I think this is rather cumbersome, because it would mean an additional roundtrip due to the NuGet package, which I cannot maintain directly in the solution (build -> deploy -> restore).

I also think that a reference would be the best and we would also avoid the roundtrip. And the way beyond that already exists in classic Sql projects.

[jeffrosenberg]

I also think that a reference would be the best and we would also avoid the roundtrip. And the way beyond that already exists in classic Sql projects.

@BenjaminAbt the "classic Sql projects" are able to assume the installation of SSDT, and so they solve this by referencing files that are installed along with SSDT. We can't make that same assumption for this project, so I don't think it's accurate to say this "already exists" for our purposes. But I'd also point out that even if we resolve this using a NuGet reference, NuGet would only perform the roundtrip once -- once you had the master.nupkg package, it wouldn't need to be downloaded each time.

[jeffrosenberg]

@jmezach I think that solution would be fine; the one reason I might prefer publishing a master.dacpac NuGet package would be to keep the syntax for database references consistent, rather than having a totally different attribute for references to the master database.

That's not a dealbreaker for me, though, so I'm fine with either solution, but ultimately I think the big issue here will be whether we're comfortable going ahead with this from a licensing perspective.

[BenjaminAbt]

How must the master database NuGet look like and/or be structured?

I put the master.dacpac as content into a NuGet pkg, put the pkg in local folder of the solution, references that folder to NuGet.config (to avoid publish to a NuGet server) and referred to it. Visual Studio recognizes the NuGet package but the project still throws the error that the system tables cannot be found.

I suspect that the structure of the package is not correct.

[jmezach]

@BenjaminAbt The NuGet package must have a tools folder and then inside the tools folder there should be the master.dacpac.

Also, how did you add the reference to your package to your project? Could you share that section of the .csproj?

[BenjaminAbt]

The nuspec file:

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
  <metadata>
    <id>MyProject.Database.MasterAzureSQLV12</id>
    <version>1.0.0</version>
    <title></title>
    <authors>MyProject</authors>
    <owners></owners>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>MSSQL Master Table Azure V12</description>
  </metadata>
  <files>
    <file src="tools\master.dacpac" target="tools\master.dacpac" />
  </files>
</package>

cspoj

<Project Sdk="MSBuild.Sdk.SqlProj/1.8.1">
    <!-- https://github.com/rr-wfm/MSBuild.Sdk.SqlProj -->

    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlServerVersion>SqlAzure</SqlServerVersion>
        <!-- For additional properties that can be set here,
        please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="MyProject.Database.MasterAzureSQLV12" Version="1.0.0" DatabaseVariableLiteralValue="master" />
    </ItemGroup>

    <ItemGroup>
        <Content Include="..\..\db\MyProject.Database.Mssql\dbo\**\*.sql" />
    </ItemGroup>

</Project>

Solution show everything should be recognized:

but errors like

Severity Code Description Project File Line Suppression State Error SQL71501 SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTableA].[rows], [dbo].[MyTableB].[rows] or [sys].[partitions].[rows]. MyProject.Database.Mssql.Build

still appear.

Same behavior on command line or in Visual Studio.

[jmezach]

That looks okay to me. @jeffrosenberg Is this also how you are doing it?

@BenjaminAbt Could you share the build output you're getting? It should tell you if the reference is correctly added.

[jeffrosenberg]

Yes, that looks like what I have. I agree with confirming that the reference has been added.

@BenjaminAbt one thing you may need to do -- it's unclear from the above whether you have done this or not -- is to add the master .nupkg file to your local NuGet repository with something like this:

dotnet nuget push /path/to/MyProject.Database.MasterAzureSQLV12.1.0.0.nupkg --source /path/to/nuget/repo

[BenjaminAbt]

The package is located to a local solution folder named "packages" . The local folder is added as source in the NuGet config

<?xml version="1.0" encoding="utf-8"?>
<configuration>

  <packageSources>
    <clear />
    <add key="Solution Store" value="./packages" />
    <add key="nuget.org" value="https://api.nuget.org/v3/index.json" />
  </packageSources>

</configuration>

this is the recommended way to deal with solution-only nuget dependencies you dont want to push anywhere. The package itself is also correctly found and resolved by NuGet. But obviously the content is not.

Build started... 1>------ Build started: Project: MyProject.Database.Mssql.Build, Configuration: Debug Any CPU ------ 1>You are using a preview version of .NET. See: https://aka.ms/dotnet-core-preview 1>Using package name MyProject.Database.Mssql.Build and version 1.0.0 1>Using SQL Server version SqlAzure 1>Adding C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Stored Procedures\Some.sql to the model .. many more adding lines... but only sql files 1>Adding C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Stored Procedures\Other.sql to the model 1 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(7,25): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] has an unresolved reference to object [sys].[partitions]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(8,25): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] has an unresolved reference to object [sys].[partitions]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(9,25): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] has an unresolved reference to object [sys].[partitions]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(7,92): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[index_id], [dbo].[MyTable2].[index_id] or [sys].[partitions].[index_id]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(8,90): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[index_id], [dbo].[MyTable2].[index_id] or [sys].[partitions].[index_id]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(9,92): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[index_id], [dbo].[MyTable2].[index_id] or [sys].[partitions].[index_id]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(7,46): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[OBJECT_ID], [dbo].[MyTable2].[OBJECT_ID] or [sys].[partitions].[OBJECT_ID]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(8,46): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[OBJECT_ID], [dbo].[MyTable2].[OBJECT_ID] or [sys].[partitions].[OBJECT_ID]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(9,46): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[OBJECT_ID], [dbo].[MyTable2].[OBJECT_ID] or [sys].[partitions].[OBJECT_ID]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(7,14): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[rows], [dbo].[MyTable2].[rows] or [sys].[partitions].[rows]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(8,14): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[rows], [dbo].[MyTable2].[rows] or [sys].[partitions].[rows]. 1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(9,14): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[rows], [dbo].[MyTable2].[rows] or [sys].[partitions].[rows]. 1>Found 12 error(s), skip building package 1>C:\Users\Ben.nuget\packages\msbuild.sdk.sqlproj\1.8.1\Sdk\Sdk.targets(193,5): error MSB3073: The command "dotnet "C:\Users\Ben.nuget\packages\msbuild.sdk.sqlproj\1.8.1\Sdk../tools/netcoreapp5.0/DacpacTool.dll" build -o "obj\Debug\netstandard2.0\MyProject.Database.Mssql.Build.dacpac" -n "MyProject.Database.Mssql.Build" -v "1.0.0" -sv SqlAzure -i "obj\Debug\netstandard2.0\MyProject.Database.Mssql.Build.InputFiles.txt" " exited with code 1. 1>Done building project "MyProjectDatabase.Mssql.Build.csproj" -- FAILED. ========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

[jeffrosenberg]

Yeah, looks like the package reference isn't being resolved properly. Here's what my master.nupkg looks like in my local NuGet repo, does yours look the same (other than names and version numbers)?

[jmezach]

Looking at the logs I think the .dacpac isn't being resolved properly. And I think I know why. Currently we're assuming that the .dacpac file has the same name as the package ID. So you could try renaming the .dacpac file to the name of the package and see if that helps.

[BenjaminAbt]

Yes, the way to rename to master works. Thank you!!

2>Adding reference to C:\Users\Ben.nuget\packages\master\1.0.0/tools/master.dacpac with external parts master

To name the dacpac to 'MyProject.Database.MasterAzureSQLV12' does not work.

[jmezach]

@BenjaminAbt That's great news, glad to hear that works for you.

I am curious though why renaming the .dacpac file doesn't work. If we are going to publish the master.dacpac as a separate NuGet package I would rather not need to name that package master ;). Would you be willing to share a binary log (obtained using dotnet build /bl) with us so that we can figure out why that scenario is not working? Do note that the binary log includes all of the environment variables on your machine.

[BenjaminAbt]

I created a demo project to show you my errors but in this case it works.

I will check what are the differences why to use another name like "master" does not work in my real project.

[BenjaminAbt]

It works today in my projec too. I guess it was a race condition of nuget caching... even if I think I have used restore with --force.

[jmezach]

@BenjaminAbt Thanks for the feedback, really appreciate it.

@jeffrosenberg @ErikEJ I guess we could ship an MSBuild.Sdk.SqlProj.MasterDatabase package with various major versions matching the SQL Server versions so that people can reference that if they want to. That way the experience is the same for users own projects and the master database, rather than having a different way of doing things for the master database as @jeffrosenberg mentioned (see https://github.com/rr-wfm/MSBuild.Sdk.SqlProj/issues/64#issuecomment-700030677).

I'm still not convinced though that is the right way to go about this. Conceptually I'm thinking that various SQL Server versions are a similar concept to the different versions of .NET which in the .NET world is modelled using target frameworks. Essentially the master database is a platform that exposes an API based on the version of SQL Server you're targeting. But you won't always need the master database in this case, so having a toggle to include/exclude it makes sense.

And of course we still need to decide whether we want to risk redistributing these master.dacpac files as part of this package (or another package for that matter).

[jeffrosenberg]

@jmezach on the first question, I can see your point, but I see it a little differently -- you always get access to the API provided by the master database, particularly its functions and stored procedures. It's really a question of whether you need a three-part reference to its tables that determines whether you need a reference to it, and I see that as being much more similar to referencing another user database. To be honest, I don't have a really strong opinion here, I just think that consistency in how to accomplish a three-part reference to an object makes the most sense.

The bigger question, which we keep circling around, is whether we can redistribute master.dacpac in the first place, and I'm not sure how to answer that one. Personally, while I would like to do it, my gut feeling is that it's a no-no unless we get the okay from someone at MS. But I don't really think it's my call.

[ErikEJ]

Given that master.dacpac is only installed with Visual Studio and Visual Studio Build Tools, I think it is a no-go.

[BenjaminAbt]

But this (dependency to VS / VSBT) is in itself a requirement that you have with classic SQL projects anyway. So at least from this point of view this would not be a deterioration of the requirement at all.

[brettrowberry]

But this (dependency to VS / VSBT) is in itself a requirement that you have with classic SQL projects anyway. So at least from this point of view this would not be a deterioration of the requirement at all.

To me, this project is about making it possible to not use Windows and Visual Studio (unlike classic SQL projects) - the only requirement is the .NET SDK.

[brettrowberry]

For a given version of SQL Server, does every instance of SQL Server have the exact same master database?

[jeffrosenberg]

For a given version of SQL Server, does every instance of SQL Server have the exact same master database?

Yes, the master, model, and msdb databases are shipped along with the SQL Server binaries and created at installation. Of course, users can modify these databases on a particular server after installation, but the base database definition is always the same.

[jmezach]

@ErikEJ @jeffrosenberg I emailed @dzsquared about whether we can redistribute the .dacpacs. So far no response though.