I have Ola Hallengren's excellent Maintenance Solution scripts incorporated in to a DBA tools database project. This is maintained using SSDT 2015 with a target platform of SQL Server 2012. The project includes references to the built-in system databases master & msdb. The project builds successfully.
Ola has recently updated his scripts and they now include references to new SQL Server 2017 DMV's, so incorporating the updated scripts into my database project generated unresolved reference errors for the new DMV's.
SQL71502: Procedure: [dbo].[DatabaseBackup] has an unresolved reference to object [sys].[dm_os_host_info]
SQL71502: Procedure: [dbo].[DatabaseBackup] has an unresolved reference to object [sys].[dm_db_log_stats]
Although I don't have SQL Server 2017 installed anywhere, I tried updating the target platform to SQL Server 2017 and then deleting and recreating the master database reference (i.e. to update the master.dacpac to the SQL Server 2017 version), but that did not remove the build errors.
The file path for the referenced dacpac is:
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\140\SqlSchemas\master.dacpac
When I check the dacpac, the new DMV's [sys].[dm_os_host_info] and [sys].[dm_db_log_stats] aren't in there.
I reached out on Slack yesterday and Peter Schott referred to me to an excellent blog post he'd written regarding how to update the master.dacpac (link). The theory being to grab the missing DMV's and add them to the master.dacpac myself. I'd have to spin up a test machine and install SQL Server 2017 to do this, but it's feasible as a workaround.
However, it feels like something I shouldn't have to do. After all, these are system DMV's missing from the master.dacpac, rather than adding my own code to the master database.
So, I guess my questions are:
While I don't have a resolution to the original issue (system DMV's missing from the master.dacpac), I do have answers to the two questions I originally posed.
Yes, this is a general issue with the master.dacpac that comes with SSDT. And no, there does not appear to be any way to get an updated master.dacpac without hacking it yourself using the workaround detailed previously.
I've now raised an item on UserVoice, requesting a method for updating the system database DACPACs in SSDT. Please consider voting for it if you have a chance. Thanks.