- Security - the data/access must be totally separated from other data/access. While the usage of schemas, privileges, roles, etc. can be used to restrict access within an application, it does not restrict access to those with broad privileges (such as the dba team). If there is a legal or other requirement that no one be able to access both sets of data, then multiple databases makes sense.
- Recovery - different schemas may have different recovery requirements. If the database is several hundred terabytes, but only a few hundred gigabytes is business critical, then it makes sense to separate the business critical component. In case of database recovery, the business critical data can be restored promptly while the remainder is recovered less urgently.
- Performance - although much of the performance setting has been made 'automatic', there are still times where different applications need different settings/configuration for optimal performance. While some of these settings can be handled via login triggers or sql hints, it may be easier to separate them out into separate databases. Each release seems to reduce the need for different parameters, so this issue is decreasing in importance.
- Features/Versioning - this is probably the main reason for separate databases. When a single database is used for multiple applications supplied by different vendors, you can find yourself in a situation where the vendor requirements are in conflict. VendorA needs FeatureA, but that feature actually breaks the code for VendorB. Not only do you need separate databases, but you often need separate Oracle versions/homes.
Friday, January 23, 2009
Multiple Databases?
A colleague recently asked me about using separate databases for a single business function/project. With a little thought (he needed feedback quickly), I came up with four reasons.
Subscribe to:
Post Comments (Atom)
Followers
5 comments:
I thought the problem was "separate databases for a single business function/project", with emphasys on the "single"
If that is the case, then having separate databases to cope with different version needs - the 4th point - makes little sense.
The third and second points also are hard to justify in light of all the extra overhead one needs to run multiple databases.
Differences in recovery can simply be taken care of with a number of various techniques and are hardly the subject of a single business function or project; as for performance, very much the same applies. I'm thinking here partitioning, multiple tablespaces, undos and temps.
The only thing I can think of is indeed the first one, and even then I am at a stretch trying to explain it in the context of single application/business function!
Let's not forget either that Oracle has been making all sorts of song and dance since 9i about its RDBMS being able to cope with various applications in a single instance. That is what they try to do in their hosted systems as well: as much as possible in a single instance.
There would also be increased costs in terms of licensing.
For all the above I'd tend to disagree with any attempt to have multiple instances for the same business function/application.
Mmm, yes, I see what Noons means. If this is really about a single business function/project, it's hard to see valid reasons. (Having said that, I work at a site where that's precisely what they do, for some of the reasons you've stated, but I find them questionable)
If we're not talking about a single business function, then my number 1 is?
Negotiating down-time.
It's not a technical point and there *should* be organisational solutions, but trying to get different areas of some businesses to agree an outage can be painful. Yes, there should be a regular change window but, even when there is, some businesses are so change resistant that, when the resistances are summed, it's impossible to get anything done!
I'm only half-joking. My main point is that most of the valid reasons are non-technical.
Security - the data/access must be totally separated from other data/access. While the usage of schemas, privileges, roles, etc. can be used to restrict access within an application, it does not restrict access to those with broad privileges (such as the dba team). If there is a legal or other requirement that no one be able to access both sets of data, then multiple databases makes sense.
But then how do you manage all these databases? In that case, one will need to maintain separate DBA teams for each database that needs to be maintained and then (probably) the access restriction be achieved, but only to a certain extent. But is it really feasible to maintain different DBA teams for single business/project?
Recovery - different schemas may have different recovery requirements. If the database is several hundred terabytes, but only a few hundred gigabytes is business critical, then it makes sense to separate the business critical component. In case of database recovery, the business critical data can be restored promptly while the remainder is recovered less urgently.
At least in 2009 and with Oracle Database, I guess there are plenty of options on hand to take care of most of the backup and recovery scenarios. So not sure whether this point is any valid. Although, I must admit I am not quite aware of "best practices" or "standards" followed in managing terabytes of data.
Performance - although much of the performance setting has been made 'automatic', there are still times where different applications need different settings/configuration for optimal performance. While some of these settings can be handled via login triggers or sql hints, it may be easier to separate them out into separate databases. Each release seems to reduce the need for different parameters, so this issue is decreasing in importance.
But if multiple databases are for single business/project, won't there be lots of places in applications, that access data from more than one databases ? Now, how will that be achieved ? Either by "connecting" all database machines (dblink) or "consolidating" data outside the databases? Will that be any good for performance. I seriously doubt that.
Features/Versioning - this is probably the main reason for separate databases. When a single database is used for multiple applications supplied by different vendors, you can find yourself in a situation where the vendor requirements are in conflict. VendorA needs FeatureA, but that feature actually breaks the code for VendorB. Not only do you need separate databases, but you often need separate Oracle versions/homes.
Now that appears to be a valid point initially. But as Noons suggested above, this kind of contradicts the fact that the databases are for "single" business/project. But again, I am not quite aware of these kind of projects.
Dan, others,
one of my fave topics: Databases Everywhere...
Pulling Test-copies or clones for other reasons comes to mind. A combi of "recovery" and "versioning" reasons above.
And one of our customers got scared of the potential multi-terabyte size (approx 2003), and demanded we split it up into what they thought were manageable chunks (define "manageable chunk", eh?).
If you do keep multiple production databases, try to separate them onto (identical, virtual) machines. Makes troubleshooting and maintenance easier.
And still, if you keep multiple databases on one (virtual) machine, you end up like Doug: negociating maintenance windows.
Regards,
PdV
I can definitely second Narendra's observation about the impact to performance once database links are used to pass data from one database to another. Well before my time at my company, my coworkers made the decision to create numerous database for various purposes. Two of them, however, interact quite regularly with each other in our OLTP environment, and we've paid a big price for this poor design.
I'm still trying to impress my developers the need to do extra work when crafting relatively simple queries that span both databases. Pulling over 400,000 rows of a very wide table regularly works, but the SQL*Net
waits are considerable. My developers know little of the driving_site hint, and I constantly have to remind them they need to think creatively when putting together their relatively simple, cross-database queries.
Another topic that I'll have to explore is the use of shared (versus dedicated) database links so that I don't see so many darn sessions on one of my databases. All of those sessions (and their associated PGA) can't be good!
Thanks,
Tom Gaines
Post a Comment