Sitecore xDB - Troubleshooting xDB Index Rebuilds on Azure

In my previous post, I shared some important tips to help ensure that if you are faced with an xDB index rebuild, you can get it done successfully and as quickly as possible.

I mentioned a lot of things in the post, but now, I want to mention common reasons where and why things can go wrong, and highlight the most critical items that impact the rebuild speed and stability.

Causes of Need To Rebuild xDB Index

Your xDB relies on your shard database's SQL Server change tracking feature in order to ensure that it stays in sync. This basically determines how long changes are stored in SQL. As mentioned in Sitecore's docs, the Retention Period setting is set to 5 days for each collection shard. 

So, why would 5-day old data not be indexed in time?

• The Search Indexer is shut down for too long
• Live indexing is stuck for too long
• Live indexing falls too far behind

Causes of Indexing Being Stuck or Falling Behind, and Rebuild Failures

High Resource Utilization: Collection Shards 

99% of the time, this is due to high resource utilization on your shard databases. Basically, if you see your shard databases hitting above 80% DTUs, you will run into this problem.

High Resource Utilization: Azure Search or Solr

If you have a lot of data, you need to scale your Azure Search Service or Solr instance.  Sharding is the answer, and I will touch in this further down.

What to check?

If you are on Azure, make sure your xConnect Search Indexer WebJob is running.

Most importantly, check your xConnect Search Indexer logs for SQL timeouts. 

On Azure, the Webjob logs are found in this location: D:\local\Temp\jobs\continuous\IndexWorker\{randomjobname}\App_data\Logs"

Key Ingredients For Rebuild Indexing Speed and Stability

SQL Collection Shards

Database Health 

Maintaining the database indexes and statistics is critically important. As I mentioned in my previous post:  "Optimize, optimize, optimize your shard databases!!!" 

If you are preparing for a rebuild, make sure that you run the AzureSQLMaintenance Stored Procedure on all of your shard databases.

Database Size

The amount of data and the number of collection shards is directly related to resource utilization and rebuild speed and stability. 

Unfortunately, there is no supported way to "reshard" your databases after the fact. We are hoping this will be a feature that is added to a future Sitecore release.

xDB Search Index

Similarly to the collection shards, the amount of data and the number of shards is directly related to resource utilization on both Azure Search and Solr. 

Specifically on Solr, you will see high JVM heap utilization.

If your rebuilds are slowing down or failing, or even if search performance on your xDB index is deteriorating, it's most likely due to the amount of data in your index, the number of shards and distribution amongst nodes that you have set up.  

Search index sharding strategies can be pretty complex, and I might touch on in these in a later post.

Reduce Your Indexer Batch Size

Another item that I mentioned in my previous post. If you drop this down from 1000 to 500 and you are still having trouble, reduce it even further. 

I have dropped the batch size to 250 on large databases to reduce the chance of timeouts (default is 30 seconds) when the indexer is reading contacts and interactions from the collection shards.

Read More

Sitecore xDB Resharding Tool - Unlock xDB Storage and Performance Limitations by Increasing Collection Shards Without Data Loss

Background

There is currently no way for you to increase the number of xDB collection database shards for an existing deployment, without starting from scratch and losing all your data.

The release of my colleague Vitaly Taleyko's Sitecore xDB Resharding Tool solves this problem for all of us, as it provides us with a migration path from an old shard collection to a new one.

https://github.com/pblrok/Sitecore.XDB.ReshardingTool

The inability to increase shards after deployment is a major problem for enterprise customers using the platform, who may not be aware of how quickly the collection databases will grow over time.

If you are a Sitecore veteran, you have experienced this rapid collection growth in MongoDB. As soon as the platform is "turned on", it starts collecting interactions and events for both anonymous and identified contacts.

Putting a Sitecore environment into Production, means opening up the flood gates for a massive amount of data that you don't have much control over.

xDB Search Index

On the xDB index side of the house, Sitecore has filtered the interactions and contact data in the xDB index to identified contacts only (by default). This is good for simple customers who aren't doing much with the platform.

However, if you have millions of contacts, you will have the same index problems as you may have faced when dealing with anonymous contact data in previous versions mentioned in this blog post from a while ago. There is a solve for this, that I may touch on in a later post.

The 2 Shard SQL Problem

Out of the box, if you install the Sitecore platform using default scripts and SIF or if Sitecore Managed Cloud has set up your environment in Azure, you will have 2 SQL shard collection databases.

The value proposition of Sitecore xDB is the ability to store any events tied to an interaction from any channel in xDB. They have provided a robust set of APIs to allow this.

The problem is that storing hundreds of gigabytes or even terabytes of data requires a very carefully planned strategy, or else, you will fail. It is just a matter of when.

I always have the following scene from Evan Almighty in my head when I talk about this problem:

The bottom line - if you have an enterprise deployment, and are using xDB, the out of the box 2 shard collection database configuration is not enough!

New Deployments

If you are new to the Sitecore platform, you can fix this by using the Shard Map Manager Tool to increase the number of shards. This great post by Kelly Rusk explains how: http://thebitsthatbyte.com/what-is-and-how-to-use-the-sitecore-9-shard-map-manager-tool

Existing / Live Deployments

Bad news if you have an existing deployment.

You will hit bottlenecks as you store more contact, interaction and event data. With limited CPU, storage capacity and memory, database performance will start to suffer and query performance and routine maintenance will slow down.

When it comes to adding resources to support database operations, vertical scaling (aka scaling up which is very easy to do on Azure) has its own set of limits and eventually reaches a point of diminishing returns.

The Negative Ripple Effect on xDB

I have seen cases where due to the massive amount of data stored in the xDB collection shards over time, xConnect Search will fail to keep the xDB index in sync, and xConnect search will stop working.

After this happens, the only option is to rebuild your xDB index, but because of the poor xDB collection database performance, it will take days, if not weeks if you are lucky.

Or, it will simply keep failing.

How Increasing Shards Helps

Adding additional collection shards to xDB means additional SQL compute capacity to serve incoming queries in the distributed configuration, and thus faster query response times and index builds.

Additional shards will increase total cluster storage capacity, speed up processing, and offer higher availability at a much lower cost than vertical scaling.

How the xDB Resharding Tool Helps

As I started working with Vitaly to architect this tool, our first idea was to use the Data Exchange Framework that is used to power the xDB Migration Tool. We had used a customized version of this tool when we migrated from our Sitecore 8.2 deployment to our current 9.1.

We decided to pivot, because we wanted a lightweight tool that could run on any Windows-based machine, and would run directly against SQL and as a result, be much more efficient!

The Beauty of the Tool Part 1: Migrating Your Data

This tool allows you to reshard your Sitecore xDB Collection Databases without any data loss.

What does this mean exactly?!

This tool allows you to migrate your current SQL xDB analytics collection database shards to a new set of xDB analytics collection database shards without losing any of your data.

So, for example if you have 2 shard databases, and want to move up to 4 shard databases, this tool will allow you to migrate over.

For this example, you would set up 4 new shards using SIF (as shown in Vitaly's GitHub readme doc), or use the Shard Map Manager Tool, and then point the tool at your old shards and the new shards and voila! Your data will get migrated over!

The Beauty of the Tool Part 2: Resume Mode

Another fantastic feature that Vitaly added to this tool is "resume mode". If there is a glitch in the migration process, or you need to stop it manually for some reason and resume later, it will remember where it left off and pick the migration right back up!

Battle-Tested and Ready For Download!

This tool has been tested, and I can say that it works, and works well!

You can check the full source code out on Vitaly's GitHub: https://github.com/pblrok/Sitecore.XDB.ReshardingTool

You can download your copy today using this link: https://github.com/pblrok/Sitecore.XDB.ReshardingTool/raw/master/ToolReleases/win-x64.zip

Read More

Sitecore xDB Cloud Edition: Using the REST API

As I mentioned in a previous post, one of the nice things about using xDB Cloud Edition is that once your licensing is in place, getting up and running is very easy.

It's important to note that you don't have direct access to the various collections. So, you can't connect using a tool like Robomongo or MongoVUE to get information about your instance. In the past, getting consumption information about your cloud instance would require you opening up a ticket with support.

All this has changed with the introduction of the REST API for the xDB Cloud service. To find more information about what the API has to offer, I recommend that you read through the REST API reference for the xDB Cloud service.

In this post, I will show you how to use the API with one of my favorite browser plugins, Postman, so that you can get useful information and manage various processes in your Cloud Instance.

Nexus Authentication Token

The first order of business is to use the SSO Encode Sitecore License endpoint (https://gateway-sso-scs.cloud.sitecore.net/api/License/Encode) to obtain a Nexus Authentication token.

In order to do this, you need to generate a POST request to the endpoint with your Sitecore license file in the body of the request.

After performing the post, you will see your Nexus token in the response:

Check xDB consumption

Once you have your Nexus token, hitting the other endpoints is a walk in the park.  All you need to do is pass over the token value in a custom header called "X-ScS-Nexus-Auth" when making the request.

One of the endpoints that you will be accessing regularly is the consumption one. This gives you very useful information such as instance size, number of contacts and interactions.

For this endpoint, you need to pass over the following parameters along with the token:

• licenseId – your Sitecore license ID (1 below)
• deploymentId – the unique identification of the deployment (2 below)
• year – the consumption year (3 below)
• month – the consumption month (3 below)

4 below is my generated Nexus token called "X-ScS-Nexus-Auth" that I have added to the HTTP header.

So for example, to get consumption information for October 2015, my GET request looks like this:

You will see that there is small bug in my particular instance, where it is returning "0GB" for size. At the time of writing this post, the Cloud Team was actively working to get this resolved.

If you want to use this data to create a fancy report, what you can do is convert the JSON data into CSV format using one of the free conversion sites like http://konklone.io/json/.

Using this site, I was able to get the data into Excel and create some cool looking graphs in a few clicks:

Other xDB Endpoints

Other endpoints that are available to you include:

Get Firewall settings
https://gateway-xdb-scs.cloud.sitecore.net/api/xdb/firewallsettings/{licenseId}/{deploymentId}

Get history processing status

https://gateway-xdb-scs.cloud.sitecore.net/api/xdb/historyProcessing/{licenseId}/{deploymentId}

Get xDB collection verification dataset

https://gateway-xdb-scs.cloud.sitecore.net/api/xdb/collection/{licenseId}/{deploymentId}

Get xDB set status

https://gateway-xdb-scs.cloud.sitecore.net/api/xdb/historyProcessing/{licenseId}/{deploymentId}

List xDB sets

https://gateway-xdb-scs.cloud.sitecore.net/api/xdb/{licenseId}

Trigger history processing

https://gateway-xdb-scs.cloud.sitecore.net/api/xdb/historyProcessing/{licenseId}/{deploymentId}

The Trigger history processing endpoint above is another one to note. This gives you the ability to trigger a rebuild of your cloud reporting database. Note that the HTTP method is a PUT for this.

Final Thoughts

With this API in place that gives us a good level of control of the xDB Cloud, we can't help but get even more excited about the additional self service APIs and the xConnect API, that will be released with 8.2 later this year.

Read More

Sitecore xDB Cloud Edition: What you need to know

I have been very fortunate to be involved in almost all of the cutting edge Sitecore implementations at Arke, and have had the privilege to work with several awesome members of the Sitecore team.

In one of my recent support projects, I got some great exposure to Sitecore's xDB Cloud offering. I wanted to share what I learnt with the community in hopes of providing a better understanding of what it takes to perform a successful implementation, some pitfalls, and how things change once you are in the cloud.

Getting Started

The "out of the box" components need to be updated in order to use xDB Cloud Edition. The following steps are from the client setup guide. I added some additional information and made some adjustments to make it easier to read.

Updating Cloud xDB Components

Before updating the Cloud xDB components, you need to perform following actions:

If you are using Sitecore 7.5:

1. Open Website\App_Config\Include\ folder
2. Remove or disable Sitecore.Cloud.Xdb.config file

If you are using Sitecore 8.0:

1. Open Website\App_Config\Include\XdbCloud folder
2. Remove or disable the following files:
• Sitecore.Cloud.Xdb.config
• Sitecore.ContentSearch.Cloud.Index.Analytics.config
• Sitecore.ContentSearch.Cloud.DefaultIndexConfiguration.config
• Sitecore.ContentSearch.Cloud.config

The following 2 packages, supplied by Sitecore's Cloud Team, need to be installed in your Sitecore instance:

1. Sitecore.Cloud.ContentSearch
2. Sitecore.Cloud.Xdb

Configuring the Sitecore Cloud xDB client

1. Open the folder of your Sitecore installation, and navigate to Website\App_Config\Include\XdbCloud
2. Open the Sitecore.Cloud.Xdb.config.disabled file and change the setting Sitecore.Cloud.Xdb.DeploymentId to the value that should identify your deployment. An example would be "Corp-V8X-Prod".
• You must specify a value for this setting before you run Sitecore for the first time after you configure Sitecore to use a cloud xDB.
3. Make sure that the your Sitecore.Cloud.Xdb.DeploymentType setting reflects the environment that you are configuring. Options are "Prod" and "NonProd".
4. It is important to note:
• Sitecore will not boot if this setting doesn't match what is retrieved from the xdb service.
• Running a production solution on a non-prod SLA will degrade your overall SLA.
• Running a non-production solution on a production data set will pollute the production data set.
5. Enable the following config files (enable means remove .disabled extension from the name): 
• Sitecore.Cloud.Xdb.config.disabled
• Sitecore.ContentSearch.Cloud.Index.Analytics.config.disabled
• Sitecore.ContentSearch.Cloud.DefaultIndexConfiguration.config.disabled

Upgrading from xDB Cloud Edition 7.5 to 8.0

1. If you have been running Sitecore Cloud xDB 7.5, there is an upgrade process that needs to take place to make your dataset work with Sitecore 8.0.
2. The reason for this is because there is an additional collection that is now present in 8.0
3. At the time of writing this post, Sitecore was still working on finalizing the script to upgrade the 7.5 dataset to work with 8.0.

Provisioning a new xDB Cloud Dataset

1. If you need to provision a new xDB set, you can do so by simply by changing the value present in Sitecore.Cloud.Xdb.DeploymentId that you will find in the Sitecore.Cloud.Xdb.config file.
2. You have a quota limit tied to your licensing, so if you see a "Quota limit has been reached" error in your logs, you will need to contact your Sitecore sales rep.
3. A common scenerio would involve setting up 2 datasets; one for staging and one for production.

Licensing

xDB Cloud Edition is tied to the your license ID. If you have recently made a change to your licensing, or if you are a new Sitecore customer, you will need to wait until you have received your permanent license in order to provision your cloud dataset.

Connection Strings

1. The various Mongo connection strings that you find in your ConnectionStrings.config file are not needed after you have successfully connected to your xDB Cloud Dataset.
2. When using the cloud, the connection strings are still actually there even though they don't appear in the configuration file. They are created dynamically InProc by the cloud discovery service. 
3. The same applies to the reporting and reporting.secondary connection strings. However you need to have those in place in order to avoid the various errors that occur when trying to rebuild the reporting database. To be clear, this doesn’t really matter anyway because there currently isn't a way to make this work when using xDB Cloud Edition.

Losing the xDB Cloud connection

1. If you lose the connection you don’t lose data. 
2. The data is stored on the file system and then pushed to the cloud once the connection is re-established
3. The data is stored in the $(dataFolder)/Submit Queue folder

xDB Cloud Dataset Access

One of the nice things about using xDB Cloud Edition, is that once your licensing is in place, getting up and running is very easy. All that you need to do is load 2 packages in your Sitecore instance, change 2 values in a configuration file, and enable the configuration. You also don't have to worry about some of the complex sharding and backup issues that will arise when dealing with an enterprise Mongo configuration.

The downside is that you don't have direct access to the various collections. So, you can't connect using a tool like Robomongo or MongoVUE. You also don’t have the ability to stand up new collections, if say you wanted to write some log entries or put some interesting CRM data in a new collection.

xDB Cloud Edition Reporting Databases

1. It's important to note that the reporting SQL databases are hosted in the cloud. This includes both the primary and secondary.
2. There is currently no way to successfully rebuild the reporting databases from the /sitecore/admin/rebuildreportingdb.aspx page.

Common Errors

Error

The connection string used by aggregation/historyTaskManager/ReportingStorage is missing when you attempt to rebuild reporting database from /sitecore/admin/rebuildreportingdb.aspx page

Resolution

The reporting.secondary connection string is not present. Although this secondary reporting database 

is not used by cloud xDB, it is necessary in order to get passed this error. 

Error

Analytics SPEAK pages show a series of errors

Resolution

All errors that I have seen in the various analytics pages are related to a xDB connection issue.

So if you are seeing some of the red error messages like this, make sure you check your logs for xDB connection errors.

Error

xDB Cloud Initialization failed

Sample:

 ERROR xDB Cloud - xDB Cloud initialization failed. Please contact cloud@sitecore.net and include this in the email:  
 **********************************************************************************  
   License Id: xxxxxxxxxxxxxxxxxxxx  
   Deployment Id: XXX-ProdV8-xDB  
   Issue id: 8bab88fa6e9c497b8d717eb62c39252c  
   Discovery Service Status Code: 403 Forbidden  
   Exception Details: Sitecore.Cloud.Xdb.Exceptions.RequestForbiddenException: Quota limit has been reached, or subscription wasn't found  
    at Sitecore.Cloud.Xdb.DiscoveryServiceClient.AssertStatusCodes(IRestResponse restResponse, String licenseId, String deploymentId)  
    at Sitecore.Cloud.Xdb.DiscoveryServiceClient.GetXdbSet(String licenseId, String deploymentId, String sitecoreVersion, DeploymentType deploymentType)  
    at Sitecore.Cloud.Xdb.DiscoveryServiceClient.GetXdbSet()  
    at Sitecore.Cloud.Xdb.UpdateXdbConnectionStrings.Process(PipelineArgs args)  
 **********************************************************************************  

Resolution

1. As the message indicates, you will need to contact Sitecore's cloud team at cloud@sitecore.net to get these connection errors resolved. You must submit a ticket through support. Cloud issues now go through the regular support channel,  rather than a separate (as it was in the past). 
2. The Issue id is key for them in identifying the cause of the error.
3. The team is extremely helpful and their turnaround to get your issues resolved is pretty quick.

Read More