SQL Server Replication

We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table. Replication converted that to 7.5 million individual update statements. When my alerting notified me that our publication was behind our threshold, I started to examine the issue.

I discovered that the updated was executed and it would have taken a few days to chew through those update statements. So I decided to see if we could skip over those records that it was trying to process. Using the system tables and stored procedure I was able to determine the period of time that the updated was executed. Once I found the last xact_seqno, I stopped the distribution agent, and did the update manually to the subscriber database. I then executed sp_setsubscriptionxactseqno, to skip past all of those 7.5 million transactions. When I started up the Distribution Agent, it seemed to have worked and was able to process the remaining transactions.

For good measure I use Redgate’s data compare to see if the data was messed up, but I was missing about 24 records (which could have been not there originally because I didn’t set it up).

My question is was that the right way to fix it? How are you always assured to get the next Xact_seqno? Do you order the transactions by Xact_Seqno or entry_time?

edited Jun 8 '16 at 13:40

SQLPRODDBA

1,09511430

asked Jun 8 '16 at 12:08

Derek Czarny

161

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.

Can you please provide details about SQL Server version and type of replication?

– SQLPRODDBA
Jun 8 '16 at 12:35

The issue was from the distributor to subscriber, which was on the same server (SQL Server 2012). Transactional replication.

– Derek Czarny
Jun 8 '16 at 12:40

add a comment |

For good measure I use Redgate’s data compare to see if the data was messed up, but I was missing about 24 records (which could have been not there originally because I didn’t set it up).

My question is was that the right way to fix it? How are you always assured to get the next Xact_seqno? Do you order the transactions by Xact_Seqno or entry_time?

edited Jun 8 '16 at 13:40

SQLPRODDBA

1,09511430

asked Jun 8 '16 at 12:08

Derek Czarny

161

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.

Can you please provide details about SQL Server version and type of replication?

– SQLPRODDBA
Jun 8 '16 at 12:35

The issue was from the distributor to subscriber, which was on the same server (SQL Server 2012). Transactional replication.

– Derek Czarny
Jun 8 '16 at 12:40

add a comment |

For good measure I use Redgate’s data compare to see if the data was messed up, but I was missing about 24 records (which could have been not there originally because I didn’t set it up).

My question is was that the right way to fix it? How are you always assured to get the next Xact_seqno? Do you order the transactions by Xact_Seqno or entry_time?

edited Jun 8 '16 at 13:40

SQLPRODDBA

1,09511430

asked Jun 8 '16 at 12:08

Derek Czarny

161

For good measure I use Redgate’s data compare to see if the data was messed up, but I was missing about 24 records (which could have been not there originally because I didn’t set it up).

My question is was that the right way to fix it? How are you always assured to get the next Xact_seqno? Do you order the transactions by Xact_Seqno or entry_time?

sql-server sql-server-2012 replication transactional-replication

edited Jun 8 '16 at 13:40

SQLPRODDBA

1,09511430

asked Jun 8 '16 at 12:08

Derek Czarny

161

edited Jun 8 '16 at 13:40

SQLPRODDBA

1,09511430

asked Jun 8 '16 at 12:08

Derek Czarny

161

edited Jun 8 '16 at 13:40

SQLPRODDBA

1,09511430

edited Jun 8 '16 at 13:40

SQLPRODDBA

1,09511430

edited Jun 8 '16 at 13:40

SQLPRODDBA

1,09511430

asked Jun 8 '16 at 12:08

Derek Czarny

161

asked Jun 8 '16 at 12:08

Derek Czarny

161

asked Jun 8 '16 at 12:08

Derek Czarny

161

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.

Can you please provide details about SQL Server version and type of replication?

– SQLPRODDBA
Jun 8 '16 at 12:35

The issue was from the distributor to subscriber, which was on the same server (SQL Server 2012). Transactional replication.

– Derek Czarny
Jun 8 '16 at 12:40

add a comment |

Can you please provide details about SQL Server version and type of replication?

– SQLPRODDBA
Jun 8 '16 at 12:35

The issue was from the distributor to subscriber, which was on the same server (SQL Server 2012). Transactional replication.

– Derek Czarny
Jun 8 '16 at 12:40

Can you please provide details about SQL Server version and type of replication?

– SQLPRODDBA
Jun 8 '16 at 12:35

The issue was from the distributor to subscriber, which was on the same server (SQL Server 2012). Transactional replication.

– Derek Czarny
Jun 8 '16 at 12:40

add a comment |

1 Answer
1

active

oldest

votes

An alternative would to remove the article from the publication and re add, causing a new snapshot to be taken of the table. This way there's no question whether the right data is in the subscriber.

To do this you need to make sure the publication properties are set to FALSE for allow_anonymous and immediate_sync - if either of these are true then the a database snapshot will be taken when you remove and re add table rather than just the table.

To check run sp_helppublication against the distribution.

To change if either of these are set to 1:

EXEC sp_changepublication

@publication = 'REP_P', --Enter your publication_name

@property = 'allow_anonymous' ,

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'REP_P', --Enter your publication name

@property = 'immediate_sync' , 

@value = 'false' 

GO

I have tested this and it works in my environment, however strongly suggest you do this in a lab first.

edited Jun 8 '16 at 13:57

SQLPRODDBA

1,09511430

answered Jun 8 '16 at 13:27

Hpk

8910

Yes, but what if the updated was already executed and you are just troubleshooting after the fact? Would you still remove the table from replication and add it back?

– Derek Czarny
Jun 8 '16 at 13:37

@Hpk IMO it may work when you have small table but still have to test it though. What if we have very large table. Re-snapshot would be pain in that case.

– SQLPRODDBA
Jun 8 '16 at 13:40

@DerekCzarny - Its an alternative. In my environment unless I would have to prove I tested my method and prove data is intact to my Manager, it would also need to go through CAB etc due to the non standard process. I guess its up to your environment. My method is a sure fire way no questions asked that the publisher matches the subscriber. In terms of table size 33 million records is not large unless its very wide or contains large data types.

– Hpk
Jun 8 '16 at 14:45

If you used distribution.dbo.msRepl_transactions and distribution.dbo.msRepl_commands to get the update commands, restrict down to the applicable article(s) then your method looks ok. xact_seqno is sequential and is the right thing to order by.

– Hpk
Jun 8 '16 at 14:59

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f140719%2fsql-server-replication%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

An alternative would to remove the article from the publication and re add, causing a new snapshot to be taken of the table. This way there's no question whether the right data is in the subscriber.

To check run sp_helppublication against the distribution.

To change if either of these are set to 1:

EXEC sp_changepublication

@publication = 'REP_P', --Enter your publication_name

@property = 'allow_anonymous' ,

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'REP_P', --Enter your publication name

@property = 'immediate_sync' , 

@value = 'false' 

GO

I have tested this and it works in my environment, however strongly suggest you do this in a lab first.

edited Jun 8 '16 at 13:57

SQLPRODDBA

1,09511430

answered Jun 8 '16 at 13:27

Hpk

8910

Yes, but what if the updated was already executed and you are just troubleshooting after the fact? Would you still remove the table from replication and add it back?

– Derek Czarny
Jun 8 '16 at 13:37

@Hpk IMO it may work when you have small table but still have to test it though. What if we have very large table. Re-snapshot would be pain in that case.

– SQLPRODDBA
Jun 8 '16 at 13:40

@DerekCzarny - Its an alternative. In my environment unless I would have to prove I tested my method and prove data is intact to my Manager, it would also need to go through CAB etc due to the non standard process. I guess its up to your environment. My method is a sure fire way no questions asked that the publisher matches the subscriber. In terms of table size 33 million records is not large unless its very wide or contains large data types.

– Hpk
Jun 8 '16 at 14:45

If you used distribution.dbo.msRepl_transactions and distribution.dbo.msRepl_commands to get the update commands, restrict down to the applicable article(s) then your method looks ok. xact_seqno is sequential and is the right thing to order by.

– Hpk
Jun 8 '16 at 14:59

add a comment |

An alternative would to remove the article from the publication and re add, causing a new snapshot to be taken of the table. This way there's no question whether the right data is in the subscriber.

To check run sp_helppublication against the distribution.

To change if either of these are set to 1:

EXEC sp_changepublication

@publication = 'REP_P', --Enter your publication_name

@property = 'allow_anonymous' ,

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'REP_P', --Enter your publication name

@property = 'immediate_sync' , 

@value = 'false' 

GO

I have tested this and it works in my environment, however strongly suggest you do this in a lab first.

edited Jun 8 '16 at 13:57

SQLPRODDBA

1,09511430

answered Jun 8 '16 at 13:27

Hpk

8910

Yes, but what if the updated was already executed and you are just troubleshooting after the fact? Would you still remove the table from replication and add it back?

– Derek Czarny
Jun 8 '16 at 13:37

@Hpk IMO it may work when you have small table but still have to test it though. What if we have very large table. Re-snapshot would be pain in that case.

– SQLPRODDBA
Jun 8 '16 at 13:40

@DerekCzarny - Its an alternative. In my environment unless I would have to prove I tested my method and prove data is intact to my Manager, it would also need to go through CAB etc due to the non standard process. I guess its up to your environment. My method is a sure fire way no questions asked that the publisher matches the subscriber. In terms of table size 33 million records is not large unless its very wide or contains large data types.

– Hpk
Jun 8 '16 at 14:45

If you used distribution.dbo.msRepl_transactions and distribution.dbo.msRepl_commands to get the update commands, restrict down to the applicable article(s) then your method looks ok. xact_seqno is sequential and is the right thing to order by.

– Hpk
Jun 8 '16 at 14:59

add a comment |

An alternative would to remove the article from the publication and re add, causing a new snapshot to be taken of the table. This way there's no question whether the right data is in the subscriber.

To check run sp_helppublication against the distribution.

To change if either of these are set to 1:

EXEC sp_changepublication

@publication = 'REP_P', --Enter your publication_name

@property = 'allow_anonymous' ,

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'REP_P', --Enter your publication name

@property = 'immediate_sync' , 

@value = 'false' 

GO

I have tested this and it works in my environment, however strongly suggest you do this in a lab first.

edited Jun 8 '16 at 13:57

SQLPRODDBA

1,09511430

answered Jun 8 '16 at 13:27

Hpk

8910

An alternative would to remove the article from the publication and re add, causing a new snapshot to be taken of the table. This way there's no question whether the right data is in the subscriber.

To check run sp_helppublication against the distribution.

To change if either of these are set to 1:

EXEC sp_changepublication

@publication = 'REP_P', --Enter your publication_name

@property = 'allow_anonymous' ,

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'REP_P', --Enter your publication name

@property = 'immediate_sync' , 

@value = 'false' 

GO

I have tested this and it works in my environment, however strongly suggest you do this in a lab first.

edited Jun 8 '16 at 13:57

SQLPRODDBA

1,09511430

answered Jun 8 '16 at 13:27

Hpk

8910

edited Jun 8 '16 at 13:57

SQLPRODDBA

1,09511430

edited Jun 8 '16 at 13:57

SQLPRODDBA

1,09511430

edited Jun 8 '16 at 13:57

SQLPRODDBA

1,09511430

answered Jun 8 '16 at 13:27

Hpk

8910

answered Jun 8 '16 at 13:27

Hpk

8910

answered Jun 8 '16 at 13:27

Hpk

8910

Yes, but what if the updated was already executed and you are just troubleshooting after the fact? Would you still remove the table from replication and add it back?

– Derek Czarny
Jun 8 '16 at 13:37

@Hpk IMO it may work when you have small table but still have to test it though. What if we have very large table. Re-snapshot would be pain in that case.

– SQLPRODDBA
Jun 8 '16 at 13:40

@DerekCzarny - Its an alternative. In my environment unless I would have to prove I tested my method and prove data is intact to my Manager, it would also need to go through CAB etc due to the non standard process. I guess its up to your environment. My method is a sure fire way no questions asked that the publisher matches the subscriber. In terms of table size 33 million records is not large unless its very wide or contains large data types.

– Hpk
Jun 8 '16 at 14:45

If you used distribution.dbo.msRepl_transactions and distribution.dbo.msRepl_commands to get the update commands, restrict down to the applicable article(s) then your method looks ok. xact_seqno is sequential and is the right thing to order by.

– Hpk
Jun 8 '16 at 14:59

add a comment |

Yes, but what if the updated was already executed and you are just troubleshooting after the fact? Would you still remove the table from replication and add it back?

– Derek Czarny
Jun 8 '16 at 13:37

@Hpk IMO it may work when you have small table but still have to test it though. What if we have very large table. Re-snapshot would be pain in that case.

– SQLPRODDBA
Jun 8 '16 at 13:40

@DerekCzarny - Its an alternative. In my environment unless I would have to prove I tested my method and prove data is intact to my Manager, it would also need to go through CAB etc due to the non standard process. I guess its up to your environment. My method is a sure fire way no questions asked that the publisher matches the subscriber. In terms of table size 33 million records is not large unless its very wide or contains large data types.

– Hpk
Jun 8 '16 at 14:45

If you used distribution.dbo.msRepl_transactions and distribution.dbo.msRepl_commands to get the update commands, restrict down to the applicable article(s) then your method looks ok. xact_seqno is sequential and is the right thing to order by.

– Hpk
Jun 8 '16 at 14:59

Yes, but what if the updated was already executed and you are just troubleshooting after the fact? Would you still remove the table from replication and add it back?

– Derek Czarny
Jun 8 '16 at 13:37

@Hpk IMO it may work when you have small table but still have to test it though. What if we have very large table. Re-snapshot would be pain in that case.

– SQLPRODDBA
Jun 8 '16 at 13:40

@DerekCzarny - Its an alternative. In my environment unless I would have to prove I tested my method and prove data is intact to my Manager, it would also need to go through CAB etc due to the non standard process. I guess its up to your environment. My method is a sure fire way no questions asked that the publisher matches the subscriber. In terms of table size 33 million records is not large unless its very wide or contains large data types.

– Hpk
Jun 8 '16 at 14:45

If you used distribution.dbo.msRepl_transactions and distribution.dbo.msRepl_commands to get the update commands, restrict down to the applicable article(s) then your method looks ok. xact_seqno is sequential and is the right thing to order by.

– Hpk
Jun 8 '16 at 14:59

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Xrhrft