Managed Identity Ranges in Merge Replication

Hi,
Over the weekend I was taking advantage of system down time and made some
changes to my merge replication database.
The changes were all successful until this morning I get users who are
getting duplicate key error messages. I have verified that the duplicate
key is causing the error.
The remote locations have all been assigned there identity ranges, but it
appears that some cross-over has occurred from the previous ranges and
values. I thought SQL would look in the range assigned for the next
available number and use that. However, it appears that each subscriber is
using the next incremental number within their respective range. That is
the problem and I don't know what to do next.....Suggestions?
WB
What changes did you make to your merge replication database?
"WB" <none> wrote in message news:%23h30yd8MFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Over the weekend I was taking advantage of system down time and made some
> changes to my merge replication database.
> The changes were all successful until this morning I get users who are
> getting duplicate key error messages. I have verified that the duplicate
> key is causing the error.
> The remote locations have all been assigned there identity ranges, but it
> appears that some cross-over has occurred from the previous ranges and
> values. I thought SQL would look in the range assigned for the next
> available number and use that. However, it appears that each subscriber
is
> using the next incremental number within their respective range. That is
> the problem and I don't know what to do next.....Suggestions?
> WB
>
|||I added a new column to one table and changed the PK on the same table.
other changes included changing the field length on a few columns and
creating a new table
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:e9YK$79MFHA.2680@.TK2MSFTNGP09.phx.gbl...
> What changes did you make to your merge replication database?
>
> "WB" <none> wrote in message
news:%23h30yd8MFHA.2136@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
some[vbcol=seagreen]
duplicate[vbcol=seagreen]
it[vbcol=seagreen]
> is
is
>
sql

manage identity field at subscriber

Anyone has the standard approach for handling the identity column with replication environment.
Currently i am using following process.

http://www.databasejournal.com/features/mssql/article.php/3483421

I want to know if someone have some generic script to do this or much easier way..So your not using Auto_ID's ? & why not ?

Which Version of SQL you using ?

GW|||SQL Server 2000 SP3sql

manage Data replicated

I have two Db with Merge Transactional replication.
I would like to delete some data in Db replicated but maintain in the
principal DB.
what i can do, what tunig for this feature ?
thanks in advance
stper
maybe one solution is to create NOT a Merge replication but a Transactinal
replication ?
thanks
stper
"stper" <stefano.perabo@.speedautomazione.it> ha scritto nel messaggio
news:#mhABKBlEHA.3544@.TK2MSFTNGP15.phx.gbl...
> I have two Db with Merge Transactional replication.
> I would like to delete some data in Db replicated but maintain in the
> principal DB.
> what i can do, what tunig for this feature ?
> thanks in advance
> stper
>

manage Data replicated

I have two Db with Merge Transactional replication.
I would like to delete some data in Db replicated but maintain in the
principal DB.
what i can do, what tunig for this feature ?
thanks in advance
stpermaybe one solution is to create NOT a Merge replication but a Transactinal
replication ?
thanks
stper
"stper" <stefano.perabo@.speedautomazione.it> ha scritto nel messaggio
news:#mhABKBlEHA.3544@.TK2MSFTNGP15.phx.gbl...
> I have two Db with Merge Transactional replication.
> I would like to delete some data in Db replicated but maintain in the
> principal DB.
> what i can do, what tunig for this feature ?
> thanks in advance
> stper
>sql

Making schema changes

I have been reading up on how replicating schema changes work, but I must be missing something. When I configured a database for replication, it added a rowguid column to each table. This I understand.

I want to test making a schema change and replicating that change. For example, I tried to add a column to an existing table. When I tried to save the table i get the following error:

"Error validating the default for column rowguid".

"- Unable to modify table.
It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The DDL operation failed inside merge DDL replication manipulation.
The transaction ended in the trigger. The batch has been aborted."

So, what am I missing? Do i need to drop the rowguid column, add the new column, and then re-add the rowguid column?

Many thanks...

Well, it works if you do it via T-SQL. So, my question now is, what does the GUI (SSMS) do that T-SQL does not that let's T-SQL add a column to a table that is marked for replication?|||

It's documented in BOL that schema changes via SSMS is not supported. See BOL topic "Making Schema Changes on Publication Databases", http://msdn2.microsoft.com/en-us/library/ms151870.aspx.

|||

Thanks Greg. I figured out schema changes via SSMS was not supported, but I was wondering why. And I know that SSMS drops and re-creates the table for any changes. So my question was more out of curiosity than anything, wondering why SSMS does that and T-SQL doesn't.

Thanks again...

|||You should post that question in the Tools forum then |||

The current SSMS design is that when you alter a table, it will drop and re-create the table underneath. Now one cannot drop a table when it is replicated. Because of this reason when SSMS is trying to drop the constraint/table, it throws up this error.

To workaround you can continue to make changes in TSQL.

Making replication more reliable

