Contained Databases

  • Andre Ranieri

    SSCrazy

    Points: 2819

    Good question. I got the mirroring one wrong. Somewhere I thought I'd learned that SQL2012 didn't support mirroring for contained databases. Good learning experience, thanks.

  • TomThomson

    SSC Guru

    Points: 104773

    Dan Graveen (7/26/2012)


    I was also stumped on the mirroring option. Did a quick lookup on msdn and found the alter database mirroring options are not available for contained databases. I guess I learned something new, but I'll have to read up on this some more. "How is it available, but it cannot be altered?" is the first question I need to answer for myself.. 😛

    http://msdn.microsoft.com/en-us/library/bb522476.aspx%5B/quote%5D

    I think you'll find that because handling mirroring through alter database for database mirroring was deprecated (in favor of using "always on high availability groups") in SQL 2012 they decide not to include a deprecated at first site way of doing it for contained databases (which were new in SQL2012) but tell people to use the replacement method from day 1. Seems quite sensible to me.

    Tom

  • Cliff Jones

    SSChampion

    Points: 10517

    I agree, great question and the selection of answers made it easy to go astray.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks Wayne - I had to check BOL to be certain.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Revenant

    SSC-Forever

    Points: 42467

    Took me 13 minutes to answer this one, but it was fun. Thanks!

  • Rob Schripsema

    SSCertifiable

    Points: 7469

    L' Eomot Inversé (7/25/2012)


    I could remember 3 of the four answers, but on the mirroring one I was racking my brains to no avail, and finally gave up and consulted BoL. The information seemed quite hard to find (for example, if I'd taken the first apparently relevant page I found in BoL, ALTER DATABASE Database Mirroring, at face value I would have got it horribly wrong. I ended up reading quite a bit. SO for me the question was a good learning experience.

    I must be overlooking something ... I don't see any mention/discussion of mirroring in any of the referenced articles, or anywhere else for that matter. Where in BOL did you find the 'right' answer to this? (I got the mirroring one wrong, too!)

    Rob Schripsema
    Propack, Inc.

  • Lynn Pettis

    SSC Guru

    Points: 442337

    I guessed wrong on Replication.

  • TomThomson

    SSC Guru

    Points: 104773

    Rob Schripsema (7/26/2012)


    L' Eomot Inversé (7/25/2012)


    I could remember 3 of the four answers, but on the mirroring one I was racking my brains to no avail, and finally gave up and consulted BoL. The information seemed quite hard to find (for example, if I'd taken the first apparently relevant page I found in BoL, ALTER DATABASE Database Mirroring, at face value I would have got it horribly wrong. I ended up reading quite a bit. SO for me the question was a good learning experience.

    I must be overlooking something ... I don't see any mention/discussion of mirroring in any of the referenced articles, or anywhere else for that matter. Where in BOL did you find the 'right' answer to this? (I got the mirroring one wrong, too!)

    The first thing to note is that in SQL 2012 "mirroring" has been subsumed under a much improved feature called "Always On High Availability Groups". Then look at the BoL page Always On Availability Groups (SQL Server)[/sql] and find the section headed Interoperability and Coexistence with Other Database Engine Features and you'll find "Contained Databases" listed.

    Tom

  • D.Oc

    SSCrazy

    Points: 2855

    Rob Schripsema (7/26/2012)


    L' Eomot Inversé (7/25/2012)


    I could remember 3 of the four answers, but on the mirroring one I was racking my brains to no avail, and finally gave up and consulted BoL. The information seemed quite hard to find (for example, if I'd taken the first apparently relevant page I found in BoL, ALTER DATABASE Database Mirroring, at face value I would have got it horribly wrong. I ended up reading quite a bit. SO for me the question was a good learning experience.

    I must be overlooking something ... I don't see any mention/discussion of mirroring in any of the referenced articles, or anywhere else for that matter. Where in BOL did you find the 'right' answer to this? (I got the mirroring one wrong, too!)

    I guessed it based on this

    "Benefit of Contained Database Users with AlwaysOn

    Creating contained users enables the user to connect directly to the contained database. This is a very significant feature in high availability and disaster recovery scenarios such as in an AlwaysOn solution. If the users are contained users, in case of failover, people would be able to connect to the secondary without creating logins on the instance hosting the secondary. This provides an immediate benefit. For more information, see Overview of AlwaysOn Availability Groups (SQL Server) and Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)."

    in Contained Database article http://msdn.microsoft.com/en-us/library/ff929071.aspx

    -------------------------------------------------------------
    "It takes 15 minutes to learn the game and a lifetime to master"
    "Share your knowledge. It's a way to achieve immortality."

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • WayneS

    SSC Guru

    Points: 95384

    Rob Schripsema (7/26/2012)


    I must be overlooking something ... I don't see any mention/discussion of mirroring in any of the referenced articles, or anywhere else for that matter. Where in BOL did you find the 'right' answer to this? (I got the mirroring one wrong, too!)

    I know when I wrote this question that it was mentioned in one of those references that I listed... but I can't find it in either.

    However, in Prepare a Mirror Database for Mirroring, in the Limitations and Restrictions section, it mentions that the only databases you cannot mirror are master, model, msdb, tempdb and databases that are in an Always On Availability Group. Implying that all other databases can be mirrored.

    I also notice a small error in my explanation. For the first reference, it should be "Contained Databases" and not "Create Table". (The link correctly goes to Contained Databases.)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • C-Note

    SSC Veteran

    Points: 232

    Dan Graveen (7/26/2012)


    "How is it available, but it cannot be altered?"

    This was exactly the question I was stumped on and exactly the logic I followed in assuming that contained dbs wouldn't support mirroring.

    Very challenging, thanks!

  • TomThomson

    SSC Guru

    Points: 104773

    C-Note (7/27/2012)


    Dan Graveen (7/26/2012)


    "How is it available, but it cannot be altered?"

    This was exactly the question I was stumped on and exactly the logic I followed in assuming that contained dbs wouldn't support mirroring.

    Very challenging, thanks!

    What makes you think it can't be altered?

    The fact that a particular deprecated feature can't be use to alter this new feature? Or something else?

    Tom

  • C-Note

    SSC Veteran

    Points: 232

    L' Eomot Inversé (7/27/2012)


    C-Note (7/27/2012)


    Dan Graveen (7/26/2012)


    "How is it available, but it cannot be altered?"

    This was exactly the question I was stumped on and exactly the logic I followed in assuming that contained dbs wouldn't support mirroring.

    Very challenging, thanks!

    What makes you think it can't be altered?

    The fact that a particular deprecated feature can't be use to alter this new feature? Or something else?

    The following quote from this MDSN article:

    "ALTER DATABASE database mirroring options are not available for a contained database."

  • TomThomson

    SSC Guru

    Points: 104773

    C-Note (7/27/2012)


    L' Eomot Inversé (7/27/2012)


    C-Note (7/27/2012)


    Dan Graveen (7/26/2012)


    "How is it available, but it cannot be altered?"

    This was exactly the question I was stumped on and exactly the logic I followed in assuming that contained dbs wouldn't support mirroring.

    Very challenging, thanks!

    What makes you think it can't be altered?

    The fact that a particular deprecated feature can't be use to alter this new feature? Or something else?

    The following quote from this MDSN article:

    "ALTER DATABASE database mirroring options are not available for a contained database."

    That's a long-winded way to say "Yes, the fact that a deprecated feature can't be used to change a new feature", isn't it?

    Right at the front of that specific article, before anything else, you have this very clear statement that ALTER DATABAE MIRRORING is deprecated:

    BoL


    Note

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On Availability Groups instead.

    It even tells you where to look for the new way of doing things.

    Tom

Viewing 15 posts - 16 through 30 (of 37 total)

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