"Could not deploy package" error while installing Sitecore 9 update 2 setup on Remote server

Question

I am facing below issue while installing Sitecore 9 update 2 on my Azure VM server, which doesn’t have any Visual Studio or SQL Management Studio installed. Error states : Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider is not valid

Below is more details about issue.

[---------------------------------------------- InstallWDP : WebDeploy -----------------------------------------------]
[WebDeploy]:[Path] C:\Program Files\iis\Microsoft Web Deploy V3\msdeploy.exe
Info: Adding MsDeploy.Site (MsDeploy.Site).
Info: Adding database (user id=sa;data source=XYZ;initial catalog=Dev_Processing.Pools)
Info: Initializing deployment: Pending.
Info: Analyzing deployment plan: Pending.
Info: Updating database: Pending.
Info: Creating deployment plan: Pending.
Info: Verifying deployment plan: Pending.
Info: Deploying package to database: Pending.
Info: Creating deployment plan: Running.
Info: Initializing deployment: Running.
Info: Initializing deployment (Start)
Info: Initializing deployment: Faulted.
Info: Initializing deployment (Failed)
Info: Creating deployment plan: Faulted.
Info: Verifying deployment plan: Faulted.
Info: Deploying package to database: Faulted.
Error Code: ERROR_EXECUTING_METHOD
Error: Could not deploy package.
Error: Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvi
der is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database p
latform service.
Error: Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvi
der is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database p
latform service.
More Information: Could not deploy package.Error count: 1.

Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider 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.
Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider 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.
  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_EXECUTING_METHOD.
Install-SitecoreConfiguration : Command C:\Program Files\iis\Microsoft Web Deploy V3\msdeploy.exe returned a non-zero
exit code - (-1)
At E:\Scripts\SiteCore\InstallSitecore9.ps1:44 char:1
+ Install-SitecoreConfiguration @xconnectParams
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Install-SitecoreConfiguration

[TIME] 00:01:30
Transcript stopped, output file is E:\Scripts\SiteCore\xconnect-xp0.180920.log
Invoke-CommandTask : Command C:\Program Files\iis\Microsoft Web Deploy V3\msdeploy.exe returned a non-zero exit code -
(-1)
At C:\Program Files\WindowsPowerShell\Modules\SitecoreInstallFramework\1.2.1\Public\Tasks\Invoke-WebDeployTask.ps1:36
char:2
+     Invoke-CommandTask -Path $Path -Arguments $msdeployArgs -TaskName ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Invoke-CommandTask

PwoserShell line 44 :

#deploy xconnect instance 
$xconnectParams = @{ 
    Path = "$PSScriptRoot\xconnect-xp0.json" 
    Package = "$PSScriptRoot\Sitecore 9.0.2 rev. 180604 (OnPrem)_xp0xconnect.scwdp.zip" 
    LicenseFile = "$PSScriptRoot\license.xml" 
    Sitename = $XConnectCollectionService 
    XConnectCert = $certParams.CertificateName 
    SqlDbPrefix = $prefix 
    SqlServer = $SqlServer 
    SqlAdminUser = $SqlAdminUser 
    SqlAdminPassword = $SqlAdminPassword 
    SolrCorePrefix = $prefix 
    SolrURL = $SolrUrl 
} 
Install-SitecoreConfiguration @xconnectParams //(line: 44)

Answer 1

There are several solutions to this problem, but the end result is the same.

First, Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider indicates that Sitecore is relying on SQL 2016 tooling (130 = SQL 2016).

Second, the tooling does not need to be installed on the dedicated SQL instance but instead needs to be applied to where the Sitecore install is being executed (CM, CD, etc.). This is due to the need for the Sitecore installer to build deployment packages.

Sitecore Azure Toolkit 1.1 relies on Microsoft Web Deploy 3.6 to build deployment packages for Sitecore Experience Platform and modules. Microsoft Web Deploy dynamically loads SQL Server Data-Tier application framework, SQL ScriptDOM, and SQL CLRTypes components using automatic version detection.

(Note: the paragraph below is correct in some circumstances, but not all) Third, even though SQL 2016 corresponds to version 130 behind-the-scenes, the DAC files are installed in the 140 folder. Keep this in mind with the solutions listed below. The 140 is not a typo.

Solution 1

(Some people may find this the simplest)

Install a full version of SQL Management Studio 2016 SP1 on the Web Server (or locally if this is a local install). 2016 SP2 is not officially supported, though it can work in my experience. However, to rule out other issues, use SP1 to be certain.

Check SQL Version with PowerShell

Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3

With SQL installed, you will still need to execute the regedits outlined below.

Solution 2

Install the SQL Server Data-Tier Application Framework. The installer can be found here: https://www.microsoft.com/en-us/download/details.aspx?id=56508 (This is confirmed to work properly with SQL 2016 SP1)

After installation completes, perform the regedits outlined below.

Solution 3

If relying on Azure ARM templates for deployment, there are several choices for deploying a Virtual Machine image. In this particular case, to reduce the amount of manual work, it's simplest to deploy a VM with the following image references:

• Publisher: MicrosoftSQLServer
• Offer: SQL2016SP2-WS2016
• Sku: Standard
• Version: latest

This image will preinstall SQL to the web server. Granted it adds the extra overhead of the SQL install, but the service does not need to run, only the tooling is needed during the Sitecore install.

After deployment completes, perform the regedits outlined below.

Mandatory Regedits

Regardless of the solution chosen, perform the following operations on the web server that is installing the Sitecore databases.

First, determine your DAC version.

1. Navigate to either \%ProgramFiles(x86)%\Microsoft SQL Server\130 or \%ProgramFiles(x86)%\Microsoft SQL Server\140
2. Whichever directory contains \DAC\bin and contains Microsoft.SqlServer.Dac.dll, Microsoft.SqlServer.TransactSql.ScriptDom.dll and Microsoft.SqlServer.Types.dll is your DAC Version.

Substitute either 130 or 140 where <Dac Version> is listed below.

• Set the path to the Data-Tier application framework in Windows Registry:
  • Locate the installation folder of the MS SQL server version that you are using: \%ProgramFiles(x86)%\Microsoft SQL Server\<Dac Version>.
  • Find the Microsoft.SqlServer.Dac.dll file in one of the subfolders: \DAC\bin or \SDK\Assemblies.
  • Store the full path to this subfolder as a DacFxPath value (String type) under the registry key \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\IIS Extensions\MSDeploy\3.
• Set the path to the SQL ScriptDOM and CLRTypes components in Windows Registry:
  • Locate the installation folder of the MS SQL server version that you are using: \%ProgramFiles(x86)%\Microsoft SQL Server\<Dac Version>.
  • Find the Microsoft.SqlServer.TransactSql.ScriptDom.dll and Microsoft.SqlServer.Types.dll files in one of the subfolders: \DAC\bin or \SDK\Assemblies.
  • Store the full path to this subfolder as a DacFxDependenciesPath value (String type) under the registry key \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\IIS Extensions\MSDeploy\3.

Ref: https://kb.sitecore.net/articles/019579

Answer 2

I too followed the steps above provided by jrap, and it didn't work. It finally did when I used /Program Files/ instead of /Program Files (x86)/ and went looking there for the correct MS Server version. I modified my registry keys and it finally installed correctly.

Answer 3

Solution: Install both platforms (x64) and (x86) of "Microsoft SQL Server Data-Tier Application Framework (17.1 DacFx)" from https://www.microsoft.com/en-us/download/details.aspx?id=55255.

You should be able to see these two components in the windows installed programs list:

Answer 4

I actually got the same error and got this fixed by installing the SQL Server pre requisites.

• DacFramework_x64.msi (https://www.microsoft.com/en-us/download/details.aspx?id=53013)
• DacFX_X86.msi (https://www.microsoft.com/en-us/download/details.aspx?id=53013)
• vc_redist.x64.exe (https://support.microsoft.com/en-us/topic/the-latest-supported-visual-c-downloads-2647da03-1eea-4433-9aff-95f26a218cc0)
• msodbcsql_17.3.1.1_x64.msi (https://www.microsoft.com/en-us/download/details.aspx?id=56567)
• MsSqlCmdLnUtils.msi (https://www.microsoft.com/en-us/download/details.aspx?id=53591)
• SQLSysClrTypes_x64.msi (https://www.microsoft.com/en-us/download/details.aspx?id=100451)
• SQLSysClrTypes_x86.msi (https://www.microsoft.com/en-us/download/details.aspx?id=100451)