125Gb heavy OLTP publication db w/ Transactional Replication replicating 400
tables to 5 different servers. No problems on remote distributor, cpu hardly
used, sits practically idle most of the day staying under 10%. Ram is used
however not getting taxed.
We added merge replication for 1 table and the cpu's pegged to 90 to 100%
each time the merge agents pick up transactions at pub. Every 20 minutes a
job populated this table w/ 100 to 200 inserts at publisher, the 5 merge
agents kick in immediately (continuously running) and spike cpu bringing
server to a crawl.
Publisher, Distributor, & all Subscribers on LAN w/ 100Mbps connections.
SQL 2000 SP3 on all servers. Distributor is a 6CPU PIII 700Mhz each. New
Distributor hardware not an option. Have to limp along until new budgets kick
in...
Thoughts...?
TIA,
Chris
Chris,
are the 100/200 inserts involving BLOB datatypes? How long does the spike
last? Anyway, I'd change the merge agents from running continuously, in
order to stagger their impact on the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Showing posts with label transactional. Show all posts
Showing posts with label transactional. Show all posts
Wednesday, March 28, 2012
Merge doesn't re-propagate changes arrived from Transaccional repl
I have a the following scenario:
- A transactional publicaction "TRN2000" (in SrvSQLServer2000 server)
- A push subscription to "TRN2000" publication(in SrvSQLServer2005 server)
- A merge publication "Merge2005" defined in SrvSQLServer2005 over the same
Database/tables
that transactional subscription replicate
- Subscriptions to "Merge2005" publication
Then, this is the behavior:
- Data changes in SrvSQLServer2000 are replicated to SrvSQLServer2005 through
the TRN2000 subscription (OK)
- When I directly modify data(update, insert,) in the SrvSQLServer2005,
merge susbscriptions
receives data changes (OK)
But the problem is that data changes received in the SrvSQLServer2005
database
through the "Trn2000" replication..are not propagated to the "Merge2005"
subscriptions..
I suspect that the problem is that Transactional Push subscription use Bulk
Operations over
SrvSqlServer2005 and these are being ignored by the "Merge2005" replication
triggers.
some idea? workaround?
thanks in advance,
Alexander.
If the problem occurs during initialization, have a look at the
@.fire_triggers_on_snapshot parameter in sp_addarticle.
HTH,
Paul Ibison
|||Thanks Paul for the answer.
Unfortunately, the problem happens not only at initialization. It happens
when propagating any change on the data..
some idea?
"Paul Ibison" wrote:
> If the problem occurs during initialization, have a look at the
> @.fire_triggers_on_snapshot parameter in sp_addarticle.
> HTH,
> Paul Ibison
|||In that case I'm confused. The commands replicated are sent down as calls to
insert/update/delete stored procedures on the subscriber. This isn't like a
bulk insert or a fast load which themselves don't fire triggers. Is your
transactional setup one that involves updating subscribers ie has triggers of
its own? Are there any other triggers on the table? Are any/all triggers
marked as NOT FOR REPLICATION?
Rgds,
Paul Ibison
|||Table “T” on which the Transaction replication records the data (same table
“T” that also is published through the Merge replication) doesn’t have other
triggers.
OK, the use of “Bulk Insert” by the Transactional replication was only a
supposition.
I thought that insert could be doing “bulk insert” because if I execute:
sp_addtabletocontents @.table_name='T'
Merge replication replicate pending changes (changes that became on table
“T” through the Transaccional replication) to its subscribers.
(I could send you by mail my replication scripts if you think that they can
clarify something)
"Paul Ibison" wrote:
> In that case I'm confused. The commands replicated are sent down as calls to
> insert/update/delete stored procedures on the subscriber. This isn't like a
> bulk insert or a fast load which themselves don't fire triggers. Is your
> transactional setup one that involves updating subscribers ie has triggers of
> its own? Are there any other triggers on the table? Are any/all triggers
> marked as NOT FOR REPLICATION?
> Rgds,
> Paul Ibison
>
|||I'll have a go at reproducing and then post back...Meanwhile can you confirm
that the changes are definitely coming from transactional replication and not
being bulk loaded into the republished table directly.
Paul Ibison
|||Surely,
I have verified that making data modifications (through sql standard
sentences insert, update, delete)
on the database that has the transaccional publication, the changes arrive
correctly at table T and these
same changes are not re-propagated to merge subscriptions.
I thank for your dedication in this subject
Alexander Wolff
|||Hello Alexander & Paul,
I'm having exactly the same problem,
I wonder if you've found a solution (or explanation) for this issue.
Thanks,
Vladimir Kofman
> Surely,
> I have verified that making data modifications (through sql standard
> sentences insert, update, delete)
> on the database that has the transaccional publication, the changes
> arrive
> correctly at table T and these
> same changes are not re-propagated to merge subscriptions.
> I thank for your dedication in this subject
> Alexander Wolff
>
- A transactional publicaction "TRN2000" (in SrvSQLServer2000 server)
- A push subscription to "TRN2000" publication(in SrvSQLServer2005 server)
- A merge publication "Merge2005" defined in SrvSQLServer2005 over the same
Database/tables
that transactional subscription replicate
- Subscriptions to "Merge2005" publication
Then, this is the behavior:
- Data changes in SrvSQLServer2000 are replicated to SrvSQLServer2005 through
the TRN2000 subscription (OK)
- When I directly modify data(update, insert,) in the SrvSQLServer2005,
merge susbscriptions
receives data changes (OK)
But the problem is that data changes received in the SrvSQLServer2005
database
through the "Trn2000" replication..are not propagated to the "Merge2005"
subscriptions..
I suspect that the problem is that Transactional Push subscription use Bulk
Operations over
SrvSqlServer2005 and these are being ignored by the "Merge2005" replication
triggers.
some idea? workaround?
thanks in advance,
Alexander.
If the problem occurs during initialization, have a look at the
@.fire_triggers_on_snapshot parameter in sp_addarticle.
HTH,
Paul Ibison
|||Thanks Paul for the answer.
Unfortunately, the problem happens not only at initialization. It happens
when propagating any change on the data..
some idea?
"Paul Ibison" wrote:
> If the problem occurs during initialization, have a look at the
> @.fire_triggers_on_snapshot parameter in sp_addarticle.
> HTH,
> Paul Ibison
|||In that case I'm confused. The commands replicated are sent down as calls to
insert/update/delete stored procedures on the subscriber. This isn't like a
bulk insert or a fast load which themselves don't fire triggers. Is your
transactional setup one that involves updating subscribers ie has triggers of
its own? Are there any other triggers on the table? Are any/all triggers
marked as NOT FOR REPLICATION?
Rgds,
Paul Ibison
|||Table “T” on which the Transaction replication records the data (same table
“T” that also is published through the Merge replication) doesn’t have other
triggers.
OK, the use of “Bulk Insert” by the Transactional replication was only a
supposition.
I thought that insert could be doing “bulk insert” because if I execute:
sp_addtabletocontents @.table_name='T'
Merge replication replicate pending changes (changes that became on table
“T” through the Transaccional replication) to its subscribers.
(I could send you by mail my replication scripts if you think that they can
clarify something)
"Paul Ibison" wrote:
> In that case I'm confused. The commands replicated are sent down as calls to
> insert/update/delete stored procedures on the subscriber. This isn't like a
> bulk insert or a fast load which themselves don't fire triggers. Is your
> transactional setup one that involves updating subscribers ie has triggers of
> its own? Are there any other triggers on the table? Are any/all triggers
> marked as NOT FOR REPLICATION?
> Rgds,
> Paul Ibison
>
|||I'll have a go at reproducing and then post back...Meanwhile can you confirm
that the changes are definitely coming from transactional replication and not
being bulk loaded into the republished table directly.
Paul Ibison
|||Surely,
I have verified that making data modifications (through sql standard
sentences insert, update, delete)
on the database that has the transaccional publication, the changes arrive
correctly at table T and these
same changes are not re-propagated to merge subscriptions.
I thank for your dedication in this subject
Alexander Wolff
|||Hello Alexander & Paul,
I'm having exactly the same problem,
I wonder if you've found a solution (or explanation) for this issue.
Thanks,
Vladimir Kofman
> Surely,
> I have verified that making data modifications (through sql standard
> sentences insert, update, delete)
> on the database that has the transaccional publication, the changes
> arrive
> correctly at table T and these
> same changes are not re-propagated to merge subscriptions.
> I thank for your dedication in this subject
> Alexander Wolff
>
MERGE AND TRANSACTIONAL REPLICATION SUPPORT
We currently have a web application (using ASP for the front-end and SQL 2000
for the back-end dB). The site is accessed from several locations around the
world. Response is very slow for people in Europe when the server is located
in the US. We have now been tasked to deploy several servers at each
location and implement SQL replication )merge or trans?). I am not sure how
much that is done out in the industry and there is just not a lot of
documentation, either from MS or elsewhere, about how to do it. We have some
of the books that show you how to set up the servers but we are looking for
more detailed instructions and case studies on what the best practice are.
Before we get started we would like to learn about people who have actually
done this and find documentation to help us implement it. I have setup a lab
with two Windows 2003 servers and SQL in each. Thank you.
Edgar
saic_edgar@.yahoo.com
Paul,
Thank you for your suggestions including the books that will soon become
available. I plan to buy them as soon as they become available. I was
concerned why there is so little material on this subject when there are
hundreds of book when using Oracle for replication. I know MS is new at this
but we are hoping it will work well.
I am not sure I can answer your questions, but here is more details on what
we need.
We expect to have 4 servers located on 4 geographical areas. Each will
start with the same snapshot and each will be used to make updates. All
updates need to replicate to keep all servers in sync. Transactional may not
work since the WAN links connecting the servers may be too slow at times. I
have to do some reading on "the republisher replication model, or merge
replication
with alternative synchronization partners" you suggested to understand how
exactly it will work. My function in the team is as SA/DBA and I do not do
any programming. That will be done by 3 other SQL programmers. The ideal
solution would be to have people in each of the 4 locations use the
application in their local server, and periodically, maybe every two hours or
so, trigger a merge replication so that all servers are kept in sync. All
servers must be available at all times if possible.
We have been using SQL 200 for a long time but at a very low level so we
will need a lot of guidance in the are of getting replication going and
maintaining it, including conflict resolution. What methods of conflict
resolution do you suggest?
Thanks for all your help.
Edgar
"Edgar" wrote:
> We currently have a web application (using ASP for the front-end and SQL 2000
> for the back-end dB). The site is accessed from several locations around the
> world. Response is very slow for people in Europe when the server is located
> in the US. We have now been tasked to deploy several servers at each
> location and implement SQL replication )merge or trans?). I am not sure how
> much that is done out in the industry and there is just not a lot of
> documentation, either from MS or elsewhere, about how to do it. We have some
> of the books that show you how to set up the servers but we are looking for
> more detailed instructions and case studies on what the best practice are.
> Before we get started we would like to learn about people who have actually
> done this and find documentation to help us implement it. I have setup a lab
> with two Windows 2003 servers and SQL in each. Thank you.
> --
> Edgar
> saic_edgar@.yahoo.com
|||Edgar,
almost always the default has been suitable for me. This
is where the publisher wins against a subscriber and if 2
subscribers conflict, the first subscriber gets the
priority of the publisher. I've used global once where
the subscribers have priorities, but you only need one
person with a high priority who has a big gap before he
synchronizes for this to become a problem. From the other
ones I've looked at 'Subscriber Always Wins Conflict
Resolver' and custom stored procedure resolver. For the
main ones there's plenty of info in BOL. For the others
my impression from this newsgroup is that they have been
used only occasionally and in specialized cases.
Regards,
Paul Ibison
for the back-end dB). The site is accessed from several locations around the
world. Response is very slow for people in Europe when the server is located
in the US. We have now been tasked to deploy several servers at each
location and implement SQL replication )merge or trans?). I am not sure how
much that is done out in the industry and there is just not a lot of
documentation, either from MS or elsewhere, about how to do it. We have some
of the books that show you how to set up the servers but we are looking for
more detailed instructions and case studies on what the best practice are.
Before we get started we would like to learn about people who have actually
done this and find documentation to help us implement it. I have setup a lab
with two Windows 2003 servers and SQL in each. Thank you.
Edgar
saic_edgar@.yahoo.com
Paul,
Thank you for your suggestions including the books that will soon become
available. I plan to buy them as soon as they become available. I was
concerned why there is so little material on this subject when there are
hundreds of book when using Oracle for replication. I know MS is new at this
but we are hoping it will work well.
I am not sure I can answer your questions, but here is more details on what
we need.
We expect to have 4 servers located on 4 geographical areas. Each will
start with the same snapshot and each will be used to make updates. All
updates need to replicate to keep all servers in sync. Transactional may not
work since the WAN links connecting the servers may be too slow at times. I
have to do some reading on "the republisher replication model, or merge
replication
with alternative synchronization partners" you suggested to understand how
exactly it will work. My function in the team is as SA/DBA and I do not do
any programming. That will be done by 3 other SQL programmers. The ideal
solution would be to have people in each of the 4 locations use the
application in their local server, and periodically, maybe every two hours or
so, trigger a merge replication so that all servers are kept in sync. All
servers must be available at all times if possible.
We have been using SQL 200 for a long time but at a very low level so we
will need a lot of guidance in the are of getting replication going and
maintaining it, including conflict resolution. What methods of conflict
resolution do you suggest?
Thanks for all your help.
Edgar
"Edgar" wrote:
> We currently have a web application (using ASP for the front-end and SQL 2000
> for the back-end dB). The site is accessed from several locations around the
> world. Response is very slow for people in Europe when the server is located
> in the US. We have now been tasked to deploy several servers at each
> location and implement SQL replication )merge or trans?). I am not sure how
> much that is done out in the industry and there is just not a lot of
> documentation, either from MS or elsewhere, about how to do it. We have some
> of the books that show you how to set up the servers but we are looking for
> more detailed instructions and case studies on what the best practice are.
> Before we get started we would like to learn about people who have actually
> done this and find documentation to help us implement it. I have setup a lab
> with two Windows 2003 servers and SQL in each. Thank you.
> --
> Edgar
> saic_edgar@.yahoo.com
|||Edgar,
almost always the default has been suitable for me. This
is where the publisher wins against a subscriber and if 2
subscribers conflict, the first subscriber gets the
priority of the publisher. I've used global once where
the subscribers have priorities, but you only need one
person with a high priority who has a big gap before he
synchronizes for this to become a problem. From the other
ones I've looked at 'Subscriber Always Wins Conflict
Resolver' and custom stored procedure resolver. For the
main ones there's plenty of info in BOL. For the others
my impression from this newsgroup is that they have been
used only occasionally and in specialized cases.
Regards,
Paul Ibison
Merge and transactional replication
What is the difference bewteen merge and transactional replication? When to
go for merge and when for transactional?
Ravi,
have a look on BOL for loads of details on this, but I'll have a go at a
very basic synopsis:
Merge is ideal when you have bi-directional flow of data. Subscribers can be
offline and still make changes, and there is a rich array of conflict
resolvers for those cases where both the publisher and subscriber change the
same data. Also, it scales well to those situations where there are lots of
subscribers, and lots of subscriber changes.
Transactional in its basic form is unidirectional - changes made on the
publisher go to the subscriber but the subscriber must be treated as RO from
any other point of view. There are variations in which there is
bidirectional data flow - immediate updating and queued updating. Queued
updating really is quite similar to merge in many ways, but is designed for
those cases where there are few changes on the subscriber and the conflict
resolution options are limited.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'd like to quibble with Paul. Too many people when they hear bi-directional
they think merge. This is incorrect. When you hear occasionally off line, or
frequently offline - that's when you should think merge.
For server to server replication, uni-directional or bi-directional you
should think transactional. For server to client, think merge. By server I
mean most transactions originate on the server, by client I mean most
transactions originate on the server
Paul is correct, merge does ship with functionality rich conflict resolution
functions, whereas transactional replication "breaks" or the agents fail
when they encounter conflicts. This is because transactional replication
replicates transactions transactionally. It guarantees consistency between
databases if you design for it and don't use the continue on data
consistency errors. Transactions are durable. Once they hit the publisher
and subscriber they remain there.
Merge replication is loosely consistent. Your publisher and subscriber will
reach a consistent state, but transactions are not durable; they could be
rolled back.
Merge also replicates only changes. So if you update a row 1000 times only
the last change which occurs before the merge agent synchronizes the
publisher and subscriber is merged. With transactional replication all 1000
updates will go across the wire.
I hope this doesn't confuse you more.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uIGSnBORFHA.248@.TK2MSFTNGP15.phx.gbl...
> Ravi,
> have a look on BOL for loads of details on this, but I'll have a go at a
> very basic synopsis:
> Merge is ideal when you have bi-directional flow of data. Subscribers can
> be offline and still make changes, and there is a rich array of conflict
> resolvers for those cases where both the publisher and subscriber change
> the same data. Also, it scales well to those situations where there are
> lots of subscribers, and lots of subscriber changes.
> Transactional in its basic form is unidirectional - changes made on the
> publisher go to the subscriber but the subscriber must be treated as RO
> from any other point of view. There are variations in which there is
> bidirectional data flow - immediate updating and queued updating. Queued
> updating really is quite similar to merge in many ways, but is designed
> for those cases where there are few changes on the subscriber and the
> conflict resolution options are limited.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||My synopsis was labelled as 'very basic', but perhaps 'massively basic'
would have been more accurate. On the plus side I'd say that
bi-directional transactional replication has an importand niche and in many
cases is significantly faster than merge replication, however it generally
involves more setting up, doesn't come out of the box and there is more work
to create the conflict resolution routines where these are required. Anyway,
I agree it's definitely an important tool in the box.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||very true - as I said it was a quibble. A massive one at that
bi-directional replication is not available through the wizards or GUI. Not
even in SQL 2005.
There are no conflict resolution mechanisms that ship with bi-directional
transactional replication either. It just goes boom. Massively. However, you
can always encapsulate the conflict logic into the replication procs, which
I venture to guess is what you are referring to.
Another point in bi-directional transactional replication's favor is that
its easier to debug when it fails.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O28VxkQRFHA.996@.TK2MSFTNGP09.phx.gbl...
> My synopsis was labelled as 'very basic', but perhaps 'massively basic'
> would have been more accurate. On the plus side I'd say that
> bi-directional transactional replication has an importand niche and in
> many cases is significantly faster than merge replication, however it
> generally involves more setting up, doesn't come out of the box and there
> is more work to create the conflict resolution routines where these are
> required. Anyway, I agree it's definitely an important tool in the box.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
go for merge and when for transactional?
Ravi,
have a look on BOL for loads of details on this, but I'll have a go at a
very basic synopsis:
Merge is ideal when you have bi-directional flow of data. Subscribers can be
offline and still make changes, and there is a rich array of conflict
resolvers for those cases where both the publisher and subscriber change the
same data. Also, it scales well to those situations where there are lots of
subscribers, and lots of subscriber changes.
Transactional in its basic form is unidirectional - changes made on the
publisher go to the subscriber but the subscriber must be treated as RO from
any other point of view. There are variations in which there is
bidirectional data flow - immediate updating and queued updating. Queued
updating really is quite similar to merge in many ways, but is designed for
those cases where there are few changes on the subscriber and the conflict
resolution options are limited.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'd like to quibble with Paul. Too many people when they hear bi-directional
they think merge. This is incorrect. When you hear occasionally off line, or
frequently offline - that's when you should think merge.
For server to server replication, uni-directional or bi-directional you
should think transactional. For server to client, think merge. By server I
mean most transactions originate on the server, by client I mean most
transactions originate on the server
Paul is correct, merge does ship with functionality rich conflict resolution
functions, whereas transactional replication "breaks" or the agents fail
when they encounter conflicts. This is because transactional replication
replicates transactions transactionally. It guarantees consistency between
databases if you design for it and don't use the continue on data
consistency errors. Transactions are durable. Once they hit the publisher
and subscriber they remain there.
Merge replication is loosely consistent. Your publisher and subscriber will
reach a consistent state, but transactions are not durable; they could be
rolled back.
Merge also replicates only changes. So if you update a row 1000 times only
the last change which occurs before the merge agent synchronizes the
publisher and subscriber is merged. With transactional replication all 1000
updates will go across the wire.
I hope this doesn't confuse you more.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uIGSnBORFHA.248@.TK2MSFTNGP15.phx.gbl...
> Ravi,
> have a look on BOL for loads of details on this, but I'll have a go at a
> very basic synopsis:
> Merge is ideal when you have bi-directional flow of data. Subscribers can
> be offline and still make changes, and there is a rich array of conflict
> resolvers for those cases where both the publisher and subscriber change
> the same data. Also, it scales well to those situations where there are
> lots of subscribers, and lots of subscriber changes.
> Transactional in its basic form is unidirectional - changes made on the
> publisher go to the subscriber but the subscriber must be treated as RO
> from any other point of view. There are variations in which there is
> bidirectional data flow - immediate updating and queued updating. Queued
> updating really is quite similar to merge in many ways, but is designed
> for those cases where there are few changes on the subscriber and the
> conflict resolution options are limited.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||My synopsis was labelled as 'very basic', but perhaps 'massively basic'
would have been more accurate
. On the plus side I'd say thatbi-directional transactional replication has an importand niche and in many
cases is significantly faster than merge replication, however it generally
involves more setting up, doesn't come out of the box and there is more work
to create the conflict resolution routines where these are required. Anyway,
I agree it's definitely an important tool in the box.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||very true - as I said it was a quibble. A massive one at that
bi-directional replication is not available through the wizards or GUI. Not
even in SQL 2005.
There are no conflict resolution mechanisms that ship with bi-directional
transactional replication either. It just goes boom. Massively. However, you
can always encapsulate the conflict logic into the replication procs, which
I venture to guess is what you are referring to.
Another point in bi-directional transactional replication's favor is that
its easier to debug when it fails.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O28VxkQRFHA.996@.TK2MSFTNGP09.phx.gbl...
> My synopsis was labelled as 'very basic', but perhaps 'massively basic'
> would have been more accurate
. On the plus side I'd say that> bi-directional transactional replication has an importand niche and in
> many cases is significantly faster than merge replication, however it
> generally involves more setting up, doesn't come out of the box and there
> is more work to create the conflict resolution routines where these are
> required. Anyway, I agree it's definitely an important tool in the box.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
Merge and Transactional from the same server
I need to come up with a solution to my replication issue. The solution that I come up with involves using one publisher to push transactional and merge replication. The database that is published is the same.
Would this cause any issues? Can this be done? I would think that if a table is needed for both, I would have to create a separate publication for each type right?
Is there any documentation anywhere to help prove if this is possible?
Hi, William,
It can be done if you use readonly transactional replication and merge replication on the same db (same tables). You can't enable updatable transactional replication & merge replication at the same time on the same table same db (it's not allowed) since they both will try to add a guid column to the publishing table. Yes you need to create separate tran (readonly) publication and merge publication on the same set of tables. And you will have problems if tran and merge subscription are both on the same db (I guess you will not do this, right?). The other restriction is that for transactional replication, you need to have an primary key on the publisher table, I guess you know it already.
Not sure if we have any documentation specifically for this particular configuration but I just tried to play with this configuration and it worked just fine for me.
Thanks,
Zhiqiang Feng
|||So readonly for both transactional and merge? the tables I want to add readonly transactional to already have updatable merge (if I understand this correctly it is two way replication right)... The transactional will go downstream only to a server and the merge can go downstream (not to the same server as the transactional) and upstream....
It doesn't sound like this is possible does it? If not I have a backup plan..
|||William,
To avoid confusion, I should say "It can be done if you use merge replication and readonly transactional replication on the same db (same tables)". For merge, there is no concept of readonly, it only has one type which you can always download & upload changes between publisher and subscriber.
Thanks,
Zhiqiang Feng
|||Ok... So as long as the transactional only goes to the subscriber and not from the subscriber back to the publisher it should work with no issues? Is that what you are saying?
Sorry if I sound ignorant but I usually have only had one or the other and now I need both.
|||Yes, your understanding is correct.
Thanks,
Zhiqiang Feng
sqlMerge and Transactional from the same server
I need to come up with a solution to my replication issue. The solution that I come up with involves using one publisher to push transactional and merge replication. The database that is published is the same.
Would this cause any issues? Can this be done? I would think that if a table is needed for both, I would have to create a separate publication for each type right?
Is there any documentation anywhere to help prove if this is possible?
Hi, William,
It can be done if you use readonly transactional replication and merge replication on the same db (same tables). You can't enable updatable transactional replication & merge replication at the same time on the same table same db (it's not allowed) since they both will try to add a guid column to the publishing table. Yes you need to create separate tran (readonly) publication and merge publication on the same set of tables. And you will have problems if tran and merge subscription are both on the same db (I guess you will not do this, right?). The other restriction is that for transactional replication, you need to have an primary key on the publisher table, I guess you know it already.
Not sure if we have any documentation specifically for this particular configuration but I just tried to play with this configuration and it worked just fine for me.
Thanks,
Zhiqiang Feng
|||
So readonly for both transactional and merge? the tables I want to add readonly transactional to already have updatable merge (if I understand this correctly it is two way replication right)... The transactional will go downstream only to a server and the merge can go downstream (not to the same server as the transactional) and upstream....
It doesn't sound like this is possible does it? If not I have a backup plan..
|||William,
To avoid confusion, I should say "It can be done if you use merge replication and readonly transactional replication on the same db (same tables)". For merge, there is no concept of readonly, it only has one type which you can always download & upload changes between publisher and subscriber.
Thanks,
Zhiqiang Feng
|||Ok... So as long as the transactional only goes to the subscriber and not from the subscriber back to the publisher it should work with no issues? Is that what you are saying?
Sorry if I sound ignorant but I usually have only had one or the other and now I need both.
|||Yes, your understanding is correct.
Thanks,
Zhiqiang Feng
Subscribe to:
Posts (Atom)