In this blog, I will focus on the improvements that I believe will deliver the most benefits, based on experiences I have had with SQL Server in the past.
Microsoft SQL Server 2012 includes a new feature called the Contained Database. Had this feature been available in earlier versions of SQL Server, it would have saved me a considerable amount of time when I was moving databases between instances, during development and deployment.
With a Contained Database, login information is stored directly in the User Database rather than the Master Database. This means that when the database is moved to another instance, logins do not have to be deleted and then recreated. Not only that, but the task of having to update user SIDs can also be avoided.
SQL Server 2012’s Contained Database will create the temp table within the scope of the Contained Database. This feature means avoiding the time consuming work of moving a database to another instance, making the required adjustments to get the users working correctly, and having everything looking like it is ready to go, only to find that one is getting collation conflicts due to this new instance having a different collation to the database that has just been moved.
This feature doesn’t require that you set the database compatibility to the latest version because it is supported back to SQL Server 2005. Given that a clear boundary is established between the Contained Database and the SQL Server instance, it may be a useful feature for simulating a cloud environment.
While this new feature has some great benefits, including those mentioned, it still has some way to go before it is ideal. The database can only be considered Partially-Contained, as the database can still have server-level logins. The authentication of logins is more complicated, but this is a price I’m happy to pay for the benefits it delivers. Overall I believe it is a positive step in the right direction.
Default Schema for Groups
With previous versions of SQL Server, you could define a default schema for a user. Now with SQL Server 2012 default schemas can be created for Windows Groups. This helps reduce the administrative nightmare of having to manage large numbers of schemas, or even if you are not an overly big user of schemas, being faced with the headache of having to remove them.
Like most of us, I am a creature of habit, and one of these habits is explicitly stating the schema when I create tables. I also explicitly reference the schema in queries, views, functions and stored procedures. But being able to assign a default schema to a group is not as necessary as before, because the administrator does not have to set the default schema for each and every user.
User-Defined Server Roles
With SQL Server 2008 R2, and earlier versions of SQL Server, we could create database roles and assign permissions to them.
However these were restricted to fixed roles at a server level. If one of the nine fixed roles didn’t match the security requirements, then permissions had to be signed at an individual level. To keep these in sync quickly became an administrative nightmare, especially when a server-level permission had to be granted to a large number of users.
A major improvement has been made with security management in SQL Server 2012. We are now able to create user defined server roles and assign permissions to them. This can be done either through SQL Server Management Studio (SSMS), or by using TRANSACT-SQL with the GRANT SERVER ROLE, ALTER SERVER ROLE and DROP SERVER ROLE commands.
As a result of this new feature, SQL Server management has become easier and more secure. With the DBA now having the ability to create server level roles, the security requirements are specific to the security requirement.