AzureSQL Multiple Invoke-SQLCmd Loop and Connection Errors

3
votes

I am attempting to loop through an invoke-sqlcmd for multiple AzureSQL databases via Azure Automation. The first item in the loop executes, but the all the rest fail with a:

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I am guessing that I need to close the connection from the first invoke-sqlcmd before executing the next, but have not found a direct method to accomplish that with invoke-sqlcmd. Here is my loop:

param(      
# Parameters to Pass to PowerShell Scripts
[parameter(Mandatory=$true)][String] $azureSQLServerName = "myazuresql",        
[parameter(Mandatory=$true)][String] $azureSQLCred = "myazureautosqlcred"        
        )

# DB Name Array
$dbnamearray = @("database1","database2","database3")
$dbnamearray

# Datatable Name
$tabName = "RunbookTable"

#Create Table object
$table = New-Object system.Data.DataTable "$tabName"

#Define Columns
$col1 = New-Object system.Data.DataColumn dbname,([string])

#Add the Columns
$table.columns.add($col1)

# Add Row and Values for dname Column
ForEach ($db in $dbnamearray) 
{
$row = $table.NewRow()
$row.dbname = $db
#Add the row to the table
$table.Rows.Add($row)
}

#Display the table
$table | format-table -AutoSize

# Loop through the datatable using the values per column 
$table | ForEach-Object {

# Set loop variables as these are easier to pass then $_.
$azureSQLDatabaseName = $_.dbname

# Execute SQL Query Against Azure SQL
$azureSQLServerName = $azureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name $azureSQLCred
$SQLOutput = $(Invoke-Sqlcmd -ServerInstance $azureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $azureSQLDatabaseName -Query "SELECT * FROM INFORMATION_SCHEMA.TABLES " -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1
Write-Output $SQLOutput
}
3
powershellazure-sql-databaseazure-automationinvoke-sqlcmd
Check out this link first link. – anu start
My first sql command invocation works. It is likely the connection is still open so the second and subsequent attempts in the loop fail – Kode
To take control of opening/closing connections, you should use System.Data.SqlClient and use SqlConnection.Open() and SqlConnection.Close(). – anu start
How would I call invoke-sqlcmd in between those? This is AzureSQL so my invoke-sqlcmd requires the server and database names, so the connection.open seems like double connecting – Kode

3 Answers

2
votes

You can try making each connection as a powershell job. This solved a very similar issue I had some time ago. Send-MailMessage closes every 2nd connection when using attachments If you want to read an explanation. Basically, if you're unable to use a .Close() method, you can force connections to close by terminating the entire session for each run. In an ideal world the cmdlet would handle all this for you, but not everything was created perfectly.

# Loop through the datatable using the values per column 
$table | ForEach-Object {

# Set loop variables as these are easier to pass then $_.
$azureSQLDatabaseName = $_.dbname

# Execute SQL Query Against Azure SQL
$azureSQLServerName = $azureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name $azureSQLCred

# Pass in the needed parameters via -ArgumentList and start the job.
Start-Job -ScriptBlock { Write-Output $(Invoke-Sqlcmd -ServerInstance $args[0] -Username $args[1].UserName -Password $args[1].GetNetworkCredential().Password -Database $args[0] -Query "SELECT * FROM INFORMATION_SCHEMA.TABLES " -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1 } -ArgumentList $azureSQLServerName, $Cred | Wait-Job | Receive-Job

}

This is untested since I do not have a server to connect to, but perhaps with a bit of work you can make something out of it.

1
votes

I faced the same issue previously while doing something with the database of azure sql. You can try this

1. Create Automation Account

New-AzureRmAutomationAccount -ResourceGroupName $resourceGroupName -Name $automationAccountName -Location $location

2. Set the Automation account to work with

Set-AzureRmAutomationAccount -Name $automationAccountName -ResourceGroupName $resourceGroupName

3. Create / Import a Runbook

Here we already have a runbook ready so we import it. Here's the runbook code

workflow runbookNameValue
{
    inlinescript
    {
        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $MasterDatabaseConnection.ConnectionString = "ConnectionStringValue"

        # Open connection to Master DB
        $MasterDatabaseConnection.Open()

        # Create command
        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
        $MasterDatabaseCommand.CommandText = "Exec stored procedure"

        # Execute the query
        $MasterDatabaseCommand.ExecuteNonQuery()

        # Close connection to Master DB
        $MasterDatabaseConnection.Close() 
    }
}

4. Importing

Import-AzureRMAutomationRunbook -Name $runBookName -Path $scriptPath -ResourceGroupName $resourceGroupName -AutomationAccountName $automationAccountName -Type PowerShell

I hope this helps. Instead of using Invoke-Sqlcmd use the $MasterDatabaseCommand.ExecuteNonQuery() like i've provided in the runbook. It will work

1
votes

It seems that you append .database.windows.net to the server name inside the loop. I guess that's why it works for the first iteration only.

Just move this line:

$azureSQLServerName = $azureSQLServerName + ".database.windows.net"

before this line:

$table | ForEach-Object {