Delete xDB contacts and interactions from Collection databases

To clean out unwanted contact data from your collection databases, there are some referential integrity constraints that mean the deletions have to happen in a specific order. The following query will delete all contacts, facets, interactions, interaction facets, and contact identifiers from xDB.

  delete
  FROM [xdb_collection].[ContactIdentifiers]
  
  delete
  FROM [xdb_collection].[ContactIdentifiersIndex]

  delete
  FROM [xdb_collection].[ContactFacets]
    
  delete
  FROM [xdb_collection].[InteractionFacets]
  
  delete
  FROM [xdb_collection].[Interactions]

  delete
  FROM [xdb_collection].[Contacts]

You will need to run this on both Collection shards.

Contained database users and Sitecore

From Sitecore 9.0 onwards, the database users changed from being traditional SQL logins to “contained database users“. With the exception of the Shard Map Manager and Collection shard databases, the users for each database are contained within the database itself. The users are portable with the database and creating and updating them is different from the “old” way.

An example of where this might be needed is creating a secondary reporting database for a reporting rebuild. If you deploy the DACPAC you will need to create a user. The SQL for this is:

CREATE USER reportinguser WITH PASSWORD = 'new_password';

To change the password:

ALTER USER reportinguser WITH PASSWORD = 'new_password';

To connect to a contained database in SQL Server Management Studio, enter your contained database username and password, then click the Options>> button on the SQL Server login modal and choose a database.

This way you can test your connection for troubleshooting purposes.

Also, slightly off topic, but for the secondary reporting database you can assign dbo role to the reporting user with sp_addrolemember

exec sp_addrolemember 'db_owner', 'reportinguser';