June 3, 2008 at 12:09 pm
Michael Earl (6/3/2008)
I agree with much of that, but this is a case of consolidating business units, not data from several companies (according to the original post). So, the security concerns (at least to me) go way down. This also can take a lot of the "restore part of" pressure off.It also seems likely to me that there will be at least some shared data, so consolidation makes a lot of sense. I do not think we should have any fear of large databases anymore. SQL Server has gotten to the point in which I would almost always prefer one big table over three identical small tables.
If it was sevaral companies, I would tend to split things into individual databases because even the procedure code may need to be a little different. It is hard to get two companies to agree to a date in which a patch that changes some code can be applied.
I guess it depends on what was meant by "business units". I'm assuming, from the original post, that there is a business case for segregating the data on a per-unit basis. The original question was about having the same procs run on data from multiple sources.
I would much rather have the procs in the same database as the data, in almost all cases. If it works to consolidate the data into one database, wonderful! I'm just pointing out that there can be disadvantages to that, if there was a valid reason to separate it in the first place.
If the separation is arbitrary and not needed, then by all means consolidate. If not, then separate, but keep the procs in each database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2008 at 12:18 pm
I agree with GSquared. The approach is not worrying about whether or not the partition is justified. They want to partition. Now, do they manage stored procedures in a central location with dynamic TSQL to determine which DB to connect to (the original solution) or do they manage their code in such a way that they can manage multiple databases containing multiple copies of the same proc (the most commonly proposed new solution)? Let's put them all in one database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2008 at 12:20 pm
noeld (6/3/2008)
The problem with merging is that some maintainability/availability requirements could be different forcing you to adapt to the "highest"...
Agreed. In my situation, all of our databases are back-ends to web-based applications used by students, professors and staff of a large university, so the availability, performance and security requirements are very similar. There are other databases, outside of my group, that have other requirements. For example, the database that holds student personal information has a much higher level of security than normal. For that reason alone, it can never be merged with any other database no matter how closely the schema matches.
But such a database sits on its own dedicated server behind its own dedicated firewall. It may be pretty safe to assume that all the databases on the same server operate under fairly similar availability, performance and security requirements -- though there could be some significant security differences.
GSquared (6/3/2008)
There are some distinct disadvantages to the one-database-to-rule-them-all method.
Of course. There are distinct advantages and disadvantages to any recommendation. There is no such thing as a one-size-fits-all solution (alas). OP will have to do some analysis work, but if only half his databases are good candidates for merging, that's half of his problem solved. That's a pretty good start. And there have been other excellent suggestions that may handle the other half.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
June 3, 2008 at 1:01 pm
Thanks to all for the excellent input. In my case security is not a factor; the application will run internally on the corporate LAN/WAN. The segreation of data is key and ease of maintenance of 'code' (stored procedures, functions) is key. As this project involves a small number of business units, to keep everything simple we will use multiple dbs and keep the code up to date using a sync process from a designated 'source master database.'
June 3, 2008 at 2:38 pm
John Samples (6/3/2008)
Thanks to all for the excellent input. In my case security is not a factor; the application will run internally on the corporate LAN/WAN. The segreation of data is key and ease of maintenance of 'code' (stored procedures, functions) is key. As this project involves a small number of business units, to keep everything simple we will use multiple dbs and keep the code up to date using a sync process from a designated 'source master database.'
Sounds like a good plan to me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply