Contained Databases

Question

Contained Databases

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

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

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 1

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 More actions · Answer 2

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: 10547 More actions · Answer 3

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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 More actions · Answer 5

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

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 6

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: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

July 26, 2012 at 4:08 pm

#1518219

I guessed wrong on Replication.

TomThomson SSC Guru Points: 104773 More actions · Answer 8

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: 2875 More actions · Answer 9

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: 259105 More actions · Answer 10

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: 95460 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

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 More actions · Answer 15

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