2

I would like to mirror/replicate my databases to the cloud. The cloud is setup using VPN to my actual machine. I am a bit confused by the options I have and would like to get some light into it. My setup is a SQL Server 2012 (standard edition) Instance which should be mirrored/replicated to a SQL server 2014 instance. The instance contains 30 databases which are in full recovery mode.

One other option would be to take a full backup, restore in cloud and later restore a differential one. But this might take some time. I would love the minimize the downtime that's why I thought about the replica/mirror way.

What are my options and riks at this point?

Improve this question
4
  • 1
    Is it Azure database in Azure cloud or is it VM running SQL Server in Azure cloud ?
    – Kin Shah
    Commented May 20, 2015 at 14:28
  • 1
    It's a VM running SQL Server. Not azure.
    – RayofCommand
    Commented May 20, 2015 at 14:32
  • Are you using Enterprise edition ? Also, do you want the DB in cloud readable ? How much the data can be stale/old in cloud ?
    – Kin Shah
    Commented May 20, 2015 at 14:38
  • sql server 2012 Standard edition. The data should be "the same" as on my machine, that I can dismiss my server and point to new machine in the end.
    – RayofCommand
    Commented May 20, 2015 at 14:43

2 Answers 2

Reset to default
2

The data should be "the same" as on my machine, that I can dismiss my server and point to new machine in the end.

Based on your comments, your best option would be Database Mirroring or Log shipping.

You have to evaluate your RPO and RTO. Since you are using VPN to your actual machine - meaning it is point-to-point connection :

  • If you go with Database Mirroring :

  • You have to use Asynchronous mode. Also, if you are not running in the same Active Directory, you’ll also need to factor in the time to re-create and tie out the accounts when calculating the RTO value.

  • RPO: As of last good synchronization

  • RTO: (Time of failure + Time of client redirect to New System ) - Time of last good synchronization

  • If you go with Log Shipping (min will be 1 min) :

  • Because the log file is copied to a Windows share, this solution requires both networking access and an Active Directory integration.

  • RPO: As of last good log backup application to the secondary system.

  • RTO: (Time of failure + Time of client redirect to New System ) - Time of last good synchronization

Refer to this excellent article by Buck Woody : Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server

Improve this answer
2
  • thank you very much, this helped a lot. Since I use this for a migration - would transactional replication work as well? If no, why not? Thank you!
    – RayofCommand
    Commented May 21, 2015 at 13:03
  • btw. asynchronous is not supported in standard. only synchronous in full safety mode. msdn.microsoft.com/en-us/library/…
    – RayofCommand
    Commented May 28, 2015 at 15:39
2

It sounds like you're trying to setup a disaster recovery environment, so that if your local machine blows-up you can switch over to the Azure machine.

There are a number of options, each with their own pros and cons. These are well documented elsewhere (see this link for an overview from Brent Ozar: http://www.brentozar.com/archive/2011/12/sql-server-high-availability-disaster-recovery-basics-webcast/). That said, you might want to look at Log Shipping and Asynchronous Database Mirroring particularly.

Log shipping is an automated process for taking your log backups and restoring them on another server (e.g. Azure), you can take log backups every five minutes so that you're Azure server is always within about five minutes of your local machine. The upside is that it's easy to configure and can be setup through the SSMS GUI.

Database Mirroring takes this to the next level, constantly sending completed transactions to the other server (i.e. Azure) so that it's within a minute or so of your local machine (this depends on your network connection, of course).

There are other options but if you're new to disaster recovery but I'd recommend looking at these to start with.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.