Transactional replication with updates on the subscriber?SQL Server transactional replication - behaviour of...
Eww, those bytes are gross
How can my powered armor quickly replace its ceramic plates?
Increasing the flow in descriptions of a sequence of events
What is a good reason for every spaceship to carry a weapon on board?
If I delete my router's history can my ISP still provide it to my parents?
How should I handle players who ignore the session zero agreement?
Absorbing damage with Planeswalker
Why is Agricola named as such?
Aligning symbols underneath each other neatly
How to make ice magic work from a scientific point of view?
Quickly creating a sparse array
How to play electric guitar and bass as a duet
What would the chemical name be for C13H8Cl3NO
Cat is tipping over bed-side lamps during the night
Why avoid shared user accounts?
Why wasn't TEventArgs made contravariant in the standard event pattern in the .NET ecosystem?
What are the exceptions to Natural Selection?
A curious equality of integrals involving the prime counting function?
How can I play a serial killer in a party of good PCs?
How do I append a character to the end of every line in an Excel cell?
Cookies - Should the toggles be on?
Can my spouse sponsor me for a UK visa if I am unemployed?
Making him into a bully (how to show mild violence)
A Missing Symbol for This Logo
Transactional replication with updates on the subscriber?
SQL Server transactional replication - behaviour of a MERGE command on the subscriber(s) vs. publishertransactional Replication of millions of updatesTransactional Replication not sending all commandsTransactional Replication Over DomainDelayed transactional replication setup?Execute a script after transactional replication setup completesHow to setup filtered transactional replicationSQL Replication for Schema changesTransactional Replication without initialization: Subscriber not activeResynchronize transactional replication in SQL Server 2012 without doing a complete reinitialization after disk filled
Summary
We need a "two-way" (bi-directional) replication topology which should be easy to setup and administer, with minimal downtime when changes are required.
Current solution
We have two SQL Server Instances 2008 R2 Standard, on Servers SQL1 and SQL2.
Server SQL1 has a database db1 with tables a,b,c,d,e,f,g,h,i,j,k.
Server SQL2 has a database db2 with tables a,b,c,d,e,f,g,h,i, x,y,z
db1 serves as the primary database of the application app1, where all transactions are executed (mainly OLTP workload).
db2 serves as the replica database of db1 where all the reports and DW tasks are executed (mainly DW workload).
db2 has almost all data of db1 (tables a-i) and some extra tables (x,y,z).
We have setup a Transactional Replication (db1_to_db2) with the following (important) options
for the publication:
- @repl_freq = N'continuous'
- @immediate_sync = N'true'
- @replicate_ddl = 1
for the published articles:
- @schema_option = 0x000001410DC37EDF
As there is little maintenance window available for both databases, with the described setup we can:
1. replicate schema changes to the subscriber
2. add new tables to the publication and use a partial snapshot rather than having to reinitialize the entire subscription
This is working like a charm.
But now, a new requirement has come up.
The Problem
Some changes on specific replicated tables, that are updated on db2 directly, should be transferred back to db1.
Let's say that tables b on db2 (that references table a and is referenced by table c through FKs), accepts updates on its data that should travel to table b on db1 and not get replicated back to db2. Only Updates are permitted on table b on db2. Not Inserts or Deletes.
Thoughts
We have tried every possible (simple and easily adopted) solution we could think of:
Setup merge replication instead of transactional.
- It does not accept partial snapshots for newly added articles.
- It adds an extra column which means major changes to the application app1.
FAIL
Transactional replication with updatable subscriber
- It does not accept partial snapshots for newly added articles.
- It adds an extra column which means major changes to the application app1.
FAIL
Update db1.b through a (ON INSERT) trigger on db2.b:
- This is a 2Phase-commit transaction that could fail due to network connectivity issues (which otherwise do not bother us)
- There is no easy way to explude these Updates from being replicated back to db2.b through the replication.
FAIL
Setup a transactional replication from db2 to db1 with table b as the only article.
- Again there is no way to exclude these transactions from being replicated back to db2. It would be nice to have something like a 'NOT FOR REPLICATION' option for these transactions...
FAIL
This is as far as we have gone in search of a solution.
Please help
Please state any idea you might have, taking into account our specific needs.
Forgive my being too analytic but I wanted to give every piece of required information you might need.
sql-server-2008 transactional-replication
asked May 11 '17 at 14:27
Konstantinos KatsoridisKonstantinos Katsoridis
111
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Summary
We need a "two-way" (bi-directional) replication topology which should be easy to setup and administer, with minimal downtime when changes are required.
Current solution
We have two SQL Server Instances 2008 R2 Standard, on Servers SQL1 and SQL2.
Server SQL1 has a database db1 with tables a,b,c,d,e,f,g,h,i,j,k.
Server SQL2 has a database db2 with tables a,b,c,d,e,f,g,h,i, x,y,z
db1 serves as the primary database of the application app1, where all transactions are executed (mainly OLTP workload).
db2 serves as the replica database of db1 where all the reports and DW tasks are executed (mainly DW workload).
db2 has almost all data of db1 (tables a-i) and some extra tables (x,y,z).
We have setup a Transactional Replication (db1_to_db2) with the following (important) options
for the publication:
- @repl_freq = N'continuous'
- @immediate_sync = N'true'
- @replicate_ddl = 1
for the published articles:
- @schema_option = 0x000001410DC37EDF
As there is little maintenance window available for both databases, with the described setup we can:
1. replicate schema changes to the subscriber
2. add new tables to the publication and use a partial snapshot rather than having to reinitialize the entire subscription
This is working like a charm.
But now, a new requirement has come up.
The Problem
Some changes on specific replicated tables, that are updated on db2 directly, should be transferred back to db1.
Let's say that tables b on db2 (that references table a and is referenced by table c through FKs), accepts updates on its data that should travel to table b on db1 and not get replicated back to db2. Only Updates are permitted on table b on db2. Not Inserts or Deletes.
Thoughts
We have tried every possible (simple and easily adopted) solution we could think of:
Setup merge replication instead of transactional.
- It does not accept partial snapshots for newly added articles.
- It adds an extra column which means major changes to the application app1.
FAIL
Transactional replication with updatable subscriber
- It does not accept partial snapshots for newly added articles.
- It adds an extra column which means major changes to the application app1.
FAIL
Update db1.b through a (ON INSERT) trigger on db2.b:
- This is a 2Phase-commit transaction that could fail due to network connectivity issues (which otherwise do not bother us)
- There is no easy way to explude these Updates from being replicated back to db2.b through the replication.
FAIL
Setup a transactional replication from db2 to db1 with table b as the only article.
- Again there is no way to exclude these transactions from being replicated back to db2. It would be nice to have something like a 'NOT FOR REPLICATION' option for these transactions...
FAIL
This is as far as we have gone in search of a solution.
Please help
Please state any idea you might have, taking into account our specific needs.
Forgive my being too analytic but I wanted to give every piece of required information you might need.
sql-server-2008 transactional-replication
asked May 11 '17 at 14:27
Konstantinos KatsoridisKonstantinos Katsoridis
111
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
+1 for providing all the info and research done so far. have you looked in to Peer-to-Peer Transactional Replication ? or have service broker deliver the changes back to publisher -- might require a bit of more thinking and POC !
– Kin
May 11 '17 at 16:04
Thank you for the ideas. However Peer-to-Peer replication is an Enterprise only feature, so not applicable to this specific project. I haven't thought about Service Broker! It sounds promising though it would mean significant changes in the application. But I will spread the thought to the devs.
– Konstantinos Katsoridis
May 17 '17 at 13:08
I was thinking something similar (to the Service Broker idea) in logic. An INSTEAD OF UPDATE trigger on db2.b that redirects the UPDATES to the db1.b table, which in return will replicate the update to the db2.b through replication. There you have it.Problem solved? Not exactly because you have to deal with the instant response of the initial Update operation that the application has executed...
– Konstantinos Katsoridis
May 17 '17 at 13:09
add a comment |
Summary
We need a "two-way" (bi-directional) replication topology which should be easy to setup and administer, with minimal downtime when changes are required.
Current solution
We have two SQL Server Instances 2008 R2 Standard, on Servers SQL1 and SQL2.
Server SQL1 has a database db1 with tables a,b,c,d,e,f,g,h,i,j,k.
Server SQL2 has a database db2 with tables a,b,c,d,e,f,g,h,i, x,y,z
db1 serves as the primary database of the application app1, where all transactions are executed (mainly OLTP workload).
db2 serves as the replica database of db1 where all the reports and DW tasks are executed (mainly DW workload).
db2 has almost all data of db1 (tables a-i) and some extra tables (x,y,z).
We have setup a Transactional Replication (db1_to_db2) with the following (important) options
for the publication:
- @repl_freq = N'continuous'
- @immediate_sync = N'true'
- @replicate_ddl = 1
for the published articles:
- @schema_option = 0x000001410DC37EDF
As there is little maintenance window available for both databases, with the described setup we can:
1. replicate schema changes to the subscriber
2. add new tables to the publication and use a partial snapshot rather than having to reinitialize the entire subscription
This is working like a charm.
But now, a new requirement has come up.
The Problem
Some changes on specific replicated tables, that are updated on db2 directly, should be transferred back to db1.
Let's say that tables b on db2 (that references table a and is referenced by table c through FKs), accepts updates on its data that should travel to table b on db1 and not get replicated back to db2. Only Updates are permitted on table b on db2. Not Inserts or Deletes.
Thoughts
We have tried every possible (simple and easily adopted) solution we could think of:
Setup merge replication instead of transactional.
- It does not accept partial snapshots for newly added articles.
- It adds an extra column which means major changes to the application app1.
FAIL
Transactional replication with updatable subscriber
- It does not accept partial snapshots for newly added articles.
- It adds an extra column which means major changes to the application app1.
FAIL
Update db1.b through a (ON INSERT) trigger on db2.b:
- This is a 2Phase-commit transaction that could fail due to network connectivity issues (which otherwise do not bother us)
- There is no easy way to explude these Updates from being replicated back to db2.b through the replication.
FAIL
Setup a transactional replication from db2 to db1 with table b as the only article.
- Again there is no way to exclude these transactions from being replicated back to db2. It would be nice to have something like a 'NOT FOR REPLICATION' option for these transactions...
FAIL
This is as far as we have gone in search of a solution.
Please help
Please state any idea you might have, taking into account our specific needs.
Forgive my being too analytic but I wanted to give every piece of required information you might need.
sql-server-2008 transactional-replication
asked May 11 '17 at 14:27
Konstantinos KatsoridisKonstantinos Katsoridis
111
Summary
We need a "two-way" (bi-directional) replication topology which should be easy to setup and administer, with minimal downtime when changes are required.
Current solution
We have two SQL Server Instances 2008 R2 Standard, on Servers SQL1 and SQL2.
Server SQL1 has a database db1 with tables a,b,c,d,e,f,g,h,i,j,k.
Server SQL2 has a database db2 with tables a,b,c,d,e,f,g,h,i, x,y,z
db1 serves as the primary database of the application app1, where all transactions are executed (mainly OLTP workload).
db2 serves as the replica database of db1 where all the reports and DW tasks are executed (mainly DW workload).
db2 has almost all data of db1 (tables a-i) and some extra tables (x,y,z).
We have setup a Transactional Replication (db1_to_db2) with the following (important) options
for the publication:
- @repl_freq = N'continuous'
- @immediate_sync = N'true'
- @replicate_ddl = 1
for the published articles:
- @schema_option = 0x000001410DC37EDF
As there is little maintenance window available for both databases, with the described setup we can:
1. replicate schema changes to the subscriber
2. add new tables to the publication and use a partial snapshot rather than having to reinitialize the entire subscription
This is working like a charm.
But now, a new requirement has come up.
The Problem
Some changes on specific replicated tables, that are updated on db2 directly, should be transferred back to db1.
Let's say that tables b on db2 (that references table a and is referenced by table c through FKs), accepts updates on its data that should travel to table b on db1 and not get replicated back to db2. Only Updates are permitted on table b on db2. Not Inserts or Deletes.
Thoughts
We have tried every possible (simple and easily adopted) solution we could think of:
Setup merge replication instead of transactional.
- It does not accept partial snapshots for newly added articles.
- It adds an extra column which means major changes to the application app1.
FAIL
Transactional replication with updatable subscriber
- It does not accept partial snapshots for newly added articles.
- It adds an extra column which means major changes to the application app1.
FAIL
Update db1.b through a (ON INSERT) trigger on db2.b:
- This is a 2Phase-commit transaction that could fail due to network connectivity issues (which otherwise do not bother us)
- There is no easy way to explude these Updates from being replicated back to db2.b through the replication.
FAIL
Setup a transactional replication from db2 to db1 with table b as the only article.
- Again there is no way to exclude these transactions from being replicated back to db2. It would be nice to have something like a 'NOT FOR REPLICATION' option for these transactions...
FAIL
This is as far as we have gone in search of a solution.
Please help
Please state any idea you might have, taking into account our specific needs.
Forgive my being too analytic but I wanted to give every piece of required information you might need.
sql-server-2008 transactional-replication
sql-server-2008 transactional-replication
asked May 11 '17 at 14:27
Konstantinos KatsoridisKonstantinos Katsoridis
111
asked May 11 '17 at 14:27
Konstantinos KatsoridisKonstantinos Katsoridis
111
asked May 11 '17 at 14:27
Konstantinos KatsoridisKonstantinos Katsoridis
111
asked May 11 '17 at 14:27
Konstantinos KatsoridisKonstantinos Katsoridis
111
asked May 11 '17 at 14:27
Konstantinos KatsoridisKonstantinos Katsoridis
111
111
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
+1 for providing all the info and research done so far. have you looked in to Peer-to-Peer Transactional Replication ? or have service broker deliver the changes back to publisher -- might require a bit of more thinking and POC !
– Kin
May 11 '17 at 16:04
Thank you for the ideas. However Peer-to-Peer replication is an Enterprise only feature, so not applicable to this specific project. I haven't thought about Service Broker! It sounds promising though it would mean significant changes in the application. But I will spread the thought to the devs.
– Konstantinos Katsoridis
May 17 '17 at 13:08
I was thinking something similar (to the Service Broker idea) in logic. An INSTEAD OF UPDATE trigger on db2.b that redirects the UPDATES to the db1.b table, which in return will replicate the update to the db2.b through replication. There you have it.Problem solved? Not exactly because you have to deal with the instant response of the initial Update operation that the application has executed...
– Konstantinos Katsoridis
May 17 '17 at 13:09
add a comment |
+1 for providing all the info and research done so far. have you looked in to Peer-to-Peer Transactional Replication ? or have service broker deliver the changes back to publisher -- might require a bit of more thinking and POC !
– Kin
May 11 '17 at 16:04
Thank you for the ideas. However Peer-to-Peer replication is an Enterprise only feature, so not applicable to this specific project. I haven't thought about Service Broker! It sounds promising though it would mean significant changes in the application. But I will spread the thought to the devs.
– Konstantinos Katsoridis
May 17 '17 at 13:08
I was thinking something similar (to the Service Broker idea) in logic. An INSTEAD OF UPDATE trigger on db2.b that redirects the UPDATES to the db1.b table, which in return will replicate the update to the db2.b through replication. There you have it.Problem solved? Not exactly because you have to deal with the instant response of the initial Update operation that the application has executed...
– Konstantinos Katsoridis
May 17 '17 at 13:09
+1 for providing all the info and research done so far. have you looked in to Peer-to-Peer Transactional Replication ? or have service broker deliver the changes back to publisher -- might require a bit of more thinking and POC !
– Kin
May 11 '17 at 16:04
+1 for providing all the info and research done so far. have you looked in to Peer-to-Peer Transactional Replication ? or have service broker deliver the changes back to publisher -- might require a bit of more thinking and POC !
– Kin
May 11 '17 at 16:04
Thank you for the ideas. However Peer-to-Peer replication is an Enterprise only feature, so not applicable to this specific project. I haven't thought about Service Broker! It sounds promising though it would mean significant changes in the application. But I will spread the thought to the devs.
– Konstantinos Katsoridis
May 17 '17 at 13:08
Thank you for the ideas. However Peer-to-Peer replication is an Enterprise only feature, so not applicable to this specific project. I haven't thought about Service Broker! It sounds promising though it would mean significant changes in the application. But I will spread the thought to the devs.
– Konstantinos Katsoridis
May 17 '17 at 13:08
I was thinking something similar (to the Service Broker idea) in logic. An INSTEAD OF UPDATE trigger on db2.b that redirects the UPDATES to the db1.b table, which in return will replicate the update to the db2.b through replication. There you have it.Problem solved? Not exactly because you have to deal with the instant response of the initial Update operation that the application has executed...
– Konstantinos Katsoridis
May 17 '17 at 13:09
I was thinking something similar (to the Service Broker idea) in logic. An INSTEAD OF UPDATE trigger on db2.b that redirects the UPDATES to the db1.b table, which in return will replicate the update to the db2.b through replication. There you have it.Problem solved? Not exactly because you have to deal with the instant response of the initial Update operation that the application has executed...
– Konstantinos Katsoridis
May 17 '17 at 13:09
add a comment |
1 Answer
1
active
oldest
votes
After all it seems that the answer to my question was #4 (Setup a transactional replication from db2 to db1 with table b as the only article)
There is a not-so-advertised option for the subscriptions (at least not to me) called @loopback_detection, which makes sure that transaction are not replicated back to their originator!
This means that you can set up a two-way transactional replication, plain and simple, and accomplish what I was asking for: a subscriber that can update specific tables, so that these changes are replicated to the publisher (and never come back to the subscriber).
You have to pay attention to some options however, to avoid messing things up when setting up a two-way transactional replication:
- When adding articles to the "reverse" publication:
@pre_creation_cmd = N'none' -- so that the original tables are not dropped/recreated
- When setting up the "reverse" subscription you should set:
@sync_type = N'replication support only' -- so that no snapshot is required to initiate the subascriber
@loopback_detection = 'true' (which is set by default)
These are the steps that need special attention. Other than that, it is a straight forward process.
When you have everything set up, you can test the reverse replication by executing an update on the replica db, while you have the "View synchronization status" windows for both replications open.
You will notice that only the reverse replication will log a "1 transaction(s) with 1 command(s) were delivered." message.
PS. I will provide a detailed description of the setup process, shortly.
answered Jun 20 '17 at 13:25
Konstantinos KatsoridisKonstantinos Katsoridis
111
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f173333%2ftransactional-replication-with-updates-on-the-subscriber%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
After all it seems that the answer to my question was #4 (Setup a transactional replication from db2 to db1 with table b as the only article)
There is a not-so-advertised option for the subscriptions (at least not to me) called @loopback_detection, which makes sure that transaction are not replicated back to their originator!
This means that you can set up a two-way transactional replication, plain and simple, and accomplish what I was asking for: a subscriber that can update specific tables, so that these changes are replicated to the publisher (and never come back to the subscriber).
You have to pay attention to some options however, to avoid messing things up when setting up a two-way transactional replication:
- When adding articles to the "reverse" publication:
@pre_creation_cmd = N'none' -- so that the original tables are not dropped/recreated
- When setting up the "reverse" subscription you should set:
@sync_type = N'replication support only' -- so that no snapshot is required to initiate the subascriber
@loopback_detection = 'true' (which is set by default)
These are the steps that need special attention. Other than that, it is a straight forward process.
When you have everything set up, you can test the reverse replication by executing an update on the replica db, while you have the "View synchronization status" windows for both replications open.
You will notice that only the reverse replication will log a "1 transaction(s) with 1 command(s) were delivered." message.
PS. I will provide a detailed description of the setup process, shortly.
answered Jun 20 '17 at 13:25
Konstantinos KatsoridisKonstantinos Katsoridis
111
add a comment |
After all it seems that the answer to my question was #4 (Setup a transactional replication from db2 to db1 with table b as the only article)
There is a not-so-advertised option for the subscriptions (at least not to me) called @loopback_detection, which makes sure that transaction are not replicated back to their originator!
This means that you can set up a two-way transactional replication, plain and simple, and accomplish what I was asking for: a subscriber that can update specific tables, so that these changes are replicated to the publisher (and never come back to the subscriber).
You have to pay attention to some options however, to avoid messing things up when setting up a two-way transactional replication:
- When adding articles to the "reverse" publication:
@pre_creation_cmd = N'none' -- so that the original tables are not dropped/recreated
- When setting up the "reverse" subscription you should set:
@sync_type = N'replication support only' -- so that no snapshot is required to initiate the subascriber
@loopback_detection = 'true' (which is set by default)
These are the steps that need special attention. Other than that, it is a straight forward process.
When you have everything set up, you can test the reverse replication by executing an update on the replica db, while you have the "View synchronization status" windows for both replications open.
You will notice that only the reverse replication will log a "1 transaction(s) with 1 command(s) were delivered." message.
PS. I will provide a detailed description of the setup process, shortly.
answered Jun 20 '17 at 13:25
Konstantinos KatsoridisKonstantinos Katsoridis
111
add a comment |
After all it seems that the answer to my question was #4 (Setup a transactional replication from db2 to db1 with table b as the only article)
There is a not-so-advertised option for the subscriptions (at least not to me) called @loopback_detection, which makes sure that transaction are not replicated back to their originator!
This means that you can set up a two-way transactional replication, plain and simple, and accomplish what I was asking for: a subscriber that can update specific tables, so that these changes are replicated to the publisher (and never come back to the subscriber).
You have to pay attention to some options however, to avoid messing things up when setting up a two-way transactional replication:
- When adding articles to the "reverse" publication:
@pre_creation_cmd = N'none' -- so that the original tables are not dropped/recreated
- When setting up the "reverse" subscription you should set:
@sync_type = N'replication support only' -- so that no snapshot is required to initiate the subascriber
@loopback_detection = 'true' (which is set by default)
These are the steps that need special attention. Other than that, it is a straight forward process.
When you have everything set up, you can test the reverse replication by executing an update on the replica db, while you have the "View synchronization status" windows for both replications open.
You will notice that only the reverse replication will log a "1 transaction(s) with 1 command(s) were delivered." message.
PS. I will provide a detailed description of the setup process, shortly.
answered Jun 20 '17 at 13:25
Konstantinos KatsoridisKonstantinos Katsoridis
111
After all it seems that the answer to my question was #4 (Setup a transactional replication from db2 to db1 with table b as the only article)
There is a not-so-advertised option for the subscriptions (at least not to me) called @loopback_detection, which makes sure that transaction are not replicated back to their originator!
This means that you can set up a two-way transactional replication, plain and simple, and accomplish what I was asking for: a subscriber that can update specific tables, so that these changes are replicated to the publisher (and never come back to the subscriber).
You have to pay attention to some options however, to avoid messing things up when setting up a two-way transactional replication:
- When adding articles to the "reverse" publication:
@pre_creation_cmd = N'none' -- so that the original tables are not dropped/recreated
- When setting up the "reverse" subscription you should set:
@sync_type = N'replication support only' -- so that no snapshot is required to initiate the subascriber
@loopback_detection = 'true' (which is set by default)
These are the steps that need special attention. Other than that, it is a straight forward process.
When you have everything set up, you can test the reverse replication by executing an update on the replica db, while you have the "View synchronization status" windows for both replications open.
You will notice that only the reverse replication will log a "1 transaction(s) with 1 command(s) were delivered." message.
PS. I will provide a detailed description of the setup process, shortly.
answered Jun 20 '17 at 13:25
Konstantinos KatsoridisKonstantinos Katsoridis
111
answered Jun 20 '17 at 13:25
Konstantinos KatsoridisKonstantinos Katsoridis
111
answered Jun 20 '17 at 13:25
Konstantinos KatsoridisKonstantinos Katsoridis
111
answered Jun 20 '17 at 13:25
Konstantinos KatsoridisKonstantinos Katsoridis
111
111
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f173333%2ftransactional-replication-with-updates-on-the-subscriber%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
+1 for providing all the info and research done so far. have you looked in to Peer-to-Peer Transactional Replication ? or have service broker deliver the changes back to publisher -- might require a bit of more thinking and POC !
– Kin
May 11 '17 at 16:04
Thank you for the ideas. However Peer-to-Peer replication is an Enterprise only feature, so not applicable to this specific project. I haven't thought about Service Broker! It sounds promising though it would mean significant changes in the application. But I will spread the thought to the devs.
– Konstantinos Katsoridis
May 17 '17 at 13:08
I was thinking something similar (to the Service Broker idea) in logic. An INSTEAD OF UPDATE trigger on db2.b that redirects the UPDATES to the db1.b table, which in return will replicate the update to the db2.b through replication. There you have it.Problem solved? Not exactly because you have to deal with the instant response of the initial Update operation that the application has executed...
– Konstantinos Katsoridis
May 17 '17 at 13:09