How can implement these simple scenario

Hello All,
I admit I am new to the replication spectrum, all my experience is basically
reading.
If you know of a book or site that has all the details please let me know.
These scenarios occur in the application that I am about to develop
Scenario #1
Client X wants specific data be download to use/modify offline. The data
changes will then be merged back with the server. As I see now, and I could
be totally wrong so please correct me, that can be done through stored
procedures on the server. I mean the client can call a stored procedure on
the server (publisher) with the specified parameters (say all the records
from Table= "SALES" Where City = "NEW YORK"). The stored procedure generates
the replica (must be pull type) and then the client subscribes to replica,
download locally (pull, on demand) and disconnect. Note download and upload
(for synchronization) must be Client-triggered i.e. on demand.
Is there such thing as Client Initiated (pull) replicas? if yes how can we
do it?
Scenario #2:
Client X schedule to synchronize. how does the data get uploaded to the
server? do I trigger the merge manager? I want to have control when a merge
can take place
Scenario #3
Client X schedule a sync. and shuts down and the user goes on 3 day vacation.
I know how collision detection woks. In our model the server always wins in
collisions.
How would client X know of a conflict next time it logs in?
How much data is kept in the System tables on the server about the
collisions?
Info about the pending CRUD transactions.
How do I poll the merge agent on the client to know how many transaction
there pending sync with the server?.
How do I set a threshold to force CLIENT X to sync every 100 or so CRUD
transaction.
Is there an merge agent API to pull out this info. or where can I get the
info?
Collinsions or related multi-Record and rollback.
How do I roll back on multi record merge?. Say Record A and B can both take
place or none. so if A is ok in Table-A and B has collided in Table-B then
roll back both. If A collides in Table-A then abort operation.
I still have few more questions that are dependents on those. So will wait
till I hear from you first.
Thank you for the help.
Canada
Answers inline...

> Hello All,
> I admit I am new to the replication spectrum, all my experience is
> basically
> reading.
> If you know of a book or site that has all the details please let me know.
>
have a look at Hilary's book - http://www.nwsu.com/0974973602p.html

> These scenarios occur in the application that I am about to develop
> Scenario #1
> Client X wants specific data be download to use/modify offline. The data
> changes will then be merged back with the server. As I see now, and I
> could
> be totally wrong so please correct me, that can be done through stored
> procedures on the server. I mean the client can call a stored procedure on
> the server (publisher) with the specified parameters (say all the records
> from Table= "SALES" Where City = "NEW YORK"). The stored procedure
> generates
> the replica (must be pull type) and then the client subscribes to replica,
> download locally (pull, on demand) and disconnect. Note download and
> upload
> (for synchronization) must be Client-triggered i.e. on demand.
> Is there such thing as Client Initiated (pull) replicas? if yes how can we
> do it?
>
You have offline updatable subscribers - this can be implemented by
transactional replication (queued updating) or merge. The client can
initiate the pull subscription using activeX controls or windows
synchronization manager or starting the relevant job.

> Scenario #2:
> Client X schedule to synchronize. how does the data get uploaded to the
> server? do I trigger the merge manager? I want to have control when a
> merge
> can take place
>
As above...

> Scenario #3
> Client X schedule a sync. and shuts down and the user goes on 3 day
> vacation.
> I know how collision detection woks. In our model the server always wins
> in
> collisions.
> How would client X know of a conflict next time it logs in?
> How much data is kept in the System tables on the server about the
> collisions?
>
The conflict viewer will show the conflicts.
The conflict winner and loser row details are held in the conflict table.

> Info about the pending CRUD transactions.
> How do I poll the merge agent on the client to know how many transaction
> there pending sync with the server?.
>
sp_Browsereplcommands for transactional and the procedure at the top of
www.replicationanswers.com/merge.asp for merge.

> How do I set a threshold to force CLIENT X to sync every 100 or so CRUD
> transaction.
> Is there an merge agent API to pull out this info. or where can I get the
> info?
>
As above for merge, and have a look at sp_replqueuemonitor

> Collinsions or related multi-Record and rollback.
> How do I roll back on multi record merge?. Say Record A and B can both
> take
> place or none. so if A is ok in Table-A and B has collided in Table-B then
> roll back both. If A collides in Table-A then abort operation.
>
This is 'logical records' and is only available in SQl Server 2005 for
merge. For transactional, the ACID properties are retained.

> I still have few more questions that are dependents on those. So will wait
> till I hear from you first.
> Thank you for the help.
> Canada
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Try my book - check my sig for more info. There are a couple of web sites
out there with replication information on them. The best is
replicationanswers.com currently.
1) you are looking for merge replication. I think you might want to look at
Windows Synchronization Manager to manage the pull, either that or a
Replication ActiveX control.
2) A Pull Subscription using Windows Synchronization Manager or using a
replication Active X control will do this.
3) If you use Windows Synchronization Manager and configure your publication
and WSM for interactive conflict resolution when WSM runs the user will get
prompted to resolve all update and delete conflicts. Insert conflicts (PK
violations) will not show up here.
Other than that you will have to connect to the Conflict Viewer which is
accessible through EM or from the command prompt by typing
C:\Program Files\Common Files\Microsoft Shared\Database Replication\Conflict
Vie
wer\wzcnflct.exe
You can also connect to this programmatically using the
Info is stored in the conflict tables on the publisher these are calle
?onflict_publicationName_tablename
two weeks worth of data - or whatever your retention settings are is stored
here.
CRUD - basically the merge agent takes care of figuring out what is waiting
to be downloaded to the subscriber. There is no api to control after how
many transactions are to be merge whether the merge agent can run, rather it
is bases on schedules.
Rollbacks - conflicts are rolled back automatically depending on your
conflict resolution policy. Rollbacks are not done on a transactional basis,
but may be done if there is a DRI relationship between the two records.
Merge replication is termed loosely consistent for this reasons.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lottoman2000 NEWBE" <Lottoman2000 NEWBE@.discussions.microsoft.com> wrote in
message news:651002C3-949F-42FA-B582-CDDB7813D607@.microsoft.com...
> Hello All,
> I admit I am new to the replication spectrum, all my experience is
basically
> reading.
> If you know of a book or site that has all the details please let me know.
> These scenarios occur in the application that I am about to develop
> Scenario #1
> Client X wants specific data be download to use/modify offline. The data
> changes will then be merged back with the server. As I see now, and I
could
> be totally wrong so please correct me, that can be done through stored
> procedures on the server. I mean the client can call a stored procedure on
> the server (publisher) with the specified parameters (say all the records
> from Table= "SALES" Where City = "NEW YORK"). The stored procedure
generates
> the replica (must be pull type) and then the client subscribes to replica,
> download locally (pull, on demand) and disconnect. Note download and
upload
> (for synchronization) must be Client-triggered i.e. on demand.
> Is there such thing as Client Initiated (pull) replicas? if yes how can we
> do it?
> Scenario #2:
> Client X schedule to synchronize. how does the data get uploaded to the
> server? do I trigger the merge manager? I want to have control when a
merge
> can take place
> Scenario #3
> Client X schedule a sync. and shuts down and the user goes on 3 day
vacation.
> I know how collision detection woks. In our model the server always wins
in
> collisions.
> How would client X know of a conflict next time it logs in?
> How much data is kept in the System tables on the server about the
> collisions?
> Info about the pending CRUD transactions.
> How do I poll the merge agent on the client to know how many transaction
> there pending sync with the server?.
> How do I set a threshold to force CLIENT X to sync every 100 or so CRUD
> transaction.
> Is there an merge agent API to pull out this info. or where can I get the
> info?
> Collinsions or related multi-Record and rollback.
> How do I roll back on multi record merge?. Say Record A and B can both
take
> place or none. so if A is ok in Table-A and B has collided in Table-B then
> roll back both. If A collides in Table-A then abort operation.
> I still have few more questions that are dependents on those. So will wait
> till I hear from you first.
> Thank you for the help.
> Canada
|||Thank you so very kindly.
I hope i am not repeating myself.
Can a stored procedure produce a custom merge replica that the client can
downlaod at a later time?
|||There are 2 parts to this process. Firstly, the snapshot agent creates the
'replica'. It'll stay around until all named subscribers have picked it up,
or the retention period is exceeded. If you have anonymous subscribers,
it'll be the latter only. Next, once there is a snapshot, the merge agent
can propagate it to the subscriber. This only occurs once and is known as
initialization. Afterwards running the merge agent doesn't rely on the
snapshot at all and this is known as synchronization. Both these agents are
run as SQL Server jobs and sp_start_job can be used to run them. So, 'yes'
if there is a snapshot, the subscriber can initiate the application of it
himself when required.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||All nice and dandy...thanks
In our model, its a one-one mapping between replicas and clients.
Client X will invoke the creation of replica X and once its picked up (by X
only), its no longer needed.
What is best to run on the client to get this functionality of this thead?
Where will the Merge Client Run?
What "manager" or who handles the the bi-directional
(upload/download)communication/interfacing between the client (subscriber)
and the publisher
Thanks
|||Assuming the snapshot agent for each publication is already run, you just
need to initiate the merge agent. This handles all the data exchenge. To do
this programatically, have a look at these articles:
http://support.microsoft.com/default...b;EN-US;319646
http://support.microsoft.com/default...b;EN-US;319646
http://www.windowsitpro.com/Articles...layTab=Article
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Can we automate all the process end-to-end?
"Paul Ibison" wrote:

> Assuming the snapshot agent for each publication is already run, you just
> need to initiate the merge agent. This handles all the data exchenge. To do
> this programatically, have a look at these articles:
> http://support.microsoft.com/default...b;EN-US;319646
> http://support.microsoft.com/default...b;EN-US;319646
> http://www.windowsitpro.com/Articles...layTab=Article
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Sure - just schedule the snapshot agent and the merge agent.
Rgds,
Paul Ibison
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:D4DD650E-FAC3-440C-BD8E-23BEE82CF7D3@.microsoft.com...[vbcol=seagreen]
> Can we automate all the process end-to-end?
> "Paul Ibison" wrote:
|||Perfect. I will be writting a document (about 1-2 pages at most) about all
these fucntionality that can be handled for our model. I would like to share
it with you.
if you agree to take a look, i will very thankful. let me know please at
lottoman 2000 AT gmail . com (remove the spaces and replace the AT with
the proper symbol)
"Paul Ibison" wrote:

> Sure - just schedule the snapshot agent and the merge agent.
> Rgds,
> Paul Ibison
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:D4DD650E-FAC3-440C-BD8E-23BEE82CF7D3@.microsoft.com...
>
>