SQLTeam.com | Weblogs | Forums

SQL 2014 Alwayson Migration

sql2014

#1

I'm planning to migrate from SQL 2012 to SQL 2014. We are using AlwaysOn on SQL 2012 with over 100 databases. I have tested restoring full backup and differential with no recovery on all the 2014 replicas (node A, B and C). I recovered the primary replica on A node and used TSQL to add the databases to AG. On the secondary replicas(B and C) I used TSQL query to add the databases to HADR. that way, I was able to have all the databases on AlwaysOn feature without having to restore it just on the primary replicas and start a full backup and log back to restore on the secondary replicas. I also used that method because I'm using TDE and it is time consuming to decrypt all the 100 databases to use the GUI. I don't think it was possible on SQL 2012 without taking a full and log backup. Please have any of you tried that before or used this method. Is there any issues doing that way.
Thanks,
Moe


#2

To add replicas to an AG, you always have to start with a full backup and then at least one log or whatever log chain there is.


#3

Tara you're right that's what I know too. but I'm migrating from SQL 2012 to SQL 2014 and I restored full backup from 2012 SQL to 2014 with no recovery to 3 replicas and Added another differential with no recovery as well. I recovered on the primary and add the databases to AD on the primary and on the secondary I executed tsql to establish HADR and worked perfectly. Have you tested that before. just to mention, I tested this scenario 3 times so far


#4

Hmmm, maybe they changed it in 2014. Not sure. If it worked for you, then I'm guessing they changed it.


#5

well, that's why I was surprised and asking if there is any issues doing that way. So far I tested it 3 times I also created tables and data in the primary to make sure it is replicated.


#6

I am a bit confused by what you did - it sounds like you did in fact restore to the replicas.

So if I am following this...you backed up the databases on 2012, restored to primary and all replicas - restored differentials to primary and all replicas with norecovery, then brought primary online (recovered) - added the databases to the AG and then enabled HADR on the replicas.

Am I missing something?


#7

I believe what he is asking is that why didn't he have to add the first log after the full/diff. One log is required to be backed up and restored to add a replica to an AG. The 2012 and 2014 documentation mention it in the "To prepare a secondary database" section of this article: https://msdn.microsoft.com/en-us/library/ff878349(v=sql.120).aspx. I am not sure why he didn't get an error on 2014. I always got an error on 2012 if I skipped it.


#8

Hello Jeff! Preparing to migrate to 2014.I backed up the databases from SQL 2012 and restored them on SQL 2014 on 3 replicas (A,B and C) I did full and differential just to test it. I recovered on the Primary A node and added the databases to AG after that I executed a query to add the databases to HADR on the other replicas(B and C). worked like charm I tested it 3 times so far.
Tara,
when it is time to migrate production I will do restore Full and diff as I did on my testing environment. and when it is time to migrate I will backup the log on SQL 2014 and restore it on SQL 2014. Recover on the primary and follow the same steps I did on staging. But as I mentioned I didn't took any log backup after I recovered the primary replica and the databases joined the AG with no issues. Thank you guys for any thoughts or inputs


#9

This may have worked - but I wouldn't trust it. When you recover a database - that is when it is actually upgraded and since you restored a 2012 backup to 2014 those databases on the secondary have not been upgraded yet.

Have you tested failing over to the secondaries?