Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts

Wednesday, March 28, 2012

merge replication with pull subscriber(compress snapshot)

Hi
I am trying to implement merge publication with compress snapshot, copy it
to the subscriber,
create a pull subscriber with the alternate folder(where the snapshot was
copied to)
and then run the agent for synchronization (first time).
i get error that the table in the publication does not exist in the
subscriber database.
It seems that the agent ignores the snapshot and it tries to just merge the
changes between publisher and subscriber.
when i create the table manually at the subscriber with the rowguid column,
it does not fail but it stills does not aplly the snapshot.
if i update/insert new row and run the agent it does merge the changes.
am i missing a crucial step in the process that causes that problem?
Thanks in advance
Daniel
daniel
Please can you post up the error message in its entirety. I suspect that it
is related to a script missing. Also, please try not using the CAB file
method. If you need to compress the snapshot, zip it up and unzip on the
subscriber (I only say this because I've never tested using an alternative
folder with a CAB file).
Paul Ibison
|||Hi and thank u for the replying.
When i start the agent for the first time i get the following error:
"Table 't3' does not exist in the Subscriber database."
At the log i can see that he does try to get the snapshot from alternate
folder:
" Snapshot will be applied from the alternate folder ..."
thanks in advance
Daniel
daniel
"Paul Ibison" wrote:

> Please can you post up the error message in its entirety. I suspect that it
> is related to a script missing. Also, please try not using the CAB file
> method. If you need to compress the snapshot, zip it up and unzip on the
> subscriber (I only say this because I've never tested using an alternative
> folder with a CAB file).
> Paul Ibison
|||Interesting - if you enable full logging is there more info? Also, are you
using the uncompressed snapshot files (if not please try this way also)? In
the snapshot files do you see the table creation script for t3?
(the alternative to this investigation if you like is to do a nosync
initialization).
Paul Ibison
|||Hi Paul
Yes there is a script for table creation.
I tried to use the uncompressed and i got the same error.
How do i do the no sync initialization?
daniel
"Paul Ibison" wrote:

> Interesting - if you enable full logging is there more info? Also, are you
> using the uncompressed snapshot files (if not please try this way also)? In
> the snapshot files do you see the table creation script for t3?
> (the alternative to this investigation if you like is to do a nosync
> initialization).
> Paul Ibison
|||Please have a look here. It largely applies to transactional, but mostly the
same info applies: http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison

merge replication with msmq

My company supports sales people with handheld devices
used to track and create orders. They are generally
offline and have a client-side copy of sql server on
their machines. Currently, we are using merge
replication and they log in each night to update our
corporate servers and download pertinent changes.
We will be moving to .NET and I have been looking into
MSMQ and most info seems to be stand-alone (ie not much
info on MSMQ in relation to Replication). My question
is: is it possible to integrate MSMQ with replication
and if so, how? What would the benefits/drawbacks be?
Also, not knowing much about replication, would MSMQ be a
better solution than replication in my situation?
thanks,
Greg
Greg,
you can use MSMQ for queued updating subscribers, as an alternative to a SQL
table (MSreplication_queue). I don't know of any performance/functionality
advantages in using MSMQ, and personally don't use it as it is another
potential point of failure - also MS discourage its use
(http://support.microsoft.com/default.aspx?kbid=830839).
As a general point, queued updating subscribers are recommended to be used
in preference to merge replication when:
Replicated data is mostly read-only at the Subscriber.
Conflicts caused by multiple updates to the same data are infrequent.
You need updates to be propagated on a transaction basis, and conflicts to
be evaluated and resolved on a transaction basis (the entire transaction is
either committed or rolled back).
HTH,
Paul Ibison
|||Queued Updating is good when your number of subscribers is less than 10.
Merge replication was designed from the ground up for mobile subscribers who frequently go off line. MSMQ was designed from the ground up to provide asynchronous messaging. Out of the box it doesn't really do bi-directional replication.
You can use MSMQ to provide a subset of the functionality or merge replication, but it really is the wrong tool for bi-directional asynchronous replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks To both of you for your replies.
As MSMQ provides asynchronous messaging, would it be the
appropriate solution in the following situation: the
sales force is online most of the time and must send in
orders. Also, they run our app and must receive updates
which are personalized for their application. For
example, our menus are dynamic and each rep's menu
structure is stored in our db; but on a rep's local db,
only their own menu structure is stored. Therefore, a
change to a particular rep's menu structure would only be
pushed down to that particular rep, not all reps. So
basically, 1 rep might be able to get to a particular
function by selecting it in their menu, but another rep
might not see that functionality at all.
thanks,
Greg

>--Original Message--
>Queued Updating is good when your number of subscribers
is less than 10.
>Merge replication was designed from the ground up for
mobile subscribers who frequently go off line. MSMQ was
designed from the ground up to provide asynchronous
messaging. Out of the box it doesn't really do bi-
directional replication.
>You can use MSMQ to provide a subset of the
functionality or merge replication, but it really is the
wrong tool for bi-directional asynchronous replication.
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>.
>
|||Greg,
I'd recommend merge replication as it performs this type of partitioning
well and is vastly simpler than involving MSMQ.
Basically, each member of the salesforce would have their own SQL Server
(MSDE?) installed against which your client application functions. Each of
these nodes exists as a subscriber to the central publisher. Data would be
partitioned according to salesperson based on the merge agent's -HOSTNAME
parameter.
HTH,
Paul Ibison
|||Paul,
Wouldn't this require reps to manually run the merge
replication process? (BTW, this is our implementation
now). As I understand, MSMQ works automatically, so
messages are sent and received real-time without any
explicit action by the user, as long as there is a
connection to the server and the server has resources to
process the sent message.
thanks,
Greg

>--Original Message--
>Greg,
>I'd recommend merge replication as it performs this type
of partitioning
>well and is vastly simpler than involving MSMQ.
>Basically, each member of the salesforce would have
their own SQL Server
>(MSDE?) installed against which your client application
functions. Each of
>these nodes exists as a subscriber to the central
publisher. Data would be
>partitioned according to salesperson based on the merge
agent's -HOSTNAME
>parameter.
>HTH,
>Paul Ibison
>
>.
>
|||Greg,
The merge agent can be initiated by them manually, by your program, using
windows synchronization manager or if the reps are always connected to the
network, you can have the merge agent running in continuous mode.
HTH,
Paul Ibison
|||Thanks for all the help Paul.
I guess my last question would be ... would MSMQ only be
preferrable if having a local db on a rep's machine was
impossible for some reason or if the messages/changes
were really only 1 way?
thanks!
Greg

>--Original Message--
>Greg,
>The merge agent can be initiated by them manually, by
your program, using
>windows synchronization manager or if the reps are
always connected to the
>network, you can have the merge agent running in
continuous mode.
>HTH,
>Paul Ibison
>
>.
>
|||Greg,
I agree, although MSDE is essentially freely distributable.
If message changes are only one way, merge can be configured this way (using
the -EXCHANGETYPE parameter).
Cheers,
Paul Ibison

Saturday, February 25, 2012

Merge replication conflicts on Identity Columns

Hi all,
I've set up a merge replication using sql server 2005.
All settings for "Copy Use Triggers" were set to false
All Identity ranges were set to be handled automatically.
There is a table - "Stock" - which has an ident field. An insert
trigger on this table inserts a new record into another table -
"barcodes" - which itself has its own ident field.
When a new "stock" record is inserted on the subscriber I get a merge
conflict which says:
"A row insert at 'xxx.YYYYYYYYY' could not be propagated to
'zzz.YYYYYYYYY'. This failure can be caused by a constraint violation.
Explicit value must be specified for identity column in table
'barcodes' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity
column."
It suggested I use sp_adjustpublisheridentityrange, which I did on
both tables, but still the conflict won't resolve.
Any suggestions will be most gratefully received
Thanks
Ken
This should resolve itself the next time the merge agent runs. It is
possibly that you have entered so much data on the subscriber that you
identity range is completely out of whack.
Issue a dbcc checkident(''tablename') on your subscriber and note the value
for the current identity value and current column value. If the current
identity value is greater than your current column value set the current
identity value to be the current column value, i.e.
dbcc checkident(''tablename',reseed, 14)
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
<kengillett@.gmail.com> wrote in message
news:1184849805.324095.134260@.w3g2000hsg.googlegro ups.com...
> Hi all,
> I've set up a merge replication using sql server 2005.
> All settings for "Copy Use Triggers" were set to false
> All Identity ranges were set to be handled automatically.
> There is a table - "Stock" - which has an ident field. An insert
> trigger on this table inserts a new record into another table -
> "barcodes" - which itself has its own ident field.
> When a new "stock" record is inserted on the subscriber I get a merge
> conflict which says:
> "A row insert at 'xxx.YYYYYYYYY' could not be propagated to
> 'zzz.YYYYYYYYY'. This failure can be caused by a constraint violation.
> Explicit value must be specified for identity column in table
> 'barcodes' either when IDENTITY_INSERT is set to ON or when a
> replication user is inserting into a NOT FOR REPLICATION identity
> column."
> It suggested I use sp_adjustpublisheridentityrange, which I did on
> both tables, but still the conflict won't resolve.
> Any suggestions will be most gratefully received
> Thanks
> Ken
>