rusanu.com

A new error started showing up in SQL Server 2008 SP2 installations:

The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.

661 sure is earlier than 662, so what seems to be the problem? This error message is a bit misleading. SQL Server 2008 supports database version 655 and earlier. But with support for 15000 partitions in SQL Server 2008 SP2, databases enabled for 15000 partitions are upgraded to version 662. This upgrade is necessary to prevent an SQL Server 2008 R2 instance from attaching a database that has more than 1000 partitions in it, since the code in R2 RTM does not understand 15000 partitions and the effects would be unpredictable. So SQL Server 2008 SP2 does indeed support version 662, but it does not support version 661. This behavior is explained in the Support for 15000 Partitions.docx document, although the database versions involved are not explicitly called out.

So the error message above should be really read as:

The database cannot be opened because it is version 661. This server supports versions 662, 655 and earlier than 655. A downgrade path is not supported

With this information the correct resolution can be achieved: the user is trying to attach a SQL Server 2008 R2 database (v. 661) to an SQL Server 2008 SP2 instance. This is not supported. User has to either upgrade the SQL Server 2008 SP2 instance to SQL Server 2008 R2, or it has to attach the database back to a R2 instance and copy out the data from the database into SQL Server 2008 instance database, eg. using the Import and Export Wizard.

When SQL Server 2005 introduced BEGIN TRY and BEGIN CATCH syntax, it was a huge improvement over the previous error handling based on @@ERROR check after each statement. Finally, T-SQL joined the rank of programming languages, no more just a data access language. Experience has shown that exception handling leads to better code compared to error checks. Yes, SEH is slower, but is basically impossible to maintain the code discipline to check @@ERROR after every operation, so exception handling is just so much easier to get right. And besides, @@ERROR never had such a masterpiece article to guide you trough like A Crash Course on the Depths of Win32™ Structured Exception Handling.

But when trying to use the new TRY/CATCH exception handling in T-SQL code, one problem quickly became apparent: the CATCH block was masking the original error metadata: error number/severity/state, error text, origin line and so on. Within a CATCH block the code was only allowed to raise a *new* error. Sure, the original error information could be passed on in the raised error message, but only as a message. The all important error code was changed. This may seem like a minor issue, but turns out to have a quite serious cascading effect: the caller now has to understand the new error codes raised by your code, instead of the original system error codes. If the application code was prepared to handle deadlocks (error code 1205) in a certain way (eg. retry the transaction), with a T-SQL TRY/CATCH block the deadlock error code would all of the sudden translate into something above 50000.

Read the rest of this entry »

Along with SQLPASS Summit 2010 announcements on SQL Server “Denali” features the MSDN site has published preliminary content on some of these features. Going over the “HADR” Overview (SQL Server) content we can get an early idea about this feature. This post summarizes the AlwaysOn technology, and compares it with its predecessor and close cousin, Database Mirroring. For brevity, I am intentionally omitting a lot of details.

The AlwaysOn technology in SQL Server “Denali”, also known by the project name “HADR” and often called Hadron, it is a huge improvement over its predecessor, Database Mirroring. Like Mirroring, AlwaysOn is also based on physical replication of database by shipping over the transaction log. In fact, it is not only similar to Database Mirroring but actually using the DBM technologies to replicate the database. The steps to set up AlwaysOn contain the steps to set up a Mirroring sessions, and the mirroring endpoints, catalog views and DMVs are still used to set up and monitor AlwaysOn. But AlwaysOn brings three more Aces to the table to make an unbeatable play:

Availability Groups

Databases with dependencies on one another fail over together, as a group.

Multiple Secondaries

AlwaysOn will allow for multiple standby replicas for each availability group.

Readable Secondaries

The standby replicas are accessible for read-only operations.

The following table shows the main differences between AlwaysOn and Database Mirroring:

Availability Groups

DBM considers each mirrored database as an individual entity:

But often application are deployed on several databases contained in an instance and there are tight dependencies between them (eg. cross database queries and procedures). With DBM one had to set up complicated logic to force failover of all related databases if one individual database was occurring a failover event. AlwaysOn introduces Availability Groups that allows explicit declaration of such dependencies. A failover occurs always as an entire group, all databases in the group fail over together become available on the new primary host.

The individual databases inside an Availability Group are still replicated using the Database Mirroring technology:

Multiple Secondaries

Database Mirroring is a private affair between two hosts. A mirroring session can only have on Principal and one Mirror, and they can switch roles. From a High Availability point of view the risk involved in losing one of the partner was fairly serious. Until the operations were set in place to either add a new partner or bring back the old partner online, the system would run at the risk of loosing availability on any further incident. With AlwaysOn there can be multiple stand-by secondary availability groups, on multiple hosts. This allows for a much safer operations, where multiple failures could be survived without loss of availability. This reduces the cost of achieving ‘five nines’ availability numbers, and eases planning, deployment and operating of mission critical systems.

The figure bellow show a possible AlwaysOn deployment on a 4 node cluster:

• Availability Group 1 contains 2 databases and 3 nodes of the cluster have joined this availability group. The SQL Server instance on node A is the the primary replica and the ones nodes B and C each host an availability replica.
• Availability Group 2 contains one database, it has the primary availability group running on the SQL Server instance on node B of the cluster and an availability secondary replica on the instance on node A of the cluster.
• Availability Group 3 contains five databases, it has the primary availability group running on the SQL Server instance on node D of the cluster and an availability secondary replica on the instance on node C of the cluster.
• Availability Group 4 contains two databases, it has the primary availability group running on the SQL Server instance on node B of the cluster and an availability secondary replica on the instance on node D of the cluster.

Readable Secondaries

Secondary replicas are readable in real-time. All read-only operations are allowed on databases in the secondary availability groups. Coupled with the capability to have multiple secondaries, this gives a very nice solution for Scale Out reads. Unlike Database Mirroring database snapshot solution, that was giving a point-in-time snapshot view of the database, readable secondaries allow for real query access to the content of the secondary database, in real-time for up-to-date changes. Access is read-only, no updates are permitted, and all queries run automatically under snapshot isolation model (lock hints and explicitly set isolation levels are ignored). The queries always get a transactionally consistent view of the data, as fresh as allowed by the underlying mirroring session that continuously updates the database. With synchronous mirroring this means that the scale-out achieves the all elusive golden standard of reads that are always perfectly consistent with the last committed writes *on any replica*, with no lag! So far this was impossible to achieve with any other scale-out technology [Correction 11/22: As Gopal points out bellow, this is still not achievable because synchronous mirroring only requires the shipped log to be hardened to disk, not redone. You can get pretty close, but but the application still needs to be prepared to handle stale reads].

The following image shows a possible AlwaysOn reads Scale-Out deployed on a 3 node cluster to serve a web farm. Read requests can be server by any of the availability replicas, including the Primary Replica. Write requests have to be all routed to the Primary Replica:

What gives

Database Mirroring has always been a viable choice for small businesses. Available in Standard Edition, running on commodity hardware and with minimal licensing cost requirements, it was basically dirt cheap to implement. AlwaysOn is on a different league. Its availability is no longer based on quorum decided by a witness, as in Database Mirroring, but instead is based on Windows Server Failover Clustering. WSFC is a premium feature available only on Enterprise and DataCenter editions of the Windows Server family, and is supported only on hardware that has passed the Validate a Configuration Wizard. Small businesses can still deploy basic Database Mirroring, but they will have to support a significantly higher entry cost to enjoy the advantages of the AlwaysOn technology.