This article describes the set of tools every DBA (at least every DBA of DB-Staff) uses on the daily basis. May be this review will help you to choose the right set of tools for your company based on your needs. There is plenty of third-party SQL Server tools. Some of them are even free

Sql Server Monitoring tools

If there is no third-party sql server monitoring tool available it is possible to use standard perfmon utility always gathering critical system and sql server counters and configure sql server agent alerts to be notified when a performance problem occurs. This approach requires a lot of manual work to setup and is inconvenient because you need to look into csv file located on the server where  perfmon runs while receiving alerts on email or pager with obvious difficulties concerning history view, classification, filtration etc.

SQL Server 2008 provides amazing embedded monitoring tool Performance Data Collector (or PDC). It allows you to constantly gather system and sql server health information but this feature does not provide alerts.

Third party sql server monitoring tools

SQL Server monitoring tools are usually centralized. You need to install the central monitoring server  and then install monitoring agents (as for CA) or add sql servers to monitor (as for Spotlight) from sql server monitoring tool admin console. You can see statuses and current warnings of all monitored servers from this monitoring tool console which is very convenient if you administer 100+ servers (otherwise you would have needed to connect to each server separately).

 Quest Spotlight

Very powerful and informative monitoring system. Spotlight enables to track historical information. However adds quite significant overhead to the monitored server. Unusual view of console is definitely a plus of the system. One of disadvantages is that if a job failed on the monitored server the server is marked red until job runs successfully. This is annoying but there is no other way then change the job steps to something like 'select 1' and run it to tell Quest spotlight monitoring system that everything is ok on the server now.

CA Agent

CA provides the full spectrum of monitoring agent for a wide range of operating systems (Unix, Linux, Solaris, Windows)  and actually sql server Monitoring. The disadvantage of CA is its configuring. Settings are stored in configuration files, to restart the CA Agent services you need to run from command line

awservices stop
awservices start 
awservices status

However CA Agent is still very powerful and agile monitoring system

HP Openview

 HP Openview is similar by its functionality to CA Agent but is more flexible and lets say polished. The advantage of this system is integration into HP Openview Service Center , of course if you use this hp's service management system.

SQL Server Development tools

Version storage systems

As for any other source code versioning (C++, C#, Java, Pascal etc)  you can use version storage systems such as VSS and CVS.

Database design tools

SQL Server management studio

 For sql server 2000 - Enterprise Manager + Query Analyzer, for SQL Server 2005 and SQL Server 2008 - SQL Server Management Studion (SSMS). These embedded  sql server toos are basic to work with t-sql scripts. SSMS is also used by DBAs for basic database administration purpose: backup/restore tasks, sql server trouble shooting, sql server status check etc. SSMS also provides ability to create database diagrams which is very useful if you can't afford third party tools such as Enterprise Architect or Embracadero Er Studio. The main advantage of SSMS is that it is supplied with sql server itself so you can always (ok, almost always) find it on the server you need to deal with.

Sparx systems Enterprise Architect

 Allows to design not only databases but also applications. UML diagrams such as case scenarios, classes, dependencies etc. Enterprise Architect allows developing ER diagrams and sql code based on these diagrams for many DBMSs. EA also provides reverse engineering for existing databases.

Embracadero Er Studio

 ER Studio is very good solution for databases design. This functionality is limited to this but it is perfect. Er Studio allows working with databases and its schemas containing more than 500 tables (while enterprise architect gets veeery slow when there are ~100 tables in your er model). Also provides great opportunity of reverse engineering of existing databases and scripting er-models to major dbms creation scripts (Oracle, MS SQL Server, Sybase, MySQL).

Backup automation tools

 On production environments DB-Staff usually implements backup strategies to have backups on centralized tape storage. For this purpose Maintenance plans may be used but it is more convenient to have a third-party backup automation system such as IBM Tivoli Data Protector or HP OpenView Data Protector or Veritas backup.

 Tivoli Data Protector

 TDP is an extension for TSM (Tivoli storage manager). Tivoli Data protector allows you to make full and transaction log backups and does not allow you to make differential backups. TDP is not easy to deploy and configure, settings lie in ancient configuration files. Database to backup are picked up by wildcards for instance master, T*, M*, clo* string in settings file will tell TDP to backup master database and all databases that names start from T, M, and clo. TDP does not check for database recovery mode so if you changed your database recovery mode from FULL to SIMPLE and did not change the settings file you will see errors in your sql server log file about inability to backup log of a database that is in SIMPLE mode.

 HP OpenView Data Protector

 HP Data protector allows you all the same as TDP (not sure about differential backups) but is more clever regarding database recovery model. The advantage of HP Data Protector is centralized console with its own logins/permissions.

SQL Server Utilities

Tools to work with SQL Server database transaction logs

There is very often need to investigate who or what changed or deleted some particular data or caused locking etc. For this purpose it would be really nice to read database log to see the operations that happened on the database within a time period.

DBCC LOG

 DBCC LOG is embedded undocumented dbcc comand. Its syntax is as following

dbcc log (databasename, 2)

 2-is view option. There are also 1, 3, 4 options and 2 is most informative view. However the information returned can hardly be read..

Lumigent LogExplorer

Good alternative to dbcc log command is Lumigent Log Explorer. LogExplorer advantages over dbcc log are

• much much more convenient presentation of log data
• ability to reads log of existing online database and reads log backups for existing databases.