1

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)
2

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

  • I followed the above instructions but still facing the same issue, I am setting up sitecore on my local machine – Simba Jun 11 at 17:44
  • I am also facing the same issue.Any help would be appreciated. – sindu Jun 11 at 19:13
  • This was hard to replicate for myself. I worked through the issue with a coworker recently and have updated the answer with the solution that worked. In that case, the issue was the use of SQL 2016 SP2. Only SP1 is supported officially by Sitecore. – jrap Aug 5 at 12:56
1

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.

0

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: enter image description here

  • This was the best solution so far, I have a pc with x64 with the DataTierApp already installed, but after installing x86 Data-Tier App the Sitecore installation continued without problems – gonzdn Sep 3 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.