SQL Server bulks up
Microsoft's beefy remake brings hefty new capabilities and a slew of new tools to master
Mirroring allows you to specify a database on another server to be kept in synch with a primary database. Doing this allows you to automatically fail over to the mirror server should the primary fail, providing SQL Server’s first zero data loss HA scenario. This configuration can be expensive to applications and networks, so you’ll need to test it thoroughly, but the capability is finally here. Note, however, that mirroring doesn’t yet qualify as a production feature in this release. Microsoft is shipping SQL Server 2005 with mirroring turned off, and the feature is unsupported.
Mirroring performed solidly in my tests, handling fail-overs of a 100GB database without a hitch. The monitoring around mirroring, however, is still very primitive, consisting of only a small number of DMVs and performance counters, so it’s not especially easy to find out how much data has been replicated, how far behind the secondary system is from the primary, or the cost of the replication. The difficulty in gaining visibility into the process may fray some nerves.
As for disaster recovery, SQL Server 2005 slashes the downtime necessary when restoring a backup. The new online restore capability allows users to begin working in the database as objects become available. With SQL Server 2000, the entire database must be restored before it can be made available to users. Given the time it takes to completely restore a multi-terabyte database, online restores could mean the difference between operating productively and being crippled for hours.
Similarly, you can now fix database corruption with less impact on users. Page-level restore allows you to restore a single corrupted page without hindering the availability of the rest of the database.
Developing for a new age
Code enhancements can be divided into two basic categories: those for developers and those for DBAs. Apart from the .Net/CLR integration, CTEs (Common Table Expressions) are arguably the most significant for developers. Besides elegantly solving the problem of recursive queries, which I won’t get into, CTEs allow developers to simplify code by using inline query definitions in place of views or derived tables. CTEs not only streamline complex queries, but perform the same as derived tables, so there isn’t any extra overhead to implementing them.
Another important enhancement: The TOP clause, which allows you to define that only a certain number of a result set is returned (such as the top 10 salespeople by revenue in the United States), can now take a parameter as an argument. I know it doesn’t sound like much, but this is one of those cases where a relatively small change can make a huge difference in development effort. In short, instead of having to include a static value (TOP 10, for example) in the code, you can now include a variable, allowing users to report the TOP 10 or TOP 100 (or any number they like) on the fly.
Other goodies for developers include ranking, random data sampling, pivot/unpivot, synonyms, much richer error handling, and code security context (see the infographic “Cool tools for SQL coders”). Many developers will also be singing in their Cheerios over SQL Server 2005’s native XML storage capabilities. As I mentioned in my April 2004 comparison of the leading relational databases’ XML handling capabilities, SQL Server 2000 was limited to “shredding” XML into relational data, a cumbersome process that fails to preserve XML hierarchies. Now, you can import XML into a column and not only perform native XML queries, but also index it for fast retrieval.