Idera SQL ToolBox
(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 3rd 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:-
