使用powershell连接mssql的相关操作记录

input params

假设我们需要执行的脚本是a.ps1，我们可以进入到对应目录下，在powershell中执行.\a.ps1的命令，其中定义参数代码示例如下：

param( [Parameter(Mandatory=$true)] $localHostName,
              $Account="root",$password="123456789")

写在开头，其中Mandatory=$true修饰的参数是必须传入的，其他不传参的情况下使用默认参数。

logFile

定义logFile文件路径：

$logFile = "C:\config.log"
Start-Transcript -path $LogFile -Force -Append –NoClobber(防止被覆盖)
......
Write-Output ('-- log1;')
......
Write-Output ('-- log2;')
......
stop-transcript

连接与执行

连接函数；事务执行；非事务执行；

Function GetSqlConnection([string]$ServerName){
  IF ([string]::IsNullOrWhitespace($ServerName)){
    $ServerName = [System.Net.Dns]::GetHostName()
  }
  [string]$ConnectionString = "Data Source=$ServerName;Initial Catalog=$Database;user id=$userName;pwd=$password"
  try{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
    $SqlConnection.Open()
    Write-Host 'Connected to sql server success.'
    return $SqlConnection
  }
  catch{
    Write-Warning ('Connect to database failed with error message:{0}' -f,$_) | WriteLog
    $SqlConnection.Dispose()
    return $null
  }
}

function Execute-SqlCommandNonQuery
{
    param
    (
    [System.Data.SqlClient.SqlConnection]$SqlConnection,
    [string]$Command
    )
    $cmd = $SqlConnection.CreateCommand()
    try
    {
        $cmd.CommandText = $Command
        $cmd.ExecuteNonQuery() | Out-Null
        return $true
    }
    catch [Exception] {
         Write-Warning ('Execute Sql command failed with error message:{0}' -f $_)
         return $false
    }
    finally{
        $SqlConnection.Close()
    }
}

function Execute-SqlCommandsNonQueryWithTran
{
    param
    (
    [System.Data.SqlClient.SqlConnection]$SqlConnection,
    [string[]]$Commands
    )
    $transaction = $SqlConnection.BeginTransaction()
    $command = $SqlConnection.CreateCommand()
    $command.Transaction = $transaction
    try
    {
        foreach($cmd in $Commands) {
            #Write-Host  $cmd -ForegroundColor Blue
            $command.CommandText = $cmd
            $command.ExecuteNonQuery()
        }
        $transaction.Commit()
        return $true
    }
    catch [Exception] {
         $transaction.Rollback()
         Write-Warning ('Execute Sql commands failed with error message:{0}' -f $_)
         return $false
    }
    finally{
        $SqlConnection.Close()
    }
}

T-SQL执行示例：

 $tSqls=@("CREATE database dba;"
                    ,-Join("CREATE database ",${dbName})
        $conn=GetSqlConnection $dbName
        Execute-SqlCommandsNonQuery  $conn $tSqls

多线程执行

foreach ($computer in $computers)
{
    $ScriptBlock = {
        Param (
            [string] [Parameter(Mandatory=$true)] $svrInstance
        )
        enable-SqlAlwaysOn -ServerInstance $svrInstance  -Force
    }
    Start-Job -Name "enablealwayson---$computer" $ScriptBlock -ArgumentList $computer
}
#While loop that will wait until the remaining jobs are finished
for($c=0; $c -lt $computers.Length * 20;) {
    if ($(Get-Job -State Completed | Where-Object {$_.Name.Contains("enablealwayson")}).Count -eq $computers.Length){
        Remove-job -State Completed
        break
    }
     $c++
    Start-Sleep –s 3
     if ($c-eq $computers.Length * 20){
         throw "error"
     }
}

有关powershell多线程执行的操作，可以参考这篇文章：https://www.cnblogs.com/zqj-blog/p/10120743.html