How to Upgrade SQL Server

  • Rudy Panigas

    SSChampion

    Points: 10683

    Comments posted to this topic are about the item How to Upgrade SQL Server

    Rudy

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8906

    my 50 cents:

    - I would suggest to swap step 1 and 2 in post-work, because in one particular case I got errors with DBCC CHECKDB which were fixed with the UPDATE USAGE statement

    - reviewing collation should be part of pre-work

    - Also I run step 8 of post-work as pre-work, before restoring the database on the new server.

    - After taking the latest full backups on the source server, I'll put these databases in OFFLINE mode, to be sure no connections are made to the old server (after step 4)

    - after installing SQL server (step 8 of pre-work), I'll run the server default configuration settings like DAC enabled, compression enabled etc. Step 2 of Data-loads is actually a part of this configuration

    One thing which is not mentioned: You can only restore 2008+ in SQL 2014, if you have a SQL2005 (or SQL2000 :w00t: database), you'll have to restore it first to an intermediate version. This because only 3 lower compatibility levels are supported.

    Wilfred
    The best things in life are the simple things

  • pstanislav

    SSC Eights!

    Points: 939

    Great checklist - thanks for compiling!

    One note about Compatibility Level - this really hinges on the applications. Of course, you need to test in advance on the new SQL version, and there may be some commands in use that require handling as in the prior SQL version. If the apps cannot be evolved in parallel to the DB upgrade, especially if you support many different apps or vendor-provided apps, you will need to keep some databases at less than the current version. True, some new features will not be available - but the apps will work in the meantime.

  • Kris Gruttemeyer

    Ten Centuries

    Points: 1185

    One thing which is not mentioned: You can only restore 2008+ in SQL 2014, if you have a SQL2005 (or SQL2000 :w00t: database), you'll have to restore it first to an intermediate version. This because only 3 lower compatibility levels are supported.

    This is incorrect. You can't do an IN-PLACE upgrade from 2005 (unless you're on SP4), you have to do a migration.

    I just did a 2005 SP3 upgrade to 2014 and backup/restore (log shipping) migration worked beautifully.

  • Jorge Serres

    Old Hand

    Points: 352

    Great topic Rudy, thanks for sharing.

    I've got a few suggestions:

    1- Instead of just scripting the logins, use the "https://support.microsoft.com/en-us/kb/918992" -- sp_hexadecimal.sql for transferring Logins and passwords.

    2-I don't believe there's such a thing as an orphan Login, there are orphaned users though! This usually occur when a database is refreshed/restored on a server and it doesn't have a mapped Login for the corresponding users.

    3- Also script out

    A-Alerts,

    B-Operators

    C-Credentials

    D-Proxies

    4- Remap all shared Drives and Permissions from OLD to NEW server. It can be done through POSH. Permissions are embedded in the Registry.

    5- Disable all jobs until the GET-GO moment for the new Server/Databases going live

  • Rudy Panigas

    SSChampion

    Points: 10683

    Kris Gruttemeyer (10/16/2015)


    One thing which is not mentioned: You can only restore 2008+ in SQL 2014, if you have a SQL2005 (or SQL2000 :w00t: database), you'll have to restore it first to an intermediate version. This because only 3 lower compatibility levels are supported.

    This is incorrect. You can't do an IN-PLACE upgrade from 2005 (unless you're on SP4), you have to do a migration.

    I just did a 2005 SP3 upgrade to 2014 and backup/restore (log shipping) migration worked beautifully.

    Wow! didn't know that! Thanks for sharing 🙂

    Rudy

  • Rudy Panigas

    SSChampion

    Points: 10683

    Thanks for the comments 🙂

    Glad to see that this article is creating conversation as there isn't a lot on this topic in the wild. My goal here was to provide some feedback on my experiences and do not claim that this is the perfect way for migration but it has worked successfully on several migrations (5 to be exact). However, with everyone's input this process can get better and better.

    Big thank you to everyone's comments. If you have done a migration, I and others would love to hear your experience.

    Rudy 🙂

    Rudy

  • carlos10robles

    SSC Journeyman

    Points: 95

    I don't think is quite necessary to update the statistics after completing the work. If you run a full scan for each database for each table it will take forever.

    I would recommend to run an "intelligent" statistics job instead, there are a lot of pretty good solutions out there but I will stick to "Ola Hallengren's" solution.

    Best regards!

    Carlos Robles  

    DBA Mastery
    Data Platform MVP | MCSE, MCSA, MCTS, MCP | ITIL v3

    w: www.dbamastery.com

    e: crobles@dbamastery.com

     

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8906

    See Supported Version and Edition Upgrades SQL Server 2012 or Supported Version and Edition Upgrades SQL Server 2014 for the supported in-place upgrade path

    Wilfred
    The best things in life are the simple things

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8906

    Kris Gruttemeyer (10/16/2015)


    One thing which is not mentioned: You can only restore 2008+ in SQL 2014, if you have a SQL2005 (or SQL2000 :w00t: database), you'll have to restore it first to an intermediate version. This because only 3 lower compatibility levels are supported.

    This is incorrect. You can't do an IN-PLACE upgrade from 2005 (unless you're on SP4), you have to do a migration.

    I just did a 2005 SP3 upgrade to 2014 and backup/restore (log shipping) migration worked beautifully.

    Is this what you mean:

    - You can do an in-place upgrade from 2005 SP4 to SQL 2012

    - Any version between SQL 2000 and SQL 2005 SP3 cannot be restoredon SQL2012+ (hence, no in-place upgrade). See: this link about restoring SQL 2000 on SQL 2012

    Wilfred
    The best things in life are the simple things

  • Kris Gruttemeyer

    Ten Centuries

    Points: 1185

    - You can do an in-place upgrade from 2005 SP4 to SQL 2012

    -Correct

    - Any version between SQL 2000 and SQL 2005 SP3 cannot be restoredon SQL2012+ (hence, no in-place upgrade).

    -Sort of, SQL 2000 DBs need to have an intermediate step (2005, 2008R2).

    -SQL 2005 DBs can be restored to 2012+ just fine. Again, I did 2005 SP3 to 2014 SP1 directly with no intermediate steps.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    I can pass this on to our admin but we are still on 2008 so I doubt he will look at it.

  • quackhandle1975

    SSChampion

    Points: 10963

    Great article, a question I've just thought of (and it might go in your "Post Work" section) is a traceflag check. So if you have a number of traceflags (1222, 4199, etc) running in older ver of SQL would they still be running post upgrade? I have always copied over the databases from old version to new version of SQL rather than an inplace upgrade.

    Thanks

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • Rudy Panigas

    SSChampion

    Points: 10683

    quackhandle1975 (11/26/2015)


    Great article, a question I've just thought of (and it might go in your "Post Work" section) is a traceflag check. So if you have a number of traceflags (1222, 4199, etc) running in older ver of SQL would they still be running post upgrade? I have always copied over the databases from old version to new version of SQL rather than an inplace upgrade.

    Thanks

    qh

    Hello and thanks for the great question. I too do not like to do in-place upgrade and copy over the databases. Ensure that you change the compatibility level to the new version you have installed, unless there are specific reasons you need to use the older level. Be sure to document this too.

    As for trace flags, you should document the existing trace flags and then review/compare them with the flags on the newer version of SQL. In some cases the old trace flags may not be needed any more. Like everything else, you should read and review before moving to a different version of SQL.

    Hope this helps 🙂

    Rudy

    Rudy

  • peter.row

    SSCarpal Tunnel

    Points: 4290

    Also remember if you use SSRS component of SQL Server and you have multiple instances of it installed on the same server don't upgrade to SQL Server 2017 because SSRS can now only have 1 instance per server because MS hate you.

Viewing 15 posts - 1 through 15 (of 24 total)

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