The SQL Sharding Deployment Tool

Version: 10.1

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 executed from an Azure environment.

Note

The Sitecore Installation Framework (SIF) and the Sitecore Azure Toolkit (SAT) create two shards by default. 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.

Location

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-Prem

Sitecore 10.0.0 rev. 001250 (OnPrem)_xp0xconnect.scwdp.zip

\Content\Website\App_Data\collectiondeployment\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe

XP Single (Developer) - Cloud

Sitecore 10.0.0 rev. 001250 (Cloud)_xp0xconnect.scwdp.zip

XP Scaled - On-Prem

Sitecore 10.0.0 rev. 001564 (OnPrem)_xp1collection.scwdp.zip

XP Scaled - Cloud

Sitecore 10.0.0 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:

    RequestResponse
    .\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation create /connectionstring "user id=sa;password=12345;data source=./SQL2017;" /shardMapManagerDatabaseName "Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb" /shardnumber 2 /shardnameprefix "Sitecore.Xdb.Collection.Database.Sql" /shardnamesuffix "_" /dacpac ".\Sitecore.Xdb.Collection.Database.Sql.dacpac"

  • Drop the xDB Collection shard cluster (drop). For example:

    RequestResponse
    .\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation drop /connectionstring "user id=sa;password=12345;data source=.\SQL2017;" /shardMapManagerDatabaseName "Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb"

  • Add a shard to the shard cluster (addShard). For example:

    RequestResponse
    .\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation addShard /connectionstring "user id=sa;password=12345;data source=./SQL2017;" /shardMapManagerDatabaseName "Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb" /shardConnectionString "user id=sa;password=12345;data source=./SQL2017;" /shardnameprefix "Sitecore.Xdb.Collection.Database.Sql" /shardnamesuffix "_" /dacpac ".\Sitecore.Xdb.Collection.Database.Sql.dacpac"
    Note

    This command is only available with a cloud environment, and not on-prem.

  • Remove a shard from the shard cluster (deleteShard). For example:

    RequestResponse
    .\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation deleteShard /connectionstring "user id=sa;password=12345;data source=.\SQL2017;" /shardMapManagerDatabaseName "Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb" /shardConnectionString "user id=sa;password=12345;data source=.\SQL2017;" /shardDatabaseName "Sitecore.Xdb.Collection.Database.Sql2_"

  • Print shard map key ranges (printMapping). For example:

    RequestResponse
    .\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation printMapping /connectionstring "user id=sa;password=12345;data source=.\SQL2017;" /shardMapManagerDatabaseName "Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb"

  • Register schema for ShardMapManager (registerSchema). For example:

    RequestResponse
    .\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation registerSchema /connectionstring "user
id=sa;password=12345;data source=.\SQL2017;" /shardMapManagerDatabaseName "Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb"
Important

Using the SQL Sharding Deployment Tool to add or remove shards does not automatically split and merge existing data. You use the SQL Sharding Deployment Tool as part of performing the split and merge operation.

Registering a schema is one of the prerequisites for the split and merge operation. If schemas are not registered, the split and merge operation cannot migrate the data.

Parameters

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

Parameter name

Required

Description

Values / examples

/operation

Yes

Specifies which operation to perform.

  • create

  • drop

  • addShard

  • deleteShard

  • printMapping

  • registerSchema

/connectionstring

Yes

SQL Server connection string used to create the database.

"user id=sa;password=SamplePassword;data source=.\SQL2016"

/dbedition

No

Azure SQL DTU-based service tiers . This value is only used if you are deploying to Azure SQL.

  • Basic

  • Standard

  • Premium

  • GeneralPurpose

  • BusinessCritical

/shardMapManagerDatabaseName

Yes

The name of the shard map manager database.

Example: Sample_ShardMapManagerDb

/shardnumber

Yes

The number of shards to create.

5

/shardnameprefix

No

Format is {Prefix}{Number}{Suffix}.

Example: Sample.Shard

/shardnamesuffix

No

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

/log

No

Path to log file. If no log file is specified, output is written to console.

/shardConnectionString

Yes

Used by the addShard and /deleteShard operations.

/shardDatabaseName

Yes

Used by the /deleteShard operation.

/force

No

Optional parameter, used by the /addShard operation. Forces the shard to be added without a schema comparison.

- true

- false

/elasticpool

No

The name of the registered elastic pool to deploy cluster databases within. The argument is SQL Azure specific.

Use with create and addShard.

Do you have some feedback for us?

If you have suggestions for improving this article,