Transfer SQL 2008r2 DBs to SQL 2016

Question

Post reply

Transfer SQL 2008r2 DBs to SQL 2016

LaVerne Sanders

SSC Enthusiast

Points: 145
More actions
September 8, 2016 at 7:56 am

#317245

It's time to retire my SQL 2008r2 server and move to SQL2016. I have about 150 SQL DBs and about the same number of SSAS cube DBs. What would be a suggested plan/method to accomplish this? Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 1

First download SQL Server 2016 Upgrade Advisor then check this link

https://www.mssqltips.com/sqlservertip/3688/prepare-for-an-upgrade-with-the-sql-server-2016-upgrade-advisor/

and if you use SQL Server 2008 sp2 or sp3 must installed

SQL Server 2016 supports upgrade from the following versions of SQL Server:

•SQL Server 2008 SP3 or later

•SQL Server 2008 R2 SP2 or later

•SQL Server 2012 SP2 or later

•SQL Server 2014 or later

LaVerne Sanders SSC Enthusiast Points: 145 More actions · Answer 2

Thanks for the notes. Just want to confirm the response. This project is not upgrading the existing SQL 2008r2 server. A new machine is replacing it with SQL 2016. All DBs will be moved/copied to the new server. Does the info link still apply? Thanks!!

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

The upgrade advisor part does, you should still run upgrade advisor and fix anything that breaks, and you absolutely MUST do performance tests on SQL 2016 due to the changes to the cardinality estimator (if you don't do them pre-upgrade, your users do them afterwards)

SQL DBs can be restored up-version without a problem, I have no idea about SSAS cubes.

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

LaVerne Sanders SSC Enthusiast Points: 145 More actions · Answer 4

Just one more question. My SQL 2008rs is still on service pack 1. Should it be SP2 before I start this process? Thanks.

Phil Parkin SSC Guru Points: 246873 More actions · Answer 5

LaVerne Sanders (9/12/2016)
Just one more question. My SQL 2008rs is still on service pack 1. Should it be SP2 before I start this process? Thanks.

This page answers your question, in detail.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

LaVerne Sanders (9/12/2016)
Just one more question. My SQL 2008rs is still on service pack 1. Should it be SP2 before I start this process? Thanks.

If you plan an in-place upgrade, yes. If you plan a side-by-side, or new hardware, not necessary.

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