Resetting xConnect Shard databases

Question

We are looking for possibility to reset xConnect Shard databases in Sitecore 9 to "factory settings".

As a developer I would like to reset these databases when there is some fault in inserted data when we are developing something new. We had an issue recently where some values in facets where not stored correctly which resulted in Experience Profile not being loaded at all and ended up with error/exception.

Also our second thought is around integration environment were it would be useful from time to time to clean contact data, interactions and so on.

"Quick" solution for now was to recreate Sitecore instance from scratch. We would like to avoid this time consuming approach in the future.

As there is no possibility to delete data as per documentation programmatically is there any other option that you can think of?

Answer 1

I did this with SIF. Using the xConnect json template. I was able to remove all the tasks that build xConnect and just rebuild the shards.

1. You first need to delete the two shard databases and the shard manager database.
2. You need the Sitecore 9.0.1 rev. 171219 (OnPrem)_xp0xconnect.scwdp.zip file in the C:\sitecoreInstall\9.0.1 rev. 171219\ folder. OR what ever version you are using.
3. Then copy the xconnect-xp0-clean-shards.json into the C:\sitecoreInstall\9.0.1 rev. 171219\Configs\ folder
4. Change the SQL login detail and site $prefix.
5. Then run CleanShards.ps1. It will re-add the shard databases to your SQL server.

NOTE: If you are doing this on Azure SQL, just change this line in the JSON file.

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

to

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

CleanShards.ps1

Import-Module SitecoreInstallFramework
$prefix = "xp0"
$PSScriptRoot = "C:\sitecoreInstall\9.0.1 rev. 171219\Configs"
$XConnectCollectionService = "xp0.xconnect.local"
$sitecoreSiteName = "xp0.sc9.local"
$SolrUrl = "https://solr:8983/solr"
$SolrRoot = "C:\solr\solr-6.6.2"
$SolrService = "solr-6.6.2"
$SqlServer = "localhost\SQL2016"
$SqlAdminUser = "dev"
$SqlAdminPassword="dev123"

$certParams = @{
Path = "$PSScriptRoot\xconnect-createcert.json"
CertificateName = "$prefix.xconnect_client"
}

$xconnectParams = @{
Path = "$PSScriptRoot\xconnect-xp0-clean-shards.json"
Package = "C:\sitecoreInstall\9.0.1 rev. 171219\Sitecore 9.0.1 rev. 171219 (OnPrem)_xp0xconnect.scwdp.zip"
LicenseFile = "C:\sitecoreInstall\license.xml"
Sitename = $XConnectCollectionService
XConnectCert = $certParams.CertificateName
SqlDbPrefix = $prefix
SqlServer = $SqlServer
SqlAdminUser = $SqlAdminUser
SqlAdminPassword = $SqlAdminPassword
SolrCorePrefix = $prefix
SolrURL = $SolrUrl
}

Install-SitecoreConfiguration @xconnectParams -Verbose

xconnect-xp0-clean-shards.json

