Introduction
In my earlier article, we have discussed about the SQL Server schema comparison tool in Visual Studio 2010 SQLCompare.aspx. Today in this article, I’m going to explain yet another new tool in Visual Studio 2010 which is nothing but the DATA COMPARISON tool. This tool is of extreme use for the developers to compare data in 2 tables in different environments (in real time, say a reference table data should be compared from Development to Production).
Many third party software are available in the market which serve the same purpose for comparing the data between the environments, but with Visual Studio, Microsoft has given this functionality inbuilt as a comparison tool so that Visual Studio emerges as a bundled product which serves most of the development tasks and day to day activities which the developers and DBAs required to do. Microsoft Database server version for Database administrators and developers has provided with many features like database development, version controlling, unit testing, code analysis, code maintenance. etc…
Availability
Microsoft has provided an option in Visual Studio 2010 to compare the data for SQL Server 2005 and 2008 versions. Since SQL Server 2000 support is not provided by Microsoft, this version is not included with VS 2010. So both SQL Schema Comparison and Data Comparison are supported for SQL Server 2005 and 2008 versions. These tools are available for the licenses with Visual Studio Premium and Visual Studio Ultimate versions and not available for the Visual Studio Professional version.
Let’s jump into the demonstration on how to use the Visual Studio 2010 Data Compare tool; I’m taking SQL Server database data comparison. Let’s see the step by step process on how to use this tool.
Step by Step Process
Step 1 – Open Visual Studio 2010 by moving to Start > Program > Microsoft Visual Studio 2010 > Microsoft Visual Studio 2010.
Step 2 – Once Visual Studio is opened, go to Data --> Data Compare --> New Data Comparison…
Step 3 – You will see a dialog box which requests for the source and destination db and the properties to connect to those db servers as below:
Step 4 – Choose the source and the destination DB servers which you want to compare with. If you can see the above image, there are the options available for the comparison like Different Records, Only in Source, Only in Target, Identical Records. Based on the needs, these options can be selected to make the comparison of the data. Once the options are selected, click on the FINISH button, it will open a new screen.
Step 5 – The new window will show the list of tables and views in the Source/destination servers and the list of columns for each table. You have given the option to select the columns which need to be compared. The main point to note down here is the tables to be compared need to have a Primary or Unique Key by which the tool will make the comparison. So select the list of tables and the constraints as per the requirement as shown in the below figure and click on FINISH button.
Step 6 – Once we click the FINISH button, the tool will start doing the comparison based on the selection we made and gets the result in a new window as shown in the below image. The window has few sections which gives the user to make analysis of the data which are compared. The options which we gave at step 3 are shown as output here. The window will have the Count of data difference in both the source and the destination, Count of data difference in source only, Count of data difference in destination only and finally the identical records in both the servers.
Step 7 – In the same window, we have some useful information, and guess what is the information? Yes, the tool gives what are the differences in the data stored as per the selection we made in step 3 as shown in the below image:
Step 8 – Finally in the same window, we have some more sets of information like the tool itself generates the insert and the update scripts to sync between the databases as shown in the below image:
Conclusion
This tool is really going to be more productive for the day to day activities of developers and improves the productivity. Thanks to Microsoft for incorporating these tools into Visual Studio 2010.
History
- 17th January, 2011: Initial post
