Jaa

Comparing two databases (schema and/or data)

  • Artikkeli

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... ):

VSMenuSchemaCompare

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

VSMenuSchemaCompare2

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

VSMenuSchemaCompare3 

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!):

VSDataComparepng

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

VSSchemaCompare

Or closer view of the schema compare:

VSMenuSchemaCompare4

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!):

CRMMyDemoEntity

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

VSNewEntitySchemaChange

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

Comments

  • Anonymous
    February 13, 2008
    PingBack from http://www.biosensorab.org/2008/02/13/comparing-two-databases-schema-andor-data/

  • Anonymous
    February 13, 2008
    What version of VS2008 are you using? I've got the Team Sys for Developers version and I don't see the Schema Compare as an option in my Data menu after I've connected my two databases thru the Server Explorer. Do I have to have CRM loaded? Is this only in VSTS for Database Professionals? Or, is this an Add-In that I haven't loaded? Thanks for your info, Skip Valentine

  • Anonymous
    February 13, 2008
    Hi Skip! I'm running Team Suite :-) And you're right that you need the VSTS Database Edition or Team Suite in order to get this functionality. See http://msdn2.microsoft.com/en-us/vstudio/products/cc149003.aspx for more details and look for the "Data Compare". So you don't need to have any CRM related stuff at you machine... I just used that as an example. You just need correct edition of Visual Studio. J