title: Azure Sql Database replication - 9 1/2 hour delay + cant remove the replica
body: <h2>Note</h2>
  
  <p>This is getting quite long so I will try and re-edit parts through the day. </p>
  
  <ul>
  <li>These databases are no long active, which means I can play with them to work out what is going wrong. </li>
  </ul>
  
  <p><strong>The only thing left to answer:</strong> Given two databases running on Azure Databases at S3 (100 DTU). Should any secondary ever get significantly behind the primary database? Even while the DTU is hammered to 100% for over half the day. The reason for the DTU being hammered being IO writes mostly. </p>
  
  <h2>The Start: a few problems.</h2>
  
  <p>DTU limits were hit on Monday, Tuesday and to some extent on Wednesday for a significant amount of time. 3PM UTC - 6AM UTC. </p>
  
  <p><strong>Problem 1 (lag in data on the secondary)</strong>: This had appeared to have caused a lag of data in the secondary of about 9 1/2 hours. The servers were effectively being spammed with updates causing a lot of IO updates. 6-8 million records on one table for the 24 hour period for example. This problem drove the reason for the post: </p>
  
  <blockquote>
    <p>Shouldn't these be much more in sync?   </p>
  </blockquote>
  
  <p>The data became out of sync on Monday morning and continued out of sync until Friday. On Thursday some new databases were started up to replace these standard SQL databases and so they were left to rot. Well, for me to experiment with at least. </p>
  
  <p>The application causing the redundant queries couldn't be fixed for a few days (I'm doubting they will ever fix it now) so that leaves changing the instance type. That action was attempted on the current instance but, the instance must disconnect with all standard replicas to increase to the performance tier. This led to the second problem (see below). The replica taking its time to be removed. Began on Wednesday morning and did not complete until Friday. </p>
  
  <p><strong>Problem 2 (can't remove the replica)</strong>:</p>
  
  <p><strong>(Solved itself after two days)</strong></p>
  
  <p>Disconnecting the secondary database process began ~ Wed 8UTC (when the primary was at about 80GBs). The secondary being about 11GB behind in size at this point. </p>
  
  <h2>Setup</h2>
  
  <p>The databases (primary and secondary) are S3 that is geo-replicated (North + West Europe).</p>
  
  <p>It has an audit log table(which I read from the secondary - normally with an SQL query), but this is currently 9 1/2  hours behind the last entry for the primary database. Running the query again on the secondary a few seconds later it is slowly catching up, but appears to be relative to the refresh rather than playing catch-up.  </p>
  
  <p>Both primary and secondary (read-only) databases are S3 (about to be bumped to P2). </p>
  
  <p>the <a href="https://azure.microsoft.com/en-us/documentation/articles/sql-database-geo-replication-overview/" rel="nofollow noreferrer">azure documentation</a> states: </p>
  
  <blockquote>
    <p>Active Geo-Replication (readable secondaries) is now available for all databases in all service tiers. In April 2017, the non-readable secondary type will be retired and existing non-readable databases will automatically be upgraded to readable secondaries.</p>
  </blockquote>
  
  <p>How has the secondary has got so far behind? <em>seconds</em> to <em>minutes</em> would be acceptable. Hours not so much. The link above describes it as <em>slightly</em>:</p>
  
  <blockquote>
    <p>While at any given point, the secondary database might be slightly behind the primary database, the secondary data is guaranteed to always be transactionally consistent with changes committed to the primary database.</p>
  </blockquote>
  
  <p>Given the secondary is about to be destroyed and replaced by a higher level (need to remove replicas when upgrading from standard -> premium). I'm curious to know if it will happen again as well as what the definition of slight might be in this instance?</p>
  
  <p>Notes: The primary did reach maximum DTU for a few hours but didn't harm the performance significantly, which is where the 9-hour difference was noticed.  </p>
  
  <h2>Stats</h2>
  
  <p><strong>Update for TheGameiswar</strong>:
  I can't query it right now as it started removing itself as a replica (to be able to move the primary up to the P2 level, but that began hours ago at ~8.30UTC and 5 hours later it is still going). I think it's quite broken now.  </p>
  
  <p>Query - nothing special:</p>
  
  <blockquote>
    <p>SELECT TOP 1000 [ID]
          ,[DateCreated]
          ,[SrcID]
          ,[HardwareID]
          ,[IP_Port]
          ,[Action]
          ,[ResponseTime]
          ,[ErrorCode]
          ,[ExtraInfo]
      FROM [dbo].[Audit]
      order by datecreated desc</p>
  </blockquote>
  
  <p>I can't compare the tables anymore as it's quite stuck and refusing connections. </p>
  
  <p><a href="https://i.stack.imgur.com/1UpCQ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1UpCQ.jpg" alt="Replication"></a></p>
  
  <p><a href="https://i.stack.imgur.com/Da4ky.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Da4ky.png" alt="Primary DTU"></a></p>
  
  <p><a href="https://i.stack.imgur.com/nZpsm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nZpsm.png" alt="Primary insights"></a></p>
  
  <p>The 586 hours (10-14GB) are inserts into the <em>primary</em> database audit table. It was similar yesterday when noticing the 9 1/2 hour difference in data. </p>
  
  <p>When the attempt to remove the replica (another person stated the process) it had about 10GB difference in size. </p>
  
  <p><strong>Cant compare data but can show DB-size at equivalent time</strong></p>
  
  <p><strong>Primary</strong> DB Size (last 24 hours):</p>
  
  <p><a href="https://i.stack.imgur.com/UD8cS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UD8cS.png" alt="Primary DB size at 4AM"></a></p>
  
  <p><strong>Secondary</strong> DB Size (last 24 hours):</p>
  
  <p><a href="https://i.stack.imgur.com/ENtdN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ENtdN.png" alt="enter image description here"></a></p>
  
  <p><strong>Primary</strong> database size - week view</p>
  
  <p><a href="https://i.stack.imgur.com/WW8OW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WW8OW.png" alt="Primary database size - week view"></a></p>
  
  <p><strong>Secondary</strong> database size - week view </p>
  
  <p><a href="https://i.stack.imgur.com/36dBt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/36dBt.png" alt="secondary database size - week view"></a></p>
  
  <p>As mentioned ... it is being removed as a replica... but is still playing catch up with the DB size if you observe the charts above.</p>
  
  <blockquote>
    <p>Stop replication errored for serverName: ---------, databaseName: Cloud4
    ErrorCode: 400
    ErrorMessage: A terminate operation is already in progress for database 'Cloud4'.</p>
  </blockquote>
  
  <hr>
  
  <h2><strong>Update 2 - Replication - dm_continuous_copy_status</strong></h2>
  
  <p>Replication is still removing ... moving on...</p>
  
  <p><code>select * from sys.dm_continuous_copy_status</code> </p>
  
  <p><a href="https://i.stack.imgur.com/Vj9yz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Vj9yz.png" alt="result from sys.dm_continuous_copy_status"></a></p>
  
  <h2>sys.dm_exec_requests</h2>
  
  <p>Querying from Thursday
  Appears to be quite empty. The only record being </p>
  
  <h2>Replica removed itself at last.</h2>
  
  <p>The replica has removed itself after 2 days. At the 80GB mark that I predicted. It waited to replay the data in the transactions (till the point it was removed as a replica) before it would remove the replica. 
  <a href="https://i.stack.imgur.com/ZZE5K.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZZE5K.png" alt="enter image description here"></a></p>
  
  <h2>A Week after on the P2 databases</h2>
  
  <p>DTU is holding between 20-40% at busy periods and currently performing ~12 million data entries every 24 hours (a similar amount for reads, but writing is much worse on the indexes and the table). 70-100% inserts extra in a week. This time, the replica is not struggling to keep up, which is good but that is likely due to it not reaching 100% DTU. </p>
  
  <h2>Conclusion</h2>
  
  <p>The replicas are useful but not in this case. This one caused degraded performance for several days that could have been averted. A simple increase to the performance tier until the cause of the problem could be fixed. IF the replica looks like it is dragging behind and you are on the border of Basic -> Standard or Standard -> Performance it would be safe to remove the replica as soon as possible and increase to a different tier. </p>
  
  <p>Now we are on P2. The database is increasing at 20GB a day... and they say they have fixed the problem that sends 15 thousand redundant updates per minute. Thanks to the query performance insight for highlight that as querying the table is extremely painful on the DTU (even querying the last minute of data in that table is bad on the DTU. ~15 thousand new records every minute). </p>
  
  <p><a href="https://i.stack.imgur.com/5lM1y.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5lM1y.png" alt="12 million inserts"></a>
  62617: insert ... 
  62618: select ... 
  62619: select ...</p>
  
  <p>A positive from the above is that it's moved from <strong>586 hours</strong> combined time for the insert statements (7.5 million entry rows per day) on S3 to <strong>3 hours</strong> on P2 (12.4 million row rows per day). An extremely significant decrease in processing time. It did start with an empty table on Thursday but that has surpassed the previous size in a week whereas the previous one took a few months to get there. </p>
  
  <p>It's doing well on the new tier. It should be ~5% if the applications were using the database responsibly and the secondary is up to date.<br>
  <a href="https://i.stack.imgur.com/aJ1lI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aJ1lI.png" alt="P2 DTU"></a>  </p>
  
  <p>Spoke too soon. Now on P2 
  <a href="https://i.stack.imgur.com/EB7tH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EB7tH.png" alt="enter image description here"></a>
  Someone thought it was a good idea to run an SQL query that repeats itself that deletes a thousand rows at a time. 12 million new rows a day.
  10AM - 12AM it's managed to remove about 5.2 million rows. Now the database is showing signs of being in the same state as last week. Im curious if that is what happened now. </p>