How To Install a WDP in a Sitecore 9.1 On-Premises Instance?

Let’s face it, if you are using Sitecore 9 you will end up dealing with custom Web Deploy Packages (WDP) since this is the new standard when it comes to installing packages on Azure Sitecore instances.

If you want to know more about this format and how to create one of those packages, I wrote a really extensive blog post on this subject which could be a nice entry point for you.

Why Even Bother?

But let’s move directly to today’s issue. Long history short, the WDPs you create using the method I described above will work smoothly on a Sitecore 9.1 Azure PaaS environment, but won’t work that smoothly if you try to install it locally in your on-premises instance.

Pro tip: The WDP wasn’t conceived to be installed locally, but still being able to do so it’s really important and even lifesaver when you are developing something new and need to test your module/package over and over again multiple times per day. One knows how #not fast installing a WDP package in Azure using ARM templates is.

The Issue

I was trying to install my beautiful WDP using the following msdeploy command-line:

msdeploy -verb:sync -source:package="D:\coveoticore.scwdp.zip" -dest:auto -enableRule:DoNotDeleteRule -setParam:"Application Path"="coveoticore" -setParam:"Core Admin Connection String"="Data Source=.\HSANTOS2016;Initial Catalog=coveoticore_Core;User ID=sa;Password=mypassword" -setParam:"Master Admin Connection String"="Data Source=.\HSANTOS2016;Initial Catalog=coveoticore_Master;User ID=sa;Password=mypassword" -verbose

That’s the exact same command I use on Sitecore 9.0 and older versions without any issue. This is why I was so confused the first time I faced it.

But basically, I had the two following error messages in my prompt:

Error: .Net SqlClient Data Provider: Msg 5061, Level 16, State 1, Line 5 ALTER DATABASE failed because a lock could not be placed on database 'coveoticore_Core'. Try again later.
Error: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET CONTAINMENT = NONE
            WITH ROLLBACK IMMEDIATE;
    END


Error: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.
Error: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET CONTAINMENT = NONE
            WITH ROLLBACK IMMEDIATE;
    END

As you can see, there was something wrong with my Core database. My first reflex was to think “Ok, I did so many tests on this instance that I clearly broke it during the process. Let’s just spawn a brand new fresh instance, fix this error and move on”.

What was my surprise when it didn’t work?

But not only that, I actually realized that I had another error which could be linked to the first one:

Error Code: ERROR_EXECUTING_METHOD
More Information: Could not deploy package.
Warning SQL72023: The database containment option has been changed to None.  This may result in deployment failure if the state of the database is not compliant with this containment level.
Error SQL72014: .Net SqlClient Data Provider: Msg 5061, Level 16, State 1, Line 5 ALTER DATABASE failed because a lock could not be placed on database 'coveoticore_Core'. Try again later.
Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET CONTAINMENT = NONE
            WITH ROLLBACK IMMEDIATE;
    END


Error SQL72014: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.
Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET CONTAINMENT = NONE
            WITH ROLLBACK IMMEDIATE;
    END

Wow wow, hold on… The database containment option has been changed to None. That’s some really helpful tip. My brand new Core database had something on it that was causing issues when trying to change the database containment option to none.

It took a few hours of investigations until I finally understand what the hell was going on.

The Solution

The end of my nightmare happened when I found this official documentation from Sitecore:

Use DisableDacPacOptions to create tables or users when you are deploying. You must disable SqlDatabaseOptions for the module WDP package if you want to deploy them to an on-premise instance. If you do not disable SqlDatabaseOptions, msDeploy will try to convert the contained database into an uncontained database and the attempts to apply these options while deploying the module package produces the wrong result.

A little bit more investigation and everything started making sense again.

Basically, if the target database already contains any user (which is always true to a Sitecore 9.1 but not true to older versions) you can’t change its containment option. 

You can conclude by looking at my second error message that the msdeploy command always try to change this value to None.

The only way to bypass this is by adding DisableDacPacOptions on the command-line that creates our WDP to completely disable any DacPac option. You will end up with something like this:

ConvertTo-SCModuleWebDeployPackage -Path "yourPackagePath.zip" -Destination "wdpOutputDirectoryPath" -force -verbose -DisableDacPacOptions "*"

Conclusion

Ouch, that was a very tricky issue. But at least you guys won’t have to figure that out again by yourselves now that you already know what to do. Hope you guys enjoyed the content of this blog post and make sure you follow me on twitter (hsantos_x) to be up to date with the new ones!