replication technique

Hello,
I read serveral articles about the mechanism of replication from a sql
server 2000 database on
desktop with an mobile device running windows mobile.
I want to remark my understanding fo the replication mechanism and wanted to
ask you if you
could give me feedback if I understand it in a correct way.
-----
1) First of all I create a publication - which is a collection of articles -
rows. It is
possible to define certain tables and rows for publication.
2) After the publication a windows ce-based application can call the methods
of the SQL Server
Ce Replication object to subscribe to the publication. When the mobile
application has executed
the subscription the initial snapshot of the published table is stored on
the mobile device and
so the database file is created on the device.
3) The subscription database on the device can then be updated by
synchronzing it with the desktop
database. For that the synchronisation is always intiated by the device. For
that the SQL Server CE
Client Agent extracts all modified rows from the subscription database put
these rows in a message
file and sends it over HTTP to the SQL Server CE Server Agent. The SQL
Server CE Server Agent
creates a new message file on the desktop where the IIS is running and
writes in that file the
modified rows which are sent by the SQL Server CE Client Agent.Then the SQL
Server CE Server Agent
initiates the SQL Server Reconciler process. The SQL Server Reconciler
Process loads the SQL Server CE
Replication Provider which reads the message file which was created by the
SQL Server CE Server Agent
and apply the changes to the publisher database. The SQL Server CE
Replication Provider informs the SQL Server
Reconciler about the changes which were made in the subscription
database.The SQL Server Reconciler
also solves conflicts if more than one subscriber or publisher updates the
same record.
On the other hand the SQL Server Reconciler informs the SQL Server CE
Replication Provider of
changes which occured at the publisher database. The SQL Server CE
Replication writes this
information - especially the modified rows - to an output message file.
After the Reconciler
process is completed the SQL Server CE Server Agent locates the the output
message file reads
the information and transfer it to the SQL Server CE Client Agent which
apply the changes from
the output message file to the subscription databse on the windows ce device.
-----
a) Are these steps correct or have I forgotten anything important or have I
understand something
wrong?
b) What I have to imagine behind the SQL Server CE Client Agent, the SQL
Server CE Server Agent,
SQL Server Reconciler and the SQL Server CE Replication Provider? What are
these terms exactly?
c) When I publish a database, especially tables in the enterprise manager
the tables get automatically
a row guid. What part plays these row guid's in my above mentioned
replication process?
d) The Replication Mechanism uses static and dynamic filtering. Is it right
that the dynamic filter
could not use on the application layer? - means that it is not possiblt to
replicate for example
the information according to the user which is currently logged in the
application? - I think it
is only possible to synchronize according to a certain device especially
certain host, is that right?
I know this is a extensive posting but I would appreciate it very much if
anybody find time to
answer me my questions. :-/
Regards.
patrick
answers inline
--
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
"pat" <pat@.discussions.microsoft.com> wrote in message
news:273513F0-8240-4E1A-9598-9ED04BC448CE@.microsoft.com...
> Hello,
> I read serveral articles about the mechanism of replication from a sql
> server 2000 database on
> desktop with an mobile device running windows mobile.
> I want to remark my understanding fo the replication mechanism and wanted
> to
> ask you if you
> could give me feedback if I understand it in a correct way.
> -----
> 1) First of all I create a publication - which is a collection of
> articles -
> rows. It is
> possible to define certain tables and rows for publication.
Yes, you will need to set up a merge publication. When you say rows for
publication you mean horizontal filtering.
> 2) After the publication a windows ce-based application can call the
> methods
> of the SQL Server
> Ce Replication object to subscribe to the publication. When the mobile
> application has executed
> the subscription the initial snapshot of the published table is stored on
> the mobile device and
> so the database file is created on the device.
Yes, you will code something using vb or vc embedded or the .Net compact
framework to pull a merge subscription. This will use a SQL CE database on
your PocketPC.
> 3) The subscription database on the device can then be updated by
> synchronzing it with the desktop
> database. For that the synchronisation is always intiated by the device.
> For
> that the SQL Server CE
> Client Agent extracts all modified rows from the subscription database put
> these rows in a message
> file and sends it over HTTP to the SQL Server CE Server Agent. The SQL
> Server CE Server Agent
> creates a new message file on the desktop where the IIS is running and
> writes in that file the
> modified rows which are sent by the SQL Server CE Client Agent.Then the
> SQL
> Server CE Server Agent
> initiates the SQL Server Reconciler process. The SQL Server Reconciler
> Process loads the SQL Server CE
> Replication Provider which reads the message file which was created by the
> SQL Server CE Server Agent
> and apply the changes to the publisher database. The SQL Server CE
> Replication Provider informs the SQL Server
> Reconciler about the changes which were made in the subscription
> database.The SQL Server Reconciler
> also solves conflicts if more than one subscriber or publisher updates the
> same record.
> On the other hand the SQL Server Reconciler informs the SQL Server CE
> Replication Provider of
> changes which occured at the publisher database. The SQL Server CE
> Replication writes this
> information - especially the modified rows - to an output message file.
> After the Reconciler
> process is completed the SQL Server CE Server Agent locates the the output
> message file reads
> the information and transfer it to the SQL Server CE Client Agent which
> apply the changes from
> the output message file to the subscription databse on the windows ce
> device.
>
This is basically it.
> -----
> a) Are these steps correct or have I forgotten anything important or have
> I
> understand something
> wrong?
that's basically it

> b) What I have to imagine behind the SQL Server CE Client Agent, the SQL
> Server CE Server Agent,
> SQL Server Reconciler and the SQL Server CE Replication Provider? What are
> these terms exactly?
> c) When I publish a database, especially tables in the enterprise manager
> the tables get automatically
> a row guid. What part plays these row guid's in my above mentioned
> replication process?
it uniquely identifys each row, so SQL Server can figure out which row has
changed or needs changing.

> d) The Replication Mechanism uses static and dynamic filtering. Is it
> right
> that the dynamic filter
> could not use on the application layer? - means that it is not possiblt to
> replicate for example
> the information according to the user which is currently logged in the
> application? - I think it
> is only possible to synchronize according to a certain device especially
> certain host, is that right?
>
No, you define your filter on the publication. I don't believe you can
filter your subscribers.

> I know this is a extensive posting but I would appreciate it very much if
> anybody find time to
> answer me my questions. :-/
> Regards.
> patrick
>
|||Hello,
Thank you very much for your reply but few things are still unclear:
1) Why do I need the row guid? - the changed rows are saved in a file and
are sent to the SQL Server CE Server Agent, so he knows which rows were
modified when he looks into the message file. Why does he need then a row
guid?
2) The SQL Server CE Client Agent, the SQL, Server CE Server Agent, SQL
Server Reconciler and the SQL Server CE Replication Provider - are these
processes or components or parts of the os? what I have to imagine behind
these terms?
3) Referring to dynamic filtering I thought it is possible to synchronize
after a certain host on the mobile device? - What can I then realize with
dynamic filterin?
(static filtering is when I define some rows or columns or tables which I
can then synchronize or?)
Hopefully you could answer me once more.
regards
patrick
3)
|||Hi,
Thank you very much for your reply but unfortunately few things are unclear:
1)Why do I need this row guid's, because the SQL Server CE Server Agent
reads the modified rows from the message file which is sent by the SQL
Server CE Client Agent and so the SQL Server CE Replication Provider knows
what rows he has to modify. So why do I neet this row guids?
2)Tthe SQL Server CE Client Agent, the SQL Server CE Server Agent, SQL
Server Reconciler and the SQL Server CE Replication Provider - what do I have
to imagene behind these terms? Are these processes or components or part of
the os or anyting else?
3)Referring to the filtering. So it is not possible to define a filter
according to a certain host on a mobile device? - The static filtering allows
to define some rows or columns or tables which can be modified, but then for
what is the dynamic filtering?
regards
patrick
|||hi,
is it possible to answer me the last time on my three posted questions?
regards
patrick
sql

Replication methods

Hi,

I'm a newbee to replication and wanted to know about the whole mechanism of replication and how is two way replication implemnted on 2 diffrent servers located at 2 different remote locations?

Replication is a huge subject, but it depends on what your requirements are.

Can data be entered at both locations, and do you want data sent both directions? If so, you want Merge replication.

I think that before we give you an educated answer, you need to provide us with a few more requirements of what you are trying to accomplish. The reason I say this is becuase a blanket statement like "how does replication work" would take volumes to answer. I'm not trying to be rude, just trying to get a little more information so we can best answer your question, only because there are so many aspects and types of replication.

However, it is certainly possible to implement replication with 2 different servers located at 2 different remote locations. It just depends on how you want it implemented and your data requirements.

|||Basically triggers fire and write enteries in tracking tables which refer to the row and table which was updated.

When the merge agent runs it consults these tables to determine what changed on the subcriber since the last time it ran. It then uploads this list to the publisher and processes the deletes first. It compares the changes which have occured on the publisher and subscriber and if any of them have occured on the same row, this row is marked as a conflict and the conflict resolution mechanims kicks in.

Then the merge agent begins to fill in changes which have occured on the publisher to the subscriber and vice versa.

Replication mechanism

Hello,
My situation is as follows:
I use Replication in my Mobile Application and when the PocketPc is
connected via USB the replication works fine. But when I tried to synchonize
the data via WLAN it does not work. My quoted url in my code looks as follows:
oRpl.InternetUrl = "http://IP-Adress/HelpDesk/sscesa20.dll"; //I also tried
it to replace the IP-Address with the machineName of my Desktop but it does
also not work.
I also tried to type in this address in my PocketPc when the WLAN is
activated but it occurs always an error that this page could not be loaded.
Does anybody know what else could be go wrong?
thx
patrick
what is the error message you are getting?
Also what happens when you use IE to connect to this url from your PocketPC?
You should get a message saying SQL Server CE agent or something like that.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"pat" <pat@.discussions.microsoft.com> wrote in message
news:09678E54-FBB5-44EA-9327-6ED5FCBD28D7@.microsoft.com...
> Hello,
> My situation is as follows:
> I use Replication in my Mobile Application and when the PocketPc is
> connected via USB the replication works fine. But when I tried to
> synchonize
> the data via WLAN it does not work. My quoted url in my code looks as
> follows:
> oRpl.InternetUrl = "http://IP-Adress/HelpDesk/sscesa20.dll"; //I also
> tried
> it to replace the IP-Address with the machineName of my Desktop but it
> does
> also not work.
> I also tried to type in this address in my PocketPc when the WLAN is
> activated but it occurs always an error that this page could not be
> loaded.
> Does anybody know what else could be go wrong?
> thx
> patrick
|||That's the strange thing no error message occurs. When it is possible to
call this url "http://IP-Adress/HelpDesk/sscesa20.dll"; with my PocketPc and
the SQL Server CE Agent message appears, does then the replication must work?
patrick
|||Not necessarily, all it means is that your Pocket PC can connect to the web
server and the virtual directory. I think it also means that your web server
security is set up correctly. Now check the underlying security of your
snapshot folder. Does this reflect read access for the anonymous account
(IUSER_MachineName, or whatever account you are authenticating with)?
Then you should run profiler to determine how far into the replication
process your pull is going. This will help to trouble shoot further.
What would be really helpful is if you could post the error message your
program is raising.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"pat" <pat@.discussions.microsoft.com> wrote in message
news:732A1E5F-E844-4D0C-B119-B75692989AF7@.microsoft.com...
>
> That's the strange thing no error message occurs. When it is possible
> to
> call this url "http://IP-Adress/HelpDesk/sscesa20.dll"; with my PocketPc
> and
> the SQL Server CE Agent message appears, does then the replication must
> work?
> patrick

Replication mechanism

Hello,
Can anybody here explain me shortly how the mechanism of replication
generally works? (with SQL Server CE Agent, SQL Server Agent, etc.)
thx
robert
Robert,
the 'SQL Server CE documentation' has a good set of descriptions and BOL
will explain standard replication. Is there something specific you have in
mind?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||With merger replication each row has a tracking column called a rowguid
column. Each row has a different value for this rowguid column, but
globally tables in the publisher and all subcribers which contain this
row will have the same value for this guid. This enables the merge
replication process to track changes to this row. This of it as a
Social Security Number, or Social Insurance Number (not sure what the
non American, non Canadian equivalents are).
Each table which is part of a merge publication has triggers of it, so
if one of the rows is modified tracking information is written to
tracking tables.
When the merge agent runs (run by SQL Server Agent) it consults these
tracking tables to determine what has changed since the last time it
has run. It has a counter called a generation which increments each
time the merge agent runs. It then gets a list of the rows which has
changed since the last time it ran and brings them to the publisher in
batches.
When the list of rows arrives at the publisher the publisher compares
its list with the list of changes it has and figures out what goes
where. If changes have to be made on the subsciber it fires procs there
to make these changes, if changes have to be made locally it uses sql
statements. If it detects that the same rows have changed on both the
subscriber and publisher it figures out who wins by using the conflict
resolution policy and logs the conflict info to conflict tracking
tables.
It then gets the next batch.
SQL CE does much the same thing - only it is done programmatically.
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

Replication Mechanism

Hello,
My Replication works fine when my PocketPc is connected via USB Cable with
the Desktop. Do I have to implement any additional things that the
Replication Mechanism also works with WLAN? (My PocketPc has WLAN). Is the
oRpl.InternetUrl the decisive factor? - because at this url I have stated the
following url: http://machineName/HelpDesk/sscesa20.dll.
thx
robert
That depends.
On your Publisher do this: http://machineName/HelpDesk/sscesa20.dll
I think this gives you a message saying something like "SQL Server Agent CE
Runnning" or something like this.
Then on your Subscriber issue the same query through the Ineternet Exporer
program. You should see the same result.
If you do, you have connectivity and your problem is likely with setting up
security in your script. If you can't you have to configure security on your
publisher.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"robert" <robert@.discussions.microsoft.com> wrote in message
news:770EC375-0C9F-4304-9D33-E03AE7583C48@.microsoft.com...
> Hello,
> My Replication works fine when my PocketPc is connected via USB Cable with
> the Desktop. Do I have to implement any additional things that the
> Replication Mechanism also works with WLAN? (My PocketPc has WLAN). Is the
> oRpl.InternetUrl the decisive factor? - because at this url I have stated
> the
> following url: http://machineName/HelpDesk/sscesa20.dll.
> thx
> robert
|||Okay thank you for your reply, unfortunately I have two more questions:
1)Do I not have to replace the machineName with my IP-Adress of the desktop
when I call this in the IE of the PocketPc?
2)And what can be the problem if this would not work, although with
connectivity of the USB it works?
thx
robert
|||1) You may have to. The pocket pc will attempt to do name resolution on its
own using a local broadcast. If the IP address works and the name doesn't
you have a problem with name resolution.
2) with the USB you have two options in active sync, connecting to the
INternet or work. If you were connected to work it will find your host no
problem as it is using USB. If not, it has to do the same name resolution as
it will using Wireless.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"robert" <robert@.discussions.microsoft.com> wrote in message
news:C6222C3B-F64B-4219-BF2C-2DFEB166EA93@.microsoft.com...
>
> Okay thank you for your reply, unfortunately I have two more questions:
> 1)Do I not have to replace the machineName with my IP-Adress of the
> desktop
> when I call this in the IE of the PocketPc?
> 2)And what can be the problem if this would not work, although with
> connectivity of the USB it works?
> thx
> robert