Top 10 SQL Server 2005 New Features for Operations

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

May 27, 2006

You may have heard incredible amount of hype about SQL Server 2005 new features since its beta was introduced more than two years ago. How it matches up against ORACLE, cost of ownership, even being ballpark 20% faster than SQL Server 2000. You might have even read articles on how it can process millions of transactions when configured with 64 processors, which maybe a bit far-fetched if your server has only 4 processors. So how better is actually SQL Server 2005 for operations 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 - including financial transactions processing - and 24/7 consumer websites, I came up with a rating. These features are mainly for operations. Albeit sometimes the lines are blurred: a new operational feature may turn out to be a development and/or administration godsend as well. So without much further ado, here comes the Top 10 operations list for Microsoft's latest version of SQL Server.

1. Database Mirroring. Dubbed as "Poorman's Clustering" provides few seconds automatic switchover to standby server. Much improved over log shipping where failover typically took ½ - 1 hour. There are several modes of operation: automatic, manual and forced failover. In a way it is even better than clustering because two independent servers operating in parallel. It does not come with virtual IP, so you have to provide that from a third-part source.

2. Reporting Services. Full-blown Business Intelligence delivery layer. Will be popular with shops which don't want to spend big bucks with BI vendors.

3. Online Reindexing. A real miracle improvement for 24/7 shops. Users won't be blocked anymore why a table is being totally reindexed usually with a FILL FACTOR for next day's operations.

4. Security Improvements. Major functionality improvements in security will help large organizations to setup their environments appropriately. One example for improvement: database object visibility can be restricted. For small organizations even SQL Server 2000 security facilities are too overwhelming.

5. Service Broker. Asynchronous queuing facility is a freebie. It is an application add-on that can be used to build database administration tools or business applications.

6. Event Notification. Also an application add-on, can be used to build system or application near real-time notification. A system notification maybe a table dropped in a database. An application notification maybe an order placed with the company.

7. Analysis Services. Great number of improvements over SQL Server 2000 AS. Among the new features, support for the Unified Dimensional Model, MDX improvements and new data mining tools.

8. Business Intelligence Development Studio. A unified environment to build BI based on SSAS, SSIS and SSRS.

9. Table/Index Partitioning. Really helpful when you are operating with tables over 100 million rows or over 10GB in size.

10. Memory Support. The Standard and Enterprise Edition provides for unlimited memory support. Memory, being cheap, can be used to boost server performance.