Logshipping for Migrating from SQL Server 2008 to 2019

  • Hello DB Gurus,

    Business wants to have minimum downtime cutover from SQL Server 2008 to SQL Server 2019 , so I've decided to use Logshipping. 200 GB size DB's.  On Primary- I'll run the MS port logins script, run it on the destination. Then on Primary set up Logshipping ( server1)  and it will be backing up on Primary- Copy and Restore on Secondary( server2). In the App connection strings, names are pointing to server 1, any gotchas before I shutdown primary and move to secondary. Once they say Go live, I just assume secondary is caught up and change DB to readwrite or any missing steps?

    Then do I keep the old compat level or change it to latest compat level / rebuild IX's / update stats ?


  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • You want to take a final tail-log backup on the 2008 system at cutover...this will be the final log file for the database(s) and will take the database offline (so it cannot be accessed anymore).

    You cannot assume the secondary is caught up...you need to validate that all transaction log backups have been applied including the final tail-log backup before bringing the databases online on the new server.

    Before changing the compatibility level - you need to test and validate the application and all code.  Have you done that yet?  If not...then you really need to plan for that testing and validation.  There are many changes that can and will impact performance - as well as some code that may no longer work or doesn't work as well because of the changes.

    To resolve these differences - you might want to consider enabling query store with the current compatibility and capture the data for at least a month.  You can then identify any queries that have an issue and address those before changing the compatibility mode.  But that only goes for code in SQL Server - any code in the application(s) will also need regression testing.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply