Contained Databases

Question

Contained Databases

Viewing 7 posts - 31 through 36 (of 36 total)

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

C-Note SSC Veteran Points: 232 More actions · Answer 1

L' Eomot Inversé (7/27/2012)
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.

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.

davoscollective SSCertifiable Points: 6355 More actions · Answer 2

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

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 Partially 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".

TomThomson SSC Guru Points: 104773 More actions · Answer 3

davoscollective (8/1/2012)
Are relevant temp tables contained within the contained database as well, rather than tempdb?

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.

"Temporary tables created when a contained database is active will be created using the collation of the contained database instead of the tempdb database."

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

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.

On the replication item, I clicked no because the BOL entry says Partially 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".

I think that all contained databases are partially contained. At least in SQL 2012.

Tom

davoscollective SSCertifiable Points: 6355 More actions · Answer 4

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 not 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.

TomThomson SSC Guru Points: 104773 More actions · Answer 5

davoscollective (8/2/2012)
Thanks for taking the time to explain it to me Tom, much appreciated.

Glad to be of help.

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 not use the contained database's collation, which is opposite to the correct answer in the QotD.

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 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);

Tom