September 13, 2018 at 8:58 am
we need to migrate our sqlserver 2014 to 2016
this is the current situation:
2 nodes active-passive ms failover cluster on windows server 2008r2
biggest database is about 20Tb, simple model
we could have a new box to create the new sqlsever 2016 cluster, but what is the best way to upgrade reducing downtime?
could I create an always on sqlserver 2016 cluster on the new box and then add the old 2014 instance?
what are the main steps to achieve that?
thanks
October 7, 2018 at 10:28 pm
Here is the step by step procedure to migrate SQL Server 2014 to 2014. Go through it:
https://www.virtualizationhowto.com/2017/03/how-to-upgrade-sql-server-2014-to-sql-server-2016/
January 17, 2019 at 2:49 am
Thanks for the reply
unfortunately the procedure is for an update in place
I have a 20TB db that I have to migrate into another cluster that doesn't share the same storage.
I was thinking of using some replication methods, but I do not know exactly what the best way to proceed
I have to migrate from sqlserver2014 @ windows 2008r2 failover cluster (2nodes active-passive) into another cluster ,maybe windows2012 and sqlserver 2016
any ideas?
January 17, 2019 at 7:22 am
Technically you can create an AlwaysOn replica on a higher version of SQL Server than the source, but it comes with the caveat that once you fail over you would be unable to fail back because AO doesn't support replicas on a lower version of SQL Server.
AO/Log Shipping/Database Mirroring all have pretty much the same constraints, you need to get that initial copy of the database backup to the new server while maintaining the intervening transactions in the logs. Depending on your network and server performance, 20 Tb could take days, so make sure that you have enough storage for the logs.
Replication is different as it will use bulk copy under the hood to get the data across, you will need storage for those files as it creates them and you will need to write scripts to get the non-replicated objects across too. Replicating that much data may affect your server and network performance. If your database is busy, you will see locking and blocking slowing it down too. Personally, I would not use replication for a large database.
EDIT: I just noticed that you wrote that you are in Simple Reovery Model, you will have to use Full Recovery Model to use Always On.
January 17, 2019 at 7:37 am
To reduce downtime, you could use filegroup backup/restore, but you have to prepare your database for that
January 17, 2019 at 9:52 am
I think your best bet is log shipping.
Set up the new 2016 AG, and set up log shipping from the 2014 to 2016 box.
When you need to do the cut over, remove access to the SQL 2014 box, take a final log backup and make sure it gets applied.
Re-point your connection strings, and bring the 2016 databases online.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 18, 2019 at 1:01 am
Great!
thank you all for the advice, i'll go with the last solution, a new sqlserver 2016 AG box with log shipping from 2014 box
January 18, 2019 at 5:56 am
It might be easier that you think. I needed to upgrade a SQL FCI from SQL 2012 to 2014 and found this:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance?view=sql-server-2017
Basically, you upgrade the passive node, failover, upgrade the now passive node (formerlly active node).
Down time limited to a single manual failover.
For me, it couldn't have been easier.
January 18, 2019 at 7:10 am
dearneson1 - Friday, January 18, 2019 5:56 AMIt might be easier that you think. I needed to upgrade a SQL FCI from SQL 2012 to 2014 and found this:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance?view=sql-server-2017Basically, you upgrade the passive node, failover, upgrade the now passive node (formerlly active node).
Down time limited to a single manual failover.
For me, it couldn't have been easier.
The only drawback to this method is that there is no turning back should something go wrong.
Also, what are you going to 2016 and not 2017?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 18, 2019 at 8:54 am
Michael L John - Friday, January 18, 2019 7:10 AMdearneson1 - Friday, January 18, 2019 5:56 AMIt might be easier that you think. I needed to upgrade a SQL FCI from SQL 2012 to 2014 and found this:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance?view=sql-server-2017Basically, you upgrade the passive node, failover, upgrade the now passive node (formerlly active node).
Down time limited to a single manual failover.
For me, it couldn't have been easier.
The only drawback to this method is that there is no turning back should something go wrong.
Also, what are you going to 2016 and not 2017?
for us, SQL 2017 hadn't been approved yet.
January 18, 2019 at 9:02 am
dearneson1 - Friday, January 18, 2019 8:54 AMMichael L John - Friday, January 18, 2019 7:10 AMdearneson1 - Friday, January 18, 2019 5:56 AMIt might be easier that you think. I needed to upgrade a SQL FCI from SQL 2012 to 2014 and found this:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance?view=sql-server-2017Basically, you upgrade the passive node, failover, upgrade the now passive node (formerlly active node).
Down time limited to a single manual failover.
For me, it couldn't have been easier.
The only drawback to this method is that there is no turning back should something go wrong.
Also, what are you going to 2016 and not 2017?
for us, SQL 2017 hadn't been approved yet.
Actually, that was a question for the OP.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply