Free tools for database developers
After reading the article about useful and free plugins for SSMS - TOP (10) free plugins for SSMS , I decided to share my list of free tools that can be very useful to database developers, and not only. Tools focused on development for Microsoft SQL Server . Some tools are paid, but they contain fairly full-featured free editions, which are enough for small projects.
A powerful development environment for SQL Server from Devart. This product includes a huge set of tools that significantly speed up and facilitate everyday tasks: advanced IntelliSence, visual table and query designers, database diagrams, T-SQL debugger, query profiler, data and schema comparison, test data generation, work with source control, unit-test support, backup creation, and much more. A sort of Swiss knife for the developer.
The price of the English version of this tool is almost $ 700. However, there is an express edition that is free. It is clear that the functionality in it is cut down, and it may not be enough to complete the work. But do not be upset, because there is a Russian-language version of the product, which is what is called - FULL-FEATURED. It is absolutely free for non-commercial use. If you have an open-source project, if you want to work with databases at home, if you represent an educational institution - this tool is for you!
When creating custom functions, the WITH ENCRYPTION parameter is used, which encodes information in the system catalog that contains the text of the CREATE FUNCTION instruction, thus preventing unauthorized access to viewing the text of the function creation. Then in the future, without having the source code, and such a tool, it is impossible to view the object DLL.
')
A free tool that allows you to monitor and analyze SQL Server events. You can use this information to identify and fix many problems.
A free web service that allows you to format T-SQL code. You can write the code directly in the editor, and then format it, or load it from an external file.
A tool that allows you to format and refactor SQL code. It has a large number of settings. See this link for formatting examples.
A free tool (a plugin for SSMS) that can significantly reduce time and save nerves when writing T-SQL code. The tool offers: all sorts of object hints, auto completion of paragraphs, snippets, navigation to objects from code, logging of every executed query, pick sheets of objects, and much more!
A free set of 28 must – have scripts from Redgate experts to help automate common troubleshooting, troubleshooting, and maintenance tasks. The most popular scripts are: CPU – intensive queries, SQL Server Maintenance Solution, Top resource waits, Blocking transactions, and many others.
Monitoring server performance in real time. The tool allows you to track 20 key performance metrics: read / write operations, cache, transactions, compilation and recompilation of requests, CPU utilization, and others. Results are displayed in the form of performance graphs, visualization of open connections and transactions. The free version is limited to only one server. By the way, there is a whole set of useful free tools on the company's website.
A set of stored procedures from MVP Ola Hallengren for maintaining both user and service databases on the side of SQL Server. Scripts are accompanied by instructions and examples. In 2010, this set of scripts won the Editor's Choice Bronze Award, a bronze award, as the best free tool. In 2011 I received silver and gold from the community.
sp_Blitz is a stored procedure for monitoring the state of SQL Server – a and performance problems. When executing the procedure with default parameters, you will receive a list of problems for the specified instance of the SQL server arranged in order of importance, a sort of to-do list. The most critical moments have priority 1, and further ascending. To use, you must run sp_Blits.sql, which will deploy the necessary procedures in the master. Next, in the new sql document we execute sp_Blitz.
sp_BlitzCache is a stored procedure, with a bunch of options that shows the most ineffective queries, and much more. For example, the following query:
Will give you the top 10 queries, the execution of which takes the most time. For example, if a query is executed 10 seconds and is called 1000 times, then the total execution time will be 10,000 seconds. In the "table of ranks" such a request will be higher than a request that is executed 500 seconds, but only once. More details can be found here: Using sp_BlitzCache Advanced Features
Brent Ozar :
sp_BlitzIndex is a tool that helps you analyze indexes: find out if you have duplicate indexes, or indexes that are not used, and much more.
sp_BlitzRS is a procedure for finding potential problems and analyzing the performance and queries of SQL Server Reporting Services. The procedure will help you answer questions such as:
The procedure will quickly check activity on SSRS and will provide recommendations for improving the situation. Each item contains a link to the site, with a detailed description and ways to solve the problem, which will allow to better understand the situation. Video about the procedure.
sp_BlitzTrace is a stored procedure that allows you to quickly and easily use SQL Server Extended Events. It will show how many processor resources a particular query uses, how many logical reads it uses, which temporary objects are created, and so on.
An online service from the developers of the popular SQLBackupAndFTP backup solution. The tool allows you to create MS SQL backups manually or by schedule (full and differential backup and transaction log saving are supported.). It also monitors the health and performance of the server. When problems are detected, reports are sent to the administrator by email.
All settings are made in the browser, which allows you to control the tool from any device. On the server you need to install SqlBak Client, which will perform all operations.
In general, the tool is paid, but there is a free plan. In this regard, you can work with one server and two databases. Monitoring is performed once a hour, which is sufficient for small projects.
A significant part of business logic in large projects is implemented in stored procedures and the convenience of managing code is one of the important tasks. Not all available tools allow you to conveniently work with versions, monitor changes, synchronize test and production environments. To solve such problems, the SQLFuse project can help you. This is a user-mode file system based on FUSE that maps SQL server objects to a file system: schemas, tables, views, stored procedures, functions, columns, triggers, etc. In addition to displaying objects, SQLFuse partially supports their creation, editing, and deletion.
All changes in the files are accumulated in the cache, and the SQL-commands are reset in the database using the counter. If a transaction fails, all changes are rolled back and the cache is cleared.
Wonderful free open source framework for conducting T-SQL unit tests. How to work with this tool is described in great detail in the following article: tSqlt - unit testing in Sql Server
Plug-in for Visual Studio c open source. The tool significantly expands SQL Server BI Development Studio 2005, 2008, or 2008 R2, as well as SQL Server Data Tools BI 2012, 2014. A list of all available features can be found in the product documentation .
This tool allows you to work with partitioned (partitioned) tables.
I hope that this selection will be useful to you, and you will use these tools in your practice. If you have ideas for adding this list, please write in the comments.
Thanks for attention!
dbForge Studio for SQL Server
A powerful development environment for SQL Server from Devart. This product includes a huge set of tools that significantly speed up and facilitate everyday tasks: advanced IntelliSence, visual table and query designers, database diagrams, T-SQL debugger, query profiler, data and schema comparison, test data generation, work with source control, unit-test support, backup creation, and much more. A sort of Swiss knife for the developer.
The price of the English version of this tool is almost $ 700. However, there is an express edition that is free. It is clear that the functionality in it is cut down, and it may not be enough to complete the work. But do not be upset, because there is a Russian-language version of the product, which is what is called - FULL-FEATURED. It is absolutely free for non-commercial use. If you have an open-source project, if you want to work with databases at home, if you represent an educational institution - this tool is for you!
dbForge SQL Decryptor
When creating custom functions, the WITH ENCRYPTION parameter is used, which encodes information in the system catalog that contains the text of the CREATE FUNCTION instruction, thus preventing unauthorized access to viewing the text of the function creation. Then in the future, without having the source code, and such a tool, it is impossible to view the object DLL.
')
dbForge Event Profiler for SQL Server
A free tool that allows you to monitor and analyze SQL Server events. You can use this information to identify and fix many problems.
SQL Formatter
A free web service that allows you to format T-SQL code. You can write the code directly in the editor, and then format it, or load it from an external file.
ApexSQL Refactor
A tool that allows you to format and refactor SQL code. It has a large number of settings. See this link for formatting examples.
ApexSQL Complete
A free tool (a plugin for SSMS) that can significantly reduce time and save nerves when writing T-SQL code. The tool offers: all sorts of object hints, auto completion of paragraphs, snippets, navigation to objects from code, logging of every executed query, pick sheets of objects, and much more!
SQL Scripts Manager
A free set of 28 must – have scripts from Redgate experts to help automate common troubleshooting, troubleshooting, and maintenance tasks. The most popular scripts are: CPU – intensive queries, SQL Server Maintenance Solution, Top resource waits, Blocking transactions, and many others.
SQLCheck
Monitoring server performance in real time. The tool allows you to track 20 key performance metrics: read / write operations, cache, transactions, compilation and recompilation of requests, CPU utilization, and others. Results are displayed in the form of performance graphs, visualization of open connections and transactions. The free version is limited to only one server. By the way, there is a whole set of useful free tools on the company's website.
SQL Server Maintenance Solution
A set of stored procedures from MVP Ola Hallengren for maintaining both user and service databases on the side of SQL Server. Scripts are accompanied by instructions and examples. In 2010, this set of scripts won the Editor's Choice Bronze Award, a bronze award, as the best free tool. In 2011 I received silver and gold from the community.
Brent Ozar Unlimited Stored Procedure Kit
sp_Blitz is a stored procedure for monitoring the state of SQL Server – a and performance problems. When executing the procedure with default parameters, you will receive a list of problems for the specified instance of the SQL server arranged in order of importance, a sort of to-do list. The most critical moments have priority 1, and further ascending. To use, you must run sp_Blits.sql, which will deploy the necessary procedures in the master. Next, in the new sql document we execute sp_Blitz.
sp_BlitzCache is a stored procedure, with a bunch of options that shows the most ineffective queries, and much more. For example, the following query:
EXEC sp_BlitzCache @top = 10, @sort_order = 'duration' Will give you the top 10 queries, the execution of which takes the most time. For example, if a query is executed 10 seconds and is called 1000 times, then the total execution time will be 10,000 seconds. In the "table of ranks" such a request will be higher than a request that is executed 500 seconds, but only once. More details can be found here: Using sp_BlitzCache Advanced Features
Brent Ozar :
This is the Suckerboard.
sp_BlitzIndex is a tool that helps you analyze indexes: find out if you have duplicate indexes, or indexes that are not used, and much more.
sp_BlitzRS is a procedure for finding potential problems and analyzing the performance and queries of SQL Server Reporting Services. The procedure will help you answer questions such as:
- What kind of subscriptions did the employee have (for example) when he was fired?
- What is the reason for slow server operation? Is there a problem with slow queries or does the query return a lot of data?
- What is better to use for the report: caching or snapshot?
- Do reports use “dangerous” settings?
The procedure will quickly check activity on SSRS and will provide recommendations for improving the situation. Each item contains a link to the site, with a detailed description and ways to solve the problem, which will allow to better understand the situation. Video about the procedure.
sp_BlitzTrace is a stored procedure that allows you to quickly and easily use SQL Server Extended Events. It will show how many processor resources a particular query uses, how many logical reads it uses, which temporary objects are created, and so on.
Sqlbak
An online service from the developers of the popular SQLBackupAndFTP backup solution. The tool allows you to create MS SQL backups manually or by schedule (full and differential backup and transaction log saving are supported.). It also monitors the health and performance of the server. When problems are detected, reports are sent to the administrator by email.
All settings are made in the browser, which allows you to control the tool from any device. On the server you need to install SqlBak Client, which will perform all operations.
In general, the tool is paid, but there is a free plan. In this regard, you can work with one server and two databases. Monitoring is performed once a hour, which is sufficient for small projects.
SQLFuse
A significant part of business logic in large projects is implemented in stored procedures and the convenience of managing code is one of the important tasks. Not all available tools allow you to conveniently work with versions, monitor changes, synchronize test and production environments. To solve such problems, the SQLFuse project can help you. This is a user-mode file system based on FUSE that maps SQL server objects to a file system: schemas, tables, views, stored procedures, functions, columns, triggers, etc. In addition to displaying objects, SQLFuse partially supports their creation, editing, and deletion.
All changes in the files are accumulated in the cache, and the SQL-commands are reset in the database using the counter. If a transaction fails, all changes are rolled back and the cache is cleared.
tSQLt
Wonderful free open source framework for conducting T-SQL unit tests. How to work with this tool is described in great detail in the following article: tSqlt - unit testing in Sql Server
Bids helper
Plug-in for Visual Studio c open source. The tool significantly expands SQL Server BI Development Studio 2005, 2008, or 2008 R2, as well as SQL Server Data Tools BI 2012, 2014. A list of all available features can be found in the product documentation .
SQL Server Partition Management
This tool allows you to work with partitioned (partitioned) tables.
I hope that this selection will be useful to you, and you will use these tools in your practice. If you have ideas for adding this list, please write in the comments.
Thanks for attention!
Source: https://habr.com/ru/post/273073/
All Articles