MS SQL Server is not a rarity in a network of the organizations as often goes “in loading” to the business application. Own tools usually the majority of developers and administrators arrange far not completely. Therefore it is not surprising, that for today the considerable quantity of utilities, applications and addons, including free, on an order simplifying use MS SQL is accessible.
IDERA SQL CHECK
A free monitoring tool that allows you to get basic information about server performance. Collects about twenty indicators: read/write operations, cache, transactions, compilation and recompilation of requests, CPU load and others. The result is displayed in various performance charts and an intuitive visualization of open connections and transactions.
The free version is limited to a single server and displays advertisements for commercial products from the same company. Supports all versions from SQL Server 2000 SP4. The distribution on the website will be available after a simple registration, after which a download link will be sent to the specified email. Installation is standard, you should configure the connection to the SQL server afterwards. After that we can see overview performance charts placed in several tabs.
Clicking on the button near the chart will give you more detailed information. The settings of the program are few, the only thing worth paying attention to is the setting of other intervals of chart updates in the Option tab.
Idera has many more useful free tools for diagnosing MS SQL, which you should get acquainted with: profiler, fragmentation analysis, SQL browsing, statistics aggregator, backup, PowerShell module and scripts and others.
SQLBAK
SqlBak is an interesting solution in modern spirit from the developers of a popular solution for MS SQL backup — SQLBackupAndFTP. It is implemented as an online service, all settings and actions are made in a web browser, which allows you to manage them from any place and device.
The SqlBak Client program that directly performs all operations is installed on the server. Two important administration tasks are performed. The main one is to create MS SQL database backups manually and on schedule and restore operability with just one click in the browser. A full and differential backup and saving of the transaction log is supported. Archives are compressed with zip or 7z.
Files are copied to a local or network folder, external HDD, FTP. And, importantly, cloud storage is also supported — Dropbox, Google Drive, OneDrive and Amazon S3. In addition, server health and performance is monitored. If problems are detected, operation reports are sent to the email specified by the administrator. The execution of scripts before and after backup operation, generation of checksums required to verify the integrity of the archive, verification is supported.
Each database can be copied into a separate subdirectory. You can download the archive with a backup copy or restore it to another server. The administrator can access the history of backups, whose files can be restored or saved.
Three tariff plans have been implemented. In free Free, you can work with only one server and two databases, do not support cloud storages, and are monitored at one hour intervals. Although this is usually sufficient for most small organizations, especially given the free and manageability from any location. The Professional version already has AES encryption for archives, and monitoring is performed every minute. All connections in all plans are protected with SSL.
To register in SqlBak you only need to have an account in one of the social networks (Facebook, Twitter or Google). To connect the agent, you will need a key that is generated from the Secret Key link. The client program has almost no settings, and after connecting to SqlBak you can forget about it. The software is updated automatically.
All configuration, monitoring and recovery actions are performed exclusively through SqlBak.com website. There are few tabs and settings and their purpose is quite obvious. Backup operations are displayed in Dashboard. By selecting any task, you can view detailed information. When creating a job there is nothing complicated, the same settings as via SSMS. You need to specify your computer, SQL server name and credentials, and then you will get a list of databases.
Then we specify where to copy, compression options, email and other parameters. There is also a demo account that allows you to familiarize yourself with the basic features without deploying SqlBak.
SQLFUSE
In large and medium sized projects a considerable part of business logic is implemented in the DBMS stored procedures, therefore the convenience of code management comes to the fore. The available tools, even commercial ones, do not always allow managing versions and tracking changes to the full extent, conveniently synchronizing the test and work infrastructure and navigating and searching by code.
This task is very interesting and well solved by the SQLFuse project, which displays SQL-server objects on the file system: schemes, tables, views, stored procedures, functions, columns, triggers and more. Though at present creation, editing and deleting is supported partially. All the changes made in the files are accumulated in the cache, and the SQL commands are reset in the database by timer.
If the transaction fails, all the made alterations are rolled back and the cache is cleared. It is based on the userspace file system FUSE used in *nix. Therefore you will need a computer with any Linux distribution for deployment. The build is standard, after which you need to configure the profile, i.e. connection to the SQL server in the file sqlfuse.conf, and authorization (login/password) in sqlfuse.auth.conf.
There can be several profiles in the file, which allows you to work with multiple databases. Next, just mount the SQL-server in the directory:
$ sqlfuse -o profilename=SQLServer ./sqlserver
After that you can work with files inside the directory with standard utilities *nix — vi, cat, ms and so on. For ease of use as a tool for the server you can befriend SQLFuse with Git.
SQL SENTRY PLAN EXPLORER
It often happens that some requests are slow, although there shouldn’t be any problems at all. In this case, the situation needs to be investigated more deeply. SQL Profiler and Management Studio provide a very good interface for studying queries and execution plans (Execution Plan), but its information is not always obvious.
Here comes the SQL Sentry Plan Explorer product, which was originally developed for SQL Sentry support, but later the company decided to make it available to others. It allows you to view the execution plan in different ways and easily switch between multiple queries, evaluate and configure queries using intuitive analysis of the execution plan.
The program evaluates the query, resource utilization (I/O intensity, CPU), determines the amount of data, number of rows, and the result is clearly displayed as a table and chart showing the hierarchical representation of the query plan, where heavy queries and unbalanced thread distribution in parallel operations can be quickly identified.
The diagram shows main tables and columns visualizing relations between them. By editing the query, we can analyze the change, comparing the result. A separate tab shows the expressions used in the query and their analysis allows you to see implicit transformations that can slow down its execution. Data can be sorted as needed to focus on the currently important ones.
It can be installed as a separate application and as an addon to an SSMS. Available in two versions: free FREE and commercial PRO. Distributed as a single file that includes both versions. If you do not enter your license key after fifteen days, the program’s features are automatically converted to Free (you can choose the Free option after installation).
SQLS*PlUS
Another very important tool to work with Microsoft SQL Server. SQLS*Plus — best Microsoft SQL Server command line reporting and automation tool.
The developer, the company “Memfix SQLS*Plus”, offers both the free and paid version with a large increase in functionality, depending on the selected tariff.
SQLS*Plus is a universal and extremely effective SQL tool for dba development and production. It is also a very effective tool for solving complex reporting and automation tasks in SQL Server. The work, which usually takes several days, can be done in a few minutes.
SQLS*Plus for SQL Server allows you to flexibly format data using predefined strings, page size commands, report headers, report data grouping, aggregation and many other extremely useful report formatting features.
SQLS*Plus supports the use of variables (var, define, &, &&), interactively accepts user input to variables, and spools data into TEXT, HTML, CSV and JSON output. Many other advanced functions such as BREAK and COMPUTE are available. This functionality is not available in standard SQL Server tools.
Easy and fast to use, SQLS*Plus lets you convert and fine-tune Oracle SQL*Plus scripts for transparent use in SQL Server environment to ensure maximum performance and easy conversion.
SQLS*Plus allows you to merge Oracle SQL*Plus scripts with SQL Server development process in the most efficient way. SQLS*Plus provides integration of SQL reports and service scripts into database applications for each Oracle database migration and deployment to Microsoft SQL Server.
Compliance with scripting requirements is an important key performance indicator in general compliance with Oracle requirements to migrate to SQL Server, and rest assured that SQLS*Plus makes every effort to work without breaking existing Oracle SQL*Plus scripts.
