One Major Dbase V Many Small Dbs
- Hello,We are having a debate about the merits of maintaining a single large database from which to run many different applications, or to have a single Dbase per application.Historically we have used one Dbase per application and used DTS to move data between applications in order that each Dbase is self contained. This has the benefit that a problem only affects a single application and does not bring the whole world crashing down. A single Dbase puts all our eggs into one basket, but cuts down on the amount of "plumbing" to keep each application current.Having searched for "Best Practice" I can only find articles about the technical side of running SQL Server rather than a discussion on "One V Many".Can I ask what your experience is, reasoning behind current set up and if you have any links to articles "tucked away".This is going to be an "it all depends" sort of discussion, but any comments would be most helpful. Thank you for your time and contribution.
Regards
Colin
-
Both ways have their merrits, it really depends on the aplication, the security needs, how much interaction must occurr between systems, where the interaction has to actualy take place (I have a few apps that talk to vaious databases realtime on an as needed basis which exist on other servers). We have one scenario where we even replicate the data between two servers so there is a read-only version of the data available to eac app and should something happen to on or the other they serve as each others DR site for short duration if needed. So it really isn't cut and dry. The best way to appraoch is consider the pros an cons with regards to the specific needs of each application.
-
Colin,
My two penne'th worth. I have worked in companies where both approaches have been applied and my summary would be:
Many Databases
Pros
- easier/tighter control management of database security
- faster backup/restore management with smaller files
- improved resilience/lack of impact on other databases in the event of a problem
- easier to develop in eg. only 30-40 tables to deal with instead of having to find yours within 1,000
Cons
- increased management overhead (unless it can be automated) of backups and security
- information sharing between databases
Obviously this is not exhaustive. The pros and cons for One big database are more or less the opposite of the pros and cons of the Many approach.
Personally I would err more towards the many approach and automate the backup management as much as possible. I would then attempt to extract the common information from each database (e.g. employee data, usernames, names, email addresses, phone numbers etc) and create a central database in which to manage these and replicate a readonly version out to any sql server which needs to read it. Each database can then read it's own local copy of the "CommonData" database but continue to maintain it's own application specific data.
As usual though, I suppose it's whatever suits.
-
I would also vote for many databases vs. one big single-point-of-failure database. Yes there is extra maintenance to deal with multiple databases, but with some effort you should be able to automate most of the "plumbing" instead of maintaining a unique copy for each database.
-
we recently did the same thing in our shop...we had separate databases for each application. whenever one of our clients had more than one application, the issue of duplicate data entry came up, as well as consolidated reporting of data that exists in both databases. simple things like look up tables for city/county/zip code , as well as application specific look up tables got consolidated, and made it much easier to report against. one person puts together the upgrade scripts now, instead of the leader of each application.
here's the pro's from my perspective:
less duplicate data entry
less duplicate data (many tables merged together)
easier to create reports
easier to handle login /upgrade issues
cons: some applications may be ready to issue an upgrade, that requires a schema change, but have to wait for other applications to finish their changes, because we don't want to issue weekly schema changes as applications jump to new versions.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I certainly wouldn't recommend creating additional copies of things like zip code lookup tables for each database, but these can be handled by putting them in a common database. If the application code can't handle multi-database queries, a view could be added to each database that refers to the common table.
If the db-per-client approach is using roughly identical databases and the same appllication per client, it might be a good candidate for consolidation. If there are per-client customizations or completely different applications, stick with db-per-client. I would rather have 50 databases with 50 tables apiece than one database with 2500 tables. Even if they are identical databases, do the clients have similar volumes of data? If you have one monster client with 500GB of data and 50 clients with 10MB of data, the small clients won't be happy with performance when lumped together. Instead of using one big database you could consolidate into a few databases with similar client sizes.
Are there separate logins for various databases? Will consolidation create a security nightmare? Every report may have to be written to ensure that no client can see another client's data.
Are these heavily transactional database, or mostly decision support? Consolidating the databases makes reporting easier, but hurts transaction performance.
Is reporting done by client, or do you have to constantly union data from multiple clients to get consolidated reports? Do you have to jump through hoops to get queries pointed to different databases, or even worse resort to dynamic SQL? These might be strong arguments for consolidation.
So it seems to come down to "it depends".
-
I would plan for growth.
While it is easy to overlook today, what will happen when one of your applications gets so popular that you need to move it to a different server for performance reasons? You'll need to move the tables and dependent objects to another database.
So, my approach has been to use 1 (or more) db per application. Often, I will separate the main database from other components, such as reporting and EDI, even going so far as to use separate instances of SQL.
But it does depend.
-
I would recommend a database per application as the roll-out of new databases in-line with new application versions is simplified. A backup strategy for all databases is simple enough to construct and automate.
We also employ a 'base' database with the lookup tables that are referenced by more than one of the applications - such as postcode etc.
One place I have worked we employed a separate instance of SQL server for reporting.
It really is a "horses for courses" decision.
-
I definitely lean towards multiple databases, 1 per app, especially for scalability in case one client outgrows the system or causes issues for others.
However, there have been some good points raised and I think it could be helpful if you had a way to manage duplicate data between databases if they need to be related, like one client using 2 copies. Maybe some backup replication or other process to ensure things don't require duplicate work or you can rollup data from 1..n databases.
-
I'll add one more vote to the DB per application.
We've centralized most of our lookups, and automated everything else, from creation to backups to transfers to security.
One other benefit of the multi-db model: we occasionally have to customize our apps (and their db) for a client. This could/would be considerably more difficult in the one-big-db scenario.
Cheers!
-
Thanks to all who responded, a great thread of information which has reinforced my own thoughts of 1 Dbase per application. We do indeed run a central Database for names, addresses etc and these details are copied to the individual application Dbs as required, on an hourly basis. This maintains the point of single entry and all applications are kept to an acceptable level of current data.
Thanks again for the help/support
Colin
-
I'll weigh in on the one database per application side. Its much more convenient to move an appilcation database to another server when its seperated out from the others. Its also a lot easier to create a copy for developers to see if they can resolve a customer issue.
The biggest grey area is in those common tables, postal codes, country codes and the like. For generic data like these I'd prefer a seperate read only database. Such tables don't often need updates and having a central repository to update all applications is very nice. Cross database queries are easy enough to put together and won't be a significant performance issue unless you are running close to the capacity for your machine. Use stored procedures for your reports and you won't even notice that the data is coming from two databases.
Bill
-
One big problem with multiple databases is maintaining referential integrity. If your Customer table is in one database and another database has a reference to the Customer table, you cannot have a cross database foreign key reference, especially if it on a different server instance. You can replicate the Customer table into the local database, but then there are issues with keeping it up to date.
So it really depends on how tightly the data is integrated. I would say that if the applications are closely related enough that a logical model would have a foreign key reference from one physical database to another physical database, then you should consider not splitting into two or more databases.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply