I have been tasked with migrating a database, size being up to 15Gb, from a SQL Server 2008 database in a 3rd party cloud VM, to SQL Azure. On go-live day, when we switch from the old host, the Azure, we have a 1 hr downtime allowance.
Unfortunately, the link speed is 1Mbps. So that would take around 30 hours to transfer. What I would like to do is create a Azure VM, with SQL Server 2008R2. Then, using either transaction replication, mirroring or log shipping, allow the database to replicate to my new VM from the source. I have no time limit for this, as the application migration is at my discretion. Could be a day, could be a few months.
My question is – how can I achieve this? I think transaction replication would be best, so that the database is basically up to date all the time. I need to demonstrate that it's possible, so I have created two VMs in Azure (ClientSourceVM and ClientStagingVM) in separate vnets (Trying to simulate across the internet replication).
The initial snap shot would take up to two days to transfer. Would this cause any adverse effect on the source database? Locking?
And is it possible to do replication across the internet? It seems I need to share folders and setup permissions, but not sure how that works with servers in separate domains across the internet. Is it possible?
With SQL Server 2008R2 – is there any better option that can serve my needs?
Note, once the database gets to 2008R2, I'll then hopefully migrate that to it's Azure SQL destination on go-live day. The replication is purely to allow an up to date database be available on the go-live day without waiting nearly 2 days for the backup file to copy.
Any advice and ideas would be greatly appreciated.
I could do on-prem SQL 2008 directly to Azure? And then on go live – simply stop the replication? I’d need to do more to the source database to make it a stand-alone? Or do I just remove replication – setup users and I’m good to go? I’d obviously do dry runs on a separate test environment – but the idea is do-able? We don’t have huge data spikes at all. It’s a database with only a few concurrent users. Less than 10.
Best Answer
nick-mcdermaid: I can tell you that I've set up a push replication from on-prem SQL Server (2008) to Azure SQL. The snapshot took a while but didn't appear to cause any issues.
The subsequent transactional replication worked, but due to the batch load nature of the source, we kept running out of log space and we could not keep it running. So as long as changes to the database are consistent (and you don't have huge spikes of huge amounts of activity) it should work fine.
Cross domain is only relevant if you want to use Windows Authentication. Since Azure SQL doesn't support windows authentication, just plan to use SQL authentication.
It’s completely doable. You just have to do push subscription from on-prem using SQL authentication. But first be aware of the major gotchas of migrating to SQL Azure. Three obvious ones:
Transactional Replication to Azure SQL DB now in public preview