Sitecore 9 is here, it’s in our lives, and we’re at the point where the projects we started at the beginning of the year are getting ready to roll out. That means we need to get our production environments ready. If you’re coming from the Sitecore 8.x and earlier world, this can be a challenge. There’s new databases, the xConnect service, security and certificate requirements, and of course our friend Solr is mandatory now. We have a new tool to help us get through all this, the Sitecore Install Framework (or SIF). It’s supposed to help us by automating our install steps, if you know how to use it.
Fortunately, Sitecore has really stepped up their documentation, especially with version 9. There’s a detailed guide on installing Sitecore 9, which covers a single instance (probably a local developer environment) and a scaled out production instance. However, when they say scaled out , they mean scaled out. There’s a script for every possible server role. In the real world, our environments don’t match what’s exactly in the documentation. For example, we often combine roles, or share hardware. We need to make some adjustments, and that’s when we start to go off the map.
Azure SQL and SIF
There are a number of different “gotchas” you can run into whenever you’re setting up a complex application like Sitecore. In our situation we needed to deploy Sitecore 9 to an environment that used Azure SQL as the datastore. It seemed simple enough, plug in the appropriate database server info into the SIF .json files and fire off SIF from our powershell command line. But, we ran into a problem when we got to the Web Deploy step:
Error Code: ERROR_SQL_EXECUTION_FAILURE … The command started with the following: "exec sp_configure 'contained database authenticati"
It looks like SIF is trying to execute an unknown command, having an error thrown back, and that blows up our entire install. So what is SIF trying to do that it can’t?
The error comes back to this command:
sp_configure 'contained database authentication', 1;
SIF is trying to create the users for the databases it is installing. Before doing this, it tries to set the database to contained mode. Turns out, this operation isn’t supported in Azure SQL (note the X next to Azure SQL in the documentation header). Fortunately for us, Azure SQL databases support contained mode by default, no need to specifically alter the database.
So the solution seems simple. Unzip that scwdp.zip file that’s giving you the issue, alter the sql scripts to remove the invalid command, zip it back up and feed your updated web deploy package to SIF.
That didn’t work
SIF didn’t like our edited web deploy package. Turns out, you can’t just unzip and edit a web deploy package and zip it back up again, MsDeploy will not accept it. You’ll get strange, seemingly arbitrary errors. What’s the correct way to do this, then?
Sitecore offers another set of powershell modules, the Sitecore Azure Toolkit. Included in these modules is the ability to create, edit, and transform web deploy packages in a number of ways. When downloading Sitecore Azure Toolkit, make sure you unblock the file before installing it to Powershell. Otherwise it won’t work!
We need to do two things to modify our web deploy package for SIF. First, we need to create a Sitecore Cargo Payload File, or SCCPL. This is an archive that contains the files and transformation data that will be applied to our WDP. Then, once that is prepared, we’ll use the Sitecore Azure Toolkit commands to apply that SCCPL to the WDP.
Creating the Sitecore Cargo Payload File
There’s a lot you can do with a SCCPL. In our case, we want to remove the existing SQL scripts that create the database users and replace them with new scripts that create the users without invoking that contained database command that Azure SQL doesn’t support. In this example we’ll modify the xConnect XP0 web deploy package.
If we refer to the documentation here, we need to create a file of IO Actions that will delete the existing user scripts, and we need to copy our new scripts to the root of our WDP.
Start by creating a folder that will hold the contents of our SCCPL. In that folder, create 2 more folders, CopyToRoot and IOActions. In the IOActions folder, create a text file, DeleteOldSqlScripts.ioxml. The file should contain this XML, which lists each .sql file we want to delete from the WDP.
<IOActions> <IOAction path="createuser_text_automation.sql" action="delete" /> <IOAction path="createuser_text_messaging.sql" action="delete" /> <IOAction path="createuser_text_processing.sql" action="delete" /> <IOAction path="createuser_text_referencedata.sql" action="delete" /> </IOActions>
In our CopyToRoot folder, we’re going to place the newly modified .sql script. If we copy these from the current WDP, we can remove the lines that run the problematic commands. For example, createuser_text_automation.sql will look like this:
CREATE USER [PlaceHolderForUser] WITH PASSWORD = 'PlaceHolderForPassword'; GO EXEC sp_addrolemember 'db_datareader', [PlaceHolderForUser]; EXEC sp_addrolemember 'db_datawriter', [PlaceHolderForUser]; GO GRANT EXECUTE TO [PlaceHolderForUser]; GO
Repeat this for the other createuser scripts. When you’re done, zip all these up these 2 folders, and change the extension of that .zip file to a .sccpl.zip file.
Updating your SCWDP package
Now that we have our Sitecore Cargo Payload file, we need to modify the xConnect web deploy package that SIF is failing to install. We can use another Powershell command from the Sitecore Azure Toolkit to do this.
Update-SCWebDeployPackage ` -CargoPayloadPath '.\path\to\xp0xconnect_transforms.sccpl.zip' ` -Path '.\path\to\Sitecore_xp0xconnect.scwdp' -Verbose
If executed correctly, you’ll be able to peek into the newly modified SCWDP file and see the .sql scripts have been replaced with your changes.
Now, if we execute Install-SitecoreConfiguration command, with our .json configuration pointing to the modified package, we will get past this error. If everything else is set up correctly, we’ll have our new instance of xConnect up and running.
Rinse and Repeat
Often times the realities of our business requirements or architecture constraints force us to deviate from the happy-path of official documentation. You have to get creative. The Sitecore Azure Toolkit is intended to prepare web deploy packages for installation to Sitecore/Azure environments, but you can use the tools provided to modify packages for SIF too, for an on-premises installation. By following the model above, you can customize the SIF packages distributed by Sitecore and tailor them to suit your needs.