Hello, everyone. My name is Gary Jerep. I am a solutions consultant for Quest Software. Toad Solutions provide many capabilities that meet the needs of DBAs, developers, database professionals, including things like administration and management of database objects, code debugging, performance tuning, and way more. But in this video today, I'll be giving a high level overview of Toad for SQL Server Schema Compare and Sync feature, which is found in the professional or higher editions of Toad for SQL Server.
So I'm showing my Toad for SQL Server here. Note that I've just logged into my Contoso retail data warehouse. And note also, that I have both a production and a development version of these two databases. I'd like to know what the differences are and maybe sync up development to production or maybe production with the changes on the dev system.
The way I can do that is simply to right-click here. You'll see a Schema Compare option. Because I've control-clicked or highlighted both of these databases, the wizard that comes up here will know the context. So it's set my production database. And my target database is already identified.
Now, I can add other things here too, by the way. So if I highlight the way I'm connected to, I can actually add other things like, by the way, a snapshot that I took, maybe a few months ago, of my development database. So notice that I can compare a source with multiple targets. Today, I'm just going to simply compare my production version with my dev version but just wanted to let you know that we can add more targets for your comparison purposes.
If I say, Next through the wizard, this is an important panel too within the wizard if you don't need to select and compare everything that is every object type and all the attributes of those objects, you can go ahead and display or select just the object types that you want, and then the actual objects within those types get displayed here, as well. So you can pick and choose what you want for ultimate flexibility. Today, I'm simply going to not add any objects, and when I do that, you can see the message here that says I'm going to compare everything within the two databases.
So moving ahead with the wizard, yes, there are some options that control how you want to do the compare. Do you want to ignore fill factors, do you want to ignore column orders, do you want to ignore white spaces and permissions and things like statistics select those options according to your purposes, move through the wizard.
Now, what the wizard just did there was looked at the metadata for all my objects on the two SQL server databases and gives me a nice comparison. So this is a great comparison. We do have objects that exist on the target and not on the source. There are some that exist on the source and not in the target. Got a lot of objects that are the same, exactly the same defined the same, and then we've got quite a few objects that are different in both.
Now, that's just a summary, by the way. When I go ahead and finish, you'll actually have a Doc tab that comes up within Toad for SQL Server that represents the schema comparison. Now, by default, I believe that there is no grouping. So you'll see a list of the objects for both source and target and an indicator whether that object is in the source only, maybe the target only, or there are differences, for example, differences here in the fact table.
There is a nice filter option here, and I use this quite a bit. So if you're only interested in the objects that are different, pick and choose the object types and/or the difference types or the comparison status types that are important to you. I do want to note that group by up there, for me, it's really, really useful to have the comparison status first and then the type.
That means that whatever I choose here, like for example, if I wanted to choose everything, I get a high-level overview of what's different, maybe what's in the source not in the target not in the target, in the target not in the source. But that can open up that specific status comparison and then see what types of objects are different.
For example, there are differences in functions. OK, that's not an important one. But in terms of things like tables, there are five tables that are different between my source and target. What are those differences? You'll see them visually here.
What's really nice is that these differences will be color coded. So rows in blue will indicate things that have changed on both the source and target. If you see any green rows highlighted here, those are rows that are appearing only in the source, not in the target. And any red rows here will be changes or rows that are in the target and not in the source.
So visually speaking, you're going to see a lot of stuff here that will help you understand what the differences are between the source and the target databases. One of the thing here, though, you might need different filtering approaches. When you look at the different differences, please note that you can build or create different filter profiles.
So right now, I've created one that says minimal objects. By default, we're going to be showing you everything all differences, source-only objects, target-only objects, objects of all types, et cetera. And you'll be able to see that here in the objects list, all right. And then, of course, the changes between any of the objects that you're selecting.
Most importantly, here, not only seeing the changes and understanding what those differences are, yes, you can highlight here the script, copy it, and maybe even run it in the editor, but even more important is, how do I make the source look like the target or the target look like the source?
I'm hitting a button that's going to initiate another wizard that says, hey, let me help you synchronize one side to the other. Do you want to make the target look like the source, source look like the target? You choose. We know the database context already because of the comparison you just did. So we'll move through the wizard.
Again, for synchronization purposes, there's lots of options here that you can choose. If you want to ignore collation, or permissions, et cetera you can choose to do those, just swing through Next in terms of the synchronization wizard will give you a quick summary of what we've selected in terms of the differences between the two databases, and then moving through the wizard, you get the option to run the script immediately.
I can tell you database professionals typically don't like to do that. They like to see the script first. And so we'll open this script up in the Toad Editor so we can see what it looks like. And there you go. Literally, it could be thousands and thousands of lines of code, maybe even tens of thousands of lines of code.
By the way, when you have this length of script in any Toad product, Toad products have what we call a Script Map. You can bring that out, even pin it would be useful. Why is this useful? Because if I go ahead and refresh the Script Map, it forms a little index that helps me understand my script a lot better.
So if I wanted to see all the alters in my script, for example, or anything having to do with any my fact tables within this script, I can go to here's my fact sales small one of the alter tables for altering a column. A series of columns there that takes me straight to that portion of the script, all right.
So there's a lot more things that we can do here. We can automate the comparison process. We can even automate the comparison of snapshots and live databases, but that's pretty much it. A nice clean way to see object attribute differences between a source database and multiple targets and generate the script that syncs up one database to match the other.
So you'll find this and other useful videos on toadworld.com. It's a pleasure to speak with you today, and I hope you have a great day and the rest of the week. Take care.