The Problem

As we continue to migrate more and more older versions of Kentico to Xperience by Kentico, we find issues we have to overcome with each project.  This latest issue was specifically related to a split Contact Management database in a Kentico version 13 instance.  Kentico offers the ability to separate the Contact Management portion of your database to help with performance and the ability to back up your data on a more consistent basis. 

As we started the migration process on this project we received an error when executing the CLI to start the process stating a "separated database cannot be migrated".  Kentico provides documentation to separate and rejoin the databases which are pretty straightforward. However, when rejoining the databases, it DOES NOT migrate any data back into the database due to limitations with Azure. 

After we rejoined the database and imported the split database data into the joined database tables, we received event log errors from the ActivityLogWorker throwing the event code of ACTIVITIESPROCESSING. It was complaining about a stored procedure only having 19 columns but the user-defined table type requiring 20 columns. 

A Solution

There are really two solutions because there are two problems.  Problem 1 is dealing with the data import using SSMS.  While the process worked it wasn't without a lot of trial and error.  When you're in Kentico and click the button to join the databases back together, it says it's completed and if you want to copy the data over manually, you have to do it in a specific order and they list the order.  For we determined which tables had data in them by running a query against the old contact database and found we had to import in this order using SSMS:

1. Import the following tables and data: 

   • OM_ContactStatus 
   • OM_ContactGroup 
   • OM_Activity 
   • OM_ActivityType 

2. Import the following tables and data 

   • OM_Contact 

3. Import the following tables and data 

   • OM_ContactGroupMember 

   • OM_Membership 

   • OM_VisitorToContact 

4. Import the following tables and data 

   • Newsletter_OpenedEmail 

   • Newsletter_Link 

   • Newsletter_ClickedLink 

   • Newsletter_IssueContactGroup 

If you have data in other tables, you'll have to import that data as well.  SSMS doesn't allow you to specify the order unless you write queries to perform the work.  If you check the boxes only, then you have to run the process several times in the order above. 

The second problem is in the screenshot above. The stored procedure, Proc_OM_Activity_BulkInsertActivities, is utilizing a table-value parameter (Type_OM_ActivityTable) that needs to be recreated because it didn't get updated for whatever reason.  There are some gotchas here though.  You can't simply delete the user-defined table type and recreate it AND you can't just modify the user-defined table type either.  This was my process:

1. Using SSMS I compared the CMS database against the Contact database to verify what fields were extra or needed in the stored procedure (not the user-defined table type).
2. I created a temporary user-defined table type with the proper number/name of columns in it. Essentially, right-click the existing user-defined table type and click Create to a new query window and it will generate the code for you.  You just need to modify the name slightly and keep that name handy. 
   
   
    
3. Update the stored procedure Proc_OM_Activity_BulkInsertActivities with the new user-defined table type name in #2 so can delete the corrupt one.
4. Drop the corrupt user-defined table type.
5. Recreate the user-defined table type with the same name. Use the script you generated previously and remove the "customization" you made to the name. 
6. Update the SP with the new/proper user-defined table type name.
7. Drop the temporary user-defined table type.

After I ran these sql queries, I restarted the website and activities started picking up like I was expecting.  

Summary

Nothing is ever as easy as it seems, even if you've done it a few times before.  Hopefully, this helps those who are in the process of migrating to Xperience by Kentico from earlier versions!  It's a great platform and after you get throught the small bumps in the road, it is 100% worth it.