Welcome to the second edition of "Free Tools for the SQL Server DBA". It has been a while since the original article was published in May 2007. Since that time, the number of free tools for SQL Server has continued to grow even though some have disappeared. This article covers tools not included in the original article.

All tools listed are free with no known expiration date. In order to download them, some sites will require you to register which might result in a phone call or e-mail from the vendor. Some tools are free versions of vendor's purchasable software with features disabled. While others tools are on par or better than what you can purchase.

Administration

• SQL Permissions from Idera (requires registration). Create SQL statements that duplicate the security for a login, database, or instance.

  Note: It is very useful when duplicating security from an existing login to grant to new logins.

• SQL2005 Service Manager by Jasper Smith. This tool is comparable to the SQL Server Manager tool that was removed from SQL Server 2005 and 2008. It creates an icon in the system tray of the taskbar, which allows you to view the status of the SQL Server services along with options to manage those services.

• Scriptio by Bill Graziano is an open source tool that generates the schema for tables and other objects in a SQL Server 2005 or higher databases. It offers a variety of options such as saving each object in its own file.

• SQLSafe freeware edition from Idera (requires registration). This free version of Idera's SQL Server backup software has no support, no encryption, and missing some other features.

  Note: Because of its lack of support, I would not recommend using the freeware version of this tool for production backups and other key backups. You might find it useful for moving test databases between servers or on non-production servers tight on space. Only use this backup tool with your manager's permission. Make sure your manager is well aware that support is not provided for the freeware version and must be purchased.

• ExpressMaint by Jasper Smith. This tool emulates for SQL Express the maintenance plans functions that were originally available with SQL Server 2000. There are two versions of this tool; one uses a stored procedure and the other a SMO executable. Both offer similar functions.

  Note: I prefer the SMO executable for creating windows command files for scheduling through Window's task scheduler. The stored procedure can also be executed through a command line or command file using SQLCMD.

Database Compares

• StarInix SQL Compare creates an HTML report of differences between online databases and snapshots of databases. No SQL is generated to promote changes. This tool's help panel provides step-by-step instructions.

• SQL Accord Community Edition for Microsoft SQL Server also referred to as "SQL Effects Clarity CE" from SQL Effects Software (e-mail registration required). This tool compares the schema of two databases. Specific tables, indexes, etc..., can be selected for comparison. The differences are displayed but no report option is included in this version. No SQL is generated to promote changes. It supports SQL Server versions 7.0, 2000, 2005, and MSDE.

  Note: This tool is handy when reviewing the schema differences in databases.

• DBDiff by Safi is an open source database properties & schema comparison tool for SQL Server 2005 that generates scripts to synchronize database. It supports database comparisons between SQL Server 2000 and 2005. It includes many comparison options. In addition, it provides an excellent interface for browsing the SQL scripts for most database elements such tables, indexes, triggers, functions, views, and stored procedures.

  Note: I found it difficult to promote only one of the changes that were found.

  Warning: Carefully review all generated SQL used to promote database changes and backup your database prior to applying any changes. I make this recommendation for all such tools.

• Open DBDiff by Grimpi (Requires .NET 3.5) is an open source database comparison tool for SQL Server 2005 that generates synchronization SQL. It has options to customize the comparison and SQL generations. The database differences are identified at a high level. The synchronization SQL it generates can be copied to a query tool to be executed after you review it.

  Note: Installing .NET 3.5 broke the SQL Server 2005 services on the Windows computer that I installed this tool on. The logon for the services lost its authority to "Logon On As A Service". To fix the problem, I reenter the logon password for the service using services.msc.

  Warning: Carefully review all generated SQL used to promote database changes and backup your database prior to applying any changes. I make this recommendation for all such tools.

Monitoring

• SQL SPY by Hybridx provides 75 features for SQL Server 2000 and 2005 instances. Most of these features are reports. The GUI interface provides easy access to all its features. Seven live monitoring tools provide real-time or near real-time monitoring of SQL Server activity and performance.

  Note: My favorite feature is "Database Activity" which identifies the busiest database. You think you know which database is the busiest on a server. Try this tool and see if you are right.

• SQL HeartBeat by SQL Solution is free with optional registration. The tool provides six real time monitoring tools for SQL Server. It offers a quick means to check the health of a SQL Server instance.

• SQL Internal Viewer by David Gould. This tool shows how SQL Server stores tables, indexes, etc in database files. This offers a DBA the chance to see where in a file the table data is stored. All sorts of detailed information is accessible through the GUI interface. The website's quick start provides an overview on how to use this tool.

  Note: This tool does not work on SQL Server 2000 or older databases even if they are running in a SQL Server 2005 instance.

SQL Server Management Add-ons

• Sorting SQL Project Files in SQL Server Management Studio by Jasper Smith. This tool compliments SQL Server Management Studio (SSMS). Once added to SSMS, it allows you to sort the query files of the opened packages.

• SSMS Tools Pack adds eleven features to SQL Server Management Studio. These features include: generate inserts, search execution plans, convert case, store procedure generation, and five more features.

