New Features in Microsoft SQL Server 2012

(Welcome to The Next Byte, a series on Microsoft SharePoint and SQL Server written by Don Conrad, a.k.a. “Don SQL”, the Fpweb.net SharePoint database wizard and expert in all things SQL-related. In this post Don examines new features in SQL Server 2012. Missed an installment of The Next Byte? Check out the complete Microsoft SharePoint and SQL Server series.)

SQL Server 2012 logo image

and now… THE NEXT BYTE

SQL Server 2012 Topics to Discuss:

  1. Which new features will have the biggest impact for SharePoint?
  2. Why is the filetable so important?
  3. What are the pricing considerations?

Microsoft recently released the newest version of its premier enterprise database management system, SQL Server 2012. This update contains many new and significant features from the current release, 2008 R2. Many of the features are notable improvements over 2008 R2 and there are so many that we couldn’t possibly do justice to them in one article.

In fact, we won’t even try. Instead we’ll focus some of the feature updates that will have the most impact on Microsoft SharePoint, since, after all, SharePoint hosting is what we do here at Fpweb.net.

SQL Server 2012 key features (in no particular order):

    1. AlwaysOn
    2. Contained databases
    3. The FileTable feature (*not a datatype)
    4. Core-based licensing

1. AlwaysOn is perhaps the most important new feature in SQL Server 2012. It advances availability beyond mirroring. The AlwaysOn feature groups designated databases into a primary availability group. Each primary availability group is then hosted on up to four secondary availability replicas. Each secondary replica group is hosted on a different node of a Windows Server Failover Cluster (WSFG). Each node is hosted on a different server. The primary replica sends transaction log records to each of the secondary replicas at the database level. This update can be either synchronous or asynchronous. Each secondary replica updates its databases independently of the other secondary replicas. Each of the replica groups is evaluated as to its health independently of the other groups.  And each of the secondary replicas can be configured to support read-only access.

So in an AlwaysOn setup, any given production database can be duplicated (in real-time) in up to four other databases, each of which can be read-only. WSFG monitors the health of each of the resource groups to determine which are healthy and which may be compromised. The WSFG then determines which secondary replica group becomes the primary – in case an unplanned failover event takes place.

In a nutshell, AlwaysOn can provide significant protection against data loss by maintaining up to four copies of the production database. Each of the four copies can be available for reporting purposes. Obviously, given the broad set of options there are a significant number of different configurations for synch/asynch, read-only, and no-access availability (in this case, “no-access availability” is NOT an oxymoron).

2. A Contained Database is a database that is isolated from other databases on the server. In effect, a Contained Database is also independent from the database server for the purposes of metadata and authentication. This means the database can be backed up and restored to another database server without having to change owners, schemas or other typically server-level functions. These credentials are contained within the database itself, providing for much faster and simpler database restores when appropriate. And a database can either be entirely or partially contained. Again, a whole spectrum of configuration options are available.

Imagine being able to restore a backup to another server and having it immediately operational. No “sp_change_users_login”. No ensuring all domain-authenticated logins are valid on the destination. No ensuring all user-defined functions and datatypes are in place and current. Just restore and and you’re ready to login!

3. The FileTable feature. Some database admins might (mistakenly) refer to this a data type. It is a data field which can store, process and retrieve O/S level directories, sub directories and files in each. The access to the files and directories is controlled by the SQL Server via its standard authentication. If a user or application has SQL Server access permissions, then the data can be access and updated directly at the operating system level, bypassing SQL Server once the users are authenticated. The files can be remote blob storage—RBS. As RBS is possibly the faster document access method available in SQL Server, this has tremendous promise. Previously users have been reluctant to use RBS, especially in a shared environment. That’s because RBS then required o/s access permission, possibly even local admin rights to be used.

With FileTable this has been solved because SQL Server controls the access.  If a would-be user has no permissions to the data, then they cannot even see the data. Using this new FileTable feature is going to require extensive testing but could prove crucial to SharePoint systems and Web Apps.

4. Core-level pricing. SQL S2008 R2 Enterprise was licensed according to the number CPU processors in the server farm. However, Microsoft has amended its licensing terms for the SQL Server 2012 version. Instead of CPUs, licensing for SQL Server 2012 is based on cores.  In the modern age of parallel computing, most CPUs nowadays have at least two, if not four or more, cores. As a result, this seemingly minor change in the licensing terms has the potential to double the cost of SQL Server 2012 Enterprise Edition. But with the addition of significant new features, upgrading is probably well worth the investment for most enterprise IT departments.

SQL Server 2012 is a new product, and we haven’t had the opportunity to perform thorough tests on the relationship between this new version and SharePoint 2010. But in the coming weeks and months, Fpweb.net SharePoint Experts will be reviewing these new features in-depth. So don’t worry. We’ll continue to keep you updated on the progress of SQL Server 2012 and new insights about working with SharePoint and SQL Server.

As always, feel free to post any questions or comments you may have.

Until the next byte,
Don

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)

About Fpweb.net Crew

Our business is centered on bringing enterprise-class strategy, support, and security to your hosted or managed platforms no matter where you choose to deploy your environment. We specialize in providing managed services, cyber security, and expert, USA-based, 24/7 Absolute Support® on-premises, or in any cloud.
This entry was posted in SQL Server and tagged , , , , , , , , , , , . Bookmark the permalink.

3 Responses to New Features in Microsoft SQL Server 2012

  1. Andy Milsark says:

    Great Article! Do the contained databases only support SQL authentication or Windows as well?

  2. Pingback: Upgrading to SharePoint 2013 from Older Versions - Fpweb.net

  3. Rich says:

    Great improvements! We are going to be using Sharepoint 2010 with SQL 2012. We’d like to use database mirroring but we’d also like to use Remmote Blob Storage, which wasn’t possible with SQL 2008 R2. Will 2012 permit the use of RBS with mirrored databases?

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Let's make sure you're human first: *