Comparing two databases (schema and/or data)

Quite often people like to know what's happening under the covers when they do something through user interface. For example they use CRM, SharePoint or some other product through user interface and they would like to know what has happened at the database. Normally my answer is that you don't need to know that because "Don't touch the database rule still applies" but this time I have different answer :-)

I'll shortly explain how can you check that stuff yourself using Visual Studio 2008 and Schema compare and/or Data compare functionalities. But it's important to understand that this kind of approach shouldn't be ever executed against production databases. So you really need to have separate dev environment (and databases) for this kind of testing.

Now we're ready to go. I'll use Microsoft CRM 4.0 in my example. I have created two tenants and named them Demo and DemoEmpty. And that of course means that I have databases Demo_MSCRM and DemoEmpty_MSCRM at my SQL Server. If I now start up my Visual Studio 2008 and connect those two databases to the server explorer and start playing around with this (Data -> Schema Compare -> New Schema Comparison... ):

Then I get this dialog and select those two databases (NOTE : Right hand side is the target database! ):

And when I click the OK-button the Visual Studio starts crawling the two databases and then creates list of differences:

Currently we're not yet interested at the differences since we just want to make the databases equal. So I just pressed Write updates button from the toolbar to make the DemoEmpty_MSCRM same as the Demo_MSCRM (remember DemoEmpty was the target database). Obviously this makes my DemoEmpty database useless but I use it only to track changes at the Demo database.

I also used Data Compare -> New Data Comparison... so that both databases would then have same content (of course you can achieve this same with backup/restore but it's not as fun as this approach!):

Now if I refresh the Schema compare we'll get this view:

Or closer view of the schema compare:

We can easily see that our database schemas are equal since the Status is Equal for the tables and Update Action is set to Skip.

And now we're finally ready to go to the user interface for the tenant Demo and make some changes. I'll type https://crmserver/Demo into my browser and go to the Settings and Customization -> Customize Entities. And I'll just add new custom entity called MyDemo (how original name!) and it makes my entity name new_demo (since I didn't even change the default prefix... and this is just lazyness I know!):

After I have saved my new entity I'm ready to re-run the Schema compare:

And of course the changes we're something that you could expect... two new tables called New_demoBase and New_demoExtensionBase. If you look at the definitions:

New_demoBase:

 1234567891011121314151617181920

 -- ColumnsCREATE TABLE [dbo].[New_demoBase]([New_demoId] [uniqueidentifier] NOT NULL,[CreatedOn] [datetime] NULL,[CreatedBy] [uniqueidentifier] NULL,[ModifiedOn] [datetime] NULL,[ModifiedBy] [uniqueidentifier] NULL,[OwningUser] [uniqueidentifier] NULL,[OwningBusinessUnit] [uniqueidentifier] NULL,[statecode] [int] NOT NULL,[statuscode] [int] NULL,[DeletionStateCode] [int] NULL,[VersionNumber] [timestamp] NULL,[ImportSequenceNumber] [int] NULL,[OverriddenCreatedOn] [datetime] NULL,[TimeZoneRuleVersionNumber] [int] NULL,[UTCConversionTimeZoneCode] [int] NULL) ON [PRIMARY]-- ...

New_demoExtensionBase:

 1234567

 -- ColumnsCREATE TABLE [dbo].[New_demoExtensionBase]([New_demoId] [uniqueidentifier] NOT NULL,[New_name] [nvarchar] (100) COLLATE Latin1_General_CI_AI NULL) ON [PRIMARY]-- ...

You'll understand that actually only those base CRM columns are at the New_demoBase table and all the other (1 in my case since I was lazy and didn't add more fields :-) fields are at the New_demoExtensionBase table. Of course there are other important changes too like the two new views: FilteredNew_demo and New_demo. FilteredNew_demo is view that uses the users rights to retrieve data... so it automatically filters out all the rows that user doesn't have access to (that's why it's called Filtered :-). And the other view just combines the two tables (Base + ExtensionBase) together.

But this was just small example how you could compare databases and see what is happening at your application. Remember to use this compare tool carefully... because you could easily make you target database useless... so it would be wise to create backup before playing around with it.

Anyways... Happy hacking!

J