Shards DB is not created during the installation of xconnect-xp1-collection

Question

During the xconnect-xp1-collection for sitecore 9.1.0, it fails to create the shards db, although the rest are created successfully. I get the following error during the installation:

Could not load type 'Microsoft.SqlServer.TransactSql.ScriptDom.CatalogCollationOption' from assembly 'Microsoft.SqlServer.TransactSql.ScriptDom, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd80
80cc91'. System.TypeLoadException: Could not load type 'Microsoft.SqlServer.TransactSql.ScriptDom.CatalogCollationOption' from assembly 'Microsoft.SqlServer.TransactSql.ScriptDom, Version=14.0.0.0, Culture
=neutral, PublicKeyToken=89845dcd8080cc91'.
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.AppendInitializeDatabaseSteps(DeploymentPlan plan)
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.OnGeneratePlan()
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.GeneratePlan(List`1 drops)
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.BuildPlan()
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.Controller.CreatePlan()
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<>c__DisplayClass5.<CreatePlanInitializationOperation>b__1()
   at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token)
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLogging
Context loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
   at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.DacpacManager.DeployDacpac(String connectionString, String databaseName, String dacpacName)
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.ShardDeploymentManager.CreateOrGetEmptyShard[T](RangeShardMap`1 shardMap, ISqlDatabaseManager sqlDatabaseManager, IDacpacManager dacpacManag
er, IShardManager shardManager)
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.ShardDeploymentManager.CreateShard[T](RangeShardMap`1 shardMap, Range`1 rangeForNewShard, ISqlDatabaseManager sqlDatabaseManager, IDacpacMan
ager dacpacManager, IShardManager shardManager)
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.ShardDeploymentManager.CreateShard[T](RangeShardMap`1 shardMap, ISqlDatabaseManager sqlDatabaseManager, IDacpacManager dacpacManager, IShard
Manager shardManager, IEnumerable`1 ranges, IConfiguration configuration)
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.Program.CreateShardMapManagerAndShard(IShardDeploymentManager shardDeploymentManager, IShardManager shardManager, ISqlDatabaseManager sqlDat
abaseManager, IDacpacManager dacpacManager, IRangeManager rangeManager, IConfiguration configuration)
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.Program.Main(String[] args)

Answer 1

This is an edge case to be sure, but maybe this would help:

At one point in the SC 9.0 installation, I had an error and used a hack/workaround that involved adding Microsoft.SqlServer.TransactSql.ScriptDom into the mix using gacutil.

That worked for me at the time to complete the 9.0 install, but with the 9.1 upgrade, I was having the exact same error message you encountered. Some blogs have suggested adding paths to resources through the registry, but that did not work for me. Removing the reference to ScriptDom in the GAC helped solve the problem.