Administration/Query Tools

• Microsoft SQL Server Management Studio Express is Microsoft's free version of management studio for use with SQL Express. It lacks a few features such as Notifications Services, SQL Agent, and SQLServerManager.msc.

  Note: This tool is a good alternative to SSMS because of its smaller installation file size and is packaged with the latest service pack.

• SQLDeveloper by Oracle (registration required). This free graphical tool is for Oracle but can be used to query and export SQL Server tables. It has seven formats to choose from.

  Note: It uses JDBC, so it can be a challenge to setup. However, we are DBA's and challenges like these do not deter us. After all, if an Oracle developer can get it to work, why not a SQL Server DBA. (-:

• SQuirrel SQL Client also referred to as a Universal SQL Client is an open source Java program that allows you to view the database structures, execute SQL, view connections, and monitor SQL Server performance counters.

  Note: I used a JDBC ODBC connection with a SQL Id to connect to a SQL Server instance. It definitely does not look like SSMS or Enterprise Manager. If you have SQLDeveloper working this one might be easier.

File Editors/Viewers

• PsPad and NotePad++ are two full feature editors that offer similar features in editing a large number of files types such as SQL, VBScript, HTML, XML, and windows command files. Their support for SQL includes a syntax helper. PsPad goes little further by including a SQL Formatter. The file compare tools in each are great for comparing SQL. They each have many free add-ons such as spell checker. Notepad++ even has a text reader add-on. Portable versions of these editors are available so no installation is required.

  Note: These are both great editors that any developer would love to use.

• Microsoft XML Notepad is an excellent editor for XML files.

• Universal Viewer provides quick viewing of large files that many editors just cannot handle. It supports a variety of file types and is great for viewing large export files.

• Log Parser by Microsoft (forum) - Log parser is an all purpose query tool for text-based data files such as log files, XML files, CSV files, Event Log, registry, and more. The syntax can be challenging, but documentation is included with the tool. In addition, there is an active forum with many examples available.

File Compression

• IZArc and 7-Zip are two file compression tools that break the 4 GB limit used by older versions of WINZIP. They are not as fast as the latest release of WINZIP but you can't beat the price. In addition, they have portable versions, so no installation required.

System Information

• WinAudit from Parmavex. It creates a lengthy report; detailing software, licenses, security, hardware, network settings, etc...

• CPU-Z by Franck Delattre from CPUID. Provides very detail information on the CPU, motherboard, and memory.

System Monitoring

• AccessEnum by Bryce Cogswell from Sysinternals (Microsoft). It allows you to view all of file system and registry security settings.

• DiskMon by Mark Russinovich from Sysinternals (Microsoft). Displays and logs all disk activity.

• Process Monitor by Mark Russinovich and Bryce Cogswell from Sysinternals (Microsoft). Monitors file system, registry, process, thread, and DLL activity in real-time.

Task Manager

• Process Explorer (portable) by Mark Russinovich from Sysinternals (Microsoft). A feature rich version of task manager that does everything that Windows task manager does plus much more. Is very useful at finding processes locking files you are trying to replace, delete, move, or rename.

• DTaskManager (portable) by Dimitrious Coutsoumbas from D-Software. This is a Task Manager tool with extra functions. Its clear clean interface makes it easier to use than Windows Task Manager and supports Windows 2000/XP/Vista.

Screen Snapshot

• GreenShot - easy to use screen snapshot. The tool has an editor taht allows you to add comments, shapes, arrows, etc...

  Warning: When first executed, this tool registers itself up for automatic startup. Just disable this in its settings.

• PicPick - provides more features than GreenShot and some differences in the objects you can add to an image. Some of its extra features include color picker, pixel ruler, protractor, crossbar, and whiteboard. The whiteboard feature turns your screen into a whiteboard allowing you to scribble over it with your mouse cursor acting as a marker.

Conclusion

At the time this article was written, all listed tools were available and free. I hope you find this list helpful. It is really a collaboration of the different resources mentioned below as well as tools I use.

Additional Resources

• The discussion for the article "Free Tools for the SQL Server DBA" contains many responses with free tools recommended by other DBAs & Developers.
• Gizmo's Tech Support Alert Wiki- a wiki site from the creator of the "46 Best-ever Freeware Utilities" newsletter. With over 40 contributors, it offers freeware reviews and much more to feed the techie inside each of us.
• Freeware Wiki - provides freeware reviews, tips, website links, and free weekly newsletter.
• Portable Freeware - this site provides reviews and links to portable freeware. Portable applications can be extremely helpful when working with servers because you do not install anything extra on a server. Just put these tools on a shared drive and access them from any PC/Server.

  Warning: Always test portable software first on your PC before using it on a server. Occasionally some software automatically tries to register for automatic startup.

• CodePlex is Microsoft's website for open source projects with free tools.
• "Free SQL Server tools that might make your life a little easier" a blog post from SQLTeams that list many free tools for SQL Server.