Loading the player...

Hello. Today we are going to be a looking at a product called IDERA SQL Comparison Toolset. This toolset includes two underlying features. One of them is called the IDERA SQL Schema Comparison Toolset. And the other one is called the IDERA SQL Data Comparison Toolset. And they are both bundled and to the same product offering which we call the IDERA SQL Comparison Toolset. And we are looking at the SQL Schema Comparison tool right this moment. We get into looking at some of the functionality here in just a moment. These two products can be very valuable to your organization when it comes to keeping databases in synchronization. And you may ask “Well why would need to keep databases in sync?” Well, there could be a variety of reasons. Maybe we have changes that are happening on a test environment that need to be published to a production environment. Perhaps there is data records that exist in our production database that need to be synchronized over to a reporting database. Maybe there is a situation where we have a payroll environment that is (you know) where our payroll department is making changes and updating information in a database that not need to be live until the date of payroll where it needs to be posted. And we use a synchronization process to sync from that one database to the next. There are all kinds of different reasons. You might even take a backup archive and bring it online to restore objects instead of having to restore an entire database. So all kinds of different options available through synchronization or different benefits through synchronization. So, what we are looking at right now is the IDERA SQL Comparison Toolset. This tool is very easy to use. And for that matter both of the products, both of the toolsets, as part of the higher level SQL Comparison Toolset are very easy to install. It takes about five minutes to get them up and running.

And so we are starting off with the SQL Schema Comparison Toolset. Both of the products have about a five-step operation from beginning to end for using them. So, within Schema Comparison Toolset, the first thing we will do is add a workspace. And you can see in order what you have to do here on the left-hand side. First, create a workspace. A workspace is basically just your little area that you are going to be performing different projects and working on different projects. And so I have a couple of projects in my environment. Once you have created a workspace, you will add databases. And actually part of the creation of your workspace is to add the databases. It is very simple to do. We just simply select the Add Databases. You can hit the drop-down list here to see all the SQL instances in your environment that are available. Pick on any of them and refresh the database list. And pick and choose whatever databases are relevant to your roles or responsibilities. Like I said, I have already added my instances and databases into my workspace. And you can see them here on the left-hand side. The couple of databases that I am going to be working with today are my Compare databases. SQLCompare_DB1 on the left and SQLCompare_DB2, where I have some explicit differences between both databases. And I have added them into my database analysis and synchronization project called FINANCE DB COMPARISON. Now notice that these different database projects are color-coded differently. That is (you know) one of the features of the product where we can more easily identify certain projects relevant to different organizational units or different activities relevant to our daily responsibilities. In this case, I have color coded it with green and yellow to represent (you know, I guess) money or you know looking at finance information. So, to edit the color schemes, you can hit the little pencil icon. This where you can change the colors scheme. So you can even use gradient colors if you want to if you want to get all fancy with it. I have given the session name for this database analysis and synchronization project the name FINANCE DB COMPARISON. And there is also the option to put in different descriptive information. So, when I click on that database project, it brings up all of the different schema objects (all of the structural components) that make up the database. I have collapsed my table objects, in this case, so you can see different object types. And then by expanding them, you can see all of the underlying objects in each of those different categories. This is the table objects. And so, obviously, we see all of the different tables. And then you can go down the list here. Look for what is alike or what is not alike. I find it is easier to just do my sorting by the status here at the top. Click on the status and then we will bring all of my tables that are not alike or views or store procedures from one database to the next. It will show what is alike, what is not alike, what exists on one side that does not exist on the other. You will have all of that information. When we look at the differences at the bottom, we will see highlighted what is actually different from one side to the next. (You know) But, if it gets a little bit busy here with some of the T-SQL, you can also right-click and toggle lines where you only show the differences. So, there is some of that type of capability to really focus on what it is that you need to see. Also expanding any one of these objects will show us the underlying objects and what is different from one side to the next, per object. (Okay) So, now what we have done here is we have analyzed all of these different tables, views, store procedures, functions. We see what is different permissions. Now, we go through and select what it is what we want to synchronize from one database to the next. And to be clear, at the top, you can see the database one SQLCompare_DB1 and on the right is SQLCompare_DB2 to know which way we are synchronizing. So, to do the synchronization, over here on the right-hand side we have different menu options. One of them is the comparison options as well where you can go in and kind of nit-pick what it is that you wanted to do as part of its comparison. But, at some point, I want to synchronize the changes from one side to the next. And so, what I am going to do is to select the generate script for moving from SQLCompare_DB1 database over to SQLCompare_DB2. When I click on that, it brings up the synchronization script in the window. And if I want to take it a step further I can go ahead and on the right-hand side open this up in Management Studio, and it will bring up Management Studio and load this script in for me. You can also go ahead and save this script out. Or, better yet, here on the right-hand side, we have the option to execute the script which will actually make the changes for us directly. There is also a command line capability that goes along with the SQL Schema Comparison Toolset as well as the Data Comparison Toolset. So that you can setup different types of automation to automatically analyze two databases and automatically synchronize the changes from one database to the next, so you do not have to do this on your own on a manual and regular basis. So, as you can see, this about a five-step process. It is very simple to use this tool.

