June 16, 2020 / Sitecore

What is (and How to Use) the Sitecore 9 Shard Map Manager Tool

Contents

1 What is the Shard Map Manager Tool
2 How to Use the Shard Map Manager Tool – Manual Execution
3 How to Use the Shard Map Manager – SIF Execution
4 Post Shard Map Manager Tool Execution Steps

What is the Shard Map Manager Tool

The Sitecore 9 SQL Shard Map Manager sharding deployment tool is designed to create your initial sharded environment that houses raw xConnect data. It’s not intended for rebalancing or reconfiguration of an environment with data. You would need to perform manual steps to reconfigure your environment.

The SQL sharding deployment tool can be found in the (OnPrem)_xp1collection.scwdp.zip package, in the \Content\Website\App_data\collectiondeployment\ folder. It is a command line interface tool that can be used either for deploying a sharded environment or for removing it.

There are two execution methods that I have found. The first is manual execution. The second is via SIF (including a small execution for deploying to Azure SQL).

How to Use the Shard Map Manager Tool – Manual Execution

Manual execution of the Shard Map Manager Tool is achieved via a command prompt. You will need to open a Command Prompt in Administrative Mode and ensure you are in the directory where the Shard Map Manager Tool exists:

Example: C:/Sitecore/xConnectCollection/Content/Website/App_data/collectiondeployment

Execution depends of various parameters. Details of these parameters are as follows:

Parameters:

Switch	Description
/operation	Can be “create” or “drop”, the type of operation performed by tool: create or delete shards
/connectionstring	Connection string to the server for shards to be created on. Should contain name of the server and credentials. Note: In Azure SQL, you need to include the Port number (ex. mysqlserver.database.windows.net,1433)
/dbedition	The edition to use for Shards and Shard Map Manager Database if the server is an Azure SQL DB server. If the server is a regular SQL Server then this is ignored. Note: While you can choose Basic, Standard, or Premium… Standard is the only that worked without fail in my testing
/shardMapManagerDatabaseName	The database name for the Shard Map Manager database, which contains the shard maps
/shardMapNames	The names for the Shard Maps which contain metadata for all the shards and the mappings to those shards, separated by comma Note: It is recommended to leave these as “ContactIdShardMap, DeviceProfileIdShardMap, ContactIdentifiersIndexShardMap”
/shardnumber	The number of the shards to create
/shardnameprefix	The shard name prefix Note: The Shard name pattern is “{shardNamePrefix}{shardNumber}{shardNameSuffix}”
/shardnamesuffix	The shard name suffix Note: The Shard name pattern is “{shardNamePrefix}{shardNumber}{shardNameSuffix}”
/dacpac	The path to the dacpac file
/log	Optional parameter, the name of the log file. Console will be used if log file name was not specified

Here are examples of operations can be performed, noting that a logged output will display success or errors:

/operation "create" /connectionstring "Data Source=.\SQLSERVER2016;User Id=myuserid;Password=12345abc;Integrated Security=false;Timeout=30" /dbedition "Standard" /shardMapManagerDatabaseName "Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb" /shardMapNames "ContactIdShardMap,DeviceProfileIdShardMap,ContactIdentifiersIndexShardMap" /shardnumber "3" /shardnameprefix "Sitecore.Xdb.Collection.Database.Sql.Shard" /shardnamesuffix "" /dacpac "C:\Temp\Sitecore.Xdb.Collection.Database.Sql.dacpac" /log "tool.log"

/operation "drop" /connectionstring "Data Source=.\SQLSERVER2016;User Id=myuserid;Password=12345abc?;Integrated Security=false;Timeout=30" /dbedition "Basic" /shardMapManagerDatabaseName "Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb" /shardMapNames "ContactIdShardMap,DeviceProfileIdShardMap" /log "tool.log"

After deploying environment as in the first example above, following databases will be created:

Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb
Sitecore.Xdb.Collection.Database.Sql.Shard0
Sitecore.Xdb.Collection.Database.Sql.Shard1
Sitecore.Xdb.Collection.Database.Sql.Shard2?

Azure SQL Shard Map Manager Deployment

Deploying to Azure SQL from Sitecore VMs can be achieved via the following:

.\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation "create" /connectionstring "Data Source=mysqlserver.database.windows.net,1433;User Id=my-admin;Password=12345678;Integrated Security=false;Timeout=120" /dbedition "Standard" /shardMapManagerDatabaseName "XP1UAT_Xdb.Collection.ShardMapManager" /shardMapNames "ContactIdShardMap,DeviceProfileIdShardMap,ContactIdentifiersIndexShardMap" /shardnumber "2" /shardnameprefix "XP1UAT_Xdb.Collection.Shard" /shardnamesuffix "Db" /dacpac "S:\Sitecore\xConnectCollection\App_data\collectiondeployment\Sitecore.Xdb.Collection.Database.Sql.Azure.dacpac" /log "tool.log"

Deploying to Azure SQL from Sitecore WebApps can be achieved via the following:

.\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe /operation "create" /connectionstring "Data Source=mysqlserver.database.windows.net,1433;User Id=my-admin;Password=12345678;Integrated Security=false;Timeout=120" /dbedition "Standard" /shardMapManagerDatabaseName "XP1UAT_Xdb.Collection.ShardMapManager" /shardMapNames "ContactIdShardMap,DeviceProfileIdShardMap,ContactIdentifiersIndexShardMap" /shardnumber "2" /shardnameprefix "XP1UAT_Xdb.Collection.Shard" /shardnamesuffix "Db" /dacpac "D:\home\site\wwwroot\App_Data\collectiondeployment\Sitecore.Xdb.Collection.Database.Sql.Azure.dacpac" /log "tool.log"

It is important to note:

Declare the 1433 port number after your Azure SQL address
While the /dbedition switch should allow you to select any level (ex. Basic, Standard, Premium), I found failures using Basic and Premium so use Standard to deploy then resize your databases as appropriate

Shard Map Manager and Shard Database Permissions

Post deployment, you need to add the correct permissions to the Shard Map Manager and Shard databases. This entails a “collectionuser” which resides outside of the database contained user security paradigm used for all other databases as this user needs to communicate across the Shards and Shard Map Manager.

If IaaS with Azure SQL:

Run on the Master DB

CREATE LOGIN collectionuser WITH PASSWORD = 'mypassword'

Run on the Shard Map Manager

CREATE USER collectionuser FROM LOGIN collectionuser GRANT SELECT ON SCHEMA :: __ShardManagement TO [collectionuser] GRANT EXECUTE ON SCHEMA :: __ShardManagement TO [collectionuser]

Run on each Shard

CREATE USER collectionuser FROM LOGIN collectionuser EXEC [xdb_collection].[GrantLeastPrivilege] @UserName = 'collectionuser'

If PaaS/Web Apps with Azure SQL:

Run on the Shard Map Manager

GRANT SELECT ON SCHEMA :: __ShardManagement TO [xcsmmuser] GRANT EXECUTE ON SCHEMA :: __ShardManagement TO [xcsmmuser]

Run on each Shard

EXEC [xdb_collection].[GrantLeastPrivilege] @UserName = 'xcsmmuser'

How to Use the Shard Map Manager – SIF Execution

The Sitecore 9 SIF actually calls the Shard Map Manager tool during its deployment process for an On Premises installation, passing along the parameters declared in the xConnectCollection JSON file.

You can also deploy shards to Azure SQL if you change the dacpac file name declared in the xConnectCollection JSON file.

Change this line in the SIF XP1 Collection JSON File:

"Sharding.DacPac.Path":     "[joinpath(variable('Sharding.Root.Path'), 'Sitecore.Xdb.Collection.Database.Sql.dacpac')]",

To this:

"Sharding.DacPac.Path":     "[joinpath(variable('Sharding.Root.Path'), 'Sitecore.Xdb.Collection.Database.Sql.Azure.dacpac')]",

Post Shard Map Manager Tool Execution Steps

Post execution of either the manual or SIF deployment, there are a few steps to take to ensure that everything is wired up and operating as expected:

Rebuild the xDB Index via these instructions: https://doc.sitecore.net/developers/xp/xconnect/xconnect-search-indexer/rebuild-index/rebuild-solr-index.html?highlight=rebuild#rebuild-the-core
Rebuild the Solr indexes
Rebuild the link databases
Deploy marketing definitions
Rebuild the Reporting Database: /sitecore/admin/RebuildReportingDB.aspx (Note, this requires a secondary reporting database)
Generate some visits (ensuring to close your browser for session end) and check your Experience Analytics for results.

NOTE: it may take a while for Sitecore to compile and process the raw data but you can check the interactions table in the shards to confirm the raw data is present (it will be in one of the shards, selected by the Shard Map Manager) and check the visits table in the Reporting database to see if your visits have processed. If you can see visits in the Reporting database it should appear in the Experience Analytics GUI assuming your Sitecore 9 environment is properly configured, including SSL certificates.