SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Wayne Sheffield / Contained Databases / Latest Posts

RE: Contained Databases

james.grondalski — Fri, 05 Oct 2012 11:54:45 GMT

Nice

RE: Contained Databases

Neha05 — Wed, 08 Aug 2012 11:38:28 GMT

Simple and straightforward question, thanks!

RE: Contained Databases

L' Eomot Inversé — Fri, 03 Aug 2012 02:22:22 GMT

[quote][b]davoscollective (8/2/2012)[/b][hr]Thanks for taking the time to explain it to me Tom, much appreciated.[/quote]Glad to be of help.[quote]The reason why I asked if the temp tables are in the contained database (during the time in which the temp tables live - not permanently obviously) is because of the BOL info that says temp tables will use the DATABASE_DEFAULT collation. To me that means that the temp tables will [b]not [/b]use the contained database's collation, which is opposite to the correct answer in the QotD. [/quote]Ah, you have missed something there. The DATABASE_DEFAULT collation for temp tables created in the context of a (partly) contained database is the database default collation for that database, not the database default collation of tempdb.If you look at the Non-Contained Databases section of [url=http://msdn.microsoft.com/en-us/library/ff929080.aspx] you'll see an example 2 (it's a pity example numbering restarts in each new section of that page, so that several examples get the same number) where, in an uncontained database, the collation clause on creation of a temp table is used to get the same behaviour as a contained database: CREATE TABLE #T2 (T2_txt nvarchar(max) COLLATE DATABASE_DEFAULT);

RE: Contained Databases

davoscollective — Thu, 02 Aug 2012 20:33:49 GMT

Thanks for taking the time to explain it to me Tom, much appreciated.The reason why I asked if the temp tables are in the contained database (during the time in which the temp tables live - not permanently obviously) is because of the BOL info that says temp tables will use the DATABASE_DEFAULT collation. To me that means that the temp tables will [b]not [/b]use the contained database's collation, which is opposite to the correct answer in the QotD. I still find that confusing, but this next part gets to the point, which is that it doesn't matter if the collations are different. The main reason to be concerned about collation is to avoid errors in query results, especially ones you might not pick up for subtle reasons (like is a = á ?). "this collation behavior eliminates the need for an explicit COLLATE clause in most temp table uses. In a contained database, this code now runs without error, even if the database and instance collations differ"What you say about sane file sizes for contained database makes total sense.Thanks Tom.

RE: Contained Databases

L' Eomot Inversé — Thu, 02 Aug 2012 04:08:48 GMT

[quote][b]davoscollective (8/1/2012)[/b][hr]Are relevant temp tables contained within the contained database as well, rather than tempdb? [/quote]I may be wrong, but I am certain that contained databases temp tables are in tempdb. Why should they be in the contained database? At first sight hey don't exist while the database is closed (that's the defining property of a temp, as opposed to ordinary, table), and any time it's open it has access to the tempdb of the system it's open on. So whether they are intempdb or somewhere else could make no difference to moving the database from one SQL server or instance to another, and hence is irrelevant to containment.[quote]"Temporary tables created when a contained database is active will be created using the collation of the contained database instead of the tempdb database."[/quote]That of course is essential to containment - if it weren't true, moving to a different instance with a different default collation might mek the database sease to function correctly.in BOL: [url]http://msdn.microsoft.com/en-us/library/ff929080.aspx[/url]There is a table under the Contained Databases heading that says Temp Data will use DATABASE_DEFAULT for a contained database. I am probably misreading it but to me that means it won't use the CATALOG_DEFAULT of the contained database. What am I missing there?[/quote]You aren't missing anything - except perhaps the catalog collation for contained databases is fixed and the same for all contained databases. This can happen when a connection flips fromone database to another - see examples two and three at the bottom of the page to which you refer. The examples mentioned above seem to me to make it clear that the temp tables, despite the collation issue, are still in tempdb - eg in example 3 it's quite clear that the attempt, while the thread is using the contained database, to access a table whose name begins # is made in tempdb, not in the contained database, while in example 2 it's quite clear that a thread running in non-contained master database accesses a table beginning # in tempdb even though it was created while the thread was using the contained database. Of course it would be possible to achieve those effects by by an enormously complicated mechanism that didn't have contained databases temp tables in tempdb, but it really would be enormously complicated, especially when one considers that the files sizes for the contained database have to remain sane, that we generally want tempdb on a different storage unit from the contained database data and logs for performance reason, and that these temp tables in the contained database would have to be omitted from all backups of the database and not exist on instance startup.[quote]On the replication item, I clicked no because the BOL entry says [i]Partially [/i]Contained databases cannot use replication, however on closer reading it says "In SQL Server 2012 the contained database feature is currently available only in a partially contained state".[/quote]I think that all contained databases are partially contained. At least in SQL 2012.

RE: Contained Databases

davoscollective — Wed, 01 Aug 2012 23:13:50 GMT

Are relevant temp tables contained within the contained database as well, rather than tempdb?"Temporary tables created when a contained database is active will be created using the collation of the contained database instead of the tempdb database."in BOL: [url]http://msdn.microsoft.com/en-us/library/ff929080.aspx[/url]There is a table under the Contained Databases heading that says Temp Data will use DATABASE_DEFAULT for a contained database. I am probably misreading it but to me that means it won't use the CATALOG_DEFAULT of the contained database. What am I missing there?On the replication item, I clicked no because the BOL entry says [i]Partially [/i]Contained databases cannot use replication, however on closer reading it says "In SQL Server 2012 the contained database feature is currently available only in a partially contained state".

RE: Contained Databases

C-Note — Fri, 27 Jul 2012 10:55:34 GMT

[quote][b]L' Eomot Inversé (7/27/2012)[/b][hr][quote][b]C-Note (7/27/2012)[/b][hr][quote][b]L' Eomot Inversé (7/27/2012)[/b][hr][quote][b]C-Note (7/27/2012)[/b][hr][quote][b]Dan Graveen (7/26/2012)[/b][hr] "How is it available, but it cannot be altered?" [/quote]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![/quote]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?[/quote]The following quote from [url=http://msdn.microsoft.com/en-us/library/bb522476.aspx]this MDSN article[/url]:"ALTER DATABASE database mirroring options are not available for a contained database."[/quote]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:[quote][b]BoL[/b][hr]NoteThis 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.[/quote]It even tells you where to look for the new way of doing things.[/quote]I'm not disputing the fact that the feature has been deprecated, that was also the first thing I noticed when reading the article. I understand that "deprecated" != prohibited, but I incorrectly assumed (as Dan did) that since ALTER db mirroring options won't work with contained dbs, that mirroring was not supported at all.

RE: Contained Databases

L' Eomot Inversé — Fri, 27 Jul 2012 10:40:30 GMT

[quote][b]C-Note (7/27/2012)[/b][hr][quote][b]L' Eomot Inversé (7/27/2012)[/b][hr][quote][b]C-Note (7/27/2012)[/b][hr][quote][b]Dan Graveen (7/26/2012)[/b][hr] "How is it available, but it cannot be altered?" [/quote]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![/quote]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?[/quote]The following quote from [url=http://msdn.microsoft.com/en-us/library/bb522476.aspx]this MDSN article[/url]:"ALTER DATABASE database mirroring options are not available for a contained database."[/quote]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:[quote][b]BoL[/b][hr]NoteThis 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.[/quote]It even tells you where to look for the new way of doing things.

RE: Contained Databases

C-Note — Fri, 27 Jul 2012 09:49:56 GMT

[quote][b]L' Eomot Inversé (7/27/2012)[/b][hr][quote][b]C-Note (7/27/2012)[/b][hr][quote][b]Dan Graveen (7/26/2012)[/b][hr] "How is it available, but it cannot be altered?" [/quote]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![/quote]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?[/quote]The following quote from [url=http://msdn.microsoft.com/en-us/library/bb522476.aspx]this MDSN article[/url]:"ALTER DATABASE database mirroring options are not available for a contained database."

RE: Contained Databases

L' Eomot Inversé — Fri, 27 Jul 2012 09:43:29 GMT

[quote][b]C-Note (7/27/2012)[/b][hr][quote][b]Dan Graveen (7/26/2012)[/b][hr] "How is it available, but it cannot be altered?" [/quote]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![/quote]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?

RE: Contained Databases

C-Note — Fri, 27 Jul 2012 08:55:36 GMT

[quote][b]Dan Graveen (7/26/2012)[/b][hr] "How is it available, but it cannot be altered?" [/quote]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!

RE: Contained Databases

WayneS — Fri, 27 Jul 2012 07:50:32 GMT

[quote][b]Rob Schripsema (7/26/2012)[/b][hr]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!)[/quote]I [i]know[/i] 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 [url=http://msdn.microsoft.com/en-us/library/ms189053#Restrictions][u]Prepare a Mirror Database for Mirroring[/u][/url], 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.)

RE: Contained Databases

Koen Verbeeck — Fri, 27 Jul 2012 07:25:30 GMT

Nice question, thanks.

RE: Contained Databases

D.Oc — Fri, 27 Jul 2012 05:46:14 GMT

[quote][b]Rob Schripsema (7/26/2012)[/b][hr][quote][b]L' Eomot Inversé (7/25/2012)[/b][hr]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, [url=http://msdn.microsoft.com/en-us/library/bb522476.aspx][i]ALTER DATABASE Database Mirroring[/i][/url], 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.[/quote]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!)[/quote]I [b]guessed[/b] it based on this "Benefit of Contained Database Users with AlwaysOnCreating 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 [url]http://msdn.microsoft.com/en-us/library/ff929071.aspx[/url]

RE: Contained Databases

L' Eomot Inversé — Fri, 27 Jul 2012 05:44:52 GMT

[quote][b]Rob Schripsema (7/26/2012)[/b][hr][quote][b]L' Eomot Inversé (7/25/2012)[/b][hr]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, [url=http://msdn.microsoft.com/en-us/library/bb522476.aspx][i]ALTER DATABASE Database Mirroring[/i][/url], 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.[/quote]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!)[/quote]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 [url=http://msdn.microsoft.com/en-us/library/hh510230.aspx][i]Always On Availability Groups (SQL Server)[/i][/sql] and find the section headed [b]Interoperability and Coexistence with Other Database Engine Features[/b] and you'll find "Contained Databases" listed.

RE: Contained Databases

Lynn Pettis — Thu, 26 Jul 2012 16:08:46 GMT

I guessed wrong on Replication.

RE: Contained Databases

Rob Schripsema — Thu, 26 Jul 2012 12:59:25 GMT

[quote][b]L' Eomot Inversé (7/25/2012)[/b][hr]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, [url=http://msdn.microsoft.com/en-us/library/bb522476.aspx][i]ALTER DATABASE Database Mirroring[/i][/url], 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.[/quote]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!)

RE: Contained Databases

Revenant — Thu, 26 Jul 2012 12:42:59 GMT

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

RE: Contained Databases

SQLRNNR — Thu, 26 Jul 2012 12:27:36 GMT

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

RE: Contained Databases

Cliff Jones — Thu, 26 Jul 2012 12:02:42 GMT

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

RE: Contained Databases

L' Eomot Inversé — Thu, 26 Jul 2012 10:27:39 GMT

[quote][b]Dan Graveen (7/26/2012)[/b][hr]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.. :Phttp://msdn.microsoft.com/en-us/library/bb522476.aspx[/quote]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.

RE: Contained Databases

Andre Ranieri — Thu, 26 Jul 2012 09:52:00 GMT

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.

RE: Contained Databases

OzYbOi d(-_-)b — Thu, 26 Jul 2012 09:34:26 GMT

very interesting question. definitely a learning experience here for me today - cheers

RE: Contained Databases

bitbucket-25253 — Thu, 26 Jul 2012 08:39:05 GMT

[quote][b]L' Eomot Inversé (7/25/2012)[/b][hr]Nice straightforward question, thanks. 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, [url=http://msdn.microsoft.com/en-us/library/bb522476.aspx][i]ALTER DATABASE Database Mirroring[/i][/url], 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.[/quote]+1 Made the same mistake .. oh well learned something new

RE: Contained Databases

sestell1 — Thu, 26 Jul 2012 07:40:26 GMT

Great question, thanks!I was sure I had them all correct, I must've clicked on one of the wrong ones by mistake. :angry:

RE: Contained Databases

KWymore — Thu, 26 Jul 2012 07:30:14 GMT

Nice question, thanks

RE: Contained Databases

jeff.mason — Thu, 26 Jul 2012 07:24:23 GMT

[quote][b]Lokesh Vij (7/26/2012)[/b][hr][quote][b]anthony.green (7/26/2012)[/b][hr]Damn, got 3 out of the 4, I thought mirroring wouldn't of been true as it is now a depreciated feature so, wouldn't of thought the two would co-exist.[/quote]I also thought the same :-)But in the end changed my mind, thinking that what would be the use of contained DB, if it cannot be mirrored? Features like running environment reports from contained DBs and other stuff like delegating access control will be of no use without mirroring.[/quote]Really? I got it wrong because I thought the exact opposite. As I understand contained DBs, they should be portable and independent of the main server, and be as much a single unit as they can. Therefore I would have thought that mirroring them would be sort of useless. So this is a question for learning the new stuff -- when would you want to have a contained database that is mirrored? I know a bit about how contained databases technically work, but I am not so much familiar with the business case for when and why to use them.

RE: Contained Databases

Dan Graveen — Thu, 26 Jul 2012 07:23:04 GMT

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.. :Phttp://msdn.microsoft.com/en-us/library/bb522476.aspx

RE: Contained Databases

Dana Medley — Thu, 26 Jul 2012 06:54:21 GMT

Thank you for the great question. The answer on mirroring kinda threw me for a loop; I had it and then second guessed myself and got it wrong.

RE: Contained Databases

Stewart "Arturius" Campbell — Thu, 26 Jul 2012 05:40:58 GMT

RE: Contained Databases

Lokesh Vij — Thu, 26 Jul 2012 03:50:03 GMT

[quote][b]anthony.green (7/26/2012)[/b][hr]Damn, got 3 out of the 4, I thought mirroring wouldn't of been true as it is now a depreciated feature so, wouldn't of thought the two would co-exist.[/quote]I also thought the same :-)But in the end changed my mind, thinking that what would be the use of contained DB, if it cannot be mirrored? Features like running environment reports from contained DBs and other stuff like delegating access control will be of no use without mirroring.

RE: Contained Databases

rodjkidd — Thu, 26 Jul 2012 02:59:41 GMT

Good question. I was in the same boat as Tom, was it replication or mirroring...But I didn't check BOL ( :-D ) took a punt on replication, oh well!Rodders...

RE: Contained Databases

Sreepathi1987 — Thu, 26 Jul 2012 02:55:11 GMT

Good Question.....

RE: Contained Databases

anthony.green — Thu, 26 Jul 2012 02:53:11 GMT

Damn, got 3 out of the 4, I thought mirroring wouldn't of been true as it is now a depreciated feature so, wouldn't of thought the two would co-exist.

RE: Contained Databases

kapfundestanley — Thu, 26 Jul 2012 02:28:42 GMT

Nice simple question on SQL Server 2012 new features thank you.Got my point surprised only 27% got it so far.

RE: Contained Databases

L' Eomot Inversé — Wed, 25 Jul 2012 21:37:03 GMT

Nice straightforward question, thanks. 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, [url=http://msdn.microsoft.com/en-us/library/bb522476.aspx][i]ALTER DATABASE Database Mirroring[/i][/url], 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.

Contained Databases

WayneS — Wed, 25 Jul 2012 20:45:17 GMT

Comments posted to this topic are about the item [B]Contained Databases[/B]