Upgrading from SQL Server 2008 R2 to SQL Server 2016

Question

Upgrading from SQL Server 2008 R2 to SQL Server 2016

SJanki

SSCrazy

Points: 2847
More actions
December 23, 2016 at 12:27 pm

#324680

Hi All ,
[moving my question from the wrong forum to here!]
We have all application databases in SQL Server 2008 R2 .
I am looking forward to upgrade the 2008 R2 databases to SQL Server 2016.
Could you please share your experience with this upgrade .
What are the challenges ?
Please also help in selecting the Standard vs Enterprise based on commonly used features.
Thanks!!
December 25, 2016 at 9:51 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

GroverVivek SSCommitted Points: 1674 More actions · Answer 1

There are couple of important things we need to take care of. We must keep in mind that all applications whether those are web hosted or those are executable/batch files should work with improved or same quality at new production environment. Hence,

1. Testing the compatibility of code (procedures, views, triggers, functions) is required. - Microsoft Upgrade Advisor for 2016 can be used (not sure if available for this version)

2. For adhoc queries' compatibility test, we can run 24*7 statement level traces against upgrade advisor.

3. Performance testing, can be done by capturing replay template traces and running them against Upgrade Assistant tool. It will give report that how your is going to perform against 2016 version.

4. Transfer logins to new server.

5. Transfer SQL Jobs to newer version.

Helpful links :

https://sqlearner.wordpress.com/2016/12/19/sql-server-database-migration-recommendations/

https://msdn.microsoft.com/en-us/library/ms143393.aspx

Regards
VG

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 2

SJanki (12/23/2016)
Hi All ,
[moving my question from the wrong forum to here!]
We have all application databases in SQL Server 2008 R2 .
I am looking forward to upgrade the 2008 R2 databases to SQL Server 2016.
Could you please share your experience with this upgrade .
What are the challenges ?
Please also help in selecting the Standard vs Enterprise based on commonly used features.
Thanks!!

Which edition are you currently using for 2008 R2?

Also, have you run the "upgrade advisor", yet? While it does have some "lost leaders", it does provide a list of spots that you at least need to consider.

--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)

SJanki SSCrazy Points: 2847 More actions · Answer 3

Hi Jeff,

I have not yet run the 'update advisor'. Thanks for the idea. I'll run it.

Currently in SQL Server 2008 R2 , Standard edition we are running 2 databases.

We have another 2 databases in SQL Server 2008 R2 Enterprise edition.

For the enterprise edition databases, I am not sure if there was a particular reason for it but the databases can be run in the Standard edition without any functionality disrupts.

Thanks

~Janki

Jeff Moden (12/26/2016)

SJanki (12/23/2016)
Which edition are you currently using for 2008 R2?
Also, have you run the "upgrade advisor", yet? While it does have some "lost leaders", it does provide a list of spots that you at least need to consider.

SJanki SSCrazy Points: 2847 More actions · Answer 4

Thank you Vivek for the very helpful information and the links!

Now I know what are the preliminary and important steps to follow.

SQL Learner - VKG (12/23/2016)
There are couple of important things we need to take care of. We must keep in mind that all applications whether those are web hosted or those are executable/batch files should work with improved or same quality at new production environment. Hence,
1. Testing the compatibility of code (procedures, views, triggers, functions) is required. - Microsoft Upgrade Advisor for 2016 can be used (not sure if available for this version)
2. For adhoc queries' compatibility test, we can run 24*7 statement level traces against upgrade advisor.
3. Performance testing, can be done by capturing replay template traces and running them against Upgrade Assistant tool. It will give report that how your is going to perform against 2016 version.
4. Transfer logins to new server.
5. Transfer SQL Jobs to newer version.
Helpful links :
https://sqlearner.wordpress.com/2016/12/19/sql-server-database-migration-recommendations/
https://msdn.microsoft.com/en-us/library/ms143393.aspx

SJanki SSCrazy Points: 2847 More actions · Answer 5

Thank you Jason for the helpful information.

JasonClark (12/25/2016)
Please also help in selecting the Standard vs Enterprise based on commonly used features.
Microsoft described this in brief:
https://www.microsoft.com/en-us/sql-server/sql-server-editions
you may have a look this may help you.

darshan.joshi29 SSC Veteran Points: 293 More actions · Answer 6

The first step you have check your applications compatibility with 2016 So in this situation mirroring is best option because with this in minimum down time you check all applications sanity test with 2016 only cut hour downtime required.

1. first restore full db backup on 2016 server

2. Apply mirroring from 2008 r2 instance database to 2016 database

3.check mirroring status means data is sync or not

4. Take cut out downtime and suspend mirroring So 2016

5.sanity check of application with 2016 version database.

SJanki SSCrazy Points: 2847 More actions · Answer 7

darshan.joshi29 (12/28/2016)
The first step you have check your applications compatibility with 2016 So in this situation mirroring is best option because with this in minimum down time you check all applications sanity test with 2016 only cut hour downtime required.

Thank you Darshan. Will consider this option too.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

darshan.joshi29 (12/28/2016)
5.sanity check of application with 2016 version database.

Testing should be the first thing done, in a dev/test environment, long before the production server is touched. If you test after the upgrade, you don't have much (any) time to fix the problems that you will encounter.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SJanki SSCrazy Points: 2847 More actions · Answer 9

GilaMonster
Testing should be the first thing done, in a dev/test environment, long before the production server is touched. If you test after the upgrade, you don't have much (any) time to fix the problems that you will encounter.

Yes Gail. We have 3 environments:Dev,Test,Prod

In phase 1 ,after running the upgrade advisor, we have started fixing issues in the procedures' code, table schema etc.

Once this is all done we will upgrade and Test in Test env. before moving to prod.

GroverVivek SSCommitted Points: 1674 More actions · Answer 10

SJanki - Monday, December 26, 2016 5:21 PM
Thank you Vivek for the very helpful information and the links!Now I know what are the preliminary and important steps to follow.
SQL Learner - VKG (12/23/2016)
There are couple of important things we need to take care of. We must keep in mind that all applications whether those are web hosted or those are executable/batch files should work with improved or same quality at new production environment. Hence, 1. Testing the compatibility of code (procedures, views, triggers, functions) is required. - Microsoft Upgrade Advisor for 2016 can be used (not sure if available for this version)2. For adhoc queries' compatibility test, we can run 24*7 statement level traces against upgrade advisor.3. Performance testing, can be done by capturing replay template traces and running them against Upgrade Assistant tool. It will give report that how your is going to perform against 2016 version.4. Transfer logins to new server.5. Transfer SQL Jobs to newer version.Helpful links :https://sqlearner.wordpress.com/2016/12/19/sql-server-database-migration-recommendations/https://msdn.microsoft.com/en-us/library/ms143393.aspx

Thanks Dear

Regards
VG