March 26, 2003 at 12:59 pm
I am trying to determine the best way to organize our SQL Server 2000 databases. Currently, we create a database for each application that is developed. Due to the number of applications that have been developed, we have begun to question whether this design is appropriate. Most of these databases have tables that are common across all applications, but currently each database has a copy of the tables. Should we move the common tables to a "Common" database and link to the database via SQL statements? What are the pros and cons of this method? Is creating an independent database for each application appropriate, or should we split these databases up based on departments in our organization? or some other logical entity. Any thoughts and/or ideas on this would be greatly appreciated.
thanks,
Gregory Olds
Systems Developer
Gregory Olds
Systems Developer
March 26, 2003 at 1:31 pm
If you are going to use only one db for all app, how many concurrent connections do you espect?
March 26, 2003 at 1:39 pm
To me this is a matter of data, I mean does your apps use the same data, then they should use the same database. Way too many people see a database as simply a storage point for a single application. But if they are not the same at all and unse completely different data there is no reason to consolidate them into a single database, you won't get any automatic performance boost from it.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 26, 2003 at 1:45 pm
We still would use a database for each app but we would also have a database that would have data common to all applications. Most of the data would be static data such as tables for states, pay codes, employees. It seems much easier to update one Employee table in a common database instead of updating a dozen databases with the same exact Employee table every time an Employee's status changes.
Gregory Olds
Systems Developer
Gregory Olds
Systems Developer
March 26, 2003 at 2:26 pm
Yep, as I said, if they use the same data, then they could have a common database (for this data that is shared between them).
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 26, 2003 at 6:42 pm
I would consider your apps cross functional in nature. If you are duplicating data accross apps you risk corruption and consistansy issues that can really cause some grief. If all these databases are on the same server and the server isn't overloaded with connections or poor I/O performance I would consider merging all like tables. Other things to consider is time to recode applications and the like to point to the new structures. You won't see a performance boost from rolling all tables together ether. Another aspect to consider is are all these apps OLTP or are some reporting apps that wouldn't play well with high transaction apps?
Pros,
Consistancy and ease of updates to single tables that are shared accross databases and applications.
Less chance of logic problems accross applications in general.
Much easier to monitor and maintain fewer databases.
Cons,
There could be a performance issue with all connections hitting one table for information i.e. locking issues you may not have seen due to the distributed natue of the data.
Migrating the databases to diffrent servers as load increases means some sort of replication or re-design of systems again as tables get split back out for performance reasons.
I would lay out all your applications and their data needs then deside the best way to re-organize your databases and data in them.
Wes
March 27, 2003 at 3:50 am
Gregory:
Moving all the tables with the static data might be a wise move but do remember the problem of having inter-DB foreign keys.
For the time problem of recoding the apps, it could easily be solved with simple hiding of your moved tables with views that 'knows' about the new locations of your tables.
Locking issues might be low due to the static nature of the tables.
quote:
You won't see a performance boost from rolling all tables together ether.
I would say that SQL Servers ability to cache tables might lead to performance gains (depending of course of the size of the tables and the memory in your server machine).
In the end I belive that is as Wes said, this course of action might not be suitable for all your apps and a specific advise bout your situation is hard to give.
March 31, 2003 at 10:04 am
now this is weird when you realize that databases were invented precisely for the purpose of integrating the data of an organization, eliminate the "islands of data" problem and providing a common and consistent view of the organization's data.
the reason databases were developed was precisely to eliminate this "each application had its own set of data" situation.
March 31, 2003 at 10:04 am
now this is weird when you realize that databases were invented precisely for the purpose of integrating the data of an organization, eliminate the "islands of data" problem and providing a common and consistent view of the organization's data.
the reason databases were developed was precisely to eliminate this "each application had its own set of data" situation.
March 31, 2003 at 10:26 am
Omen: I believe you are talking about data warehousing and not DBs in general?
Many times in OLTP processing you need different sets of data depending on the demands of the departments/applications (it could still be the same data but with different designs and preformance impacts). Though care should be taken for keeping your data consistent it does not automatically mean that storing it in one place always is the most efficient...
/Hans
March 31, 2003 at 10:32 am
It looks as if creating a common database with like tables is the way to go, if I have a small group of concurrent users. These tables will be static. We have a very small user base in the office so I do not forsee any problems with locks, or other performance related issues.
Gregory Olds
Systems Developer
Gregory Olds
Systems Developer
March 31, 2003 at 11:40 am
hanslindgren: i meant databases in general and not datawarehouses. the concept of databases was developed earlier than datawarehouses. its true that different departments might have slightly different needs and uses for the same data, but this is the very reason we have the concepts of enterprise models, logical models, etc.
datawarehouses, as seen from the original idea of having a database vs application specific files/data structures, is just a stop gap measure. its ironic that the stop gap measure seems to have achieved an independent existence of its own.
in practice, and specially if you dont have legacy baggage to deal with, i would almost always recommend that an enterprise level view of data be sought first.
about the only instance when separate databases should be thought of automatically is when the data being considered is inherently not related to each other (e.g. a development database server containing databases meant for different companies with totally different needs...but each company should each have an as-integrated-as-possible database)
April 1, 2003 at 12:28 am
Omen, I agreee with you in that databases where 'invented' for integrating the data of an organization and to provide a centralized management of this data. However, one should not mix the general term database with SQL Server's term database.
I see no reason at all to store all data in one user database in SQL Server if the data is not related, however if the data is related then I see no reason not to store it in a common database. Also, as I said earlier, if the data is actually the same, then of course it should be in a single database and definately not redundantly stored in different databases. I think we mean the same thing omen, just wanted to state out that a user database in SQL Server is not the exact same thing as a database in it's general definition.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
April 1, 2003 at 1:28 am
At one site where I worked, they had a very strong view of how we should develop systems.
What they did was to identify data as either being corporate data or application data and each had its own database. If the data was needed in more than one application it was deemed corporate data and if it was specific to an application it was in the application database (a bit simplified but you get the idea). It was a bit like normalisation of data. When designing an application, you don't (usually) have repeated/redundant data within an application so why do it within an organisation?
Most of the accesses to the corporate data will be reads and you will not have any locking issues.
However, one of the big problems you will have is convincing management of the time, effort and cost of moving over to the new style. It costs a lot more than you think and there is very little immediate benefit - lots of long terms ones such as reduced maintenance, quicker development times as you don't have to duplicate somthing that is already there etc. They will say 'You want to spend all this money and I'm not getting anything extra!' Good luck with this one.
Jeremy
April 1, 2003 at 6:41 am
Jeremy,
Using a corporate and application database structure, how did you manage the data integrity. Did you use triggers to manage the relationships of the application data with the corporate data. I assume when you are coding applications you need two connections, one with the corporate database and the other with the specific application database for the application you are developing. Is the corporate database on the same server as the application databases?
thanks,
Greg
Gregory Olds
Systems Developer
Gregory Olds
Systems Developer
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply