Top 10 SQL Server 2005 New Features for DBAs

By Kalman Toth, M.Phil., M.Phil., MCDBA

May 20, 2006

You may have heard tons of hype about SQL Server 2005 new features. Scalability, cost of ownership, even being 20% faster than SQL Server 2000. You might have even seen scripts and demonstrations on integration with Visual Studio 2005. So how better is actually SQL Server 2005 for administrators after the hype is cut out? Based on my 15 years of experience with T-SQL and SQL Server/Sybase administration in heavy-duty practical applications of Wall Street high finance and consumer websites, I came up with a rating. These features are mainly for administration not for programming. Albeit sometimes the lines are blurred: a new administration feature may turn out to be a development godsend as well. So without much further ado, here comes the Top 10 administration list for Microsoft's latest version of SQL Server.

1. Management Studio. Heavy duty, much better, more reliable than the Enterprise Manager/Query Analyzer duo in SQL Server 2000. One negative: SSIS (DTS successor) is not included. For marketing purposes Microsoft moved it into Business Intelligence Studio.

2. SSIS. A really superior replacement for DTS with lots of new features and excellent error logging.

3. Indexes including non-indexed columns. This feature can be a make or fail feature in several applications.

4. Deadlock visualizer. Really takes the mystery out of deadlocks. Clearly show the participant queries, dead and surviving.

5. Column-level data encryption/decryption. Long time missing, but finally here. High powered encryption like 3DES made real easy.

6. Correlating perfmon chart with profiler trace. This feature makes finding the performance hog "culprit" real easy.

7. Event notification on server events such as logins. Like a dream come true, futuristic feature for administrators.

8. SQLCMD. Truly super-duper enhancement of OSQL with true scripting language.

9. Connect as dedicated administrator (DAC). Surely can be day saver when the CPU-s are spinning 100% and you cannot get in through Management Studio.

10. Configuration Manager. This tool unites several distinct operational management components of SQL Server 2000 such as services manager and network configuration.