SQL Server 2005 offers a plethora of new features and enhancements that improve productivity, efficiency, availability, and administrative ease. In this article, I discuss 10 of the more advanced features of the SQL Server 2005 Database Engine.
1. Database mirroring
Database mirroring is a new high-availability feature in SQL Server 2005. It's similar to server clustering in that failover is achieved by the use of a stand-by server; the difference is that the failover is at the database level rather than the server level. The primary database continuously sends transaction logs to the backup database on a separate SQL Server instance. A third SQL Server instance is then used as a witness database to monitor the interaction between the primary and the mirror databases.
2. Database snapshots
A database snapshot is essentially an instant read-only copy of a database, and it is a great candidate for any type of reporting solution for your company. In addition to being a great reporting tool, you can revert control from your primary database to your snapshot database in the event of an error. The only data loss would be from the point of creation of the database snapshot to the event of failure.
3. CLR integration
With SQL Server 2005, you now have the ability to create custom .NET objects with the database engine. For example, stored procedures, triggers, and functions can now be created using familiar .NET languages such as VB and C#. Exposing this functionality gives you tools that you never had access to before such as regular expressions.
4. Service Broker
This feature gives you the ability to create asynchronous, message-based applications in the database entirely through TSQL. The database engine guarantees message delivery, message order consistency, and handles message grouping. In addition, Service Broker gives you the ability to send messages between different SQL Server instances. Server Broker is also used in several other features in SQL Server 2005. For example, you can define Event Nonfictions in the database to send a message to a Queue in the database when someone attempts to alter a table structure, of if there is a string of login failures.
5. DDL triggers
In previous articles, I outlined how you can use data definition language (DDL) triggers in SQL Server 2005 to implement custom database and server auditing solutions for Sarbanes-Oxley compliance (here are part one and part two of my SOX articles). DDL triggers are defined at the server or database level and fire when DDL statements occur. This gives you the ability to audit when new tables, stored procedures, or logins are created.
6. Ranking functions
SQL Server 2005 provides you with the ability to rank result sets returned from the database engine. This allows you to customize the manner in which result sets are returned, such as creating customized paging functions for Web site data.
7. Row versioning-based isolation levels
This new database engine feature improves database read concurrency by reducing the amount of locks being used in your database. There are two versions of this feature (both of which must be enabled at the database level):
· Read Committed Isolation Using Row Versioning is used at the individual statement level, and guarantees that the data is consistent for the duration of the statement.
· Snapshot Isolation is used at the transaction level, and guarantees that the data is consistent for the duration of the transaction.
The database engine is able to guarantee the consistency through row versions stored in the tempdb database. When a statement or transaction is issued with their respective isolation levels, read operations accessing the same data that is being involved in a transaction will read from the previous version of the data that is stored in tempdb. Using these techniques in the appropriate situations can significantly decrease your database locking issues.
8. XML integration
SQL Server 2005 introduces the new XML data-type. You can store full XML documents in this new data-type, and you can place validations on the well-formed documents in the database. Additional enhancements include the ability to query the XML documents and create indexes on the XML data-type.
9. TRY...CATCH
In a previous article, I outlined how you can use the new TRY...CATCH constructs in SQL Server 2005 to catch and handle deadlocks when they occur in the database. This long-awaited feature simplifies error handling in the database.
10. Database Mail
Database Mail, the eventual successor to SQL Mail, is a greatly enhanced e-mail solution available in the database engine. With Database Mail, there is no longer a dependency on Microsoft Outlook or MAPI e-mail clients. Database Mail uses standard SMTP to send e-mail messages. These messages may contain query results, attachments (which can be governed by the DBA), and is fully cluster aware. In addition, the e-mail process runs outside of the database engine space, which means that messages can continue to be queued even when the database engine has stopped.
