Temporarily removing replication from Publisher
Is it possible to temporarily remove Transactional Replication from a publisher database in order to allow a release to be executed against the database?
If I stopped the log reader agent would I be able to perform DDL/DML changes against the publisher database without error?
The product vendor has a bespoke tool which issues the T-SQL commands against the database for the release, which will include dropping and recreating constraints, keys, indexes etc. I need to make sure releases can happen without error caused by replication - the database needs to allow the changes.
Publisher and Distribution db live on the same server. Both publisher and subscriber are SQL 2014 Enterprise. I am replicating the entire database.
Help is appreciated.
Thanks
Peter
Update: Yes I am replicating schema changes
Update: Is this the answer? https://stackoverflow.com/questions/15673829/resume-replication-after-restoring-publication-database
Update: I've tested our vendor's release on a published database and found that dropping stored procedures is the first issue we see.
sql-server sql-server-2014 transactional-replication
edited May 23 '17 at 12:40
Community♦
1
asked Sep 22 '15 at 15:30
PeterPeter
7431726
bumped to the homepage by Community♦ 10 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 |
Is it possible to temporarily remove Transactional Replication from a publisher database in order to allow a release to be executed against the database?
If I stopped the log reader agent would I be able to perform DDL/DML changes against the publisher database without error?
The product vendor has a bespoke tool which issues the T-SQL commands against the database for the release, which will include dropping and recreating constraints, keys, indexes etc. I need to make sure releases can happen without error caused by replication - the database needs to allow the changes.
Publisher and Distribution db live on the same server. Both publisher and subscriber are SQL 2014 Enterprise. I am replicating the entire database.
Help is appreciated.
Thanks
Peter
Update: Yes I am replicating schema changes
Update: Is this the answer? https://stackoverflow.com/questions/15673829/resume-replication-after-restoring-publication-database
Update: I've tested our vendor's release on a published database and found that dropping stored procedures is the first issue we see.
sql-server sql-server-2014 transactional-replication
edited May 23 '17 at 12:40
Community♦
1
asked Sep 22 '15 at 15:30
PeterPeter
7431726
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Are you replicating schema changes (DDL) ?
– Spörri
Sep 22 '15 at 16:29
You should be able to make DDL changes as described in the answer. Make sure your vendor is not doing anything drastic - dropping PK, etc - replication wont allow that. Also, since you are using SQL Server 2014 Enterprise Edition, you can think of using AlwaysON and you can leverage your read requests to secondary server. Just putting it as food for thoughts.
– Kin
Sep 23 '15 at 1:23
add a comment |
Is it possible to temporarily remove Transactional Replication from a publisher database in order to allow a release to be executed against the database?
If I stopped the log reader agent would I be able to perform DDL/DML changes against the publisher database without error?
The product vendor has a bespoke tool which issues the T-SQL commands against the database for the release, which will include dropping and recreating constraints, keys, indexes etc. I need to make sure releases can happen without error caused by replication - the database needs to allow the changes.
Publisher and Distribution db live on the same server. Both publisher and subscriber are SQL 2014 Enterprise. I am replicating the entire database.
Help is appreciated.
Thanks
Peter
Update: Yes I am replicating schema changes
Update: Is this the answer? https://stackoverflow.com/questions/15673829/resume-replication-after-restoring-publication-database
Update: I've tested our vendor's release on a published database and found that dropping stored procedures is the first issue we see.
sql-server sql-server-2014 transactional-replication
edited May 23 '17 at 12:40
Community♦
1
asked Sep 22 '15 at 15:30
PeterPeter
7431726
Is it possible to temporarily remove Transactional Replication from a publisher database in order to allow a release to be executed against the database?
If I stopped the log reader agent would I be able to perform DDL/DML changes against the publisher database without error?
The product vendor has a bespoke tool which issues the T-SQL commands against the database for the release, which will include dropping and recreating constraints, keys, indexes etc. I need to make sure releases can happen without error caused by replication - the database needs to allow the changes.
Publisher and Distribution db live on the same server. Both publisher and subscriber are SQL 2014 Enterprise. I am replicating the entire database.
Help is appreciated.
Thanks
Peter
Update: Yes I am replicating schema changes
Update: Is this the answer? https://stackoverflow.com/questions/15673829/resume-replication-after-restoring-publication-database
Update: I've tested our vendor's release on a published database and found that dropping stored procedures is the first issue we see.
sql-server sql-server-2014 transactional-replication
sql-server sql-server-2014 transactional-replication
edited May 23 '17 at 12:40
Community♦
1
asked Sep 22 '15 at 15:30
PeterPeter
7431726
edited May 23 '17 at 12:40
Community♦
1
asked Sep 22 '15 at 15:30
PeterPeter
7431726
edited May 23 '17 at 12:40
Community♦
1
edited May 23 '17 at 12:40
Community♦
1
edited May 23 '17 at 12:40
Community♦
1
1
asked Sep 22 '15 at 15:30
PeterPeter
7431726
asked Sep 22 '15 at 15:30
PeterPeter
7431726
asked Sep 22 '15 at 15:30
PeterPeter
7431726
7431726
bumped to the homepage by Community♦ 10 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♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Are you replicating schema changes (DDL) ?
– Spörri
Sep 22 '15 at 16:29
You should be able to make DDL changes as described in the answer. Make sure your vendor is not doing anything drastic - dropping PK, etc - replication wont allow that. Also, since you are using SQL Server 2014 Enterprise Edition, you can think of using AlwaysON and you can leverage your read requests to secondary server. Just putting it as food for thoughts.
– Kin
Sep 23 '15 at 1:23
add a comment |
Are you replicating schema changes (DDL) ?
– Spörri
Sep 22 '15 at 16:29
You should be able to make DDL changes as described in the answer. Make sure your vendor is not doing anything drastic - dropping PK, etc - replication wont allow that. Also, since you are using SQL Server 2014 Enterprise Edition, you can think of using AlwaysON and you can leverage your read requests to secondary server. Just putting it as food for thoughts.
– Kin
Sep 23 '15 at 1:23
Are you replicating schema changes (DDL) ?
– Spörri
Sep 22 '15 at 16:29
Are you replicating schema changes (DDL) ?
– Spörri
Sep 22 '15 at 16:29
You should be able to make DDL changes as described in the answer. Make sure your vendor is not doing anything drastic - dropping PK, etc - replication wont allow that. Also, since you are using SQL Server 2014 Enterprise Edition, you can think of using AlwaysON and you can leverage your read requests to secondary server. Just putting it as food for thoughts.
– Kin
Sep 23 '15 at 1:23
You should be able to make DDL changes as described in the answer. Make sure your vendor is not doing anything drastic - dropping PK, etc - replication wont allow that. Also, since you are using SQL Server 2014 Enterprise Edition, you can think of using AlwaysON and you can leverage your read requests to secondary server. Just putting it as food for thoughts.
– Kin
Sep 23 '15 at 1:23
add a comment |
1 Answer
1
active
oldest
votes
This seems to be the best method: Script the Publication -> Drop Subscriptions -> Drop Publication -> Re-create them
I was hoping there would be a 'trick' with replication where I could leave subscribers in place and just script out the publisher and recreate it. I don't see a clear way to restart replication without doing a full rebuild/reinitialise.
What I have noted is that this method does work pretty well once you have established replication for a database, worked out all the issues, scripted it all out; running those scripts does simplify the process.
Hope this helps someone
answered Oct 4 '15 at 8:53
PeterPeter
7431726
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%2f115786%2ftemporarily-removing-replication-from-publisher%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
This seems to be the best method: Script the Publication -> Drop Subscriptions -> Drop Publication -> Re-create them
I was hoping there would be a 'trick' with replication where I could leave subscribers in place and just script out the publisher and recreate it. I don't see a clear way to restart replication without doing a full rebuild/reinitialise.
What I have noted is that this method does work pretty well once you have established replication for a database, worked out all the issues, scripted it all out; running those scripts does simplify the process.
Hope this helps someone
answered Oct 4 '15 at 8:53
PeterPeter
7431726
add a comment |
This seems to be the best method: Script the Publication -> Drop Subscriptions -> Drop Publication -> Re-create them
I was hoping there would be a 'trick' with replication where I could leave subscribers in place and just script out the publisher and recreate it. I don't see a clear way to restart replication without doing a full rebuild/reinitialise.
What I have noted is that this method does work pretty well once you have established replication for a database, worked out all the issues, scripted it all out; running those scripts does simplify the process.
Hope this helps someone
answered Oct 4 '15 at 8:53
PeterPeter
7431726
add a comment |
This seems to be the best method: Script the Publication -> Drop Subscriptions -> Drop Publication -> Re-create them
I was hoping there would be a 'trick' with replication where I could leave subscribers in place and just script out the publisher and recreate it. I don't see a clear way to restart replication without doing a full rebuild/reinitialise.
What I have noted is that this method does work pretty well once you have established replication for a database, worked out all the issues, scripted it all out; running those scripts does simplify the process.
Hope this helps someone
answered Oct 4 '15 at 8:53
PeterPeter
7431726
This seems to be the best method: Script the Publication -> Drop Subscriptions -> Drop Publication -> Re-create them
I was hoping there would be a 'trick' with replication where I could leave subscribers in place and just script out the publisher and recreate it. I don't see a clear way to restart replication without doing a full rebuild/reinitialise.
What I have noted is that this method does work pretty well once you have established replication for a database, worked out all the issues, scripted it all out; running those scripts does simplify the process.
Hope this helps someone
answered Oct 4 '15 at 8:53
PeterPeter
7431726
answered Oct 4 '15 at 8:53
PeterPeter
7431726
answered Oct 4 '15 at 8:53
PeterPeter
7431726
answered Oct 4 '15 at 8:53
PeterPeter
7431726
7431726
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%2f115786%2ftemporarily-removing-replication-from-publisher%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
Are you replicating schema changes (DDL) ?
– Spörri
Sep 22 '15 at 16:29
You should be able to make DDL changes as described in the answer. Make sure your vendor is not doing anything drastic - dropping PK, etc - replication wont allow that. Also, since you are using SQL Server 2014 Enterprise Edition, you can think of using AlwaysON and you can leverage your read requests to secondary server. Just putting it as food for thoughts.
– Kin
Sep 23 '15 at 1:23