The other product is called SQL Data Comparison Toolset. So, we will bring that up at this point. So, now we are switching over to the Data Comparison Toolset. And as you can see, it very similar in appearance to the Schema Comparison Toolset that we were working with. It has the same types of functionality basically, that same five-step process from beginning to end. Starting off with creating a workspace where we add databases that are going to be available to be compared as part of these database comparison projects. I have already added my databases, my instances and databases, into my workspace. And, there are listed here on the right-hand or the left-hand side. And then I have created different projects for doing comparison and synchronization. And again, if you want to go in and select new databases and create your own project, select a Compare Databases. And, just pick from the list any databases to compare from one instance to the next. Choose your Comparison Options for what it is that you want to compare within the database with respect to data. And then it is going to perform its comparison after that. So, what we see now is the different projects that I have created for database comparison. So I am going to pick on my reporting server databases from one instance to the next. Because I know that there are going to be a bunch of changes over there. As you can see again, the color status has helped me to identify what projects are relevant to different responsibilities that I have, or different organizational units or departments. (You know) Whatever you want the color status to mean. So let us get ahead and click on Report DB Differences. This brings up all of the different tables where the data records are going to be stored that we can perform a comparison against. It is important that we have indexes to be able to link. And indexes, primary keys you know to link the data from one table to the next table, from one instance and database to the next instance and database. This is, by the way, this database report server is one my 2014 instance. The other report server database is on my local NEW_IDERAPRODDM instance, represented here at the top. You can choose the comparison options here on the right-hand side. And, there are the different options again to compare and synchronize views, memory tables, columns, stored tables, different scripting options, where we can have it build out multiple scripts and basically bite off the synchronization operation in chunks that can be executed in multiple steps as opposed to having one lengthy operation. All right, so let us go ahead and do a data comparison. Up here at the top right we select Compare Data. And, it is going to step through all of the tables. And, I can kind of scroll to the right here to see what the number of equal rows are, what is on the left-hand side that is not on the right-hand side, or what is on the right-hand side that is not on the left-hand side, the number of overall different rows. I will click on the right row header here to bring kind of all of the changes up to the top. And then at the bottom, you will be able to see a little arrow pointing to the right or pointing to the left. I clicked explicitly the right row, so it is showing me here that for this object (this first object), that these are all of the objects that exist on the right-hand side database, on my local instance but do not exist on the left-hand side. And then, if there is anything that does match up, we will be able to see (you know) as far as the data objects within the tables, what links together and what is different from one side to the other with the actual data record difference between one side and the other. And, you can move to the right and see highlighted anything that was different. And, it is also going to change, it will show you the color status here, what is on the left-hand side versus what is on the right-hand side. Again, the color status is up here at the top. The orange on the left, the blue on the right. So, this point we can take the differences from one database and synchronize them to the other. The first thing we will do is we will go through and select anything or deselect that we do not want to synchronize. You can be very selective; you can select all of it if you want to. And so, once you pick the tables that you want to synchronize the data from one side to the next, then on the right-hand side we can generate the script. And I am going to, from my standpoint, I am just going to point the synchronization from the left-hand side to the right. And, we will go ahead and generate the script. Just like within Schema Comparison Toolset, we have the T-SQL that pops up here that we can review. We need to just see all of the operations that are going to take place to actually move that data difference from one side to the next, in my case from the left to the right. On the right-hand side, you have some different options available to save the script out, to execute the script, view script options. Again, this is what we saw earlier through the options screen. Or, open the script in Management Studio if you want. Or, just simply (you know) execute the script to push that change out at this point.

Like I said, both tools are very easy to use, about a five-step operation. They both come with command line operations, so you can have it as part of your own automation, have it go ahead and analyze and synchronize differences on an ongoing basis without manual intervention. There is also an option through the command line capability to take snapshots of these databases so that you can do future comparisons of databases the way they stood at a point in time. So, with that, that wraps up the demonstration of the IDERA SQL Comparison Toolset. Hopefully, this has been useful. If there are any questions that come up as you start using it, feel free to get in touch with your account manager. I tried to give you just a high-level summary view of the features and functionality. But we can also dive in a little bit more in depth if we need to. So, I appreciate you watching the video. Thank you.

SQL Comparison Toolset Overview

IDERA SQL Comparison Toolset provides a quick and easy way for developers and DBAs to compare SQL Server database schemas and propagate schema changes from one environment to another. Data compare functionality allows you to compare and synchronize tables across servers, databases and versions when performing data migrations, copying databases or auditing data. Learn More →

Start a FREE Trial of SQL Comparison Toolset
Share This
Contact IDERA:
Call toll-free in North America
1-877-464-3372
Call International
+1-713-523-4433