for large database processing

Question

for large database processing

mayank jarsaniya

Hall of Fame

Points: 3532
More actions
September 30, 2009 at 7:06 am

#214933

hi i have initially 10 millions of data in single table of sql server 2005 with 10 millions rows.
now i have business rule which updates a table minimum of 10 times through out the process.
there are 10 users which can connect sql server through LAN.
now i want to split this 10 millions rows between this 10 users and 10 processes ,this users process at their local PC (local sql server at client side) and gives all the updates to main server.
i think replication will help me but which type of it I should go with?
or any known suggestion for same.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

replication could work here, but with ten seperate databases replicating I would not recommend it.

Why do you want each user to work from their own copy of the database? The whole idea of a client/server application is that one central database is used.

You could possbily look at table partioning, but with only 10 million rows I am not sure it is neccesary, Perhaps if you provide a bit more information about what you want to do someone may be able to offer some more advice..

mayank jarsaniya Hall of Fame Points: 3532 More actions · Answer 2

thanks for reply.

My application continues update table for every records.

and if i will keep central server then all users are connected directly to this table.

and creates a lots of load situation for whole time frame.

so, i am thinking all user has local sql server is already installed and

then each user process 1/10 part of data at locally on local table and then submit their work to central server table.

'I have present simple scenario with 10 million of records it my be 100 million also and we have to process all at once and mostly update for all the records minimum ten times u can bunch data in ten parts separately.'

any advice be great help to me!

steveb. SSC-Forever Points: 46733 More actions · Answer 3

If you are running enterprise edition you could look at table partioning

http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx

The way you describe you situation it seems as though each user is responsible for changing their own subset of data , if this is not the case I apologise but this is how I read it, So a better scenario may be to have each user running their own database and then using SSIS to transfer the data into a read-only datawarehouse for reporting.

Also SQL server can easily handle 10 million rows, 100 million should also be not problem if you have decent indexes and properly written queries.

mayank jarsaniya Hall of Fame Points: 3532 More actions · Answer 4

Ok.thanks.

you are on right track.

I should go or search for table partitioning or ssis.