Data compare and sync in Toad for SQL Server

Hello, my name is Robert Pound, and I am a Quest technologist. This video will cover data compare and sync for SQL Server. For instance, updating the production environment from a test environment. For complex ETL tooling such as Quest Shareplex, or workload replication tools like Quest Benchmark Factory, you can visit www.quest.com for more information. So on screen, I have Toad for SQL server opened and ready to be used. You have options when doing compare and sync, as you do with several different components of Toad. So looking at the ribbon bar, we can see at the top, there is a compare tab. And if we drop this down, you have options. Comparing data, schemas, or the servers themself. In this instance, we will be comparing data. If I clicked here, we could compare several tables in specific databases. But for this demo, I'm simply going to right click on a table that I specifically want to look at. And then choose data compare. I will be asked for the name of the comparison, new demo one. And a description. And then whether we are looking at the current database table as a target or a source. In this case, we're looking at the test table. So I'm going to use this as the source, because potentially, we're updating the production environment. Another reason you may want to save this comparison and name it, this can be used in the future for automation. So if you want to do this comparison on a scheduled basis, you can use this new demo one to go ahead and facilitate that. So now that we've selected our source table, we need to select our target table. So I'm going to choose the database. And click next. So at this point, it has found the table in the source and the target, and it has aligned the columns. If your tables do not match in the source on the target, you may have to manually select the columns that you specifically want to compare. You can also deselect some of the columns. For instance, if you're not interested in comparing two or three of these columns, you can do that. This is a fairly simple and straightforward demonstration. So I'm going to go ahead and compare all the columns. And again, if I was comparing multiple tables, they would show up here, and I could select them individually. I will simply choose Next. At this point, we have the option of ignoring certain things inside the tables as far as blobs, ignoring white space, et cetera. And these four check boxes at the bottom have to do with the data that's presented in the comparison itself. Again, we want to look at everything in this demo. So I'm going to go ahead and leave all of them checked. At this point, we will be presented with a summary of the comparison. So in this case, we had one item that was source only, three that were different, and seven that were equal. And none that were in the target only. So once I click Finish, we're presented with the actual data itself. So you can see here at the top of the list, if there were multiple tables, they would be presented here. And you can actually filter the tables specifically for only showing those that are equal, different, et cetera. Since we only have one table, that's not relevant. And here in the pane below, where you're actually seeing the data itself, you can again show only the rows that are different, only the rows that are equal, et cetera, or all of them. And you can also change the way in which the data is presented. Currently, it is presented column next to column, so that you can see which data has changed. You can also do a table by table look. This may be useful if you're looking at a smaller table. But again, it's up to you, how you want to present the data. So now that we actually have the data compared and we can see what data has been updated, the next logical thing is to actually sync the data. So I will come up to the top, I will use the synchronization wizard. And again, we choose the direction of the sync itself. So potentially, I could have said make the source like the target. But in this case, we want to make the target like the source. We want to update the target, which is the production environment. And we want to make it like the source, which is the test environment. Because presumably, we've made changes that were successful in the front end code, and we can name the script. I'm just going to leave this default for now. And then we're presented with any constraints that we want to put on the script that we're about to run, as far as disabling triggers, dropping foreign keys, et cetera. Again, I'm going to leave all this blank, because I'm simply going to get to the script itself. The last option is just whether you want it to be run immediately. You can have the option to delete the script once it's opened, if you don't want to store it on your hard drive. Take no action, and just save the script itself. I'm going to go ahead and open it up in the editor, just so you can see the final product. And here it is. This is the sync script for that particular table. I hope this was a helpful video. You can visit www.quest.com/toad for more information. Thank you.