1 The Ultimate Free SQL Server ToolkitKevin Kline, Quest Software SQL Server MVP since 2004

2 About ME… About Me SQL Server Expert for Quest SoftwareFormer developer, DBA, and enterprise architect Former director-level manager Author & Community Organizer SQL in a Nutshell and 8 other books; SQLMag & DBTA columns Former president of PASS, , and founding board member Microsoft MVP since 2004 kekline Slides and other resources at

3 Agenda Relational Engine Tools Analysis Services ToolsSecurity Administration Transact-SQL Testing Performance DTS/SSIS/SQLAgent Analysis Services Tools Non-SQL Server Tools too good to miss Looking at SQL2005 and later. No tools covering SQL2000 (only) are discussed. All tools covered are non-commercial products. So even great free products, such as the Discovery Wizard from my employer Quest Software, and SQL Prompt from Red-Gate are not covered. The only exception to that rule are free tools from Microsoft.

4 General Security - because there is no 'patch' for stupidBookmark it! SQLPing 3.0: discover all SQL Servers Lockdown.sql: locks down rarely used functionality/security holes, while not breaking common features Microsoft Baseline Security Analyzer: perform a security assessment of your SQL Servers, Don’t let security be an afterthought has great scripts and excellent FAQs. Be sure to read the FAQs here if you’ve never gone through a security checklist for your SQL Servers or you’ve never understood SQL Injection attacks very well. There’s also a nice encryption key manager that plugs into SSMS but it’s offered free by a commercial venture. So I didn’t profile it here.

5 SQL Injection – ever heard of locking the door?HP Scrawlr: detect & identify SQL injection vulnerability, URL Scan: check for app weakness to bad HTTP requests, Microsoft Source Code Analyzer for SQL Injection: find vulnerabilities in static ASP code, Image courtesy of Protect Your ASP and ASP.NET Applications SQL injection attacks don’t occur on only SQL Server databases backing ASP and ASP.NET front-end applications; they can also occur on PHP applications with MySQL back ends and Java applications with Oracle back ends. All database platforms are vulnerable to SQL injection attacks. You can prevent such attacks by filtering entry fields on your web pages so that only allowable values are permitted. Because of the upsurge in SQL injection attacks, Microsoft has published an important security advisory that’s available at The advisory points to the following three important tools that you can use with your ASP and ASP.NET applications to prevent SQL injection attacks: HP Scrawlr—This free scanner utility can detect and identify whether your website is susceptible to an SQL injection attack. The utility crawls a website, analyzing the entry fields of each web page for SQL injection vulnerabilities as it goes. (Note that it doesn’t work against JavaScript, flash parsing, or POST parameters.) You can learn more about HP Scrawlr at URLScan—This security tool actively restricts the kind of HTTP requests that Microsoft IIS will process. URLScan isn’t a substitute for properly programming a web application, but it can prevent some potentially harmful requests from reaching the web application and SQL Server. It works on IIS 5.1 and later, including IIS 7.0 for Windows Server For more information about URLScan, go to Microsoft Source Code Analyzer for SQL Injection—This command-line tool analyzes your static ASP source code written in VBScript (not ASP.NET) and reveals possible vulnerabilities to SQL injection attacks. The tool then generates a report detailing the vulnerabilities it detected and possible remedies. Microsoft Source Code Analyzer for SQL Injection is available at Design and Develop Applications with Security in Mind Although each of these tools can help prevent an attacker from penetrating and damaging your website and SQL Server database, none of them are as effective as designing and developing your application with security in mind. SQL injection is an old style of hacking websites, and it’s quite easy to prevent when value checks are written into the code from the beginning. For more information about preventing SQL injection attacks, see the Learning Path. I recommend reading "Hack Your Database Before the Hackers Do," May 2008, InstantDoc ID 98576, to learn more about how to protect your websites and databases from SQL injection attacks. MSDN also offers many articles on that topic.

6 Administration – whassup?!?Extended Events Manager: everything you need to manage Xevents and metadata, StandardCDC: all the flavor of Change Data Capture, but half the calories!, MSSQL Blocks: find out about locks & blocks on multiple SQL2000/2005 instances, Internals Viewer: see how data is written at the 8k page level, SQL 2008 Extended Events are a powerful new way of troubleshooting problems with SQL Server. One short coming is that there is no UI support for this new feature. The Extended Events Manager is a SSMS Addin to bridge this gap. Originally an external application, this project has undergone numerous updates to get it to the point where it is now a SSMS Addin that integrates directly into Management Studio. The latest build of the external application is still available for download, but all new development efforts will target the SSMS Addin only. StandardCDC is a project to enable customers on Standard Edition SQL Server (2005 or above) to implement data capture in a manner conceptually equivalent to the Change Data Capture feature in SQL Server 2008 Enterprise Edition.

7 Administration – Suite! Er, sweet!SSMS Tools Pack: a snap-in that enhances SSMS with CRUD procedure generation, generated Inserts and much more, Tarantino: tools to automate and propagate change management through multiple environments, SQLPSX: a boatload of security management PowerShell scripts for SQL Server, The SSMS Tools Pack provides developers with tools that fill in the gaps in SQL Server Management Studio’s (SSMS’s) functionality. The SSMS Tools Pack includes a tool that generates stored procedures that perform all of your create, read, update, and delete operations. The SSMS Tools Pack is available for SQL Server Management Studio (SSMS) and for SQL Server Management Studio (SSMS) Express. Tarantino Database change management provides a set of tools which make the process of propagating database schema and data changes to multiple environments frictionless. SQL Server PowerShell Extensions (SQLPSX) aims to provide intuitive functions around the SMO objects which are backward compatible with SQL A secondary goal is to provide an application which reports flatten/effective security settings for logins, users, roles, and permissions. FineBuild provides 1-click install and best-practice configuration of SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.

8 Administration – mo bettaFineBuild: flexible and configurable deployment tool which incorporates SPs, CUs, and best practice configurations, Assessment & Planning Toolkit: Microsoft’s free tool to assess VM candidates & consolidation, Infrastructure Planning and Design Guide: a Microsoft checklist and decision tree, Best Practice Analyser: checks your SQL2005 database for potential performance and configuration issues, FineBuild provides 1-click install and best-practice configuration of SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2. Microsoft Assessment and Planning Toolkit Identify SQL instances automatically Assess Hyper-V virtualization candidates for server consolidation Download at Infrastructure Planning and Design Guide Outline SQL Server 2008 infrastructure design decisions and ensure that business and technical requirements are met Offer easy-to-follow steps to design architecture including decision flows Download at BPA isn’t supported on SQL2008, but Policies exist that do the same thing. SQL Server 2008 you will have access to a whole bunch of policies which you can import and use. They’re in: Program Files\Microsoft SQL Server\100\Tools\Policies

9 Admin PM Script Libraries – Joke: How do you eat an elephantAdmin PM Script Libraries – Joke: How do you eat an elephant? Punchline… YourSQLDBA: all the best PM tasks in one tight procedure with a French accent, AITScripts: Microsoft’s internal production PM scripts, AbaPerls: automate and manage configurations & schema objects and builds, OlaH: a great collection of PM scripts from Ola Hallengren, Product Sample Scripts: great scripts for alerts and event notification from Microsoft, YourSQLDba is a stored procedure that uses T-SQL to perform routine database maintenance tasks. YourSQLDba automates database and log backups, consistency checks, and other preventative database maintenance tasks. YourSQLDba works with SQL Server 2008 and SQL Server 2005 in 32-bit and 64-bit environments. AbaPerls, a set of command-line tools, automates and eases script development, configuration management, and implementation of SQL Server databases and schema objects. AbaPerls offers both development (e.g., ABASQL) and database management features (e.g., DBBUILD). AbaPerls requires SQL Server 2000 and later, and Microsoft Visual SourceSafe 2005 and 6.0.

10 Testing tSQLt: a unit testing tool for Transact-SQL code, SQLIOSim, an IO workload simulator to test disk IO capabilities, SQLQueryStress: stress test your queries to see how far they can scale,

11 Transact-SQL Generators – “She cannay give ye any more, cap’n!”SPGen: generate CRUD procedures for all your tables, ScriptDB: script all of your database objects via SMO, Open DBDiff: compare and sync two databases, Instant SQL Formatter is a free online sql tidy tool, actually, it not only can beautify your sql but also can turn your formatted sql into html code, so you can post coloured sql code in your blog, forum,wiki and any website easily. In addition to beautifying SQL code, this sql tool can translate SQL code into C#, Java, PHP, DELPHI and other program languages. Another useful feature is find out all database objects such as table, column, function in sql by selecting output format to list database object.

12 Transact-SQL Formatters, Translators, & Migrators – “Hablo T-SQL?”SQL in Form: a simple Transact-SQL formatting tool, Instant SQL Formatter: a Transact-SQL formatter and translator that can generate code in HTML, Java, and C#, Simple-Talk Prettifier: converts an ASCII source into HTML for use in blogs & websites, SQL Server Migration Assistants: ready to move off of Oracle, Sybase, or Access? Instant SQL Formatter is a free online sql tidy tool, actually, it not only can beautify your sql but also can turn your formatted sql into html code, so you can post coloured sql code in your blog, forum,wiki and any website easily. In addition to beautifying SQL code, this sql tool can translate SQL code into C#, Java, PHP, DELPHI and other program languages. Another useful feature is find out all database objects such as table, column, function in sql by selecting output format to list database object.

13 DTS, SSIS, and SQLAgent – let automation do the heavy liftingDTLoggedExec: makes logging SSIS inside packages fast & easy while providing lots of performance info, SSISUnit: a declarative testing tool based on Xunit tailored to SSIS environments, cspJobMonitor: track the activity of all your SQLAgent, BIDS Helper: VS.Net add-in with oodles of extra features to augment BIDS, IS, and RS, DTLoggedExec is a tool that allows you to run a Sql Server Integration Services (SSIS) Package producing a full and detailed logging informations of execution status and package runtime data, including dataflow profiling information. In brief it allows to fully log and instrument package execution. BIDS Helper: Too many features to list, but it does a lot over and above the default Business Intelligence Development Studio. The complete list of features includes: Features * Analysis Services o Aggregation Manager o Calculation Helpers o Column Usage Reports o Delete Unused Aggregations o Deploy Aggregation Designs o Deploy MDX Script o Dimension Data Type Discrepancy Check o Dimension Health Check o Dimension Optimization Report o Measure Group Health Check o Non-Default Properties Report o Parent-Child Dimension Naturalizer o Printer Friendly Aggregations o Printer Friendly Dimension Usage o Test Aggregation Performance o Tri-State Perspectives o Similar Aggregations o Smart Diff o Show Extra Properties o Sync Descriptions o Update Estimated Counts o Validate Aggregations o Visualize Attribute Lattice * Integration Services o Create Fixed Width Columns o Deploy SSIS Packages o Design Warnings o dtsConfig File Formatter o Expression and Configuration Highlighter o Expression List o Fix Relative Paths o Pipeline Component Performance Breakdown o Reset GUIDs o Sort Project Files o Sortable Package Properties Report o SSIS Performance Visualization o Variables Window Extensions * Reporting Services o Dataset Usage Reports o Delete Dataset Cache Files * General o Enable/Disable features o Version Notification

14 Analysis Services – because we really needed yet one more way to write SQL codeReporting Services Scripter: move reports easily between servers, RSBuild: executes scripts, publish reports RS & data sources, Analysis Services Product Samples: loads of cool tools including backup & recovery, activity viewer, tracing, AMO browser, and Data Mining WebControls, Also available, IS, RS samples MDX Performance Analyser: collects metrics on your MDX queries, Reporting Services Scripter is a .NET Windows Forms application that enables scripting and transfer of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules), Subscriptions (normal and data driven) and server side report parameters. What is RSBuild: RSBuild is a deployment tool for SQL Server Reporting Service. It currently supports two types of tasks: executing SQL Server scripts and publishing SQL Server Reporting Service reports and shared data sources. From version onwards SQL Server 2008 Reporting Services is supported as well as all previously supported versions of SQL Server Reporting Services. Also includes a fantastic command-line utility, ascmd utility ships as part of Analysis Services Administrative Samples in the SQL Server 2005 Samples Analysis Services provides, a unified and integrated view of all your business data as the foundation for all of your traditional reporting, online analytical processing (OLAP) analysis, Key Performance Indicator (KPI) scorecards, and data mining. The MDX Script Performance Analyser allows the user to identify how much each calculated member, named set and scoped assignment in an Analysis Services cube's MDX script contributes to the overall performance of a given MDX query.

15 Non-SQL Server Tools PowerGUI: Don’t want to learn PowerShell? Use this free GUI tool and it’s huge library of pre-built scripts, Scriptomatic: Don’t want to learn WMI? Use this free tool instead, Log Parser: Read just about any data source from XML to the Event Log to the Registry, mRemote: manage all your remote connections from one GUI, Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®

16 Summary Don’t reinvent the wheel! Reuse what’s available.12/16/2017 9:34 PM Summary Don’t reinvent the wheel! Reuse what’s available. Free script-based tools are usually better maintained than Free GUI tools. Microsoft’s versions and release cycle are the biggest issue with free tools Remember – you get what you pay for; free tools are free after all © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

17 Resources http://tinyurl.com/ylauses http://www.codeplex.comMy SQLMag Tool Time column. A fantastic collection of SQL Server tools, free to registered members Free tools from a large, independent open source community and Excellent tools and resources by Gert Drapers for SQL2005+ and SQL2000, respectively

18 Q & A THANK YOU! Send questions to me at: [email protected]Twitter at kekline Blogs at SQLblog.com, SQLMag.com Rate me – Slides at THANK YOU!

19 Complete the Evaluation Form & Win!You could win a Dell Mini Netbook – every day – just for handing in your completed form! Each session form is another chance to win! Pick up your Evaluation Form: Within each presentation room At the PASS Booth near registration area Drop off your completed Form: Near the exit of each presentation room Sponsored by Dell

20 for attending this session and the 2009 PASS Summit in SeattleThank you for attending this session and the PASS Summit in Seattle