Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Contained database Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 12:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 09, 2013 5:37 PM
Points: 9, Visits: 53
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
Post #1472448
Posted Thursday, July 11, 2013 1:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:09 AM
Points: 583, Visits: 962
http://msdn.microsoft.com/en-us/library/ff929071.aspx

this might help you.



Pramod
SQL Server DBA | MCSA SQL Server 2012
Post #1472457
Posted Thursday, July 11, 2013 7:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 09, 2013 5:37 PM
Points: 9, Visits: 53
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

Post #1472836
Posted Thursday, July 11, 2013 8:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:20 PM
Points: 32,764, Visits: 14,928
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
Post #1472843
Posted Thursday, July 11, 2013 9:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 09, 2013 5:37 PM
Points: 9, Visits: 53
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.

Post #1472849
Posted Friday, July 12, 2013 11:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:20 PM
Points: 32,764, Visits: 14,928
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
Post #1473183
Posted Sunday, July 14, 2013 3:21 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 755, Visits: 629
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
Post #1473395
Posted Sunday, July 14, 2013 10:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 09, 2013 5:37 PM
Points: 9, Visits: 53
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.
Post #1473446
Posted Monday, July 15, 2013 1:45 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 755, Visits: 629
...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
Post #1473511
Posted Tuesday, July 16, 2013 9:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:37 AM
Points: 153, Visits: 941
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
Post #1474182
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse