The SQL Sharding Deployment Tool

Abstract

An overview of the SQL Sharding Deployment Tool operations and parameters.

The SQL Sharding Deployment Tool is an executable that deploys and manages the xDB Collection database and can be used on its own if you want greater control over parameters such as the number of shards to deploy. The tool must be run from a local machine. It cannot be uploaded and run from an Azure environment.

Note

The Sitecore Installation Framework (SIF) and the Sitecore Azure Toolkit (SAT) create two shards by default. This is the recommended number of shards that handles the majority of cases; for better performance, we usually recommend increasing the Azure tier, while increasing the number of shards is a good solution only if the database is huge. To change the number of shards you can either re-deploy the xDB Collection database. For SIF, you can also change the JSON files used during the installation process.

The tool is bundled with the xConnect Collection service and is available in the following web deploy packages (WDP):

Topology name	Example WDP Name	Tool location
XP Single (Developer) - On Premise	`Sitecore 9.X.X rev. 001250 (OnPrem)_xp0xconnect.scwdp.zip`	`\Content\Website\App_Data\collectiondeployment\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe`
XP Single (Developer) - Cloud	`Sitecore 9.X.X rev. 001250 (Cloud)_xp0xconnect.scwdp.zip`
XP Scaled - On Premise	`Sitecore 9.X.X rev. 001564 (OnPrem)_xp1collection.scwdp.zip`
XP Scaled - Cloud	`Sitecore 9.X.X rev. 001564 (Cloud)_xp1collection.scwdp.zip`

Operations

You can use the SQL Sharding Deployment Tool to:

Create the xDB Collection shard cluster (create). For example:

.\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation create /connectionstring 'user id=sa;password=12345;data source=.' /dbedition Standard /shardMapManagerDatabaseName TestDatabase /shardMapNames ContactIdShardMap,DeviceProfileIdShardMap,ContactIdentifiersIndexShardMap /shardnumber 2 /shardnameprefix Shard_ /shardnamesuffix _Sample /dacpac Sitecore.Xdb.Collection.Database.Sql.dacpac

Drop the xDB Collections hard cluster (drop). For example:

.\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation drop /connectionstring "user id=sa;password=12345;data source=." /dbedition Standard /shardMapManagerDatabaseName "TestDatabase" /shardMapNames "ContactIdShardMap,DeviceProfileIdShardMap,ContactIdentifiersIndexShardMap"

Parameters

The following table describes the parameters used by the SQL Sharding Deployment Tool. Different operations accept a specific set of parameters in a specific order.

Parameter name	Mandatory	Description	Values / Examples
`/operation`	Yes	Specifies whether to create or drop the database.	`create` `drop`
`/connectionstring`	Yes	SQL Server credentials used to create the database.	`"user id=sa;password=SamplePassword;data source=.\SQL2016"`
`/dbedition`	Yes	Azure SQL DTU-based service tiers. This value is only used if you are deploying to Azure SQL.	Basic Standard Premium
`/shardMapManagerDatabaseName`	Yes	The name of the shard map manager database.	Example: `Sample_ShardMapManagerDb`
`/shardMapNames`	Yes	Comma-separated list of shard maps that contain shard meta data and mappings. Do not modify this value.	Value when using the `create` operation:`ContactIdShardMap,DeviceProfileIdShardMap,ContactIdentifiersIndexShardMap`. Value when using the `drop` operation: `ContactIdShardMap,DeviceProfileIdShardMap`
`/shardnumber`	Yes	The number of shards to create. Count starts at 0.	5
`/shardnameprefix`	Yes	Format is {Prefix}{Number}{Suffix}.	Example: `Sample.Shard`
`/shardnamesuffix`	Yes	Format is {Prefix}{Number}{Suffix}. Suffix can be left blank.	Example: `_ABC`
`/dacpac`	Yes	Path to DACPAC file. By default, DACPAC files are in the same folder as the tool.	`Sitecore.Xdb.Collection.Database.Sql.dacpac` (on-premise) `Sitecore.Xdb.Collection.Database.Sql.Azure.dacpac` (Azure)
`/log`	No	Path to log file. If no log file is specified, output is written to console.