Feature Rich Database Management Tool and IDE for 40+ Platforms

By: Eric Blinn   |   Updated: 2023-06-13   |   Comments   |   Related: More > Database Administration

Problem

I write a lot of SQL code across a series of databases and database platforms and I need a better tool to help me manage and organize my work. Having to work with so many different tools, slows the process and I need a better approach.

Solution

Aqua Data Studio by AquaFold is a management tool and IDE for database servers. It has every feature you're used to having in an IDE and a few you probably didn't think were possible. It is so feature-rich that you can replace several feature-specific tools by rolling those functions into Aqua Data Studio - saving money and streamlining operations.

A Universal Data Management Tool

Aqua Data Studio can run on Windows, Linux, or MacOS. It's able to seamlessly connect to more than 40 database platforms. That means you can replace several single-platform DBMS administration tools with just one - Aqua Data Studio. With this being MSSQLTips.com, this tip is going to focus on how Aqua Data Studio can connect to and help manage the Microsoft data platform including on-premises SQL Server, Azure databases, and Azure Managed Instances.

Here is a screenshot of Aqua Data Studio connected to each of the 3 main SQL Server delivery methods. Working between them is seamless.

It is very easy to connect to cloud services and below shows the connection values I used to connect to the Managed Instance and Azure DBs respectively.

Manage Many Instances with Ease

When you're working with a large number of database instances it's really easy to mix up which server you're working on. I've lost track of the number of times I found myself running a query, getting unexpected results, then realizing I'm still connected to test when I thought I was on production. The opposite can also happen which is even scarier!

Aqua Data Studio helps you manage this scenario by color coding tabs. For this server, I've chosen a deep blue color.

I also get to choose a text label for my tabs. It can vary by server just like the colors. In this example, I've chosen to put the server type, in this case "Production", at the front of the tab name and you can customize as needed.

Interactive Results

When using Aqua Data Studio, the results of queries in the grid are interactive. This can be a big time saver. I know that when I'm debugging an issue or chasing down a data anomaly, I'm constantly having to copy the results into Excel to do a quick sort or subtotal. That can all be done right from the IDE!

Simply clicking on a header sorts the results by that column.

Click again to sort descending and one more time to remove that sort and go back to the default.

Another great feature is the ability to highlight a series of cells and get a sum of those values right on the IDE results window. This is so much faster than rewriting a query, using a calculator or even using a spreadsheet.

Even better, it's not limited to summation. Instantly calculate any of these aggregate operations against a series of values from the query result set.

One final feature of the grid results is the ability to send the highlighted data directly to Excel. Do it with just one click in Aqua Data Studio.

Query Builder

Writing your own TSQL is a skill that takes a long time to master and can also be time consuming. Therefore, having a tool to assist in writing queries is essential. Anyone that has spent time trying to learn the different kinds of joins will attest to this fact! Aqua Data Studio comes with a query builder to do just that. Just drag, drop, and watch the TSQL write itself.

This is a screenshot of the query builder in action. The UI is so intuitive that adding multiple tables, joining them, adding a group by, aggregate function, where clause, and an order by were all done in just a few seconds.

The output of the query builder above is this query.

SELECT
   [Invoices].[InvoiceID],
   [Customers].[CustomerName],
   SUM([InvoiceLines].[ExtendedPrice]) 
FROM
   [aquadatastudio].[Sales].[Invoices] [Invoices] 
      INNER JOIN [aquadatastudio].[Sales].[InvoiceLines] [InvoiceLines] 
      ON [Invoices].[InvoiceID] = [InvoiceLines].[InvoiceID] 
         INNER JOIN [aquadatastudio].[Sales].[Customers] [Customers] 
         ON [Invoices].[CustomerID] = [Customers].[CustomerID] 
WHERE
   ([Invoices].[InvoiceDate] < '2013-01-01') 
GROUP BY
   [Invoices].[InvoiceID],
   [Customers].[CustomerName] 
ORDER BY
   [Customers].[CustomerName] ASC

When you write more complex TSQL code like stored procedures or functions, Aqua Data Studio will be there to help.

