X
Closing this message and/or accessing our website tells us you are happy to receive all cookies on the ClearPeople website.
However, if you would like to, you can change your cookies settings at any time.
Recently, we came up to a scenario where, after playing around with Sitecore, we needed to remove all the contacts information and get a clean instance before going live. This is how we achieved it

Sitecore 9 has changed the way Contacts and Analytics information is stored, changing from MongoDB to SQL Server, and adding a completely new scenario to manage. While we have a pretty good API to manage contacts, facets and interactions from the code point of view, and some nice tools like Experience Analytics or Experience Profile to explore the content, there is functionality that is missing from both Sitecore or API: Deleting a contact or cleaning up the database.

 

In our case (this website), we did an internal soft launch a few weeks before going live, so analytics started tracking contacts and interactions. Furthermore, we have an integration with our CRM, so when we enabled synchronisation, all the test and dummy users from the test phase went straight ahead to our production CRM. Fortunately, there were not that many, and we could clean them up, but we had to disable synchronisation until we could clean up the whole xConnect data before going live.

 

So, after contacting Sitecore support, these are the steps to consider to clean up xConnect database:

 

Remove all Contacts

To remove all contacts, we will truncate all the tables in both databases Shard0 and Shard1. As some of these tables have foreign keys references, we cannot simply truncate them all, as we will get an error. To help with that, we have our own script to delete these references, truncate, and create the references back again. Once we have this procedure in place, we can safely truncate all the tables with dependencies. You'll find all the SQL scripts at the end of this post.

 

The steps to clean up contacts are:

  1. Backup Shard0 & Shard1 database
  2. Stop xConnect site on ISS
  3. Stop the 2 xConnect Windows services. Depending on your infrastructure scaling architecture, these may be on different servers.
  4. On each DB: Shard0 & Shard1:
    • Create the SP to recreate dependencies: truncate_non_empty_table.sql
    • Execute the clean script: CleanUp_xConnect_Tables.sql
    • Run a rebuild of the search index:
      • Go to "C:\ <xconnect_path>\App_data\jobs\continuous\IndexWorker"
      • Open a command line window and run “XConnectSearchIndexer -rr”
      • Get more detailed info at https://doc.sitecore.net/developers/xp/xconnect/xconnect-search-indexer/rebuild-index/rebuild-solr-index.html

Reporting Database

Now that we have cleaned all contacts and interactions, we need to rebuild the reporting database to clean up all the associated analytics data.The steps to rebuild the reporting database are:

  1. Get a clean Sitecore.Reporting.DACPAC from distribution package
    • Check if it's on premise (SQL Server) or Azure, and grab the right one
  2. Deploy to Sitecore.Reporting.Secondary database
  3. On premise SQL:
    • Add the reporting user executing provided "AddReportingUser.sql". Edit & review password.
  4. SQL Azure:
    • Move DB to the Elastic pool, if using one.
    • Execute "SetCompatibilityLevel.Reporting.sql" in the new reposting secondary db
    • Execute "CreateUser.Reporting.sql". Edit & review user and password.
  5. Add connection string to the secondary DB: reporting.secondary
  6. If using WFFM, run the WFFM_Analytics.sql script in the new DB
  7. Rebuild Reporting database at <sitename>/sitecore/admin/RebuildReportingDB.aspx
  8. Manually swap connection string:
    • Reporting -> point to secondary
    • Reporting.secondary -> commented out

SQL Scripts

Find below the SQL script used:

truncate_non_empty_table.sql

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[truncate_non_empty_table]
  @OwnerName VARCHAR(64), @TableToTruncate VARCHAR(64)
AS 

BEGIN

SET NOCOUNT ON

-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)
DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)   
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

-- 1 = Will not execute statements 
 SET @Debug = 0
-- 0 = Will not create or truncate storage table
-- 1 = Will create or truncate storage table
 SET @Recycle = 0
-- 1 = Will print a message on every step
 set @Verbose = 1

 SET @i = 1
    SET @CreateStatement = 'ALTER TABLE [' + @OwnerName + '].[]  WITH NOCHECK ADD  CONSTRAINT [] FOREIGN KEY([]) REFERENCES [' + @OwnerName + '].[] ([])'
    SET @DropStatement = 'ALTER TABLE [' + @OwnerName + '].[] DROP CONSTRAINT []'
    SET @TruncateStatement = 'TRUNCATE TABLE [' + @OwnerName + '].[]'

-- Drop Temporary tables
IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
    DROP TABLE #FKs

-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       OBJECT_NAME(parent_object_id) as TableName,
       clm1.name as ColumnName, 
       OBJECT_NAME(referenced_object_id) as ReferencedTableName,
       clm2.name as ReferencedColumnName
  INTO #FKs
  FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1 
         ON fk.parent_column_id = clm1.column_id 
            AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2
         ON fk.referenced_column_id = clm2.column_id 
            AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
 WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
 ORDER BY OBJECT_NAME(parent_object_id)


-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
   BEGIN
        IF @Verbose = 1
     PRINT '1. Creating Process Specific Tables...'

  -- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
  CREATE TABLE [Internal_FK_Definition_Storage] 
  (
   ID int not null identity(1,1) primary key,
   FK_Name varchar(250) not null,
   FK_CreationStatement varchar(max) not null,
   FK_DestructionStatement varchar(max) not null,
   Table_TruncationStatement varchar(max) not null
  ) 
   END 
ELSE
   BEGIN
        IF @Recycle = 0
            BEGIN
                IF @Verbose = 1
       PRINT '1. Truncating Process Specific Tables...'

    -- TRUNCATE TABLE IF IT ALREADY EXISTS
    TRUNCATE TABLE [Internal_FK_Definition_Storage]    
      END
      ELSE
         PRINT '1. Process specific table will be recycled from previous execution...'
   END

IF @Recycle = 0
   BEGIN

  IF @Verbose = 1
     PRINT '2. Backing up Foreign Key Definitions...'

  -- Fetch and persist FKs             
  WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
   BEGIN
    SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
    SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
    SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
    SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
    SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)
    SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'',@TableName),'',@ConstraintName)
    SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'',@TableName),'',@ColumnName),'',@ConstraintName),'',@ReferencedTableName),'',@ReferencedColumnName)
    SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'',@TableName) 

    INSERT INTO [Internal_FK_Definition_Storage]
                        SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp

    SET @i = @i + 1
    IF @Verbose = 1
       PRINT '  > Backing up [' + @ConstraintName + '] from [' + @OwnerName + '].[' + @TableName + ']'
    END   
    END   
    ELSE 
       PRINT '2. Backup up was recycled from previous execution...'

       IF @Verbose = 1
     PRINT '3. Dropping Foreign Keys...'

    -- DROP FOREIGN KEYS
    SET @i = 1
    WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
          BEGIN
             SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)

    IF @Debug = 1 
       PRINT @Statement
    ELSE
       EXEC(@Statement)

    SET @i = @i + 1

    IF @Verbose = 1
       PRINT '  > Dropping [' + @ConstraintName + ']'
    END     


    IF @Verbose = 1
       PRINT '4. Truncating Tables...'

    -- TRUNCATE TABLES
    IF @Verbose = 1
       PRINT '  > TRUNCATE TABLE [' + @OwnerName + '].[' + @TableToTruncate + ']'

    IF @Debug = 1 
        PRINT 'TRUNCATE TABLE [' + @OwnerName + '].[' + @TableToTruncate + ']'
    ELSE
        EXEC('TRUNCATE TABLE [' + @OwnerName + '].[' + @TableToTruncate + ']')


    IF @Verbose = 1
       PRINT '5. Re-creating Foreign Keys...'

    -- CREATE FOREIGN KEYS
    SET @i = 1
    WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
          BEGIN
             SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

    IF @Debug = 1 
       PRINT @Statement
    ELSE
       EXEC(@Statement)

    SET @i = @i + 1
    IF @Verbose = 1
       PRINT '  > Re-creating [' + @ConstraintName + ']'
    END

    IF @Verbose = 1
       PRINT '6. Process Completed'
