SQLSaturday #150 – Baton Rouge: Presentations Uploaded

My presentation slide decks and demo scripts from SQLSaturday #150 have been uploaded.

• Managing SQL Server in the Enterprise with TLAs
• SQL Server 2012 Database Engine – Why Upgrade?

Thanks to the planning team for selecting my sessions and thanks to everyone who attended my sessions – I enjoyed the opportunity to share my passion.   

Database Security Roles in msdb

All databases have a standard set of “fixed” database security roles which have been available since SQL Server 2000 and hopefully, even if you are new to SQL Server, you are familiar with this set:

• db_accessadmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_ddladmin
• db_denydatareader
• db_denydatawriter
• db_owner
• db_securityadmin

Back in the day, the msdb database was primarily used for managing backups, SQLAgent jobs, and DTS packages. And, other than the TargetServersRole role for multi-server job management via a master server and target servers, there were no other database roles defined for helping establish a separation of duties security model for tasks controlled via msdb. 

In SQL Server 2005 some long awaited new roles were added to msdb (http://msdn.microsoft.com/en-us/library/ms188283(SQL.90).aspx) which help with segregating permissions for managing SQLAgent Jobs, DTS\SSIS, Database Mirroring, and Database Mail (the replacement for SQL Mail):

• DatabaseMailUserRole
• db_dtsadmin – renamed in later versions as db_ssisadmin
• db_dtsltduser -renamed in later versions as db_ssisltduser
• db_dtsoperator – renamed in later versions as db_ssisoperator
• dbm_monitor – does not appear until database mirroring is implemented
• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole

Then, in SQL Server 2008 even more roles appeared as msdb took on increased importance in the management of new features such as the CMS (Central Management Server), PBM (Policy Based Management), Data Collection, and MDW (Management Data Warehouse).

Thus, the following new roles may be found in SQL Server 2008+:

• dc_admin – see BOL for description of permissions for Data Collector & MDW roles
• dc_operator
• dc_proxy
• mdw_admin
• mdw_reader
• mdw_writer
• PolicyAdministratorRole – members can manage policies
• ServerGroupAdministratorRole – members can manage the CMS
• ServerGroupReaderRole – members can read from the CMS

In SQL 2008 R2 one more set of roles was added for the Server Utility (UCP) feature:

• UtilityCMRReader
• UtilityIMRReader
• UtilityIMRWriter

The above UCP roles are primarily assigned via the Server Utility configuration wizard and Utility Explorer Security tab. The “Utility Reader role” referenced in the Security tab is equivalent to the UtilityCMRReader role. As you may notice, certain service and administrator accounts are automatically in this role and cannot be changed (grayed out).

And, now in SQL Server 2012, believe it or not, there are no new roles in msdb!  So, if you haven’t kept up with all the changes in msdb in the past few versions, this is your chance to catch up and make sure that you are fully utilizing these features and roles as needed in your environment. For a recap of all the features which utilize msdb, you can also refer to an earlier post I wrote – “What’s in Your msdb?”.

Discontinued Features in SQL Server 2012 – Part 4

In the previous installment on this topic, I looked at discontinued features in SQL Server 2012 which may mostly impact your administration scripts.  Today, we are widening the net and looking at discontinued features which may impact either the application layer or administration programs and scripts. Most of these items have been deprecated as far back as SQL Server 2000, so it isn’t like they have gone away overnight. However, if you are still running on SQL Server 2000, then you really haven’t had a need to go replace them…until now.

SQL Mail is finally gone! It would have been totally fine with me if SQL Mail had been discontinued as soon as Database Mail was added in SQL Server 2005.  However, you do lose functionality (receive mail processing) with Database Mail, but the trade-off is much improved reliability, security, supportability, and scalability. For example, you no longer need an Extended MAPI client (e.g. Outlook) installed; you don’t run the risk of attacks via reading mail; and Database Mail is supported on clusters. So, if you are still using the read\process mail functionality of SQL Mail, you’ll need to find another method for your application to use before upgrading to SQL Server 2012.  If all you are doing are send mail tasks, including notifications from SQLAgent, then the switch to Database Mail is a no-brainer and you can do it now in SQL Server 2005/2008/2008R2. However, this is more than just a configuration change; it does require usage coding changes for example from xp_sendmail to sp_send_dbmail.  

Unless you’ve been stuck on SQL Server 2000, you have probably already started converting applications using SQL-DMO (Database Management Objects) to use SQL-SMO (SQL Server Management Objects) when you found you wanted to access newer features in SQL Server 2005+ that were not available via SQL-DMO.  If you want to use your SQL-DMO scripts against SQL Server 2012 – you must convert to SQL-SMO; SQL-DMO is discontinued.

If you have databases in compatibility mode 80, it might be because the code using them is still using the old non-ANSI outer join syntax “*=” or “=*”.  This syntax is not supported in later compatibility modes and because compatibility mode 80 is not supported in SQL Server 2012, this syntax is no longer recognized in SQL Server 2012 in any scenario.  You’ll need to get all of this code up-to-date with current ANSI join syntax used in the FROM clause.

Another T-SQL syntax which is disappearing is the COMPUTE / COMPUTE BY clause in SELECT statements; you will need to convert these statements to use the ROLLUP option of the GROUP BY clause.

If you tried to speed up the results returned to your client or force a particular query plan by using the FASTFIRSTROW query hint, you’ll need to switch to the OPTION (FAST n) syntax as FASTFIRSTROW is no longer available.  For more info on the overall impact of using this option, see this blog from the SQL Server Query Optimization team and this one from SQL MVP Grant Fritchey. As with all query hints, you should re-evaluate these periodically as they may no longer be as beneficial as when originally added.

In SQL Server 2012, you can no longer invoke an ad-hoc error message number using RAISERROR integer ‘message’ syntax as in: RAISERROR 50111 ‘Sample error message’.  You’ll need to convert to the RAISERROR() syntax:  RAISERROR (‘Sample error message’, 10, 1). But, if you want to really get your code current, convert to TRY…CATCH (available since SQL Server 2005) and use THROW (new in SQL Server 2012). There are some differences in behavior between RAISERROR and THROW and these are documented in BOL’s THROW entry.

I’ve only highlighted a sample of discontinued features in this set of articles; for the complete list of discontinued database engine features in SQL Server 2012, please reference BOL. And for help finding out if you are using these features (which were deprecated in earlier releases), I covered that here.

Happy upgrading!

Discontinued Features in SQL Server 2012 – Part 3

Part 1 discussed some discrepancies between documentation of discontinued features in SQL Server 2012 and actual implementation. Part 2 discussed the database compatibility level minimum requirement. Now, in this installment, let’s look at some features which have disappeared from SQL Server 2012 which may impact your administration scripts.

The first one, the DATABASEPROPERTY function, I already covered in Part 1. While it doesn’t appear to be completely gone, the documentation states that it is gone. So, as you edit your scripts for all the other items mentioned next don’t forget to make the change to use DATABASEPROPERTYEX instead of DATABASEPROPERTY.

Next up, sp_dboption is gone. That’s right – it’s not there… You get a nice red error message in SSMS when you try to use it.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure ‘sp_dboption’.

Everything you needed to set using sp_dboption is now available via the ALTER DATABASE syntax – and pretty much has been since SQL Server 2000. But, if you are as “experienced” as I am (going back to 4.21), then you may still have some old scripts laying around with sp_dboption in them.  Unless you still have some 7.0 or earlier systems on which to use those scripts – update them to use ALTER DATABASE and they should work on SQL Server 2000 and up (with an exception for the database ownership chaining option which was added in SQL Server 2000 SP3 and only available to set via sp_dboption in SQL Server 2000).

Now – how about a review of your Backup and Restore scripts?  Did you make use of the WITH PASSWORD or WITH MEDIAPASSWORD options on your BACKUP DATABASE and BACKUP LOG commands? If so, you’ll need to remove those options for use on SQL Server 2012. These options have been deprecated since SQL Server 2005, so in theory you should have stopped using them at that point in time. You can still use these options with the RESTORE command for those old backups which used a password, but you can’t make any new backups using the password options.  Keep in mind though that the ability to use the RESTORE command for backups with passwords is deprecated and will be removed in future version.

This next one will really date you (and me!) – do you have RESTORE DATABASE…WITH DBO_ONLY syntax in any scripts?  If so, you should have replaced WITH DBO_ONLY as WITH RESTRICTED_USER starting with SQL Server 2000.

Do you run scripts to dump the meta-data from the system tables\views?  Do they include the sys.database_principal_aliases view? If so, it is gone now, too. You’ll receive error 208 (invalid object name) if you reference it.  Aliases have been replaced by roles.

OK – so now you know what to look for, but how about an easy way to find where these keywords are used in your scripts?  Here’s a quick PowerShell command example (adapted from http://guyellisrocks.com/powershell/powershell-grep/) to help: 

Get-ChildItem -include *.sql -recurse | Select-String “sp_dboption”

Have fun hunting down these obsolete keywords!

Discontinued Features in SQL Server 2012 – Part 2

Previously I discussed a couple of features where there is a discrepancy between the documentation and the implementation as to their status as either discontinued or deprecated in SQL Server 2012. Today, I’m looking at another discontinued feature which you will need to identify and address before upgrading to SQL Server 2012 – database compatibility level 80.

A database’s compatibility level is used by the database engine to ensure that most behavior is consistent to that version for the specific database.  This database-level setting is intended as a work around to allow applications to upgrade to a newer version of the database engine for which usually all databases can derive some benefits without requiring the application to make any changes (in theory). If an individual application database is using a feature or syntax previously supported for which the behavior changed in the later version, then you can set a down-level compatibility to keep the original behavior from that version….until your application development team or your vendor can make the code compatible with the current version of SQL Server.  

Beginning with SQL Server 2008, Microsoft’s announced life cycle policy for SQL Server was that a new version will attempt backwards compatibility with only the 2 previous versions. Since databases in SQL Server 2012 are compatibility level 110, then only compatibility level 100 (used for both SQL Server 2008 and SQL Server 2008 R2) and compatibility level 90 (SQL Server 2005) are additionally supported.  Thus, database compatibility level 80 (SQL Server 2000) will not be supported in SQL Server 2012 and is therefore classified as a discontinued feature.

If you are a fairly new DBA who inherited your database systems and don’t know their history, you’d better check all the databases carefully for compatibility level.  While SQL Server 2008 and SQL Server 2008 R2 both support databases in 80, 90, and 100 compatibility mode, SQL Server 2005 supports databases in 70, 80, and 90 compatibility modes (and allows 60 & 65!).  Thus, although you can upgrade a SQL Server 2005 instance to SQL Server 2012 – you need to check that all of the application databases are at compatibility level 90. You might be surprised (or not) to find that you still have databases which exist with compatibility level 6x on your SQL Server 2005 instances! 

So, how do you find these? One way is to use a multi-server query using a CMS group containing all your servers…

Select [name],[compatibility_level] From sys.databases

Where [compatibility_level]< 90;

How do you fix it? Well, ultimately you have to run one of these commands:

– If source system is SQL 2005

ALTER DATABASE reallyold_database

SET COMPATIBILITY_LEVEL = 90;

– If source system is SQL 2008 or 2008 R2 and compatibility_level is 80,

– then you might as well take it as far as you can go!

ALTER DATABASE reallyold_database

SET COMPATIBILITY_LEVEL = 100;

But, first you’ll have to figure out what is keeping it from being at the higher compatibility level already.  Check the SQL Server documentation under “ALTER DATABASE Compatibility Level (Transact-SQL)” for details of differences between compatibility levels. Be sure to check the SQL Server 2008 version of the documentation for differences between the 80 and 90 compatibility levels. In some cases, it is simply that the database was upgraded and the former DBA didn’t realize that they needed to change the compatibility level after the upgrade.  And, of course, don’t you now forget to change upgraded databases to compatibility level 110 after you complete the upgrade to SQL Server 2012!

 

Discontinued Features in SQL Server 2012 – Part 1

Now that SQL Server 2012 has officially been released, it is time to start revisiting deprecated features.  But, first, a definition reminder:

• “Discontinued” means that the feature is not available in SQL Server 2012.
• “Deprecated” means that the feature will be removed in a future version.  “Deprecated” also typically means that there are no updates to this feature in the current release.

Thus, I will first review a couple of the features which you may have used in prior versions which were deprecated in earlier versions and now discontinued in SQL Server 2012 – or are they?

SOAP/HTTP Endpoints

Per the note in the CREATE ENDPOINT BOL link this feature is discontinued. However, it is also currently listed in BOL under “Deprecated SQL Server Features in SQL Server 2012” which implies it won’t go away until a release after SQL Server 2012.  So which is it – gone already or still waiting to go?  A test of running sample code to create such an endpoint in SQL Server 2012 results in the error message shown below supporting it is now actually discontinued, not deprecated for removal later. Thus, if you have used these endpoint types, you will need to find them and convert to Windows Communication Foundation (WCF) or ASP.NET before upgrading to SQL Server 2012. Check this BOL reference for assistance.

Here’s the sample code that was executed which was copied from SQL Server 2008 R2 BOL:

DATABASEPROPERTY Function

This function has been replaced and superseded with the DATABASEPROPERTYEX function since SQL Server 2005.  Intellisense in SSMS 2012 will flag that it doesn’t recognize this as valid syntax (see image below).  The unexpected thing here is when you execute a query using DATABASEPROPERTY in SQL Server 2012 – you don’t get an error!  It seems to still work (see results image below). I don’t know about you – but this was not the result I expected.  If DATABASEPROPERTY is no longer valid as indicated in SSMS (and BOL) – shouldn’t the execution return an invalid function error?  At any rate, it appears you may have a little longer to track down your usage of this function and update it to use DATABASEPROPERTYEX as DATABASEPROPERTY does still work for the time being.   

Obviously, it doesn’t hurt to start replacing the usage of deprecated features as soon as possible. Then, you don’t have to worry as much about which future version actually discontinues the feature, if you have already discontinued your usage of it.

Deprecated Features in SQL Server 2012

Earlier in the year, I wrote a series of posts on Deprecated Features with a promise to revisit as the final version of SQL Server “Denali”, now known as SQL Server 2012, neared. With RC0 recently released, now is the time for that review!

To recap, deprecated features are still available in a version, but are targeted for removal in a future version. Thus, you should avoid using these features in any new development work and consider replacing them where currently used as soon as convenient. So without further delay, the features which are still available in SQL Server 2012 Database Engine, but now deprecated include:

Data programmability features which should be replaced with Windows Communications Foundation (WCF) or ASP.NET:

• SOAP/HTTP endpoints with CREATE ENDPOINT (Transact-SQL) and ALTER ENDPOINT (Transact-SQL)
• sys.soap_endpoints (Transact-SQL)
• sys.endpoint_webmethods (Transact-SQL)

Slipstream functionality will go away – sort of. Starting with SQL Server 2012, the command line parameters /PCUSource and /CUSource should be replaced with the Product Update feature (/UpdateSource) which I have previously discussed. Although the old parameters are still valid, I’d switch to the new parameters ASAP as they are much easier to use and maintaining your source is also simplified.  

There are also Database Engine Backup and Restore T-SQL command changes: RESTORE { DATABASE | LOG } WITH [MEDIA]PASSWORD is deprecated. While BACKUP { DATABASE | LOG } WITH PASSWORD and BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD are actually discontinued in SQL Server 2012. These commands have no replacement.

Database Compatibility level 90 and upgrades from SQL Server 2005 will not be supported in the version after SQL Server 2012.  This is in keeping with Microsoft’s stated support model of only supporting 2 down-level versions for upgrades. You should be well aware of this by now, but I’m including it as a reminder for those new to SQL Server version management. For example, SQL Server 2000 (or database compatibility level 80) is not a supported version to upgrade to SQL Server 2012.   

The ability to return results sets from triggers will not be supported post-SQL Server 2012.

As stronger encryption algorithms are now available, encryption using RC4 and RC4_128 has been deprecated. However, decrypting RC4 and RC4_128 has not been deprecated.

Several stored procedures, a global variable, and SET command related to remote servers have been deprecated. It has been recommended for several versions now to replace remote servers with linked servers.  Hopefully you have already done this task.

SET ROWCOUNT for INSERT, UPDATE, and DELETE has been replaced by the TOP keyword, so if you have been using SET ROWCOUNT to limit the number of rows affected by your INSERT, UPDATE, or DELETE statements, you’ll need to switch these to use the TOP keyword instead.

If you’ve been using the table hint HOLDLOCK without parenthesis, then you will need to add the parenthesis in the next release of SQL Server.

On the tools side of things, the SQLMAINT utility has been deprecated with the recommendation to use SQL Server maintenance plans as the replacement.

And if you want to really get a jump on things, along with the list of deprecated features just described for the “next” version of SQL Server (after 2012), you can start eliminating the usage of features listed in “Features Not Supported in a Future Version of SQL Server” – both can be found here:  http://msdn.microsoft.com/en-us/library/ms143729(v=SQL.110).aspx

If you are using the Full-Text feature of SQL Server then you’ll want to check-out the list of deprecated items specific to that feature here: http://msdn.microsoft.com/en-us/library/cc646010(v=SQL.110).aspx. These include many stored procedures (e.g. sp_fulltext_catalog) which have been replaced with new T-SQL syntax (e.g. CREATE\ALTER).

And, finally, if you are using components other than the Database Engine (i.e. SSAS, SSIS, SSRS), then you’ll need to review the deprecated/discontinued lists for them in their respective Backward Compatibility sections of Books Online.

As I pointed out in Part 3 of my earlier blog series, you can detect most deprecated features by using the SQL Server: Deprecated Features object performance counter and trace events.   A future blog will discuss which previously deprecated features have actually been discontinued in SQL Server 2012 – meaning you will have to address them before upgrading.

SQL Server 2012 RC0 – SSMS Review

Microsoft released SQL Server 2012 RC0 (Release Candidate) this week – just in time for those of us traveling over the holidays to have something to do as we head to Grand-Ma’s (assuming we aren’t the one driving).  With any new release, I suspect most of us do the same thing – run as fast as we can through the install process and open SSMS and start playing!  If you haven’t been previously playing with “Denali” CTP3, then I encourage you to get the RC0 install (on a test server, of course) – if only for the new SSMS.

The first time you connect to an instance in Object Explorer, you’ll immediately notice differences from SSMS 2008 R2.

Oh look! There’s a folder called “AlwaysOn High Availability” – let’s go click it!

Yikes! That wasn’t nice. It seems like SSMS should already know this and have a “not available” icon showing like it does for my SQL Server Agent which is currently disabled.

OK – let’s try the other new folder “Integration Services Catalog”.  That’s interesting because I do have SSIS 2008 installed, but haven’t upgraded it. The only option here when I right-click is to “Create Catalog”. However, then I see that I need CLR enabled and once I do that then I need to make some other decisions.

Ok – I guess this will be something I need to do some more research on to know when we’ll want to use this and exactly how to use it. But, that is the whole purpose of this exercise, seeing what is new and where to focus future in-depth investigation.

So, back to drilling down through Object Explorer to see what else looks new…

Looks like I’m going to have to study up on Extended Events now that they’ve made it into SSMS; and Microsoft appears to be setting up some default entries here.

Now, I’ll start drilling down to the next layer of some of these objects. We use PBM (Policy-Based Management), so let’s see if anything is new there. Yep! Looks like Microsoft is installing policies by default in preparation for AlwaysOn usage.  So, when I get around to playing with AlwaysOn, it will be interesting to revisit these and see if they are automatically enabled when setting up AlwaysOn.

I didn’t show it in the image, but there are new Conditions created upon install for the AlwaysOn policies to use. And, obviously with new features in SQL Server 2012, new facets related to those features have also been added (76 facets in SQL Server 2008 R2 versus 84 in SQL Server 2012). Interestingly, if I connect to a SQL Server 2008 or SQL Server 2008 R2 instance from SSMS 2012, I see all the facets for SQL Server 2012. I think this means I’ll be able to keep using my central PBM repository from SQL Server 2008 R2, but run policies specific for SQL Server 2012 (using our version of EPM). More to investigate!

Lastly for this initial drive-by look, I checked out the Management\Legacy folder. Here’s the comparison of SQL Server 2008 R2 SSMS (first) and SQL Server 2012 (second):

SSMS 2008 R2 Legacy Objects

SSMS 2012 Legacy Objects

Looks like DTS and SQL Mail are finally gone as promised! 

Until next time, Happy Exploring!

PASS Summit 2011 – What I Learned

In prior posts, I reflected on the things that I think make the PASS Summit such a great value – content, volunteers, Microsoft support, and networking opportunities.  This post will concentrate on the content provided at this year’s Summit and basically what I learned over those 3 days. 

In no particular order – here’s the brain dump (from my notes, of course, with occasional commentary thrown in!):

• SQL Server Code Name “Denali” will officially be SQL Server 2012 and released in the first half of 2012.
• Project Crescent is now Power View.
• Microsoft is now promoting both on-premise databases and cloud-based databases as co-existing for the foreseeable future; but the cloud is getting bigger for SQL Azure (150 GB databases supported by year-end).
• Big Data has arrived and Microsoft has taken notice – and action – by releasing the Microsoft SQL Server Connector for Apache Hadoop … with more coming in this arena.
• I run the Houston Area SQL Server User Group so these next items are of importance to me personally:
  • PASS may finally establish a speaker bureau to help local chapters find speakers
  • PASS will be increasing services to chapter leaders, providing DNN (DotNetNuke) training, etc.
• At (Principal Architect Escalation Engineer in Microsoft CSS) Bob Ward’s “Inside Tempdb” half-day session:
  • The model database in Denali SQL Server 2012 will be 4MB up from the 2MB it has been forever. Why is this important? Well, recovery isn’t complete until tempdb is started and the default size for tempdb (if you don’t change it) is the size of model. 4MB isn’t much these days, but it is a change you should be aware of.
  • tempdb is the “garbage dump of the engine”. It is used for user objects such as temp tables, table variables, temp procs, user-defined objects and online index space. It is also used by internal objects – sorts, work tables, work files (used for hash joins) and version store. 
  • Consider moving tempdb to its own storage volume if it has heavy I/O. SSD is an option, but as expensive as that is ensure you can’t make better use of it somewhere else first.
  • Bob’s rule of thumb for how many data files to allocate for tempdb:
    • Less than 8 CPUs (cores), 1 file per CPU
    • 8 or more CPUs, start with 8 files and increase by sets of 4 until contention resides
    • Also see Paul Randal’s blog for a slight variation on this theme.
• At the “What’s New in Manageability in Denali” Microsoft session:
  • Since SSMS is now built on Visual Studio 2010, you will get multi-monitor support! Hooray!
  • There is a new Database Recovery Advisor which will be able to build a restore plan for you based on available backup files (database, log, etc.) in a folder even if you don’t have any msdb backup history. It can also handle split backups.  I’d cheer for this too, but seriously – this should have been in the product years ago!
  • Log viewer will now work with offline instances and has improved performance.
  • SCOM Management Pack (will be released at the same time as SQL Server 2012)
    • Ability to discover Availability Groups
    • Detailed knowledge of AlwaysOn tasks
    • Performance counter collections for AlwaysOn
    • Policy-Based Management integration, yes, integration – it’ll pull your PBM policies right into SCOM and alert on failures
    • Enhanced mirroring support – discovers and diagrams
    • 20 new rules for replication
    • Support for mount points (another “hooray/finally!”)
• At the “AlwaysOn: Active Secondaries” Microsoft Session:
  • You can offload backups to any replica so as to not impact production. The LSN is communicated back to the primary which then notifies all replicas so that all log backups in all replicas form a single log chain.
  • If that sounds like a nightmare for a restore, remember the Database Recovery Advisor mentioned previously and restore is simple!
  • Differential backups are not supported on secondaries.
  • Only “Copy Only” full backup is supported on secondaries.
  • Advisable to store backups centrally (so you can just point the Database Recovery Advisor to a single location to create the restore plan).
  • Declarative policy to determine where backups occur automatically. This is advisory only, not enforced. Implement via a system function which returns a Boolean indicating if this is the preferred backup location.
  • And, of course, probably the number one reason for multiple secondaries (besides general DBA paranoia) is to offload reporting workloads without having to use database snapshots.
• In MVP Tim Ford’s “Periodic Table of DMOs” session:
  • Using the format of the Periodic Table of the Elements from general science, Tim gives us a creative way to organize all the DMOs (Dynamic Management Objects – views and functions) now available to DBAs for troubleshooting and general understanding of what is happening within a database engine instance. I might actually use this to remember when and what to use.
• SQL Server guru Paul Randal (blog) busted more DBA Myths in a spotlight session. As usual, not all myths were blatantly true or false, there were a few “it depends”! This session lasted until 6:30pm and I think most would have stayed until 9:30pm to listen to Paul’s in-depth, but humorous explanations about why these myths were true/false or especially “it depends”.  
• Allan Hirt, Clustering MVP, demonstrated “Denali on Windows Server Core”:
  • Currently Denali is only supported on W2K8R2 SP1, no Windows 8 support announced yet (as of October 14, 2011).
  • You’ll need to add .NET and Allan will post on his blog how to do this effectively
  • You’ll need to add PowerShell 2.0. See KB976736 and use option 2!
  • If installing a Windows Cluster, then all nodes must be Server Core or all must allow GUI.
• Microsoft Senior Program Manager for the Database Engine Security features, Il-Sung Lee, presented “What’s New in Security for SQL Server Code Name Denali”:
  • Windows groups can now have a default schema. The first group with the lowest principal id will be chosen if a user belongs to more than one group.
  • User-defined schema roles
  • All SKUs will now have the ability to specify server audits; database audits still require Enterprise Edition or higher.
  • User-defined audit events (via sp_audit_write)
  • Database Authentication (for contained databases)
  • Lots of cryptography changes – deprecating older methods, supporting newer, more secure ones
  • Il-Sung’s team blogs at: http://blogs.msdn.com/b/sqlsecurity/
• SQLCAT brought in speakers from 4 companies who are early adopters of Denali & AlwaysOn to discuss their HA/DR requirements and migration paths to AlwaysOn. I’ll definitely be revisiting their solutions in the future and suspect there will be some whitepapers posted on SQLCAT’s website soon.
• In “Where should I be encrypting my data”, MVP Denny Cherry provided a good refresher on some encryption basics:
  • Encryption will almost always increase the size of stored data
  • Encryption will decrease the usefulness of data deduplication
  • Encryption will add CPU load
  • Enabling TDE for an application database will also enable it for tempdb
  • TDE is for “data at rest” including backups
  • For “data on the wire” use IPSEC or SSL
  • If your storage uses MPIO, then “data at rest” protection at the LUN level. If you copy a file to another server, it would be readable; if you detach and attach the LUN elsewhere it would not be readable.
• I also attended one of the “Lightning Talk” sessions, where 8 speakers had 5 minutes each to convey their topic to the audience. Some were humorous, some were serious, and one was seriously humorous! Grant Fritchey’s “Backup Testing, The Rant” is not something any of us present will ever forget – nor will we ever forget the importance of actually testing your backup! I really wish that had been caught on video, because the audio on the DVD just won’t do the presentation justice. Two other speakers in the session covered topics near and dear to me – “Thinking of Hosting a SQLSaturday?” (John Sterett) and “Build Up” (Niko Neugebauer). Both encouraged attendees to get involved in their local SQL Server community activities – and if you don’t have a local group – start one!

Now to start making the list of all the interesting sessions I couldn’t get to that I’ll want to watch (or re-watch) as soon as the Conference DVDs arrive! I foresee team “lunch ‘n learns” being scheduled.

My Denali First Glance – AlwaysOn Availability Groups

I’m finally finding some time to get more familiar with the new feature in Microsoft SQL Server Code-Named “Denali” called “AlwaysOn”. No, that is not a typo – capitalize “Always”, capitalize “On” and concatenate – “AlwaysOn”!

There are two flavors of AlwaysOn – failover clustering and availability groups.  AlwaysOn Failover Clustering is exactly what it sounds like – enabling a SQL Server Denali Instance to failover to another node in a Windows Server Failover Cluster. This is also known as a Failover Cluster Instance (FCI). On the other hand, AlwaysOn Availability Groups can best be described as database mirroring on steroids – and that is what I’ll cover in this post. I’ll cover the differences in Denali’s FCI implementation from prior versions in a later post.

First, think of all the things you don’t like about database mirroring:

•         Only 1 mirror
•         The mirror is always “in recovery” and unusable outside of a snapshot
•         Failover is per user database (i.e. one at a time)
•         Have to choose between synchronous (high availability) and asynchronous (disaster recovery)
•         Requires a 3^rd instance as a witness if you want “high safety mode” and automatic failover

Here’s what AlwaysOn Availability Groups will provide:

•         Up to 4 replicas (the term “replica” essentially replaces “mirror”) in CTP3
•         Read-only access is provided to replicas
•         Multiple databases can be grouped together for failover defined as “Availability Groups”
•         Synchronous and Asynchronous replicas at the same time – covering both high availability and disaster recovery (hence HADR)
•         Windows Server Failover Clustering detects when failover is required – no third (or fourth or fifth) server required

Yes, you read that right; the Windows Failover Clustering feature is required to be installed on your Denali servers in order to use AlwaysOn Availability Groups. It does not mean, however, that you have to actually implement a Windows failover cluster of two or more nodes. But, it does mean that Windows Server 2008 Enterprise Edition is required….and that makes me believe that this feature will likely only be available in the Denali Enterprise Edition or higher once the final SKU feature sets are announced by Microsoft. However, SQL Server Enterprise Edition is required today to use asynchronous mode for database mirroring, so this shouldn’t come as a surprise.

Similar to database mirroring failover options, the availability failover modes are automatic or planned manual failover for synchronous replicas to prevent data loss or forced manual failover for asynchronous replicas which may incur data loss.  If the secondary replica in synchronous mode is not synchronized with the primary replica, then a forced manual failover is also the only option.

For seamless client connectivity, when you establish an Availability Group you may also create a Virtual Network Name (VNN) which will be assigned a Virtual IP (VIP). Whenever the Availability Group fails over, the VNN will redirect connections to the new primary replica. Alternatively, if there are only two availability replicas in an Availability Group, then “old-style” database mirroring connection strings may be used until you can establish a VNN and update your application to use the VNN. If you want to add a third replica, then you must convert to VNN for your client connectivity.

In addition to having read-only access to secondary replicas, you can take backups of your secondary replicas.  There are several options you will want to specify when setting up your Availability Groups (or alter afterwards) in order to properly control the backup of your primary and secondary replicas. Since this is still the CTP as I’m writing this, I’ll just point you to this BOL link for the most current info as it may change by the time you get around to implementing this.

The main purpose of this article is to make you aware of what is coming so that you can start planning for your future HADR requirements as you make the move to Denali when it is finally released. To dig deeper, check out the current BOL entry for AlwaysOn Availability Groups.