当脚本托管在Azure存储帐户上时，从PowerShell调用SQL脚本 [英] Call a SQL script from PowerShell when the script is hosted on a Azure Storage Account

问题描述

在我的生活中不能知道如何做到这一点。 

我正在使用Invoke-SQLCmd并且可以从文件中获取-InputFile目录没有后顾之忧，但似乎无法解决如何获取存储在blob容器或azure文件中的sql文件。我能想到的唯一方法就是启动Azure VM并使用Net Use挂载
Azure文件共享，然后使用UNC引用该文件。 

原因我我正在考虑这一点，以便我可以从Azure自动化帐户调用.sql文件，而不必将所有SQL嵌入到PowerShell Runbook中。这可以，但我想要一个更简洁的方法。 

问候 

Alan 

解决方案

Hello   从下载SQL脚本Azure存储帐户。

使用 Invoke-Sqlcmd 在提供所有必要参数的目标Azure SQL数据库上运行此脚本。

实施后的Powershell Runbook看起来像这样：

 #define DB-related参数

ServerInstance =" xxxxxxx" ＃看起来像'xxxxxxx.database.windows.net'

Database =" xxxxxxx"

＃使用运行方式连接进行身份验证

Can't for the life of me work out how to do this. 

I'm using Invoke-SQLCmd and can pick up -InputFile from a file directory no worries but can't seem to work out how to pick up a sql file stored in either a blob container or azure files. Only way I could think of is spinning up a Azure VM and mounting an Azure file share using Net Use and then referencing the file using a UNC. 

The reason I'm looking at this is so that I can call .sql file from an Azure Automation Account and not have to embed all the SQL in a PowerShell runbook. This works but I'd like a neater way to do it. 

Regards 

Alan 

解决方案

Hello Alan,

I do like the mentioned VM approach, but if the requirement is to run a SQL script stored in a Blob container from an Azure Runbook, you could try something much simpler like this:

• Import the SqlServer Poswershell Module ( <Your Azure Automation account> -> Modules -> Browse Gallery -> Search for SqlServer -> Import ). Here is a screenshot for your reference:
  
  
  
  
• Use the Get-AzureStorageBlobContent to download your SQL script from Azure Storage Account.
• Use Invoke-Sqlcmd to run this script over the intended Azure SQL Database supplying all the necessary parameters.

This Powershell runbook when implemented would look something like this:

# Define DB-related parameters

ServerInstance = "xxxxxxx" #Looks something like 'xxxxxxx.database.windows.net'

Database = "xxxxxxx" # Authenticate using Run As connection

这篇关于当脚本托管在Azure存储帐户上时，从PowerShell调用SQL脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！