New features in SQL 2012, the contained database

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4742

    Comments posted to this topic are about the item New features in SQL 2012, the contained database

  • Divine Flame

    SSCoach

    Points: 15941

    Nice article Daniel. It is always good to know about new features. It would have been nice to put some more details in the topic like how contained databases can resolve the collation conflicts when creating/using temporary tables.


    Sujeet Singh

  • Peter Pirker

    Ten Centuries

    Points: 1079

    I suppose what you meant to say was the main idea is to remove the dependency?

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    Why do we want a database that can be accessed without a login?

    Between functional/role-based logins for applications and trusted machines, I can't think of a reason for this other than to accommodate lazy developers. I assume the DBA should be guarding against lazy developers.

    I hope I've misunderstood what you've shown here. Can you provide another few examples of why we should like this feature and how it would be used appropriately?

    btw, I'm not disparaging lazy developers: I can relate, which is why I know I should do better while wearing the DBA hat.

  • mtassin

    SSC-Insane

    Points: 23096

    Mike,

    The logins are technically within the database at this point. It makes the database portable from instance to instance without having to clone the users from the original database including SIDs and hashed passwords like Microsoft sp_help_revlogin or SSIS Transfer logins tasks do.

    With 2008 R2 and below, when a database is restored to an instance, unless the logins were created by using one of the other methods listed in this article, even if they have the same password the SIDs don't match and the login has no rights to the database. Normally this is fixed with sp_change_users_login after the fact.

    This only mattered for SQL Authentication, AD carries the SIDs for Windows Authentication.

    Now the user SQL login accounts port around with the databases when you restore them.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • JJ B

    SSCarpal Tunnel

    Points: 4571

    Daniel: Thank you for this article. I particularly appreciate that you took the time to explain the methods used prior to 2012 for transferring logins. For those of us who are not likely to actually see 2012 for many a year, that was a nice inclusion.

    I wanted to read your article even though I do not have access to 2012 and likely will not for some time, because I am very interested in learning about the new features. While you did a great job of explaining what contained databases are as a concept, I would have wanted to learn more about the options. For example, why "= partial"? Only partial? What are we missing?

    Also, is this feature only available with t-sql or is there a user-interface/check box option somewhere? If available in the SSMS GUI, I would have liked to have seen a picture in the article of how it looks. I do a lot of my work in code, but for the initial creating of a database, I usually just do that using the GUI. Even if I didn't create databases that way, it seems like knowing whether a database is "contained" or not would be something someone would want to be able to quickly look up - (actually, both in the GUI and by code).

    Just some feedback. I was pleased with the article and appreciate you writing it.

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4742

    Yes, all this features can be done using GUI.

    I added some links for more details

    Use the references for more information.

  • JJ B

    SSCarpal Tunnel

    Points: 4571

    Daniel: Thanks for the reply and additional work! Very nice.

  • David in .AU

    SSCommitted

    Points: 1784

    Sounds very interesting for a self contained database, I have many business systems where I would find this valuable for DR.

    Just wondering how this works if I need to join tables between different databases, or is that one of those cases where it is listed as unsupported?

    thanks!

  • Divine Flame

    SSCoach

    Points: 15941

    David in .AU (5/22/2012)


    Just wondering how this works if I need to join tables between different databases, or is that one of those cases where it is listed as unsupported?

    thanks!

    yes, it is unsupported till now. If you are joining the tables from other databases then you are crossing the boundary of the database.


    Sujeet Singh

  • mtassin

    SSC-Insane

    Points: 23096

    Divine Flame (5/23/2012)


    David in .AU (5/22/2012)


    Just wondering how this works if I need to join tables between different databases, or is that one of those cases where it is listed as unsupported?

    thanks!

    yes, it is unsupported till now. If you are joining the tables from other databases then you are crossing the boundary of the database.

    Happens all the time though.... our development team prior to me created a separate database for every project and joined across databases all the time.

    The real question is when you are authenticating to a single database via contained databases and need to join to table in another database, how do you do that? It's a valid question...



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    mtassin (5/22/2012)


    The logins are technically within the database at this point. It makes the database portable from instance to instance without having to clone the users from the original database including SIDs and hashed passwords like Microsoft sp_help_revlogin or SSIS Transfer logins tasks do.

    With 2008 R2 and below, when a database is restored to an instance, unless the logins were created by using one of the other methods listed in this article, even if they have the same password the SIDs don't match and the login has no rights to the database. Normally this is fixed with sp_change_users_login after the fact.

    This only mattered for SQL Authentication, AD carries the SIDs for Windows Authentication.

    Now the user SQL login accounts port around with the databases when you restore them.

    I'd be tempted to make this the default for database creation on a webserver where each hosted domain has an associated database. What are the downsides to using a self-contained database, either in this usage example or in more general practice ?

  • Charles Kincaid

    SSChampion

    Points: 13593

    So lets say that my application needs to connect. How do I construct the connection string?

    ATBCharles Kincaid

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Nice article. Thanks for sharing

    We have created SSIS package to transfer logins from one server to other when we move the database. We have to do coding as well to retain the same password because transfer login doesn't retain the same password.

    Thanks

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4742

    Thanks.

    If you like the post, a rating to the article is apreciated.

    🙂

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply