We have a 100+ GB database as our production db. We need to replicate to a
subscriber across a wan to have absolute minimal latency.
Originally this was configured to be a push subscription with the publisher
acting as the distributer. The distribution agent ran on the publisher as
well.
We have been having problems all over the place, and I want to recommend to
my boss to start anew with a different configuration. After reading some
parts of Hiliary's book and many posts, this is what I "think" would be the
best. Please let me know if this sounds right. Our biggest problems are a
slower production server, many locks, and the dist cleanup agent not being
able to keep up when run nightly, but when run continuously locking with the
other agents.
Proposed plan:
drop the current subscription (which has to be droped anyways because it
needs to be manually reinitialized.)
remove the production DB as a publisher and drop the distribution database.
use sqllite to create a backup of 6 files that are compressed and push the
files over the wan (also have an IT person send the files express mail as a
backup)
restore backup to db that will be subscriber
continue to let the production db make hourly transaction log backups, and
pull these over the wire to be applied as well to make up for the lag time
of pulling main backup over the wire.
restore transaction log backups
set up production db as publisher AND if approved use a remote distributer
set up subscriber as pull subscription (if we can get a remote distributer,
than do I need the distribution agent to run on the subscriber? What about
if we can't get a remote distributer?)
I need the data on the subscriber to be as up to date as physically
possible. A few seconds is the goal.
Please let me know if you need anymore info and if you think this would be a
better/optimal setup.
Thanks,
Kristy
Also, what type of machine would be needed for a remote distributor?
Questions from management:
What kind of machine does it need to be? Can it be a single processor
machine and how much memory and hard disk space would be required?
Could we run it on a subscriber machine?
--Kristy
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:O9le4avLFHA.568@.TK2MSFTNGP09.phx.gbl...
> We have a 100+ GB database as our production db. We need to replicate to a
> subscriber across a wan to have absolute minimal latency.
> Originally this was configured to be a push subscription with the
publisher
> acting as the distributer. The distribution agent ran on the publisher as
> well.
> We have been having problems all over the place, and I want to recommend
to
> my boss to start anew with a different configuration. After reading some
> parts of Hiliary's book and many posts, this is what I "think" would be
the
> best. Please let me know if this sounds right. Our biggest problems are a
> slower production server, many locks, and the dist cleanup agent not being
> able to keep up when run nightly, but when run continuously locking with
the
> other agents.
> Proposed plan:
> drop the current subscription (which has to be droped anyways because it
> needs to be manually reinitialized.)
> remove the production DB as a publisher and drop the distribution
database.
> use sqllite to create a backup of 6 files that are compressed and push the
> files over the wan (also have an IT person send the files express mail as
a
> backup)
> restore backup to db that will be subscriber
> continue to let the production db make hourly transaction log backups, and
> pull these over the wire to be applied as well to make up for the lag time
> of pulling main backup over the wire.
> restore transaction log backups
> set up production db as publisher AND if approved use a remote distributer
> set up subscriber as pull subscription (if we can get a remote
distributer,
> than do I need the distribution agent to run on the subscriber? What about
> if we can't get a remote distributer?)
>
> I need the data on the subscriber to be as up to date as physically
> possible. A few seconds is the goal.
> Please let me know if you need anymore info and if you think this would be
a
> better/optimal setup.
> Thanks,
> Kristy
>
|||Be careful about placement of your remote distributor. You want it as close
physically to your publisher as possible. You want it clustered for maximum
uptime.
If you put your distributor on your subscriber across your WAN you will have
log reader agent errors galore if you have a wan congestion or if your link
goes down. When this happens your tlog will balloon and then you will have
problems getting your log reader agent started again.
Then what you really need to do is find a period of quiescence where there
is no activity on your publisher and then do your backup and do a no sync
subscription, but stop your distribution agent. BTW - I would probably use a
pull to offload your distribution agent to your subscriber.
Then I'd set my PollingInterval on both my Log Reader and Distribution
agents to 1. Replicate the execution of stored procedures wherever possible,
use the independent agent option, and group your articles into separate
publications according to DRI.
However - for this type of a scenario - you might want to look at hardware
data mirroring. This will offload processing to hardware, and latencies can
be much smaller. The problem with them is that they can be very expensive,
and your entire array is replicated - so you can't do any business logic or
filtering.
If the cost of latency is significant to you, you have to use a solution
like this.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:O9le4avLFHA.568@.TK2MSFTNGP09.phx.gbl...
> We have a 100+ GB database as our production db. We need to replicate to a
> subscriber across a wan to have absolute minimal latency.
> Originally this was configured to be a push subscription with the
publisher
> acting as the distributer. The distribution agent ran on the publisher as
> well.
> We have been having problems all over the place, and I want to recommend
to
> my boss to start anew with a different configuration. After reading some
> parts of Hiliary's book and many posts, this is what I "think" would be
the
> best. Please let me know if this sounds right. Our biggest problems are a
> slower production server, many locks, and the dist cleanup agent not being
> able to keep up when run nightly, but when run continuously locking with
the
> other agents.
> Proposed plan:
> drop the current subscription (which has to be droped anyways because it
> needs to be manually reinitialized.)
> remove the production DB as a publisher and drop the distribution
database.
> use sqllite to create a backup of 6 files that are compressed and push the
> files over the wan (also have an IT person send the files express mail as
a
> backup)
> restore backup to db that will be subscriber
> continue to let the production db make hourly transaction log backups, and
> pull these over the wire to be applied as well to make up for the lag time
> of pulling main backup over the wire.
> restore transaction log backups
> set up production db as publisher AND if approved use a remote distributer
> set up subscriber as pull subscription (if we can get a remote
distributer,
> than do I need the distribution agent to run on the subscriber? What about
> if we can't get a remote distributer?)
>
> I need the data on the subscriber to be as up to date as physically
> possible. A few seconds is the goal.
> Please let me know if you need anymore info and if you think this would be
a
> better/optimal setup.
> Thanks,
> Kristy
>
|||It should be a dual processor at least. I can't give you answers on memory
or hard disk space, basically as you haven't given me enough information,
and even if you did, I doubt I would be able to give you a useful spec. I
would pick a top of the line machine, 4G of Ram, Xeon processors. No idea of
the hard drive size, as I have no idea how much data you are pushing daily.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:upkqxwvLFHA.2604@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Also, what type of machine would be needed for a remote distributor?
> Questions from management:
> What kind of machine does it need to be? Can it be a single processor
> machine and how much memory and hard disk space would be required?
> Could we run it on a subscriber machine?
> --Kristy
>
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:O9le4avLFHA.568@.TK2MSFTNGP09.phx.gbl...
a[vbcol=seagreen]
> publisher
as[vbcol=seagreen]
> to
> the
a[vbcol=seagreen]
being[vbcol=seagreen]
> the
> database.
the[vbcol=seagreen]
as[vbcol=seagreen]
> a
and[vbcol=seagreen]
time[vbcol=seagreen]
distributer[vbcol=seagreen]
> distributer,
about[vbcol=seagreen]
be
> a
>
|||Thanks Hilary.
The remote distributor that is available has multiple disks C: 80GB, E:250GB
and F:80GB single processor. It also has 1.5GB RAM. This sounds like it is
not enough RAM. I'll see if they will add some. Won't be able to get a dual
processor though.
Are you saying that the distributor needs to be clustered? Meaning another
machine. Not sure if that one will fly.Maybe in the future, but I am pushing
it to get a remote server in the first place.
Between the time that I do the backup and am able to restore locally does
the distribution agent need to be stopped? What will the publisher still be
able to replicate all of the commands that have happened in the interum?
There is about a 36 hour window before I can get the data to our local
server and I can not have the production server down that long. I'd rather
have a build up of commands that will take a few days to catch up.
You wrote:
> use the independent agent option, and group your articles into separate
> publications according to DRI.
Our app doesn't use stored procs (heavy sigh) because they want the app to
be DB independant. (I'm trying to convince otherwise). Should there be
multiple publications from the DB with articles grouped by DRI for the same
DB going to multiple subscriptions to the same DB? I'm really confused on
that one.
THanks so much for your help!
--Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ODsIAd7LFHA.2704@.TK2MSFTNGP15.phx.gbl...
> Be careful about placement of your remote distributor. You want it as
close
> physically to your publisher as possible. You want it clustered for
maximum
> uptime.
> If you put your distributor on your subscriber across your WAN you will
have
> log reader agent errors galore if you have a wan congestion or if your
link
> goes down. When this happens your tlog will balloon and then you will have
> problems getting your log reader agent started again.
> Then what you really need to do is find a period of quiescence where there
> is no activity on your publisher and then do your backup and do a no sync
> subscription, but stop your distribution agent. BTW - I would probably use
a
> pull to offload your distribution agent to your subscriber.
> Then I'd set my PollingInterval on both my Log Reader and Distribution
> agents to 1. Replicate the execution of stored procedures wherever
possible,
> use the independent agent option, and group your articles into separate
> publications according to DRI.
> However - for this type of a scenario - you might want to look at hardware
> data mirroring. This will offload processing to hardware, and latencies
can
> be much smaller. The problem with them is that they can be very expensive,
> and your entire array is replicated - so you can't do any business logic
or[vbcol=seagreen]
> filtering.
> If the cost of latency is significant to you, you have to use a solution
> like this.
> --
> 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
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:O9le4avLFHA.568@.TK2MSFTNGP09.phx.gbl...
a[vbcol=seagreen]
> publisher
as[vbcol=seagreen]
> to
> the
a[vbcol=seagreen]
being[vbcol=seagreen]
> the
> database.
the[vbcol=seagreen]
as[vbcol=seagreen]
> a
and[vbcol=seagreen]
time[vbcol=seagreen]
distributer[vbcol=seagreen]
> distributer,
about[vbcol=seagreen]
be
> a
>
|||I was just throwing the RAM figure out there. You might be able to get by
with 1.5, basically you have to monitor paging to see if you have memory
pressures/problems. Its really a function of throughput.
Your problem is going to be what happens when your remote distributor goes
down. This will cause your tlogs on your publisher to balloon. Ideally you
will need to cluster this machine. If you don't you will have a single point
of failure which will make your system vulnerable. I would probably look at
having a local distributor in this case. However depending on throughput
this could cause locking problems. You don't need a cluster, but a cluster
will help.
The distribution agent will have to be stopped until you get your subscriber
ready.
Regarding your question on multiple publications and multiple subscribers -
the answer is yes - for each publication you will need a subscription. If
you have 4 publications, you will need 4 subscriptions to the same
subscription database on the same subscriber.
It sounds like your manager is being unrealistic - he/she wants low latency
but is not willing to pay for it. Either the cost of latency is very high
for you and you should purchase serious hardware, or the cost of latency is
low, and you will have to live with low cost solutions.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:eGkZTr9LFHA.508@.TK2MSFTNGP12.phx.gbl...
> Thanks Hilary.
> The remote distributor that is available has multiple disks C: 80GB,
E:250GB
> and F:80GB single processor. It also has 1.5GB RAM. This sounds like it
is
> not enough RAM. I'll see if they will add some. Won't be able to get a
dual
> processor though.
> Are you saying that the distributor needs to be clustered? Meaning another
> machine. Not sure if that one will fly.Maybe in the future, but I am
pushing
> it to get a remote server in the first place.
> Between the time that I do the backup and am able to restore locally does
> the distribution agent need to be stopped? What will the publisher still
be
> able to replicate all of the commands that have happened in the interum?
> There is about a 36 hour window before I can get the data to our local
> server and I can not have the production server down that long. I'd rather
> have a build up of commands that will take a few days to catch up.
> You wrote:
> Our app doesn't use stored procs (heavy sigh) because they want the app to
> be DB independant. (I'm trying to convince otherwise). Should there be
> multiple publications from the DB with articles grouped by DRI for the
same[vbcol=seagreen]
> DB going to multiple subscriptions to the same DB? I'm really confused on
> that one.
>
> THanks so much for your help!
> --Kristy
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ODsIAd7LFHA.2704@.TK2MSFTNGP15.phx.gbl...
> close
> maximum
> have
> link
have[vbcol=seagreen]
there[vbcol=seagreen]
sync[vbcol=seagreen]
use[vbcol=seagreen]
> a
> possible,
hardware[vbcol=seagreen]
> can
expensive,[vbcol=seagreen]
> or
to[vbcol=seagreen]
> a
> as
recommend[vbcol=seagreen]
some[vbcol=seagreen]
be[vbcol=seagreen]
are[vbcol=seagreen]
> a
> being
with[vbcol=seagreen]
it[vbcol=seagreen]
> the
> as
> and
> time
> distributer
> about
would
> be
>
|||Hilary:
A follow-up question...
As I understood it, if your connection from your distributor to your
subscriber goes down, it wouldn't be the transaction log of the publishing
database that balloons, but actually the Distribution database itself, since
this is where all the commands that need to be replicated are kept.
You can still perform normal log backups while that link is down, can't you?
Please correct me if my understanding is incorrect.
Thank You
"Hilary Cotter" wrote:
> I was just throwing the RAM figure out there. You might be able to get by
> with 1.5, basically you have to monitor paging to see if you have memory
> pressures/problems. Its really a function of throughput.
> Your problem is going to be what happens when your remote distributor goes
> down. This will cause your tlogs on your publisher to balloon. Ideally you
> will need to cluster this machine. If you don't you will have a single point
> of failure which will make your system vulnerable. I would probably look at
> having a local distributor in this case. However depending on throughput
> this could cause locking problems. You don't need a cluster, but a cluster
> will help.
> The distribution agent will have to be stopped until you get your subscriber
> ready.
> Regarding your question on multiple publications and multiple subscribers -
> the answer is yes - for each publication you will need a subscription. If
> you have 4 publications, you will need 4 subscriptions to the same
> subscription database on the same subscriber.
> It sounds like your manager is being unrealistic - he/she wants low latency
> but is not willing to pay for it. Either the cost of latency is very high
> for you and you should purchase serious hardware, or the cost of latency is
> low, and you will have to live with low cost solutions.
>
> --
> 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
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:eGkZTr9LFHA.508@.TK2MSFTNGP12.phx.gbl...
> E:250GB
> is
> dual
> pushing
> be
> same
> have
> there
> sync
> use
> hardware
> expensive,
> to
> recommend
> some
> be
> are
> with
> it
> would
>
>
|||Thanks so much Hilary!
We've had the transaction logs balloon before because of similar issues and
we have the space and some processes in place to accomadate if that were to
happen. We currently have had the distributor local and it has just caused
too many locking problems.
With the distribution agent stopped, will the transactions still be recorded
that need to be pushed to the subscriber? Is this the log agent?
I'm still confused on the multiple publication/multiple subscriber issue. I
have one 110 GB database that is our production database. The DB needs to be
replicated to one subsriber database. Should I make this into multiple
publications and multiple subscriptions (same DB different articles) for the
one on one databases?
Also, is a single processor machine for the distributor server going to be
enough? Currently approximately 500,000 commands (in the MSrepl_commands
table) are logged a day.
--k
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:evE7D89LFHA.3988@.tk2msftngp13.phx.gbl...
> I was just throwing the RAM figure out there. You might be able to get by
> with 1.5, basically you have to monitor paging to see if you have memory
> pressures/problems. Its really a function of throughput.
> Your problem is going to be what happens when your remote distributor goes
> down. This will cause your tlogs on your publisher to balloon. Ideally you
> will need to cluster this machine. If you don't you will have a single
point
> of failure which will make your system vulnerable. I would probably look
at
> having a local distributor in this case. However depending on throughput
> this could cause locking problems. You don't need a cluster, but a cluster
> will help.
> The distribution agent will have to be stopped until you get your
subscriber
> ready.
> Regarding your question on multiple publications and multiple
subscribers -
> the answer is yes - for each publication you will need a subscription. If
> you have 4 publications, you will need 4 subscriptions to the same
> subscription database on the same subscriber.
> It sounds like your manager is being unrealistic - he/she wants low
latency
> but is not willing to pay for it. Either the cost of latency is very high
> for you and you should purchase serious hardware, or the cost of latency
is[vbcol=seagreen]
> low, and you will have to live with low cost solutions.
>
> --
> 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
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:eGkZTr9LFHA.508@.TK2MSFTNGP12.phx.gbl...
> E:250GB
> is
> dual
another[vbcol=seagreen]
> pushing
does[vbcol=seagreen]
> be
rather[vbcol=seagreen]
separate[vbcol=seagreen]
to[vbcol=seagreen]
> same
on[vbcol=seagreen]
will[vbcol=seagreen]
> have
> there
> sync
> use
separate[vbcol=seagreen]
> hardware
latencies[vbcol=seagreen]
> expensive,
logic[vbcol=seagreen]
solution[vbcol=seagreen]
replicate[vbcol=seagreen]
> to
publisher[vbcol=seagreen]
> recommend
> some
> be
> are
> with
because[vbcol=seagreen]
> it
push[vbcol=seagreen]
mail[vbcol=seagreen]
backups,[vbcol=seagreen]
lag
> would
>
|||you are completely correct for a local distributor! However, if you have a
remote distributor, the log reader agent will not be marking the
transactions as replicated in the log, and hence your transaction log will
start to balloon.
You can perform normal log dumps, but then your log will continue to grow.
What happens is that when you do a tlog dump, your log is not truncated, or
shrunk in any way. What happens is the status column on the vlf's is set to
0, which means that particular vlf can be used again.
When your distributor comes up, your log reader agent will be able to read
the transactions in the log, write them to the distribution database, and
then when you dump your tlog your status column of all the vlf's will be set
to 0, and the vlf can be used again.
Its a subtlety, but one which can bite you if you are not careful.
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
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:D56FDDCD-2A49-4ED6-9011-ED37FD5759D7@.microsoft.com...
> Hilary:
> A follow-up question...
> As I understood it, if your connection from your distributor to your
> subscriber goes down, it wouldn't be the transaction log of the publishing
> database that balloons, but actually the Distribution database itself,
since
> this is where all the commands that need to be replicated are kept.
> You can still perform normal log backups while that link is down, can't
you?[vbcol=seagreen]
> Please correct me if my understanding is incorrect.
> Thank You
> "Hilary Cotter" wrote:
by[vbcol=seagreen]
goes[vbcol=seagreen]
you[vbcol=seagreen]
point[vbcol=seagreen]
at[vbcol=seagreen]
cluster[vbcol=seagreen]
subscriber[vbcol=seagreen]
subscribers -[vbcol=seagreen]
If[vbcol=seagreen]
latency[vbcol=seagreen]
high[vbcol=seagreen]
is[vbcol=seagreen]
it[vbcol=seagreen]
another[vbcol=seagreen]
does[vbcol=seagreen]
still[vbcol=seagreen]
interum?[vbcol=seagreen]
rather[vbcol=seagreen]
separate[vbcol=seagreen]
app to[vbcol=seagreen]
on[vbcol=seagreen]
as[vbcol=seagreen]
will[vbcol=seagreen]
your[vbcol=seagreen]
will[vbcol=seagreen]
probably[vbcol=seagreen]
Distribution[vbcol=seagreen]
separate[vbcol=seagreen]
latencies[vbcol=seagreen]
logic[vbcol=seagreen]
solution[vbcol=seagreen]
replicate[vbcol=seagreen]
publisher[vbcol=seagreen]
reading[vbcol=seagreen]
would[vbcol=seagreen]
problems[vbcol=seagreen]
not[vbcol=seagreen]
locking[vbcol=seagreen]
because[vbcol=seagreen]
push[vbcol=seagreen]
mail[vbcol=seagreen]
backups,[vbcol=seagreen]
lag[vbcol=seagreen]
What[vbcol=seagreen]
physically[vbcol=seagreen]
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Wednesday, March 28, 2012
HELP! Subscription obsolete and Data NOT sychronized!
We have 3 databases, one publisher and two subscriber.
Merge-Replication, constantly replicated.
Because of unknown circumstances, the replication process failed for
over 14 days, and the publication had a retention time of 14 days.
I wasn=B4t detected earlier, and now is the great question:
HOW CAN I MERGE MY 3 DATABASES TOGETHER?
Is there a possibility to tell the subscribion, that they are NOT
obsolete?
To create a new snapshot won't work, i get a lot of errors afterwards.
To remove the subscriptions and setup the subscriptions anew, will
procede a well-running merge-replication, but with different data.
What about turning back the system time for a few days?
I would be very glad if you have an idea.
Andreas, you could manually synchronize the data using datacompare from
Redgate then do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison schrieb:
> Andreas, you could manually synchronize the data using datacompare from
> Redgate then do a nosync initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
But SQL DataCompare can either copy from database A to B or from B to
A, but cannot merge, or am I wrong?
And I a dataset exists on site A, but not on B, does that mean, that
the dataset was new on A and should copy to B, or was the dataset
delete on B and should also be deleted on A?
|||there is no way in Merge, there is a way in transactional but its dangerous.
If you do reinitialize it will prompt you to upload changes from the
subscriber first which will send the subscribers changes to the publisher.
I think you need to investigate 1) what is causing the problem which makes
your subscriber go offline 2) what is causing these errors you get after
running the snapshot.
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
"Andreas Lauffer" <alweb01@.online.de> wrote in message
news:1128770684.895595.285420@.g44g2000cwa.googlegr oups.com...
We have 3 databases, one publisher and two subscriber.
Merge-Replication, constantly replicated.
Because of unknown circumstances, the replication process failed for
over 14 days, and the publication had a retention time of 14 days.
I wasnt detected earlier, and now is the great question:
HOW CAN I MERGE MY 3 DATABASES TOGETHER?
Is there a possibility to tell the subscribion, that they are NOT
obsolete?
To create a new snapshot won't work, i get a lot of errors afterwards.
To remove the subscriptions and setup the subscriptions anew, will
procede a well-running merge-replication, but with different data.
What about turning back the system time for a few days?
I would be very glad if you have an idea.
|||Andreas - you're quite right. To get this to work, you'll have to select
which one is the master in terms of updates to the same record on both
sites. If the updates are partitioned, then you can use this tool more
easily, but if there are a lot of conflicts then there is a big problem to
sort out, and only you can decide what is appropriate ie who wins these
conflicts, or in other words, which node is the master.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hillary,
thank you for your answer, but I don=B4t understand exacly what this
means for me.
I use merge-replication, not transactual replication.
What happens, if I reinitialize my databases? Will the subscribers
upload their data then?
What is so dangerous?
|||Unfortunately there is no master. Each of the 3 databases have the same
priority.
I cannot set anything aside.
What about my question:[vbcol=seagreen]
Do you think there is a chance to get a system which synchronizes the
data, without marking the subscibers as "obsolete"?
|||What do you think about this scenario:
1) Deleting the subscriptions
2) New pull Merge Subscriptions => Replication will run again, but with
different data
3) Restore the subscibers databases, state before deleting the
subscriptions
Do you think this will work to synchronize the databases?
If this works, maybe the changes from the publisher were not
transmittet to the subscriters yet, and I have to backup the subscribes
database and restore it as the subscribers database.
MAY THIS WORK?
|||Andreas,
this sounds feasible. You'll probably also need to change the value of the
status flag in sysmergesubscriptions. Alternatively (and probably easier)
you might be able to increase the activation/retention period and change
this flag. I haven't tried either approach and don't have any expired
subscriptions to test, so please let us know how you get on.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, with merge replication when you reinitialize you are prompted to upload
the changes which happened on your subscribers first.
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
"Andreas Lauffer" <alweb01@.online.de> wrote in message
news:1128924652.854174.261410@.g47g2000cwa.googlegr oups.com...
Hillary,
thank you for your answer, but I dont understand exacly what this
means for me.
I use merge-replication, not transactual replication.
What happens, if I reinitialize my databases? Will the subscribers
upload their data then?
What is so dangerous?
Merge-Replication, constantly replicated.
Because of unknown circumstances, the replication process failed for
over 14 days, and the publication had a retention time of 14 days.
I wasn=B4t detected earlier, and now is the great question:
HOW CAN I MERGE MY 3 DATABASES TOGETHER?
Is there a possibility to tell the subscribion, that they are NOT
obsolete?
To create a new snapshot won't work, i get a lot of errors afterwards.
To remove the subscriptions and setup the subscriptions anew, will
procede a well-running merge-replication, but with different data.
What about turning back the system time for a few days?
I would be very glad if you have an idea.
Andreas, you could manually synchronize the data using datacompare from
Redgate then do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison schrieb:
> Andreas, you could manually synchronize the data using datacompare from
> Redgate then do a nosync initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
But SQL DataCompare can either copy from database A to B or from B to
A, but cannot merge, or am I wrong?
And I a dataset exists on site A, but not on B, does that mean, that
the dataset was new on A and should copy to B, or was the dataset
delete on B and should also be deleted on A?
|||there is no way in Merge, there is a way in transactional but its dangerous.
If you do reinitialize it will prompt you to upload changes from the
subscriber first which will send the subscribers changes to the publisher.
I think you need to investigate 1) what is causing the problem which makes
your subscriber go offline 2) what is causing these errors you get after
running the snapshot.
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
"Andreas Lauffer" <alweb01@.online.de> wrote in message
news:1128770684.895595.285420@.g44g2000cwa.googlegr oups.com...
We have 3 databases, one publisher and two subscriber.
Merge-Replication, constantly replicated.
Because of unknown circumstances, the replication process failed for
over 14 days, and the publication had a retention time of 14 days.
I wasnt detected earlier, and now is the great question:
HOW CAN I MERGE MY 3 DATABASES TOGETHER?
Is there a possibility to tell the subscribion, that they are NOT
obsolete?
To create a new snapshot won't work, i get a lot of errors afterwards.
To remove the subscriptions and setup the subscriptions anew, will
procede a well-running merge-replication, but with different data.
What about turning back the system time for a few days?
I would be very glad if you have an idea.
|||Andreas - you're quite right. To get this to work, you'll have to select
which one is the master in terms of updates to the same record on both
sites. If the updates are partitioned, then you can use this tool more
easily, but if there are a lot of conflicts then there is a big problem to
sort out, and only you can decide what is appropriate ie who wins these
conflicts, or in other words, which node is the master.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hillary,
thank you for your answer, but I don=B4t understand exacly what this
means for me.
I use merge-replication, not transactual replication.
What happens, if I reinitialize my databases? Will the subscribers
upload their data then?
What is so dangerous?
|||Unfortunately there is no master. Each of the 3 databases have the same
priority.
I cannot set anything aside.
What about my question:[vbcol=seagreen]
Do you think there is a chance to get a system which synchronizes the
data, without marking the subscibers as "obsolete"?
|||What do you think about this scenario:
1) Deleting the subscriptions
2) New pull Merge Subscriptions => Replication will run again, but with
different data
3) Restore the subscibers databases, state before deleting the
subscriptions
Do you think this will work to synchronize the databases?
If this works, maybe the changes from the publisher were not
transmittet to the subscriters yet, and I have to backup the subscribes
database and restore it as the subscribers database.
MAY THIS WORK?
|||Andreas,
this sounds feasible. You'll probably also need to change the value of the
status flag in sysmergesubscriptions. Alternatively (and probably easier)
you might be able to increase the activation/retention period and change
this flag. I haven't tried either approach and don't have any expired
subscriptions to test, so please let us know how you get on.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, with merge replication when you reinitialize you are prompted to upload
the changes which happened on your subscribers first.
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
"Andreas Lauffer" <alweb01@.online.de> wrote in message
news:1128924652.854174.261410@.g47g2000cwa.googlegr oups.com...
Hillary,
thank you for your answer, but I dont understand exacly what this
means for me.
I use merge-replication, not transactual replication.
What happens, if I reinitialize my databases? Will the subscribers
upload their data then?
What is so dangerous?
Friday, March 23, 2012
Help! Replication jobs fails when it was working fine.
Hello,
I have a push subscription setup to replicate some tables. Initially, this
was setup properly and everything was working perfectly. Until recently, the
jobs started failing even though nothing was changed. The errors do not give
me enough information to find out what the problem is. Can someone give me
some hints or ideas of what to do?
FYI, I've re-generated snapshots and re-initialized the subscriptions but it
still fails.
FIRST ERROR
==============================================
Date4/21/2005 9:01:35 AM
LogJob History (XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)
Job NameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Step ID2
Step NameRun agent.
MessageExecuted as user: xxx\Administrator. The step did not generate any
output. NOTE: The step was retried the requested number of times (10)
without succeeding. The step failed.
Duration00:05:10
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted10
SECOND ERROR
==============================================
Date4/21/2005 9:06:46 AM
LogJob History (XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)
Job NameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Step ID3
Step NameDetect nonlogged agent shutdown.
MessageExecuted as user: NT AUTHORITY\SYSTEM. Replication-Replication
Distribution Subsystem: agent XXXXXXXX for XXXXXXXXXX failed. Executed as
user: Domain\Administrator. The step did not generate any output. NOTE: The
step was retried the requested number of times (10) without succeeding. The
step failed. [SQLSTATE 42000] (Error 14151). The step failed.
Duration00:00:00
Sql Severity18
Sql Message ID14151
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
its hard to say. Can you do logging to see if it reveals anything? also
look for dumps in case there were any access violations.
Follow these instructions to enable logging.
http://support.microsoft.com/default...b;en-us;312292
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
|||Also run profiler on the subscriber, check for blocking, or any exceptions
being thrown.
Donna
"Hilary Cotter" wrote:
> its hard to say. Can you do logging to see if it reveals anything? also
> look for dumps in case there were any access violations.
> Follow these instructions to enable logging.
> http://support.microsoft.com/default...b;en-us;312292
> --
> 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
>
I have a push subscription setup to replicate some tables. Initially, this
was setup properly and everything was working perfectly. Until recently, the
jobs started failing even though nothing was changed. The errors do not give
me enough information to find out what the problem is. Can someone give me
some hints or ideas of what to do?
FYI, I've re-generated snapshots and re-initialized the subscriptions but it
still fails.
FIRST ERROR
==============================================
Date4/21/2005 9:01:35 AM
LogJob History (XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)
Job NameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Step ID2
Step NameRun agent.
MessageExecuted as user: xxx\Administrator. The step did not generate any
output. NOTE: The step was retried the requested number of times (10)
without succeeding. The step failed.
Duration00:05:10
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted10
SECOND ERROR
==============================================
Date4/21/2005 9:06:46 AM
LogJob History (XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)
Job NameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Step ID3
Step NameDetect nonlogged agent shutdown.
MessageExecuted as user: NT AUTHORITY\SYSTEM. Replication-Replication
Distribution Subsystem: agent XXXXXXXX for XXXXXXXXXX failed. Executed as
user: Domain\Administrator. The step did not generate any output. NOTE: The
step was retried the requested number of times (10) without succeeding. The
step failed. [SQLSTATE 42000] (Error 14151). The step failed.
Duration00:00:00
Sql Severity18
Sql Message ID14151
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
its hard to say. Can you do logging to see if it reveals anything? also
look for dumps in case there were any access violations.
Follow these instructions to enable logging.
http://support.microsoft.com/default...b;en-us;312292
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
|||Also run profiler on the subscriber, check for blocking, or any exceptions
being thrown.
Donna
"Hilary Cotter" wrote:
> its hard to say. Can you do logging to see if it reveals anything? also
> look for dumps in case there were any access violations.
> Follow these instructions to enable logging.
> http://support.microsoft.com/default...b;en-us;312292
> --
> 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
>
HELP! Problems with Merge Replication
Hi,
I have created a publication on SQL Server 2005 and am using SQL CE RC1 to synchronize with the DB from the client. We did not have any problems until we implemented microsoft recommendations in the Mergereplication script. We changed the following
1.Changed column_tracking to true from false.
2. use_partition_groups = true from null
3. partition_options = 2 from 1
4. automatic_reinitialization_policy = 1 from 0
and 5. join_unique_key = 1 from 0( this change was made depending on the relationships within the tables)
I have created a publication on SQL Server 2005 and am using SQL CE RC1 to synchronize with the DB from the client. We did not have any problems until we implemented microsoft recommendations in the Mergereplication script. We changed the following
1.Changed column_tracking to true from false.
2. use_partition_groups = true from null
3. partition_options = 2 from 1
4. automatic_reinitialization_policy = 1 from 0
and 5. join_unique_key = 1 from 0( this change was made depending on the relationships within the tables)
I have a couple of questions before tho
1. Does replication take care of transactional integrity of the both the databases( i.e if the synchronization fails in between for any reason, does it rollback the server DB and maintain the transactional integrity. Is there a place where we need to enable this?
2. What happens when you change row level to column level tracking. I am seeing in the "view conflicts" section of the replication object that there are conflicts. Does SQL Server take care of resolving these issues. If yes, how do we provide the criteria where the subscriber wins or the publisher wins and why do I see conflicts in the "view conflicts" section .
Please do help. We are in time crunch situation and need to deliver this app into prod this friday.
Below are a couple of errors that we received during merge replication.
Error 1:
Error messages:
The row operation cannot be reapplied due to an integrity violation. Check the Publication filter. [,,,Table,Operation,RowGuid] (Source: MSSQLServer, Error number: 28549)
Get help: http://help/28549
Error 2:
Error messages:
· The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199363)
Get help: http://help/MSSQL_REPL-2147199363
The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up. (Source: MSSQLServer, Error number: 21800)
Get help: http://help/21800
Thanks a lot in advance,
Kals.
HELP! Problems with Merge Replication
Hi,
I have created a publication on SQL Server 2005 and am using SQL CE RC1 to synchronize with the DB from the client. We did not have any problems until we implemented microsoft recommendations in the Mergereplication script. We changed the following
1.Changed column_tracking to true from false.
2. use_partition_groups = true from null
3. partition_options = 2 from 1
4. automatic_reinitialization_policy = 1 from 0
and 5. join_unique_key = 1 from 0( this change was made depending on the relationships within the tables)
I have created a publication on SQL Server 2005 and am using SQL CE RC1 to synchronize with the DB from the client. We did not have any problems until we implemented microsoft recommendations in the Mergereplication script. We changed the following
1.Changed column_tracking to true from false.
2. use_partition_groups = true from null
3. partition_options = 2 from 1
4. automatic_reinitialization_policy = 1 from 0
and 5. join_unique_key = 1 from 0( this change was made depending on the relationships within the tables)
I have a couple of questions before tho
1. Does replication take care of transactional integrity of the both the databases( i.e if the synchronization fails in between for any reason, does it rollback the server DB and maintain the transactional integrity. Is there a place where we need to enable this?
2. What happens when you change row level to column level tracking. I am seeing in the "view conflicts" section of the replication object that there are conflicts. Does SQL Server take care of resolving these issues. If yes, how do we provide the criteria where the subscriber wins or the publisher wins and why do I see conflicts in the "view conflicts" section .
Please do help. We are in time crunch situation and need to deliver this app into prod this friday.
Below are a couple of errors that we received during merge replication.
Error 1:
Error messages:
The row operation cannot be reapplied due to an integrity violation. Check the Publication filter. [,,,Table,Operation,RowGuid] (Source: MSSQLServer, Error number: 28549)
Get help: http://help/28549
Error 2:
Error messages:
· The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199363)
Get help: http://help/MSSQL_REPL-2147199363
The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up. (Source: MSSQLServer, Error number: 21800)
Get help: http://help/21800
Thanks a lot in advance,
Kals.
Wednesday, March 21, 2012
Help! Merge Replication and identity values
Hi,
I have a Merge replication set up between a SQL Server and many (around
150-200) SQL CE databases. I am using identity as primary keys in 2 of the
replicated tables which have a identity seed (set to 1) and range set (set
to 1000 with 10000 as publisher range and 80% threshold). All was going well
until there was a database schema change and I had to rebuild the
publication and re-initialize all subscribers (which was ok).
But after rebuilding the publication and re-initialization all subscribers,
the agent started giving out identity ranges that conflicted with current
values in the database. As some of you have faced similar situation and have
found workarounds. I used a script to manually change the "next seed" value
of the MSrepl_identity_range table to set the "next seed" identity value to
be the max value of the table...basically used UPDATE
distribution..MSrepl_identity_range SET next_seed = max value + range...so
on and so forth. This worked as far as giving each re-initialized
subscribers a new identity range. I have 2 questions regarding this:
* How can I set the publishers range? I manually updated the
MSrepl_identity_range to a higher value than the max value used for updating
the distribution..MSrepl_identity_range using the sql script. What effects
would this have?
* In Check Constraint tab of the replicated table (generating identity
values at subscriber), there is a check constraint value (value is:
[Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
for Insert and Update" with constraint name like
"repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7 F8D53C0B20A". I had to
uncheck them for the merge agent to be able to add new rows to the server.
Where can I get more info on how this constraint value is generated and how
is it used? Also what is the harm leaving the enforcement of constraint
uncheck?
Can anyone point me to a website where someone has successfully dealt with
this issue without manually setting the ranges?
Please help.
Thanks.
wow! thats a lot of SQL CE databases.
1) to set the identity range on the publisher the correct way to do this is
through the articles property, select the identity range tab. Sounds like
you have already being there. After setting the range on the publisher the
ranges should be parceled out to the Subscribers. As Subscribers come online
they'll get a range assigned to them.
There are instances where the range won't be incremented correctly. For
instance if you have a range size on the publisher of 100 and you update
more than the threshold or range size on the publisher in a batch, the range
adjustment won't be done until the batch is complete. If the batch is more
than 100 records you blow the range and get a constraint error.
In cases like this you have to automatically adjust the identity ranges or
do it manually by adjusting the range table and the corresponding
constraint.
Because of these "limitations" many DBA's elect to use the set it and forget
it approach, where they assign a range to the publisher and subscriber
manually which will not be exceeded in the lifetime of the project/solution.
The dangers of manually making the adjustment is you have to use consistent
values everywhere and you have to adjust the constraint correctly. Other
than that its pretty safe.
The constraint is created when you create the snapshot and adjusted with the
proc sp_MSreseed. This proc is completely undocumented. If you disable the
constraint you may run into problems depending on what is updating your
table. Disableing it for inserts and updates will be harmless if only
replication is making the changes, otherwise you may have problems, if the
identity range is blown and another subscriber/publisher uses it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Deepak Ramakumar" <dramakumar@.strongtie.com> wrote in message
news:evu$7CnUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I have a Merge replication set up between a SQL Server and many (around
> 150-200) SQL CE databases. I am using identity as primary keys in 2 of the
> replicated tables which have a identity seed (set to 1) and range set (set
> to 1000 with 10000 as publisher range and 80% threshold). All was going
well
> until there was a database schema change and I had to rebuild the
> publication and re-initialize all subscribers (which was ok).
>
> But after rebuilding the publication and re-initialization all
subscribers,
> the agent started giving out identity ranges that conflicted with current
> values in the database. As some of you have faced similar situation and
have
> found workarounds. I used a script to manually change the "next seed"
value
> of the MSrepl_identity_range table to set the "next seed" identity value
to
> be the max value of the table...basically used UPDATE
> distribution..MSrepl_identity_range SET next_seed = max value + range...so
> on and so forth. This worked as far as giving each re-initialized
> subscribers a new identity range. I have 2 questions regarding this:
>
> * How can I set the publishers range? I manually updated the
> MSrepl_identity_range to a higher value than the max value used for
updating
> the distribution..MSrepl_identity_range using the sql script. What effects
> would this have?
> * In Check Constraint tab of the replicated table (generating identity
> values at subscriber), there is a check constraint value (value is:
> [Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
> for Insert and Update" with constraint name like
> "repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7 F8D53C0B20A". I had to
> uncheck them for the merge agent to be able to add new rows to the server.
> Where can I get more info on how this constraint value is generated and
how
> is it used? Also what is the harm leaving the enforcement of constraint
> uncheck?
>
> Can anyone point me to a website where someone has successfully dealt with
> this issue without manually setting the ranges?
>
> Please help.
> Thanks.
>
>
|||Hilary,
Thanks a lot for your response. Googling on sp_MSreseed gives only one result by Fiach Reid who details the stored procedure itself. I will not be doing any batch inserts so I guess sql should handle the identity ranges automatically, but if it doesn't then I will start seeing conflicts on the server again and may be by that time I could have gathered more info on sp_MSreseed.
Thanks,
Deepak.
Hilary Cotter" <hilaryk@.att.net> wrote in message news:u$aeWOoUEHA.3420@.TK2MSFTNGP12.phx.gbl...
> wow! thats a lot of SQL CE databases.
> 1) to set the identity range on the publisher the correct way to do this is
> through the articles property, select the identity range tab. Sounds like
> you have already being there. After setting the range on the publisher the
> ranges should be parceled out to the Subscribers. As Subscribers come online
> they'll get a range assigned to them.
> There are instances where the range won't be incremented correctly. For
> instance if you have a range size on the publisher of 100 and you update
> more than the threshold or range size on the publisher in a batch, the range
> adjustment won't be done until the batch is complete. If the batch is more
> than 100 records you blow the range and get a constraint error.
> In cases like this you have to automatically adjust the identity ranges or
> do it manually by adjusting the range table and the corresponding
> constraint.
> Because of these "limitations" many DBA's elect to use the set it and forget
> it approach, where they assign a range to the publisher and subscriber
> manually which will not be exceeded in the lifetime of the project/solution.
> The dangers of manually making the adjustment is you have to use consistent
> values everywhere and you have to adjust the constraint correctly. Other
> than that its pretty safe.
> The constraint is created when you create the snapshot and adjusted with the
> proc sp_MSreseed. This proc is completely undocumented. If you disable the
> constraint you may run into problems depending on what is updating your
> table. Disableing it for inserts and updates will be harmless if only
> replication is making the changes, otherwise you may have problems, if the
> identity range is blown and another subscriber/publisher uses it.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Deepak Ramakumar" <dramakumar@.strongtie.com> wrote in message
> news:evu$7CnUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> well
> subscribers,
> have
> value
> to
> updating
> how
>
I have a Merge replication set up between a SQL Server and many (around
150-200) SQL CE databases. I am using identity as primary keys in 2 of the
replicated tables which have a identity seed (set to 1) and range set (set
to 1000 with 10000 as publisher range and 80% threshold). All was going well
until there was a database schema change and I had to rebuild the
publication and re-initialize all subscribers (which was ok).
But after rebuilding the publication and re-initialization all subscribers,
the agent started giving out identity ranges that conflicted with current
values in the database. As some of you have faced similar situation and have
found workarounds. I used a script to manually change the "next seed" value
of the MSrepl_identity_range table to set the "next seed" identity value to
be the max value of the table...basically used UPDATE
distribution..MSrepl_identity_range SET next_seed = max value + range...so
on and so forth. This worked as far as giving each re-initialized
subscribers a new identity range. I have 2 questions regarding this:
* How can I set the publishers range? I manually updated the
MSrepl_identity_range to a higher value than the max value used for updating
the distribution..MSrepl_identity_range using the sql script. What effects
would this have?
* In Check Constraint tab of the replicated table (generating identity
values at subscriber), there is a check constraint value (value is:
[Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
for Insert and Update" with constraint name like
"repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7 F8D53C0B20A". I had to
uncheck them for the merge agent to be able to add new rows to the server.
Where can I get more info on how this constraint value is generated and how
is it used? Also what is the harm leaving the enforcement of constraint
uncheck?
Can anyone point me to a website where someone has successfully dealt with
this issue without manually setting the ranges?
Please help.
Thanks.
wow! thats a lot of SQL CE databases.
1) to set the identity range on the publisher the correct way to do this is
through the articles property, select the identity range tab. Sounds like
you have already being there. After setting the range on the publisher the
ranges should be parceled out to the Subscribers. As Subscribers come online
they'll get a range assigned to them.
There are instances where the range won't be incremented correctly. For
instance if you have a range size on the publisher of 100 and you update
more than the threshold or range size on the publisher in a batch, the range
adjustment won't be done until the batch is complete. If the batch is more
than 100 records you blow the range and get a constraint error.
In cases like this you have to automatically adjust the identity ranges or
do it manually by adjusting the range table and the corresponding
constraint.
Because of these "limitations" many DBA's elect to use the set it and forget
it approach, where they assign a range to the publisher and subscriber
manually which will not be exceeded in the lifetime of the project/solution.
The dangers of manually making the adjustment is you have to use consistent
values everywhere and you have to adjust the constraint correctly. Other
than that its pretty safe.
The constraint is created when you create the snapshot and adjusted with the
proc sp_MSreseed. This proc is completely undocumented. If you disable the
constraint you may run into problems depending on what is updating your
table. Disableing it for inserts and updates will be harmless if only
replication is making the changes, otherwise you may have problems, if the
identity range is blown and another subscriber/publisher uses it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Deepak Ramakumar" <dramakumar@.strongtie.com> wrote in message
news:evu$7CnUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I have a Merge replication set up between a SQL Server and many (around
> 150-200) SQL CE databases. I am using identity as primary keys in 2 of the
> replicated tables which have a identity seed (set to 1) and range set (set
> to 1000 with 10000 as publisher range and 80% threshold). All was going
well
> until there was a database schema change and I had to rebuild the
> publication and re-initialize all subscribers (which was ok).
>
> But after rebuilding the publication and re-initialization all
subscribers,
> the agent started giving out identity ranges that conflicted with current
> values in the database. As some of you have faced similar situation and
have
> found workarounds. I used a script to manually change the "next seed"
value
> of the MSrepl_identity_range table to set the "next seed" identity value
to
> be the max value of the table...basically used UPDATE
> distribution..MSrepl_identity_range SET next_seed = max value + range...so
> on and so forth. This worked as far as giving each re-initialized
> subscribers a new identity range. I have 2 questions regarding this:
>
> * How can I set the publishers range? I manually updated the
> MSrepl_identity_range to a higher value than the max value used for
updating
> the distribution..MSrepl_identity_range using the sql script. What effects
> would this have?
> * In Check Constraint tab of the replicated table (generating identity
> values at subscriber), there is a check constraint value (value is:
> [Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
> for Insert and Update" with constraint name like
> "repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7 F8D53C0B20A". I had to
> uncheck them for the merge agent to be able to add new rows to the server.
> Where can I get more info on how this constraint value is generated and
how
> is it used? Also what is the harm leaving the enforcement of constraint
> uncheck?
>
> Can anyone point me to a website where someone has successfully dealt with
> this issue without manually setting the ranges?
>
> Please help.
> Thanks.
>
>
|||Hilary,
Thanks a lot for your response. Googling on sp_MSreseed gives only one result by Fiach Reid who details the stored procedure itself. I will not be doing any batch inserts so I guess sql should handle the identity ranges automatically, but if it doesn't then I will start seeing conflicts on the server again and may be by that time I could have gathered more info on sp_MSreseed.
Thanks,
Deepak.
Hilary Cotter" <hilaryk@.att.net> wrote in message news:u$aeWOoUEHA.3420@.TK2MSFTNGP12.phx.gbl...
> wow! thats a lot of SQL CE databases.
> 1) to set the identity range on the publisher the correct way to do this is
> through the articles property, select the identity range tab. Sounds like
> you have already being there. After setting the range on the publisher the
> ranges should be parceled out to the Subscribers. As Subscribers come online
> they'll get a range assigned to them.
> There are instances where the range won't be incremented correctly. For
> instance if you have a range size on the publisher of 100 and you update
> more than the threshold or range size on the publisher in a batch, the range
> adjustment won't be done until the batch is complete. If the batch is more
> than 100 records you blow the range and get a constraint error.
> In cases like this you have to automatically adjust the identity ranges or
> do it manually by adjusting the range table and the corresponding
> constraint.
> Because of these "limitations" many DBA's elect to use the set it and forget
> it approach, where they assign a range to the publisher and subscriber
> manually which will not be exceeded in the lifetime of the project/solution.
> The dangers of manually making the adjustment is you have to use consistent
> values everywhere and you have to adjust the constraint correctly. Other
> than that its pretty safe.
> The constraint is created when you create the snapshot and adjusted with the
> proc sp_MSreseed. This proc is completely undocumented. If you disable the
> constraint you may run into problems depending on what is updating your
> table. Disableing it for inserts and updates will be harmless if only
> replication is making the changes, otherwise you may have problems, if the
> identity range is blown and another subscriber/publisher uses it.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Deepak Ramakumar" <dramakumar@.strongtie.com> wrote in message
> news:evu$7CnUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> well
> subscribers,
> have
> value
> to
> updating
> how
>
Help! INSERT Replication not working on Subscriber FOR ONE TABLE ONLY
Everything is working great except ONE table is not replicating INSERTS from
the subscriber back to the publisher. UPDATES *are* being replicating, and
INSERTS *are* being replicated for every other table! I can't figure out
what's going on.
When I first setup the subscription/publication, the table did have a "int
identity" field. This field has now been removed but INSERTS are still not
being replicated.
Can someone please help!
Thanks!
-Ryan
Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison
the subscriber back to the publisher. UPDATES *are* being replicating, and
INSERTS *are* being replicated for every other table! I can't figure out
what's going on.
When I first setup the subscription/publication, the table did have a "int
identity" field. This field has now been removed but INSERTS are still not
being replicated.
Can someone please help!
Thanks!
-Ryan
Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison
Monday, March 19, 2012
Help! How to give replication admin access without giving system a
Hi,
Currently we have a problem regarding security for replication. We have
a SQLServer that hosts about 30 databases. One of the database owner
requests that he needs to setup replication. But I don't want him to give
full 'system admin' rights to setup replication as he might mess up other
sqlserver settings and other databases that don't concern him. What is the
best way so that I can grant him enough access, so he can setup replication
for his database, and I don't have to worry about compromising the security
of other databases and the server.
Thanks
Hi Tony,
To administer replication through the enterprise manager you have to be a
member of the sysadmin role, and therefor have unlimited access to the whole
server.
The only way I can think of is to develop your own custom application using
SQL-DMO and/or SQL server ActiveX objects for administering a specific
replication.
see BOL, "Replication Programming"
Regards,
Jorgen D:
"Tony" wrote:
> Hi,
> Currently we have a problem regarding security for replication. We have
> a SQLServer that hosts about 30 databases. One of the database owner
> requests that he needs to setup replication. But I don't want him to give
> full 'system admin' rights to setup replication as he might mess up other
> sqlserver settings and other databases that don't concern him. What is the
> best way so that I can grant him enough access, so he can setup replication
> for his database, and I don't have to worry about compromising the security
> of other databases and the server.
> Thanks
>
Currently we have a problem regarding security for replication. We have
a SQLServer that hosts about 30 databases. One of the database owner
requests that he needs to setup replication. But I don't want him to give
full 'system admin' rights to setup replication as he might mess up other
sqlserver settings and other databases that don't concern him. What is the
best way so that I can grant him enough access, so he can setup replication
for his database, and I don't have to worry about compromising the security
of other databases and the server.
Thanks
Hi Tony,
To administer replication through the enterprise manager you have to be a
member of the sysadmin role, and therefor have unlimited access to the whole
server.
The only way I can think of is to develop your own custom application using
SQL-DMO and/or SQL server ActiveX objects for administering a specific
replication.
see BOL, "Replication Programming"
Regards,
Jorgen D:
"Tony" wrote:
> Hi,
> Currently we have a problem regarding security for replication. We have
> a SQLServer that hosts about 30 databases. One of the database owner
> requests that he needs to setup replication. But I don't want him to give
> full 'system admin' rights to setup replication as he might mess up other
> sqlserver settings and other databases that don't concern him. What is the
> best way so that I can grant him enough access, so he can setup replication
> for his database, and I don't have to worry about compromising the security
> of other databases and the server.
> Thanks
>
Help! Got an error while do the replication update
Dear all,
I got the following error while doing the replication in updating the date,
I found that a column's datatype is NTEXT, but i have other table also are
have columns set to NTEXT datatype and it works.
Does anyone have any idea on it?
"Only text pointers are allowed in work tables, never text, ntext, or image
columns. The query processor produced a query plan that required a text,
ntext, or image column in a work table."
can you post your schema here for the problem table? Also do you recall what
update/insert/delete caused this problem?
Perhaps try to restart your agent and log according to:
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Madstan" <stanley.chong@.hk.mrspedag.com> wrote in message
news:uQdRmGKvEHA.1404@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> I got the following error while doing the replication in updating the
date,
> I found that a column's datatype is NTEXT, but i have other table also are
> have columns set to NTEXT datatype and it works.
> Does anyone have any idea on it?
> "Only text pointers are allowed in work tables, never text, ntext, or
image
> columns. The query processor produced a query plan that required a text,
> ntext, or image column in a work table."
>
I got the following error while doing the replication in updating the date,
I found that a column's datatype is NTEXT, but i have other table also are
have columns set to NTEXT datatype and it works.
Does anyone have any idea on it?
"Only text pointers are allowed in work tables, never text, ntext, or image
columns. The query processor produced a query plan that required a text,
ntext, or image column in a work table."
can you post your schema here for the problem table? Also do you recall what
update/insert/delete caused this problem?
Perhaps try to restart your agent and log according to:
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Madstan" <stanley.chong@.hk.mrspedag.com> wrote in message
news:uQdRmGKvEHA.1404@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> I got the following error while doing the replication in updating the
date,
> I found that a column's datatype is NTEXT, but i have other table also are
> have columns set to NTEXT datatype and it works.
> Does anyone have any idea on it?
> "Only text pointers are allowed in work tables, never text, ntext, or
image
> columns. The query processor produced a query plan that required a text,
> ntext, or image column in a work table."
>
Friday, March 9, 2012
HELP! Can't delete merge replication subscribers in 2005
My client is using SQL 2005 Workgroup Edition. About 18-20 users will be
merge replication subscribers over the Internet with web synchronization.
During testing, several subscriptions were created and they will not drop
with the normal drop stored procedures.
This is an urgent problem because with my bogus, testing subscriptions
there, we have reached the 25 subscription limit and we cannot get their
final users subscribed.
I've seen others having this problem but haven't seen the resolution. Any
help would be greatly appreciated.
Thanks!
I take it you have tried to drop them using sp_dropmergesubscription and
sp_dropmergepullsubscription correct?
If so you should be able to drop them by deleting the contents of the
following tables on your subscriber db.
sysmergesubscriptions
MSmerge_supportability_settings
MSmerge_log_files
MSmerge_replinfo
MSmerge_supportability_settings
MSmerge_log_files
MSrepl_errors
MSmerge_history
MSmerge_articlehistory
MSmerge_sessions
MSmerge_replinfo
sysmergearticles
sysmergepublications
sysmergepartitioninfo
sysmergepartitioninfoview
sysmergeschemaarticles
sysmergeextendedarticlesview
sysmergeschemachange
sysmergesubsetfilters
sysmergesubscriptions
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
"Shannon Willison" <Shannon Willison@.discussions.microsoft.com> wrote in
message news:2ABD7D82-C738-4547-A9F6-DF29202DF39F@.microsoft.com...
> My client is using SQL 2005 Workgroup Edition. About 18-20 users will be
> merge replication subscribers over the Internet with web synchronization.
> During testing, several subscriptions were created and they will not drop
> with the normal drop stored procedures.
> This is an urgent problem because with my bogus, testing subscriptions
> there, we have reached the 25 subscription limit and we cannot get their
> final users subscribed.
> I've seen others having this problem but haven't seen the resolution. Any
> help would be greatly appreciated.
> Thanks!
merge replication subscribers over the Internet with web synchronization.
During testing, several subscriptions were created and they will not drop
with the normal drop stored procedures.
This is an urgent problem because with my bogus, testing subscriptions
there, we have reached the 25 subscription limit and we cannot get their
final users subscribed.
I've seen others having this problem but haven't seen the resolution. Any
help would be greatly appreciated.
Thanks!
I take it you have tried to drop them using sp_dropmergesubscription and
sp_dropmergepullsubscription correct?
If so you should be able to drop them by deleting the contents of the
following tables on your subscriber db.
sysmergesubscriptions
MSmerge_supportability_settings
MSmerge_log_files
MSmerge_replinfo
MSmerge_supportability_settings
MSmerge_log_files
MSrepl_errors
MSmerge_history
MSmerge_articlehistory
MSmerge_sessions
MSmerge_replinfo
sysmergearticles
sysmergepublications
sysmergepartitioninfo
sysmergepartitioninfoview
sysmergeschemaarticles
sysmergeextendedarticlesview
sysmergeschemachange
sysmergesubsetfilters
sysmergesubscriptions
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
"Shannon Willison" <Shannon Willison@.discussions.microsoft.com> wrote in
message news:2ABD7D82-C738-4547-A9F6-DF29202DF39F@.microsoft.com...
> My client is using SQL 2005 Workgroup Edition. About 18-20 users will be
> merge replication subscribers over the Internet with web synchronization.
> During testing, several subscriptions were created and they will not drop
> with the normal drop stored procedures.
> This is an urgent problem because with my bogus, testing subscriptions
> there, we have reached the 25 subscription limit and we cannot get their
> final users subscribed.
> I've seen others having this problem but haven't seen the resolution. Any
> help would be greatly appreciated.
> Thanks!
Help! cannot remove some of the replication remnants
Hope somebody can help because this is rather fustrating. I have recently
been doing some replication configurations between two servers. I started to
get a warning symbol on the replication monitor so I decided to uninstall
replication and start again. Unfortunately when I reinstall replication the
warning symbol returns.
How can I remove ALL history of replication on this server (I have tried
dropping distribution also). Although functinally replication still works
(regardless of icon) it is extremely annoying and does not relate to any
publications in existance.
Also I cannot remember any db names or publication names of those that were
published because they were dropped during cleanup.
Thanks in advance,
Mark
Mark,
if you've disabled publishing, this should take case of most things.
sp_removedbreplication can also be useful on individual databases.
Resrarting the services should remove error info on the replication monitor,
while sp_MSload_replication_status achieves the same but is less drastic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||These commands may help:
sp_removedbreplication 'pubs' --replicated database on publisher
sp_dropdistpublisher @.publisher = '<sql server name>'
--, @.no_checks = no_checks
sp_dropdistributiondb distribution --name of dist db
Patrick
"Mark Broadbent" wrote:
> Hope somebody can help because this is rather fustrating. I have recently
> been doing some replication configurations between two servers. I started to
> get a warning symbol on the replication monitor so I decided to uninstall
> replication and start again. Unfortunately when I reinstall replication the
> warning symbol returns.
> How can I remove ALL history of replication on this server (I have tried
> dropping distribution also). Although functinally replication still works
> (regardless of icon) it is extremely annoying and does not relate to any
> publications in existance.
> Also I cannot remember any db names or publication names of those that were
> published because they were dropped during cleanup.
> Thanks in advance,
> Mark
|||disable replication. Then go through the databases looking for tables called
mssubscriptions or mspublications - the names elude me right now and I don't
have SQL installed on this machine.
Delete the content of these rows. Make sure mind you that you don't have any
active subscriptions on this database - other wise they will get hosed;)
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
"Mark Broadbent" <Mark Broadbent@.discussions.microsoft.com> wrote in message
news:7CD45485-9352-449C-9170-618823D3856D@.microsoft.com...
> Hope somebody can help because this is rather fustrating. I have recently
> been doing some replication configurations between two servers. I started
> to
> get a warning symbol on the replication monitor so I decided to uninstall
> replication and start again. Unfortunately when I reinstall replication
> the
> warning symbol returns.
> How can I remove ALL history of replication on this server (I have tried
> dropping distribution also). Although functinally replication still works
> (regardless of icon) it is extremely annoying and does not relate to any
> publications in existance.
> Also I cannot remember any db names or publication names of those that
> were
> published because they were dropped during cleanup.
> Thanks in advance,
> Mark
|||Thanks Paul that stored proc did the trick. You have saved me a lot of grief,
thanks a million!
Thanks also to everyone else for their contributions.
"Paul Ibison" wrote:
> Mark,
> if you've disabled publishing, this should take case of most things.
> sp_removedbreplication can also be useful on individual databases.
> Resrarting the services should remove error info on the replication monitor,
> while sp_MSload_replication_status achieves the same but is less drastic.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
been doing some replication configurations between two servers. I started to
get a warning symbol on the replication monitor so I decided to uninstall
replication and start again. Unfortunately when I reinstall replication the
warning symbol returns.
How can I remove ALL history of replication on this server (I have tried
dropping distribution also). Although functinally replication still works
(regardless of icon) it is extremely annoying and does not relate to any
publications in existance.
Also I cannot remember any db names or publication names of those that were
published because they were dropped during cleanup.
Thanks in advance,
Mark
Mark,
if you've disabled publishing, this should take case of most things.
sp_removedbreplication can also be useful on individual databases.
Resrarting the services should remove error info on the replication monitor,
while sp_MSload_replication_status achieves the same but is less drastic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||These commands may help:
sp_removedbreplication 'pubs' --replicated database on publisher
sp_dropdistpublisher @.publisher = '<sql server name>'
--, @.no_checks = no_checks
sp_dropdistributiondb distribution --name of dist db
Patrick
"Mark Broadbent" wrote:
> Hope somebody can help because this is rather fustrating. I have recently
> been doing some replication configurations between two servers. I started to
> get a warning symbol on the replication monitor so I decided to uninstall
> replication and start again. Unfortunately when I reinstall replication the
> warning symbol returns.
> How can I remove ALL history of replication on this server (I have tried
> dropping distribution also). Although functinally replication still works
> (regardless of icon) it is extremely annoying and does not relate to any
> publications in existance.
> Also I cannot remember any db names or publication names of those that were
> published because they were dropped during cleanup.
> Thanks in advance,
> Mark
|||disable replication. Then go through the databases looking for tables called
mssubscriptions or mspublications - the names elude me right now and I don't
have SQL installed on this machine.
Delete the content of these rows. Make sure mind you that you don't have any
active subscriptions on this database - other wise they will get hosed;)
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
"Mark Broadbent" <Mark Broadbent@.discussions.microsoft.com> wrote in message
news:7CD45485-9352-449C-9170-618823D3856D@.microsoft.com...
> Hope somebody can help because this is rather fustrating. I have recently
> been doing some replication configurations between two servers. I started
> to
> get a warning symbol on the replication monitor so I decided to uninstall
> replication and start again. Unfortunately when I reinstall replication
> the
> warning symbol returns.
> How can I remove ALL history of replication on this server (I have tried
> dropping distribution also). Although functinally replication still works
> (regardless of icon) it is extremely annoying and does not relate to any
> publications in existance.
> Also I cannot remember any db names or publication names of those that
> were
> published because they were dropped during cleanup.
> Thanks in advance,
> Mark
|||Thanks Paul that stored proc did the trick. You have saved me a lot of grief,
thanks a million!
Thanks also to everyone else for their contributions.
"Paul Ibison" wrote:
> Mark,
> if you've disabled publishing, this should take case of most things.
> sp_removedbreplication can also be useful on individual databases.
> Resrarting the services should remove error info on the replication monitor,
> while sp_MSload_replication_status achieves the same but is less drastic.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Friday, February 24, 2012
Help with Web Sync sql 2005 to sql express
Hello,
OK I finally got the subscriber connected to the IIS server for replication. I am now getting errors when trying to apply the snap shot. Below is the error? Did I setup the publication incorrectly by selecting replication with another sql 2005? Am I supposed to select something different when trying to replicate between slq 2005 and sql express?
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'activities_2.sch' could not be propagated to the subscriber.
2005-08-24 20:52:35.920 Percent Complete: 0
2005-08-24 20:52:35.920 Category:NULL
Source: Microsoft SQL Native Client
Number: 1703
Message: Online index operations can only be performed in Enterprise edition of SQL Server.
'activities_2.sch' script
drop Table [dbo].[activities]
go
SET ANSI_PADDING ON
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[activities](
[activity] [varchar](50) NOT NULL,
[billing] [bit] NOT NULL,
[category] [varchar](50) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_77F8C0F06FB942A7B7206EF4GD99AD745] DEFAULT (newsequentialid())
)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[activities] ADD CONSTRAINT [PK_activities] PRIMARY KEY CLUSTERED
(
[activity] ASC
)WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)
GO
I've experienced the same problem but am struggling with the bitwise syntax for disabling the XMLIndex schema option using sp_changemergearticle. Could you provide an example?
Also, as an alternative workaround during development I've been manually commenting out the problem index from the .dri and .sch files in the snapshot:
--WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
Obviously it would better to fix the XMLIndex schema option than to have to do this everytime I create a new snapshot!
Thanks, STEVE
|||The current schema option should be something like: 0x04nnnnnnTo remove the XML index, make it 0x00nnnnnn and run snapshot again and sync (reinit).
If it is 0x07nnnnnn, then make it 0x03nnnnnn.
Basically you want to remove the 04 part in it.|||The current schema options on my tables is :
0x000000000C034FD1
I tried changing it to
0x000000000C030FD1
but that didn't work.
Any ideas?
|||The current schema option: 0x000000000C034FD1New one to try: 0x0000000008034FD1|||This was a known problem with Express subscribers.
Which CTP are you using? This should be fixed in the further CTPs. Either try the next CTP or there is a workaround below:
Meanwhile you can workaround the issue by disabling the XMLIndex schema option (0x04000000) on the table articles.
Use sp_changemergearticle to change the schema_option to remove this and then the snapshot should be applied correctly.
Sunday, February 19, 2012
help with understanding transactional replication
Hi everybody,
I was told that for transactional replication (see posts below) I need to
have snapshot scheduled to run for instance each night. But i dont
understand this.
This is my way of thinking how transactional replication should be
initiated:
- logreader, snapshot and distributer are stopped
- logreader is started so that it captures transactions that snapshot might
miss out on
- run snapshot immediately after logreader is started.
- snapshot starts doing its thing (copying the schema and the data in the
tables of the ddatabase). If for example snapshot has already processed
TableA and a change in data is made to TableA, logreader will pick this
change up and record it.
- once snapshot has completed its task, the distributer is started. The
distributer moves the snapshot to its destination and then reindexes the
tables. Finally it processes those transactions captured by the logreader.
Is this right?
I cant understand why the snapshot agent should be scheduled for
transactional replication, if the logreader is processing all future
transactions. My thought was that:
initial_snapshot
+
ongoing_transactions (as processed by the logreader)
=
current state of database
So why is there is a need to schedule snapshot for transactional
replication?
Any help in clearing up any of my misconceptions would be fantastic!
cheers, john
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23$2wX4c7FHA.3648@.tk2msftngp13.phx.gbl...
> The snapshot agent should be scheduled, perhaps each hour, or at a time
> when there are few users on your system. Note that a snapshot will only be
> generated if a subscriber needs one. Otherwise no snapshot will be
> generated. So, the only time you need to start this agent is when a
> subscriber needs one.
> The log reader agent should be running continuously.
> I normally run the distributation agent continuously. It doesn't matter in
> which order you start it.
> --
> 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
> "john r" <johnr@.trailer.com> wrote in message
> news:ucO85ub7FHA.956@.TK2MSFTNGP10.phx.gbl...
>
John,
the snapshot agent runs for initialization and reinitialization only. If you
have loads of anonymous subscribers where you have no idea when they'll come
online, then perhaps there is a case for frequent snapshots (is this what
was being referred to by whoever it was who told you that the snapshot agent
needs to run every night?), but most likely this isn't the case for you. In
my setup, we have only ever run the snapshot agent once on some
publications. Certainly the snapshot agents are all disabled and only run
manually when necessary.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I was told that for transactional replication (see posts below) I need to
have snapshot scheduled to run for instance each night. But i dont
understand this.
This is my way of thinking how transactional replication should be
initiated:
- logreader, snapshot and distributer are stopped
- logreader is started so that it captures transactions that snapshot might
miss out on
- run snapshot immediately after logreader is started.
- snapshot starts doing its thing (copying the schema and the data in the
tables of the ddatabase). If for example snapshot has already processed
TableA and a change in data is made to TableA, logreader will pick this
change up and record it.
- once snapshot has completed its task, the distributer is started. The
distributer moves the snapshot to its destination and then reindexes the
tables. Finally it processes those transactions captured by the logreader.
Is this right?
I cant understand why the snapshot agent should be scheduled for
transactional replication, if the logreader is processing all future
transactions. My thought was that:
initial_snapshot
+
ongoing_transactions (as processed by the logreader)
=
current state of database
So why is there is a need to schedule snapshot for transactional
replication?
Any help in clearing up any of my misconceptions would be fantastic!
cheers, john
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23$2wX4c7FHA.3648@.tk2msftngp13.phx.gbl...
> The snapshot agent should be scheduled, perhaps each hour, or at a time
> when there are few users on your system. Note that a snapshot will only be
> generated if a subscriber needs one. Otherwise no snapshot will be
> generated. So, the only time you need to start this agent is when a
> subscriber needs one.
> The log reader agent should be running continuously.
> I normally run the distributation agent continuously. It doesn't matter in
> which order you start it.
> --
> 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
> "john r" <johnr@.trailer.com> wrote in message
> news:ucO85ub7FHA.956@.TK2MSFTNGP10.phx.gbl...
>
John,
the snapshot agent runs for initialization and reinitialization only. If you
have loads of anonymous subscribers where you have no idea when they'll come
online, then perhaps there is a case for frequent snapshots (is this what
was being referred to by whoever it was who told you that the snapshot agent
needs to run every night?), but most likely this isn't the case for you. In
my setup, we have only ever run the snapshot agent once on some
publications. Certainly the snapshot agents are all disabled and only run
manually when necessary.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)