Contained database

  • 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

  • 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/

  • 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

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

  • 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 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?

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

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

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

  • ...but as far as I can tell, there are no changes to contained databases in SQL 2014, at least not in CTP1.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • 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

Viewing 10 posts - 1 through 9 (of 9 total)

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