I need some help with getting data from my SQL MI into my Azure VM

Question

Good day,

We are busy moving from on prem solution into Azure.

A big issue I have run into is, currently we have automated jobs that takes data from SQL, runs a PowerShell script, creates a CSV where it then gets dropped onto my VM where it can be access by a third party via SFTP.

We decided to go with a SQL Managed Instance and an Azure VM. I am now stuck since you cannot perform PowerShell scripts on a SQL MI. Options I have come across so far is using Azure Logic apps to do the data collection and generate my CSV file where it gets stored in blob storage.

However, I can't see any "easy" way to get the files onto the VM where we can have the third party collect the file via SFTP.

I would also like to mention I want this automated with as little human intervention as possible.

Answer 1

To automate the process of transferring data from your SQL Managed Instance (SQL MI) to an Azure VM, you can consider the following approach:

1. Use Azure Logic Apps: As you mentioned, Azure Logic Apps can help you automate the data collection and CSV generation. You can set up a Logic App to trigger based on a schedule or an event that runs a query against your SQL MI and generates a CSV file.
2. Store in Azure Blob Storage: Once the CSV file is created, you can store it in Azure Blob Storage. This is a good practice as it allows for easy access and management of files.
3. Transfer to Azure VM: To move the CSV file from Azure Blob Storage to your Azure VM, you can use Azure Automation or Azure Functions. Here’s how:
   • Azure Automation: Create a runbook that uses PowerShell to download the CSV file from Blob Storage to your Azure VM. You can schedule this runbook to run at specific intervals.
   • Azure Functions: Alternatively, you can set up an Azure Function that triggers when a new file is uploaded to Blob Storage. This function can handle the file transfer to your Azure VM.
4. SFTP Access: Once the file is on your Azure VM, you can configure an SFTP server on the VM to allow the third party to access the files. Ensure that proper security measures are in place to manage access.

By implementing these steps, you can achieve an automated workflow with minimal human intervention, ensuring that your data is regularly transferred and accessible to the third party via SFTP.

---

References:

• Automate management tasks using SQL Agent jobs in Azure SQL Managed Instance
• Manage databases in Azure SQL Managed Instance by using Azure Automation

Answer 2

Could you run the PowerShell script from Windows Task Scheduler on the VM?

Answer 3

Hi @Ziaan Kunneke

Thank you for contacting to Microsoft QA.

It seems like you're transitioning to Azure and running into some challenges with automating the data flow from your SQL Managed Instance (SQL MI) to your Azure VM for SFTP access. Here's a comprehensive approach you can take:

Azure Logic Apps: You're already considering using Azure Logic Apps for collecting data and generating your CSV file. This is a solid option! You can set up Logic Apps to automate the retrieval of data from your SQL MI and create a CSV file in Blob Storage.

Automated File Transfer:

• After creating your CSV file in Blob Storage, the next step is to get that file onto your Azure VM. You can achieve this by using Azure Functions or even by employing a scheduled Azure Batch job.
  • Alternatively, you can use AzCopy, which is a command-line tool designed for copying data to and from Azure Storage accounts. You can run AzCopy on your Azure VM through a scheduled task or Azure Automation to periodically download the newest CSV file.
  SFTP Access: Once the CSV is on your Azure VM, you can configure your SFTP server (like OpenSSH for Windows) to share the directory where the CSV files are located so that your third party can access them as needed. Minimal Human Intervention: To ensure minimal manual oversight, consider using:

  - **Runbooks** in Azure Automation to handle tasks like moving files or starting processes automatically.
  
     - **Alerts** that notify you if something goes wrong in this automated process.
  

Here are some extra resources that might help you:

• Azure Logic Apps documentation
• Using AzCopy
• Azure Automation

Follow-Up Questions:

1. What specific type of data do you need to extract from SQL MI?
2. How frequently do you need the CSV files generated and transferred?
3. Would you prefer to use Azure Functions or another service for handling the file transfer to your VM?
4. Are there any specific constraints on using external storage or the SFTP server you’re working with?