I have three locations each with multiple SQL 2000 databases that need to
stay in constant sync with the other locations. Which is the best type of
replication? Merge Replication or 2-way Transactional Replication?
TIA,
Kevin
Probably peer to peer in SQL 2005. However bi-directional transactional
would probably be best. Keep in mind you are locked into a schema with 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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:e4PRqHGTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>I have three locations each with multiple SQL 2000 databases that need to
>stay in constant sync with the other locations. Which is the best type of
>replication? Merge Replication or 2-way Transactional Replication?
> TIA,
> Kevin
>
|||Schema issues are the reason I posted this question in the first place. In
my test scenario, I have 3 servers doing merge replication back and forth to
each other. Each server subscribes to the other two. Everything was working
fine until I tried to add a column for testing. I got the following error
message:
Schema replication failed because database X on server X is not the original
Publisher of table
Any ideas?
Kevin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uMWCuhGTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> Probably peer to peer in SQL 2005. However bi-directional transactional
> would probably be best. Keep in mind you are locked into a schema with
> 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
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:e4PRqHGTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
|||Do you have a hierarchy? IE Server A publishes to Server B which publishes
to Server C? Is so you must make the schema change at the top of the
hierarchy.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:emdknlGTHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Schema issues are the reason I posted this question in the first place. In
> my test scenario, I have 3 servers doing merge replication back and forth
> to each other. Each server subscribes to the other two. Everything was
> working fine until I tried to add a column for testing. I got the
> following error message:
> Schema replication failed because database X on server X is not the
> original Publisher of table
> Any ideas?
> Kevin
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uMWCuhGTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||I do not have a heirarchy. The topology I would like to use is a sort of
triangle, with no one server being the master and all transactions shared.
Thanks,
kevin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OXSz4XHTHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Do you have a hierarchy? IE Server A publishes to Server B which publishes
> to Server C? Is so you must make the schema change at the top of the
> hierarchy.
> --
> 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
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:emdknlGTHHA.2256@.TK2MSFTNGP02.phx.gbl...
>
|||You are talking about a multi-master topology which merge replication does
not support. You will need to use peer-to-peer in SQL Server 2005 for 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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:eemppbHTHHA.1036@.TK2MSFTNGP03.phx.gbl...
>I do not have a heirarchy. The topology I would like to use is a sort of
>triangle, with no one server being the master and all transactions shared.
> Thanks,
> kevin
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OXSz4XHTHHA.1200@.TK2MSFTNGP02.phx.gbl...
>
|||Kevin - just checking why you don't set up one merge publisher and 2 merge
subscribers. Is it because we don't have alternative sync partners anymore
and you can't guarantee connectivity with the publisher?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Yes Paul, I need sort of a triangle setup with each node working
independently and then sharing information with the other two.
Hilary, is Peer-to-Peer replication available in 2005 Standard? I took a
quick look at the replication options on our 2005 boxes on Friday and didn't
see anything about Peer-to-Peer.
Kevin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e3pPvcITHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Kevin - just checking why you don't set up one merge publisher and 2 merge
> subscribers. Is it because we don't have alternative sync partners anymore
> and you can't guarantee connectivity with the publisher?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||It is only available on the Enterprise Editions of SQL Server 2005.
You will be able to get it working using pure bi-directional transactional
replication but will probably have to do some filtering to get it to work
correctly.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:e08ed4qTHHA.600@.TK2MSFTNGP05.phx.gbl...
> Yes Paul, I need sort of a triangle setup with each node working
> independently and then sharing information with the other two.
> Hilary, is Peer-to-Peer replication available in 2005 Standard? I took a
> quick look at the replication options on our 2005 boxes on Friday and
> didn't see anything about Peer-to-Peer.
> Kevin
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:e3pPvcITHHA.4756@.TK2MSFTNGP06.phx.gbl...
>
|||Kevin,
what you are proposing is not in conflict with the merge setup, as you
mention the data is exchanges with the other 2 nodes. If this is a
prerequisite, then the connectivity must exist and the merge setup remains
an option. It doesn't perform any where near as well as peer to peer (only
enterpriose as you mentioned
http://msdn2.microsoft.com/en-us/library/ms143761.aspx) , but has the
advantage of easily adapting to schema changes and conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts
Friday, March 30, 2012
Replication to multiple subscribers question
We have an application that runs with MS SQL 2000 (SP3a) which is used for
tracking information from multiple sources. We are shortly going to be
setting up replication of this database with a secondary location, with a
third location planned by mid year. We know that there will be other
locations added over the course of the next few years. Our concern is that
if we have the snapshot expire, we will have to regenerate the snapshot and
reapply it to ALL subscribers before we can add a new subscriber. This is a
problem for 2 reasons. First because the foreign key relationships we have
between many of the tables appears to require that we delete the database on
the subscriber before we can apply the snapshot, and second because the
nature of our subscribers will mean that most of them will be disconnected
for long periods and as the number of subscribers grows it will be
increasingly unlikely that we will have them all connected at the same time.
It appears that our other option is to not have the snapshot expire, but MS
warns that this may cause performance problems but I haven't seen anything
that quantifies the potential performance problems.
Can anyone tell me what the "right" way of doing this is? What sort of
performance problems are we looking at if we don't have the snapshot expire?
Our database consists of approximately 100 or so user tables and currently
is about 300MB of data.
TIA
Ron L.
Ron,
I suspect the performance problems being talked about are proportional to
the amount of changes taking place to the data once the snapshot has been
made. EG if your snapshot contained static lookup tables, then having a
long-lived snapshot file wouldn't really be an issue. However if your
snapshot was of a productlisting table containing 1 million rows, and
product prices were changed on a regular basis (my case) then after
application of the snapshot you might find your merge agent (or distributor)
would struggle to send down the accumulated changes. BTW, in this case the
problem would be least for merge assuming records could be repeatedly
changed, and it avoids the issue of not being able to run the distribution
cleanup agent that you'll have.
HTH,
Paul Ibison
|||Paul
Thanks for the response. My data tends to be less changes and more
additions - we have logging records for text logs and records tracking
movement of parts within systems. At the moment, our largest table has just
over 100,000 records and the next largest is in the 20,000 record range.
One other option we were looking at was to have seperate publications
for each (or a small group of) additional server, thus making the snapshot
regeneration have less impact. Do you have any feeling as to whether this
is a better approach?
Thanks,
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FVkMhWLEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Ron,
> I suspect the performance problems being talked about are proportional to
> the amount of changes taking place to the data once the snapshot has been
> made. EG if your snapshot contained static lookup tables, then having a
> long-lived snapshot file wouldn't really be an issue. However if your
> snapshot was of a productlisting table containing 1 million rows, and
> product prices were changed on a regular basis (my case) then after
> application of the snapshot you might find your merge agent (or
distributor)
> would struggle to send down the accumulated changes. BTW, in this case the
> problem would be least for merge assuming records could be repeatedly
> changed, and it avoids the issue of not being able to run the distribution
> cleanup agent that you'll have.
> HTH,
> Paul Ibison
>
|||Ron,
I agree that this approach leads to more granularity and therefore is more
versatile for your needs, As long as the publications are distinct units
then it should be OK - eg if there are PK-FK relationships across
publications then it's a no-go, so it depends how related the tables are.
Regards,
Paul
|||Paul
What we are thinking is multiple publications covering the same set of
tables. Is this not going to work?
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e454$eeLEHA.1484@.tk2msftngp13.phx.gbl...
> Ron,
> I agree that this approach leads to more granularity and therefore is more
> versatile for your needs, As long as the publications are distinct units
> then it should be OK - eg if there are PK-FK relationships across
> publications then it's a no-go, so it depends how related the tables are.
> Regards,
> Paul
>
|||Ron,
I've not heard of this arrangement before, but it sounds feasible with merge
replication.
I guess the only downside is increased maintenance.
Cheers,
Paul
tracking information from multiple sources. We are shortly going to be
setting up replication of this database with a secondary location, with a
third location planned by mid year. We know that there will be other
locations added over the course of the next few years. Our concern is that
if we have the snapshot expire, we will have to regenerate the snapshot and
reapply it to ALL subscribers before we can add a new subscriber. This is a
problem for 2 reasons. First because the foreign key relationships we have
between many of the tables appears to require that we delete the database on
the subscriber before we can apply the snapshot, and second because the
nature of our subscribers will mean that most of them will be disconnected
for long periods and as the number of subscribers grows it will be
increasingly unlikely that we will have them all connected at the same time.
It appears that our other option is to not have the snapshot expire, but MS
warns that this may cause performance problems but I haven't seen anything
that quantifies the potential performance problems.
Can anyone tell me what the "right" way of doing this is? What sort of
performance problems are we looking at if we don't have the snapshot expire?
Our database consists of approximately 100 or so user tables and currently
is about 300MB of data.
TIA
Ron L.
Ron,
I suspect the performance problems being talked about are proportional to
the amount of changes taking place to the data once the snapshot has been
made. EG if your snapshot contained static lookup tables, then having a
long-lived snapshot file wouldn't really be an issue. However if your
snapshot was of a productlisting table containing 1 million rows, and
product prices were changed on a regular basis (my case) then after
application of the snapshot you might find your merge agent (or distributor)
would struggle to send down the accumulated changes. BTW, in this case the
problem would be least for merge assuming records could be repeatedly
changed, and it avoids the issue of not being able to run the distribution
cleanup agent that you'll have.
HTH,
Paul Ibison
|||Paul
Thanks for the response. My data tends to be less changes and more
additions - we have logging records for text logs and records tracking
movement of parts within systems. At the moment, our largest table has just
over 100,000 records and the next largest is in the 20,000 record range.
One other option we were looking at was to have seperate publications
for each (or a small group of) additional server, thus making the snapshot
regeneration have less impact. Do you have any feeling as to whether this
is a better approach?
Thanks,
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FVkMhWLEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Ron,
> I suspect the performance problems being talked about are proportional to
> the amount of changes taking place to the data once the snapshot has been
> made. EG if your snapshot contained static lookup tables, then having a
> long-lived snapshot file wouldn't really be an issue. However if your
> snapshot was of a productlisting table containing 1 million rows, and
> product prices were changed on a regular basis (my case) then after
> application of the snapshot you might find your merge agent (or
distributor)
> would struggle to send down the accumulated changes. BTW, in this case the
> problem would be least for merge assuming records could be repeatedly
> changed, and it avoids the issue of not being able to run the distribution
> cleanup agent that you'll have.
> HTH,
> Paul Ibison
>
|||Ron,
I agree that this approach leads to more granularity and therefore is more
versatile for your needs, As long as the publications are distinct units
then it should be OK - eg if there are PK-FK relationships across
publications then it's a no-go, so it depends how related the tables are.
Regards,
Paul
|||Paul
What we are thinking is multiple publications covering the same set of
tables. Is this not going to work?
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e454$eeLEHA.1484@.tk2msftngp13.phx.gbl...
> Ron,
> I agree that this approach leads to more granularity and therefore is more
> versatile for your needs, As long as the publications are distinct units
> then it should be OK - eg if there are PK-FK relationships across
> publications then it's a no-go, so it depends how related the tables are.
> Regards,
> Paul
>
|||Ron,
I've not heard of this arrangement before, but it sounds feasible with merge
replication.
I guess the only downside is increased maintenance.
Cheers,
Paul
Monday, March 12, 2012
Replication on multiple servers
here what I want to do,
1) transactional replication using two servers
2)Configure one server as publisher and distributor
3)Create publication on server 1
4)Create immediate update push subscription on server 2
5)I want bidirectional replication
First three steps I have created successfully
for step 4,
I created a push subcription from server 1 on publication created in step 3.
I am unable to find the option "immediate updating subscriber" in the wizard of creating push subcription.
anyway even without this option I am unable to create the replication.
in the replication monitor it is displaying as follows for the publication,
Snapshot---succeeded---snapshot of 1 artile created
Log reader--idle------no replicated transaction available
Server2:db2--Push--failed--the process could not connect to subscriber 'Server2'
what is missing?
thanks in advance for your advice
mayurThere is one step missing in your Replication setup. You have to define 'Server 2' as a subscriber. And 'Server 2 has to be able to lookup 'Server 1' as a publisher.
As far as setting 'Server 2' as updatable subscriber, you need to not only turn on 'Allow_Sync_Trans' in publisher, 'update_mode' has to be 'Sync Trans' in subscriber.
One last recommandation, write script if you can.|||I succeeded creating replication from Server1 to server2.
The problem is Server1 is in one domain and Server2 is in seperate domain.
the data is being replicated from S1 to S2
the problem is when I add/update any record in S2 database then it gives error that,
"Login failed for user sa"
what should i do next
which user account should I set up at subscriber i.e S2
thanks
Q: One last recommandation, write script if you can.
what do you mean by that, as I am using Enterprise manager|||S2 is now defined as remote server (or link server) on S1.
The authentification of S2 is made via windows logins. You shoul do one of the follwoing.
1) Talk to your domain admin(s) and set a trust relationship between those two domains
2) If you want to use the sa user to authentificate from s1 to s2, make sure that SqlServer on S2 is set up with MixedMode authentificatio, or will not work
Good luck,
ionut|||In BOL, under system stored procedure there is a section with all the system stored procedures you need to create or disable replication. Writing script gives you more control in the replication setup. You can start out using EM, but eventually you will find things that you can't do with EM.
1) transactional replication using two servers
2)Configure one server as publisher and distributor
3)Create publication on server 1
4)Create immediate update push subscription on server 2
5)I want bidirectional replication
First three steps I have created successfully
for step 4,
I created a push subcription from server 1 on publication created in step 3.
I am unable to find the option "immediate updating subscriber" in the wizard of creating push subcription.
anyway even without this option I am unable to create the replication.
in the replication monitor it is displaying as follows for the publication,
Snapshot---succeeded---snapshot of 1 artile created
Log reader--idle------no replicated transaction available
Server2:db2--Push--failed--the process could not connect to subscriber 'Server2'
what is missing?
thanks in advance for your advice
mayurThere is one step missing in your Replication setup. You have to define 'Server 2' as a subscriber. And 'Server 2 has to be able to lookup 'Server 1' as a publisher.
As far as setting 'Server 2' as updatable subscriber, you need to not only turn on 'Allow_Sync_Trans' in publisher, 'update_mode' has to be 'Sync Trans' in subscriber.
One last recommandation, write script if you can.|||I succeeded creating replication from Server1 to server2.
The problem is Server1 is in one domain and Server2 is in seperate domain.
the data is being replicated from S1 to S2
the problem is when I add/update any record in S2 database then it gives error that,
"Login failed for user sa"
what should i do next
which user account should I set up at subscriber i.e S2
thanks
Q: One last recommandation, write script if you can.
what do you mean by that, as I am using Enterprise manager|||S2 is now defined as remote server (or link server) on S1.
The authentification of S2 is made via windows logins. You shoul do one of the follwoing.
1) Talk to your domain admin(s) and set a trust relationship between those two domains
2) If you want to use the sa user to authentificate from s1 to s2, make sure that SqlServer on S2 is set up with MixedMode authentificatio, or will not work
Good luck,
ionut|||In BOL, under system stored procedure there is a section with all the system stored procedures you need to create or disable replication. Writing script gives you more control in the replication setup. You can start out using EM, but eventually you will find things that you can't do with EM.
Friday, March 9, 2012
Replication on multiple clients
One Server and many clients .
How do I set it up that my clients can replicate with my server.
ie Create the replication.
I am using foxpro as my front end and sql server as my back end.
With SQL Server I can use Enterprise Manager to create the pull subscribtion
..
Work well.
But
the clients don't have SQL Server but get the free MSDE.
I can create the databases and such using foxpro and sql exec commands but
how do I setup the replication.
Is anonymous pull connections safe or must I use named connections.
How do I setup the named pull subscriptions automatically.( via sql
commands)
Thanks in advance
Robert Craill
Web Development
USP Designs (Pty) Ltd
E: robert@.cyberprop.com
T: +27 11 955 9100
F: +27 11 955 9110
W: www.cyberprop.com
W: www.cyberagent.co.za
W: www.cyberbonds.co.za
USP Designs (Pty) Ltd is Proudly South African!
This message and any attachments are confidential and intended solely for
the addressee. Any unauthorized use, alteration or dissemination is
prohibited. USP Designs (Pty) Ltd. accepts no liability whatsoever for any
loss, whether it be direct, indirect or consequential, arising from
information made available and actions resulting there from.
No reply's
Is my request so idiotic
"Robert Craill" <robert@.cyberprop.com> wrote in message
news:uwj3LZ$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
> One Server and many clients .
> How do I set it up that my clients can replicate with my server.
> ie Create the replication.
> I am using foxpro as my front end and sql server as my back end.
> With SQL Server I can use Enterprise Manager to create the pull
> subscribtion .
> Work well.
> But
> the clients don't have SQL Server but get the free MSDE.
> I can create the databases and such using foxpro and sql exec commands but
> how do I setup the replication.
> Is anonymous pull connections safe or must I use named connections.
> How do I setup the named pull subscriptions automatically.( via sql
> commands)
> Thanks in advance
> ----
> Robert Craill
> Web Development
> USP Designs (Pty) Ltd
> E: robert@.cyberprop.com
> T: +27 11 955 9100
> F: +27 11 955 9110
> W: www.cyberprop.com
> W: www.cyberagent.co.za
> W: www.cyberbonds.co.za
> USP Designs (Pty) Ltd is Proudly South African!
> This message and any attachments are confidential and intended solely for
> the addressee. Any unauthorized use, alteration or dissemination is
> prohibited. USP Designs (Pty) Ltd. accepts no liability whatsoever for any
> loss, whether it be direct, indirect or consequential, arising from
> information made available and actions resulting there from.
>
|||Hi Robert, you can use replication system stored procedures to set up
replication in your case. The documentation for
sp_add[merge]pullsubscription[_agent] should be able to get you started.
HTH
-Raymond
"Robert Craill" wrote:
> No reply's
> Is my request so idiotic
> "Robert Craill" <robert@.cyberprop.com> wrote in message
> news:uwj3LZ$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
>
How do I set it up that my clients can replicate with my server.
ie Create the replication.
I am using foxpro as my front end and sql server as my back end.
With SQL Server I can use Enterprise Manager to create the pull subscribtion
..
Work well.
But
the clients don't have SQL Server but get the free MSDE.
I can create the databases and such using foxpro and sql exec commands but
how do I setup the replication.
Is anonymous pull connections safe or must I use named connections.
How do I setup the named pull subscriptions automatically.( via sql
commands)
Thanks in advance
Robert Craill
Web Development
USP Designs (Pty) Ltd
E: robert@.cyberprop.com
T: +27 11 955 9100
F: +27 11 955 9110
W: www.cyberprop.com
W: www.cyberagent.co.za
W: www.cyberbonds.co.za
USP Designs (Pty) Ltd is Proudly South African!
This message and any attachments are confidential and intended solely for
the addressee. Any unauthorized use, alteration or dissemination is
prohibited. USP Designs (Pty) Ltd. accepts no liability whatsoever for any
loss, whether it be direct, indirect or consequential, arising from
information made available and actions resulting there from.
No reply's
Is my request so idiotic
"Robert Craill" <robert@.cyberprop.com> wrote in message
news:uwj3LZ$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
> One Server and many clients .
> How do I set it up that my clients can replicate with my server.
> ie Create the replication.
> I am using foxpro as my front end and sql server as my back end.
> With SQL Server I can use Enterprise Manager to create the pull
> subscribtion .
> Work well.
> But
> the clients don't have SQL Server but get the free MSDE.
> I can create the databases and such using foxpro and sql exec commands but
> how do I setup the replication.
> Is anonymous pull connections safe or must I use named connections.
> How do I setup the named pull subscriptions automatically.( via sql
> commands)
> Thanks in advance
> ----
> Robert Craill
> Web Development
> USP Designs (Pty) Ltd
> E: robert@.cyberprop.com
> T: +27 11 955 9100
> F: +27 11 955 9110
> W: www.cyberprop.com
> W: www.cyberagent.co.za
> W: www.cyberbonds.co.za
> USP Designs (Pty) Ltd is Proudly South African!
> This message and any attachments are confidential and intended solely for
> the addressee. Any unauthorized use, alteration or dissemination is
> prohibited. USP Designs (Pty) Ltd. accepts no liability whatsoever for any
> loss, whether it be direct, indirect or consequential, arising from
> information made available and actions resulting there from.
>
|||Hi Robert, you can use replication system stored procedures to set up
replication in your case. The documentation for
sp_add[merge]pullsubscription[_agent] should be able to get you started.
HTH
-Raymond
"Robert Craill" wrote:
> No reply's
> Is my request so idiotic
> "Robert Craill" <robert@.cyberprop.com> wrote in message
> news:uwj3LZ$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
>
Wednesday, March 7, 2012
Replication Monitor Question
Hello,
We have some mobile devices that are setup to replicate with our sql 2005 server via web sync. It seems that the devices are added multiple times within replication monitor. Is there a way to purge the bogus items. For example we have a mobile unit labeled as Device12 we have it listed under the subscriptions 5x. I would like to purge four of them as they are not currently being the replicated one.
Device12-A6EE69A0944 is the valid subscription
Device12-EE44D1EF3E24 is an invalid one.
Thanks in advance.
John
You may want to query table dbo.MSsubscriptions in the distribution database to see if it has those extra entries. I'm not saying it's safe to delete them, but I'm guessing that's where sql monitor is pulling those rogue entries from.Saturday, February 25, 2012
Replication issues with multiple databases
I am planning to use replication from 400 databases to the master database on
a periodic basis. Do you see any issues with that ?
All databases have the same structure
Alu,
this is a bit vague as it stands
but certainly this is 'possible'. In
this link they discuss scaling merge to 2000 subscribers:
http://www.microsoft.com/technet/pro...mergperf.mspx.
For your setup, the type of replication selected will have some
implications. There are also implications with the type of topology
selected, hardware available, bandwidth, data volume etc so really you just
have to implement it, monitor using Perfmon and then see how it goes. As an
aid to avoid contention (data and disk) you might need to consider
staggering synchronization.
Regards,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
a periodic basis. Do you see any issues with that ?
All databases have the same structure
Alu,
this is a bit vague as it stands
but certainly this is 'possible'. Inthis link they discuss scaling merge to 2000 subscribers:
http://www.microsoft.com/technet/pro...mergperf.mspx.
For your setup, the type of replication selected will have some
implications. There are also implications with the type of topology
selected, hardware available, bandwidth, data volume etc so really you just
have to implement it, monitor using Perfmon and then see how it goes. As an
aid to avoid contention (data and disk) you might need to consider
staggering synchronization.
Regards,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Tuesday, February 21, 2012
Replication issue- data changes not seen at subscriber
Hi,
I have very simple one way transactional replication. There are multiple
publications. Every publication has multiple articles. All data in tables
have been published without a filter condition. Recently i have been getting
compalints about data sync. Changes appliad at publisher are not being
propated to subscriber. I verified agents log reader and distributor are
running fine, no error msgs. I manually updated one table in a publication
where i ususally encounter problems, but the change have not been propagated
to subsciber. It is working fine for other table updates. All publications
are from same database. and they go to same destination db.
Can someone pass me, how to troubleshoot to find out where is the problem?.
I reinitialized it , but not no use, didn't work.
Thanks,
Subbu.
Hi,
A way to confirm in transactional replication that sql server is not
replicating a specific table of a publication (among other things) is to
verify that when a transaction is made at the publisher the corresponding
store procedure is executed at the subscriber, that is:
spMS_upd_yourtablename
spMS_ins_yourtablename
spMS_del_yourtablename
You can modify this stored procedures (after a backup, to be able to reverse
the procedure after troubleshooting) to include some code that will led you
to determine if the present stored procedure is being executed and if the
intended result is achieved.
If you come to the conclusion that sql is not replicating the table, it’s
advisable that you apply the latest service pack (all severs) and re create
de publications and subscriptions.
Regards,
Rafael Meza
"subbu" escribió:
> Hi,
> I have very simple one way transactional replication. There are multiple
> publications. Every publication has multiple articles. All data in tables
> have been published without a filter condition. Recently i have been getting
> compalints about data sync. Changes appliad at publisher are not being
> propated to subscriber. I verified agents log reader and distributor are
> running fine, no error msgs. I manually updated one table in a publication
> where i ususally encounter problems, but the change have not been propagated
> to subsciber. It is working fine for other table updates. All publications
> are from same database. and they go to same destination db.
> Can someone pass me, how to troubleshoot to find out where is the problem?.
> I reinitialized it , but not no use, didn't work.
> Thanks,
> Subbu.
>
>
|||Hi,
Thanks for the response.
As per my understanding, logreader will make entry into repl_commands table
in distributor db as soon as there is a change on published data. Then
distributor will pickup the cmds from this table to apply on subscribers.
From the moment i made some changes to the published table, i watched
repl_commands table in distributor to see the transaction, it never got into
the (repl_commands) table.
My question is why logreader agent is not picking up the changes i made.
Is there a way to troubleshoot or reset secondary log truncation mark
without droping and recreating replication.
Thanks,
Subbu.
whether they have been read by logreader agent. there are no entries
"rafameza" <rafameza@.discussions.microsoft.com> wrote in message
news:30218EEC-EB8E-4F22-AF9C-766DC1E4348D@.microsoft.com...
> Hi,
> A way to confirm in transactional replication that sql server is not
> replicating a specific table of a publication (among other things) is to
> verify that when a transaction is made at the publisher the corresponding
> store procedure is executed at the subscriber, that is:
> spMS_upd_yourtablename
> spMS_ins_yourtablename
> spMS_del_yourtablename
> You can modify this stored procedures (after a backup, to be able to
reverse
> the procedure after troubleshooting) to include some code that will led
you
> to determine if the present stored procedure is being executed and if the
> intended result is achieved.
> If you come to the conclusion that sql is not replicating the table, it's
> advisable that you apply the latest service pack (all severs) and re
create[vbcol=seagreen]
> de publications and subscriptions.
> Regards,
> Rafael Meza
> "subbu" escribi:
tables[vbcol=seagreen]
getting[vbcol=seagreen]
publication[vbcol=seagreen]
propagated[vbcol=seagreen]
publications[vbcol=seagreen]
problem?.[vbcol=seagreen]
I have very simple one way transactional replication. There are multiple
publications. Every publication has multiple articles. All data in tables
have been published without a filter condition. Recently i have been getting
compalints about data sync. Changes appliad at publisher are not being
propated to subscriber. I verified agents log reader and distributor are
running fine, no error msgs. I manually updated one table in a publication
where i ususally encounter problems, but the change have not been propagated
to subsciber. It is working fine for other table updates. All publications
are from same database. and they go to same destination db.
Can someone pass me, how to troubleshoot to find out where is the problem?.
I reinitialized it , but not no use, didn't work.
Thanks,
Subbu.
Hi,
A way to confirm in transactional replication that sql server is not
replicating a specific table of a publication (among other things) is to
verify that when a transaction is made at the publisher the corresponding
store procedure is executed at the subscriber, that is:
spMS_upd_yourtablename
spMS_ins_yourtablename
spMS_del_yourtablename
You can modify this stored procedures (after a backup, to be able to reverse
the procedure after troubleshooting) to include some code that will led you
to determine if the present stored procedure is being executed and if the
intended result is achieved.
If you come to the conclusion that sql is not replicating the table, it’s
advisable that you apply the latest service pack (all severs) and re create
de publications and subscriptions.
Regards,
Rafael Meza
"subbu" escribió:
> Hi,
> I have very simple one way transactional replication. There are multiple
> publications. Every publication has multiple articles. All data in tables
> have been published without a filter condition. Recently i have been getting
> compalints about data sync. Changes appliad at publisher are not being
> propated to subscriber. I verified agents log reader and distributor are
> running fine, no error msgs. I manually updated one table in a publication
> where i ususally encounter problems, but the change have not been propagated
> to subsciber. It is working fine for other table updates. All publications
> are from same database. and they go to same destination db.
> Can someone pass me, how to troubleshoot to find out where is the problem?.
> I reinitialized it , but not no use, didn't work.
> Thanks,
> Subbu.
>
>
|||Hi,
Thanks for the response.
As per my understanding, logreader will make entry into repl_commands table
in distributor db as soon as there is a change on published data. Then
distributor will pickup the cmds from this table to apply on subscribers.
From the moment i made some changes to the published table, i watched
repl_commands table in distributor to see the transaction, it never got into
the (repl_commands) table.
My question is why logreader agent is not picking up the changes i made.
Is there a way to troubleshoot or reset secondary log truncation mark
without droping and recreating replication.
Thanks,
Subbu.
whether they have been read by logreader agent. there are no entries
"rafameza" <rafameza@.discussions.microsoft.com> wrote in message
news:30218EEC-EB8E-4F22-AF9C-766DC1E4348D@.microsoft.com...
> Hi,
> A way to confirm in transactional replication that sql server is not
> replicating a specific table of a publication (among other things) is to
> verify that when a transaction is made at the publisher the corresponding
> store procedure is executed at the subscriber, that is:
> spMS_upd_yourtablename
> spMS_ins_yourtablename
> spMS_del_yourtablename
> You can modify this stored procedures (after a backup, to be able to
reverse
> the procedure after troubleshooting) to include some code that will led
you
> to determine if the present stored procedure is being executed and if the
> intended result is achieved.
> If you come to the conclusion that sql is not replicating the table, it's
> advisable that you apply the latest service pack (all severs) and re
create[vbcol=seagreen]
> de publications and subscriptions.
> Regards,
> Rafael Meza
> "subbu" escribi:
tables[vbcol=seagreen]
getting[vbcol=seagreen]
publication[vbcol=seagreen]
propagated[vbcol=seagreen]
publications[vbcol=seagreen]
problem?.[vbcol=seagreen]
Replication Issue
*** Apologies for the post in multiple newsgroup ***
Dear All
We have a publication on Server 1, using Transactional
Replication. Server 2 has the distribution database, and
the database we are replicating too.
We switched off the publication using EM Tools ->
Replication -> Disable Publication and Distribution, and
attempted to change some columns on Server 1, and recieved
a cannot change column as used in replication message.
I have tried a range of sql commands with no luck.
Can anyone sugest to me a way of going forward on this ?
Thanks
JThanks this has been solved.
>--Original Message--
>*** Apologies for the post in multiple newsgroup ***
>Dear All
>We have a publication on Server 1, using Transactional
>Replication. Server 2 has the distribution database, and
>the database we are replicating too.
>We switched off the publication using EM Tools ->
>Replication -> Disable Publication and Distribution, and
>attempted to change some columns on Server 1, and
recieved
>a cannot change column as used in replication message.
>I have tried a range of sql commands with no luck.
>Can anyone sugest to me a way of going forward on this ?
>Thanks
>J
>.
>
Dear All
We have a publication on Server 1, using Transactional
Replication. Server 2 has the distribution database, and
the database we are replicating too.
We switched off the publication using EM Tools ->
Replication -> Disable Publication and Distribution, and
attempted to change some columns on Server 1, and recieved
a cannot change column as used in replication message.
I have tried a range of sql commands with no luck.
Can anyone sugest to me a way of going forward on this ?
Thanks
JThanks this has been solved.
>--Original Message--
>*** Apologies for the post in multiple newsgroup ***
>Dear All
>We have a publication on Server 1, using Transactional
>Replication. Server 2 has the distribution database, and
>the database we are replicating too.
>We switched off the publication using EM Tools ->
>Replication -> Disable Publication and Distribution, and
>attempted to change some columns on Server 1, and
recieved
>a cannot change column as used in replication message.
>I have tried a range of sql commands with no luck.
>Can anyone sugest to me a way of going forward on this ?
>Thanks
>J
>.
>
Subscribe to:
Posts (Atom)