In a previous blog post I have introduce the DBXL Migration Tool that allowed automating many DBXL operations. Today I will outline a general approach for migrating complex solutions involving multiple document types (i.e. InfoPath Templates) and database tables.
The Migration Tool typically works with the following folder structure:
Samples
DocTypeName
mapping.xml
install.sql
SampleDocs
(In the example scenario that comes with the tool, the full path is C:\Temp\Samples\DocType)
While both mapping.xml and SampleDocs are automatically created by the export runs, the install.sql has to be created manually. We will discuss how this is done as we go through the migration process. This process can be broken up into logical steps:
1. Analyze your solution
Suppose you need to migrate a Timecards solution. The first step is to edit the Timecard configuration in DBXL and to inspect what tables it is being shred into. Let's say that Timecards shred into two tables: Hours and WorkItems.
The second step is to figure out the dependencies. For this, design the form in InfoPath Designer and inspect the secondary data sources. In a typical DBXL solution, data connections such as QueryDocuments are indicative of a dependency. Verify the web service arguments to find out what the doctypes we depend upon are.
Finally, the third step is to repeat the same process for all the doctypes we've found in the step 3.
When done, you will end up with a sort of a Solution Manifest (you can create a text file for convenience, for example) that will list all doctypes and dependencies. For example, in the Timecard case this could be:
Main Document Type: Timecard
Shreds into: Hours, WorkItems
Depends on: TimecardConfig
Depends on: Employees
Shreds into: Employees
Depends on: Projects
Shreds into: Projects
2. Create SQL scripts
Now that you know what your solution is composed of, it is time to create the SQL scripts. If you are lucky you already have them around, otherwise you'll need to re-create them. The easiest way to do so is by using the SQL Server Management Studio. Create one install.sql file per doctype that creates all the tables this doctype needs. Place the SQL scripts in the locations where the Migration Tool will drop the mappings and actual documents. In our example, those will be:
C:\Temp\Samples\Timecard\install.sql – contains SQL code to create Hours and WorkItems tables
C:\Temp\Samples\Employees\ install.sql – contains SQL code to the Employees table
C:\Temp\Samples\Projects\ install.sql – contains SQL code to create the Projects table
3. Configure the tool
Configure the Migration Tool to export the four document types (Timecard, TimecardConfig, Employees and Projects) along with their documents into C:\Temp\Samples (see the previous blog post for more details)
4. Run the tool - export
Execute the tool with the Export run. This should export all the data you need.
Caveat: Often enough, complex solutions shred into a separate database which might not exist on the target DBXL machine. Because of this, install.sql scripts will fail to run. To solve this problem, create a fake doctype called ensured in the Samples folder and put an install.sql script (without the mapping). The code in it could look like this:
IF NOT EXISTS( SELECT * FROM sys.databases WHERE name = 'YourDB' ) CREATE DATABASE YourDB
GO
USE YourDB;
IF NOT EXISTS (SELECT * FROM dbo.sysusers where name='NT AUTHORITY\NETWORK SERVICE')
BEGIN
CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_addrolemember N'db_datareader', N'NT AUTHORITY\NETWORK SERVICE'
EXEC sp_addrolemember N'db_datawriter', N'NT AUTHORITY\NETWORK SERVICE'
END
GO
5. Reconfigure the tool
Configure the Migration Tool to import the four document types along with their mappings. Make sure that you pass the correct DB connection striong to the ImportMapping method!
6. Run the tool - import
Execute the tool with the Import run. This should complete the migration by importing all the data into the new DBXL instance. Voila!