Experience Sitecore ! | All posts tagged 'SQL'

Experience Sitecore !

More than 200 articles about the best DXP by Martin Miles

XM Cloud content migration: connecting external database

Historically when performing content migration with Sitecore we used to deal with database backups. In a modern SaaS world, we do not have the luxury of neither managing cloud database backups, nor the corresponding UI for doing this. Therefore, we must find an alternative approach.

Technical Challenge

Let’s assume we have a legacy Sitecore website, in my case that was XP 9.3 and we’ve been provided with only a master database backup having all the content. The objective is to perform content migration from this master database into a new and shiny XM Cloud environment(s).

Without having direct access to the cloud, we can only operate locally. In theory, there could be a few potential ways of doing this:

  1. Set up a legacy XP of the desired version with the legacy content database already attached/restored to it. Then try to attach (or restore) a vanilla XM Cloud database to a local SQL Server as a recipient database in order to perform content migration into it. Unfortunately, the given approach would not work since SQL Server version incompatibility between XM Cloud and XP 9.3. Even if that was possible, running XP 9.3 with the XM Cloud database won’t work as Xз 9.3 neither knows about XM Cloud schema nor is capable of handling Items as Resource required feature which was invented later in XP 10.1. Therefore – this option is not possible.

  2. Can we go the other way around by using the old database along with XM Cloud? This is not documented, but let’s assess it:

    1. Definitely won’t work in the cloud since we’re not given any control of DBs and their maintenance or backups.

    2. In a local environment, XM Cloud only works in Docker containers and it is not possible to use it with an external SQL Server where we have a legacy database. But what if we try to plug that legacy database inside of the local SQL Container? Sadly, there are no documented ways of achieving that.

  3. Keep two independent instances side by side (legacy XP and XM Cloud in containers) and use an external tool to connect both of them in order to migrate the content. In theory that is possible but carries on few drawbacks.
    1. The tool of choice is Razl, but this tool is not free, requires a paid license, and does not have a free trial to ever test this out.
    2. Connecting to a containerized environment may not be easy and require some additional preps
    3. You may need to have a high-spec computer (or at least two mid-level machines connected to the same network) to have both instances running side by side.

After some consideration, the second approach seems to be reasonable to try so let’s give it a chance and conduct a PoC.

Proof of Concept: local XM Cloud with external content database

Utilize the second approach we’re going to try attaching the given external legacy database to XM Cloud running in a local containerized setup. That will allow using a built-in UI for mass-migrating the content between the databases (as pictured below) along with the Sitecore PowerShell script for finalizing and fine-tuning the migrated content.

Control Panel

Step 1: Ensurу SQL Server port is externally exposed

We are connecting the external SQL Server Management studio through a port of the SQL Server container that is exposed externally in order to make it possible. Luckily, that has been done for us already, just make sure docker-compose has:

ports:
      - "14330:1433"

Step 2: Spin up an XM Cloud containers and confirm XM Cloud works fine for you

Nothing extraordinary here, as easy as running .\init.ps1 followed by .\up.ps1.

Step 3: Connect SQL Management Studio to SQL Server running in a container.

After you sound up containers, run SQL Management Studio and connect to SQL Server running in SQL container through an exposed port 14330, as we did at step 1:

Connection parameters

Step 4: Restore the legacy database

If you have a Data-Tier “backpack” file you may want to do an extra step and convert it into a binary backup for that particular version used by XMCloud before restoring. This step is optional, but in case you want to restore the backup more than once (which is likely to happen), it would make sense to take a binary backup as soon as you restore the data-tier “backpack” first time ever. Data-tier backups process much slower than binaries, so that will definitely save time in the future.

Once connected, let’s enable contained database authentication. This step is mandatory, otherwise, that would not be possible to restore a database:

EXEC sys.sp_configure N'contained database authentication', N'1'
go
exec ('RECONFIGURE WITH OVERRIDE')
go

One more challenge ahead: when performing backup and restore operations, SQL Server shows up a path local to the server engine, and not the host machine. That means, our backup should exist “inside” of SQL container. Luckily, w have this also covered. Make sure docker-compose.override.yml contains:

mssql:
volumes:
      - type: bind
source: .\docker\data\sql
target:c:\data

That means, one can locate legacy database backups into .\docker\data\sql folder of a host machine and it will magically appear within C:\datafolder when using SQL Management Studio database backup restore tool which you can perform now.

Important! Restore legacy database using the “magic name” in a format Sitecore.<DB_NAME_SUFFIX>, further down below I will be using the value RR as DB_NAME_SUFFIX.

Once got restored database in SQL Server Management Studio under the name Sitecore.RR we need to plug this database to the system. There is a naming convention hidden from our eyes within CM containers.

Step 5: Configure connection strings

Unlike in XM/XP – there is no documented way to plug an external database. The way connection strings are mapped to the actual system is cumbersome, it uses some “magic” hidden within the container itself and obfuscated from our eyes. It only tool to reach it experimental way. Here are the steps to reproduce:

  • Add environmental variable to docker-compose record for CM:

    • Sitecore_ConnectionStrings_RR: Data Source=${SQL_SERVER};Initial Catalog=${SQL_DATABASE_PREFIX}.RR;User ID=${SQL_SA_LOGIN};Password=${SQL_SA_PASSWORD}
  • Add a new connection string record. To do so you’ll need to create a connection strings file within your customization project as .\src\platform\<SITENAME>\App_Config\ConnectionStrings.config with the content of the connection strings file from the CM container with the addition of a new string:

Please note the difference in the suffix format of both above records, that is totally fine. CM container still processes that correctly.

Step 6: Reinstantiating CM container

Simply restarting a CM container is not sufficient. You must remove it and re-create it, just killing/stopping is not sufficient.

For example, the below command will work for that purpose:

docker-compose restart cm

… not will this one:

docker-compose kill cm

The reason is that CM will not update environmental variables from docker-compose file upon restart. Do this instead:

docker-compose kill cm
docker-compose rm cm --force
docker-compose up cm -d

Step 7: Validating

  1. Inspecting CM container for environmental variables will show you this new connection string, as added:

    1. "Env": [
                  "Sitecore_ConnectionStrings_RR=Data Source=mssql;Initial Catalog=Sitecore.RR;User ID=sa;Password=6I7X5b0r2fbO2MQfwKH"
  2. Inspecting connection string config (located at C:\inetpub\wwwroot\App_Config\ConnectionStrings.config on CM container) contains the newly added connection string.

Step 8: Register new database with XM Cloud

It can be done the below config patch that does this job. Save it as docker\deploy\platfo.rm\App_Config\Include\ZZZ\z.rr.config for test and later do not forget to include it in a platform customization project, so that it gets shipped with each deployment
<?xml version="1.0" encoding="UTF-8"?>
<configuration
	xmlns:patch="www.sitecore.net/.../">
	<sitecore>
		<eventingdefaultProvider="sitecore">
			<eventQueueProvider>
				<eventQueuename="rr"patch:after="evertQueue[@name='web']"type="Sitecore.Data.Eventing.$(database)EventQueue, Sitecore.Kernel">
					<paramref="dataApis/dataApi[@name='$(database)']"param1="$(name)"/>
					<paramref="PropertyStoreProvider/store[@name='$(name)']"/>
				</eventQueue>
			</eventQueueProvider>
		</eventing>
		<PropertyStoreProvider>
			<storename="rr"patch:after="store[@name='master']"prefix="rr"getValueWithoutPrefix="true"singleInstance="true"type="Sitecore.Data.Properties.$(database)PropertyStore, Sitecore.Kernel">
				<paramref="dataApis/dataApi[@name='$(database)']"param1="$(name)"/>
				<paramresolve="true"type="Sitecore.Abstractions.BaseEventManager, Sitecore.Kernel"/>
				<paramresolve="true"type="Sitecore.Abstractions.BaseCacheManager, Sitecore.Kernel"/>
			</store>
		</PropertyStoreProvider>
		<databases>
			<databaseid="rr"patch:after="database[@id='master']"singleInstance="true"type="Sitecore.Data.DefaultDatabase, Sitecore.Kernel">
				<paramdesc="name">$(id)
				</param>
				<icon>Images/database_master.png</icon>
				<securityEnabled>true</securityEnabled>
				<dataProvidershint="list:AddDataProvider">
					<dataProviderref="dataProviders/main"param1="$(id)">
						<disableGroup>publishing</disableGroup>
						<prefetchhint="raw:AddPrefetch">
							<sc.includefile="/App_Config/Prefetch/Common.config"/>
							<sc.includefile="/App_Config/Prefetch/Webdb.config"/>
						</prefetch>
					</dataProvider>
				</dataProviders>
				<!-- <proxiesEnabled>false</proxiesEnabled> -->
				<archiveshint="raw:AddArchive">
					<archivename="archive"/>
					<archivename="recyclebin"/>
				</archives>
				<cacheSizeshint="setting">
					<data>100MB</data>
					<items>50MB</items>
					<paths>2500KB</paths>
					<itempaths>50MB</itempaths>
					<standardValues>2500KB</standardValues>
				</cacheSizes>
			</database>
		</databases>
	</sitecore>
</configuration>

Step 9: Enabling Sitecore PowerShell Extension

Next, we’d want to enable PowerShell, if that is not yet done. You won’t be able to migrate the content using SPE without performing this step.

<?xml version="1.0" encoding="utf-8"?>
<configuration
	xmlns:patch="http://www.sitecore.net/xmlconfig/"
	xmlns:role="http://www.sitecore.net/xmlconfig/role/"
	xmlns:set="http://www.sitecore.net/xmlconfig/set/">
	<sitecorerole:require="XMCloud">
		<powershell>
			<userAccountControl>
				<tokens>
					<tokenname="Default"elevationAction="Block"/>
					<tokenname="Console"expiration="00:55:00"elevationAction="Allow"patch:instead="*[@name='Console']"/>
					<tokenname="ISE"expiration="00:55:00"elevationAction="Allow"patch:instead="*[@name='ISE']"/>
					<tokenname="ItemSave"expiration="00:55:00"elevationAction="Allow"patch:instead="*[@name='ItemSave']"/>
				</tokens>
			</userAccountControl>
		</powershell>
	</sitecore>
</configuration>

Include the above code into a platform customization project as .\docker\deploy\platform\App_Config\Include\ZZZ\z.SPE.config. If everything is done correctly, you can run SPE commands, as below:

SPE results

The Result

After all the above steps are done correctly, you will be able to utilize the legacy content database along with your new shiny local XM Cloud instance:
Result in Sitecore Content Editor
Now you can copy items between databases just by using built-in Sitecore UI preserving their IDs and version history. You can also copy items with SPE from one database to another which are both visible to the SPE engine.

Few SQL tricks helping to work with Sitecore

1. Delete all the databases having a specific prefix, in the example below it will drop all the databases starting with habitat.dev.local_ - this may be helpful when Sitecore 9 installation broke half way down and you'd like to return everything to an initial point, also removing (partly) installed databases:

USE MASTER
GO

DECLARE @dbnames NVARCHAR(MAX)
DECLARE @statement NVARCHAR(MAX)
SET @dbnames = ''
SET @statement = ''
SELECT @dbnames = @dbnames + ',[' + name + ']' FROM sys.databases WHERE NAME LIKE 'habitat.dev.local_%'
IF LEN(@dbnames) = 0
    BEGIN
    PRINT 'no databases to drop'
    END
ELSE
    BEGIN
    SET @statement = 'drop database ' + SUBSTRING(@dbnames, 2, LEN(@dbnames))
    PRINT @statement
    EXEC sp_executesql @statement
    END


2. Backup all SQL database having some prefix (in this case, habitat.dev.local_) to a folder (like C:\DatabaseBackups\):

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @scPrefix VARCHAR (20) -- Sitecore database prefix

-- specify database backup directory
SET @path = 'C:\DatabaseBackups\' 

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

-- specify the Sitecore db prefix
SET @scPrefix = 'habitat.dev.local_'
DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE CharIndex(@scPrefix, name) = 1 OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

3. Shrink all databases larger than a certain number of megabytes (stored in @megabytesMoreToShrink variable):

DECLARE @megabytesMoreToShrink INT SET @megabytesMoreToShrink = 100

DECLARE @name NVARCHAR(MAX) 
DECLARE @logical_name NVARCHAR(MAX) 

DECLARE db_cursor CURSOR FOR 
SELECT db.name AS DBName, mf.Name AS Logical_Name
FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id Where type_desc = 'LOG' AND (size*8)/1024 > @megabytesMoreToShrink

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name, @logical_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  

              exec('USE ' + @name + ' ;ALTER DATABASE ' + @name + ' SET RECOVERY SIMPLE;DBCC SHRINKFILE ('''+@logical_name +''', 1);ALTER DATABASE '+@name +' SET RECOVERY FULL')

              FETCH NEXT FROM db_cursor INTO @name, @logical_name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

4. Change sa password for SQL instance, you do not need to know the previous password at all, but need to have an administrative access, for sure. Run osql tool, it's path should be added to the environment variable, but if it's not there, please find it here: "%ProgramFiles%\Microsoft SQL Server\130\Tools\Binn\OSQL.exe":

osql -S SITECOREDEV -E
1> sp_password NULL, 'SA_PASSWORD','sa'
2> GO

5. Some basic item's operations, such as retrieving by ID, by parent and retrieving fields and versions:

-- Get item by Sitecore ID
SELECT TOP 1000 *
  FROM [habitat_Master].[dbo].[Items]
  WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'

-- Get item by ID of parent in Sitecore
SELECT TOP 1000 *
  FROM [habitat_Master].[dbo].[Items]
  WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'

-- Enumerate all the versioned fieds for an item in Sitecore by item ID
SELECT TOP 1000 
  i.Name, v.[Id], [ItemId], [LANGUAGE], [FieldId], [Value], v.[Created], v.[Updated]
  FROM [habitat_Master].[dbo].[versionedFields] v
  JOIN [habitat_Master].[dbo].Items i 
    ON v.FieldId = i.ID
  WHERE ItemId = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
  ORDER BY [Name]

5. Some basic item's operations, such as retrieving by ID, by parent and retrieving fields and versions:

-- Get item by Sitecore ID
SELECT TOP 1000 *
  FROM [habitat_Master].[dbo].[Items]
  WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'

-- Get item by ID of parent in Sitecore
SELECT TOP 1000 *
  FROM [habitat_Master].[dbo].[Items]
  WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'

-- Enumerate all the versioned fieds for an item in Sitecore by item ID
SELECT TOP 1000 
  i.Name, v.[Id], [ItemId], [LANGUAGE], [FieldId], [Value], v.[Created], v.[Updated]
  FROM [habitat_Master].[dbo].[versionedFields] v
  JOIN [habitat_Master].[dbo].Items i 
    ON v.FieldId = i.ID
  WHERE ItemId = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
  ORDER BY [Name]

6. One of the most popular snippets, to restore default admin / b password. Please keep in mind running that against core database:

-- use core database
UPDATE dbo.aspnet_Membership
SET
    Password='qOvF8m8F2IcWMvfOBjJYHmfLABc=',
    PasswordSalt='OM5gu45RQuJ76itRvkSPFw=='
WHERE
    UserId = (SELECT UserId FROM dbo.aspnet_Users WHERE UserName = 'sitecore\Admin')

7. Copy users and all their data including passwords from core database of one instance to another:

-- Copy all non-existing users and their data from OLD core database to NEW core database
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Applications SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Applications 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Applications WHERE [Sitecore.NEW.Core].dbo.aspnet_Applications .ApplicationId = [Sitecore.OLD.Core].dbo.aspnet_Applications.ApplicationId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Membership SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Membership 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Membership WHERE [Sitecore.NEW.Core].dbo.aspnet_Membership .UserId = [Sitecore.OLD.Core].dbo.aspnet_Membership.UserId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Paths SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Paths 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Paths WHERE [Sitecore.NEW.Core].dbo.aspnet_Paths .PathId = [Sitecore.OLD.Core].dbo.aspnet_Paths.PathId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_PersonalizationAllUsers 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers WHERE [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers.PathId = [Sitecore.OLD.Core].dbo.aspnet_PersonalizationAllUsers .PathId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_PersonalizationPerUser 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser WHERE [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser.PathId = [Sitecore.OLD.Core].dbo.aspnet_PersonalizationPerUser .PathId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Profile SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Profile 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Profile WHERE [Sitecore.NEW.Core].dbo.aspnet_Profile.UserId = [Sitecore.OLD.Core].dbo.aspnet_Profile.UserId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Roles SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Roles 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Roles WHERE [Sitecore.NEW.Core].dbo.aspnet_Roles.RoleName = [Sitecore.OLD.Core].dbo.aspnet_Roles.RoleName)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_SchemaVersions 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions WHERE [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions.Feature = [Sitecore.OLD.Core].dbo.aspnet_SchemaVersions.Feature)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Users SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Users 
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Users WHERE [Sitecore.NEW.Core].dbo.aspnet_Users.UserName = [Sitecore.OLD.Core].dbo.aspnet_Users.UserName)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_UsersInRoles 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles WHERE [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles.UserId = [Sitecore.OLD.Core].dbo.aspnet_UsersInRoles.UserId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_WebEvent_Events 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events WHERE [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events.EventId = [Sitecore.OLD.Core].dbo.aspnet_WebEvent_Events .EventId)

8. Delete children item of an item with a given ID directly from SQL (beware item cache). Please make sure you execute that in a context of a Sitecore database (ie. call use Sitecore_master before running this):

DECLARE @parentId AS UNIQUEIDENTIFIER; SET @parentId = '{29267838-AAB2-415F-B07A-D006719CD088}'
DECLARE @RowsToProcess  INT
DECLARE @CurrentRow     INT
DECLARE @SelectCol1     UNIQUEIDENTIFIER
DECLARE @Items2Delete TABLE (RowID INT NOT NULL PRIMARY KEY IDENTITY(1,1),ID UNIQUEIDENTIFIER)

SET @parentId = '{29267838-AAB2-415F-B07A-D006719CD088}'

INSERT INTO @Items2Delete (ID)
SELECT [Descendant] AS ID
  FROM [dbo].[Descendants]
  WHERE Ancestor = @parentId
SET @RowsToProcess=@@ROWCOUNT
PRINT  @RowsToProcess

INSERT INTO @Items2Delete (ID)
SELECT ID FROM [dbo].[Items] WHERE ParentID = @parentId
SET @RowsToProcess=@@ROWCOUNT
PRINT  @RowsToProcess

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT
        @SelectCol1=ID
        FROM @Items2Delete
        WHERE RowID=@CurrentRow

  DECLARE @id AS UNIQUEIDENTIFIER;

  SET @id = @SelectCol1
  PRINT @id

  EXEC sp_executesql N'DELETE FROM [Items]
        WHERE [ID] = @itemId

        DELETE FROM [SharedFields]
        WHERE [ItemId] = @itemId

        DELETE FROM [UnversionedFields]
        WHERE [ItemId] = @itemId

        DELETE FROM [VersionedFields]
        WHERE [ItemId] = @itemId',N'@itemId UNIQUEIDENTIFIER',@itemId=@id

  EXEC sp_executesql N'DELETE FROM [Descendants] WHERE [Descendant] = @itemId',N'@itemId uniqueidentifier',@itemId=@id
  EXEC sp_executesql N' DELETE FROM [Links] WHERE [SourceItemID] = @itemID AND [SourceDatabase] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'
  EXEC sp_executesql N'DELETE FROM [Tasks] WHERE [ItemID] = @itemID AND [Database] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'
END

Hope that helps you!

Web Forms for Marketers 8.0 - missing Save to Database action and making it work with SQL database again

As we know, Sitecore version 8.0 incorporated popular the module called Web Forms for Marketers (WFFM), and it became an integral part of Sitecore. WFFM is still shipped as a package, however now through Sitecore SDN portal, rather than via Marketplace as before.

So, let's assume you have a brand clean install of Sitecore (I have 8.0 Update 4 for this example). In order to download WFFM for 8.0 Update 4 please follow to https://dev.sitecore.net/Downloads/Sitecore_Experience_Platform/8_0/Sitecore_Experience_Platform_80_Update4.aspx and find download link below in the module section.

Remark 1: zip archive you download is not a package - it is normal archive containing 2 child packages - first is package for CM instance and additionally contains all dialogs etc. while another is just CD installation package. For our demonstration, unpack Web Forms for Marketers 8.0 rev. 150625.zip into /Data/Packages directory and use Development Tools - Installation Wizard, as you normally install packages.

Remark 2: ensure you have Mongo up and running, otherwise it will come to "package never installs" issue (more details about that bug here). it is required just for installation, after you have it installed you it is safe to turn off Mongo - WFFM will save data to database without any problems.

Remark 3: it may take up to 5 minutes to install the package, so please do not panic, as soon as you got Mongo running - you'll reach the point when it prompts you for setting Placeholder settings.


After WFFM installation is complete, you may configure a form. By default WFFM contains several pre-created forms as an example for people to play with it and get acknowledges with the module. For those who are new to WFFM here is a screenshot below displaying how to locate Form Designer:


Selecting Form Designer above loads a screen with the list of existing forms. Let's pick Get Our Newsletter form and click OK. Form Designer will load form configuration screen with fields and Subscribe button at the bottom. A click to that button opens Submit panel left hand side with several settings for pre-save (validation), save and post-save (success page / success message).


Remark 4: Those who used to work with previous versions of WFFM will be frustrated by missing Save to Database action from save actions menu. These are save actions that come out of box:


There is nothing to worry about, Save to Database action is still there but is not displayed only because as it always happens. However:

Remark 5: SQL database itself is not coming with the package, so you need to pick database backup from one of previous versions - its schema remains the same. To make you life easier, I have attached forms empty database backup so you may download it by this link. In WFFM 8.0 there is the setting that references connection string name (in you ConnectionStrings.config file) that is used by WFFM.

<!-- CONNECTION STRING - Sets the name of the connection string -->
<setting name="WFM.ConnectionString" value="wfm" />
<add name="wfm" connectionString="Data Source=.;Initial Catalog=test2_Forms;Integrated Security=False;User ID=sa;Password=your_password" />

To make Get Our Newsletter form work, we need to assign it to some page. Default landing page called Home will work well for our purpose. This is how you set a form to a placeholder:


Image above displays webform, because we use default layout that comes with webforms aspx page. For MVC there is rendering called Form MVC. With next step below (as clicking Edit button) you need to specify which exact form should be served by this sublayout (or rendering for MVC):



Now you may verify /Home page in Publish - Preview screen or publish web site and then load the page normally (http://your.site.name/) so you end up with something like on a screenshot below:


After you fill in valid email and click Subscribe button, you'll get "Thank you for filling in the form" message, that's in case database was references correctly. if not - there will be message abut unexpected error and corresponding exception details will appear in log file.

Let's get physically into database to verify the data has been stored correctly. In successful case you'll see one record in Form table and few records in Field table, one record per each form field.


That's all!

Following blog post will demonstrate how to make WFFM result screen display forms records from SQL database.