Multiple Databases vs a Few

I started work a new company recently and they provide a web based tool to
companies. The database model is in rough shape -- when a new client comes
on they simply create a new database for each client. Right now they have
roughly 400+ databases split over two database servers.
I am suggesting they bring that # down to a couple. I think managing 400
connections to a variety of databases might be a performance concern. I
would also think that in terms of Stored Procs it must be using more
resources -- instead of optimizing 1 it has to optimize the same 1, 400
times.
Im guessing on those above -- does anyone know the postive and negatives in
doing this, or know of any article discussing this topic.
ThanksBrian wrote:
> I started work a new company recently and they provide a web based
> tool to companies. The database model is in rough shape -- when a
> new client comes on they simply create a new database for each
> client. Right now they have roughly 400+ databases split over two
> database servers.
> I am suggesting they bring that # down to a couple. I think managing
> 400 connections to a variety of databases might be a performance
> concern. I would also think that in terms of Stored Procs it must
> be using more resources -- instead of optimizing 1 it has to optimize
> the same 1, 400 times.
> Im guessing on those above -- does anyone know the postive and
> negatives in doing this, or know of any article discussing this topic.
> Thanks
I don't necessarily think using separate databases is an architectural
problem. It does, however, lead to more involved backups and SP
management. OTOH, the databases are smaller, can be backed up more
quickly, and different databases offers a level of security that might
be more difficult to manage should all clients be combined into the same
tables in the same database. What happens when a client leaves? How
involved is the removal of data if they share tables? What happens if
one client's data is accidentally shown as being "owned" by another
client? Does that lead to potential legal concerns... You get the idea.
The architecture, it seems, may need to involve legal as well as IT.
Your comments about SP duplication, however, is a concern. Certainly, if
you could find a way to share all common procedures, it would help
maintenance. You could consider using a shared database for all (most)
stored procedure access. That should allow you to use a single set of
procs for all user databases. Instead of executing the procedure in the
current database, just prefix the shared db (shareddb.dbo.procname).
David Gugick
Imceda Software
www.imceda.com|||Brian
There are no reasons to create a database for each client. I think you are
going to build a new model of the database , only you do know a business
requrements.
Visit at www.sql-server_performance.com and you will find a really good
stuff there.
"Brian" <brian@.nospam.com> wrote in message
news:%23k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client
comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>|||It is an administrator's nightmare. However, if the DBs for each of your
clients has data related to that specific client and should not be mixed
with other clients (which is correct) then I guess, you will have to live
with it. If the DBs contain code and metadata for your web based client
application, then consolidating is the right approach.
"Brian" <brian@.nospam.com> wrote in message
news:#k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client
comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>|||Have you considered using partitioned views to unify all this data in a
common DB? Using that method you may only need a single copy of much of your
server-side code.
I would have expected that the main argument for unifying the databases into
one would be based on the potential savings on development, reporting and
admin costs over the costs of those things for 100s of identical databases.
David Portas
SQL Server MVP
--|||There is little to no difference between the databases. A couple clients
have a few custom pieces but nothing that could not be solved in the data
model.
Aside from the admin nightmare (it exists now), is there any performance
hit. I guess this may be more is an IIS webserver question. Having to
manage 400 different connection strings.
And again the duplicate Stored Procs, and who knows what else.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:uwhvCspuEHA.2144@.tk2msftngp13.phx.gbl...
> It is an administrator's nightmare. However, if the DBs for each of your
> clients has data related to that specific client and should not be mixed
> with other clients (which is correct) then I guess, you will have to live
> with it. If the DBs contain code and metadata for your web based client
> application, then consolidating is the right approach.
>
>
> "Brian" <brian@.nospam.com> wrote in message
> news:#k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> comes
> in
>|||Hi Brian,
I am a dba for multiple companies in a datacenter and one of our companies
has around 400 lawfirms and everything is in one database. The main thing
that you have to think about is security. In our case each lawfirm has a rol
e
and many tables contain a field that specifies to which law firm the row
belongs (row level security). This is basically the idea but it gets more
complex when you have multiple groups with each law firm. This is based on
the requirements and something that you have to analyze). If it was me, I
would create one database with 2 clients and test the crap out of it... The
process might be painful but you have a lot to win as a DBA (amount of work
for you and the developers, $$$, more simplified disaster recovery
process...).
Sasan Saidi, MSc. in CS
"Brian" wrote:

> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client come
s
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>
>|||Hi Brian,
I have noticed Peter and Sasan Saidi had given their suggestions on this
issue. I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!