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 status check

What is the best way to determine if transactional replication is alive and
well? I would like to know if the snapshots are completed, the articles are
being published, and that replicated transactions are being processed by the
subscriber.
Bob b
run sp_MSenum_snapshot
look for a status of 2, which means complete.
to check that replicated commands are being processed issue the below
select * from distribution.dbo.msdistribution_status
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
"bob b" <bobb@.discussions.microsoft.com> wrote in message
news:677477A8-E644-439A-BE90-96B1D94E0B14@.microsoft.com...
> What is the best way to determine if transactional replication is alive
and
> well? I would like to know if the snapshots are completed, the articles
are
> being published, and that replicated transactions are being processed by
the
> subscriber.
> --
> Bob b

Replication sql 2000 question

If i add NEW COLUMNS to 2-3 existing Articles\Tables using
"sp_repladdcolumn" do i have to re-initialize replication & apply
SNAPSHOT or LOG READER will apply all data in that new columns for
subscriber. i am trying to understand when i add new columns how
columns are getting replicated to SUBSCRIBER & how data will be copied
to new columns.
Any help is appreciated to make me understand & thanking experts in
advance.
Provided you're not intending to modify the PK in trans replication (in
which case it won't work anyway) there's no need to reinitialize. The
columns will be nullable initially and there will be no data in them.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||If DDL replication is turned on do you need to use sp_repladdcolumn?
On 4 Feb 2007 21:34:05 -0800, myrights99@.gmail.com wrote:

>If i add NEW COLUMNS to 2-3 existing Articles\Tables using
>"sp_repladdcolumn" do i have to re-initialize replication & apply
>SNAPSHOT or LOG READER will apply all data in that new columns for
>subscriber. i am trying to understand when i add new columns how
>columns are getting replicated to SUBSCRIBER & how data will be copied
>to new columns.
>Any help is appreciated to make me understand & thanking experts in
>advance.
|||In most cases a mini-snapshot of only the modified article will be generated
and sent to all named subscribers.
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
<myrights99@.gmail.com> wrote in message
news:1170653645.410658.238120@.m58g2000cwm.googlegr oups.com...
> If i add NEW COLUMNS to 2-3 existing Articles\Tables using
> "sp_repladdcolumn" do i have to re-initialize replication & apply
> SNAPSHOT or LOG READER will apply all data in that new columns for
> subscriber. i am trying to understand when i add new columns how
> columns are getting replicated to SUBSCRIBER & how data will be copied
> to new columns.
> Any help is appreciated to make me understand & thanking experts in
> advance.
>

Replication setup

How many articles can take part in merge replication?
I need to replicate about 200 tables. From them 25 are common for all my
subscribers and do not need to be filtered. Other I plan to filter with
static filters.
Is there benefit to allocate this 25 common tables in separate publication
or it will be better to include them in all publications where tables with
filters are placed?
Ideally you would have a single publication. There is no performance impact
by having multiple publications. If you are going to have multiple
publications (perhaps a publication for each article), you should group
articles which are related (PK-FK) relationships into the same publication.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"massa" <massa@.discussions.microsoft.com> wrote in message
news:460AB955-4C93-4EE0-BC24-3B479E7807CF@.microsoft.com...
> How many articles can take part in merge replication?
> I need to replicate about 200 tables. From them 25 are common for all my
> subscribers and do not need to be filtered. Other I plan to filter with
> static filters.
> Is there benefit to allocate this 25 common tables in separate publication
> or it will be better to include them in all publications where tables with
> filters are placed?
>

Replication Question

Hi all,
I am seting up a Publisher in SQL 2000.
When I get to the screen to specify Articles after selecting a transactional replication, most of my tables are unselectable. In fact out of 25 tables only one is selectable. The rest have a key with an x next to them. Why would that be?Hi again,
I went ahead and started a replication as merge replication. But now when I created a Pull Subscriber, the job failed with the following:

Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script '\\NJRARSVR00E9\d$\sqldata\system\MSSQL$P001\ReplD ata\unc\NJRARSVR00E9$P001_PTR_PTR Merge\20040106110922\tblUser_1.sch' could not be propagated to the subscriber.

What does this mean?|||Do you have a primary key for each of those 25 tables?|||I do not. I have a primary key on some, but not all.|||You can't do transactional replication on tables tat do not have primary keys.

Try adding primary keys into your table again before doing it.

Replication Programming

I am trying to write c# code to automatically setup a configuration.
I am given a database and a list of articles in the database to
publish.
The Generate Sql script function generats some sql which is what i
based my c# program on.
I used sql-dmo to generate TSQL statments to be executed via
ExecuteImmediate.
When I run my program, there is a hand icon showing the database has a
publication.
The publication shows up in the publication folder.
When I use dbo.sysmergeextendedarticlesview it gives the same result
as what the sqldmo object returns in my code.
But when i try to see the articles in the publication properties, It
says "Cannot retrieve Articles from the database"
Anyone know what the problem is?
thanks,
Hananiel
can you post your code?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hananiel" <hananiel@.yahoo.com> wrote in message
news:e338e321.0410131347.4a668fd5@.posting.google.c om...
> I am trying to write c# code to automatically setup a configuration.
> I am given a database and a list of articles in the database to
> publish.
> The Generate Sql script function generats some sql which is what i
> based my c# program on.
> I used sql-dmo to generate TSQL statments to be executed via
> ExecuteImmediate.
> When I run my program, there is a hand icon showing the database has a
> publication.
> The publication shows up in the publication folder.
> When I use dbo.sysmergeextendedarticlesview it gives the same result
> as what the sqldmo object returns in my code.
> But when i try to see the articles in the publication properties, It
> says "Cannot retrieve Articles from the database"
> Anyone know what the problem is?
> thanks,
> Hananiel
|||Hilary Thanks!,
I found the problem. There was a table dtproperties which shows up as
a system table in enterprise manager. However its type in sysobjects
is 'U'.
I just added it to a list of files i need to exclude anyway. However i
dont how to prevent such a thing in future.
Whats the easiest way to add a job to a scheduler (I need to add an
immediate job to generate a snapshot once, publisher is setup.
(Merge ActiveX didnt have anything to do with the snapshot generation
and the snapshot activeX gives an error no snapshot or transactional
replication item found)
Thanks,
Hananiel
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:<#$LgKWesEHA.2808@.TK2MSFTNGP14.phx.gbl>...[vbcol=seagreen]
> can you post your code?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Hananiel" <hananiel@.yahoo.com> wrote in message
> news:e338e321.0410131347.4a668fd5@.posting.google.c om...
|||Ok I got the snapshot part too!
Thanks,
Hananiel
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:<#$LgKWesEHA.2808@.TK2MSFTNGP14.phx.gbl>...[vbcol=seagreen]
> can you post your code?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Hananiel" <hananiel@.yahoo.com> wrote in message
> news:e338e321.0410131347.4a668fd5@.posting.google.c om...

Replication issue- data changes not seen at subscriber

Hi,
I have very simple one way transactional replication. There are multiple
publications. Every publication has multiple articles. All data in tables
have been published without a filter condition. Recently i have been getting
compalints about data sync. Changes appliad at publisher are not being
propated to subscriber. I verified agents log reader and distributor are
running fine, no error msgs. I manually updated one table in a publication
where i ususally encounter problems, but the change have not been propagated
to subsciber. It is working fine for other table updates. All publications
are from same database. and they go to same destination db.
Can someone pass me, how to troubleshoot to find out where is the problem?.
I reinitialized it , but not no use, didn't work.
Thanks,
Subbu.
Hi,
A way to confirm in transactional replication that sql server is not
replicating a specific table of a publication (among other things) is to
verify that when a transaction is made at the publisher the corresponding
store procedure is executed at the subscriber, that is:
spMS_upd_yourtablename
spMS_ins_yourtablename
spMS_del_yourtablename
You can modify this stored procedures (after a backup, to be able to reverse
the procedure after troubleshooting) to include some code that will led you
to determine if the present stored procedure is being executed and if the
intended result is achieved.
If you come to the conclusion that sql is not replicating the table, it’s
advisable that you apply the latest service pack (all severs) and re create
de publications and subscriptions.
Regards,
Rafael Meza
"subbu" escribió:

> Hi,
> I have very simple one way transactional replication. There are multiple
> publications. Every publication has multiple articles. All data in tables
> have been published without a filter condition. Recently i have been getting
> compalints about data sync. Changes appliad at publisher are not being
> propated to subscriber. I verified agents log reader and distributor are
> running fine, no error msgs. I manually updated one table in a publication
> where i ususally encounter problems, but the change have not been propagated
> to subsciber. It is working fine for other table updates. All publications
> are from same database. and they go to same destination db.
> Can someone pass me, how to troubleshoot to find out where is the problem?.
> I reinitialized it , but not no use, didn't work.
> Thanks,
> Subbu.
>
>
|||Hi,
Thanks for the response.
As per my understanding, logreader will make entry into repl_commands table
in distributor db as soon as there is a change on published data. Then
distributor will pickup the cmds from this table to apply on subscribers.
From the moment i made some changes to the published table, i watched
repl_commands table in distributor to see the transaction, it never got into
the (repl_commands) table.
My question is why logreader agent is not picking up the changes i made.
Is there a way to troubleshoot or reset secondary log truncation mark
without droping and recreating replication.
Thanks,
Subbu.
whether they have been read by logreader agent. there are no entries
"rafameza" <rafameza@.discussions.microsoft.com> wrote in message
news:30218EEC-EB8E-4F22-AF9C-766DC1E4348D@.microsoft.com...
> Hi,
> A way to confirm in transactional replication that sql server is not
> replicating a specific table of a publication (among other things) is to
> verify that when a transaction is made at the publisher the corresponding
> store procedure is executed at the subscriber, that is:
> spMS_upd_yourtablename
> spMS_ins_yourtablename
> spMS_del_yourtablename
> You can modify this stored procedures (after a backup, to be able to
reverse
> the procedure after troubleshooting) to include some code that will led
you
> to determine if the present stored procedure is being executed and if the
> intended result is achieved.
> If you come to the conclusion that sql is not replicating the table, it's
> advisable that you apply the latest service pack (all severs) and re
create[vbcol=seagreen]
> de publications and subscriptions.
> Regards,
> Rafael Meza
> "subbu" escribi:
tables[vbcol=seagreen]
getting[vbcol=seagreen]
publication[vbcol=seagreen]
propagated[vbcol=seagreen]
publications[vbcol=seagreen]
problem?.[vbcol=seagreen]

replication issue

need to set up replication with a distributor - does anyone know a good
article on how to do this ?
when i publish the articles, how do I like the subscriber to the publisher
when there is a distributor in the middle... do I point to the distributor ?John
Look at these articles
http://www.swynk.com/friends/achigrik/SetupMR.asp -- Setting Up Merge
Replication: A Step by Step Guide
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"John Smith III" <jsmithIII@.yahoo.com> wrote in message
news:eO7B2eVuDHA.3224@.tk2msftngp13.phx.gbl...
> need to set up replication with a distributor - does anyone know a good
> article on how to do this ?
> when i publish the articles, how do I like the subscriber to the publisher
> when there is a distributor in the middle... do I point to the distributor
?
>

Replication Identity Ranges

Hello,
We have set up Merge replication, using sql 2000. We have one publisher and
four subscribers. When setting up the articles on the publisher, we manually
set an identity range for one of the tables. The range size at the publisher
and the subscriber is set to 5000, and the assign new range is set to 95%.
When looking at the design of the table and ckecking the replication
constraint, on the first subscriber, the range is 103000 - 108000. The next
subscriber has a range of 105000 - 111000. All the subscribers seem to have
a range that overlap with each other. The table has an identity column and
it is set to Not for Replication.
It is my understanding that each subscriber should have a range that do not
overlap. I have checked other tables on the subscribers and they each have
ranges that do not overlap.
I hope I have given enough information, and I hope someone could shed some
light on this.
Thanks in advance
Identity ranges should never overlap.
Did you use the same snapshot for each subscriber?
|||Hello,
we used the same snapshot for each subscriber. This problem has now been
corrected. We manually modified the MSrepl_identity_range table on each
subscriber, then forced replication. This fixed the problem.
"Jim Breffni" wrote:

> Identity ranges should never overlap.
> Did you use the same snapshot for each subscriber?
>