SQL and Win 2008 R2 -> SQL and Win 2016

morning team,

What is the best approach and practice to upgrade a production sql server 2008 r2 (on win server 2008 r2)  to sql and win 2016 servers?

John EsraeloDatabase / SQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobertSystem AdminCommented:
Simple high level the following is the approach I would take.
Build your new server
install SQL
Backup the database\databases on your existing server.
Restore DBs to the new server.

If you need to do an in place upgrade you would need to go to server 2012 in between as there is no supported upgrade path directly from 2008 to 2016.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John EsraeloDatabase / SQL DeveloperAuthor Commented:
That's very true, it is a catch 22, you cannot have 2008 sql on 2016 and you cannot have 2016 sql on 2008 r2 windows.
Here is 2 possible related questions:  1> can the sql server name be aliased in surface configuration so we don't have to rename all the "callers" and 2> do we have to recompile all the 32 bit in-house applications to 64 bit including the SSIS packages?

thx Robert

John EsraeloDatabase / SQL DeveloperAuthor Commented:
Robert, forgot to mention that the SSIS packages are written in visual studio 2008.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

RobertSystem AdminCommented:
As long as your instance names are the same (default or otherwise) then all you would need to do is create a DNS alias to redirect current clients to the new server or you can go through the process and rename it to match current server name after you shutdown the current one. (if you do rename don't forget to correct the SPN's)

As for SSIS I don't have a lot of knowledge on that (or know how your in house apps utilize the integration service) however generally speaking you normally would not need to.
John EsraeloDatabase / SQL DeveloperAuthor Commented:
Thank you Robert, well done

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Before running SQL Server Setup to upgrade to SQL Server 2016, review the following topics about the upgrade process and the release notes

and start with Prepare for upgrade by running Data Migration Assistant

it makes sense to start all this from non prod server
John EsraeloDatabase / SQL DeveloperAuthor Commented:
Thank you Robert, well done !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.