Sql-server – SQL Server Availability Groups between virtual server and a bare metal sql server

Swiped from my answer on StackOverflow, only to prevent others from spending effort on the same type of answer.

Personally I think this will work a lot better than transactional replication, though I haven't done any formal comparisons of the two in a true migration scenario. I know that with the amount of troubles folks have with transactional replication, compared to even standard mirroring (and this is an upgrade of that), you are certainly bound to have fewer problems.

The biggest boon is that the secondary can be marked as read only - so you can run all the reporting off of it that you want, and it won't affect the mirroring at all. You just need beefier tempdb (since it essentially uses rcsi to do this).

Of course you do need to be aware that both sides of the AG need to be fully licensed in order to use the replica for read-only operations. And both sides need to be running on specific versions of Windows (Enterprise or better on 2008 R2 and lower; Standard or better on 2012 and above) because they require failover clustering - the SQL instances in AGs don't need to be clustered, but they need to be sitting on top of that infrastructure in the OS.

This is perfectly viable (and is how I moved several databases before). The outline of the process is this:

1. Make sure the source database is in full recovery mode
2. Configure the source to no NO_TRUNCATE transaction log backup (so the log is preserved)
3. Full Backup the source. Preferably use backup compression
4. Take your full backup (on a USB stick if it takes to long to copy over the network) to the target
5. Restore full backup on target
6. Take a log backup of the source
7. Move (using your fastest method as in 4) the log backup to the target an apply it there
8. Configure mirroring in async mode (source and target can now mirror)
9. Start regular transaction log backups again on the primary
10. Wait for mirror to catch up (use the mirror admin tools to monitor)
11. Set mirror to sync mode
12. Move the app pointer and manually fail over the mirror

A thing you have to be careful about is that you have enough space to hold the transaction log while the move is happening. But a 40GB database is tiny, so I assume you wont have a lot of tlog traffic on it.

Another thing to watch out for is that you have the same security set up on the target as on the source. If not, you may end up with orphaned users when you swing over the server. This is less of an issue if you only use Windows logins.

Getting this right takes a bit of practise. I would advise to test it out on a development system first to make sure you have all the steps scripted nicely.