We have about 90 sites doing merge replication with pull subscriptions over
a DSL VPN.
Replication often stalls following a dropped connection. Normally,
restarting SQL Server Agent at the subsciber gets things moving again.
Any good suggestions on a way to a perform this restart. We can't really do
it from within our user application as they don't have sufficient privilege.
Any easy way to do it from the publisher site?
Also are we making things worse for ourselves by running the merge agent
continuously?
Tony Toker
Data Identic Ltd.
either schedule your agents to run every 10 minutes, or set up your job so
that on job failure it wraps around and starts job step 1 again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tony Toker" <xyzzy@.identic.co.uk> wrote in message
news:cgi1vo$p1d$1$830fa795@.news.demon.co.uk...
> We have about 90 sites doing merge replication with pull subscriptions
over
> a DSL VPN.
> Replication often stalls following a dropped connection. Normally,
> restarting SQL Server Agent at the subsciber gets things moving again.
> Any good suggestions on a way to a perform this restart. We can't really
do
> it from within our user application as they don't have sufficient
privilege.
> Any easy way to do it from the publisher site?
> Also are we making things worse for ourselves by running the merge agent
> continuously?
> Tony Toker
> Data Identic Ltd.
>
|||Thanks for the tip.
Some of the agents don't actually fail, but run indefinitely.
I'll look at checking connectivity then running replmerge, or the activex
controls (which aren't currently deployed) from our application to synch
when necessary.
This will be a stupid question whatever the answer but would replmerge work
with a push subscription, ie can you initiate synchronization of a push
subscription at the subscriber?
Thanks for all your help on here, you need to get that book out and start
earning for your advice!
Tony
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uZzHfRqiEHA.3428@.TK2MSFTNGP11.phx.gbl...
> either schedule your agents to run every 10 minutes, or set up your job so
> that on job failure it wraps around and starts job step 1 again.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Tony Toker" <xyzzy@.identic.co.uk> wrote in message
> news:cgi1vo$p1d$1$830fa795@.news.demon.co.uk...
> over
> do
> privilege.
>

Making Identity to be Not for replication

Hello there
I have many tables on my database (Approx 500)
Many of them have identity column and none of them are set for Not for
replication
Is there a way to change it by code automaticly?
try this
http://groups.google.com/group/micro...5?dmode=source
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:e5gD6%23vAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I have many tables on my database (Approx 500)
> Many of them have identity column and none of them are set for Not for
> replication
> Is there a way to change it by code automaticly?
>

Making changes to a DB with Replication

Hi,
What is the best method/model for having real time replication set up and also needing the ability to drop and recreate SP's/Tables/UDF's etc? I keep getting the error of the SP is being used in replication so it cannot be dropped...
Thanks in advance.
AdamThe best method/model is not to introduce structural changes to replicated articles. The fact that you have a need for it tells me that the app design is not thought through, and needs to be changed.

Maintenance required after deploying merge pull replication?

Hi,
Before I ask for input, I just want to thank everyone in this
newsgroup for being so helpful... I've really gotten through some
complicated issues thanks to the information here. People like Hilary
are always coming through.
I am deploying a solution that depends on Continuous Merge Pull
replication... I can configure the systems of my customers, but once I
deploy them, I won't have direct administrative access to their copy
of MSDE which contains a pull subscription to our central database.
If needed, I could write an application to interface with MSDE through
the ActiveX controls...
What sort of control am I going to need so that I can always be sure
they are in sync? How can I make sure that if the connection is
dropped, or a new article is added, that they automatically keep
retrying until the process works?
In general, what are the problems do you think I might have after
deploying this sort of solution? Is it even workable without being
able to remote administer?
Thanks again
josh
Josh,
the agent failure/retry/success alerts should take care of some of these
concerns, provided you can configure email integration in your MSDEs.
To ensure the data is correctly transferred, occasional validation could be
used.
To ensure that the merge agent keeps retrying in the event of a network
failure, you can force it to work in an infinite loop (step 3 of the merge
agent's job step can have 'on success' and 'on failure' going back to step
2).
HTH,
Paul Ibison
|||So with an infinite loop trying to sync, will I also be covered for
any of the issues that normally cause the agent to stop?
For instance, replicating schema changes and new articles typically
requires a new snapshot, so the merge agent just stops itself (even in
continuous mode) until an up-to-date snapshot is posted. Will the
loop allow it to keep checking this?
Basically, I have no administrative access over the MSDE once it is
deployed (since it is on dynamic IP), and need to be sure there is
nothing that can cause sync to fail.
I suppose if the MSDE is on dynamic IP, there is no good way to
configure after deployment. Perhaps using replication is not the best
choice in this scenario. ..
Any input appreciated.
Thanks again,
josh
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<eTsOlvAYEHA.3476@.tk2msftngp13.phx.gbl>...
> Josh,
> the agent failure/retry/success alerts should take care of some of these
> concerns, provided you can configure email integration in your MSDEs.
> To ensure the data is correctly transferred, occasional validation could be
> used.
> To ensure that the merge agent keeps retrying in the event of a network
> failure, you can force it to work in an infinite loop (step 3 of the merge
> agent's job step can have 'on success' and 'on failure' going back to step
> 2).
> HTH,
> Paul Ibison
|||Josh,
the loop will protect against network issues but not adding new articles.
When the article is added, you'll need to run the snapshot agent
immediately, to ensure the merge agent doesn't stop. You could have a step
in the snapshot agent (sp_start_job) which restarts the merge agent if
necessary.
Not to sure about the dynamic IP bit. The Netbios name is normally used
rather than the IP address to connect using Enterprise Manager. If you are
replicating to a subscriber on a non-trusted domain or over the internet
then I could see your problem.
The thought that you could no longer monitor the subscriber makes me
concerned, and in my opinion this will be a problem as time goes by. Perhaps
you could have the subscriber run a job on a schedule which posts its IP
address to the publisher, so you have the option of troubleshoting if
necessary.
Regards,
Paul Ibison