Need to update up to 3000 user DB

Hi,
We have an SQL 2005 Server with the whole enterprise data.
We are building an application that requires a per user database with its
own data (horizontal filtering).
Our first idea is to create 3000 mdf files, one db for each user. This DB is
created by copying a "model" db with the data structure, then using
SqlExpress's AttachDbFilename feature to connect to user db and fill it with
filtered data from the main server. Each user DB has the main server in its
linked servers in order to be able to query the main server from the user DB
and compare data from both sides (to find the differences). Subsequent
updates are done using the same SQL queries. MDF files are stored on a
server that has SQL Express installed to which the app connects.
This solution works well but the problem is that each user DB update (near
300 Sql requests for 50 mb results DB) requires at least 30 seconds to
execute. 3000 users means 25 hours to complete the whole process. The main
server stay at low CPU level, but the "express" server quickly increase its
CPU usage (near 100% when 4 updating threads are working simultaneously),
but we cannot buy a 64 cpu computer ;).
We are looking for a faster solution. Do you have any idea which could help
us either to optimize our solution or even another method ?
We do not want to use the Sql Server replication because it is not
applicable in our environment.
Buying other SQL Server is possible but must be justified.
Thanks,
Steve
Steve
> Our first idea is to create 3000 mdf files, one db for each user. This DB
> is created by copying a "model" db with the data structure, then using
Actually you have not explained what are you trying to achive?
What is if the data will be stored in one database? Where do user locate?
How do they access the database?
We have many customers installed on their workstations MSDE/EXPRESS and just
pushed the data (as a publisher) from pur main server . Yep , it is a
replication which sometimes has some problems that hard to solve.
"Steve B." <steve_beauge@.com.msn_swap_com_and_msn> wrote in message
news:%23%23SBw8nTGHA.6048@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We have an SQL 2005 Server with the whole enterprise data.
> We are building an application that requires a per user database with its
> own data (horizontal filtering).
> Our first idea is to create 3000 mdf files, one db for each user. This DB
> is created by copying a "model" db with the data structure, then using
> SqlExpress's AttachDbFilename feature to connect to user db and fill it
> with filtered data from the main server. Each user DB has the main server
> in its linked servers in order to be able to query the main server from
> the user DB and compare data from both sides (to find the differences).
> Subsequent updates are done using the same SQL queries. MDF files are
> stored on a server that has SQL Express installed to which the app
> connects.
> This solution works well but the problem is that each user DB update (near
> 300 Sql requests for 50 mb results DB) requires at least 30 seconds to
> execute. 3000 users means 25 hours to complete the whole process. The main
> server stay at low CPU level, but the "express" server quickly increase
> its CPU usage (near 100% when 4 updating threads are working
> simultaneously), but we cannot buy a 64 cpu computer ;).
> We are looking for a faster solution. Do you have any idea which could
> help us either to optimize our solution or even another method ?
> We do not want to use the Sql Server replication because it is not
> applicable in our environment.
> Buying other SQL Server is possible but must be justified.
> Thanks,
> Steve
>
|||The Sql express db are used to store an image of data that each user
require.
The actual "end user" application is a mobile application in which a sql
mobile DB is synchronized against a http application that will make all the
required work to ensure the sql mobile DB has exactly the same data than the
Sql express one.
This "intermediary" sql express db is required since the MS Merge
replication between sql 2005 and mobile device can't be used because of
replication limitations.
Since the main DB is quite big, we want to compute differences for each user
within the lan, before sending it to the device (that's why we want to use
mdf files that are exact copy of the data for the user, which is easier to
synchronize since it is 1-1 with the device).
Thanks,
Steve
"Uri Dimant" <urid@.iscar.co.il> a crit dans le message de news:
%23LSWwGoTGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Steve
> Actually you have not explained what are you trying to achive?
> What is if the data will be stored in one database? Where do user locate?
> How do they access the database?
> We have many customers installed on their workstations MSDE/EXPRESS and
> just pushed the data (as a publisher) from pur main server . Yep , it is a
> replication which sometimes has some problems that hard to solve.
>
>
>
> "Steve B." <steve_beauge@.com.msn_swap_com_and_msn> wrote in message
> news:%23%23SBw8nTGHA.6048@.TK2MSFTNGP11.phx.gbl...
>