Upgrading Sitecore Web Forms for Marketers to 8.1 and keeping MSSQL

If you are lucky enough to need to upgrade a Sitecore solution containing WFFM from 7.2 or earlier (or even 6.x as I have been doing) to 8.1, and you have a requirement to keep form data stored in MSSQL then you may encounter a few issues.

The first thing to note is that in Sitecore 7.5 WFFM data moves from the MSSQL database to Mongo with no support for keeping it in MSSQL. Once you get to 8.1 there is support for MSSQL but the docs do not tell you this and you have to hunt for the configuration changes you need to make as Mongo is the default.

If you are not upgrading and starting off with 8.1+ you are able to do fairly easily by following this article by Jason Wilkerson – I especially love the connection string setup, classic Sitecore.

However, if you are upgrading you cannot simply take your 7.5 WFFM database and follow the above setup as the database structure has changed. Sitecore does not provide a supported method of upgrading your MSSQL WFFM database.

Luckily the structure has not changed significantly and Sitecore Support were able to provide the following SQL script to move data from a WFFM 2.4 (used on Sitecore 7.1-7.2) database to new empty WFFM 8.1 database.

INSERT INTO [YOUR 8.1 SQL DATABASE].[dbo].[FieldData] (Id, FormId, FieldItemId, FieldName, Value, Data) SELECT Id, FormId, FieldId, FieldName, Value, Data 
FROM [YOUR 2.4 SQL DATABSE].[dbo].[Field] 
INSERT INTO [YOUR 8.1 SQL DATABASE].[dbo].[FormData] (Id, FormItemId, InteractionId, TimeStamp, Data, ContactId) SELECT Id, FormItemId, SessionId, Timestamp, Data, NEWID() 
FROM [YOUR 2.4 SQL DATABSE].[dbo].[Form] 

Sitecore have put a request in to the Product department to add an upgrade option for this but I won’t hold my breath.

My full process for upgrading consisted of:

• Follow the standard Sitecore upgrade path for Sitecore CMS and modules up to 7.2 Initial release
• Continued to follow the upgrade path but did not convert any WFFM data to Mongo, simply updated any application elements of WFFM
• Once on Sitecore 8.1 I created a new WFFM 8.1 database and ran the script above to move data across (deleted/backed up the original WFFM database)
• Updated the forms configuration as per the article given above

Hopefully this might help someone, Sitecore upgrades are painful enough.