SQL server 2014/2016 upgrade

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 !

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

You can go from 2008 SP3 directly to 2016.

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 ?

any idea?

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.

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.

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?

It says in the article distributor -> publisher -> subscriber.

I would do it in that order.

so you have tried before ? if we don't have distributor and do it publisher and then subscriber ?

so SQL server 2008 with SP3 upgrade SQL server 2014 SP1 also the same ?

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 ?

any update ?

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.

"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.

"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?

An hotfix from one version doesn't fix bug from previous versions.

"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?

Check if my article SQL Server database migration - The Copy Database Wizard method can help you.

will take a look later.

sorry, any pre procedure and post steps before and after SQL server 2014 upgrade in a SQL replication and log shipping enabled environment?

The same for any SQL Server upgrade:
- Run Upgrade Advisor before migration to check any incompatibilities
- Run Backup and Reindex after migration

your zone's holiday today ?

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

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

"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 !

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.

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

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.

Ok. Good luck with the migration.

basically I do the migration a lot of time but without replication, this concern me.

this should start by the first quarter of next year. I don't hope I will come back because of this.