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
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.
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
Hmmm, maybe they changed it in 2014. Not sure. If it worked for you, then I'm guessing they changed it.
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.
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?
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.
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
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?