Now that CTP3 has been released to the public, I can start to blog about a huge list of content that I have been working on related to Denali. Although this is technically a post about CTP3 features, CTP3 is very close to what I expect the final product to be, with only a limited set of enhancements from here until RTM.
This post is about features. After these three posts, where I briefly discuss some of the new bells and whistles of the product, I can start to do some deep dives into functionality and configuration. I have divided the features up into the following categories:
- High Availability / Disaster Recovery
- Security
- Business Intelligence (BI)
- SSIS / Data Tools
- Programmability
- Other
I will discuss the new features in that order. I have devoted a significant amount of time to working with the SQL team on the HA/DR (AlwaysOn) features of Denali, so of course that gets put at the top of the list. 🙂
High Availability / Disaster Recovery (“AlwaysOn”)
Over the course of the last two years, we have referred to the high availability features as “HADR”, “HADRON”, and “AlwaysOn”. From here on I will simply call these features “AlwaysOn”. AlwaysOn incorporates these new features:
- Multiple secondaries (multiple copies of the data, similar to the older concept of a “mirror”).
- Readable secondaries!! We can now run queries against a secondary without jumping through hoops with snapshots and views and other tricks. Just query the database and it will work – no more work needed.
- Backup on secondary – you can now run backups on your secondary to isolate the workload from your primary system, or to store the backups in a different location, like your DR site.
- Availability Groups – With SQL Sever 2005 and 2008, we had database mirroring (DBM), which allowed you to set up a mirroring relationship at the database level. With Denali we have Availability Groups (AGs), which allow you to set up a data movement relationship with multiple databases in the availability group. This more accurate describes most applications, and allows you to fail over the AG (and therefore all of its DBs) together. You can have up to four secondaries and up to two synchronous secondaries
- Automatic page repair – this is the same functionality that we had with DBM, but it has also been included with AGs. If a data page is corrupted, and this is detected when a query runs, SQL Server will automatically get an uncorrupted copy of the data page from a secondary copy, and fix the corrupted page. It’s magic, and it truly works that easily, with no user interaction.
- Online Indexing of LOB data – to me this is one of the most exciting new features. With SQL Server 2008, we were not able to do online indexing on tables that contained LOB data. That meant that we had to be much more careful about when we used Reindex, Reorganize, or just skipped the index. For availability, we had to error on the side of not rebuilding often enough. Now, we can rebuild all indexes online, even if they contain LOB data! The only exception to this are XML indexes and deprecated datatypes: text, ntext and image. This means higher availability for everyone out-of-the-box, simply by selecting to rebuild the index online! With our SLA requirements, we look for ways to save seconds per yer. This feature saves us minutes – about half of the budgeted planned downtime, so it is truly an incredible feature.
- Add Columns with Default Value – Prior to Denali, you could add a new NULLABLE column without incurring any downtime, but could not add a column with a default value. Now, with Denali, you can add a column with a default value and it is simply a metadata operation until certain things like an index rebuild happen. This is another feature that has been requested for years and Microsoft has delivered it in Denali.
- Hyper-V and Live Migration – With Denali you can now deploy SQL Server as a virtual machine and use Hyper-V Live Migration to move the server between one host and another with zero downtime. This means that for patching the host, it is not longer necessary to incur the downtime associated with failing over a node of the cluster. If your application can run as a VM, you now have the ability to achieve higher uptime.
- Dashboards – Multiple new dashboards give you an instant view of your system and how it is functioning. This can help you quickly find and eliminate problems, or give you the assurance that everything is working correctly.
- DMVs and XEvents– there a multiple new DMVs and XEvents related to AlwaysOn that allow you to creatively manage your system and raise alerts on events that are crticial to you and your environment. This is worthy of an entire post, so I will dedicate one to this topic in the next few months.
- Server Core Support – Denali also lets you deploy SQL Server on Windows Server Core. Depending on the features you have installed, UI security updates may make up as much as 67% of the total patches that you apply. Be reducing the surface area, you can reduce the number of patches you need to apply, therefore reducing the number of reboots or failovers.
Security
There are several new security features which are compelling. I will briefly mention them here, but you should definitely look into these features in the product. I think you will find some features that can both save you headaches and enhance your auditing.
- Apply Default Schema to Group – this is a great new feature, and one of the highest requested security features in SQL Server. Database scheme can now be linked to a Windows Group instead of individual users. While this is being billed as a way to help with compliance, to me the real win is the lack of administration overhead and the ability to prevent errors by assigning the schema to the incorrect users.
- Auditing functionality now works on all SKUs – the sku-specific limits for auditing are now gone. No matter what version of SQL Server you use, you will be able to implement the auditing features.
- User-Defined Auditing and Filtering – you can now customize the audit information and remove unnecessary content from being entered in your audit logs. See sp_audit_write in Books Online for details on the new functionality.
- Stack Info – enhancements have been made to see the full stack information for 3-tier applications. Typically all you can see is the middle-tier, that usually has a limited number of connection strings. Now, with the enhanced functionality, you can trace the call all the way from the calling application.
- Contained Databases (CDBs) – If you have single-database applications, you will be excited about contained databases. CDBs encapsulate almost everything about the database into a single container. For example, all of the database logins live inside the CDB, not stored in system databases. This makes moving the database to new instances or back and forth between environments very simple.
- Crypto Enhancements – enhancements have been made for increased functionality and to support newer algorithms with higher security.
You can see by the list of just AlwaysOn and Security features in Denali, that this version of SQL Server is packed full of new stuff. As I mentioned in a post earlier this year, you need to start spending some time working with the CTPs so that you get up-to-speed quickly.
Check out Part 2 for BI and SSIS/Data features.
David P. Smith 