SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Contained database


Contained database

Author
Message
c4ntaloop
c4ntaloop
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 81
Hi,

Can someone please explain what is the downside of switching a database into contained database?

I know the benefit of contained database is easy for migrating between server/instance, it makes the database very portable.

Any help will be much appreciated.

Thanks
psingla
psingla
SSC Eights!
SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)

Group: General Forum Members
Points: 880 Visits: 1249
http://msdn.microsoft.com/en-us/library/ff929071.aspx

this might help you.

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
c4ntaloop
c4ntaloop
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 81
Thank you for the link.

If I can gather all the limitations of using contained database do not allow:

> Partially contained databases cannot use replication, change data capture, or change tracking.
> Numbered procedures
> Schema-bound objects that depend on built-in functions with collation changes
> Binding change resulting from collation changes, including references to objects, columns, symbols, or types.

If I have a situation where I need to move a 'normal' database (non-contained db) across server, and it has one of the features above active . Would that be a good practice to switch it to contained database, move it to new server, then switch it back to its original state? Will it work as it used to be? I hope my question make sense.

Thanks
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62450 Visits: 19102
If you don't have contained users, or collation issues, you get zero benefit from contained databases. There's no point to changing to partial containment, moving the db, and changing it back if you don't create the contained logins.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
c4ntaloop
c4ntaloop
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 81
Thanks Steve.

So that makes me think in what real scenario do we want to use contained database? We know that it can have contained users and solve collation issue, which makes a contained db is very portable and can be migrated easily anytime. But in what real case or why one want to sacrifice those features for the sake of easy migration. Can anyone please shed a light.

Thanks.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62450 Visits: 19102
The case is that you don't have any instance logins, only contained ones in your database. You can migrate all your users (windows are easy) to contained, and then you can easily move the database across instances.

I suspect they'll get the Service Broker, CDC, etc. figured out in a version or two, but it's really a network service issue. How do I register the endpoint if I'm moving a queue or replication to a new instance?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 872
c4ntaloop (7/11/2013)

So that makes me think in what real scenario do we want to use contained database? We know that it can have contained users and solve collation issue, which makes a contained db is very portable and can be migrated easily anytime. But in what real case or why one want to sacrifice those features for the sake of easy migration. Can anyone please shed a light.


Obviously it is a trade-off. If you want the ease of database logins, and easier collation handling, and you think that that none of the features not supported in a partially contained database are of interest for you, then a contained database is a good choice.

The problem is only know when you don't need any of those other feature. You may not need Service Broker or Change Tracking today, but who knows what needs you may have three years from now?

I think the idea of a contained database is very good, but what is offered in SQL 2012 is too half-baked, to be really useful.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
c4ntaloop
c4ntaloop
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 81
Erland Sommarskog (7/14/2013)
The problem is only know when you don't need any of those other feature. You may not need Service Broker or Change Tracking today, but who knows what needs you may have three years from now?

I think the idea of a contained database is very good, but what is offered in SQL 2012 is too half-baked, to be really useful.


I agree, requirements keep changing and I think it's important to be flexible as much as possible. It will be great to see improvement from Microsoft in the next or two versions.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 872
...but as far as I can tell, there are no changes to contained databases in SQL 2014, at least not in CTP1.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Andreas.Wolter
Andreas.Wolter
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1056
c4ntaloop (7/11/2013)
Thanks Steve.

So that makes me think in what real scenario do we want to use contained database? We know that it can have contained users and solve collation issue, which makes a contained db is very portable and can be migrated easily anytime. But in what real case or why one want to sacrifice those features for the sake of easy migration. Can anyone please shed a light.

Thanks.



The main reason why contained databases have been implemented is the AlwaysOn Feature. (In fact it can be said that there is one major company with this specific demand)
So in other words: Contained Databases are meant for AlwaysOn Failover scenarios, even though some of the feature may help in other circumstances. Second one would be deployment to Azure..
Hope that helps.

Andreas

---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search