New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.

Ending the data migration nightmare


I have been doing a lot of DotNetNuke work lately for customers as well as working on a large eCommerce conversion effort.  One thing that all of my projects have had in common is that they have required frequent database changes: including changes to both the schema and the data.  One of the biggest challenges I have faced is how to keep multiple databases in synch during the development.  DotNetNuke has made several releases over the course of my projects and the same is true of my eCommerce project.  I have been using the Red-Gate SQL Compare for a couple years now and it has worked well for migrating schema changes into various environments.  But where I was struggling was with keeping data tables synchronized.  The schema problem is pretty straight forward but the data problem can be a real mess.  How do you easily isolate the data that should be changed from those values which are "production" or "test" data.

For my eCommerce project this is especially acute.  I am working on converting AspDotNetStorefront to run as a set of native DotNetNuke modules.  During my conversion efforts, AspDotNetStorefront has come out with multiple product upgrades.  Some of them have minor bug-fixes, while others have added a ton of new features and introduced schema and data changes.  Dealing with schema changes was easy, I could either run their migration scripts, or I could use SQL Compare to keep selected portions of the schema in synch.  I do not always want to run the full migration script because it could overwrite changes that I have made as part of the development effort.  SQL Compare allows me to easily handle a selective migration.

I had looked at Red-Gate's SQL Data Compare once before but found that it did not allow me to easily select just individual columns (some of my data is autogenerated GUIDs and I do not need/want to synchronize this data).  This spring Red-Gate released a major upgrade to their SQL tools so I decided to re-evaluate SQL Data Compare to see if the new version could solve my problem.  For my specific case, the new version was easily able to isolate changes to just the columns and tables I needed and greatly simplified the process of keeping my data in synch.  Let's take a look at the process.  Who knows it just might help you on your projects as well.

Migrating Data

Define the project

The first step to migrating data is to define the Databases that you want to compare.  This step is fairly straightforward and uses standard SQL Server login screens (Figure 1) for selecting the two DBs under comparison.

Select your databases
Figure 1: Select the databases to compare

Once we have our databases defined, select the tables and views tab.  Now we can select various tables for comparison.  In AspDotNetStoreFront, the AppConfig table is used to hold the various name/value pairs that controls most of functionality of the system.  Keeping this table in synch is critical and this is where I will focus my efforts.  As you can see in Figure 2 I have selected this table and left everything else unchecked.

Select the tables to compare
Figure 2:  Select the tables you want to synchronize.

As I stated previously, this table has a GUID column that I don't want to include in my comparison.  If I were to include this column, then I am guaranteed that every row in the table would show a difference and my migration effort would quickly become much more difficult.  SQL Data Compare offers a feature to solve this problem.  I am not sure if it existed in previous versions.  If it was then I overlooked it.  Notice in Figure 2 the far right column labeled "Columns in Comparison".  By selecting this column for my table, I am presented with a dropdown (Figure 3) that allows me to selectively include/exclude columns included in the comparison.  In order for this to work, I need to have a key or index on both tables than SQL Data Compare can use for joining the two tables.  In this case I am only interested in synchronizing data from a few columns.  Notice that I don't include the AppConfigGUID column.

Select the columns to compare/migrate
Figure 3:  Select the columns and index/key that will be used for this comparison.

There are a lot more options on the "Remap Objects" and "Options" tabs which I didn't need so I will skip those for this discussion.  At this point, I have fully configured project ready to begin the comparison.  So just press the "Done" button and wait for SQL Data Compare to begin performing it's magic.

Identify data to migrate

Now I have a screen that shows me the results of the comparison (Figure 4).  I can readily see the values which differ between the two databases and can decide to include or exclude various data rows in the final migration.  Notice that the data row with the "UseRotatorStyleSheet" has a Configvalue in the target database (StoreBeta6103), but not in the source database (aspdnsf621x).  This is a value that my version of the database requires, but that is not included in the standard storefront installation.  I definitely want to keep this row, and therefore have unchecked the "Include" column so that this row will not be affected during the migration.

Select rows to migrate
Figure 4:  Compare data values

Once I have completed reviewing the data to be migrated we are ready to actually generate the migration scripts.  We have glossed over a lot of options that are available to help you review the data.  I definitely recommend becoming familiar with this screen as some of the options make it very easy to quickly isolate those values which need to be examined versus the tons of data rows which are the same in both DBs.

Migrate the Data

Now that my selections are complete, just select the "Synchronization Wizard..." button on the toolbar (Figure 5).

Synchronize Wizard
Figure 5:  Launch the Synchronization Wizard

I will get one more chance to review what data will be migrated.  I can look at various summaries and even view the T-SQL script that will be used for the migration (Figure 6).  Everything looks good so far, so I click "Next >".

Review the changes
Figure 6:  Review the changes

SQL Data Compare will automatically apply all of my changes for me or it will launch SQL Query Analyzer so that I can have a little more control over the final migration process (Figure 7).  I may want to tweak the T-SQL, save the script or even apply the script to multiple databases.  I like the fact that SQL Data Compare provides this flexibility.  At every step I feel like I am in control rather than the tool.

Launch Query Analyzer
Figure 7: Let SQL Data Compare perform the migration or do it yourself

In my case I felt like I wanted to make a few changes to the script before applying it to the database (Figure 8). (NOTE:  The script shown is slightly different from the rest of our examples as I have altered the included columns before generating the script)

Run the script
Figure 8:  Edit and Run your script in SQL Query Analyzer

Now I can go ahead an execute the script and my development database now has a bunch of updated data values that will enable new features offered in the latest codebase.


As a developer, I am always looking for ways to simplify my job.  There are so many technologies involved in a typical web application and it is nearly impossible for one person to master them all.  My SQL skills are passable, but I am far from being an expert.  Data migration/synchronization tasks can be very complicated and any tool that can help me is very welcome indeed.  I believe that every developer needs a good stable of tools that allow them to leverage other developer's expertise.  For me SQL Data Compare is just one more outstanding tool that will see more and more use in my development efforts.


There are currently no comments, be the first to post one.

Comment Form

Only registered users may post comments.


Aderson Oliveira (22)
Alec Whittington (11)
Alessandra Daniels (3)
Alex Shirley (10)
Andrew Hoefling (3)
Andrew Nurse (30)
Andy Tryba (1)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (37)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Bogdan Litescu (1)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (213)
Chris Paterra (55)
Clint Patterson (108)
Cuong Dang (21)
Daniel Bartholomew (2)
Daniel Mettler (181)
Daniel Valadas (48)
Dave Buckner (2)
David Poindexter (12)
David Rodriguez (3)
Dennis Shiao (1)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (80)
Francisco Perez Andres (17)
Geoff Barlow (12)
George Alatrash (12)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (274)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Kelly Ford (4)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matt Rutledge (2)
Matthias Schlomann (16)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Miguel Gatmaytan (3)
Mike Horton (19)
Mitchel Sellers (40)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Oliver Hine (1)
Patricio F. Salinas (1)
Patrick Ryan (1)
Peter Donker (54)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Sacha Trauwaen (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott Schlesier (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Steven Fisher (1)
Tony Henrich (3)
Torsten Weggen (3)
Tycho de Waard (4)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (40)
Will Strohl (180)
William Severance (5)
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out