Which Replication Method To Use - 11-23-2006 , 06:47 PM
I have never used replication before and I have been asked to consider
it in a project I am currently working on.
I have created an application for a sales team which is loaded on their
machines, it uses ms sql as its data source and connects via the
internet back to the central server in the office.
Problem is this has shown to be too slow causing time out error
messages and so on. I have been told to research the possibility of
replication, but am unclear what type of replication to use or where to
Any assistance would be appreciated.
Re: Which Replication Method To Use - 11-24-2006 , 02:29 AM
bfausti (AT) gmail (DOT) com wrote:
Books On Line can be quite helpful, since it describes what is
available, and typical characteristics of situations in which you would
apply each type of replication.
Typical questions you have to ask yourself are where data is going to
be updated (at the central server, at the remote machine, both), how
frequently data changes, how rapidly data needs to be transferred to
other machines, etc.
For instance, if each sales team member needs to be able to access and
update all data (and be offline for long periods?), then you probably
would be looking at merge replication. If the sales team members should
only have access to some subsets of the data (e.g. They're only allowed
to view their own sales history), then you'd still be looking at merge,
but you'd also want to look at dynamic filters.
On the other hand, if the central server is only hosting catalog
information, and everything else is only dealt with locally, then
snapshot replication would probably be more appropriate.
Re: Which Replication Method To Use - 11-24-2006 , 10:18 AM
You also have to take in consideration what is your software security.
Are you user able to create or modify everything, or only some data ?
If they have wright to create and modify everything, you must replicate
all tables in your publication. If not , only a subset of your tables
are to be involve in your publication. That could speed up your
replication process, and he developpement of your replication solution.
Second, i suggest that you synchronise "manually" from the client side,
because your client wont probably be connect all the time to the
"publisher". (ask your user to synchronise minimum once a week)
Re: Which Replication Method To Use - 11-24-2006 , 04:01 PM
I appreciate your help with this, I think merge replication would
probably be best suited as the sales people would only connect
manually, say once a week.
Re: Which Replication Method To Use - 11-24-2006 , 04:14 PM
Thanks for your reply.
This is the situation regarding the software security, all users have
read only access to all data and have the ability to create new
entries, but sales people can only modify/delete their own.
There is also an additional 2 levels of security applied, sales
managers & sales directories.
Sales managers can modify/delete their branches/states sales
information & sales directories can modify/delete their countries sales
information. (As this application is used internationally.)
I have been doing some further research and have concluded that merge
replication would be best suited here while applying identity ranges
for tables containing identity columns for each sales staff to
avoid/minimize conflicts when syncing. Could you please let me know if
there is anything else I have missed?
Also another quick question, when setting up each
subscription/salesperson do I have to replicate all tables that get
used in the application or only the ones that will be modified by the