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:
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:
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:
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:
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:
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.
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:
Visual Studio is usually used for the following main functions:
Among the main disadvantages of SSMS as well as Visual Studio, are the following ones:
They do not have all the necessary functions for development, testing, and administration
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:
Let us now look at the dbForge Studio for SQL Server editor:
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:
Unfortunately, for the present moment, dbForge Studio for SQL Server does not support multi-editing.
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:
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.
One more free editor, DBeaver:
There is a paid version as well.
This editor, among all other things, has useful functions such as:
Let’s now look at ApexSQL developer tools and ApexSQL DBA tools, which are plugged in SSMS and in Visual Studio:
ApexSQL developer tools are designed for development and testing. It provides, among other things, the following functions:
ApexSQL DBA tools are designed for SQL Server administration. Among others, it provides the following functionality:
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.
Let us now look at RedGate SQL Toolbelt:
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.
At the end of the overview of the SQL Server tools, we will briefly go over the most popular systems for SQL Server monitoring.
Let us start by looking at Spotlight on SQL Server
This tool is quite rich in functionality for monitoring database servers and has, among others, the following functions:
A more advanced solution for SQL Server monitoring is Foglight for SQL Server:
Let us now look at 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.
Let us now consider 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.
The main tools for working with SQL Server databases are:
SSMS
Visual Studio
They can be extended by third-party plugins to cover all development, testing and administration needs:
dbForge SQL Tools
ApexSQL developer tools и ApexSQL DBA tools
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:
To monitor queries and query history, the following tools are often used:
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:
Originally posted at https://codingsight.com/best-sql-tools-for-database-developers-and-administrators/
Author - Evgeniy Gribkov
#sql #sql-server #database