(http://www.idera.com/Products/SQL-toolbox/)

The datasheet for ToolBox – asks the question – Want an Easy way to save money? No manager is going to answer that negatively! But does the product live up to its billing of a Best of TechEd 2010 award winner? After all 298 products were judged overall – so it has to be good right?

Throughout this series of blog posts I have evaluated the product and would like to share my opinions with you. I find the Idera products very easy to use and great value for money, offering the DBA (and developer!) a plethora of handy administrative tools. It’s not to say other products from other 3^rd parties aren’t as good, I’m just putting forward the case for my preferred vendor.

In this first post I’m concentrating on the SQL Admin Toolset.

SQL Admin Toolset

I’ve been an avid user of the SQL Admin Toolset for over 12months now and it has certainly eased my maintenance and administrative headaches.

Idera SQL Admin Toolset provides an efficient & effective way to simplify and automate many day-to-day database administration procedures on your SQL Server databases. It streamlines database administration procedures by eliminating the need for manual intervention and the laborious need to search for the relevant information about the key aspects of your database state and its performance.

Administration tools

Multi Query

If like me you are fed up with having to run queries on individual servers against individual databases, then this tool is for you. Simple add the desired servers/databases to a list, create your query and fire away. Granted SQL Server 2008 has improved somewhat in its ability to provide this functionality, but I still favour this interface. Brings back query results as individual server sets or combined into one. A great time-saving invention!

Database Mover

The ability to copy a database from one instance to another, as well as the possibility of moving actual datafiles within the same instance.

Presently, we utilise a number of different methods to ‘transport’ our databases – usually opting for detach/re-attach or backup & restore.

Using this tool only requires a simple 4-step process which includes stating the source and destination Servers/Databases, giving you the option to copy additional transaction logs and overwrite the destination database if already exists etc (similar to manual backup/restore procedures).

During the process the files can be relocated and renamed if desired also.

The ability to automatically create logins on the destination server was a neat feature, as in the past this is an extra manual procedure we used to do once the database had been moved.

Note – you’ll need to ensure the destination server ‘allows remote connections’.

Login Copy

A very simple tool to use, you simply need to state the source and destination servers.

If you do not transfer the logins from the source server to the destination server, your current SQL Server users may be unable to log on to the destination server – a problem I’ve stumbled across many a time !

What was useful was the option to ‘preview missing logins’, from one click you can see what logins are on the source but not on the destination etc – once this information was reviewed, clicking on another button to actually ‘move the logins’ took seconds to run.

Again, previously this would involve at least 2 manual processes (checking each server’s login list and running a SQL script to move them).

Again you need to ensure the involved servers allow remote connections.

User Clone

A tool which allows us to create a new user with the same permissions, access level etc as an existing user. We will find this tool useful when building test and training environments where currently we have to manual re-create users on a frequent basis.

Some of our database schemas are replicated over certain smaller applications, the use of this tool will allow for more rapid creation of users and their required permissions.

Job Mover

A nice menu-driven tool that gives you the ability to copy or move your SQL jobs from one SQL server to another. Simply asks for the Source and Destination servers, choose the jobs you want copied/moved and 3 clicks later, it’s all done. Again, minimal effort required for what can be a laborious task.

Job Editor

This tool offered a better alternative to SSMS. It allows the DBA to instantly spot which jobs have failed (highlighted in red), which jobs have not run for the last 2 weeks (highlighted in yellow) and whether the jobs were notifying the event log or not. Again running this tool on our servers identified some flaws in our procedures and I can certainly see a productive use for this tool. The main plus for us was the ability to see all the job information from all servers within certain server groups in one screen, eliminating the need to manual check each server through SSMS.

Quick Reindex

I found this tool extremely useful. To use as a quick snapshot to review the state of my indexes (which is presented in a colour-coded summary for ease of identification of over-fragmented indexes etc), and also as a straightforward and simple way of either re-organising or re-building the indexes. By simply highlighted problem indexes and clicking on Rebuild button – the operation is seamless and somewhat novice-proof!

Note the rebuild indexes option does take the indexes offline to carry out the operation, but even this can be scheduled via this utility to run at any given time.

At present, we have numerous SQL scripts which we run to provide fragmentation information etc on a weekly basis – having this tool will eliminate the need for manually running these scripts and provide a user-friendly pictorial view of their state.

The utility also allowed you to copy the results of any operation performed to the clipboard for post analysis and record etc.

Server Configuration & Database Configuration

Other tools which I found pertinent to my current working practices were the server & database configuration tools, documentation of our servers has always been somewhat minimal, and by using these tools I was able to compare both the standard & advanced options of each of the servers and individual databases. This would serve you well in your goal to standardise your SQL Server infrastructure.

Partition Generator

I don’t use partitioning in earnest at my workplace, but this tool gives you a quick and easy to create and manage your table partitions. It also acts as a guide and hints at whether you should partition certain tables for efficiency and performance advantages.

Note: – Partitioning requires Enterprise Edition.

Connection String

A neat little utility that allows you to create custom connection strings and test them all from one easy-to-use interface.

Table Pin

Again most of the databases we administer here had no pinned tables anyway.

The usual reason for pinning tables into memory is to guarantee rapid access to the data in time critical situations. If databases are under memory pressure pinning tables would let you override SQL Server’s normal decision making process about which pages to keep in memory.

Pinning tables into memory can fill up the data cache and force you to stop and restart SQL Server. So if you were administering an unfamiliar database then this utility would certainly help identify potential issues.

Linked Server Copy

Allows database administrators to copy linked servers from one SQL Server instance to another along with the associated settings and permissions. With this tool DBAs can also, view all the linked servers that exist on the source server and not on the destination SQL Server instance and copy all or specific linked servers from the source SQL Server instance to a destination SQL Server instance

Diagnostic tools

Backup Status

Again, in the past we have been required to browse SQL jobs from within SQL Enterprise Manager to check the backups have been successful, or simply check local drives to check backup files exist!

This utility will display all backups – successful & unsuccessfully, again suitably highlighted, as well as those databases which have not been backed up for the last 7 days (& never backed up !)

Backup history can also be viewed at the click of a button.

Again this can be run against individual servers or particular server groups – allowing us to monitor the backup status of numerous databases from differing servers all in one window.

SQL Search

Although we have no real use internally to use this utility, as most of our stored procedures are written by third-party suppliers etc, I found it a useful tool to have. For instance if you vaguely knew of a stored procedure you had used in the past in one particular database, couldn’t remember what it was called, or where it was even, then you could use SQL Search to hunt it down. Simply required to supply server name, database name (optional) and the text for which to search and away it goes to retrieve possible candidates. Use of wildcards is supported.

 Object Search

At last a straightforward way of finding my stored procedures or logins! This tool allows you to search any SQL Server object across your entire SQL enterprise. Very handy for me, as I’m always creating ad-hoc stored procedures on one server, and a few months later having to repeat the process for another server – only to forget where I put it, what it was exactly called etc. With the added bonus of being able to use wildcards – searching across multiple servers is very straightforward and quick (you have the option to limit the matches it returns too).

SQL Discovery

A quick scanning tool to discover all the SQL Servers in your organisation using IP ranges or specific SQL Server names. Uses 5 different probes to discover the servers (SQL Server Resolution, TCP, WMI Enumeration, Windows Registry and Service Control Manager).

We run it and it reported some SQL Server instances we didn’t know existed! so proved valuable.

Tool also has the option of scanning for SQL servers registered in Active Directory – which will become useful to us in the future.

Password Checker

This tool certainly opened our eyes! The ability to check all the passwords on all our servers to see whether (a) they’re actually set and (b) what strength they were. After some alarming results for us we were able to pinpoint potential security flaws and rectify them promptly.

The utility allows us to check sa passwords, server & database role passwords and individual login passwords. We will certainly be using this on a frequent basis and hope it reports back green ticks!!

Connection Check

Having initial problems connecting to your server? All you need to do with this utility is enter your server name and click a button! One click tests the SQL connections, reports back with IP details of the server, carries out ping checks and ensures the SQL service is actually running ok. Effortless.

Analytics

Inventory Reporter

This feature, I found very useful indeed. I was able to choose 10/12 different servers, retrieve configuration information on them all and highlight all the differences.

The utility allows you to report on the SQL Server properties, SQL Server version properties and the actual computer properties (server hardware) – or simply choose the Comprehensive Inventory report to encompass them all.

This is an ideal utility to track how each of your servers is configured and use in general infrastructure documentation. Again, in the past if we did keep an inventory of our servers, it wasn’t as comprehensive as what this utility allows and it meant we would have to go looking manually for the relevant information.

 It’s very quick to report back the information and allows us to save snapshots of the report for future comparison work etc.

 Patch Analyzer

 Another useful reporting mechanism, allowing us to view information such as edition of SQL Server, whether any updates are available, build numbers etc.  It enables us to keep abreast of any download/support information by providing a link to SQL Server 2000/2005/2008 Download web pages and KB articles.

 Index Analyzer

 Making sure your indexes are performing as they should be and if indeed they’re being used appropriately is a key area of Database performance management. Index Analyzer presents an easy-to-follow colour-coded view of all your databases’ indexes, highlighting their usefulness, selectivity and overall usage. I also found this a quick place to identify which of my indexes were enabled/disabled and whether they were clustered or not. Yes you can get all this information from various sources using coding – but if you like pretty pictorial views whereby you can identify immediately potential problem areas, then this is the utility for you. You can run the utility against individual databases or your entire server.

 Space Analyzer

 Similar in appearance to the Index Analyzer, a quick and easy tool which depicts your overall disk space and log file usage statistics. I recently used this tool to gauge our hardware upgrade requirements; it’s also easier to present this data in this fashion to management – because all they’ll be interested in are the red highlighted ‘danger’ areas! (red being your disks are 85% or more full) They’ll soon give you the go-ahead to order more powerful, spacious kit!

 Server Statistics

 Again, a very neatly presented tool. Very similar to the information you get from SSMS but nonetheless, its appearance is more user-friendly and very simple to interpret. This would be an ideal tool to offload on newbies; it gives them a quick overview of a server (DB details, logins, jobs, processes, error logs etc). Great place to locate quick counts of various objects – e.g. you can see the differing compatibility levels of all the databases on the server, without having to click too many times!

 Server Ping

 This utility can be used to quickly diagnose server connection problems. Choosing a particular server (or optionally a specific database), you can test general SQL Server connections, IP address details, host computer pinging and status of the SQL Server services.

If a problem is detected and highlighted, the utility offers various recommendations for resolving the issue.

 To conclude

 With all of these tools in the toolset – all it takes is a couple of clicks, as long as you can remember your server name – then all your management and administration is at your fingertips from one central area!

 You’ll be very impressed by the Toolset’s ability to diagnose and resolve problems quickly, often before the user knows a problem exists.  In a busy IT department where firefighting issues and trying to prevent important data corruption/loss is paramount, a toolset such as this is invaluable.

It is an extremely effective tool for identifying performance issues on all aspects of SQL Server. Utilities such as Quick-Reindex, Password Checker and Database/Login Mover are used in earnest in my workplace on a daily basis, limiting the monotony of the DBAs’ role.

Ideras’ ability to produce more complex inventory and patching reports has also impressed me and formerly adopted manual processes have been replaced by convenient and more efficient tasks through the Toolset.

The utilities the Admin Toolset offer will streamline the general management, monitoring and auditing across all your databases.

It has the ability to alert DBAs and provides faster response times in investigating, diagnosing and eventually resolving any performance and availability problems.

Overall productivity of both your skilled and novice DBAs will increase, as current complex and time-consuming procedures are eliminated. And perhaps more importantly both the end-users (customers) and senior management will be more content!

There is a lot of functionality in the toolset which, with some competitor vendors would require the use of 3 or 4 different individual tools.

For a more comprehensive walkthrough of the tools and their functionality check out the Idera video tutorials:-

http://www.idera.com/Product-Video-Tour/SQL-toolbox/SQL-admin-toolset-Overview/?s=PT_IderaEB_tbx&elq=bbf6b8809c8549bf98dc3f0549a54013

Heather Eichman interviews Kevin Kline, the former President of PASS and systems specialist for the SQL Server Business Unit at Quest Software.

Posted February 08, 2008.

Yesterday was an exciting time for PASS. As soon as ballots went out there was a surge of interest on Twitter, people voting (and often sharing their choices), others waiting – impatiently! – for their chance to vote as it took a while for all the email to get sent. Thanks for voting!

Tell us about yourself Buck.

Wow – I’m kind of old, so that would take longer than we probably have. Think of the old guy in the room randomly waving his arms blurting out “All of this used to be orange groves, far as the eye could see”, before dropping back into a nap.

But I actually feel like that. I’ve been around tech a long time, and seen the changes along the way.  I’ve always been interested in technology and electronics, even as a little kid. Star Trek nerd, the whole bit. I grew up on the Space Coast in Florida, and when I saw the moon shot I decided I wanted to work at NASA when I grew up. After school, the Air Force and college, I did end up working at both Lockheed Space Operations at NASA and at the U.S. Space Command at Patrick Air Force Base. I worked with mainframes to start, then built my own PC from a Zilog chip and played with everything from Commodores to Apples and IBM PC’s, and was one of the early adopters in business of OS/2 and Windows NT.

I’ve been a sysadmin, a developer, a network tech and even a hardware tech. But I’ve always lived in the data space, first with COBOL flat-file systems, then with Oracle, Dbase (Clipper, actually) Sybase, PostGres and then finally SQL Server. As far as training, well, it’s been a mix of college courses, OJT, and lots and lots of reading and practical applications. And the training never ends. In fact, I teach a database course now at the University of Washington, and I’m still learning all the time.

What have you had published?

I’ve got five books on SQL Server published, from SQL Server 7 to 2005. I write each week at InformIT.com – you can find that here: www.informit.com/guides/guide.aspx?g=sqlserver and a full “TOC” here: buckwoody.com/informittoc.htm. I’ve written lots of articles along the way, and even have some articles and checklists out there on Simple-Talk.com.

Where do you blog?

I have a few blogs, but the main one (with links to the others at the top) is at blogs.msdn.com/buckwoody. I post multiple entries to that one every week, and it has to do with everything from technical career advice to T-SQL and PowerShell scripts, security, management tools and more. It’s all technical, though.

I’ve found great benefits in blogging. I get a lot of feedback, all useful – even when folks disagree. It’s just another method of communicating, and that’s always a good thing in your career and personal life.

What advice can you give to DBAs about writing?

Writing is easy. Re-writing is hard! Probably because you don’t want to do it. You write something down, and you think, “that’s awesome”. Then you look at it again and you wonder if you finished kindergarten.  And you’re always sensitive that someone may not agree with you – and you have to get over that. One of my favorite quotes is one that goes back past Roman times: “We learn to write by writing.”

Tell us a little about your speaking experience.

I’ve been doing public speaking since I was a teenager. Once again, college courses helped, along with lots and lots and lots and lots of reading. And watching. I constantly watch others and their audiences to see what resonates and what doesn’t.

What advice do you have for DBAs who want to begin making public presentations?

Join Toastmasters. Brent Ozar and I are also doing a joint session at PASS this year that you don’t want to miss, called “You’re not attractive, but your presentations can be.” Definitely check that out.

How do you keep up with your SQL Server continuing education?

Read read read read read read read. And then experiment with everything I can.

What’s your favorite SQL Server Book?

It’s an out-of-print Oracle book from version 7 by Tom Kyte. I kid you not. The guy writes in such a clear style that the SQL constructs, maintenance concepts, and so on, just hold right on to every platform I’ve worked with.

Why should DBAs consider taking part in the SQL Server Community?

Because you can learn, and you pay for that learning by helping those who don’t know as much as you do. The examples are numerous – but you should at least start at the Microsoft forums helping out newbies at social.msdn.microsoft.com/Forums/en-US/category/sqlserver.

What are some of the key characteristics that you feel differentiate between “good” and an “exceptional” DBAs?

Paranoia, and attention to detail.

What are some of the biggest challenges for DBAs in the immediate future?

Not adapting to new technologies. I see a lot of resistance in the community, to everything from new versions of SQL Server to the cloud. I saw the same thing in the mainframe days against PC’s and LANS, and those guys were soon out of work.

What advice would you give to a person who is considering becoming a DBA?

Check out my article series here: www.informit.com/guides/content.aspx?g=sqlserver&seqNum=247.

What do you consider one of the most useful, but underrated features of SQL Server?

Standard reports. They are incredibly valuable – I’ve blogged them all here: blogs.msdn.com/b/buckwoody/archive/tags/standard+reports/.

When you are not working, what do you like to do for fun?

Family adventures. You can see a list of what we’re up to here: carpedatum.spaces.live.com/.

If you were not a DBA, and could choose the perfect job, what would it be?

I’d be in tech somewhere. I’ve done lots of things in my checkered past, but I have always return to tech.

Tell us about the sessions you will be presenting at the SQLServerCentral.com track at SQL Server Connections this November?

I’m doing a database design session, and I’m hoping that I’ll get some folks new to that discipline. I’m also doing a “SQL Server for the Oracle Professional” that I trust will be useful to someone who codes in Oracle, and is looking to add (not change to) the SQL Server platform. I can’t wait to see everyone there!

The Oracle guys will tell you that SQL Server does not have as many  capabilities and features which Oracle has (or certainly 2 years ago they would have !) - and vice versa – SQL guys will claim their beast is the mightier etc. They may both be right in their own arugments.

But my reply to the question of which is best usually includes asking them the following:-

a. Do you need / will you use all the featues that the RDBMS offers?

b.More importantly in the current financial climate, how much extra will these features cost the organisation ? 

c.If we do need the features, how do you convince management they’ll get a return on their investment ?

d.What is the running cost of your RDBMS? Everything – from hardware, software, actual manpower, any DR/contingency planning costs etc.

e.Is it easy to get technical people to manage my database environment? e.g. I recently read a blog post claiming SQL Server experts were dwindling in numbers ?? Does your organisation have existing skillsets, or will they need to re-train/recruit ?

Its a debate which has raged for many a year and will probably continue to do so…………… but once they can answer the questions above then they should naturally be led to a decision.

Here’s the outline:

Learn to network and make professional connections that will pay off for years to come from best-selling author, communications trainer and “small talk” expert, Don Gabor. Using demonstrations, exercises, role plays, and coaching that focus on networking at breakfast, lunch, between sessions and at the evening reception Don will guide you step-by-step through the networking process. By practicing confidence-boosting skills, tips and strategies you’ll see that connecting with your colleagues is easy, fun and profitable! Take-aways include:

Using body language to build instant rapport

Introducing yourself and remembering names

Breaking the ice & keeping the conversation going

Transitioning from topic to topic

Ending conversations and working the room

Following up and building business relationships

Each workshop attendee will receive an autographed copy of Don’s best-selling book, Turn Small Talk into Big Deals.

Do you need business contacts? Of course you do! Just because you’re not a consultant or selling a product doesn’t mean you can’t benefit from business contacts. One of the fun parts of networking is that you just never know who you’ll meet or how it may help you (or how you may help them).

I’m going to this one, and I’m writing the $75 check to attend. I want to be in room with a bunch of other people that want to network, what better place to both meet people and practice new skills?

Now to be fair I’m biased. I met Don after reading his book about how to start conversations, hired him to coach me, and we’ve become friends. I don’t want you to attend so that Don can make $75, I want you to attend because I think he’s just about the best in the world at what he does. I watched him greet people coming into a room last year, hand shake and hello, and 10 minutes later name at least 50 of the 75 attendees. Remembering names is hard, and he makes it look easy. That’s just a part of his game!

Whether the boss pays or not (he should), I hope you’ll attend, we’ll have fun and learn a lot too.

The first attempt was to build and deploy to the master database. This resulted in the following error:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate “drive_info”:
System.Security.SecurityException: Request for the permission of type ‘System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.IO.Directory.GetLogicalDrives()
at System.IO.DriveInfo.GetDrives()
at UserDefinedFunctions.drive_info()

After getting the above error and not really understanding the ramifications of an unsafe assembly, I decide it was time to do some reading. I found that the book, A Developers Guide to SQL Server 2005, had an excellent discussion of the CLR and the different assembly security types. Now I was ready to create an environment for this function.

Since this was a function for DBAs to use for system monitoring, I created a new database named DBA_Tools and set the owner to sa. In order to let an Unsafe assembly execute, the TRUSTWORTHY option needs to be enabled. Turn on Trustworthy with this command.

ALTER DATABASE DBA_Tools SET TRUSTWORTHY ON

Make sure that the CLR server option is enabled, otherwise you can’t execute the CLR function. This can be accomplished with this script.

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDEEXECUTE sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

One thing that I did notice is having the CLR server option enabled or disabled had no impact on deploying the function. This is a great feature if you typically don’t want the CLR enabled all of the time. The CLR can be enabled by the script that runs drive_info() function and then be disabled immediately after.

Moving on to the Visual Studio setup, make sure the connection in the CLR database project points to the DBA_Tools database. Set the Assembly name property on the Application tab of the Solution property page to something more appropriate.

The CLR function performed as advertised after setting everything up correctly. The function was built in both Visual Studio 2005 and 2008 and deployed to the corresponding versions of SQL Server. The setup worked on both SQL Server 2005 and 2008. Here is a sample output.

Utilizing the table, DriveInfo, that was used in the previous installments, the script to populate it would be:

INSERT INTO DriveInfo
SELECT letter   AS Drive, total_mb AS DriveSize, free_mb  AS FreeSpaceFROM drive_info()WHERE type = 'Fixed'

Although this solution works well enough, there is some risk with this approach. Based on how an Unsafe assembly is handled with in the SQLOS, there is a risk for memory leaks when an unhandled exception occurs. If a solution can be created that uses alternative SQL Server features rather than an Unsafe CLR assembly, I would choose the alternative. Speaking of alternatives, that where the next installments come in. They will cover using SSIS to get the drive info via the Script Component and WMI.