October 15, 2015 at 12:06 am
Comments posted to this topic are about the item How to Upgrade SQL Server
Rudy
October 15, 2015 at 1:33 am
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
October 16, 2015 at 8:11 am
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.
October 16, 2015 at 8:12 am
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.
October 16, 2015 at 9:21 am
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
October 16, 2015 at 11:16 am
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
October 16, 2015 at 11:21 am
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
October 16, 2015 at 6:46 pm
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
October 18, 2015 at 9:30 am
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
October 18, 2015 at 9:48 am
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
October 19, 2015 at 7:11 am
- 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.
October 21, 2015 at 7:21 am
I can pass this on to our admin but we are still on 2008 so I doubt he will look at it.
November 26, 2015 at 9:53 am
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
November 26, 2015 at 11:01 am
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
January 26, 2018 at 1:05 am
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 23 total)
You must be logged in to reply to this topic. Login to reply