Voice of the DBA

Author: way0utwest

  • Enabling Contained Databases in SQL Server 2012

    One of the new features in SQL Server 2012 is the Partially Contained Database feature. I gave a talk on this recently, and I’m looking forward to seeing where this might go in the future.

    This post looks at how you can enable containment in SQL Server 2012. It’s a very simple process, in a couple steps, and I’ll show you both with the GUI and with T-SQL.

    Enable the Instance setting

    There are two levels to enable containment. The first is at the instance level. If you get the server properties for your instance, and look at the advanced tab, you’ll see this:

    cdb1

    You can use the drop down to select the setting you want (true = enabled), but please don’t click the OK button. Always, always, always click the “Script” button. This allows you to see the exact code being run, and then you can also use this in your documentation for change control. Even if this is a development server, get in the habit of scripting things.

    script

    Once you click the script button, you’ll get this T-SQL:

    EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

    This will enable containment for the instance and you’ll be halfway there.

    Database Containment

    The database itself also has a containment setting. In this case, you can look at the properties for the database, on the options tab.

    cdb2

    If you look near the top, you have the collation drop down, the Recovery mode, the compatibility model, and then containment is new in 2012. You have “None” and “Partial” available, and clicking Partial will enable containment in  2012. Again, please don’t click OK, but click script.

    The code will appear as below:

    USE [master]
GO
ALTER DATABASE [cdb2] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

    You can also set this value when you create a database:

    -- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL

    That’s it.

    These two items together will enable containment on a database, and then you can work with contained users, something I’ll talk about in another post.

  • The On-call Demands

    I used to sleep with one of these on my chest to respond to issues.

    I have been on call in all sorts of situations, but over the years the demands lessened at a few companies. Perhaps that was due to maturity on the part of the staff, or maybe the quality of basic software has improved over the years. I know that hardware has improved and it seems that less and less alarms from failed hardware interrupted my evening hours.

    However the amount of after hours calls varied widely, often related to the size of the company. In larger companies, it seemed as though there were more calls per week, but more people to handle them. Smaller companies often had me responding to anything, but there were few calls. This Friday I’m wondering what it’s like for you.

    What is your on-call schedule and the frequency of calls after hours at your current job?

    I’m wondering this week what type of demands are placed on you to respond to issues outside of normal work hours. I assume that a major issue might result in a late night call, whether or not you are formally “on-call” or not, but what is the official on-call schedule like for you. How often are you on call, and how often do you get called. Let us know this week.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Treat the Database like Code

    Source Control can drive people crazy

    In one of Grant Fritchey’s presentations at SQL Server Connections recently, he talked about treating the databaese like code, and storing the changes you make, the DDL for the objects, and more, in a version control system of some sort. Grant had a number of points to make about why this is good, not the least of which is that it brings the DBAs closer to the developers.

    It was a great presentation and it made perfect sense to me. I’ve always tried to store my DDL code in some sort of VCS, checking it and out as needed. I found it helpful for keeping track of which version of an object was released with which deployment. This also helped me to organize the changes which I was going to release, branching them away from the full list of changes stored in source control.

    Databases don’t inherently lend themselves to source control, with the various levels of state that must be stored, the strict ordering of changes, and more, but by imposing a little discipline, you can include the code and keep track of everything important for deploying changes. It’s not easy however, especially since the process of building a current version of your database from VCS would not be the latest change to each object. Objects are built with CREATE statements, but changes over time are made with ALTER statements. A little creativity will help you here, as will any number of third party tools that can keep a current set of CREATE statements handy.

    If you’re not storing your DDL in a VCS, I’d urge you to start. You might not need the features often, but when you do, having versions available can be career-saving. There are a number of free VCS systems available as well, so there’s no monetary reason why you can’t start tracking your code tomorrow.

    Steve Jones

    SQL Source Control from Red Gate Software can make version control possible from within SSMS. Download your trial version today.

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • SQL Server 2012 Beta Exams

    I signed up for a few of the SQL Server 2012 Beta certification exams, and have been going through them over the last few days. The exams I signed up for are:

    I took the first one on Monday, and the second on Wednesday. I have the other two scheduled for Friday and next Wednesday.

    I can’t really comment on what was in the exam, but I did see some evolutions of the testing software, which was nice. I like a few of the changes, and they should help people demonstrate a few more skills than the old multiple-guess exams. There are still multiple choice questions, but some new ways to ask someone to show knowledge.

    I’m not really worried about being certified, but I do like to see how the certification process works, I might write some stuff to help people (I’ve worked on three cert books in the past) and most importantly, I get a rough idea of what I know on the exams. They’ll ask me a few things I might not have looked at in depth, and I certainly found a few holes in my knowledge over the last few exams.

    If you have the chance to take a beta, I’d encourage you. They’re free, and the time they take can be a valuable aid in helping plan your future learning.