SQL Server AlwaysOn With LogShipping

Question

We are planning to migrate all SQL Server 2008 R2 to SQL Server 2016.

Old environment:

SQL Server 2008 R2 with Log shipping 

New environment:

SQL Server 2016 With AlwaysOn (1 Primary+2 Secondary)

Now the new envrironment is ready with AG. the next step is to plan for curover with minimum downtime.

In order to achieve i am planning to configure Logshipping between Old and New environmnet.

In this scenerio how can i configure logshipping between. Once migrated we don't need Logshipping.

Answer 1

Hi S.Vijay,

In your scenario, you are going to migrate SQL Server 2008 R2 to SQL Server 2016 with minimum downtime. So you choose log shipping to do the migration work. Right?

If so, it is able to do the log shipping between lower version SQL Server 2008 R2 to higher version SQL Server 2016. But the database should be set in "No recovery mode".

However you should know that after doing the log shipping, it is impossible to go back from SQL Server 2016 to SQL Server 2008 R2. Which means there will exist some potential risks.

Thereby, basically it is able to achieve your requirement with log shipping. However this is not a good option. 

Thanks,
Xi Jin.

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi Xi,

In this case which is the good option?

Answer 3

What Xi describes is the best option. You can simultaneously log ship to another SQL Server 2008 r2 server which will protect that instance in case you have a catastrophic failure and need to recover somewhere else. This way you can failover and failback from the 2 SQL 2008 r2 instances.

Answer 4

In order to achieve i am planning to configure Logshipping between Old and New environmnet.

In this scenerio how can i configure logshipping between. Once migrated we don't need Logshipping.

In other words, this is a one-time log shipping that's being used for migration purpose. You can follow the quick steps below for minimal downtime during the cut-over. 

1) Take FULL backups of all user DBs on old 2008 R2 instance and restore with NO RECOVERY on the new AG primary replica (SQL 2016).

2) Keep the new Primary replica updated by continuously restoring DIFF or T-log backups, again, with NO RECOVERY.

3) Migrate all the instance level objects that are outside the DB such as Logins, Jobs, Server Triggers, Proxy, credentials, operators, Linked Servers etc.

4) When it's time to cut over, take the DBs in single_user mode (on 2008 R2) and perform the last Log backup and restore on to the new server WITH RECOVERY. 

The above is log-shipping too, but not the one you would create using GUI etc. 

Once all DBs are restored on the new primary replica (SQL 2016), add them to the Availability Group and synch with the two secondary replicas. 

Hope this helps!

---

Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

Answer 5

Hi Mohsin,

This is look good.

A small doubt. Currently we took the backup of Old SQL Server 2008 Db's and restored in SQL 2016 and configured AG. Now application team is test from application end. like HA and DR.

What my plan is once application testing is done, i will delete all DB's from 2016 and configure Logshipping for actual migration.

Does it looks good?

Answer 6

What my plan is once application testing is done, i will delete all DB's from 2016 and configure Logshipping for actual migration.

Does it looks good?

Yes, once testing goes well, remove all DBs from AG and drop them from all replicas and start fresh restores. 

Alternatively, you can leave the DBs as is and when you do the first FULL backup restore (for actual migration), you can check the option "Restore with Replace" to overwrite the existing DBs. It's really your choice.

---

Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

Answer 7

Excellent!.

Need one more guidance, could you please provide monitoring setup for Alwayson.

We are not going to use any Third Party tools to monitor AG.

If you provide any script/any document, those are more helpful to us.

Answer 8

If you provide any script/any document, those are more helpful to us.

The following article has some great scripts that you can schedule from SQL agent job to run every x mins and send an email alert if latency exceeds the acceptable threshold etc.

https://www.sqlshack.com/measuring-availability-group-synchronization-lag/

There are other ways too, such as using Extended Events. As a matter of fact, When you configure AG, an event session is created by default. The above link should help you with what you're looking for.

---

Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

Answer 9

We are planning to Migrate SQL Server from 2008 to 2016 using logshipping.

Logshipping is running on current SQL Server 2008 (1 Primary+4 Secondries).

As part of Migration i am planning to adde SQL Server 2016  as secondary to 2008 Logshipping to sync data.

In this case How to add SQL Server 2016 server to Logshipping as secondary?

so that we can minimize the cutover downtime;

Answer 10

You don't need to add 2016 as the secondary to your current log shipping setup. You can do the same thing outside the current log shipping setup. Copying my response from earlier thread.

• In other words, this is a one-time log shipping that's being used for migration purpose. You can follow the quick steps below for minimal downtime during the cut-over. 

  1) Take FULL backups of all user DBs on old 2008 instance and restore with NO RECOVERY on the new SQL 2016.

  2) Keep the new 2012 DB updated by continuously restoring DIFF or T-log backups, again, with NO RECOVERY.

  3) Migrate all the instance level objects that are outside the DB such as Logins, Jobs, Server Triggers, Proxy, credentials, operators, Linked Servers etc.

  4) When it's time to cut over, take the DBs in single_user mode (on 2008) and perform the last Log backup and restore on to the new 2016 server WITH RECOVERY. 

  The above is log-shipping too, but not the one you would create using GUI etc. 

  Hope this helps!

---

Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

Answer 11

How is this question different from one you already asked which you marked as answer. PLEASE dont just post for sake of posting, read what responses are posted. We take time to answer your question and when you repeatedly ask same question it defeats the purpose

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 12

Hi Mohsin,

I already started Step2

 2) Keep the new 2012 DB updated by continuously restoring DIFF or T-log backups, again, with NO RECOVERY.

If possible could you please provide any T-SQL script  which can dynamically restore TRN file instead of Manually. So that i will create a SQL JOB which can run every 1 Hr to restore TRN files.

Answer 13

Hi Mohsin,

I already started Step2

 2) Keep the new 2012 DB updated by continuously restoring DIFF or T-log backups, again, with NO RECOVERY.

If possible could you please provide any T-SQL script  which can dynamically restore TRN file instead of Manually. So that i will create a SQL JOB which can run every 1 Hr to restore TRN files.

Can you post this as a new thread, preferably in the T-SQL forum if you still need help with this? Not that I don't want to help or something, it's just that your question will have better visibility. Once you have posted it, I'll jump on to that thread.

---

Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.