Multiple databases

Greetings,
I have a problem which I hope that some SQL guru can help me with. I work
for a start-up software development firm and am currently the only
programmer on staff (and it looks to remain that way for a while.) We have
a product (problem) that I inherited and I am in the process of
re-architecting it from the ground up as the previous developer was... inept
in every possible way. The problem I am having centers around a database
architectural issue:
We process applications (rental) for four stores at the moment (as an ASP),
and the project stakeholders expect to expand this greatly in the future.
The application is web based and uses a separate, identical database for
each customer. I find this to be an inefficient design, however, the
project stakeholders feel more comfortable with this design as it is. The
primary worry being security. My primary worry on the other hand is
maintenance and programability. At any rate, I am looking for some case
studies and or white papers of companies that have developed products in
which their primary business is to service customers as an ASP.
What is the better method, one database in which all client/company records
are dileneated by an identifier or one database per client/company. Please
note that we do not expect any one store/company to even remotely stress the
server. In fact, having done some observation over the last month or so, I
have never seen any one company execute more that 400 inserts or 3200
queries per day.
If someone could point me to some case studies I'd be mighty appreciative.
Regards
Troytroy
I would think one of the main issues for you to think about is availability
rather than security. If you put all four stores in one database. and you ha
ve a major problem with one store you will probably affect all the other sto
res. Depending on the probl
em this may be a very slow service or even no service at all.
Obviously if you are providing a service you need to give your customers as
good a service as possible. Either having four different servers or using SQ
L 2000 and having four seperate instances will give you much higher availabi
lity. It is down to your bu
siness to decide if the extra cost in hardware and licenses is worth it for
the higher availability.
Hope this helps
John|||John,
Thanks for the input. I agree that ensuring up-time is of paramount
importance. As with most things related to software development, it's all
about trade-offs. The more I have thought about it the more sense it makes
to have each company's data reside in a separate database (as opposed to
different servers or named instances of SQL running,) however, having said
that, I am not sure as to how I am going to manage all of the databases. As
I mentioned in my first post, each of these databases are identical in all
ways - tables, stored procs, views, indexes - everything.
The thing that I am struggling with the most is, ensuring that any changes
made to one database are reflected in all databases. For example, if I need
to make a universal business rule change to a stored procedure it is no
problem to propagate that change to four databases; however, if I need to
propagate those changes to 500 databases... well, that's a problem. I can
always automate the process using SQLDMO or a stored proc. But as you can
tell, it's not going to be elegant or pretty.
You wouldn't happen to have any on-line resources (white papers or case
studies) would you?
Regards,
Troy
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:481E24D0-6CC7-4281-B4AF-ED0404CD36F5@.microsoft.com...
> troy
> I would think one of the main issues for you to think about is
availability rather than security. If you put all four stores in one
database. and you have a major problem with one store you will probably
affect all the other stores. Depending on the problem this may be a very
slow service or even no service at all.
> Obviously if you are providing a service you need to give your customers
as good a service as possible. Either having four different servers or using
SQL 2000 and having four seperate instances will give you much higher
availability. It is down to your business to decide if the extra cost in
hardware and licenses is worth it for the higher availability.
> Hope this helps
> John|||Troy
From what you are saying I would be tempted to think about making it one dat
abase with a different set of views for each customer. If your business is s
uccessful and you do get to the 500 mark, you may need more than one server
(I don't know database size
s or server specs, so I can not guess), even so you should be able to do it
with a minimum of changes that way.
The obvious drawback to this is you may end up with some very large tables w
hich are hard to maintain.
Sorry I dont have any white papers. Have you tried [url]www.sql-server-performance.com[
/url] they might have something to help.
Regards
John