February 20, 2018 at 9:58 am
Hi,
Earlier on today one of our DBs started to fall behind and isn't replicating anymore - normally opening up Replication Monitor would reveal some red crosses but all green and says it is delivering transactions?
How can I find out why it stopped working - we haven't made any changes today.
February 20, 2018 at 10:55 am
steve.roberts 86619 - Tuesday, February 20, 2018 9:58 AMHi,Earlier on today one of our DBs started to fall behind and isn't replicating anymore - normally opening up Replication Monitor would reveal some red crosses but all green and says it is delivering transactions?
How can I find out why it stopped working - we haven't made any changes today.
Check if all of the agents are running.
In the distribution databases, check the delivered and undelivered transactions by querying MSdistribution_status
In the distribution database, query the tables MSrepl_errors and MSdistribution_history.
You can also query the history tables in the distribution databases - query whichever ones apply to whatever type of replication you are using such as MSlogreader_history for transactional, MSmerge_history for merge, MSsnapshot_history for snapshot, etc)
Sue
February 20, 2018 at 11:03 am
Hi,
Thanks I think I know what has happened today.
We have a production Sage ERP DB and two subscribers to the DB. All good.
Someone rang a SQL update script on the Publisher to reset some values. All OK no problems with doing that. These changes have all filtered though to Subscriber 1 but not 2. Running SQL Profiler on Sub2 shows loads of these entries
exec [sp_MSupd_dboStockItem] default,default,default,5038673022,default,default,default,default,default,default,default,
Which is it updating the table in question but it seems to keep looping round and you see the same values appear in the numeric bit over and over again. Basically I think it is stuck in a loop and this is back is it going out of sync?
February 20, 2018 at 1:56 pm
steve.roberts 86619 - Tuesday, February 20, 2018 11:03 AMHi,Thanks I think I know what has happened today.
We have a production Sage ERP DB and two subscribers to the DB. All good.
Someone rang a SQL update script on the Publisher to reset some values. All OK no problems with doing that. These changes have all filtered though to Subscriber 1 but not 2. Running SQL Profiler on Sub2 shows loads of these entriesexec [sp_MSupd_dboStockItem] default,default,default,5038673022,default,default,default,default,default,default,default,
Which is it updating the table in question but it seems to keep looping round and you see the same values appear in the numeric bit over and over again. Basically I think it is stuck in a loop and this is back is it going out of sync?
Assuming it's transactional replication, in the distribution database, execute sp_browsereplcmds to see what's in the queue. Watch to see if xact_seqno is changing at all. The command column has which stored procedure would be executing.
In the publisher database you can see if things are not replicated yet executing sp_repltrans. In the publisher you can also do DBCC OPENTRAN and check the Oldest non-distributed LSN. It it reports (0:0:0) then the log reader is caught up.
Sue
February 20, 2018 at 2:04 pm
Sue_H - Tuesday, February 20, 2018 1:56 PMsteve.roberts 86619 - Tuesday, February 20, 2018 11:03 AMHi,Thanks I think I know what has happened today.
We have a production Sage ERP DB and two subscribers to the DB. All good.
Someone rang a SQL update script on the Publisher to reset some values. All OK no problems with doing that. These changes have all filtered though to Subscriber 1 but not 2. Running SQL Profiler on Sub2 shows loads of these entriesexec [sp_MSupd_dboStockItem] default,default,default,5038673022,default,default,default,default,default,default,default,
Which is it updating the table in question but it seems to keep looping round and you see the same values appear in the numeric bit over and over again. Basically I think it is stuck in a loop and this is back is it going out of sync?
Assuming it's transactional replication, in the distribution database, execute sp_browsereplcmds to see what's in the queue. Watch to see if xact_seqno is changing at all. The command column has which stored procedure would be executing.
In the publisher database you can see if things are not replicated yet executing sp_repltrans. In the publisher you can also do DBCC OPENTRAN and check the Oldest non-distributed LSN. It it reports (0:0:0) then the log reader is caught up.
Sue
Hi it seems to be a volume thing - it is now catching up with the other tables after processing all the updates for the stockitem table - which is weird
Any good SQL tools for monitoring and fixing replication?
Also how come repl monitor reported all ok when it had millions of records to process and a tracer token took hours to get across?
February 20, 2018 at 2:26 pm
steve.roberts 86619 - Tuesday, February 20, 2018 2:04 PMSue_H - Tuesday, February 20, 2018 1:56 PMsteve.roberts 86619 - Tuesday, February 20, 2018 11:03 AMHi,Thanks I think I know what has happened today.
We have a production Sage ERP DB and two subscribers to the DB. All good.
Someone rang a SQL update script on the Publisher to reset some values. All OK no problems with doing that. These changes have all filtered though to Subscriber 1 but not 2. Running SQL Profiler on Sub2 shows loads of these entriesexec [sp_MSupd_dboStockItem] default,default,default,5038673022,default,default,default,default,default,default,default,
Which is it updating the table in question but it seems to keep looping round and you see the same values appear in the numeric bit over and over again. Basically I think it is stuck in a loop and this is back is it going out of sync?
Assuming it's transactional replication, in the distribution database, execute sp_browsereplcmds to see what's in the queue. Watch to see if xact_seqno is changing at all. The command column has which stored procedure would be executing.
In the publisher database you can see if things are not replicated yet executing sp_repltrans. In the publisher you can also do DBCC OPENTRAN and check the Oldest non-distributed LSN. It it reports (0:0:0) then the log reader is caught up.
Sue
Hi it seems to be a volume thing - it is now catching up with the other tables after processing all the updates for the stockitem table - which is weird
Any good SQL tools for monitoring and fixing replication?
Also how come repl monitor reported all ok when it had millions of records to process and a tracer token took hours to get across?
It could have been actively processing a large batch so replication monitor wouldn't report anything. Viewing the subscription details and the Undistributed commands would or should have shown the undistributed commands.
Tracer tokens aren't necessarily very good for monitoring but in your case it likely waited behind whatever other transactions you had to distribute.
I don't know of any tools that fix replication other than the person that supports it. The tables and stored procedures are the most valuable for me. There is this list of some of the stored procedures:
Programmatically Monitor Replication
This article has links to different areas to monitor:
Monitoring Replication
Information in those links and looking at the definitions for the stored procedures gives an idea of what tables have information that can help. A lot of the tables used are in the distribution database. Going through the dbo.MSxxxxxx tables in the distribution database isn't a bad idea to understand things better.
Sue
February 20, 2018 at 2:45 pm
Cool, what are your thoughts on using the AWS Migration service instead of native SQL to produce SQL replicas ? If you are lacking on SQL experience it makes it nice and easy and painless?
February 20, 2018 at 2:45 pm
Sue_H - Tuesday, February 20, 2018 2:26 PMsteve.roberts 86619 - Tuesday, February 20, 2018 2:04 PMSue_H - Tuesday, February 20, 2018 1:56 PMsteve.roberts 86619 - Tuesday, February 20, 2018 11:03 AMHi,Thanks I think I know what has happened today.
We have a production Sage ERP DB and two subscribers to the DB. All good.
Someone rang a SQL update script on the Publisher to reset some values. All OK no problems with doing that. These changes have all filtered though to Subscriber 1 but not 2. Running SQL Profiler on Sub2 shows loads of these entriesexec [sp_MSupd_dboStockItem] default,default,default,5038673022,default,default,default,default,default,default,default,
Which is it updating the table in question but it seems to keep looping round and you see the same values appear in the numeric bit over and over again. Basically I think it is stuck in a loop and this is back is it going out of sync?
Assuming it's transactional replication, in the distribution database, execute sp_browsereplcmds to see what's in the queue. Watch to see if xact_seqno is changing at all. The command column has which stored procedure would be executing.
In the publisher database you can see if things are not replicated yet executing sp_repltrans. In the publisher you can also do DBCC OPENTRAN and check the Oldest non-distributed LSN. It it reports (0:0:0) then the log reader is caught up.
Sue
Hi it seems to be a volume thing - it is now catching up with the other tables after processing all the updates for the stockitem table - which is weird
Any good SQL tools for monitoring and fixing replication?
Also how come repl monitor reported all ok when it had millions of records to process and a tracer token took hours to get across?
It could have been actively processing a large batch so replication monitor wouldn't report anything. Viewing the subscription details and the Undistributed commands would or should have shown the undistributed commands.
Tracer tokens aren't necessarily very good for monitoring but in your case it likely waited behind whatever other transactions you had to distribute.
I don't know of any tools that fix replication other than the person that supports it. The tables and stored procedures are the most valuable for me. There is this list of some of the stored procedures:
Programmatically Monitor Replication
This article has links to different areas to monitor:
Monitoring ReplicationInformation in those links and looking at the definitions for the stored procedures gives an idea of what tables have information that can help. A lot of the tables used are in the distribution database. Going through the dbo.MSxxxxxx tables in the distribution database isn't a bad idea to understand things better.
Sue
Agreed with all of the above. In addition, this thread was of help to me some time ago : SQL Replication Merge Not Completing
February 20, 2018 at 3:00 pm
steve.roberts 86619 - Tuesday, February 20, 2018 2:45 PMCool, what are your thoughts on using the AWS Migration service instead of native SQL to produce SQL replicas ? If you are lacking on SQL experience it makes it nice and easy and painless?
Do you want a server or databases? AWS server migration service is for replicating servers, not databases so it's kind of two different things. I think that AWS server migration only supports VMware virtual machines natively. I think you can do Hyper-V by adding in some other connector or doing a three phase migration...something like that.
But what direction you go also all depends on what you need the replicas or replicated databases for.
Sue
February 20, 2018 at 3:05 pm
Sue_H - Tuesday, February 20, 2018 3:00 PMsteve.roberts 86619 - Tuesday, February 20, 2018 2:45 PMCool, what are your thoughts on using the AWS Migration service instead of native SQL to produce SQL replicas ? If you are lacking on SQL experience it makes it nice and easy and painless?Do you want a server or databases? AWS server migration service is for replicating servers, not databases so it's kind of two different things. I think that AWS server migration only supports VMware virtual machines natively. I think you can do Hyper-V by adding in some other connector or doing a three phase migration...something like that.
But what direction you go also all depends on what you need the replicas or replicated databases for.Sue
Databases - they offer a DB migration service as well now.
February 20, 2018 at 4:11 pm
steve.roberts 86619 - Tuesday, February 20, 2018 3:05 PMSue_H - Tuesday, February 20, 2018 3:00 PMDo you want a server or databases? AWS server migration service is for replicating servers, not databases so it's kind of two different things. I think that AWS server migration only supports VMware virtual machines natively. I think you can do Hyper-V by adding in some other connector or doing a three phase migration...something like that.
But what direction you go also all depends on what you need the replicas or replicated databases for.Sue
Databases - they offer a DB migration service as well now.
I don't know anything about how that one works, not even remotely. 🙂 If it's like it was previously, it used to be based on snapshots. So that wouldn't be similar to replication, availability groups, log shipping, etc. If this is for DR it could be viable depending on the business needs. If you need close to real time, offload some of the workload, it may not be current enough if it's still snapshots.
I think one of the easiest ways to have database copies is log shipping. But what you do depends on why you need the copies - is it DR, offloading, HA and what the business requirements are that usually narrows things down. That and cost.
Sue
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply