locked
Upgrade MSSQL database server from 2005 to 2012 RRS feed

  • Question

  • Currently our production  application running with MSSQL database 2005. application running fine for nearly 5 years.

    now we need to upgrade the database to 2012. So we upgrade the database to 2012 version with a cold backup of 2005.

    After upgrading the database all the reports of application are damp slow.

    please help us to over come the situation 


    D.E.H Vitharana

    Monday, September 18, 2017 7:10 AM

All replies

  • Hello,

    After Migration you should update all statistics and also index maintenance are often helpfull.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, September 18, 2017 7:52 AM
  • Following are the details of the current production

    MSSQL database 2005 SP 2   should upgraded to  MSSQL database 2012

    Please let me know the complete steps for  upgrading the database 


     


    D.E.H Vitharana

    Monday, September 18, 2017 9:37 AM
  • Please perform the following tasks;

    1. If possible, Install Latest SP for SQL Server 2012 (I think it is SP3)

    1. Check the memory allocation on your New SQL Server 2012

    2. Check the Parallelism settings in 2005 and 2012 SQL Server

    3. Update Stats (Possibly with FULL SCAN)

    4. Re-build your indexes



    Please visit my Blog for some easy and often used t-sql scripts
    My BizCard

    Monday, September 18, 2017 10:32 AM
  • Mainstream support for SQL 2012 has already ended.  I highly suggest if you are going through the pain of upgrading from SQL 2005, you should upgrade to at least SQL 2014 if not SQL 2016.

    Monday, September 18, 2017 12:36 PM
  • I am using the Side-By-Side Upgrade for upgrading from 2005 to 2012

    using Backup/Restore Upgrade Method  

    After upgrading the user database is it mandatory to  transfer the master and msdb database.

    this has to be done before upgrading the user database or after upgrading it

    if it's mandatory to transfer master and msdb database from 2005 

    please guide me to do it 


    D.E.H Vitharana

    Wednesday, September 20, 2017 4:15 AM

  • After upgrading the user database is it mandatory to  transfer the master and msdb database.

    this has to be done before upgrading the user database or after upgrading it

    if it's mandatory to transfer master and msdb database from 2005 

    please guide me to do it 


    D.E.H Vitharana

    Hi,

    Actually not,but looks like you have not planned your migration properly.

    you have to work with your applications team to ensure what logins,roles, permission to be provided to the newly migrated server i,e like not all the logins is required for them &  collect the information's from them grant accordingly to see works.

    see if any sql agent jobs need to be migrate  then work with apps team & about SSIS packages related too.

    see if any linked server,any kind of an HA exists(if not u can ignore) but if exists you should prepare well.

    since you are doing side by side,there might be servername/ip address they may need to redirect everything their batches/ssis package configuration/jobs etc..

    >Prior to this after you restore the database hope you know what to do like checkdb,db compatibility change(inform to apps team before you do),perform update stats,purity,ensure you new sql server is up to date in SP & your windows OS is up to date interms of patches/hotfix & set power saver to high perf mode in windows level.

    how ever you would have been checked 


    sql_server_2012_upgrade_technical_reference_guide_white_paper.pdf in online.


    Regards, S_NO "_"


    • Edited by S_NO Wednesday, September 20, 2017 1:58 PM +
    Wednesday, September 20, 2017 4:42 AM
  • we have done the upgrade once. and we have connect the application to the upgraded database.

    we have notice that some reports are really slow in the new system. in 2005 those reports are are running less than minit. but in the new database (2012) it takes more than 10 minits.

    In our first cycle of upgrade we didn't transfer master and msdb databases .

    we are following  SQL Server 2012 Upgrade Technical Guide



    D.E.H Vitharana

    Wednesday, September 20, 2017 5:13 AM
  • we have upgrade our MSSQL database from 2005 to 2012. after connecting the application we have notice that

    reports which runs less than 1 minutes in 2005 database takes more than 10 minutes in the upgraded 2012 database.

    how to over come the situation 


    D.E.H Vitharana

    Wednesday, September 20, 2017 5:22 AM
  • Did you performed complete update stats?.

    what kind of an wait you are seeing while running(or) do you see any blocking (or) resource utilization high?.



    Regards, S_NO "_"

    Wednesday, September 20, 2017 8:03 AM
  • Yes i have updated statistics. I am not seeing resource utilization  of the server.

    There is no blocking sessions too 

    same reports runs in the current production (2005) less than a min. but it's takes more than 10 min in the upgraded database 


    D.E.H Vitharana

    Wednesday, September 20, 2017 8:56 AM
  • can some one help me to identify the issue

    D.E.H Vitharana

    Wednesday, September 20, 2017 11:22 AM
  • what is your DB compatibility that you set & the update stats may vary in higher version?.

    did you compare the execution plans?.

    did you compared the Infra settings ?.


    Regards, S_NO "_"

    Wednesday, September 20, 2017 12:49 PM
  • Please post the results of SELECT @@VERSION.

    Wednesday, September 20, 2017 1:03 PM