Overview
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.
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.
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.
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.
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).
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 >".
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.
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)
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.
Conclusion
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.