January 19, 2012 at 2:17 pm
I have a database design where I've split the tables into five different schemas. One of the managers/architects (not a data architect) is fairly insistently suggesting that the schemas should be removed and placed into five separate databases.
I believe this is a mistake, but I do not know of any articles to quote and/or direct him to for verification.
Am I mistaken? Can someone help out?
Chris
January 19, 2012 at 2:24 pm
Have they stated why they believe it should be done?
If each of the schemas is completely independent from the others and are used by completely different applications, then I would support putting them in different databases. This would allow independent recovery of the databases without impacting the other applications.
January 19, 2012 at 2:28 pm
chrisvolny (1/19/2012)
I have a database design where I've split the tables into five different schemas. One of the managers/architects (not a data architect) is fairly insistently suggesting that the schemas should be removed and placed into five separate databases.I believe this is a mistake, but I do not know of any articles to quote and/or direct him to for verification.
Am I mistaken? Can someone help out?
How about functionality? do the five schemas/databases serve a single application like lets say billing or ledger?... or do they serve very dissimilar stuff like Payroll and CERN's central monitoring system?
How about consistency? Ask yourself (and your manager) this question... in case of a small disaster where you have to restore/recover, is it important for these five schemas/databases to be consistent with each other?... hard to do it if you have five separate databases, by-design piece of cake if you have five schemas on a single database.
Can't comment further 'cause no details about the environment were given.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 19, 2012 at 2:50 pm
I appreciate the comments. All five schemas are used by a single application. He did bring up an example from a different project he worked on where the security information was kept in a separate database because it was used by several different applications; I pointed out exactly you pointed out.
No, no reason was given other than he thinks it would be easier. Although, when I told him as the DBA I disagreed and think it is much easier as designed, he started mentioning database growth. I did not see this as an issue because four of the schemas will grow rather slowly, if at all.
Is there a Best Practices document regarding schemas vs databases?
Chris
January 19, 2012 at 3:09 pm
Not really a best practices and the growth argument goes against him too. Either way there will be database growth.
If it is to support a single application and the functionality is somewhat interdependent, I think it should be a single database. Multiple schemas is fine.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2012 at 3:23 pm
I don't know of a best practices document, however, my general guideline in this:
If you can restore the data independently of each other, different databases for maintenance simplicity and faster recovery. If the data is dependent but you'd like to keep it ODC levels of organized, schemas. Schemas generally are an additional layer of security controls for databasing, but can be used for pure organization as well.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 19, 2012 at 3:28 pm
Evil Kraig F (1/19/2012)
I don't know of a best practices document, however, my general guideline in this:If you can restore the data independently of each other, different databases for maintenance simplicity and faster recovery. If the data is dependent but you'd like to keep it ODC levels of organized, schemas. Schemas generally are an additional layer of security controls for databasing, but can be used for pure organization as well.
If the data is truly separate and/or is used by different apps - different databases most likely.
Another alternative though is to use filegroups and piecemeal restores.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2012 at 3:34 pm
SQLRNNR (1/19/2012)
Evil Kraig F (1/19/2012)
I don't know of a best practices document, however, my general guideline in this:If you can restore the data independently of each other, different databases for maintenance simplicity and faster recovery. If the data is dependent but you'd like to keep it ODC levels of organized, schemas. Schemas generally are an additional layer of security controls for databasing, but can be used for pure organization as well.
If the data is truly separate and/or is used by different apps - different databases most likely.
Another alternative though is to use filegroups and piecemeal restores.
True, but I personally hate filegroup/file restorations. Personal preference however, not a lack of validity. I mostly use them for access speed, BLOB separation, and spindle optimization.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 19, 2012 at 4:53 pm
Evil Kraig F (1/19/2012)
SQLRNNR (1/19/2012)
Evil Kraig F (1/19/2012)
I don't know of a best practices document, however, my general guideline in this:If you can restore the data independently of each other, different databases for maintenance simplicity and faster recovery. If the data is dependent but you'd like to keep it ODC levels of organized, schemas. Schemas generally are an additional layer of security controls for databasing, but can be used for pure organization as well.
If the data is truly separate and/or is used by different apps - different databases most likely.
Another alternative though is to use filegroups and piecemeal restores.
True, but I personally hate filegroup/file restorations. Personal preference however, not a lack of validity. I mostly use them for access speed, BLOB separation, and spindle optimization.
All good reasons.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply