0

I'm completely stymied. Let me describe my situation.

We're a relatively small company and the vast majority of our operational data is contained in a vendor database. Our vendor offers a Data Warehousing service. They've taken all of our data and applied some OLAP-ish modeling to it. Each day, they place either a .bak or a .diff file (.bak once a week, .diff every other day) in a FTP endpoint that we pay to access. Currently, we use a PowerShell script to download this data to a server that we've got sitting at a local server farm, where we then use SQL Server to "rehydrate it" by restoring from it.

That's all fine and good, but we really want to move as many of our workloads into the cloud as possible (we use Azure). As far as I can tell, SQL Managed Instances are the only way we can restore from a .bak file in the cloud. This is waaaay more expensive than we need, and we really don't need the managed instance platform at all except to restore from this file.

Basically, everything about this current process is diametrically opposed to us moving it to the cloud, unless we want to pay even more than we are to rent out this server farm.

I'm trying to lobby them for a different method of getting their data, but I'm having trouble coming up with a method to propose. We need to, every day, transfer a ~40gb database from SQL Server (at our vendor) to Azure SQL (in our cloud). What's the least-intrusive way we could do this?

nyx
  • 51
  • 1
  • 4
  • You might want to try this: https://stackoverflow.com/questions/33436344/restoring-sql-server-backup-to-azure-sql-database – Alex Nov 11 '20 at 23:59
  • As far as I understand they run some processes to extract data into another database and then they give you a backup of said database. Ask them if they can run said processes directly against Azure SQL Database, thus bypassing all the cumbersome backup exchanges. – Alex Nov 12 '20 at 00:52
  • Not a direct answer, but SQL Server on Azure VMs is a pretty good solution. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/ You can opt in to automated backup, patching, and get an OOB 99.9 uptime SLA without a cluster if you use premium disks. – David Browne - Microsoft Nov 12 '20 at 00:56
  • If you can configure the Data sync for your Azure SQL database and the on-premise SQL Server at vendor, this can be a good way. – Leon Yue Nov 12 '20 at 06:19
  • Hey all, thanks for the suggestions! I've actually seen all of those resources already. The biggest obstacle here is that the entire process has to be automated end-to-end, which makes bacpac restores more difficult (they'd have to write some sort of .NET app to back up to bacpac). Alex, good suggestion. Answer is probably no, but at least worth looking into. Leon, Data sync is not an option because ALL Azure SQL DBs are UTC, and our vendor's db is not (time synchronization is required for data sync). I think SQL on Azure VM is the only real option, so I may have to look at cost for that. – nyx Nov 12 '20 at 15:50
  • @nyx You've welcome. We are glad that you choose the Azure SQL on Azure VM as the solution. Thanks for Alex and Davaid too. I help you post it as answer. You can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Leon Yue Nov 13 '20 at 00:41

1 Answers1

0

We are glad that you choose the Azure SQL on Azure VM as the solution. Thanks for the suggestions of Alex and Davaid too:

  • I've actually seen all of those resources already. The biggest obstacle here is that the entire process has to be automated end-to-end, which makes bacpac restores more difficult (they'd have to write some sort of .NET app to back up to bacpac). I think SQL on Azure VM is the only real option, so I may have to look at cost for that.

If others face the same scenario, we could reference this. This also can be beneficial to other community members.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23