Read this if you have transactional replication configured and plan to upgrade from SQL 2008/2008 R2 to SQL 2012/2014

  • Article

SQL Server online documentation makes very clear that you need to 'drain' your replicated transactions before doing any upgrade if you have replicated databases. Below are requirements for transactional replication:

  1. Make sure that the Log Reader Agent is running for the database. By default, the agent runs continuously.
  2. Stop user activity on published tables.
  3. Allow time for the Log Reader Agent to copy transactions to the distribution database, and then stop the agent.
  4. Execute sp_replcmds to verify that all transactions have been processed. The result set from this procedure should be empty.
  5. Execute sp_replflush to close the connection from sp_replcmds.
  6. Perform the server upgrade to SQL Server 2012.
  7. Restart SQL Server Agent and the Log Reader Agent if they do not start automatically after the upgrade.

 

A recent customer issue further confirms the need of following the steps before upgrade. The customer has transaction replication configured and they needed to upgrade from SQL Server 2008 to SQL Server 2012. This customer's publisher database was mirrored. They used 'rolling upgrade approach. They allowed publisher to continue to accept incoming transactions on primary server while upgrading the mirrored server. Then they failed over to the upgraded server and made it primary server. After that, they upgraded the original primary server. During the upgrade process, some transactions occurred on SQL Server 2008 but log reader didn't get a chance to read them and copy them to distribution. After the upgrade, they experienced the following error and engaged us.

Error 542 An invalid datetime value was encountered. Value exceeds the year 9999. (Source: MSSQLServer, Error number: 542)

Via some internal testing, we also reproduced additional error below if there are replicated transactions left prior to upgrade.

Error 515 Cannot insert the value NULL into column 'column1', table abc'; column does not allow nulls. UPDATE fails

Upon further investigation, we have discovered that SQL 2012 has some minor difference in terms how certain log records are handled. For those SQL 2008 log records (that weren't 'drained' before upgrade), Log reader agent made some incorrect assumptions and ended up reading the log records incorrectly. For the above errors 542 and 515, the underlying the issue is the same (reading some incorrect data). For 542, log reader agent was able to catch the fact that the value read was invalid for date. So log reader stopped processing the log record. So the error is raised by log reader agent. For 515, log reader didn't even know NULL is invalid for a non-NULL column. So log reader processed the log record and put it in distribution database. But this was caught by actually executing the update to subscriber and error was raised by distribution agent.

This further supports requirement documented above that you must ensure you 'drain' all replicated transaction before upgrade. However, it's undesirable to have log reader read incorrect values in such situation either. So our product team decides to fix this. Currently, we have a fix for SQL 2012. Fix for SQL 2014 is being built.

Solutions

Let's summarize the solutions here.

To avoid the problem entirely:

  1. If you follow online documentation prior to upgrade to ensure no replicated transaction left on 2008 or 2008 R2, you won't experience the above issue.
  2. If your situation doesn't allow you to stop accepting incoming transactions during upgrade (using mirror for rolling upgrade as an example), you should follow these steps:
    1. First call our support to obtain the fix/patch for this issue
    2. Prior to upgrade, disable log reader agent
    3. Upgrade and then immediately apply the fix/patch you obtained from above step.
    4. Enable and start log reader agent

If you have already upgraded and experience the errors (542, 515), the following are the options

  1. You can re-initialize your replication and everything will be fine
  2. If you only experience 542 error, you can obtain the fix/patch from us and new log reader will process the log record correctly.
  3. If you see 515 error, you only option is to re-initialize. This is because log reader already incorrectly process the record and it can't go back and reprocess.

 

I want to point out this: The fact we are providing a fix for this particular situation doesn't mean that you should avoid the requirement of 'draining' replicated transactions per online documentation. You should still follow the upgrade requirement documentation.

 

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support