SQL Server Performance

  • Home
  • Articles
  • Forums
  • Tips
  • Training
  • FAQ's
  • Blogs
  • Software
  • Books
  • About Us
RSS Feeds
Sign in | Join


Article Topics

All Articles
Performance Tuning
Audit
Business Intelligence
Clustering
Reporting Services
Developer
General DBA
ASP.NET / ADO.NET

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...
Latest Articles

Working with Windows Communication Foundation (WCF)
Transfer Logins Task and Transfer Database Task in SSIS
Practical Database Change Management (Part 2)
Practical Database Change Management (Part 1)

More     
 
Latest FAQ's

ALTER TABLE SWITCH statement failed because column '%.*ls' has data type ...
ALTER TABLE SWITCH statement failed because column '%.*ls' has data type ...
ALTER TABLE SWITCH statement failed. There is no identical index in ...
'%ls' statement failed because the expression identifying partition number for the ...

More     
   
Latest Software Reviews

Spotlight on ApexSQL Diff - Server-based database comparison tool ...
Spotlight on ApexSQL Data Diff - Server-based database comparison tool ...
Spotlight on ApexSQL Doc 2008
ApexSQL Enforce

More     

articles >> performance tuning >> Data Comparison with Data Dude

Data Comparison with Data Dude

By : Dinesh Priyankara
Mar 05, 2008

I have already written two articles regarding Data Dude (aka Visual Studio Team Edition for Database Professionals); one published as Schema Comparison with Data Dude and other published as Test Data Generation with Data Dude. This is my third article regarding Data Dude, and in this article I will illustrate the way of doing "Data Comparison" with Data Dude. Since both my previous articles provided an introduction to Data Dude, I decided not to add the same here. Let's see what "Data Compare" is.

Data Compare allows us to make a comparison of data between two databases and update the target. Just like the "Schema Compare" operation, this requires a "source" and "target" too. On comparison, it allows us to see the differences and update the "target" as we want. We will perfrom a simple comparison and see how it can be used and how it is useful for us.

The first step is, creating a database project. And we need two databases with the same schema for comparing data. Since I have covered the initial steps for creating the project model with my previous articles, I am not going to show them here again. Simply follow the steps below;

  • Open "SQL Server Business Intelligence Development Studio" and create a "SQL Server 2005" database project. Since we do not want to load a schema in to the project, do not use the "SQL Server 2005 wizard".
  • Let's use ther "AdventureWorks" database as the source for the comparison. As we need to have a target database with the same schema, let's duplicate the "AdventureWorks" database. So open SQL Server Management Studio.
  • Backup the "AdventureWorks" database and restore the database as "AdventureWorksTemp".
  • Now go back to the project in BIDS. Though there are no differences between data in the two databases, we will see a way of performing the data comparison between the two databases. Click on "Data -> Data Compare -> New Data Comparison" menu item.
  • It opens the "New Data Comparison" window as below.

 

We need to set the source as the "AdventureWorks" database. If the drop-down contains the connection for the database, select it, or else make a new connection by clicking on the "New Connection" button and select it.

  • Select the target as "AdventureWorksTemp". Again, if it is not in the drop-down, create and select.
  • There are four Data Compare options in the screen;
    • Different Records: This results the records available in both databases that have differences. Records will be updated in the target database.
    • Only in Source: This results the records only in the source database. Records will be inserted to the target.
    • Only in Target: This results the records only in the target database. Records will be deleted from the target.
    • Identical Records: This results similar records in source and  target. No action will be performed against target for these records.
  • Select all checkboxes. Click on "Next" to continue.
  • It opens a window like below;

This allows us to select the objects we want to do the comparison of data on. If you want to exclude any object, expand "Tables" and de-select items you want to exclude. Click on the "Finish" button to start the operation.


    Next Page>>    








Home | Peformance Articles | Audit Articles | Business Intelligence Articles | Clustering Articles | Developer Articles | Reporting Services Articles | DBA Articles | ASP.NET / ADO.NET Articles | SQL Server Training Videos | DBA FAQ's | Developer Peformance FAQ's | DBA Peformance FAQ's | Developer FAQ's | Clustering FAQ's | Error Messages | Audit Tool Reviews | Backup Tool Reviews | Coding Tool Reviews | Compare Tool Reviews | Documentation Tool Reviews | Design Tool Reviews | Monitoring Tool Reviews | Log Tool Reviews | Reporting Tool Reviews | Clustering Tool Reviews | Security Tool Reviews | Change Management Tool Reviews | Remote Access Tool Reviews | Book Reviews | Security Tool Reviews | QDPMA Performance Tuning | ADO.NET / ASP.NET | Administration | Analysis/OLAP Services | Application Development | Configuration | Components | ETL | Hardware | High Availability | Hints | Index | Misc | Operating Systems | Performance Tuning | Replication | T-SQL | Views


              © 1999-2008 by T10 Media. All rights reserved