END
GO

CleanUp_xConnect_Tables.sql

truncate table [xdb_collection].[GetContactIdsByIdentifiers_Staging]
truncate table [xdb_collection].[DeviceProfiles_Staging]
truncate table [xdb_collection].[DeviceProfileFacets_Staging]
truncate table [xdb_collection].[GetContactsByIdentifiers_Staging]
truncate table [xdb_collection].[InteractionFacets_Staging]
truncate table [xdb_collection].[ContactIdentifiersIndex_Staging]
truncate table [xdb_collection].[ContactFacets_Staging]
truncate table [xdb_collection].[ContactIdentifiersIndex]
truncate table [xdb_collection].[ContactIdentifiers_Staging]
truncate table [xdb_collection].[CheckContacts_Staging]
truncate table [__ShardManagement].[ShardMapManagerLocal]
truncate table [xdb_collection].[Interactions_Staging]
truncate table [xdb_collection].[Contacts_Staging]

EXECUTE [dbo].[truncate_non_empty_table] 'xdb_collection', 'DeviceProfiles'
GO

truncate table [xdb_collection].[DeviceProfileFacets]

truncate table [xdb_collection].[InteractionFacets]
EXECUTE [dbo].[truncate_non_empty_table] 'xdb_collection', 'Interactions'
GO

truncate table [xdb_collection].[ContactFacets]
truncate table [xdb_collection].[ContactIdentifiers]
EXECUTE [dbo].[truncate_non_empty_table] 'xdb_collection', 'Contacts'
GO

truncate table [__ShardManagement].[ShardMappingsLocal]
EXECUTE [dbo].[truncate_non_empty_table] '__ShardManagement', 'ShardsLocal'
GO
EXECUTE [dbo].[truncate_non_empty_table] '__ShardManagement', 'ShardMapsLocal'
GO

AddReportingUser.sql

Use [cp9_Reporting_Secondary]
Go

alter database [cp9_Reporting_Secondary] 
set containment = partial
go

CREATE USER reportinguser WITH PASSWORD = 'Test12345';
GO

EXEC sp_addrolemember 'db_datareader', reportinguser;
EXEC sp_addrolemember 'db_datawriter', reportinguser;
GO

GRANT EXECUTE TO reportinguser;
GO 

SetCompatibilityLevel.Reporting.sql

DECLARE @dbName NVARCHAR(128)
SET @dbName = DB_NAME()
EXEC ('ALTER DATABASE [' + @dbName + '] SET compatibility_level = 130;')
GO

CreateUser.Reporting.sql

IF USER_ID('PlaceHolderForUser') IS NULL
BEGIN
	CREATE USER [PlaceHolderForUser] WITH PASSWORD = 'PlaceHolderForPassword';

	EXEC sp_addrolemember 'db_datareader', [PlaceHolderForUser];
	EXEC sp_addrolemember 'db_datawriter', [PlaceHolderForUser];

	GRANT EXECUTE TO [PlaceHolderForUser];
end

 

If everything has gone correctly, you should get an empty list in the Experience Profile and an empty Experience Analytics applications. Did you find this useful? Do you have a different approach? Leave your comment below!

Author bio

Sergi Gisbert
Sergio Gisbert
Technical Architect
I'm primarily focused on the delivery of successful projects with Sitecore CMS. When I’m not at work, I’m running a side project started back in 2009 called “Fatherhood," which takes up most of my non-working hours. I'm always keen to escape to snowy mountains and ski down them!

Comments


comments powered by Disqus

Related Articles

Sign up to our Newsletter

Every now and then, we'd like to send you information that delivers, develops and promotes our products and services that are relevant to you. Submitting your details tells us that you're OK with this and you also agree to our Privacy & Cookies policy. You can, of course, opt out of these communications at any time.