Multiple SQL Server

[georgepatotk]

Dear all,

 

I am facing a big problem here. I have a SQL server and this server is shared by few branches around Asia.

 

Since all the branches are connecting to the HQ MSSQL server, the speed of processing in the branches are slow.

 

I got a solution from a friend, just an idea, and I dont' know how to implement it practically. Hope that you could tell me how to do so.

 

I want to install MSSQL Server in every branches, and all the SQL Server in branches are connecting to HQ's Server. SQL Servers in branches will do batch processing with HQ server every 15 minutes.

 

What are the methos/tools that I need to use to accomplish this?

Hope you could tell me the step-by-step solution as well. Thanks a lot.

[JABE]

I suppose your problem is synchronizing branch data to HQ, right? These types of problems are usually handled by SQL Server Replication. The online help provides much detail on this.

[PlausiblyDamp]

Unfortunately ther is probably going to be no magic solution that will make the performance improve. Splitting the data over several SQL servers can definately offload traffic from the network and improve response times by getting users to connect to their office's local install rather than a central server.

SQL server does include support for replication as part of the standard product install, however this could involve a lot of work and possibly a redesign of parts of your application. Implemented properly however replication could be a major performance improvement and also can have benefits (replicate to devices like CE or notebooks that are not permanently connected etc).

 

You may find these links a bit more informative

http://www.microsoft.com/sql/evaluation/features/replication.asp

http://vyaskn.tripod.com/repl_ques.htm

http://www.windowsitlibrary.com/Documents/Book.cfm?DocumentID=77

[georgepatotk]

Yes, i think replication is the one I am looking for.

 

There are three types, which is the most effiecient to be implemented with my topology as shown..

[PlausiblyDamp]

Really depends - where do updates need to be made and where will these updates need to be propagated to?

how much of a lag is acceptable between changes in one server and them being replicated to remote servers?

[georgepatotk]

The scenario is like this:

 

Every half an hour, client will update the latest data into the HQ server.

after updating, client will request for the latest copy of databases from HQ.

 

there is no connection/communication in between clients/branches. HQ will be the one handle all the transction..

 

lag is acceptable but not until very bad..

 

How?

[PlausiblyDamp]

In that case you would probably be best going for a simple model with HQ being a publisher (and probably distributor) while each of the remote office are subscribers. Each remote office could be scheduled to retreive the data every 15 - 30 minutes (probably testing will give more accurate times).

Going for a transactional replication model would probably be easiest as this would only send changes out to the subscribers rather than the entire DB - although initially you would need to sync the remote offices with the HQ server (this can be taken care of within the replication system itself though)

[georgepatotk]

ok, thanks a lot, i will test it out when i am in office.