// -------------------------------------------------------------------------- //
//         Sitecore Install Framework - XConnect XP0 Configuration            //
//                                                                            //
//  Run this configuration to install a single instance of XConnect.          //
//                                                                            //
//  NOTE: Only single line comments are accepted in configurations.           //
// -------------------------------------------------------------------------- //
{
    "Parameters": {
        // Parameters are values that may be passed when Install-SitecoreConfiguration is called.
        // Parameters must declare a Type and may declare a DefaultValue and Description.
        // Parameters with no DefaultValue are required when Install-SitecoreConfiguration is called.
        "Package": {
            "Type": "string",
            "Description": "The path to the Web Deploy package to deploy."
        },
        "LicenseFile": {
            "Type": "string",
            "Description": "The path to the Sitecore license file."
        },
        "SiteName": {
            "Type": "string",
            "DefaultValue": "XConnect",
            "Description": "The name of the site to be deployed."
        },
        "XConnectCert": {
            "Type": "string",
            "Description": "The certificate to use for encryption. Provide the name or the thumbprint."
        },
        "SqlDbPrefix": {
            "Type": "string",
            "Description": "The prefix used for all Sql databases."
        },
        "SolrCorePrefix": {
            "Type": "string",
            "Description": "The prefix used for all Solr cores."
        },
        "SSLCert": {
            "Type": "string",
            "Description": "The certificate to use for HTTPS web bindings. Provide the name or the thumbprint. If not provided a certificate will be generated.",
            "DefaultValue": ""
        },
        "SqlAdminUser": {
            "Type": "string",
            "DefaultValue": "sa",
            "Description": "The Sql admin user account to use when installing databases."
        },
        "SqlAdminPassword": {
            "Type": "string",
            "DefaultValue": "12345",
            "Description": "The Sql admin password to use when installing databases."
        },
        "SolrUrl": {
            "Type": "string",
            "DefaultValue": "https://localhost:8983/solr",
            "Description": "The Solr instance to use."
        },
        "SqlCollectionUser": {
            "Type": "string",
            "DefaultValue": "collectionuser",
            "Description": "The Sql admin user account to use when installing databases."
        },
        "SqlCollectionPassword": {
            "Type": "string",
            "DefaultValue": "Test12345",
            "Description": "The Sql admin password to use when installing databases."
        },
        "SqlProcessingPoolsUser": {
            "Type": "string",
            "DefaultValue": "poolsuser",
            "Description": "The Sql user for the Processing Pools connection string in Sitecore."
        },
        "SqlProcessingPoolsPassword": {
            "Type": "string",
            "DefaultValue": "Test12345",
            "Description": "The Sql password for the Processing Pools connection string in Sitecore."
        },
        "SqlReferenceDataUser": {
            "Type": "string",
            "DefaultValue": "referencedatauser",
            "Description": "The Sql user for the Reference Data connection string in Sitecore."
        },
        "SqlReferenceDataPassword": {
            "Type": "string",
            "DefaultValue": "Test12345",
            "Description": "The Sql password for the Reference Data connection string in Sitecore."
        },
        "SqlMarketingAutomationUser": {
            "Type": "string",
            "DefaultValue": "marketingautomationuser",
            "Description": "The Sql user for the Marketing Automation connection string in Sitecore."
        },
        "SqlMarketingAutomationPassword": {
            "Type": "string",
            "DefaultValue": "Test12345",
            "Description": "The Sql password for the Marketing Automation connection string in Sitecore."
        },
        "SqlMessagingUser": {
            "Type": "string",
            "DefaultValue": "messaginguser",
            "Description": "The Sql user for the Messaging connection string in Sitecore."
        },
        "SqlMessagingPassword": {
            "Type": "string",
            "DefaultValue": "Test12345",
            "Description": "The Sql password for the Messaging connection string in Sitecore."
        },
        "SqlServer": {
            "Type": "string",
            "DefaultValue": ".\\SQLSERVER",
            "Description": "The Sql Server where databases will be installed."
        },
        "XConnectEnvironment": {
            "Type": "string",
            "DefaultValue": "Development",
            "Description": "The configuration environment for this instance."
        },
        "XConnectLogLevel": {
            "Type": "string",
            "DefaultValue": "Information",
            "Description": "The level of log information to output."
        }
    },
    "Variables": {
        // Variables are values calculated in a configuration.
        // They can reference Parameters, other Variables, and config functions.
        // The prefix for shards created by the tool
        "Sharding.Database.Prefix": "[concat(parameter('SqlDbPrefix'), '_Xdb.Collection.Shard')]",

        // The database names.
        "Sql.Database.ShardMapManager":     "[concat(variable('Sharding.Database.Prefix'), 'MapManager')]",
        "Sql.Database.Shard0":              "[concat(variable('Sharding.Database.Prefix'), '0')]",
        "Sql.Database.Shard1":              "[concat(variable('Sharding.Database.Prefix'), '1')]",
        "Sql.Database.Pools":               "[concat(parameter('SqlDbPrefix'), '_Processing.Pools')]",
        "Sql.Database.MarketingAutomation": "[concat(parameter('SqlDbPrefix'), '_MarketingAutomation')]",
        "Sql.Database.Messaging":           "[concat(parameter('SqlDbPrefix'), '_Messaging')]",
        "Sql.Database.Reference":           "[concat(parameter('SqlDbPrefix'), '_ReferenceData')]",

        // The security certificate details
        "Security.CertificateStore":                "Cert:\\Localmachine\\My",
        "Security.SSL.CertificateThumbprint":       "[GetCertificateThumbprint(parameter('SSLCert'), variable('Security.CertificateStore'))]",
        "Security.XConnect.CertificateThumbprint":  "[GetCertificateThumbprint(parameter('XConnectCert'), variable('Security.CertificateStore'))]",
        "Security.XConnect.CertificatePath":        "[joinpath(variable('Security.CertificateStore'), variable('Security.XConnect.CertificateThumbprint'))]",

        // The sites full path on disk
        "Site.PhysicalPath": "[joinpath(environment('SystemDrive'), 'inetpub', 'wwwroot', parameter('SiteName'))]",
        "Site.DataFolder":  "[joinpath(variable('Site.PhysicalPath'), 'App_Data')]",

        // The path to the index worker windows service
        "Services.IndexWorker.InstallPath": "[joinpath(variable('Site.DataFolder'), 'jobs','continuous','IndexWorker')]",
        "Services.IndexWorker.Name":        "[concat(parameter('SiteName'), '-IndexWorker')]",

        // The path to the automation engine windows service
        "Services.MarketingAutomationEngine.InstallPath":   "[joinpath(variable('Site.DataFolder'), 'jobs','continuous','AutomationEngine')]",
        "Services.MarketingAutomationEngine.Name":          "[concat(parameter('SiteName'), '-MarketingAutomationService')]",

        // The marketing automation endpoint.
        "Endpoint.MarketingAutomation": "[concat('https://', parameter('SiteName'))]",

        // The reference data endpoint.
        "Endpoint.ReferenceData": "[concat(variable('Endpoint.MarketingAutomation'), '/refdata')]",

        // The sharding tool details.
        "Sharding.Root.Path":   "[joinpath(variable('Site.DataFolder'), 'collectiondeployment')]",
        "Sharding.Tool.Path":   "[joinpath(variable('Sharding.Root.Path'), 'Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe')]",
        "Sharding.DacPac.Path": "[joinpath(variable('Sharding.Root.Path'), 'Sitecore.Xdb.Collection.Database.Sql.dacpac')]",
        "Sharding.Map.Names": [
            "ContactIdShardMap",
            "DeviceProfileIdShardMap",
            "ContactIdentifiersIndexShardMap"
        ],
        "Sharding.DB.Connection": "[sqlconnectionstring(parameter('SqlServer'), '', parameter('SqlAdminUser'), parameter('SqlAdminPassword'))]",
        "Sharding.Edition": "Basic",        
        "Sharding.SqlCmd.Path.CreateShardApplicationDatabaseServerLogin":   "[joinpath(variable('Sharding.Root.Path'), 'CreateShardApplicationDatabaseServerLogin.sql')]",
        "Sharding.SqlCmd.Path.CreateShardManagerApplicationDatabaseUser":   "[joinpath(variable('Sharding.Root.Path'), 'CreateShardManagerApplicationDatabaseUser.sql')]",
        "Sharding.SqlCmd.Path.CreateShardApplicationDatabaseUser":          "[joinpath(variable('Sharding.Root.Path'), 'CreateShardApplicationDatabaseUser.sql')]",

        // The solr configuration details.
        "Xdb.Name":         "[concat(parameter('SolrCorePrefix'), '_xdb')]",
        "Xdb.Rebuild.Name": "[concat(variable('Xdb.Name'), '_rebuild')]",
        "Solr.Url.Xdb":     "[concat(parameter('SolrUrl'), '/', variable('Xdb.Name'))]",

        // The content at this path is installed by the wdp
        "Solr.Config.Path": "[joinpath(variable('Site.DataFolder'), 'solrcommands', 'schema.json')]"
    },
    "Tasks": {
        // Tasks are separate units of work in a configuration.
        // Each task is an action that will be completed when Install-SitecoreConfiguration is called.
        // By default, tasks are applied in the order they are declared.
        // Tasks may reference Parameters, Variables, and config functions.
        "CleanShards": {
            // Drop existing shards.
            "Type": "Command",
            "Params": {
                "Path": "[variable('Sharding.Tool.Path')]",
                "Arguments": [
                    "/operation", "drop",
                    "/connectionstring", "[variable('Sharding.DB.Connection')]",
                    "/dbedition", "[variable('Sharding.Edition')]",
                    "/shardMapManagerDatabaseName", "[variable('Sql.Database.ShardMapManager')]",
                    "/shardMapNames", "[join(variable('Sharding.Map.Names'))]"
                ],
                "TaskName": "ShardingTool - Clean"
            }
        },
        "CreateShards": {
            // Create new shards.
            "Type": "Command",
            "Params": {
                "Path": "[variable('Sharding.Tool.Path')]",
                "Arguments": [
                    "/operation", "create",
                    "/connectionstring", "[variable('Sharding.DB.Connection')]",
                    "/dbedition", "[variable('Sharding.Edition')]",
                    "/shardMapManagerDatabaseName", "[variable('Sql.Database.ShardMapManager')]",
                    "/shardMapNames", "[join(variable('Sharding.Map.Names'))]",
                    "/shardnumber", 2,
                    "/shardnameprefix", "[variable('Sharding.Database.Prefix')]",
                    "/shardnamesuffix", "\"\"",
                    "/dacpac", "[variable('Sharding.DacPac.Path')]"
                ],
                "TaskName": "ShardingTool - Create"
            }
        },
        "CreateShardApplicationDatabaseServerLoginSqlCmd": {
            // Create Collection Shard Database Server Login
            "Type": "Command",
            "Params": {
                "Path": "sqlcmd",
                "Arguments": [
                    "-i",
                    "[variable('Sharding.SqlCmd.Path.CreateShardApplicationDatabaseServerLogin')]",
                    "-S",
                    "[parameter('SqlServer')]",
                    "-U",
                    "[parameter('SqlAdminUser')]",
                    "-P",
                    "[parameter('SqlAdminPassword')]",
                    "-v",
                    "[concat('UserName=',parameter('SqlCollectionUser'))]",
                    "-v",
                    "[concat('Password=',parameter('SqlCollectionPassword'))]"
                ]
            }
        },
        "CreateShardManagerApplicationDatabaseUserSqlCmd": {
            // Create Collection Shard Manager Database User
            "Type": "Command",
            "Params": {
                "Path": "sqlcmd",
                "Arguments": [
                    "-i",
                    "[variable('Sharding.SqlCmd.Path.CreateShardManagerApplicationDatabaseUser')]",
                    "-S",
                    "[parameter('SqlServer')]",
                    "-U",
                    "[parameter('SqlAdminUser')]",
                    "-P",
                    "[parameter('SqlAdminPassword')]",
                    "-v",
                    "[concat('UserName=',parameter('SqlCollectionUser'))]",
                    "-v",
                    "[concat('ShardMapManagerDatabaseName=',variable('Sql.Database.ShardMapManager'))]"
                ]
            }
        },
        "CreateShard0ApplicationDatabaseUserSqlCmd": {
            // Create Collection Shard 0 Database User
            "Type": "Command",
            "Params": {
                "Path": "sqlcmd",
                "Arguments": [
                    "-i",
                    "[variable('Sharding.SqlCmd.Path.CreateShardApplicationDatabaseUser')]",
                    "-S",
                    "[parameter('SqlServer')]",
                    "-U",
                    "[parameter('SqlAdminUser')]",
                    "-P",
                    "[parameter('SqlAdminPassword')]",
                    "-v",
                    "[concat('UserName=',parameter('SqlCollectionUser'))]",
                    "-v",
                    "[concat('ShardDatabaseName=',variable('Sql.Database.Shard0'))]"
                ]
            }
        },
        "CreateShard1ApplicationDatabaseUserSqlCmd": {
            // Create Collection Shard 1 Database User
            "Type": "Command",
            "Params": {
                "Path": "sqlcmd",
                "Arguments": [
                    "-i",
                    "[variable('Sharding.SqlCmd.Path.CreateShardApplicationDatabaseUser')]",
                    "-S",
                    "[parameter('SqlServer')]",
                    "-U",
                    "[parameter('SqlAdminUser')]",
                    "-P",
                    "[parameter('SqlAdminPassword')]",
                    "-v",
                    "[concat('UserName=',parameter('SqlCollectionUser'))]",
                    "-v",
                    "[concat('ShardDatabaseName=',variable('Sql.Database.Shard1'))]"
                ]
            }
        }
    }
}

Answer 2

You can do it deploying new shards and new shardmapmanager. Next exampple is to deploy new shards on Azure SQL. On premise you should use Sitecore.Xdb.Collection.Database.Sql for Premise installation.

1. You need to get collectiondeployment tool from Sitecore XP Collection role WDP (Sitecore 9.0.1 rev. 171219 (Cloud)_xp1collection.scwdp.zip)

2. Run next powershell script to deploy the new shards :

   param(
   [string]$dbServer = "y",
   [string]$dbUser = "sql",
   [string]$dbPass = "x",
   [string]$shardMapManagerDbName = "dbprefix.smm",
   [string]$shardDbNamePrefix = "dbprefix.shard",
   [string]$shardDbNameSuffix = [string]::Empty,
   [string]$totalShards = "2",
   [string]$shardMapNames = "ContactIdShardMap,DeviceProfileIdShardMap,ContactIdentifiersIndexShardMap")
   
   $elapsed = [System.Diagnostics.Stopwatch]::StartNew()
   try 
   {
   Write-Host "Database Server: $dbServer"
   
   $invocationDir = Split-Path -Parent $MyInvocation.MyCommand.Path
   $rootDir  = Join-Path -Path $invocationDir -ChildPath ..
   
   $exe = "C:\foldertosqlshardingdeploymenttool\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe"
   $dacpac = "C:\foldertoazuredacpac\Sitecore.Xdb.Collection.Database.Sql.Azure.dacpac"
   
   Write-Host "Xdb Collection Sql Deploy Tool: $exe"
   
   if (Test-Path -path $exe) {
   
   Write-Host "Create Xdb Collection Databases"
   & $exe "/operation" "create" "/connectionstring" """user id=$dbUser`;password=$dbPass`;data source=$dbServer`;""" "/dbedition" """Basic""" "/shardMapManagerDatabaseName" """$shardMapManagerDbName""" "/shardMapNames" """$shardMapNames""" "/shardnumber" """$totalShards"""  "/shardnameprefix" """$shardDbNamePrefix""" "/shardnamesuffix" """$shardDbNameSuffix""" "/dacpac" """$dacpac"""
   Write-Host "Create Xdb Collection Databases Complete"
   }
   else
   {
   Write-Error "Xdb Collection Sql Deploy Tool Not Found: $exe"
   }  
   
   } 
   finally 
   {
     Write-Host "Total time: $($elapsed.Elapsed.ToString())"
   }
   

   3. You need to create users for Collection DB

      In the collectiondeployment you will find scripts for On Premises.

   Create DB Server login (you need to execute query on master DB)

   CreateShardApplicationDatabaseServerLogin.sql

       :SETVAR UserName username
   
       :SETVAR Password yourpassword
   
       IF(SUSER_ID('$(UserName)') IS NULL)
   
       BEGIN
   
         CREATE LOGIN [$(UserName)] WITH PASSWORD = '$(Password)';
   
       END;
   

   Create SMM user CreateShardManagerApplicationDatabaseUser.sql

     :SETVAR UserName username
   
     BEGIN
   
     CREATE USER [$(UserName)] FOR LOGIN [$(UserName)];
   
     GRANT SELECT ON SCHEMA :: __ShardManagement TO [$(UserName)];
   
     GRANT EXECUTE ON SCHEMA :: __ShardManagement TO [$(UserName)];
   
     END;
   

   Create user for each Shard DB CreateShardApplicationDatabaseUser.sql

   :SETVAR UserName username
   
   BEGIN
   
   CREATE USER [$(UserName)] FOR LOGIN [$(UserName)];
   
   EXEC [xdb_collection].[GrantLeastPrivilege] @UserName = '$(UserName)';
   
   END;
   

Grant permissions to user for SMM GrantShardMapManagerDbPermissions.sql

     :SETVAR CollectionDbUserName username

      GRANT SELECT ON SCHEMA :: __ShardManagement TO [$(CollectionDbUserName)]

      GRANT EXECUTE ON SCHEMA :: __ShardManagement TO [$(CollectionDbUserName)]

Grant permissions for shard used (repeat for each shard) GrantShardDbPermissions.sql

      :SETVAR CollectionDbUserName username

      EXEC [xdb_collection].[GrantLeastPrivilege] @UserName = '$(CollectionDbUserName)'

All this scripts should be run on SQLCMD mode

You need to delete solr indexes after you reset xDB dbs.

Clean up Xdb Solr Cores

https://localhost:8983/solr/solrcorename_xdb/update?stream.body=<delete><query>*:*</query></delete>&commit=true


https://localhost:8990/solr/solrcorename_xdb_rebuild/update?stream.body=<delete><query>*:*</query></delete>&commit=true