The Mega Guide to Free SQL Server Tools

At SQL Server Magazine, we’re all about helping DBAs and developers do their jobs faster and more efficiently—especially now, when they’re being expected to do more with less staff and funding. That’s why, when we realized just how many free SQL Server tools are available, we decided to bring them to your attention. You might recognize some of these tools from Kevin Kline’s Tool Time or Michael Otey’s Back Page, and many of these tools can be found on Microsoft’s CodePlex website. These tools were created by in-the-trenches DBAs and developers, as well as third-party SQL Server product vendors, to solve specific SQL Server problems or to fill holes in SQL Server’s functionality. There are so many free SQL Server tools that we couldn’t test them all, so be sure to test them yourself before running them in production.

Administration

• AutoAudit<—Dynamically create audit trail triggers using this set of T-SQL scripts. (www.sqlmag.com/article/sql-server/AutoAudit.aspx)
• Data Scripter Add-in for Management Studio—This add-in lets you script data for SQL Server tables through SQL Server Management Studio (SSMS) (sqlblogcasts.com/blogs/seanprice/archive/2007/08/28/data-scripter-add-in-for-management-studio.aspx)
• Iometer—This tool measures and characterizes I/O subsystems in single and clustered servers (www.iometer.org)
• Microsoft SQL Server 2005 Upgrade Advisor—This tool analyzes your SQL Server 2000 and SQL Server 7.0 instances to find features and configuration changes that could affect an upgrade to SQL Server 2005 (www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e05-4322-a677-95ab44f12d75)
• mRemote—You can use this tool (which supports both RDP and VNC) to manage your remote server connections from a central GUI (www.mremote.org/wiki)
• MSSQL Blocks—This tool collects information from multiple SQL Server 2005 and 2000 instances about locked or blocked processes and stores this information in XML files for later analysis (sqlblocks.narod.ru)
• Optillect's SQL Decryptor—Use this tool to decrypt objects that you previously encrypted using the WITH ENCRYPTION option (www.optillect.com/products/sqldecryptor/overview.html)
• Orphan Finder—You can use Orphan Finder to find records in your SQL Server 2005 databases in which there are values in a foreign key column that aren’t in the parent table (http://www.spi.hr/EnglishaboutLC/tabid/470/Default.aspx)
• Partition Management—This tool uses the Sliding Window technique to remove data from one partition and place it into a staging table, and creates a staging table that’s used to load data into partitions (www.codeplex.com/SQLPartitionMgmt
• Quest Software’s Toad for SQL Server Freeware—Toad for SQL Server Freeware offers a code and data editor, a data grid for modifying tables, and a database browser that lets you view and manage database objects graphically (www.toadsoft.com/toadsqlserver/toad_sqlserver.htm)
• Red Gate Software's SQL Search—Use this tool to easily find SQL Server databases, specific fragments of SQL text, and references to objects (www.red-gate.com/products/SQL_Search/)
• SortSQLFilesInProject—You can use this tool to sort the SQL files contained in an SSMS project or the packages in a SQL Server Integration Services (SSIS) project (www.sqldbatips.com/showarticle.asp?ID=78)
• SP_WhoIsActive—Use this stored procedure to quickly retrieve information about users’ sessions and activities. (www.sqlmag.com/article/sql-server/SP_WhoIsActive.aspx)
• SQL Power Doc—Use this collection of Windows PowerShell scripts and modules to discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations (https://sqlpowerdoc.codeplex.com/)
•SQLS*Plus—This product is similar to Oracle, and can be used with Microsoft SQL Server 2000, 2005, and 2008. (http://www.memfix.com/)
• SQLDBSize 1.0—This tool graphically displays size information about your databases, tables, indexes, and more (www.sqldbtools.com/Tools.aspx?ProductId=3)
• SQL Management Studio Snapshot Add-In—This utility adds the Create Snapshot option to context menus in SSMS, enabling you to create a database snapshot (sqlblogcasts.com/blogs/seanprice/archive/2007/07/15/sql-management-studio-snapshot-add-in.aspx)
• SQLRecon—SQLRecon finds all of your SQL Server and Microsoft SQL Server Desktop Engine (MSDE) systems by performing active and passive scans on your network (www.specialopssecurity.com/labs/sqlrecon)
• SQL Server 2008 Extended Events Manager—This tool lets you create, delete, modify, start, and stop Extended Events sessions and metadata files in SQL Server 2008 (www.codeplex.com/ExtendedEventManager)
• SQL Server Automation Scripts—This tool provides SQL Server Agent jobs that you can use to perform automated maintenance tasks (download.microsoft.com/download/4/0/C/40CBAD9A-D990-450B-8785-F288CEBFB448/AITScripts.zip)
• SQL Server Database Copy Tool—With this utility, you can copy databases from one SQL Server system to another (dbcopytool.codeplex.com)
• SQL Server Express Utility—You can use this command-line utility to perform various SQL Server maintenance tasks (www.microsoft.com/downloads/details.aspx?FamilyID=fa87e828-173f-472e-a85c-27ed01cf6b02)
• SQL Server Health and History Tool (SQLH2) Performance Collector—This tool collects performance counter data from your SQL Server systems and stores it in a repository (www.microsoft.com/downloads/details.aspx?familyid=64983AF0-7902-427E-9B41-7C2E8FDCC140)
• SQL Server Health and History Tool (SQLH2) Reports—Prevent performance problems by using this tool to collect and store change and trending information (www.microsoft.com/downloads/details.aspx?familyid=A4B837C7-A1FA-4F25-840B-FEF15E917F18)
• YourSQLDba—You can use this T-SQL stored procedure to automate routine database maintenance tasks such as database and log backups and consistency checks (www.grics.qc.ca/YourSqlDba)

Backup and Recovery

• AppAssure's DocRetreiver Lite—This free tool lets you restore individual documents back to a SharePoint farm from a SQL Server backup (www.appassure.com/applications/free-tools/docretriever-lite-for-sharepoint/)

Best Practice and Compliance

• Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0—You can use this tool to verify whether your SQL Server 2000 systems are managed and operated according to Microsoft’s SQL Server best practices (www.microsoft.com/downloads/details.aspx?FamilyID=b352eb1f-d3ca-44ee-893e-9e07339c1f22)
• Microsoft SQL Server 2005 Assessment Configuration Pack for Sarbanes-Oxley Act (SOX)—This configuration pack audits SQL Server systems to see if they meet the SOX configuration requirements (www.microsoft.com/downloads/details.aspx?familyid=976df931-a3cf-40e3-802e-3281b1451835)
• SQL Server 2005 Best Practices Analyzer—This utility checks your databases for potential problems by using SQL Server 2005 best practices configurations (www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63)
• SQL Server FineBuild—FineBuild lets you install SQL Server 2008 and 2005 with one click, and provides best practice configurations as well (www.codeplex.com/SQLServerFineBuild)

Business Intelligence

• BIDS Helper—This Visual Studio .NET add-in enhances SQL Server 2008 and 2005’s Business Intelligence Development Studio (BIDS) (www.codeplex.com/bidshelper)
• DTLoggedExec—With DTLoggedExec, each step executed by SSIS packages is logged, which makes configuring and troubleshooting SSIS packages fast and easy (dtloggedexec.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=21219)
• expressor Studio 3.0—This extraction, transformation, and loading (ETL) product offers a drag-and-drop interface that simplifies the process of connecting to data sources, mapping data, and creating and implementing complex Data Flow applications (expressor-community.com/expressor-studio-download)
• Jaspersoft Community Projects—These open-source projects offer reporting, analysis, and extraction, transformation, and loading (ETL) capabilities (jasperforge.org/)
• Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007—These add-ins let you use SQL Server 2005’s predictive analysis features in Microsoft Excel 2007 and Microsoft Visio 2007 (www.microsoft.com/downloads/details.aspx?FamilyID=7c76e8df-8674-4c3b-a99b-55b17f3c4c51)
• Microsoft SQL Server Metadata-Driven ETL Management Studio (MDDE)—You can use MDDE to quickly generate SSIS packages (sqlservermddestudio.codeplex.com)
• Pentaho BI Suite Community Edition (CE)—Use this open-source tool to perform OLAP analysis, data mining, reports, and more (community.pentaho.com/)
• Quest Software's Toad for Data Analysts, Freeware Edition—A querying and reporting tool that lets you find, analyze, and deliver data from any data source (www.toadworld.com/DOWNLOADS/Freeware/ToadforDataAnalystsFreeware/tabid/559/Default.aspx)
• Reporting Services Scripter—This .NET Windows Forms application offers the ability to easily move SQL Server Reporting Services (SSRS) reports and catalog item properties between multiple servers (www.sqldbatips.com/showarticle.asp?ID=62)
• RSBuild—You can use this SSRS deployment tool to execute database scripts with ADO.NET and publish reports and data sources to SSRS (rsbuild.codeplex.com)
• SQL Nexus—This front-end visualization tool is used to load and analyze performance data that was collected by SQLDiag and PSSDiag (www.codeplex.com/sqlnexus)

Change Management

• SQLDBSearch 2.0—With SQLDBSearch 2.0, you can find objects that could be affected by a soon-to-be implemented database change (www.sqldbtools.com/Tools.aspx?ProductId=2)
• Tarantino Database Change Management—This utility makes it easier to apply database schema and database changes to multiple SQL Server systems (code.google.com/p/tarantino/wiki/DatabaseChangeManagement)
• Quest Software's Toad Data Modeler, Freeware Edition—You can use this tool to create data models and deploy changes to data structures (www.toadworld.com/DOWNLOADS/Freeware/ToadDataModelerFreeware/tabid/637/Default.aspx)
• Quest Software's Benchmark Factory for Databases, Freeware Edition—This performance and scalability tool helps you determine and minimize the risk of change in your SQL Server environment (www.quest.com/benchmark-factory/freeware.aspx)

Code Formatters

• ApexSQL Refactor—This SQL Server Management Studio add-in offers 43 customizable options for formatting your SQL code and lets you preview your changes before you save them (www.apexsql.com/sql_tools_refactor.asp)
• Instant SQL Formatter—This tool not only reformats your SQL code but also turns your formatted SQL code into HTML code and translates your SQL code into other coding languages such as C# and Java (www.wangz.net/gsqlparser/sqlpp/sqlformat.htm)
• Manoli C# Code Format—You can use this tool to format code to the HTML 4.01 specification for a website or blog (www.manoli.net/csharpformat)
• Simple-Talk Code Prettifier—This tool converts your ASCII source code into HTML code so that you can paste it into an online element such as a blog or website (www.simple-talk.com/prettifier/default.php)
• SQLinForm—With this SQL code formatter, SQL statements aren’t transferred over the Internet and there’s nothing to install (www.sqlinform.com)

Database Comparison

• CompareData—This utility uses ODBC drivers to compare and synchronize data between two SQL Server databases (www.zidsoft.com)
• Open DBDiff—This database schema comparison tool provides you with a report showing the differences between two database schemas and includes a synchronization script that you can use to update the databases (www.codeplex.com/OpenDBiff)
• SQL Accord Community Edition for Microsoft SQL Server—This tool offers schema comparison features via an interactive GUI (www.sqleffects.com/Articles/Product/sqlAccordInfo/aboutSqlAccordCommunityEd.html)
• SQLDBDiff—You can use this tool to compare and synchronize SQL Server 2005 and 2000 database schemas (www.sqldbtools.com)
• TableDiff.exe GUI—With TableDiff.exe GUI, you no longer have to run the TableDiff.exe table comparison tool (found in SQL Server 2005) from the command line (weblogs.sqlteam.com/mladenp/archive/2007/08/10/60279.aspx)

Database Security

• Devenius SQL Encryption Assistant Basic Edition—You can use this SQL Server Management Studio add-in to manage your encryption keys (www.devenius.com/sql_server_tools/sql_encryption_assistant/SEA.aspx)
• Idera’s SQL permissions—You can use this tool to move or copy SQL Server login information and permissions setting between servers (www.idera.com/Products/Free-Tools/SQL-permissions)
• Lockdown.sql—Your SQL Server systems are locked down to the most secure configuration, letting you deploy systems that have only required functionality enabled (www.sqlsecurity.com/Tools/LockdownScript/tabid/64/Default.aspx)
• Microsoft Baseline Security Analyzer 2.1—This tool performs vulnerability assessment checks on your SQL Server systems to verify their security (technet.microsoft.com/en-us/security/cc184923.aspx)
• SQLPing 3.0—SQLPing scans your network to find new and unprotected SQL Server and MSDE instances (www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx)

Development

• DbFit—You can use DbFit to run Framework for Integrated Testing (FIT) tests directly against databases, making it easier to perform integration testing on databases (www.fitnesse.info/dbfit)
• Red Gate Software's SQL Scripts Manager—This tool includes automated scripting by SQL Server experts. (http://www.red-gate.com/products/dba/sql-scripts-manager)
• ScriptDB—This application scripts database objects using SQL Server Management Objects (SMO) (www.codeplex.com/ScriptDB)
• SpatialViewer—This utility lets you view spatial data easily (www.codeplex.com/SpatialViewer)
• SQL Server PowerShell Extensions (SQLPSX)—This tool helps make working with SMO from PowerShell easier by offering PowerShell functions around SMO objects (sqlpsx.codeplex.com)
• SQL Spatial Tools—SQL Spatial Tools includes two tools: Shape2SQL, which you use to upload ESRI files to SQL Server Spatial, and SQLSpatial Query Tool, which queries SQL Server 2008 and displays the geometry output via a Windows Presentation Foundation map (www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx)
• SQLCLRProject—With SQLCLRProject, you can use MSBUILD tasks to deploy .NET assemblies to SQL Server 2008 and 2005 (www.codeplex.com/sqlclrproject)
• SQLIO Disk Subsystem Benchmark Tool—With this tool, you could find out what the I/O capacity of a SQL Server configuration is (www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19)
• SQLScripter—This command-line utility automates schema generation and scripts SQL Server 2005 databases (www.valinor.co.il/tools-sqlscripter.asp)
• SQLsharp—This free tool offers a CLR library of useful functions that you can use to write better SQL code in SQL Server 2008/2005 (www.sqlsharp.com)
• SSMS Tools Pack—This suite of tools enhances SSMS’s functionality with features such as a query template tool and a CRUD procedure-generation tool (www.ssmstoolspack.com)
• Stored Procedure Generator—This tool generates the stored procedures necessary to access tables in SQL Server (spgen.codeplex.com)
• tsqlt—As a unit testing framework for T-SQL, tsqlt helps isolate functionality to be tested (sourceforge.net/projects/tsqlt)

Job Management

• Idera’s SQL job manager—With SQL job manager, you can view and manage SQL Server Agent jobs across multiple SQL Server systems from a central administration console (www.idera.com/Products/Free-Tools/SQL-job-manager)
• JAMS Monitor—With JAMS Monitor, you can manage Windows Task Scheduler and SQL Server jobs and ensure that they’re running successfully from a centralized management console (www.mvpsi.com/FreeMonitor.aspx)
• SQLjobvis—You can use SQLjobvis to view your SQL Server Agent job history, including failures and long-running jobs, via a visually appealing interface (sqlsoft.co.uk/sqljobvis.php)

Log Management

• Log Parser 2.2—You can use this tool to access text-based data (e.g., log files, XML files), as well as data sources in Windows (e.g., Active Directory, the event log) (www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07)
• PSSDIAG Data Collection Utility—This diagnostic data collector can be used to gather logs and data files (www.microsoft.com/downloads/details.aspx?familyid=5564386A-28C2-4483-8293-76FFF67B9EB3)
• SQL Log Rescue—You can use this tool to analyze your transaction logs, and, in the event of a disaster, you can use it to recover data (www.red-gate.com/products/SQL_Log_Rescue/index.htm)

Performance and Monitoring

• ClearTrace—You can use this graphical display tool to summarize query performance data from server-side trace and SQL Server Profiler sessions (www.cleardata.biz/cleartrace/default.aspx)
• Confio Software's IgniteFree—Use this response-time database performance analysis tool to view bottlenecks in your SQL Server environment (www.ignitefree.com)
• DMVStats 1.01—You can use this tool to collect, analyze, and report on performance data from SQL Server 2005 Dynamic Management Views (DMVs) (www.codeplex.com/sqldmvstats)
• Idera’s SQL check—This tool runs as a secure screensaver and monitors your SQL Server systems, as well as offers a look at processes, error logs, and SQL Server Agent jobs (www.idera.com/Products/Free-Tools/SQL-check)

• Internals Viewer for SQL Server—You can use this tool to see how data is allocated, organized, and stored in the SQL Server storage engine (internalsviewer.codeplex.com)
• MDX Script Performance Analyser—You can use this tool to identify which statements in your SQL Server Analysis Services (SSAS) MDX script are negatively affecting MDX query performance (www.codeplex.com/mdxscriptperf)
• Performance Analysis of Logs (PAL) Tool—PAL automates the collection and analysis of performance counters in the Performance Monitor Counter log (www.codeplex.com/PAL)
• Quest Discovery Wizard for SQL Server (beta)—You can use this tool to monitor your network for changes and find all your SQL Server instances (www.quest.com/discovery-wizard-for-sql-server)
• RML Utilities for SQL Server (x86)—With this tool, you can monitor SQL Server performance by processing SQL Server trace files and viewing reports, and test how a specific change will affect your SQL Server systems (www.microsoft.com/downloads/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926)
• SQLIOSim—SQLIOSim replicates SQL Server’s types and patterns of I/O requests on a disk subsystem and verifies data similarly to how SQL Server would in a production environment (blogs.msdn.com/sqlserverstorageengine/archive/2006/10/06/SQLIOSim-available-for-download.aspx)
• SQLQueryStress—This tool is helpful when performance stress testing T-SQL queries because it collects data about whether your queries will run under load, and each query’s resource usage (www.datamanipulation.net/sqlquerystress)
• SQL Server 2005 Performance Dashboard Reports—These reports help you quickly identify performance bottlenecks and provide the information necessary to resolve them (www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc)
• SQL Server Cache Manager—Find out what is being stored in the SQL Server cache and if the cache is being used efficiently with this free tool (www.codeplex.com/sqlservercache)
• SQL Server DBA Dashboard—This tool uses graphs and reports, which are integrated with SSMS, to identify what is using your SQL Server systems’ resources (www.sqlserverexamples.com/v2/Products/tabid/76/Default.aspx)
• SQL Spy—Use this tool to monitor database activity, user connections, CPU, wait statistics, and more (www.ispysql.com/index.php?option=com_content&view=article&id=2:spy&catid=2:spy&Itemid=2)

Prevent SQL Server Injection Attacks

• bsqlbf-v2—This Perl script lets you extract data from blind SQL injections (code.google.com/p/bsqlbf-v2)
• HP Scrawlr—This scanner tool crawls your web pages, checking for SQL injection vulnerabilities (www.communities.hp.com/securitysoftware/blogs/spilabs/archive/2008/06/23/finding-sql-injection-with-scrawlr.aspx)
• Microsoft Source Code Analyzer for SQL Injection—You can use this tool to find SQL injection vulnerabilities in ASP code (support.microsoft.com/kb/954476)
• URLScan—URLScan restricts the type of HTTP requests Microsoft IIS will process (learn.iis.net/page.aspx/473/using-urlscan)

Web Administration

• SQL Server Hosting Toolkit—The SQL Server Hosting Toolkit offers two tools: the Database Publishing Wizard, which you can use to upload your databases to a hosting environment, and the Database Publishing Services, which offers ASP.NET web services that can make it easier for you to deploy databases (sqlhost.codeplex.com)
• SQL Server Web Tools—This web-based administration tool lets you perform management tasks, including administering accounts, managing databases and tables, and creating and running queries and stored procedures (sqlwebtools.codeplex.com)
• SQL Web Data Administrator—This SQL Server management tool lets you create and edit databases, export and import database schema and data, and more (www.codeplex.com/SqlWebAdmin)

Who Says Nothing in Life Is Free?

Although this list includes dozens of free SQL Server tools, it doesn’t include them all. If you know about a free SQL Server utility that’s not on this list, we want to hear about it: You can email me at [email protected]. Also, please share your feedback about the tools included in this list in the Tool Time forum at forums.sqlmag.com. For even more free tools, see the sidebars "Free SQL Server Projects and Samples" and "Free PowerShell Scripts for SQL Server."