У цій статті ми розглянемо всі робочі способи підключення до SQL Server і виконання SQL запитів з PowerShell. Зазвичай таке завдання стоїть перед системними адміністраторами, які займаються написанням сценаріїв і автоматизацією на PowerShell.

Є багато різних способів роботи з SQL Server через PowerShell, і дивлячись на статті в інтернеті в них легко заплутатися, тому що в різних статтях описані різні способи, і навіть у досвідченого адміністратора може виникнути питання.

T-SQL запити в PowerShell через System.Data.OleDb

Так як PowerShell має доступ до класів .NET, то для виконання T-SQL можна використовувати класи, що знаходяться в System.Data.OleDb.

Приклад PowerShell скрипта з використанням System.Data.OleDb. Виконаємо SELECT запит до таблиці в базі даних MS SQL:

$dataSource = “serverinstance”
$database = “master”
$sql = “SELECT * FROM sysdatabases”
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; “ +
“Initial Catalog=$database; “ +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
$rows=($dataset.Tables | Select-Object -Expand Rows)
echo $rows

OleDb запити до mssql server з powershell

Приклад PowerShell скрипта для виконання INSERT / UPDATE / DELETE запиту до бази MSSQL:

$dataSource = “serverinstance”
$database = “test”
$sql = "insert into test_table (test_col) Values ('Test')"
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; ” +
“Initial Catalog=$database; ” +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$command = New-Object data.OleDb.OleDbCommand $sql
$command.connection = $connection
$rowsAffected = $command.ExecuteNonQuery()

Змінна $ rowsAffected містить в собі кількість доданих або змінених рядків. Щоб виконати update або delete запит, потрібно просто змінити рядок SQL запиту в змінній $ sql.

SQL запит в PowerShell до MSSQL через System.Data.SqlClient

Як і у випадку з OleDb для звернення до SQL Server через System.Data.SqlClient, ми використовуємо вбудовані класи .NET. Приклад SELECT запиту в скрипті PowerShell:

$server = "serverinstance"
$database = "Test"
$sql = "select * from test_table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

select запит з powershell до бази даних Microsoft SQL Server

Приклад INSERT / DELETE / UPDATE запиту:

$server = "serverinstance"
$database = "Test"
$sql = "insert into test_table (test_col) Values ('Test')"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$rowsAffected = $SqlCmd.ExecuteNonQuery();
$SqlConnection.Close()

замітка. Зверніть увагу, що код з класами SqlClient дуже схожий на код з OleDB. У цих класів схожий принцип роботи:

  1. Створюється об’єкт з’єднання з MSSQL сервером;
  2. Створюється об’єкт з SQL запитом, і йому присвоюється об’єкт з’єднання;
  3. Потім в разі виконання SELECT запиту створюється об’єкт адаптера і в контексті цього адаптера виконується запит;
  4. У разі виконання INSERT / UPDATE / DELETE запиту об’єкт із запитом (вже містить в собі об’єкт з’єднання) виконує метод ExecuteNonQuery().

SQL запит в PowerShell через модуль SQL Server Management Studio

Для використання класів Microsoft.SqlServer.Smo (SMO), В системі повинна бути встановлена Студія управління SQL Server.

Завантажуємо модуль SMO і створюємо новий об’єкт сервера, потім виконуємо SELECT запит:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "devsrvdevsrv"
$results = $serverInstance.Databases['test'].ExecuteWithResults('select * from test_table')
foreach ($res in $results.Tables) {
$nbsp;echo $res
}

sql запросити PowerShell через SQL Server Management Studio

Для insert / update / delete запит виконуємо ExecuteNonQuery:

$db = $serverInstance.Databases['test']
$db.ExecuteNonQuery("insert into test_table (test_col) Values ('Test555')")

Замітка. SMO бібліотеки також можна встановити через пакетний менеджер NuGet.

  1. скачайте nuget.exe https://www.nuget.org/downloads;
  2. Запустіть PowerShell з правами адміністратора і перейдіть в директорію з файлом nugget.exe;
  3. виконайте: .nuget.exe Install Microsoft.SqlServer.SqlManagementObjects;
    установка модуля SqlManagementObjects для powershell
  4. У тій же директорії де лежить nuget.exe з’явиться папка Microsoft.SqlServer.SqlManagementObjects з усіма потрібними DLL;
  5. Завантажте бібліотеку SMO з DLL файлу. Додайте в ваш скрипт:

add-type –Path "C:UsersusernameDownloadsMicrosoft.SqlServer.SqlManagementObjects.150.18208.0libnet45Microsoft.SqlServer.Smo.dll"

Після цього класи SMO ​​стануть доступні для використання.

Командлет Invoke-Sqlcmd з модуля SQLServer для PowerShell

Для роботи з Командлети Invoke-Sqlcmd потрібно встановити модуль SqlServer для PowerShell. Запустіть PowerShell з правами адміністратора і введіть

Install-Module -Name SqlServer

(Кілька разів прийміть повідомлення інсталятора, натиснувши Y і enter).

Після установки можна перевірити що модуль коректно встановився, набравши:

Get-Module SqlServer -ListAvailable

установка модуля SqlServer для powershell

Командир Invoke-Sqlcmd простіший і інтуїтивний у використанні ніж інші способи підключення до SQL Server. Invoke-Sqlcmd використовує один і той же синтаксис для SELECT і INSERT / UPDATE / DELETE запитів.

Приклад SELECT запиту:

Invoke-Sqlcmd -ServerInstance "serverinstance " -Query "sp_who"

команда Invoke-Sqlcmd

Приклад INSERT запиту:

Invoke-Sqlcmd -ServerInstance "serverinstance" -Database "test" -Query "insert into test_table (test_col) Values (‘Test123’)"

На відміну від інших способів, запит в Invoke-Sqlcmd завжди задається в параметрі –Query.

Який варіант підключення до SQL вибрати?

Вибирати між oledb / smo / sqlclient / invoke-sqlcmd потрібно з урахуванням завдання яка перед вами стоїть, і в залежності від оточення, де планується виконувати скрипт.

Якщо ви збираєтеся поширювати скрипт (наприклад, ваш скрипт локально збирає дані для моніторингу) на безліч серверів, то варіанти c використанням SMO і плагіна SqlServer (invoke-sqlcmd) варто розглядати в останню чергу, тому що для відпрацювання скрипта потрібно буде встановлювати додаткові пакети в систему, чого хотілося б уникнути, при великій кількості серверів.

У свою чергу модуль SqlServer для PowerShell надає безліч інших командлетів для роботи з SQL Server (можна ознайомитися тут https://docs.microsoft.com/ru-ru/powershell/module/sqlserver/). Цей модуль містить найбільше команд для адміністрування самого SQL Server.

Якщо ваш скрипт буде виконувати не адміністративні завдання (а, наприклад, відповідати за якусь частину бізнес-логіки), то варто розглянути System.Data.SqlClient / SMO, так як вони надають більш зручні інструменти для розробки. Плюс OleDb в тому, що він може працювати не тільки з SQL Server, а й, наприклад, з Access.

Leave a Reply

Your email address will not be published. Required fields are marked *