Best SQL Tools for Database Developers and Administrators

For a SQL database developer, it is essential to have the necessary SQL tools in hand, which enable to reduce the development time of given functionality. The same is true for the tester. For a database administrator, it is also important to have monitoring tools.

In this article, we are going to look at some MS SQL Server development and monitoring tools, such as:

1. SQL Server Management Studio
2. dbForge SQL Tools
3. Visual Studio and Visual Studio Code
4. dbForge Studio for SQL Server if necessary
5. Notepad ++
6. DBeaver
7. ApexSQL Developer and DBA tools
8. RedGate SQL Toolbelt
9. Spotlight on SQL Server и Foglight for SQL Server
10. Zabbix
11. Prometheus

SQL Server Management Studio

Let us begin with a free SSMS application. The functionality for development can be found here.

To add, there is a convenient function of Registered Server, which allows you to add the necessary MS SQL Server instances and also group them:

Fig.1. Registered Server for SSMS

It is also possible to create subgroups, to send requests to a given group, or a subgroup, or to a certain registered configured server, to upload registered configured servers for further import between all developers, for testing and administration.

It is also important to note the possibility of multi-editing:

Fig. 2. Multi-editing possibility in SSMS

In SSMS, there is also a possibility of managing the Agent and designing databases through a database diagram. But the latter isn’t very user-friendly.

What is more, in SSMS, it is also possible to see active queries using Activity Monitor, but in general, it isn’t the best option to monitor a database and a database server.

Also, to monitor performance, you can use Profiler and Extended events in SSMS.

There are a bunch of other functions in SSMS, including the following:

1. data import and export
2. performance reports
3. database engine log shipping configuration and monitoring
4. configuring and monitoring AlwaysOn availability groups
5. configuring and monitoring query store

and many more.

dbForge SQL Tools

dbForge SQL Tools is a multi-functional toolkit for SQL Server. The tools are useful for any Microsoft SQL Server developer because they are integrated into SSMS, expanding it with the missing functionality, to perform a lot of routine tasks. SQL Tools can be used to automate development and deploy databases with the help of dbForge DevOps Automation. The package includes:

• SQL Complete, which is one of the most functional add-ins, used to write and format SQL queries in SSMS and VS. This utility has not only functions of SQL code IntelliSense, formatting, and refactoring, but also a huge number of useful features that simplify code writing, formatting, and refactoring. This add-in also integrates with Visual Studio, which is particularly convenient.
• Source Control is a powerful SSMS add-in for managing SQL Server database changes via all popular source control systems.
• Unit Test is a convenient GUI for unit automation in SSMS.
• Schema Compare and Data Compare
• an excellent Data Generator for generation SQL Server test data.
• Documenter
• Data Pump, which provides you with a convenient functionality for data export/import.
• Index Manager, which is used for index optimization.
• Query Builder allows users to visually build SQL queries of any complexity.
• Search is used for a quick search for objects and data in the database.
• Monitor for SQL Server has basic required functionality.
• Event Profiler
• SQL Decryptor

Fig. 3. SQL Complete in dbForge SQL Tools

Fig. 4. Unit Test in dbForge SQL Tools

Fig. 5. Database context menu from dbForge SQL Tools

Apart from this, the package includes dbForge DevOps Automation, whose functionality allows you to implement a comprehensive database continuous integration process for SQL Server. In this way, a user gets a full set of tools for database DevОps.

You can buy all tools from the package and install them separately, but experience has proven that buying the package will reduce the cost of the tools substantially.

Visual Studio Editor for SQL Server

Hand in hand with SSMS goes the powerful Visual Studio editor for SQL Server Database Project projects and other types of projects for databases and T-SQL scripts:

Fig. 6. Visual Studio

Visual Studio is usually used for the following main functions:

1. Viewing and editing code and the database as a whole
2. Comparing database schemas and data, and making changes to the schema if necessary
3. Database schema export and import

Among the main disadvantages of SSMS as well as Visual Studio, are the following ones:

1) They do not have all the necessary functions for development, testing, and administration

2) Some of the capabilities, even if present, do not allow to perform all required tasks, and the process itself is very time-consuming.

There is also Visual Studio Code, which a cross-platform analogue of Visual Studio and which has only part of its functionality:

Fig. 7. Visual Studio Code

dbForge Studio for SQL Server

Let us now look at the dbForge Studio for SQL Server editor:

Fig. 8. dbForge Studio for SQL Server

This solution is an alternative to SSMS, which complements and expands it. On top of that, this editor has well-implemented such important functions as:

1. Database Documenter
2. Database Schema Compare and Database Data Compare
3. Unit Test
4. Code Formatting
5. Source Control
6. Database Designer
7. Index Manager
8. Data Generator
9. Search
10. Monitor
11. Backup and Restore
12. Debugger
13. Query Profiler
14. Intellisense

and lots more.

Unfortunately, for the present moment, dbForge Studio for SQL Server does not support multi-editing.

Notepad ++

We can not but mention a wonderful simplified and free editor which is Notepad++. You can use it to view and edit T-SQL scripts. The editor supports multiple programming languages:

Fig. 9. Notepad++

This editor is especially handy when a T-SQL query is too large and it has many thousands and dozens of thousands of code lines because SSMS can freeze even when it displays a big chunk of code. There are various extensions for Notepad++, the most frequently used ones are SQL code comparison and formatting. You can also search for files with the help of Notepad++.

We won’t name the drawbacks of Notepad++, as it is mainly used for reading and editing the content of a file, and also for searching for a fragment in files and comparing the contents of files.

DBeaver

One more free editor, DBeaver:

Fig. 10. DBeaver

There is a paid version as well.

This editor, among all other things, has useful functions such as:

1. Code formatting
2. Database design
3. Open Dashboard
4. Search
5. Transaction Log

However, this editor does not support multi-editing and some other useful functions for development, testing, and administration.

ApexSQL Developer and DBA tools

Let’s now look at ApexSQL developer tools and ApexSQL DBA tools, which are plugged in SSMS and in Visual Studio:

Fig. 11. ApexSQL developer and DBA tools

Fig. 12. Apex database context menu

ApexSQL developer tools are designed for development and testing. It provides, among other things, the following functions:

1. Database schema and data comparison
2. SQL code formatting
3. Database documentation
4. Synthetic test data generation
5. Unit testing
6. Database design
7. Connecting to popular version control systems
8. Search for objects and data

ApexSQL DBA tools are designed for SQL Server administration. Among others, it provides the following functionality:

1. Index optimization
2. Backup and recovery
3. Reading transaction log

Together, ApexSQL developer tools and ApexSQL DBA tools, as well as dbForge SQL Tools, allow us to fully implement continuous integration and delivery processes and to organize ci/cd pipeline.

RedGate SQL Toolbelt

Let us now look at RedGate SQL Toolbelt:

Fig. 13. RedGate menu bar

Fig. 14. SQL Prompt in the main menu of RedGate

Fig. 15. Context menu of RedGate

This tool is designed to develop, test, and administer databases. It provides, among other things, the following functions:

     1) Comparison of schemas and database data

     2) SQL Server monitoring

     3) Search

     4) Backup

     5) Documentation of databases

     6) Index Manager

     7) Analysis and formatting of the code

     8) Continuous integration and automatic deployment of SQL Server database

     9) Data generator

and much more.

As you can see, RedGate is the most expensive of all mentioned tools and it has a wide range of functionalities, but such competitive products as dbForge SQL Tools, ApexSQL Developer and DBA tools are constantly being improved, and new functionality is added to them. Therefore, today it is safe to say that such products as dbForge SQL Tools, ApexSQL Developer and DBA tools are on the level with, and in some cases even superior to RedGate, yet they are much cheaper.

SQL Server Monitoring Tools

At the end of the overview of the SQL Server tools, we will briefly go over the most popular systems for SQL Server monitoring.

Spotlight on SQL Server and Foglight for SQL Server

Let us start by looking at Spotlight on SQL Server

Fig. 16. Spotlight on SQL Server

This tool is quite rich in functionality for monitoring database servers and has, among others, the following functions:

1. collect basic characteristics of both the server and each database
2. fine-tuning alerts for each performance metric
3. several ready-made templates for performance metrics and alerts
4. a possibility to create user performance metrics
5. many convenient visual reports, including database growth rate reports
6. collection and analysis of heavy queries and their performance plans in the context of the total performance duration, of CPU performance, and of the number of the records, physical and logical reads
7. analysis of index fragmentation
8. a possibility to have a detailed look into every moment of time when the messages or alerts appeared, including details on queries and their performance plans (Playback)

and a lot more

A more advanced solution for SQL Server monitoring is Foglight for SQL Server:

Fig. 17. Foglight for SQL Server

Zabbix

Let us now look at Zabbix:

Fig. 18. Zabbix

This product for monitoring provides a wide range of performance metrics and their triggers, as well as templates for them, different graphics, dashboards, and a wide variety of functionality, most of which can be customized.

The monitor has one major drawback. Zabbix, unlike Spotlight on SQL Server, can’t show queries at the specific point in time. But Zabbix is an open-source solution that allows you to monitor much more than just a database server.

Prometheus

Let us now consider Prometheus:

Fig. 19. Grafana and Prometheus

Prometheus is usually used with the client side of Grafana.

Grafana can also be used for Zabbix, but the latter has a built-in customizable client side view to display the results in the form of dashboards and graphs.

Prometheus and Zabbix work fine for SQL Server monitoring and they fully cover the needs for monitoring databases and servers without giving details of queries in chronological order. Zabbix can be used without Grafana, so it can be deployed and set up faster than other solutions. However, Prometheus is much more modular and extendable than Zabbix.

Conclusion

The main tools for working with SQL Server databases are:

1. SSMS
2. Visual Studio

They can be extended by third-party plugins to cover all development, testing and administration needs:

1. dbForge SQL Tools
2. ApexSQL developer tools и ApexSQL DBA tools
3. RedGate SQL Toolbelt

or other similar systems.

These solutions also allow us to fully support the DevOps process.

To monitor database servers, I usually use the following systems:

1. Zabbix
2. Prometheus и Grafana

or other similar systems.

To monitor queries and query history, the following tools are often used:

1. Spotlight on SQL Server
2. Foglight for SQL Server
3. SQL Server Query Store

or other similar systems.

There are also a number of additional tools that partially or completely replace SSMS depending on the tasks that are solved, working with SQL Server:

1. dbForge Studio for SQL Server
2. Visual Studio Code
3. DBeaver

The Notepad++ editor is handy when editing content, searching, or comparing very large files.