DATABASE TOOL
Database Development Tools
Choosing a database development and/or management tool doesn’t need to be a complicated process, but you should at least determine what features are important and would be most productive on a day to day basis. Granted, most tools have many features that you may never use, but they are built into the application based on some user request and it may be a feature that could be useful in the future.
In this paper, we will look at some of the tasks database professionals deal with on a regular basis and 11 things you may want to consider when selecting a tool.
Multiple Database Platforms
As a database professional, one of the tasks that makes the job interesting, but complicated, is the need to work with data from multiple database platforms. One of the key problems related to this is the need to learn and have several database tools installed. Often these tools are very different and may provide a variety of features that sometimes overlap and other times do not. Mastering multiple tools is not something we as database professionals should be spending our time on, but instead focusing on the tasks that have a direct impact to the business.
In the past, most companies stuck with one flavor of a database management system, and most of these implementations were on physical hardware located at the company using the same OS. And even if there were multiple RDBMS installed on different hardware and operating systems, you usually had different groups of people working on the systems, so the need to learn more than one tool was not an issue.
This has changed over the years and the database landscape has become more complicated, yet as database professionals, we are being asked to do more and know more about different database platforms as well as where the database lives. We now have databases on premises and in the cloud, installed on physical machines or virtual machines, running over Windows, Mac OS, or Linux, built on SQL Server, Oracle, MySQL, MongoDB, or PostgreSQL, and the list goes on.
So, finding a common tool that can support multiple database platforms on various operating systems and having the ability to work with all types of implementations is key to being the most productive and eliminating the need to constantly learn new tools.
Query Tools
What use would a database tool be if you weren’t able to query the data? Not much. So, one of the first things you need to look at is whether the tool satisfies your needs for query writing and execution. What output options are there for the results? Does the tool offer autocompletion when you are typing the query? Another helpful item is reformatting the query. As we write and rewrite our queries we often get a little sloppy. Sure, we can take the time to reformat the code and indent where necessary, make keywords upper case and lower case, etc., but having a built-in feature to handle this can be a great time saver.
Another thing that is necessary is the ability to look at query execution plans. The task of writing a query and returning the results is just one part of the equation. There are many ways a query can be written to achieve the end result, but is the query efficient, and can you make improvements to speed up the query runtime? Having tools to help aid in the decision of whether the query is optimized is an important aspect.
Visual Query Builder
SQL is a powerful language, but it is often a tedious and sometimes confusing task to write queries. With the aid of a robust visual query builder, database professionals can create and edit SQL much easier. In addition, as we move from platform to platform, the syntax rules may closely match, but there are specifics for each database platform, and a visual query builder can help eliminate the need to know every nuance.
Once again, we are faced with the need to know the specific nuances for each database platform and making sure the syntax is written correctly. This becomes very frustrating when you are super-proficient at writing T-SQL but have no clue why your query doesn’t work for some other database platform. This is where having a visual query building tool can be extremely helpful to make sure you get the syntax correct the first time and can focus on the data that you are trying to retrieve and the business problem you are trying to solve.
Visual Query Results
Another great feature when working with data would be the ability to create visual output of your data like charts and graphs directly within the tool. How many of us have written queries, then taken the result set and pasted into Excel to build a graph? Then you find out the dataset wasn’t exactly what you needed so you modify the query and repeat the process until you get it right. Also, with each iteration you spend more time trying to get the visualization correct than the time it takes to query the data.
In addition, think of the queries you run on a daily basis when managing your database platform, whether looking at free space, the size of tables, failed processes, etc. Think how much more beneficial it would be to have graphs and charts of this data instead of just raw numbers. With visual aids you can look at a lot more data over a longer time period to look at trends and quickly identify issues or anomalies. This is very difficult to do with just raw data.
Data Editing
Let’s face it, there is often the need to add new data to a table or make quick data modifications in a table. Sure, we could do this by writing a query or using the application interface if one exists, but there are times when we as database professionals need to make some quick adjustments to data and having a data editing feature built into the tool can save you a lot of time. I am not saying that as a general practice you should bypass the application to make data changes, but what if there is no application or easy way to fix the data? Having a robust data editing feature can save you a lot of time with making data adjustments. With just a couple of mouse clicks you can have the data updated, versus taking time to write a query to do the update. In addition, as you move from database platform to database platform, knowing the different syntax rules makes the updates that much more complicated.
Import and Export Data
Data movement is a fundamental aspect of a database professional’s job. This could be running queries that update or insert data, replication of data or physically moving data from one system to another such as loading a data mart or a data warehouse. In addition to these items there is also the need to import data from heterogenous systems as well as export data for various needs such as sharing data with partners or the need to move this data to some other system.
Having a tool that makes the process simple is a start but having a robust tool that can handle many different systems as well as data structures is what you really want. Also, since this a task you may do on a regular basis, having these features built into the tool you use on a daily basis makes more sense than having to learn a totally different tool just to tackle imports and exports. You also want to avoid having to learn how to do this with each database platform you work on. Sometimes you may not have the right tool to do this or you don’t know how to use the tool, and you need to rely on someone else to fulfill this task for you. So, having a simple yet robust import and export process built into your go to tool can save you time and avoid frustration.
Scripting
Another key feature to look for in a database tool is the ability for automated scripting. We don’t always have the source code available, but the DDL and DML is stored in the database, so having the ability to quickly generate scripts from existing database objects can be a big time saver. In addition to scripting existing objects, it is helpful to be able to generate scripts based on options that we select using the GUI. The code can get complicated or have several parameters that are needed, so being able to use the GUI to select all of the options and then create a script instead of actually executing the code is an important feature to look for. Other time savers for scripting can include auto-creation of statements to be used for doing simple SELECTs, UPDATEs and DELETEs. If the GUI can generate most of the script, it can be a big time saver to have a starting point that can be modified, versus typing everything from scratch.
Comparison Tools
Another task we often need to tackle is the ability to compare scripts, database objects and even the data in some cases. There may be the need to compare schemas between your development and production environments or the need to compare the data in tables. Also, you may have scripts and code in files outside of the database that you need to compare to see what if any differences exist.
There are several tools that focus on just this task to either compare objects or data or in some cases both, but once again how many tools do you want to learn, and do you have the ability to purchase multiple tools?
Query History
This is one item I wish I had on many occasions.
How many times have you written a query and ran it successfully, then thought of some great idea to make it even better, so you tweaked it a bit? The query ran fine again, so you make some more tweaks until the query doesn’t do what you want it to do anymore, so you want to go back to a previous version. You could try to use the undo feature, but this doesn’t always get you back to where you want to be.
You could write the query and run it. Then you could copy the code, modify it and run again. And keep repeating this process over and over. I guess if you are very methodical in your approach this would work, but this is probably not always the case, especially if you are trying to get something done as quickly as possible.
Also, what about capturing other information such as how long it took to run, or how many rows were returned? Once again you can capture this information and keep track of it, but it would be great if the tool could capture this history so you could quickly go back to a previous version as well as look at some additional metrics of the query execution.
Search Tools
One frustrating thing about working with databases is that there are so many objects, and finding things is not always as simple as you think it should be, such as what tables use a specific column name or which stored procedures access a specific table.
Sure, you can query the system metadata to find a lot of this information, but why bother if the database tool has a built-in search mechanism to quickly find your database objects. Also, as you move from database platform to database platform, different system tables are used so you would need to know how to do this many different ways if you try to query the metadata directly.
Product Support and Updates
The last thing to consider when looking at a database tool is the company behind the tool and the history of the tool. Are new features being added as the product matures? How often are product updates released and any necessary fixes? Does it support the latest database platforms you are working with? And lastly, can you get the needed support in the event you need assistance? You are not just selecting a tool, you are also selecting a vendor that will hopefully be there for you when you need help.
These are just some of the considerations to think about when selecting a tool or tools for your database needs. I think most of the items listed above are things you would use on a daily basis, but I am sure there are other things you may need from time to time. When evaluating and selecting a tool, think about the features you are going to be using the most and whether those features are available in the tool of choice. A good tool can make all the difference in your day to day activities.
Aqua Data Studio
Aqua Data Studio is productivity software for Database Developers, DBAs, and Analysts. It allows you to develop, access, manage, and visually analyze data. Whether you are working with relational, NoSQL or cloud databases, your data is easily and quickly accessible with Aqua Data Studio. It supports all major database vendors, works on all major operating systems and is localized to 9 languages.