Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about what are the excellent SQL Server tools for database development and management, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

For SQL database developers and database administrators (DBA), it is necessary to have handy SQL tools, which can not only greatly reduce the development time of features, but also important for DBA to have monitoring tools.

Today, let's take a look at the tools for SQL Server development and monitoring, which are described in more detail below.

SQL Server Management Studio

The free SSMS has a convenient registration server feature that allows you to add and group the necessary SQL Server instances:

Figure: SSMS Registration Server

You can also create subgroups to send requests to a given group or subgroup, or to a registered configuration server to upload a registered configuration server for further import among all developers for testing and management.

It is also important to note the possibility of multiple edits:

Figure: multiple editing possibilities in SSMS

In SSMS, you can also manage agents and design databases through database diagrams. But the latter is not very friendly.

Also, in SSMS, you can use Activity Monitor to view active queries, but in general, monitoring databases and database servers are not the best choice.

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

There are many other features in SSMS, including: data import and export, performance reports, database engine log shipping configuration and monitoring, configuration and monitoring of AlwaysOn availability groups, configuration and monitoring of query storage, and so on.

DbForge SQL tool

The dbForge SQL tool is a multi-functional toolkit for SQL Server. These tools are helpful to any SQL Server developer because they have been integrated into SSMS and have been more or less extended by visual capabilities so that they can perform many routine tasks. With the help of dbForge DevOps Automation, SQL tools can be used to automate the development and deployment of databases. The package includes:

SQL Complete is one of the most powerful add-ins for writing and formatting SQL queries in SSMS and VS. This utility not only has the functions of SQL code IntelliSense, formatting and refactoring, but also has a large number of useful functions to simplify code writing, formatting and refactoring. This add-in is also integrated with Visual Studio, which is particularly convenient.

Source Control is a powerful SSMS plug-in for managing SQL Server database changes through all popular Source Control systems.

Unit Test is a convenient GUI for unit automation in SSMS.

Schema Compare and Data Compare

Data Generator is an excellent data generator for generating SQL Server test data.

Documenter

Data Pump provides you with convenient data export / import functions.

Index Manager is used for index optimization.

Query Builder, users can build any complex SQL query intuitively.

Search is used to quickly search for objects and data in a database.

Monitor for SQL Server has basic required functions.

Event Profiler

SQL Decryptor

Figure: SQL Complete in the dbForge SQL tool

Figure: Unit Test in the dbForge SQL tool

Figure: database context menu in the dbForge SQL tool

In addition, the package includes dbForge DevOps Automation, which allows SQL Server to implement a comprehensive process of continuous database integration. Users can get a full set of tools for database DevOps.

Experience has shown that the purchase of software packages will greatly reduce the cost of using tools.

Visual Studio and Visual Studio Code

Closely integrated with SSMS is the powerful Visual Studio editor for SQL Server database projects as well as other types of database and T-SQL scripting projects:

Figure: Visual Studio

Visual Studio is typically used for the following main functions: viewing and editing code and the entire database, comparing database schemas and data, and making changes to schemas as necessary; database schema export and import

The main disadvantages of SSMS and Visual Studio are as follows: they do not have all the necessary functions to develop, test, and manage; moreover, some features (even if they exist) do not allow all the necessary tasks to be performed, and the process itself is very time-consuming.

Then there is Visual Studio Code, which is a cross-platform tool for Visual Studio and has only partial functionality.

Figure: Visual Studio Code

DbForge Studio for SQL Server

DbForge Studio for SQL Server is an alternative to SSMS that complements and extends it. Most importantly, the editor implements the following important functions: database logger, database schema comparison and database data comparison, code formatting, source code control, database design, backup and restore, debugger, query analyzer, intelligent perception and so on.

Figure: dbForge Studio for SQL Server

Currently, however, dbForge Studio for SQL Server does not support multiple edits.

Notepad + +

A great simplified and free editor that you can use to view and edit T-SQL scripts. The editor supports multiple programming languages.

Figure: Notepad + +

This editor is especially convenient when T-SQL queries are too large and have thousands of lines of code. Notepad + + has a variety of extensions, the most common of which are SQL code comparison and formatting.

Notepad + + is mainly used to read and edit the contents of a file, as well as to search for fragments in the file and compare the contents of the file.

DBeaver

Another free editor is also available for a paid version. The editor also has some useful functions, such as code formatting, database design, opening dashboards and so on.

Figure: DBeaver

However, DBeaver does not support multi-editing and other useful features for development, testing, and management.

ApexSQL Developer and DBA tools

Now let's take a look at the ApexSQL Developer and ApexSQL DBA tools inserted in SSMS and Visual Studio:

Figure: ApexSQL Developer and DBA tools

Figure: Apex database context menu

ApexSQL Developer workers are used for development and testing. It provides, among other things, the following functions: database architecture and data comparison, SQL code format, comprehensive test data generation, database design, connection to popular version control systems, search objects and data, etc.

The ApexSQL DBA tool is designed for SQL Server management. Among other functions, it also provides the following functions: index optimization, backup and restore, and reading transactions.

ApexSQL Developer, together with DBA tools and dbForge SQL tools, enables us to fully implement continuous integration and delivery processes and organize CI/CD pipelines.

RedGate SQL tool set

The RedGate SQL toolset is capable of developing, testing, and managing databases. In addition, it also provides the following functions: comparison of schema and database data, SQL Server monitoring, search, backup, continuous integration and automatic deployment of SQL Server databases, data generator and so on.

Figure: RedGate menu bar

Figure: context menu for RedGate

Figure: SQL Prompt in the RedGate main menu

RedGate is the most expensive of all mentioned tools and has a wide range of features, but competitive products such as dbForge SQL tools, ApexSQL Developer, and DBA tools are constantly improving and adding new features to them. The above tools are already at the same level as RedGate, or even better than RedGate in some cases, but they are much cheaper.

Spotlight on SQL Server and Foglight for SQL Server

Spotlight on SQL Server has a wealth of features for monitoring database servers, such as collecting the basic characteristics of the server and each database, fine-tuning alerts for each performance indicator, several off-the-shelf templates for performance metrics and alerts, the possibility of creating user performance indicators, and many convenient visual reports including database growth rate reports. Collect and analyze a large number of queries and their performance plans in the context of total performance duration, CPU performance, and the number of records, physical and logical reads.

More advanced solutions such as Spotlight on SQL Server are Foglight for SQL Server.

Figure: Spotlight on SQL Server

Figure: Foglight for SQL Server

Zabbix

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

Unlike Spotlight on SQL Server, Zabbix cannot display queries at a specific time. But Zabbix is an open source solution that can be used to monitor not only database servers, but also network performance and application performance.

Figure: Zabbix

Prometheus

Prometheus is usually used with clients of Grafana. Grafana can also be used with Zabbix, but the latter has a built-in customizable client-side view that displays the results in dashboards and graphics.

Figure: Grafana and Prometheus

Prometheus and Zabbix can be well used for SQL Server monitoring, they fully meet the needs of monitoring databases and servers, and do not provide query details in chronological order. Zabbix can be used without using Grafana, so it can be deployed and set up faster than other solutions. However, Prometheus is more modular and extensible than Zabbix.

After reading the above, do you have any further understanding of the excellent SQL Server tools for database development and management? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.