SQL server 2014/2016 upgrade

Dear all,

we are using SQL server 2008 SP3 and we have SP4 upgrade  and/or SQL server 2014/2016 upgrade project.

question is :
1) any direction upgrade path from SQL server 2008 SP3 to SQL  server 2016?
2) direct upgrade path from SQL server 2003 SP3 to SQL server 2014.

what is the steps to do the upgrade when replication in place? we have replication. any step by step guideline ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
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.

marrowyungSenior Technical architecture (Data)Author Commented:
can DAG in SQL 2014/2016 can replace replication in anyway ? our SQL server 2008 SP3 replication is so bad, slow and easy to break when change happened !
0
Kyle AbrahamsSenior .Net DeveloperCommented:
https://msdn.microsoft.com/en-us/library/ms143393.aspx  

You can go from 2008 SP3 directly to 2016.
0
marrowyungSenior Technical architecture (Data)Author Commented:
good.

but actually can SQL server 2014/2016 upgrade can solve some replication slow problem, by using DAG ?


any upgrade sequence on a replicated environment ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
any idea?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Apparently 2016 you can do in place without issue:
https://msdn.microsoft.com/en-us/library/ms143699.aspx

My thoughts are that upgrading will probably not solve your slow replication issue.  I would try to resolve that first or rule out everything else (disk speed, clusters, network, defragging, etc) before upgrading.
0
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, that one is good link to show replicated DB on now it replicated.

how about any ORDER to upgrade.

like in mirror DB, for SP upgrade, I have to upgrade  the mirror partner first, then primary one, then the mirror monitoring/witness DB.
0
marrowyungSenior Technical architecture (Data)Author Commented:
any feature you know in SQL server 2014//2016 can solve the replication nightmare ?

any idea  to take out the replication but use sth else?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
It says in the article distributor -> publisher -> subscriber.

I would do it in that order.
0
marrowyungSenior Technical architecture (Data)Author Commented:
so you have tried before ? if we don't have distributor and do it publisher and then subscriber ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
so SQL server 2008 with SP3 upgrade SQL server 2014 SP1 also the same ?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I haven't done it myself but publisher then subscriber would make sense.  Going off of the information that Microsoft provided in the article.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok,

but one thing, we have other server which is not part of the replication server, so order should be :

1) normal server in any order (if there are SSIS package and job to call it, upgrade will handle this automatically ? for new hardware, how can we move SSIS package ?)
2) then SQL server in replication in the order of publisher and then subscriber. restart the publisher and then subscriber.

agree? what else  you can see ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
any update ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
1) any direction upgrade path from SQL server 2008 SP3 to SQL  server 2016?
 2) direct upgrade path from SQL server 2003 SP3 to SQL server 2014.
1) Why upgrade to MSSQL 2016 if still in development?
2) Always install last patches before update, so first install MSSQL 2008 SP4 and then you can migrate to MSSQL 2014

what is the steps to do the upgrade when replication in place? we have replication. any step by step guideline ?
Stop the replication, migrate the database and recreate replication.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"1) Why upgrade to MSSQL 2016 if still in development?"

we just planning about that and as our plan is upgrade SQL server next year, so 2016 is one of the choice and basically we all agree for SQL server 2014 with SP1, as SP1 provide much accurate number of record prediction. So execution plan much accurate.

"2) Always install last patches before update, so first install MSSQL 2008 SP4 and then you can migrate to MSSQL 2014
"

So if we are using SP3 now and we still need to upgrade it to SP4? downtime will be prolonged and any benefit by doing this before SQL server 2014 upgrade?

any needs to know for in-place upgrade and migration to SQL 2014 ? I do it a lot before basically for SQL 2005 -> 2008 R2, other than we need to script out user password and login using a separate script, and then detach and attach DB/backup +restore can do the job. SQL job can be script out.

will it be the same for SQL 2008 SP3 to SQL server 2014?

how about if we have SSIS package and job, any good way to migrate/upgrade it ? only script out all SQL job will do the job ?

"Stop the replication, migrate the database and recreate replication. "

tks. any step by steps link you can provide for me to follow ? I am quite worry about the SSIS and replication bit.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So if we are using SP3 now and we still need to upgrade it to SP4? downtime will be prolonged and any benefit by doing this before SQL server 2014 upgrade?
Any bug in SP3 will be resolved in SP4. Migrating as is can migrate the bug as well. It's only a best practice and not a must do thing.

any needs to know for in-place upgrade and migration to SQL 2014 ?
Just run the SQL Upgrade Advisor on the MSSQL 2008R2 instance.

how about if we have SSIS package and job, any good way to migrate/upgrade it ? only script out all SQL job will do the job ?
Yes, you can script out those objects or use Copy Database Wizard feature to perform the migration.
CopyDatabaseWizard.PNG
any step by steps link you can provide for me to follow ?
Try this MSDN article.
0

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
marrowyungSenior Technical architecture (Data)Author Commented:
"Any bug in SP3 will be resolved in SP4. Migrating as is can migrate the bug as well. It's only a best practice and not a must do thing.
"

basically  SP3-> SQL server 2014 > 2014 SP1 should be the same as SQL 2008 SP3-> sp4 -> SQL server 2014 -> 2014 SP1?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
An hotfix from one version doesn't fix bug from previous versions.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Yes, you can script out those objects or use Copy Database Wizard feature to perform the migration."

so copy database wizard can handle any SSIS job and migrate it?

any step by steps user guide for Copy database wizard on migrating SSIS jobs and all related?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
0
marrowyungSenior Technical architecture (Data)Author Commented:
will take a look later.
0
marrowyungSenior Technical architecture (Data)Author Commented:
sorry, any pre procedure and post steps before and after SQL server 2014 upgrade in a SQL replication and log shipping enabled environment?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The same for any SQL Server upgrade:
- Run Upgrade Advisor before migration to check any incompatibilities
- Run Backup and Reindex after migration
0
marrowyungSenior Technical architecture (Data)Author Commented:
your zone's holiday today ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
so no special thing to do before and after upgrade from SQL server 2008 ? specially SQL 2008 ?

how many success case you have experienced before? what problem usually it give?

please share about this on in place upgrade or migration
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I never did an in-place upgrade. It just doesn't worth it. Why keep a new SQL Server version in an old machine in an old Operating System? It's losing a good opportunity to upgrade all.

I would say a rate of 90% of success for side by side migration. The 10% that failed were mostly application issues (not prepared for new SQL Server version).
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I never did an in-place upgrade. It just doesn't worth it. Why keep a new SQL Server version in an old machine in an old Operating System? It's losing a good opportunity to upgrade all.

I understand when we need to upgrade the existing one should already very old, but in place upgrade solve a lot of problem which migration will left there. like some object can't be migrate but in-place upgrade.

this one will have to ask the company first, I am not sure how old it is. will the procedure will be diff, right?

BTW, copy database wizard will handle migration instead of in-place upgrade, right?


"I would say a rate of 90% of success for side by side migration. The 10% that failed were mostly application issues (not prepared for new SQL Server version).

that one is easy to solve, setup a test SQL server 2014 DB and let them point a clone test application server to point to that test SQL server 2014 and test it themselves !
0
marrowyungSenior Technical architecture (Data)Author Commented:
so the logic is :

order of upgrade:

distributor -> publisher -> subscriber.


1) Just run the SQL Upgrade Advisor on the MSSQL 2008R2 instance.
2) Stop the replication,
3) migrate the database (script out SSIS Package or use Copy Database Wizard feature to perform the migration, or by backup restore, detach/attach DB)
3) recreate replication.
4) done.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
BTW, copy database wizard will handle migration instead of in-place upgrade, right?
Yes. For in-place upgrade you don't need to migrate databases.

so the logic is : (...)
correct :)
0
marrowyungSenior Technical architecture (Data)Author Commented:
ahha , I will close this ticket but might come back if I have some question, as I will write down the procedure starting from next week.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Good luck with the migration.
0
marrowyungSenior Technical architecture (Data)Author Commented:
basically I do the migration a lot of time but without replication, this concern me.
0
marrowyungSenior Technical architecture (Data)Author Commented:
this should start by the first quarter of next year. I don't hope I will come back because of this.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.