The "SQL Debugger" section of the application can help work with larger blocks of code by adding the ability to insert breakpoints, view variable values, step over code lines, or step into functions. The best part is that this feature works for more than just Microsoft's TSQL. There are specific SQL Debugger applications for Sybase, Oracle, and DB2. Read more about the entire list of debugger features here.

Entity-Relationship Diagram

An important step in documenting a database project is building an Entity-Relationship Diagram, or an ERD. It's not uncommon for a DBA to have to leave their main application to go to a special modeling program. Not so with Aqua Data Studio. That feature is built right in. You can build, save, export, or even print an entity-relationship diagram for your databases.

Here is a sample from the SSRS database, ReportServer. Once designed, it is just a few clicks to generate the DDL to incorporate any changes you've made.

Visualizations

Generating visualizations is a great way to get your message to business users. Aqua Data Studio has several built-in methods to generate intriguing visuals. For this demo, I started by executing this query.

SELECT TOP 10 SUM(ExtendedPrice) TotalRevenue, SP.StateProvinceCode, SP.StateProvinceName
FROM Sales.Invoices I
INNER JOIN Sales.InvoiceLines IL ON I.InvoiceID = IL.InvoiceID
INNER JOIN Sales.Customers C ON C.CustomerID = I. CustomerID
INNER JOIN Application.Cities AC ON C.DeliveryCityID = AC.CityID
INNER JOIN Application.StateProvinces SP ON AC.StateProvinceID = SP.StateProvinceID
GROUP BY SP.StateProvinceCode, SP.StateProvinceName
ORDER BY SUM(ExtendedPrice);

After it executed, I clicked the bar chart icon on the top left of the results area - highlighted in the screenshot below. Then I picked StateProvinceName from the first drop down and TotalRevenue from the second. Those 2 options are just to the right of the bar chart icon. That's it. Just 3 clicks after executing my query and I'm looking at a chart. You can just see a hint of it in this screenshot.

There is also an option to export the chart to an image. I've done that here so you can see the full result.

This was just a simple chart created from the query result set. There is an entire Visual Analytics application attached to Aqua Data Studio that takes this to a whole other level. In my opinion, it's better to see it than to read about it. Check out this video showing the more advanced visualizations.

Compare

It is a very common scenario when working at a DBA or Developer to have to compare the schema of 2 databases. The most common reason is to take all of the changes from one environment and promote them to another. Often a DBA has to leave their regular management tool and go to a completely separate program to accomplish this. Not so with Aqua Data Studio.

There are several compare tools built right into the IDE. Here is a screenshot from the Schema Compare and Synchronize tool. I made a list of all of the objects I've placed into the dbo schema of WideWorldImporters over the years that aren't in the default download. Just a few clicks later and I'm looking at a synchronization script - all without ever leaving my management application.

Comparing doesn't end with the schema. Use Aqua Data Studio to compare files, directories, the text in open tabs, or even query results.

Final Thoughts

There are so many other features offered by Aqua Data Studio that I just don't have time to list them all. Some are bigger (like a random data generator) and others smaller (like a library of time-saving hotkeys), but they all add up to huge efficiency improvements.

Some other great features include:

• Database administrator tools for many different database platforms – there are so many tools specific to each database platform. You can check out the list here.
• Debug SQL code for Oracle Database, SQL Server, Db2, and Sybase – as mentioned above, here is more information about what is available in the debuggers in Aqua Data Studio.
• Monitor running SQL queries with the Execution Monitor.
• Connection Monitor offers deeper insight and control over queries and connections.

I'll leave you with one small feature that I really like. Almost any screen that involves a wizard generating SQL code for you will include the "Preview SQL" tab so that you can see the code before you attempt to execute it. I am a big fan of having the preview right on the wizard screen instead of having to explicitly script it out and then view the code.

Aqua Data Studio also lets you generate server scripts and schema scripts. Here is a link to additional information about the server script generator.

Next Steps

• Download a 14-day free trial
• Are your databases among the 40+ supported by Aqua Data Studio?
• There is an entire library of videos to show more about the functionality within Aqua Data Studio

MSSQLTips.com Product Spotlight sponsored by AquaFold, makers of Aqua Data Studio.

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips