1

I have to move a database from one cloud provider to another. It has to be done in an hour window. The DB is 16Gb, and the line speed between the current host, and Azure (The final destination) equates to a 30 hour backup file copy. Far exceeding the time we have.

I wanted to do transnational replication, and once the main data volume is replicated over a few days, simply switch the website to use the replicated database, and kill the source.

Unfortunately, the database we are inheriting has numerous issues. Views are broken (Reference deleted tables/columns). And a lot of columns have no primary keys. (Please note, not my database. Well, it is now.. but..)

So, replication fails to even start because of the views, and then won't replicate tables with no PK.

My other option would to be do backups, and incremental backups. But do incremental backups split into separate files? If so, I could backup to a folder on the source, then move the large initial backup, and then move the incremental updates. Is that an option? Will incremental backups give me smaller files? I'd then be able to move the files over a period, and then on go live day, run the final backup, and move the small incremental file.

Improve this question
3
  • Look up log shipping.
    – vonPryz
    Sep 17, 2019 at 6:42
  • What is the destination cloud offering you are using? and the source.
    – Ramakant Dadhichi
    Sep 17, 2019 at 11:04
  • The source is a SQL Server 2008R2 database running on a VM in a small hosting company. The final target destination would be Azure SQL. I tried replication to an Azure VM with SQL Server 2012, and then the plan was a backup in Azure, and then restore to Azure SQL.... but due to the poorness of the DB, transnational replication isn't possible. My other constraint is - we're trying not to open firewall ports. So push (like can be done with Replication) was appealing.
    – Craig
    Sep 17, 2019 at 22:19

1 Answer 1

Reset to default
2

Absolutely, that is an option. Do the full backup, and then log backups. At the receiving end, restore all but the last using NORECOVERY.

Automating regular log backups and then restoring at the other end is what we call "log shipping", and there's a feature to do that using Agent jobs in SQL Server. Might, or might now, be relevant for you depending on your setup. Possibly not because of your cloud environment (it uses a share to store the backup files, which the originating server needs access to.

Whether to then stripe one backup over several files (not sure if you had this in mind), depends on whether it is beneficial to have several files for the transfer process. I doubt you gain much with striping for the log backups.

Improve this answer
3
  • Thank you. This sounds promising. What would the work flow be? Do a full backup - nothing special, and transport that bak file to the destination. And do a normal restore. Then, periodically, do incrementals, and that will produce a bak file per incremental? Transport each file to the destination, and restore, until the last one, which will be 'NORECOVERY'? With regards the automated version, we're not allowed to open ports on the production box. But we Azure. Can I create a share in Azure and the backup files go there - and the source can 'see' that?
    – Craig
    Sep 17, 2019 at 22:25
  • With regards stripe. Not sure what that is. We have a big risk in that the line quality between the prod server, and Azure, isn't optimal. So, a file copy fail during the bug 16Gb file would be an issue. If I could split the files, that would maybe lessen the risk of failed copies?
    – Craig
    Sep 17, 2019 at 22:26
  • Yes, you get the basic flow. But it is the other way around regarding recovery: NORECOVERY on all but the last one where you say RECOVERY (which is default). As for ports and share and Azure, I'm afraid I can't answer that. Striping is a bit like RAID0, you divide the data pretty evenly across a number of files: just specify more than one backup file in the BACKUP command, TO DISK = 'a.bak', DISK = 'b.bak', ...
    – Tibor Karaszi
    Sep 18, 2019 at 6:41

Your Answer

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

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