Distributed system patterns - SQL Server deployment

DEV ENVIRONMENT: .NET and some COM
DATABASE: SQL Server 2000
CLIENT OS: Win2K, WinXP
SERVER OS: Win2K3
Greetings,
I have a client who insists on having their customers in separate SQL
databases for an online component. I instinctively feel that a consolidated
database for all clients is the "natural" way to implement. Here's the
system setup:
1) Several hundred clients have an n-tier client/server system at THEIR site
(A). They use this system to run their business and serve THEIR customers
(B)
2) We host and operate an online system (C) for these several hundred
clients
that provides an online presence that THEIR customers (B) can use ...
therefore a database now exists at site (A) and at the web site online (C).
These databases get synchronized. Both databases serve the client base (B).
In conclusion, some technical arguments for the one consolidated database at
site (C) are:
i.) Less server resources in terms of database connection pools since these
are usually connect string dependent
ii) Easier reporting (across clients)
iii) Scaling past several hundred clients seems scary (i.e. 1000, 2000, 3000
SQL databases ?)
Proponents for a multiple database scenario have these arguments
i.) Separate SQL Server files (MDB) limit risk of data corruption to 1
client at a time
ii.) Easier to partition if clients ever decided to HOST their own online
presence.
iii) Increased security (perceived or real)
Please provide any feedback or arguments for either side.
Thanks in advance.
Bert Nieves
Software Consultant
bert89@.yahoo.comBert Nieves (bert89@.yahoo.com) writes:
> I have a client who insists on having their customers in separate SQL
> databases for an online component. I instinctively feel that a
> consolidated database for all clients is the "natural" way to implement.
> ...
> In conclusion, some technical arguments for the one consolidated
> database at site (C) are:
> i.) Less server resources in terms of database connection pools since
> these are usually connect string dependent
> ii) Easier reporting (across clients)
> iii) Scaling past several hundred clients seems scary (i.e. 1000, 2000,
> 3000 SQL databases ?)
> Proponents for a multiple database scenario have these arguments
> i.) Separate SQL Server files (MDB) limit risk of data corruption to 1
> client at a time
> ii.) Easier to partition if clients ever decided to HOST their own online
> presence.
> iii) Increased security (perceived or real)
Of these I would consider point ii) of the pro arguments and iii) of the
contra arguments to be the most important.
If you need to do reporting for one client over all their customers, then
one database per customer is not going to be fun. But you could have jobs
which extracts data from the smaller databases into a big reporting
database.
On the other hand, if your client's customers will have access to the
databases themselves, and it is instrumental that these customer cannot
see the slightest of some other customer's data, this a very strong
argument for separate databases. Yes, you can solve this with views,
stored procedures etc, but it takes just one bug to ruin the users'
confidence in the system.
As for scaling, you probably scale better with more databases, because
you can then easily distribute the databases on several boxes.
One thing you don't mention, is that many databases will require more
effort into setting up backup routines etc.
In the end, this is a trade-off between business requirements and cost
of ownership. One big database is probably cheaper - unless the business
requiresments for separating customers from each other is very high.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp