October 5, 2005 at 3:36 am
Hello,
I am part of a team designing a networked application. We are using MSDE since we do not anticipate the size of the database getting larger than 2GB. Our application has three main parts. Let's call them A, B and C.
A - can be run independently of B and C
B - is a utility of C and cannot be run independently
C - is highly dependent on A and B
My question is: Should I have one database or three? I am not worried about restore/backup scenarios. If I have one, there will be lots of tables (hundreds). But I think it will be easier to get the data I need for the application and cut down on the number of requests to the MSDE if I have one. On the other hand, it seems more logical to break them up into three.
Any thoughts? Thanks in advance.
Pete
October 5, 2005 at 7:33 am
Based on the inforamtion you provided, one database seems better in terms of development, maintanence, and deployment.
SQL server is not good at manage relations between databases. So create one db is a good idea in your case
October 5, 2005 at 2:42 pm
G'Day,
I'm not sure I agree with Peter, mostly because there is not enough information in the original post.
Is the application an internal company app? Or is it a product? Are the three parts of the application going to be developed, maintained, and managed under the same revision? Or will each part have its own associated code baseline and version number? Do you need to deploy the backend of application part B and C to a different server than application A? Is the database required to support a single customer or multiple customers? If multiple customers, do you have a scalable mechanism to support the anticipated volume? What happens if the volume doubles? increases ten fold?
In general, looser coupling between application components allows a developer to handle all of these issues, particularly over the course of an application life cycle. Separate DBs do have an associated overhead, but easier maintenance may make up for the additional effort. It is difficult to make an assertion without more information.
Hope this helps
Wayne
October 6, 2005 at 2:49 am
Thank you both for the replies. I will try to address the questions one by one.
Wayne:
The application is a product that will be sold. It will be packaged with MSDE.
As for versioning, we currently have only one, but since development has only begun on one of the three, there is the possibility that we will have separate versioning for each one. However, releases will be done with versions of the complete application (A, B and C), even if we have only changed one part.
In the event of three databases, they will all be on the same instance of the database (MSDE). The purchasing company will have the option to upgrade to a full version of SQL Server (Standard or Enterprise). This upgrade will place the db's on the same instance of the new sql server as well.
The application will be designed to support 20 users or so. Which I have read on the Microsoft site, should be fine for the MSDE. In the event they want more users, we will recommend they upgrade to SQL Server (Standard or Enterprise).
In terms of scalability, our option will be to upgrade. The code will be optimized (of course). Our main limitation will not be the size of the database but potentially the number of users. I would be very suprised if the database became larger than 2GB and in most cases it won't approach that size.
Peter:
It certainly does seem easier to use only one database at this point. My concern is that later in development I will realize that I maybe should have gone with three. Based on the new information, could you give me any more insight.
Both:
I am glad for the opposing views. I would love to hear both sides of the discussion. Thanks again.
Pete
October 6, 2005 at 6:44 am
Wayne the lack of information is exactly why all data should be in a single database; In my opinion, related items go in the same database, period. I would rather wait for a business rule that says why they must be in separate databases, rather than make an assumption that because they are separate apps, and i don't know anything about them, they need separate databases.
From a developer's perspective, maintaining a single database schema for 3 apps is much better than maintaining 3 separate database schemas, one for each app as well. It makes maintenance and upgrades go much smoother.
Lowell
October 6, 2005 at 8:03 am
Lowell: Please notice that I did not aseert whether I thought one database was right or wrong - I asserted that there was not enough information to make an educated decision.
Considering the additional information that was provided, I agree that a single DB makes the most sense for this particular product at this point in its product lifecycle. The user load, the anticipated volume, the single code baseline, etc all suggest a "simpler is better" approach.
As an example of the opposite extreme that would point towards multiple DBs appropriately partitioned, and so on, the original poster might have said something like the following:
Need to scale to 2 million concurrent users, scale up to a terebyte of data every six months, hard real time data collection, scale up to a sustained load of 1600 TPS, with near-real-time analysis and push to a data warehouse product, with each system component developed and maintained under its own product life cycle and delivery schedule. This scenario is much different that the small environment the product actually needs.
Even in this scenario, one could theoretically place everything in a single DB, but in practice it just doesn't work.
Hope this helps
Wayne
October 6, 2005 at 8:20 am
Hi all,
Thank you for the responses. I am leaning in the single database direction for the moment.
I would like to ask what the ramifications would be if I did go with the single database design and then upgraded to SQL Server Standard or Enterprise to accommodate 100 users. Once again, the size of the database will not significantly increase (there will be hundreds of tables, but still between 1GB - 2GB), just the number users.
In the case of 100 users but a small database size, is a single database still wise? If not, what would be issues I would encounter.
Thanks again for the replies.
Pete
October 6, 2005 at 9:01 am
IMHO the number of users does not affect solution regarding number of databases in any way. We have almost 200GB database with normally about 40, in peaks up to 80 concurrent users - and it is still one database.
Where you have to be extremely careful when increasing the number of concurrent users is locking issues... these need to be solved and tested properly before you put it into production, otherwise the system could become practically unusable with high number of users. Well, if your DB is really going to be like 2 GB, then probably even locking shouldn't be too bad - real problems start if you have tables with millions of rows - but it is something that is not to be overlooked.
Well, and of course the more users, the greater is probability, that they will do something wrong, unexpected, not accounted for in design :-)).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply