Post reply

How can we ensure whether database is migrated successfull from DBA Side

  • February 5, 2020 at 12:10 pm

    #3723246

    Hello Team,

    Could you please provide me is there any T-SQL Scripts to validate whehere database is migrated successful from SQL sevrer 2008 R2 to SQL Server 2012/2014/2017/2019.

    Example: Tables,SPs, Viewes, Functions and Linked server,etc.

    Regards,

    Naveen M

  • February 5, 2020 at 2:11 pm

    #3723294

    To the best of my knowledge, no such scripts exist.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 5, 2020 at 2:41 pm

    #3723311

    You can make the migration more likely to be successful by using the Data Migration Assistant before you start.  If you're migrating to SQL Server 2016 or later (and if you're not, then you're going to a version that's already out of mainstream support) then you'll encounter the new cardinality estimator introduced in SQL Server 2014.  You may wish to switch the Query Store on in each database but leave the compatibility levels where they are for a couple of weeks.  After that, switch the databases to the same compatibility level as the server and use Query Store to force the old plan for any queries that show a deterioration in performance.   There's a few articles out there that give more details about how to do that.

    To answer the question you asked, there aren't any actual scripts.  There's the installation log that will tell you whether the upgrade succeeded or failed.  The application vendor may provide you with a checklist to verify that everything is working properly.  There's various ways of monitoring performance on the server, but user experience is king, so speak to users, and if they're as happy as (or, preferably, even happier than) before the upgrade, that's the best measure of success.

    John

  • February 5, 2020 at 3:55 pm

    #3723364

    Be sure to run the Data Migration Assistant on every user db.  Don't assume one db is "just like" another one.

    If the db is open and usable after it migrates, the migration was successful.  If there's an issue, SQL will let you know.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • February 7, 2020 at 6:01 am

    #3723828

    Thanks all for your suggestions.

Viewing 5 posts - 1 through 4 (of 4 total)

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