articles >> performance tuning >> Schema Comparison with Visual Studio Team Edition ...

Schema Comparison with Visual Studio Team Edition for Database Professionals

By : Dinesh Priyankara Feb 06, 2008

Page 2 / 2

• The final screen allows us to set build and deploy settings. Since this project is just for comparing two schemas, leave settings with defaults without setting a "Target database name". Click on "Finish" to create the database project.

• Once the database project is created, it shows the summary the work done. Click on "Finish" again to complete the wizard.

Comparing two databases schemas and examining the differences

Once the project is setup, it can be used for maintaining the schema of the database and applying changes to any environment with or without examining the differences. Usually every change will be done through the project and we can later deploy changes to relevant environments. The comparison and deployment (changes) facilities are available under the "Data" menu with an item called "New Schema Compare".

When comparing the schema in the project with another database, the project itself can be considered as either a source or target. If you need to get updates from another database and get them applied to the project created, the project should be treated as the target. If you want apply the changes you have made to the schema in the project to the different database (usually the case), then the project becomes the source. Though we have not made any changes to the project yet, let's compare the project with "AdventureWorks" database and see the procedure of comparing.

• Click on the "Data -> Schema compare -> New schema comparison" menu item. It opens the "New Schema Comparison" window.

 

Make sure that "Project" radio button is selected under "Source Schema" and "Database" is selected under "Target Schema".

• Select the "AdevntureWorks" database from the "Database" drop down if it is listed. If not, click on "New Connection..." and make a connection to the "AdventureWorks" database and select. Click on to continue.

• This starts the comparison operation. Finally, it will show the result as below;

 

The first section of the result window shows all the available items in the both source and the target, categorized by SQL server objects. First column shows the status of the object . Status can be seen are;
Equal:  tells that the object has no differences between the source and target.
Different: tells that the object has some differences between the source and the target (eg: a data type of the column)
New: tells that the object is not available in the target.
Missing: tells that object is not available in the source.

Second column shows the name of the object of the "Source" and third column shows the action need to be performed against the "Target". There are four types of actions can be performed against the target;
Skip: instructs not to do anything related to the object on the target.
Create: instructs to create the object in the target.
Update: instructs to update the object in the target.
Drop: instructs to drop the object in the target.

The forth column represents the object in the "Target".

The latter part shows the definition of the selected object. It allows us to easily find the differences between the source and the target.

In addition to these screen, we can view the "update script" too. It is the script used for updating the target. Since the comparison did not return anything to update, there is nothing much in this script now but it is worth to have a look on it because it generates different types of scripts for different types of implementation of changes. Find "Data -> Schema Compare -> Show Schema Update Script" menu and click it. It opens the "schema update script".  Some of the interesting things you can find are;

• It creates #tmpErrors table to track errors and roll back transactions.

• It disables DDL triggers.
• It deletes the "description" of the database from the database by using sp_dropextenedproperty. I am not sure the reason for this but better comment this line or update the "MS_Description" property.

This script can be used to directly update the target database. The "Write Updates" button in the "Schema Compare" toolbar does it too. If you do not want to directly update the target but export the script onto editor, you can use "Export to Editor" button. Note that the comparison has no changes discovered, hence these two buttons are disabled.

Simple modification and "update script"
Let's do a simple few modifications to the schema and check the result of comparison. Let's add a new column to "Person.Contact" table and change the stored procedure "dbo.uspGetBillOfMeterials".  Follow the steps given below;

• Go to "Solution Explorer" and expand "Schema Objects -> Tables". Open "Person.Contact.table.sql" script.

• Add a new column called "New Column" to the table like below;

...
[PasswordSalt] [varchar] (10) NOT NULL,
[AdditionalContactInfo] [xml] (CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
[rowguid] [uniqueidentifier] NOT NULL ROWGUIDCOL,
[ModifiedDate] [datetime] NOT NULL,
[NewColumn] int NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
...

• Save the script and close. Expand "Stored Procedures" and "dbo.uspGetBillOfMaterials.proc.sql" stored procedure.

• Add a comment like below.

CREATE PROCEDURE [dbo].[uspGetBillOfMaterials] @StartProductID [int], @CheckDate [datetime]
AS
BEGIN

SET NOCOUNT ON;

-- Test comment

-- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1
-- components of a level 0 assembly, all level 2 components of a level 1 assembly)
-- The CheckDate eliminates any components that are no longer used in the product on this date.
WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
AS (

...

• Save the script and close it.

Now we have done two changes. Let's compare the project with "AdventureWorks" database and see how the differences going to be shown.

• Follow the same steps we did for testing the schema comparison.

• Once the result is shown, use "Filter" button to filter out "Non Skip Objects". Now you should see only two objects in the first section.

• Click on the "Update" under "Update Action" column for the row "Person.Contact". A drop-down is displayed with two items; Update and Skip. The default "Update" is selected but if you do not want to send this change to the target, you can simply select "Skip".
• Go through "Object Definition" while the "Person.Contact" row is selected and examine the detected differences. Text with green color background shows "New Texts", text with blue color background shows "Different texts" and text with pink color background shows "Missing texts".
• Note that buttons "Write Updates", "Export to Editor" and "Show Schema Update Script" icon are now enabled. Click on "Export to Editor" button to open the "update script". Here are some of the key things available with the script;
  • ALTER TABLE statement for the "Person.Contact" table.
  • sp_refreshview is added to all views that have references to changed objects. This refreshes the metadata of specified view.
  • ALTER PROCEDURE statement for "dbo.uspGetBillOfMaterials" stored procedure
  • Did you notice the way of transactions are handled? If something happens in the middle of the script, it rolls back the problematic part but continues with the rest. In addition to that, it inserts a record to #tmpErrors table. When it commits, it checks the record existence in the #tmpErrors table before committing and rolls back at record existence.
• Do not run the script or click "Write Updates" button. We are not going to update the target database. If you want to send the changes to the target, backup the target database first and then update it.
• Close the "Schema Comparison" window.

Different "update script" for same modification

Data Dude generates the script according to the changes that have been implemented. Remember, we added a column to the table as the last column and it has been interpreted in the "update script" with "ALTER TABLE" statement. But if the same column is added in between two existing columns, the "update script" is bit different. Let's see it.

• If you updated the target database with previous change, you need to bring the target to its original state by restoring the backup.
• Let's bring the project to original state too. One way is, remove the changes we did to the project manually. Or else, you can use the "Schema Compare" too. If you use it, make sure that you select the "AdventureWorks" database as source schema and project as the target.
• Once the project is brought to its original state, open the "Person.Contact" table script and add a column called "NewColumn" with the type of "int" in between "LastName" and "Suffix" columns. Save the script.
• Now follow the same steps to do the comparison. Once the result is shown, click on "Export to Editor" and examine the code. Here are few things you will notice;
  • It drops and recreates all the foreign keys in tables that have referenced "Person.Contact" table.
  • It drops all other constraints of the "Person.Contact" table and recreates, including primary keys, defaults.
  • It drops and recreates all the indexes in the "Person.Contact" table.
  • It creates a table called "Person.tmp_xx_ms_Contact" table and inserts all records from "Person.Contact" into new table.
  • Once data is transferred to the new table, it drops the original "Person.Contact" table and renames the new table as "Person.Contact".

This is how the "update script" is generated if the column is introduced between an existing columns. You may see some performance problems when updating the target, if the table contains lot of records and heavily indexed. If the table is a large table, avoid adding columns in between existing columns.

Not only these simple changes, Data Dude addresses almost all object changes in the SQL Server database. Since it supports for versioning too, this is the most relevant tool for DBAs for maintaining the schema of databases in multiple environments. If you have not started using it, download the latest from here. You can download power tools for it from here.

The feature "Schema Comparison" is a rich tool for comparing and generating an "update script" that comes with Data Dude. I hope this article provides a good view of "Schema Comparison" and the reasons for using it. Data Dude not only has the schema comparison feature it also has a data comparison feature as well. We will explore this feature in my next article. Here are some of the links that you can use to learn more about Visual Studio Team Edition for Database Professionals.

• Gert's Data Dude Blog:

• http://blogs.msdn.com/gertd/
• Visual Studio Team Edition for DB Pros: http://blogs.msdn.com/camerons/
• Article I wrote about "Test Data Generation": http://sqlserveruniverse.com/content/ADMN0100212262007DataGenerationwithDataDude.aspx