New Features of SQL Server Denali

We have just been greeted by another new version of SQL Server in the form of SQL Server 2012/Denali. This release comes with a number of new features. In this article I talk about a few of them. AlwaysON is definitely going to be one of the most exciting new things that Microsoft has added to SQL Server and seems set to change the high availability methods used by most of the organizations. Contained databases will solve a lot of problems for DBAs who have to regularly move databases from one instance to another. And finally I talk about a new function which is added to the release and that is going to help developers a lot in future.

AlwaysON/HADRON

While database mirroring is one of the many distinguished members of the SQL Server high availability features for a while now, we will be witnessing another toddler crawling his way in this club with the release of SQL Server 2012 aka Denali. And if the forecasts are to be believed, this little guy is going to rock big time.

But why did I mention mirroring while I am supposedly bragging about AlwaysON? Because, AlwaysON can be called Mirroring 2.0 for more than one reasons. A lot of things have been upgraded to create this little toddler. So let’s talk about what all things have been changed moving from mirroring to HADRON. (As a matter of fact, MS says mirroring will be removed after two releases.)

With mirroring we were not able to use the data on the mirror database. Though sometimes SQL Server used the mirror to accomplish page repairs, no one else could have done anything useful with the mirror copy of data. What we could have done was to take a snapshot of the database and query the snapshot. But this approach would hardly help us if we were looking for taking the load off the production server.

Database mirroring didn’t allow us to use it for disaster recovery in different data centers and for high availability within the same data center. We could have used any one of them but not both. So mirroring was essentially HA or DR but not HADR (now you see where the name comes from).

Moving on, database failovers can’t be coordinated by a DBA as they are database-level events. If we have applications which depend on many databases, we could not use automatic failovers.

With AlwaysON, a DBA can create availability groups, crudely a set of databases that can be failed over simultaneously to support any application which needs them. So, now you need not manually failover each database that you need for your application. With AlwaysON you can have up to four replicas (known as mirrored servers in pre-HADRON era). These replicas can be connected synchronously (HA) as well as asynchronously (DR). So you can have increased availability and uptime. Moreover, these replicas are not passive. These are active and can provide read-only access. So you can easily take the load off your production server if you only want read access (a very popular such scenario is taking reporting services off your production server and putting it to one of the secondary server).  Also, as the replicas are active, you can take full and differential backups on the secondary.

The other side of HADRON

The biggest downside with AlwaysON is the much more difficult setup process than mirroring. While mirroring won many hearts with its extremely easy setup process, AlwaysON relies on clustering that is infamously known for the hardships it presents while setup. But as they say all good things come with a price AlwaysON will be there with Windows clustering. Probably a dose of PowerShell might help the DBAs here. People just can’t get scared and leave such a good gift from the guys at Redmond !!!

Contained Databases

Any DBA must be aware of the issues when a database is backed up from one instance and restored to another instance. In other words, some people say that SQL Server databases are not very ‘portable’. That’s true because when you restore or attach any SQL Server database that is part of some application you miss a lot of things that are part of the application and must be ported along with the database. Some people might argue that those objects are not part of the database and are ‘outside’ objects but definitely they need to be shipped along with the database else the application will no longer be usable. These ‘outside’ objects include CLR objects, roles, linked servers, database mail, Service Broker objects, replication, SQL Server agent jobs etc. Also, if the target instance has a different collation, you will not be able to create temp tables on the new database and you might not be able to connect to different databases.

With Denali, Microsoft has presented a solution to these problems in the form of contained databases. A contained database can be seen as a black box which has all that it needs to use the database on any SQL Server instance. A contained database includes all the required settings, metadata and operates in isolation from the SQL Server Database Engine. In other words it has no functional dependency on SQL Server Instance be it Login, collation settings or metadata info. So it can be moved to any instance without affecting the applications that are using the database.

In Denali, the first iteration of the contained databases (which will be called as Partially Contained Database) feature will allow you to create a database-specific user without a login (and you can create multiple such users with the same name for different databases). Also, you can use a DMV to show most objects or code you have that will threaten containment.

I will not give the implementation details here but I will nevertheless talk about few things,

Fully contained vs. Partially contained database

A fully contained database does not allow any objects or functions that cross the application boundary whereas a partially contained database is a contained database that allows features that cross the application boundary. Inside of the application boundary is the application model, where the applications are developed and managed. Examples of entities located inside of the application model include, system tables like sys.tables, contained database users with passwords, and user tables in the current database referenced by a two-part name. Outside of the application boundary is the management model, which pertains to instance-level functions and management. Examples of entities located outside of the application boundary include, system tables like sys.endpoints, users mapped to logins, and user tables in another database referenced by a three-part-name.

Users without login                                                   

You can now create a user not tied to any login.

USE [ContainedDB];GO
CREATE USER [NewUser] WITH PASSWORD = 'DB1';
GO

USE [ContainedDB2];GO
CREATE USER [NewUser] WITH PASSWORD = 'DB2';
GO

The question arises, if there are two users with the same name, how does SQL Server figure out which one you mean?  Users are now authenticated at the database level first (database is specified in the connection string), and SQL Server will try to find a login with the same name if no user is found at the database level. So, if you specify a valid contained database user name but no database context, the credentials will fail even if the password is correct.

While contained users sound great if you want to move databases from one instance to another, this is even more fun with AlwayON. Consider a scenario where your application failed over to the other server in your cluster. If you are using contained databases for your application, you have nothing to worry as the users will be automatically connecting to the new server with your database already having the users.

Threats to containment

Even if you are going to implement database containment, there may be parts of your application which can’t be contained and which can make the containment fail. Denali has come up with a new DMVsys.dm_db_uncontained_entities to trace out such objects. Not only this DMV will point out the objects, but for procedure / functions / views / triggers, it will also highlight the lines in those objects which are creating the problems. For instance, if a procedure is using dynamic SQL five times, this DMV will list the procedure five times, each time with the dynamic SQL that was creating the problem. Also, if you have referenced any object that does not exists yet, it will also be picked up by the DMV as the object might break the containment after it is created.

The Format() function

This string function is introduced in Denali to support formatting of data. In previous versions of SQL Server we used convert function for the same but come March we will be having a deserving successor to it. The function will have three parameters (though the third one is optional), first parameter is the data value you want to format and the second one specifies the format. But the most interesting of them is the third parameter. You can specify the ‘culture’ for the format you want your data to be in using the third parameter. You can specify the country or language for your format using the third parameter.  Let’s have some examples,

Syntax : FORMAT ( value, format [, culture ] )

Date in local language,

SELECT FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'hi') AS DateinHindi;

SELECT FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'fr') AS DateinFrench;

SELECT FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'en') AS DateinEnglish;

Fig 1. Date in local language

Date in location aware format

DECLARE @date DATETIME = '01/26/2012';SELECT FORMAT ( @date, 'd', 'en-US' ) AS US_Date;SELECT FORMAT ( @date, 'd', 'fr-FR' ) AS FR_Date;

Fig 2. Date in location aware format

Currency in local format

DECLARE @money INT = 100;

SELECT FORMAT ( @money, 'c', 'en-In' ) AS India_Currency;

SELECT FORMAT ( @money, 'c', 'en-US' ) AS US_Currency;

SELECT FORMAT ( @money, 'c', 'fr-FR' ) AS France_Currency;

Fig 3. Currency in local format

Different currency formats

DECLARE @money INT = 100

SELECT FORMAT(@money,'c') AS Currency_Default;

SELECT FORMAT(@money,'c1') AS Currency1;

SELECT FORMAT(@money,'c2') AS Currency2;

SELECT FORMAT(@money,'c3') AS Currency3;

Fig  4. Different currency formats

Different calculation formats

DECLARE @cal INT = 50

SELECT FORMAT(@cal,'x') AS Hexadecimal;

SELECT FORMAT(@cal,'x3') AS HexadecimalAgain;

SELECT FORMAT(@cal,'p') AS Percentage;

SELECT FORMAT(@cal,'e') AS Scientific;

Fig 5. Different calculation formats