Database Mirroring Deprecated in SQL 2012

From the newly released SQL 2012 Books Online documentation:

This note shows that as of SQL Server 2012, which will be Release-To-Manufacturing (RTM) effective 04/01/2012, Database Mirroring will be marked as deprecated.  What does this mean?  As the note states, the feature will be removed in a future version of the SQL Server product and as such should not be used in any planned development for the future.  The feature *will* work in SQL Server 2012, but will be removed at some point after that.

There are two different types of deprecation for SQL Server features – “Features Not Supported in the Next Version of SQL Server” and “Features Not Supported in a Future Version of SQL Server.”  In this case, the Next Version is whatever comes after SQL 2012 (for our purposes we will call this version SQL 201X), while a “Future Version” means some version after that (SQL 201x+1).  Specifically, features not supported in a future version means “The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be removed in a later version. The specific version of SQL Server has not been determined.”

Microsoft publishes a list of both groups of features here, and Database Mirroring is on the “Future Version” list:

This means Database Mirroring will work in SQL 2012 and in the following version SQL 201X, but its status for the next version (SQL 201x+1) is unknown at this time, and will be unknown until the release of SQL 201X and the accompanying deprecation lists for that version.  Microsoft is very heavily advertising the AlwaysOn functionality that is replacing mirroring (as shown above) so mirroring will very likely be removed in the first Future Version (SQL 201X+1).

What does this mean?  Any new system development and design from this point forward needs to allow for the fact that Microsoft will probably not support Database Mirroring in the next five to six years, and since many systems live far beyond five to six years it is important to investigate AlwaysOn Availability Groups as a replacement for Mirroring, even if mirroring will be implemented in the short-term.  This awareness needs to be especially present when designing systems for the current version of SQL Server (2012) but is also relevant for systems being built for older versions of the product (SQL 2008 and 2008R2) as they may well live long enough to be upgraded in the future to a version of SQL Server that no longer supports Database Mirroring.

 Also, check out these other great bloggers on this same subject:  

• Glenn Berry (@GlennAlanBerry) - "SQL Server Database Mirroring (Not Dead Yet)"
• Ted Kreuger (@onpnt) - "Database Mirroring Deprecated in 2012"
•  Allan Hirt (@SQLHA) - "Database Mirroring Deprecated as of SQL Server 2012"

Clearing Job Notifications - HELP!

I have inherited a SQL 2005 server with SQL Agent job notifications in place, and a request to clear the existing notifications on all jobs and then set up new notifications on only a subset of jobs.

Sounds easy right?

Wrote a cursor (gasp) to loop through the jobs and build sp_update_job statements using synamic t-sql (double-gasp - you all know you've done it {-:)  of this form:

EXEC msdb.dbo.sp_update_job @job_name = 'DatabaseBackup - SYSTEM_DATABASES - FULL', @notify_level_email = 0, @notify_email_operator_name = NULL

The catch is when I run this I get the following error:
 
Msg 14266, Level 16, State 1, Procedure sp_verify_job, Line 249
The specified '@notify_level_email' is invalid (valid values are: 1, 2, 3).

I looked inside the code for sp_verify_job and found the offending line:

 -- If a valid operator is specified the level must be non-zero 
    IF (@notify_level_email = 0) 
    BEGIN 
      RAISERROR(14266, -1, -1, '@notify_level_email', '1, 2, 3') 
      RETURN(1) -- Failure 
    END 

Looking at what appeared to be a Catch-22 (can't clear the notifications unless the name is NULL) I turned to Twitter:



...and I received a response from SQL MCM and all-around knowledgeable source Robert Davis (@SQLSoldier):




I pondered briefly and considered - would the system really let me set the operator to NULL without setting the notify_level to 0?  If it did what would Management Studio show? Wondering what I would find I went ahead and gave it a try...

EXEC msdb.dbo.sp_update_job @job_name = 'DatabaseBackup - USER_DATABASES - FULL', @notify_email_operator_name = NULL

No error!  Did it really work?



Apparently not.

I checked sysjobs to verify:

select left(name,40) as JobName, notify_level_email,notify_email_operator_id
from msdb.dbo.sysjobs where name = 'DatabaseBackup - USER_DATABASES - FULL'

...and found what I expected:



I have officially run out of ideas (that do not involve directly editing the jobs table - sigh) to clear these notifications programmatically - help!

------------

UPDATE: Thanks to further #sqlhelp assistance from @SQLSoldier and @banerjeeamit I was able to get this to work by passing an empty string ('') in for the value of @notify_email_operator rather than the keyword NULL.  This does not require passing a @notify_level_email parameter at all:

EXEC msdb.dbo.sp_update_job @job_name = 'DatabaseBackup - USER_DATABASES - FULL', @notify_email_operator_name = ''

Many thanks!