MSDeploy (a.k.a. Web Deploy) is mainly known as a technology to deploy web applications, but it is much more than that. It is a platform that can be used to deploy many different application and application components. It accomplishes this by allowing custom providers to be written. MSDeploy ships with providers that cover a wide-range of deployment needs.
One common provider used is the dbDACFx provider. This is used to deploy data-tier applications (i.e. databases). The source can be an existing instance of a data-tier application or a .dacpac file, which is nothing more than a zip file that contains XML files (which is very common these days: MS Office, NuGet, etc.).
While attempting to deploy a dacpac file, I got the following error message
Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql120DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service.
Pretty cryptic. The only part that made sense was the “Sql120” piece, which is the version given to SQL Server 2014. That made sense as that was the target platform selected when the .dacpac file was created. The command line that was issued that caused the error message was something like
msdeploy -verb:sync -source:dbDACFx=”c:\myapp.dacpac” -dest:dbDACFx=”Server=x;Database=y;Trusted_Connection=True”
I installed MSDeploy through the Web Platform Installer, and I made sure to select the option that included the bundled SQL support.
I verified that I was able to install the .dacpac through Management Studio on my local machine, which tells me the database server was fine, so I knew the issue had to be with the server that was hosting MSDeploy.
After many Google Bing searches, I learned that DACFx was the short name for “SQL Server Data-Tier App Framework”. Looking on the server with MSDeploy, I discovered that the SQL 2012 (aka sql110) version was already installed. It all started coming together now, msdeploy was happy with the dbDACFx provider since it was installed, but when it went to locate the needed version of DACFx (specific by the .dacpac file), it could not find it, and it errored out.
A quick search in the Web Platform Installer resulted in finding “Microsoft SQL Server Data-Tier Application Framework (DACFx) (June 2014). Bingo! I installed it, reran my MSDeploy command, and I got a successful deployment of the .dacpac file!
For those who would rather not run the Web Platform Installer, you can go to the Microsoft site and download it directly (please search for the appropriate version you need).
If you want to see which versions are installed, go to Programs and Features on your machine and look for “Microsoft SQL Server 20xx Data-Tier App Framework”.
Thanks Brian, that helped me save some hours.