Database design question

My company presently creates a sql server database containing detailed
billing information for each of its customers and ships it to them
each month along with an application to view the data and create
reports. These databases contains 99% read only data and most of them
are less than 100Mb. They are looking at providing a web based app
which provides the same functionality however, they do not want to
modify the back end system which creates these individual databases.
I personally am advocating a single large db which would require
modification of the present system but, I am looking for feedback on
either side. Does anyone know of any documentation which compares
these types of designs? Does anyone have experience with a system
which has many databases which are dynamically attached and detached
(we don't anticipate more than 100 databases attached at one time)?
What type of performance was achieved? What maintenance issues did
you encounter?
TIA,
RossPeople can argue about this forever... However there are a couple of issues
you may wish to consider...
1. When you have a large number of databases, the SQL Tools get less
usefull. It seems to take a Lonnnng time opening up Enterprise Manager (for
instance.)
2. If you put things into a single database, you can no longer simply detach
and copy (because the database would contain information for other users .)
However, if you had a web app, you wouldn't have to do the detach at all
(unless your customers are going to require it..?)
Unless there are backup/restore issues or the detach/attach issue , I would
prefer having a single database - Less maintenance, easier use of tools, no
problem with applications - all good.
But reasonable people will differ -
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Ross Bonner" <rosspb3@.yahoo.com> wrote in message
news:47257b97.0403090954.82e83e3@.posting.google.com...
> My company presently creates a sql server database containing detailed
> billing information for each of its customers and ships it to them
> each month along with an application to view the data and create
> reports. These databases contains 99% read only data and most of them
> are less than 100Mb. They are looking at providing a web based app
> which provides the same functionality however, they do not want to
> modify the back end system which creates these individual databases.
> I personally am advocating a single large db which would require
> modification of the present system but, I am looking for feedback on
> either side. Does anyone know of any documentation which compares
> these types of designs? Does anyone have experience with a system
> which has many databases which are dynamically attached and detached
> (we don't anticipate more than 100 databases attached at one time)?
> What type of performance was achieved? What maintenance issues did
> you encounter?
> TIA,
> Ross|||The number of databases used will be large, we presently produce one db
per customer per month, we still haven't decided how many months worth
of data to retain, but it will probably be at least 3 months. The web
app would have to dynamically attach and detach these databases as
needed since leaving all attached would exceed the 32768 database limit.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!