How To Manually Deploy Sitecore Xdb Collection Databases

Every once in a while, there comes a time when your automation fails, or it doesn’t do exactly what you need it to do, and you’re faced with a choice. Do you alter the automation scripts or do you just do it by hand? Recently I faced a situation when installing Sitecore 9.1 where SIF just didn’t create the collection databases. Rather than spend time debugging SIF, I decided to just create the collection databases by hand. Turns out, it wasn’t that hard.

My first instinct was to pull the .dacpac files from the Sitecore provided WDPs (web deploy packages) and deploy them to SQL myself. Job’s done right? But how do we create that ShardMapManagerDb database? There’s no package for that.

If you watch what SIF does during an installation, you may have noticed it runs something called the SqlShardingDeploymentTool.exe. Turns out, that tool does most of the work for us, and we just have to invoke it with the right parameters. Credit to this excellent post from Kelly Rusk that explains what these parameters do. Here are the steps to do it yourself.

  1. Find the \App_Data\collectiondeployment folder in your XConnect instance. You can also extract this from the XConnect WDP package.
  2. Create a collection_user in SQL. In my case SIF did this for me (despite not creating the collection DBs), but you will need to make one if doing this completely from scratch.
  3. Prepare your command in notepad. You’ll need to pass the tool a lot of parameters, and feel free to reference the post linked earlier to understand them. Here’s one I used for reference:
    Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation "create" /connectionstring "Data Source=.\SQLEXPRESS;User Id=collection_user;Password=Asdf1234;Integrated Security=false;Timeout=30" /dbedition "Standard" /shardMapManagerDatabaseName "SC911.Xdb.Collection.Database.Sql.ShardMapManagerDb" /shardMapNames "ContactIdShardMap,DeviceProfileIdShardMap,ContactIdentifiersIndexShardMap" /shardnumber "2" /shardnameprefix "SC911.Xdb.Collection.Database.Sql.Shard" /shardnamesuffix "" /dacpac "Sitecore.Xdb.Collection.Database.Sql.dacpac" /log "tool.log"
  4. Run a command prompt as administrator and navigate to the directory with the tool. Execute your prepared command. If all goes well you’ll see a tool.log in that folder that ends with *** Everything is done. Sitecore xDB Collection SQL Sharding Deployment Tool is about to end its work. *** Make sure you see your databases in SQL.
  5. In the tool directory, you’ll see a few .sql scripts. We need to execute 2 of them to grant the proper permissions to the collection_user we created. In all cases, substitue the $(Username) variables with the name of your collection user.
  6. CreateShardApplicationDatabaseUser.sql should be run against each of the shard databases. CreateShardManagerApplicationDatabaseUser.sql should be run against the shard map manager database. The other SQL scripts in this directory are unnecessary, they execute a subset of the commands in these two scripts.

If all went well you should be able to see XConnect connecting to and logging data to the newly created collection DBs. Happy Sitecoreing!

Self-signed Certificates with Solr Cloud and Sitecore 9.1

If you’ve been using Sitecore 9 or 9.1, you know that all the services the platform depends upon must communicate using trusted, secure connections. This includes Solr. Sitecore’s instructions and the scripts provided by SIF helpfully walk you through setting up a secure Solr installation as part of standing up your 9.1 environment. Jeremy Davis has also created a wonderful powershell script to install Solr with a self signed certificate that I’ve used quite a bit.

But, what if you need to set up Solr Cloud? Sitecore has instructions for that too. These instructions largely send you off to the Solr documentation. My colleague Adam Lamarre has a post walking through the process of setting up Solr cloud on 9.1 as well, albeit on a single server.

If you follow the steps outlined in these posts, you’ll have Solr Cloud up and running on separate machines. But, when it comes time to create a collection you’re going to run into a problem. You may see something like this in the response:

"failure":{"solr3:8983_solr":"org.apache.solr.client.solrj.SolrServerException:IOException occured when talking to server at: https://solr3:8983/solr","solr2:8983_solr":"org.apache.solr.client.solrj.SolrServerException:IOException occured when talking to server at: https://solr2:8983/solr"},

We created our certificates, the nodes are up and running, Zookeeper is aware of them all, but the Solr nodes can’t seem to communicate with each other. So what gives? If we dig into the logs on any of the Solr servers, we get a little more insight into the problem.

2019-03-05 19:04:49.869 ERROR (OverseerThreadFactory-8-thread-1-processing-n:solr2:8983_solr) [   ] o.a.s.c.OverseerCollectionMessageHandler Error from shard: https://solr3:8983/solr
org.apache.solr.client.solrj.SolrServerException: IOException occured when talking to server at: https://solr3:8983/solr
at org.apache.solr.client.solrj.impl.HttpSolrClient.executeMethod(
Caused by: PKIX path building failed: unable to find valid certification path to requested target

What we’re seeing here is the Solr servers don’t trust each other. We need to fix that.

There’s a couple of things we need to do here. First, we have to get the self-signed certificates we created for each Solr node and install them on the other servers. On each Solr server, do the following,

  1. Open certlm.msc
  2. Expand Trusted Root Certification Authority -> Certificates and find your Solr certificate you created.
  3. Open the certificate and make a note of the thumbprint. We’ll need this later.
  4. Export the certificate. Make sure you check Include Extended Properties and Mark this Certificate as Exportable in the dialogue.
  5. When prompted for a password, use the same one you configured when installing Solr (the default is “secret”)

Once you have the certificates, you’ll need to install them on the other nodes. On each Solr server,

  1. Open certlm.msc
  2. Expand Trusted Root Certification Authority -> Certificates
  3. Import the certificates from the other 2 Solr nodes.

Try to hit the other Solr nodes from the browser on each server. For example, try accessing https://solr2:8983/solr/ from the Solr1 server. (You may need host file entries). If your certificates are installed properly, the browser will not warn you about an untrusted site.

There is one more thing we need to do. The Windows servers might trust our Solr nodes now, but the Solr applications themselves do not. If you take a look at the Solr installation steps, you’ll notice we’re creating a keystore file that holds the certificate for that Solr node (typically named . These keystore files needs to be updated to include the certificates from ALL of the Solr nodes, not just the one for the instance on that server.

We can easily do this with Powershell. We can do it with Java’s keytool.exe too, but we’re Sitecore people and probably more comfortable in Powershell! Remember those thumbprints we noted earlier? We’ll need them now.

Here’s the script, assuming your password is “secret”. Run this on any of the Solr nodes.

$password = ConvertTo-SecureString -String "secret" -Force -AsPlainText
Get-ChildItem -Path `
    cert:\LocalMachine\Root\<THUMBPRINT_FOR_SOLR3>; `
    | Export-PfxCertificate -FilePath D:\solr-ssl.keystore.pfx -Password $password

Take this generated solr-ssl.keystore.pfx file and copy it over the keystore file in each of the Solr nodes, then stop each node and restart them.

If we did everything correctly, when we try to create our collections again, it should go smoothly and you’ll be up and running with Solr Cloud and Sitecore 9.1.

For more information on the architecture of a Solr Cloud cluster and how to set one up for Sitecore, you can refer to my old blog series on the topic. It was written for 7.2, but the architecture principles haven’t changed. (including the need for a load balancer!)

Setup Sitecore 9.1 CM on HTTPS

Sitecore 9.1 comes bundled with a lot of new stuff, including a much improved Sitecore Install Framework. The process of setting up a local environment has been greatly streamlined, now you only need to run a script for installing prerequisites and then the XP0 installer itself. This gives you an instance of XConnect, Sitecore Identity server, both setup on HTTPS with trusted certificates. It will also install the Sitecore XP application for you and set it up on HTTP.

If you need to secure the Sitecore XP application as well, you could create a certificate in IIS and assign it to the HTTPS binding. However, this certificate won’t be trusted, and you’ll have the additional problem that Sitecore Identity Server won’t trust the site either, meaning you can’t log in over HTTPS. We’ll have to do a couple things to get past this.

You may see this error when trying to log into Sitecore 9.1 over HTTPS.

Create a new Trusted Certificate for IIS

First, we have to make a trusted certificate and assign it to our CM site. The certificate generated by IIS won’t cut it, because it uses the SHA1 encryption algorithm which is not accepted by modern browsers. Instead, let’s do what SIF does and make a certificate using Powershell. Alter the DnsName parameter to match the hostname of the Sitecore XP instance you’re working on.

New-SelfSignedCertificate `
    -DnsName "" `
    -CertStoreLocation "cert:\LocalMachine\My" `
    -FriendlyName "" `
    -TextExtension "{text}" `
    -KeyUsage DigitalSignature,KeyEncipherment,DataEncipherment `
    -Provider "Microsoft RSA SChannel Cryptographic Provider" `
    -HashAlgorithm "SHA256"

Next we’ll need to export that certificate out of the Personal store and into the Trusted Root Certification Authority. Again, this is exactly what SIF does for XConnect and Identity Server. We can script this too, but it’s easy to do using the UI.

  • In Windows, run certlm.msc. This is the Local Computer Certificate manager.
  • Expand Personal -> Certificates and find the certificate.
  • Right click, and chose Tasks -> Export. Accept the defaults and save the certificate somewhere.
  • Expand Trusted Root Certification Authority, right click Certificates and choose All Tasks -> Import
  • Choose your certificate file you just created, and again accept the defaults.

If you did everything correctly, you should see this certificate available in IIS when you try to set up the HTTPS binding.

Setting up the HTTPS binding in IIS with our new certificate.

Try hitting your site in your browser, and you should not be prompted that the certificate is not trusted.

Chrome trusts our local Sitecore XP instance now.

However, we still can’t log into Sitecore. The login page says our client is unauthorized. What gives?

Configure Identity Server to Allow a New Client

We have to do one more thing, and that’s tell the Sitecore Identity Server about this new binding. To do this we need to edit a config in the identity server application. Open up \Config\production\Sitecore.IdentityServer.Host.xml in your identity server application folder. Look for the <Clients> block and add a line for our new secure XP binding.


Try logging to Sitecore again, and this time you should be successful.

What’s new in Sitecore 9.1

Sitecore MouseSitecore 9.1 has just hit, and with it comes a lot of exciting new features. You’ll probably be hearing and reading a lot about the Big Things they’re announcing with this release, such as the general availability of Sitecore Javascript Services (JSS), automated personalization with Cortex, Sitecore’s acquisition of digital asset manager StyleLabs, and their partnership with Salesforce.

However, there are some great quality of life enhancements coming with this release as well, which may be of particular interest to developers. Here’s a few that were highlighted.


Anyone who’s worked with Sitecore for a while, especially as a developer, has noticed how long it takes to start up the application. This can be a huge drag on productivity when you have to wait and wait for application pool recycles, especially if you’re in a rapid development cycle. You lose momentum, you lose focus, and it’s just annoying. The team at Sitecore has heard these complaints and made some serious strides on this in 9.1.

Sitecore showed some benchmarks and 9.1 is boasting a startup-time that’s cut in half. That’s time from a cold start of a CM instance to loading the Launchpad. Not bad! They’ve also cut the number of .dlls the /bin folder in half, increased the load time of the Content Editor by a factor of 6, and shaved some load time of the Experience Editor as well.

3rd Party Integrations

Sitecore has historically lagged behind in updating their integrations with supporting software. This was highlighted last year with the exposure of a security flaw in their Telerik version. In 9.1, we’ll see support for the latest versions of Sitecore’ supporting software, including Telerik, Newtonsoft, Solr, and of course .NET Core.


The current Sitecore back-end has been essentially the same for many years, some CSS updates notwithstanding, and it’s lagging behind the competition. If you were at Symposium last year, it was mentioned during the closing keynote that Sitecore is working on an overhaul of their UI and authoring experience. This year they’ve announced the early-access availability of Horizon.

So, what is Horizon? Right now we’re not entirely sure. It’s meant to address the concerns of customers with the current Experience Editor. We know it’s an overhaul of the Experience Editor at least, but will it exist next to it, replace it outright, or complement it?

Sitecore is releasing an early access version of Horizon later this month and we’ll know a lot more. They want feedback, so as a developer you should download Horizon when it’s available, beat on it, and let them know what you think!

Native Indexing of Binary Content

Another small but welcome enhancement is the ability for the Content Search crawler to index PDF and MS Word files, out of the box. This was possible before with the installation of 3rd party tools, but Sitecore has heard their users and is wisely including this as a core feature.

That’s all for now. When Sitecore 9.1 hits, make sure to crack it open and put some of these changes through their paces. I certainly will be!

Modify Sitecore Install Framework Packages for Azure SQL

Unfrozen Caveman LawyerSitecore 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.

Continue reading “Modify Sitecore Install Framework Packages for Azure SQL”

Jabberwocky Updated for Sitecore 9

a jabberwockyVelir’s Jabberwocky framework has been updated for Sitecore 9.0, initial release. This update doesn’t add any new features beyond support for Sitecore 9.

For now, the package is marked prerelease, due in-part to the dependency on Glass.Mapper, which is still in prerelease for Sitecore 9 support.  We’ll be assessing the framework during our upcoming Sitecore 9 upgrades and projects, and we will correct any uncaught issues with the framework. A final release will be available in the coming months.

As always, your feedback is welcomed!

Connecting Sitecore PaaS to Azure Cosmos DB

Sitecore’s new PaaS offering in Azure is now available. When you’re creating an instance of Sitecore Experience Platform, you’re required to provide a MongoDB connection string for XDB. There are a few options in Azure for a Mongo service, but I decided to try to set it up with Microsoft’s Cosmos DB (formerly DocumentDB). Unfortunately, it didn’t work immediately, so I had to dig in a little bit to get my new PAAS Sitecore instance up and running. This post will walk through setting up Cosmos DB in Azure, attaching it to a new Sitecore PAAS instance, and deploying some custom code to our Sitecore instance to resolve the error connecting to Cosmos DB.

Setup Azure Cosmos DB

The first thing you want to do is set up Cosmos DB in Azure. Log into your portal, and select New Resource on the left. Select Database, then “Database as a Service for MongoDB”. You’ll need to provide a resource ID, as well as a Resource Group and select a Location. Fill out the fields and click create. After a few moments your new Cosmos DB instance will be available.

If you didn’t click Pin to Dashboard before creating, you can find it in the Resouces list. Click on the new database and open up the resouce viewer. You’ll see some general information in the Overview tab. On the left, find and click Connection String under Settings. Here you’ll see the connection strings, port number, username and password you’ll need to connect Sitecore to CosmosDB.

Notice at the bottom of this page the disclaimer, “Azure Cosmos DB has strict security requirements and standards. Azure Cosmos DB accounts require authentication and secure communication via SSL.” This is a problem for Sitecore out of the box, and where we’ll need to do some customization to support secure connections to Mongo.

Setup Sitecore PaaS

Next we’ll set up Sitecore PAAS. This is quite easy with the latest release of Sitecore 8.2, update 3. If you click the New Resource button and search for Sitecore, you’ll see two offerings. Sitecore Experience Platform 8.2, and Sitecore Web Experience Manager 8.2. Since we’re setting up Mongo, that means we need Sitecore XP, so choose that. You’ll need to configure a few things. For the SQL, a username and password. For Sitecore, you’ll need to provide the admin password and your license file. Make a note of these.

Under Sitecore XP Settings, you’ll need to provide connection strings to MongoDb. These will be available in the resource view for the CosmosDB instance we set up, if you didn’t make a note of them previously. You’ll need to edit that connection string to add the XDB table name that Sitecore expects. For example, for the analytics connection string,


It will take some time for your new Sitecore environment to be provisioned. Once it’s ready, open up the resource viewer. In the Essentials view, you’ll see the url of your new instance. Go ahead and open that up, and you’ll see the familiar Sitecore welcome page. You can even log into Sitecore. In Azure, open up Application Insights and view the Log Stream (you may need to turn on Application Logs in the Diagnostic Logging tab first). You’re probably seeing errors related to MongoDb, in particular an error about the transport stream.
“Unable to connect to a member of the replica set matching the read preference Primary: Authentication failed because the remote party has closed the transport stream.”

This is because Cosmos DB requires an SSL connection, and out of the box, Sitecore does not support that. So, we’ll need to deploy a fix for this. Fortunately, Sitecore provides us a pipeline to hook into to override the MongoDB Connection behavior. To correct this issue, we’ll need to enable secure connections to MongoDB.

Deploying a change to Sitecore PaaS

We’ll need to create a class to insert into the updateMongoDriverSettings pipeline. Our processor is going to explicitly set the connection mode to be secure and tell it to use TLS 1.2 in order to connect to Cosmos DB. Here’s the code:

using System.Security.Authentication;
using MongoDB.Driver;
using Sitecore.Analytics.Pipelines.UpdateMongoDriverSettings;

namespace Sitecore.SharedSource.CustomMongo
  public class CustomMongoDbClientProcessor : UpdateMongoDriverSettingsProcessor
    public override void UpdateSettings(UpdateMongoDriverSettingsArgs args)
    args.MongoSettings.SslSettings = new SslSettings();
    args.MongoSettings.SslSettings.EnabledSslProtocols = SslProtocols.Tls12;

And here’s the config file we need to insert the processor:

<?xml version="1.0" encoding="utf-8" ?>
        <processor type="Sitecore.SharedSource.CustomMongo.CustomMongoDbClientProcessor, Sitecore.SharedSource.CustomMongo" />

Finally we need to deploy this to our Azure app. Azure offers a lot of options for deployment, but for this example we’ll settle for FTP. You’ll need to set up credentials for the FTP connection, you can do that under Deployment credentials.


Once you’ve done that, take a look at the overview page, and you’ll see your FTP information.

Connect with your FTP client of choice, and upload our new DLL with our processor to the /bin folder and our new config to App_Config/Include/zzz.

With this processor in place, Sitecore should now be connected to Cosmos DB.

Why you should use Solr 6 with Sitecore

I’m recently set up Sitecore with Solr 6.2. Anyone who has used Sitecore and Solr already has probably been aggravated by one annoying bug/oversight in the Solr admin, something that’s finally been fixed.7dc962eabf1

How beautiful is that? We can finally see the full name of the core in the selector!

So far I haven’t found any compatibility issues with Sitecore 8.2 and Solr 6.2. Give it a try!

Publish Sitecore Media Items on Referenced Datasources

One of the great additions to Sitecore 8 is the ability to publish related items when executing a publish. Using this feature, you’ll be sure to publish out any necessary items that may be needed to render the page correctly, such as data sources, referenced taxonomy items, or images.

However, you may still have some gaps when using this feature. Consider common scenario where you have a new page, and you add a component to the page that uses an separate item as a data source. On that data source is a field for an image. When publishing the page, the newly created data source item goes out, but the media item linked to on that data source does not.

This is because of the way Sitecore processes referenced items. In essence, it only goes one level deep in the reference tree. So, items referenced by the item being published will be added to the queue, but items referenced by those referenced items will not.

Normally this is ok. If the publisher crawled references recursively, you’d probably wind up in an infinite publishing loop, or you’d at least wind up doing a large publish unintentionally. But it is common for data source items to reference new content, like media, so we need to include those in the publish too.

There’s a pipeline in Sitecore 8 we can use specifically for this purpose, the <getItemReferences> pipeline. Out of the box, it includes a step to AddItemLinkReferences. This step is the one responsible for adding our referenced data source item, so we can override this step to add logic to include media referenced by that data source.

Like all great Sitecore developers, we customize Sitecore by reflecting on their code and replacing it with our own logic. I opened up Sitecore.Publishing.Pipelines.GetItemReferences.AddItemLinkReferences, and added the following.

  foreach (Item obj in itemLinkArray.Select(link => link.GetTargetItem()).Where(relatedItem => relatedItem != null))
    // This will look at the item's links looking for media items.
  return list.Distinct(new ItemIdComparer());

Then we’ll add the GetLinkedMediaItems method,

protected virtual List<Item> GetLinkedMediaItems(Item item)
  List<Item> mediaList = new List<Item>();
  ItemLink[] itemLinkArray = item.Links.GetValidLinks()
    .Where(link => item.Database.Name.Equals(link.TargetDatabaseName, StringComparison.OrdinalIgnoreCase))
  foreach (ItemLink link in itemLinkArray)
      Item target = link.GetTargetItem();       
      if (target == null || !target.Paths.IsMediaItem) 
      // add parent media items or folders
      Item parent = target.Parent;
      while(parent != null && parent.ID != ItemIDs.MediaLibraryRoot)
        mediaList.Insert(0, parent);
        parent = parent.Parent;
    catch (Exception ex)
      Log.Error("Error publishing reference link related media items", ex, typeof(AddItemAndMediaLinkReferences));
  return mediaList;

We can include this new pipeline by replacing the old one we reflected on.

<configuration xmlns:patch="">
    <processor type="Sitecore.SharedSource.Pipelines.Publish.AddItemAndMediaLinkReferences, Sitecore.SharedSource"
               patch:instead="processor[@type='Sitecore.Publishing.Pipelines.GetItemReferences.AddItemLinkReferences, Sitecore.Kernel']"/>

With this in place, media items referenced on any linked item will be published. You can further refine the logic to just consider data sources, perhaps by checking the path or template to ensure it’s a data source, to cut down on unintentional publishes.