SQL Server bulks up

SQL Server 2005 also brings some exciting new features for increasing availability and smoothing disaster recovery. In the area of HA (high availability), Microsoft has introduced database mirroring and made some important changes to replication. Now schema changes will be replicated automatically to the subscribers; no longer must the DBA run code by hand to do it. Snapshots no longer need to be restarted after failures, but will automatically resume after network errors or other glitches, allowing you to get your HA scenario up and running much faster. Also, logical record replication, which allows you to define parent and child data that will either be replicated together or not at all, ensures more consistent HA scenarios and eliminates logical errors on the subscriber side.

Free IT resource Open Source Business Conference (OSBC) May 22-23, 2007 Sponsored by OSBC Free IT resource Virtualization Insights from Top Experts - Learn how virtualization gets real! Sponsored by Dell Return to special report DOWNLOAD PDF Click here to download InfoWorld's special report Microsoft SQL Server Microsoft SQL Server 2005 Microsoft, microsoft.com Excellent  9.1 criteria score weight Manageability 9 25% Performance 9 25% Availability 9 20% Scalability 9 20% Value 10 10% Cost: Standard Edition starts at $5,999 per processor or $1,849 with 5 CALs. Enterprise Edition starts at $24,999 per processor or $13,969 with 25 CALs. Platforms: Windows Server 2003 Bottom Line: SQL Server 2005 vastly improves capabilities on all fronts, including development, integration, management, and BI. Companies will be able to run safer databases, better manage their environment, and finally create a truly 24/7 operation. Among new high-availability features, partial restores will allow databases to be brought online faster after failures, and database mirroring, although not yet officially supported, will provide automatic fail-over for log shipping scenarios. About our Reviews and Scoring Methodology

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.