Voice of the DBA

Tag: sql server

  • Contained Databases – Server Setting Matters

    In doing some additional testing on contained databases, I decided to create a new database on a new test VM.

    CREATE DATABASE cdb1
 containment = PARTIAL
;

    To my surprise, I got this error:

    Msg 12824, Level 16, State 1, Line 1

    The sp_configure value ‘contained database authentication’ must be set to 1 in order to create

    a contained database.  You may need to use RECONFIGURE to set the value_in_use.

    I checked the server setting, and sure enough the instance property was set to 0 (false).

    At first you might think this shouldn’t matter, but imagine you go to attach a backup of a contained database to an instance that doesn’t have this enabled. However there are a few security and administrative concerns over contained databases. We have the password policies, the potential collision of user names, and more.

    The easy fix is to enable the instance level setting. That’s easily done with this code:

    -- Set advanced options
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'contained database authentication', 1;
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

    What about restoring a contained database backup? Surely it will just come online without the contained authentication?

    I tried it, before running the script above, and I got this error:

    containedfail

    Clearly the instance level setting matters. It’s easy to change, either in script or the GUI. However if you use the GUI, please don’t click OK and save the changes. Use the script button, save that for your logging/documentation, and then run the script.

  • AlwaysOn and Contained Databases in SQL Server 2012

    This talk is mostly based on contained databases in SQL Server 2012. The first part of the talk goes over the problems with current database movement, the idea of containment, and the implementation in SQL Server 2012. There are demos that cover the user and collation scenarios. The second part of the talk goes into the basics of AlwaysOn. I talk about the architecture, the various scenarios you can have, and the advantages and drawbacks of the technologies involved. There are no demos of AlwaysOn mostly due to time.

    Length: 60 minutes

    Slides: downloadable after the SQL in the City 2012 tour.

    Code: Demo code

  • Kill a SPID

    This editorial was originally published on Sept 5, 2007. It is being re-run as Steve is traveling.

    I know some people get a kick out of running the KILL command. Heck, I’m sure all of us enjoy stopping a runaway process from an annoying user at times. But not everyone seems to understand exactly how databases work. It’s not necessarily a knock on the original poster, after all, most of us had to learn about the ACID properties at some point. Perhaps even after someone dropped a database in our lap without warning.

    If you kill a spid, I saw some confusion about data loss in a recent thread. You won’t lose data, but you could still have a problem. It’s not a technical problem and the storage engine inside SQL Server ensures that the transaction conforms to the ACID properties to ensure data integrity.

    The users, however, might or might not realize that their “work” was not done. It depends on the error handling of your application, what message (if any) that is presented to the user, and if they’re even still around. I’ve seen people start processes they expected to run long and then leave their workstation. If there wasn’t a message on it later, they might assume the transaction had gone through.

    Even if you tell them you’re killing the process, they might still think that some amount of work is completed. There are all sorts of users, with all different kinds of expectations out there, so you should be sure that they understand exactly what is happening.

    And be sure that they know their “work” is lost. From the point of view of someone doing data entry, if the transaction rolls back and the application can’t handle it, they will have to enter information again. So their work is essentially lost.

    Those of us in technology sometimes forget the impact of our systems in the real world. Even when things work well or as designed, they may still be a problem for real people that have real tasks to get done.

  • Time Zones

    Why don’t we have a function like the one in this article to convert from time zone to time zone? Seems like something SQL Server should have added awhile ago.