Compare SQL Server Schemas

Compare SQL Server Schemas in Oracle and SQL Server

We are very excited to discuss how Aqua Data Studio’s schema Compare feature finds differences in DDL statements between database objects and two schemas and allows users to synchronize the source schema to the target schema for various databases. 

Aqua Data Studio

I’m sharing my full screen and we have a number of different areas to go through today. Here’s an introduction slide that I’ll use as a bit of a guide. There’s probably an assumption of some familiarity with Aqua Data Studio menus. We’ll dive in and move forward as though there’s some familiarity with the menus. There are other videos on introductions to Aqua. There’s areas we’ll point out for catching up on just the general interface. Here maybe just to back up and introduce the main Tools menu and the Compare drill downs. This is how that list has been built. There are certain advanced navigation features and shortcuts and benefits that will show in different ways of invoking these primary menus. Just to bring back the main slide, again, the Tools menu Compare Tools Drill Down has two wizard interfaces at the start, the Schema Compare and Schema Synchronize.

Those are the most robust areas that step you through a left and right hand side or a Source and target, and then have deployment capabilities, reporting capabilities. We’ll step through examples there and then the subset of menus has a lot of ad hoc Compare features that can be very beneficial. This quack chat will cover Compares and Diffs, and then the Synchronization capabilities. Really some flexibility in terms of how you like to see differences, your change management methodology, and then even stepping into some of the relationship diagramming that Aqua has included. Rather than being in a separate tool, aqua has easy diagramming of tables and views and constraints and indexes, and those have Compare features as well. You can compare models, see what’s different, and then choose the synchronized spec and the Schema synchronized wizard. So we’ll introduce these different areas. Some of these are invocable from either a right-click menu or shift-click Control.

Click here and there. So those are shortcuts. These are very popular, impressive slick features that really make Aqua very popular in this space from traditional case Tools to modeling tools for change management. There’s areas that we’re all familiar with over the years and any help in this area really can be beneficial and time saving and help reduce errors and improve performance of your application.

Probably helpful in terms of the range of areas we’ll see and then as you dive in and use OC on a regular basis, these features really become easy to use and you can see how users have driven the navigation of the product and where these features have been added. So that’s what I’m leading to here. Some of these last highlighted ways of invoking these wizards just by a shift click or control click in the navigation tree saves you building the left or right hand side.

User Interface

We’ll go over the control click for object compare the control click for Schema compare in the navigation tree and then some of the hidden right click menus in the query analyzer or the SQL editor where you can invoke a data compare or Results Compare or a side by side file compare just from the right click menus. Okay, so that’s the main agenda here. Maybe I’ll revisit this just to make sure we cover all of these areas but you can see in terms of the interface of Aqua Data Studio or in case you are brand new to Aqua Data Studio, you have a workspace as you might expect in a database IDE. You have pop outs on the left or right hand side and then a workspace here in the middle and so you have Tabs where you can work on your Schema compares and migration scripts and SQL scripts and look at differents we’ll introduce how to invoke these different areas but just maybe starting with a clean layout here.

Navigation

On the left hand side is where I have my database navigation tree. You can see the ability to pin or dock these pop out windows, move them to the left or right hand side. I have the traditional left hand side display for the navigation tree for the different databases that I support. On the right hand side, I have my network or file system navigation. You have the ability to group your different Aqua Data Studio files or your own scripts or however you like to work, whether you use a version control systems and have Aqua talking to those version control systems or you map network drives. If I just click on the Mount directory here, you can map different drives and perhaps collaborate with these Aqua Data Studio or sequel scripts or data sets to other users. Very flexible in terms of setting up your user interface in the way that’s comfortable for you.

Here in the middle section are the tabs or the different areas that Aqua is strong at and you can float these or undock these if you just right click on a tab. We’ll introduce some of these right click menus. Specifically here you can see there’s an example of the Compare that’s been hidden in one of the tab right clicks. We’ll introduce driving from the Compare menu. Aqua is almost 20 years old now, so it’s really been driven by end users over the years. And this list of tools has grown. Really, it warrants its own primary menu, but with the loyalty of our users, there’s a balance of not moving things around too much and making the interface vary too much. This list has grown, but really a lot of powerful drill downs. Here where there you see the two wizard interfaces, schema Compare, Schema Synchronize, and those look the same.

There’s an additional ability to generate the migration scripts with the Schema Synchronize. There are certain platform specific areas that we’ll point out where the Schema Compare can be across different platforms. Schema Synchronizes the same database vendor platform. So you can synchronize Oracle to Oracle. You can synchronize SQL Server to SQL Server. Okay, so we’ll introduce these areas and then probably circle back and maybe go through some of these in different ways that would address some of the different use cases. Whether you’re a developer and you’re migrating PL SQL code or T SQL or your DBA, and you have to do comparison dips and post reports and PostDr diagrams and then show people differences in a documentation fashion or keep track of these migration scripts and the sequel generation capabilities. These are all ways you can use Aqua Data Studio very easily and in a flexible way that really help save you time and justify the tool even if you have other methodologies in place.

Databases

Okay, so here on the left-hand side are the databases that we’ll be working with today. If I just expand or introduce some of these areas, these are the navigation folders that I’ve created. You can connect to these different databases under the Server Register server, and so there’s different videos to introduce those areas for you. You can create folders and group your database connections in different ways. The most common databases that I’ve been working on recently with customers are Oracle, SQL Server, and then Snowflake is obviously a data warehouse that’s growing in user base, of course my SQL. I have these grouped together, but I have flexibility to group any of these connections in ways that make sense. If you’re a DBA and you are responsible for SQL Server and Oracle, you can clone these connections and create different color coding to keep track of where you are.

You can see here I have some variation in color coding in terms of Oracle, SQL Server, and so those are ways that help in terms of change management and comparisons and diffs. Those are some bells and whistles that you might want to take advantage of. For today’s demonstration, let’s drill in here to a couple of these areas. If I drill into Oracle, I see my schema Storage Management Security when I drill into SQL Server, I see databases, snapshots Management Security so you’ll see some variations in the navigation tree just based on the platform for MySQL I see database Management security. Here if I drill in Oracle schema or drill into my schema or schemas, I have some filters set up. That’s a benefit or maybe an area to show. Here if I just go into Server Properties, the Filter tab, this might be helpful in terms of customizing these areas that you need to do comparisons or synchronizations with.

Schemas

You can customize your view of your world. Here I can see just a few of the schemes that I’m focusing on for today’s demonstration. I have some schemas in Oracle that will perform some comparisons and synchronizations with. There’s the filter tab within that server. Properties for SQL Server. Here you can see I drove my databases. Here’s where I have filters on my databases and SQL Server, right? Depending on the platform, you have that capability to customize your view of your world. Here I have a couple of versions of the healthcare database that we’ll use in the SQL Server comparatives and synchronization. Just to introduce my environment here and the navigation tree. There’s lots of right click benefits as you navigate in Aqua Data Studio. Those areas that we’ll introduce here today. Here if I right click on a connection, or if I drill into a connection in my objects, you have lots of right click capabilities as you navigate the object trait.

Some of the hidden areas that we’ll talk about, I mentioned in the PowerPoint just to maybe start here, if I control click on two different objects and then right click notice some of the hidden compare features are invoked. Now I’m selecting multiple objects and right clicking and there’s Compare Scheme, compare Schema Synchronize. Okay, so just a little introduction there to some shortcuts for invoking these. Let’s dive in and start introducing these menus and then just show some different ways of using the tool in ways that would make sense for you. 

Schema Compare

Okay, so the Schema Compare and Schema Synchronize are similar look and feel. If I just start with the Schema Compare window, you’ll see it has this left and right hand side. Choose your source, choose your target. Here if I drill into Choose Server, this would be a more manual way to drill in here.

I could drill in here to say my SQL Server database is on my left hand side or choose my source. Maybe I’ll start with a comparison of a test environment. We’ve made some changes. I want to compare that to my production or development box that I’d like to deploy. Thischange to right and so choose your left to right hand side, but there’s some ways of switching that. You see the arrow, the double arrows here to flip flop those. Let me just choose the source and target. Again this is a little more manual, just clicking in the tree but we’ll show some shortcuts there. All right, so here’s the familiar schema compared to the look and feel of the user interface. Here I can select my objects and then kick off the comparison, right? Here if I say choose all my objects, you’ll see those listed on the lower portion of the screen and then some other helpful wizards here.

Here I could say uncheck all the objects. Maybe I just want to choose tables and views and maybe prox right? You can isolate certain objects and then you see the left or right hand side. Here are some other settings that would be helpful in terms of what you’re documenting. Are you comparing storage? Some of the scripting comparisons, what you’re ignoring in white spaces and cases. Just introducing some of these tabs or selection boxes and then some other areas here if you were going in a different direction you could just flip flop those very easily. Swap Source and Target servers and then just select your objects again and it would map those for you. Okay, so different ways of choosing your source and target. If I was to click Compare, this would kick off this Compare and provide a summary. Here maybe if I back up just to show a previous scheme of comparison, it would have a side by side of what’s on the source, what’s on the target and then you’d be able to dive into this look and feel.

Compare SQL Server Schemas

It was just showing this interface here to then introduce the difference between the schema synchronized window. Okay, so here’s the results set of just a comparison, but here is the result set of the schema synchronize where you have this additional column for sync and then this icon is enabled as well. Okay, so why don’t I kick off that example in SQL Server where I could rerun this or introduce the shortcut menus as well. Maybe I’ll do that before I kick off this comparison. Here is the schema comparison where I had to choose the left and right hand side which is manual in the drill down. If I back up to the navigation tree and here on SQL Server here if I just control click and then right click notice the shortcuts are enabled for me. This saves time here from the navigation tree. Just shift clicking or control clicking and then the schema compare boom selects those for you and then also the order that you select influences the source and target, right?

So let me close this window again. Here, if I start with this test, there’s my left hand side and then control click, right click here’s Schema Compare, Schema Synchronize. Notice it has that as well. Here if I want to change the direction, I could easily select that. The order was influenced by how I selected here, right? Right click Schema Synchronize, you see that’s the order that it builds as well. Can easily flip flop that or the order you control click would influence that. Why don’t I select all the objects, kick this off, and then we’ll keep moving forward as that executes. We’ll show some other bells and whistles here. I did just want to introduce the delta between the Schema Compare is the Schema Compare can be any platform to any platform. The Schema Synchronize is the same platform, same vendor platform. You have this additional column or field for the sync script and the synchronized scheme.

Those are the two differences in the wizards, but otherwise very similar. We’ll probably kick this off more than once within this demonstration. If I click on Compare here and let this chug away, this will hit the catalog or the dictionary on the source and target. You can see strong performance here, hundreds of objects, and it has these different stages and status. A couple of other things I’ll mention here. You have the ability to allocate more memory to Aqua. If that’s a benefit, a lot of our client machines have plenty of memory that will speed up some of the Schema Compare performance. Just under Help memory monitor, here’s an easy way to bump up the memory available for Aqua. By default, it really never took a big footprint. Now with the client machine performance improved over the years, I always like to point that out. You can allocate more memory to Aqua.

The tabular interface here lets you use other tabs and windows as you’re working in Aqua. I could kick off the Schema Compare and then perhaps come back to some other code I was working on, right? Here maybe some SQL I was working on or any other database responsibilities I have that I’m using Aqua for. You can revisit or explore as say that Schema Compares running. Okay, so multitasking or working or multithreaded performance is a benefit for Aqua where while that Schema Compare is running, you can use other tabs and work on other areas within Aqua. Okay, so here that’s almost finished up, just about finished up. That will bring up the left and right hand side comparison window. This is probably familiar to many of us in terms of what we’re seeing here, where you’ll see, okay, what objects are in common but different, what’s on one side, what’s on the other.

Okay, so maybe just to spend some time here in this window and exploring. Again, this is a similar look and feel here for the Schema compare or Schema synchronize. You just have some additional platform capability in the sync script here, but either way these icons would be similar and then some of the navigation capabilities will be introduced here. Here I can just minimize everything and just get a summary here of what’s on one side, what’s on the other side, what’s in common, what’s different. And then here would be expand all. As I highlight here an object in common, I can explore what’s different, what code varies, and then we’ll talk about deployment in other ways as we get further here. There’s reporting information there’s, both Excel and HTML reporting. You have the ability to make changes here and rerun the comparison. Different shortcuts here that you might expect.

Just to maybe mouse over and show this refresh would just rerun this on the fly. As you make changes, you want to do a new diff or new comparison that would tick that off right away. Here’s an edit where if you want to make changes to the objects or the filter in terms of what objects you’re comparing and the Schema compare, this would give you a warning, say, okay, you’re about to lose all this previous information, do you want to kick that off? The refresh would just kick off a new comparison. Here’s the edit where you could actually change the filtering. Here are some of the reporting capabilities. Here’s comparing data. So here I’ve highlighted a table. If I wanted to compare the data within that table, this is almost a hidden way to invoke the compare data within the Schema compare. Okay, so that’s a little bit hidden.

Cross Platform Support

We’ll revisit that, but okay, so let’s maybe explore some of these areas. Here, I just ran this live Schema Compare for SQL Server. Here are some previous schema comparisons I had run earlier. Here say an Oracle Schema compares with a few more objects. Maybe that reporting would show in a little more robust way. We can step through an Oracle sample here and we’ll do that for the deployment of the synchronization. Wherever you have the results set of the schema compared with this difference, only in source, only in target, you have the reporting or documentation capabilities, right? Here I was looking at the schema synchronized from Oracle to Oracle. Here if I visit say a SQL Server synchronize or here’s even a cross platform Oracle to SQL Server, which is interesting. If you like to compare cross platform objects, say MySQL to Oracle, you can document that as well.

Reporting

What I’m just pointing out here are some different tabs that I have opened with my results set. Now let’s take a look at some of the reporting here, right, so here if I wanted to compare this Oracle schema, clicking on HTML shows a directory available. So here I have a compare directory. I’ve created some different groupings here where I like to keep my Aqua Data Studio Compare results set. Whether you’re choosing a platform or however you like to organize your navigation tree, this would create an HTML subfolder. You can easily view that and share this or post this. So just documentation might be important here. Just show me this in a report. Here’s the same information, what’s different, so what’s in common but different. The non-equal sign, what’s one side, what’s on the other, what’s only in the source, what’s only in the target.

HTML

So you see this documentation. Maybe save yourself some phone calls if people need to see what’s different out there, or if you need to delegate some change management responsibilities, this is an easy way to document that and post that. You have this side-by-side capability. Here are some different tabs that I’ve set up that we can explore and look at some of these differences. This was the example of the HTML report. 

Excel

Right next to that is the Excel icon. This would allow you to say, look at this and say an Excel spreadsheet, which not as vibrant as the HTML, I would say, but you still have the color coding and just a quick visual, what’s in common, what’s different, what’s on source, what’s on target. Of course this could be saved if you need to document this in different ways. Just introducing some of these icons here, I mentioned these different icons.

Oracle Schema Compare

The refresh will just take off the compare on the fly. The edit would actually let you choose the wizard. Again, so similar look and feel. This was the SQL Server Compare that we just ran. Now that’s a tab that we can dive into and work on. Since this is for SQL Server and Oracle, let’s kick off an Oracle comparison as well. We’ll be able to dive into those. Here just to show the similar look and feel, which is a benefit with Aquais whatever platform you’re working in, the menus are similar. Once you become productive in using Aklin one platform, you can do that across other platforms as well. Okay, so we ran the SQL Server comparison. Here again, more manual tools, compare tools, schema synchronize, and I could drill into my Oracle source and target, right? Choose my Oracle group, choose my Oracle schema.

I’ll choose this development schema where I’ve made some changes and like to compare that to my test server. I can manually drill in here and choose that target schema, say, okay, and now I’ve set up my left and right hand side. Similar, just introducing a similar look and feel here. I can kick off that Schema Compare or Filter. Maybe just for the sake of time, maybe I’ll reduce this just to say, tables use and PL SQL. Similar options here depending on where you are in terms of what objects you’d like to compare. Kicking that off and allowing the catalog to do the side by side dips. Of course, I mentioned the right click menu will save you time from having to perform this, right? Here if I on the left hand side navigate the tree and use my control click, you’ll save time here.

Here I could kick this window off. Maybe I’ll just isolate here just some tables and PL SQL, click Compare. Now it’s hitting the Oracle catalog and we’ll complete this. Here are some two live comparisons that we’ve run. Again, as I mentioned, you can use other windows in Aqua as you’re letting those transactions complete or that comparison complete. Just to introduce while that finishes the shift click for Oracle. Again, if I drill into my schemas here. Here I have some similar objects or environments in this dev environment, in this investment environment, this global investment management schema. It has some client broker stock type tables. Here, if I was to just control click and then right click there’s schema Compare, schema Synchronize notice it populates that much more quickly than having to choose the left to right hand side. So little time saver there. Just to tie this into the PowerPoint.

We’ve just been starting here with the Schema Compare and Schema synchronized where those have a similar look and feel. You just have some additional synchronized capabilities that we’ll explore here as those wizards complete. From any of the menus you have the documentation, so HTML or Excel report, and then a bit of a hidden compare data from the main menu that we’ll revisit as those complete as well. The shortcut ways of invoking those are what we’ve jumped to. So control click for the schema. Compare. If you just want to do an individual object comparison, you have that as well. We’ll tie that into the drill down into these sub compare areas. Okay. So, alright, so let’s close this window. This is just the shift click example or the Control click example. And then these are okay. So these both finished up. Here’s my schema comparison that I ran in SQL Server.

And then here’s the oracle. Okay. It’s Oracle. Still finishing up Schema comparison. SQL Server is completed. Want to introduce the delta between this sync column. Here if I go back to say compare across different platforms, there is no sync column, right? That’s the delta between the Schema Compare and the schema synchronized, you get the migration synchronization column and then that icon is enabled. Okay? Let’s back up here and take a look at some of these drill downs. We’ll lead up to the migration of the synchronization capability as that finishes. All right, so the tab compare, directory compare, file Compare, copy Compare, and Results Compare are very popular, easy-to-use areas that once you’re familiar with Aqua, can be very beneficial. The tab compare is your query analyzer. It means tabs, it means a tab of code, right? So here’s some different SQL transactions. Or here if I back up to some complex SQL, I have in say, Oracle and SQL Server, again, I have this investment broker database.

SQL Statements

Here if I run this complex SQL statements, I can see the results set. Here would be, for example, the execution plan in SQL Server, right? Some examples f executing SQL, looking at the results set, looking at the execution plan, and then here is maybe a different SQL statement, right? Let me run that and you have some different SQL statements here. If I just look at my SQL history, you can see a history of SQL you’ve executed. Here are a couple of SQL statements. 

Tabs Compare

I just executed SQL Server. If I wanted to compare that code here under the Tools menu compare, Tools tab compare lists, all the open tabs, and then I can choose that left or right hand side. In this example, I’m comparing these two SQL statements. It’s showing those editors here I can say okay, and now it has a side-by-side comparison of the differences between those two statements.

Okay? This look and feel exists in a number of areas. We’ll introduce this in different ways, but let’s just back up and we can show some other comparison features. Or an easy way to get to that feature, as you might expect, is a little shortcut. So there’s the tools to compare. Tools tab compare. That’s why I invoked that side by side comparison. Or if I just right click on the tab, notice there’s a comparison there, and that would get you to the same spot where saying, okay, you’re comparing this tab, what’s the target that you’re comparing against? Okay, so you can invoke the compare on the right click on the tab or just Tools menu tab Compare and then choose any two open tabs that you have. Tabs of code. Okay, so similar to that is what we see on the bottom portion of the Schema compare, right?

If I go back to say that Schema compare that we executed, or here’s the Schema compare in SQL Server I just executed. As I highlight a piece of code, I see that side by side comparison, right? Very helpful, you’ll get familiar with how to do these comparisons and then other bells. And whistles that users like. Scroll on my mouse scroll bar here on the right hand side of the code and see how the codes lining up with each other. These are helpful, visual, impressive ways that Aqua has been developed over the years that users really like. This is an example of just an individual SQL statement comparison. Or were looking at this tab Compare.

Directory Compare

 If you have long pieces of code, you have that scroll capability and similar is the directory Compare right here were looking at the tab Compare. Here’s a directory Compare which will compare a folder and all the files within that folder or directory.

Here just choose your file or directory choose your network driver folder. Here I have a couple of directories with a couple of files in common. Click Show Differences, click Calculate Differences and that’ll do a side by side comparison of those directories, right? Here’s a file that’s just one folder. Here’s a file that’s just in another folder on the left or right hand side. But here’s a file in common. Again I have this scroll capability. I’m just scrolling to my mouse here and you can see the side by side comparison. We’re getting to hundreds of lines of code here and this really can help save you time. Give that pop capability where I see the differences. Of course as you become familiar with aqua, some of these can really be time saving. You have these set up to do these delta or diffs very easily.

Okay, so we’re running some wizards here in terms of the schema compared synchronized. Now we’re introducing the drill down look and feel of some of these windows which are in these wizards as well. Similar look and feel as you explore these file comparisons would just show you any two files source and target. If you had those open and code tabs already that would be beneficial there and that would do a similar side by side. You have this look and feel that you might expect and that’s really a huge time saver. Here I was drilling into these in the navigation tree and choosing the left or right hand side. Again with the shift click capabilities, right if I have an object in common. Here in my oracle environment and this dev schema, I’ll choose an object and then maybe I’ll control click to a different object, the same object name but in a different schema.

Again right click there’s the compare just for those two objects and that instantly does a side by side DDL Compare. So that was pretty quick, right? Just control click side by side Compare. A more manual would be to say a Copy Controlc and then a multiple control c Compare or what we call the Copy Compare. This is a memory or clipboard you can control C, multiple pieces of code to memory, right, just the Windows clipboard. Aqua has easy ways of showing those. Here let’s just work with the same client transaction table. I just controlled clicks in the navigation tree for two different objects and then right clicked and there was Compare very quickly. So there’s other scripting capabilities in Aqua. If I just right click on an object there’s a script object to the new window. 

Query Analyzer

So new Windows Query Analyzer. If I just right click and script this object to a new Query Analyzer window with the create you could do one at a time.

Or if you’re working in an editor, you have different ways of showing this. Now these are both open in the editor. So I’ll just close this window. If I had already copied to memory or clipboard, that would be a way of showing this as well. Let me just maybe right click on an individual table. Right click here and there’s again the scripting right. Here’s the script object there’s create or Create full. Just right click and extract the DDL to multiple editors for these tables. Now let me just copy to memory right. Right click here or control c copy here’s this client transaction table in one schema here’s this clients transactions table in another schema. I’m just copying the clipboard. Maybe I’ll do this one with storage. So again, right click copy. Now in the Tools menu copy Compare, it’s showing me what’s in my clipboard. Here I can see the DDL for both these tables.

I can control click or shift click on different if they weren’t executive here I’ll just shift click and that’s a side by side showing. Okay, these are the same ones that include storage, otherwise those are identical. Oh, we actually here you can see a constraint, right. Here’s the difference between constrained storage on the source side or left hand side. Very easy right click or wizard interfaces for stepping through Quick Dips or Quick Compares and then the results Compare. Let’s circle back to so that’s Data compare of results or within the Schema Synchronize or Schema Compare wizards you can dive into a table results compare as well. Okay, so just keep an eye on the clock here and the list. So we kicked off some schema comparisons. We’ll be closing out with some of the synchronizer migration capabilities. You have the ad hoc or random Compare features Drivable from either right click menus or the Tools main menu.

Some of these are embedded in the Schema Compare and Schema Synchronized. We’ve seen some of these with the file Compare and the side by side. So let’s keep moving forward here. I think you’ve seen some of the shortcuts in the navigation tree and then the Query Analyzer. We just mentioned how to compare a way to do side by side comparisons. As we circle back to see what’s finished up here with these comparisons. So here is the SQL Server comparison. And then here is the Oracle comparison. Again you have that summary information here, how many objects were different, what’s on the source, what’s on the target. Here is an example of the compare data field that I wanted to introduce, where if you have tables with different data, you have the ability to kick off a data comparison just by selecting that table. It will show you what data is different between those two tables.

Sometimes there’s confirmation that there’s no difference, right? You have left the right hand side. Here you can see these two tables. One was empty, one had data, one didn’t. Depending on where you are, you have the ability to look at comparison of data in different ways. We’ll circle back with the data Compare. There’s some Savvy that we can introduce to that wizard, but you have the ability to look at the results compare in different ways. And then the table data compare. Maybe just to introduce here, let’s see an example of okay, I just wanted to just give you a quick introduction here of the look and feel of the results. Let me maybe, just maybe I’ll just open up a query analyzer real quick and run some different SQL. This is the last window that we perhaps haven’t seen yet. Here, let me just tick off a couple of similar SQL.

Data Results Compare

Okay, so here’s a SQL statement and SQL statement with a where clause. A subset of data result sets and right click here and here’s Results Compare. If I wanted to compare data sets between two different queries, that’s what this is showing here. This would allow you to navigate or sort between records one side or the other and see what’s different here. So here I can see a comparison. One was a sequel statement and one was a subset of that. Here I can see 77 rows or 39 rows which are different. You can sort or isolate your primary key and sort in different ways here. There’s Savvy to introduce how to navigate this data comparison. So just introducing that here. That’s something that you can invoke from say the Schema compare wizards as well. Here we say the Oracle scheme of Compare that we just executed.

Data Synchronization

I could choose a table and then explore the data differences between those two tables, right? Here’s compare data for this list of objects, right, so here’s a table in common that I could explore data differences between. You have the ability to deploy certain assignments in terms of synchronizing data. Okay, so let’s move on to some of the synchronization capabilities. Here we say an Oracle left or right side by side comparison so I had this dev schema to this GM schema. Here’s a list of the sync objects available so that enables this synchronized schema icon. This will prompt you to select certain objects to be involved in that deployment. You can check all if you’re choosing all the objects. Otherwise I could manually choose objects and deploy those changes, right, so here’s this client transaction table where it has a constraint and an index on the left or right hand side depending on which direction you were going.

You have that capability or deployment script to change that. Here’s a table just on the source. Maybe you want to bring that over to your target side. Let’s maybe select some different objects here and just dive into the synchronization wizard. You obviously can have different numbers of objects included in the synchronization script, but what we want to introduce is the look and feel here of the object wizard. As I select the synchronized schema, this is available only in the Schema synchronized wizard, right? The Schema compare would not have this column or this icon enabled. If I click on this now, a different wizard tab opens up the schema synchronization. I can maybe unfloat this or float this rather. Just so you can see the tabs here, right? Here I can see these different windows or tabs. This will step me through with the next. First it’s pointing out dependencies to be aware of, right?

Which objects are involved in this migration? These are some helpful options and warnings tabs if you have other related objects or just letting you’re changing structure from source to target. As I click next here, these become enabled. You had different scripting windows as you might expect throughout the product. SQL Server, you might have your Go Separator, maybe for Oracle you have your Semicolon, but you have that flexibility here in Settings in Aqua. The review of the SQL script itself, which is always a nice feature. You don’t want to kick off without being able to take a look at that. Some of the summary and warning tabs, right? Here you can see were dropping an index, we’re migrating the constraints or creating a new table, right? You can see there’s the drop index, drop view, add constraint, create new table. Just a different example of some objects that we included in this synchronization.

Here’s a summary, we’ll just let you know what you are doing, you’re creating this table, you’re dropping this constraint, you’re driving this view, you’re dropping this index. Okay? Warnings, if you had any warnings, any dependency warnings, so you have flexibility here, you can open this up in an editor. You can maybe choose different objects that were included in that, but obviously they just open the script and the editor is a nice feature. You’d have the ability to choose which objects are deployed or where you’re migrating those changes. Changes too. Here in SQL Server, maybe I’ll just choose a couple of different objects here in terms of source and target, maybe a table and then a schema synchronized here in SQL Server similar review dependencies. What’s your object separator? There’s the DDL script opening a query analyzer. If you have those transactions ready to go, you could execute all here I could say parse through this here’s, this create table.

I could just select this individual table and execute that and that would deploy those changes to the target environment. Here if I just execute this individual create statement on the target, this would deploy that and then you have the ability to rerun that comparison and then see what differences there are. So that’s maybe a close out. If I circle back to a previous schema Compare that we executed, this refresh will just run the live Compare again. That’s a benefit after you perform your deployment, how to execute those and synchronize those. A last area that might be of interest is the Compare data. Were looking at that in some different examples where if you do have results sets for comparing data or table data within an individual table, you have the ability to click on the Compare data and that would show okay, so here’s source and target, that’s different.

Let me maybe circle back here. Okay, so here’s a table where I could see if there’s individual rows on a certain side or left or right hand side and then a side by side comparison of rows that are unique to one side or another. Here’s deleted values or added values from a source or target. Here I can see there’s a row difference between these two tables. Here you can see what those are and actually isolate the differences here in the data set. If you isolate your primary key, this would be a way to circle the grid and see what those values are. Here I can see an altered value and what the difference is. Here I could see a new row on the source side or target side. Depending on which direction you selected the Compare result sets, you can implement those changes in different ways.

But there’s no data synchronization. So that’s something to be aware of. We have structure synchronized but not data synchronized. It’s just Compare result sets or compare table data. Another hidden Compare area that’s very beneficial for Aqua is some of the diagramming here are modeling compares, right? So there’s table diagramming across platforms. Here I have some examples and say Oracle or SQL Server. Here, without going too much into the data modeling capabilities here I could reverse engineer and document in an Er model the database in terms of tables, views and related objects. There’s the compare features in the Tools menu within the Er model. So compare with model, compare with database. This would allow me to just choose an individual Aqua Data Studio model. Maybe I’m going to a different version of a platform or I have an existing model and I want to see some comparisons there.

Just choose the target. You have an existing model, you have some settings here that might influence how it sees differences. Scheme a name or not. I’ll just take the defaults here. Now this would be a way to say compare objects in a model from left to right hand side, what’s one side, what’s on the other. Here I have maybe a more robust example of some of those. Okay, so here’s just a model compared with showing, okay, what tables are in common but different what are in common but same what’s one side, what’s on the other. The modeling comparison capability is very popular and then once you isolate what those objects are that you’d like to migrate, you could dive right into the object synchronization with the schema synchronization. Okay, so lots of productivity areas in using Aqua and then a little learning curve in terms of where the tabs are.

You really want to right click throughout the navigation tree. For some of the compare shortcuts control click on objects, right? If I click on certain objects, I see shortcuts or reduced lists, right? If I right click on it just a table itself. You can see a quite extensive list of shortcut features there. When I control click, it brings in the compare features and a shortened list. So compare schema Synchronize. Okay, so up, running out of time as usual. So thanks everybody for joining. We can open it up for questions here and then just under help about our links to the community site. A lot of these areas are documented. Here I tend to show the Aqua Data Studio drill down. Here’s a summary of all the benefits of Aqua Data Studio kind of in one page. Here’s a summary of those compare and synchronized features.

Conclusion

So this is all documented. There’s different ways that you might have seen benefit here and really been driven by end users over the years. We’re always looking for feedback and look for improvements here as Aqua continues to be developed. Version 25 is in its final stages and it’s being released at the end of this month, beginning of next month. That’s going to bring some new platform support, a job scheduler, and just more enhancements to other areas in Aqua that are very popular like the visual analytics and some of the Amazon RDS platforms. Those are the big areas in the next version. Okay, so under the Resources page, this is where this recording will be posted and look for recurring topics that we like to engage with our customers on and give some specific, use case examples. At this point, let me hand it back over to Kirsten and open it up for any questions.

Q&A

Great. Thank you, Devin. We have had a few questions come in. One question from the audience was does Aqua Data Studio allow or provide for data sync scripting? They asked an example where you identified a new record existed and one column had been changed.

Okay. Yes, so you have the synchronization capabilities for structure but not data. Here, for example, here’s a table, or maybe I’ll switch back to say, our Oracle example. Here’s a table in common where this will show the difference in structure. If I chose the synchronization script for this table, yes, that would build the migration script. We have the ability to compare and synchronize schema here, and that would step you through here. Here’s the migration script to make those in sync. In this case, it’s just altering this table to add the constraint or drop the constraint or add the index or drop the index depending on which direction you are going. But there’s not a synchronized data. To see the differences in data, you can compare data. Here, compare data from within the synchronize, but there’s not synchronized data.

Okay, fantastic. Thanks Devin. Another question we had, can you compare the schema between an Oracle database and a sequel server database?

You can. That’s the comparison but not the synchronization. I had an example of that, I’m just not finding it right now. But yes, you can compare cross platforms. Yeah, I mean here just maybe just to show this a little more quickly. Here I have this Gim Schema in SQL Server. If I control click to Oracle, right click, notice the synchronize is grayed out, but Schema compare, boom, populates that left and right hand side and then that would kick off for you. I had one that executed already, it’s in that list. Yeah, you can compare cross platform to see differences and maybe document those with some of these examples. Okay, sure. So that’s a yes.

Perfect. Another question that just came in. What is the difference between Schema Compare and synchronization?

Okay, so here’s a Schema compare window and the results sets things to point out there for synchronization is only available on the same platform, right? Here if I wanted to compare these SQL Server oracle databases, I could shift click control click, right click. If it’s the same platform, you can schema compare or schema synchronize. If they were different platforms, you only have the Schema compare available. Okay, so that’s the difference in terms of what you can execute in terms of the Schema Compare Schema synchronize, and then with the schema synchronize you have, this synchronization field is populated. I run a schema synchronization, this column sync will be displayed and this icon, synchronized Schema will be enabled where you can generate the migration script. For Schema Compare, it’s read only and we’ll just show you the differences. Schema synchronization enables the sync column, the synchronized Schema icon, and then we’ll generate the sync script or deployment script.

Those are the two differences between those two windows, this additional column, and then Schema compares any platform Schema synchronizes with the same platform.

Fantastic. Thanks, Devin. I think we have time for one or two more questions. Another that just came in. Does the tool support comparison of structured and semi structured data like RDBMS and JSON?

Okay, that I would have to follow up on in terms of specifics. What you can see is the list of objects that are available. Here I was in, say, an oracle. If I right click Schema Compare, notice all these objects, oracle to oracle. Here. If I was cross platform, that list is not as robust. I’d have to see where that would vary from JSON to our DMs.

Wonderful. I think just one more question for today. Can I switch source and target when synchronizing?

Okay, so it would be from this window, and this direction would need to be switched. You’d go back to this window and switch those and then regenerate this window. That would influence this sync look and feel. Here if I invoke this, you can choose from here. You see the script, it’s modified so you have the ability to explore that. If you wanted to generate the different direction, it would be from the Schema synchronized window and you’d flip flop the direction there. Either control, click in the right direction here where you start with your source and target, or from this window, you’d need to use these double arrows to switch those and then generate the sync script from here.

Conclusion

Wonderful. Well, that is all the time we have for today. I would like to thank Devin for an informative presentation and I invite you all to head over to Aquafold.com to learn more and download a free trial of Aqua data studio, which Devon highlighted for us today. This webcast has been recorded and we will send a followup email to all attendees with a link to our resource center where the recording will be available for viewing in a few days. I hope everyone has a great week and we hope to see you back at our next webinar. Thank you. Thanks everybody. Have a great day. Bye.