Computerprogeek
У цій статті ми розглянемо всі методи роботи для підключення до SQL Server та виконувати запити SQL від PowerShell. Як правило, таке завдання стикається з системними адміністраторами, які пишуть сценарії та автоматизацію на PowerShell.
Існує багато різних способів співпраці з SQL Server через PowerShell, і дивлячись на статті в Інтернеті, в них легко заплутатися, оскільки різні статті описують різні методи, і навіть у досвідченому адміністраторі можуть виникнути питання.
Зміст:
- Запити T-SQL до PowerShell через систему.Дані.OLEDB
- Запит на SQL до PowerShell до MSSQL через систему.Дані.Sqlclient
- Запит на SQL до PowerShell через модуль Studio Management Server Server Management
- Команда Invoke-SQLCMD з модуля SQLServer для PowerShell
- Який варіант підключення до SQL вибирає?
Запити T-SQL до PowerShell через систему.Дані.OLEDB
Оскільки PowerShell має доступ до занять .Чистий, тоді для виконання T-SQL ви можете використовувати класи в Система.Дані.OLEDB.
Приклад сценарію PowerShell за допомогою системи.Дані.OLEDB. Давайте завершимо запит на вибір до таблиці в базі даних MS SQL:
$ DataSource = "Сервер \ екземпляр"
$ База даних = "master"
$ Sql = "Виберіть * з sysdatabass"
$ auth = "Інтегрована безпека = SSPI;"
$ ConnectionTring = "provider = sqloledb; Що +
“Джерело даних = $ dataSource; Що +
“Початковий каталог = $ база даних; Що +
“$ Auth; Що
$ Connection = new -Object System.Дані.OLEDB.Oledbconnection $ connectionstring
$ Command = new -Object System.Дані.OLEDB.OLEDBCOMMAND $ SQL, $ Connection
$ З'єднання.ВІДЧИНЕНО ()
$ Adapter = New -Object System.Дані.OLEDB.Команда oledbdataadapter $
$ DataSet = New -Object System.Дані.Набір даних
[Void] $ адаптер.Заповнення ($ DataSet)
$ З'єднання.Close ()
$ рядки = ($ DataSet.Таблиці | Виберіть -Object -Sexpand рядків)
Echo $ рядки
Приклад сценарію PowerShell для виконання запиту вставки/оновлення/видалення до бази даних MSSQL:
$ DataSource = "Сервер \ екземпляр"
$ База даних = "тест"
$ Sql = "Вставте значення test_table (test_col) (" тест ")"
$ auth = "Інтегрована безпека = SSPI;"
$ ConnectionTring = "provider = sqloledb; Що +
“Джерело даних = $ dataSource; Що +
“Початковий каталог = $ база даних; Що +
“$ Auth; Що
$ Connection = new -Object System.Дані.OLEDB.Oledbconnection $ connectionstring
$ Command = new -Object System.Дані.OLEDB.OLEDBCOMMAND $ SQL, $ Connection
$ З'єднання.ВІДЧИНЕНО ()
$ Command = new -Object Data.OLEDB.Oledbcommand $ sql
$ Команда.З'єднання = $ connepten
$ ROWSFECED = $ Command.ExecutenonQuery ()
Змінна $ Rowsaffed містить кількість доданих або змінених ліній. Щоб зробити запит на оновлення або видалення, вам просто потрібно змінити рядок SQL у змінній $ SQL.
Запит на SQL до PowerShell до MSSQL через систему.Дані.Sqlclient
Як і у випадку з OLEDB для зв’язку з SQL Server через систему.Дані.Sqlclient, ми використовуємо вбудовані класи .Сітка. Приклад виберіть запит у сценарії PowerShell:
$ server = "server \ екземпляр"
$ База даних = "тест"
$ Sql = "Виберіть * з test_table"
$ Sqlconnection = new-об'єктна система.Дані.Sqlclient.Sqlconnection
$ Sqlconnection.ConnectionString = "Server = $ server; база даних = $ база даних; інтегрована безпека = true"
$ Sqlcmd = new -Object System.Дані.Sqlclient.SQLCOMMAND
$ Sqlcmd.CommandText = $ sql
$ Sqlcmd.З'єднання = $ sqlconnection
$ Sqladapter = new -Object System.Дані.Sqlclient.Sqldataadapter
$ Sqladapter.SelectCommand = $ SQLCMD
$ DataSet = New -Object System.Дані.Набір даних
$ Sqladapter.Заповнення ($ DataSet)
$ Sqlconnection.Close ()
$ DataSet.Таблиці [0]
Приклад вставити/видалити/оновити запит:
$ server = "server \ екземпляр"
$ База даних = "тест"
$ Sql = "Вставте значення test_table (test_col) (" тест ")"
$ Sqlconnection = new-об'єктна система.Дані.Sqlclient.Sqlconnection
$ Sqlconnection.ConnectionString = "Server = $ server; база даних = $ база даних; інтегрована безпека = true"
$ Sqlcmd = new -Object System.Дані.Sqlclient.SQLCOMMAND
$ Sqlcmd.CommandText = $ sql
$ Sqlcmd.З'єднання = $ sqlconnection
$ Sqlconnection.ВІДЧИНЕНО ()
$ ROWSFECED = $ SQLCMD.ExecutenonQuery ();
$ Sqlconnection.Close ()
- Об'єкт підключення створюється за допомогою сервера MSSQL;
- Створюється об’єкт із запитом SQL, і йому присвоюється об’єкт з'єднання;
- Потім, у випадку запиту на вибір, створюється об'єкт адаптера і в контексті цього адаптера виконується запит;
- У разі запиту на вставку/оновлення/видалення об'єкт із запитом (вже містить об'єкт з'єднання) виконує метод
ExecutenonQuery ().
Запит на SQL до PowerShell через модуль Studio Management Server Server Management
Для використання занять Microsoft.Sqlserver.Smo (smo), Система повинна бути встановлена SQL Server Management Studio.
Ми завантажуємо модуль SMO і створюємо новий об'єкт сервера, а потім виконуємо вибір вибору:
[Система.Роздуми.Збірка] :: loadwithpartialname ("Microsoft.Sqlserver.Smo ");
$ ServerInstance = new -Object ('Microsoft.Sqlserver.Управління.Смола.Сервер ') "devsrv \ devsrv"
$ Результати = $ serverInstance.Бази даних ['тест'].Exectewithresults ('select * from test_table')
Foreach ($ res в $ результатах.Таблиці)
$ nbsp; echo $ res
Для вставки/оновлення/видалення запиту виконується:
$ Db = $ serverInstance.Бази даних ['тест']
$ Db.Виконавець
- Завантажте Nuget.Exe https: // www.Нугет.Org/завантаження;
- Запустіть PowerShell з правами адміністратора та перейдіть до каталогу з файлом самородків.exe;
- Виконати:
.\ Нугет.Exe Встановіть Microsoft.Sqlserver.SQLMANEGICESTOBjects;
- В тому ж каталозі, де лежить Нугет.Exe з’явиться папкою Microsoft.Sqlserver.SQLMANECTIONOBJECTS з усіма необхідними DLL;
- Завантажте бібліотеку SMO з файлу DLL. Додайте до свого сценарію:
Додати -Type -Path "C: \ Користувачі \ Ім'я користувача \ Завантаження \ Microsoft.Sqlserver.SQLMANEGICESTOBjects.150.18208.0 \ lib \ net45 \ microsoft.Sqlserver.Смола.Dll "
Після цього заняття SMO будуть доступні для використання.
Команда Invoke-SQLCMD з модуля SQLServer для PowerShell
Працювати з Commandlet Invoke-sqlcmd Вам потрібно встановити модуль sQlserver для PowerShell. Запустити PowerShell з правами адміністратора та ввести
Встановити -Module -Name SQLSERVER
(Кілька разів приймайте сповіщення інсталятора, натиснувши Y та ввести).
Після встановлення ви можете перевірити, чи був правильно встановлений модуль, набираючи:
Get -module sqlserver -listavable
Командування Invoke-sqlcmd Простіші та інтуїтивніше використання, ніж інші методи підключення до SQL Server. Invoke-SQLCMD використовує той самий синтаксис для вибору та вставки/оновлення/видалення запитів.
Приклад Виберіть Запит:
Apploke -Sqlcmd -ServerInstance "Сервер \ екземпляр" -query "sp_who"
Приклад запиту вставки:
Invoke -sqlcmd -serverinstance "server \ екземпляр" -database "test" -query "вставити значення intable (test_col) ('test123)"
На відміну від інших методів, запит у invoke-sqlcmd завжди встановлюється в параметрі -Запит .
Який варіант підключення до SQL вибирає?
Виберіть між OLEDB/SMO/SQLCLCLENT/Invoke-SQLCMD, вам потрібно врахувати завдання, з яким стикається, і залежно від середовища, де планується виконати сценарій.
Якщо ви збираєтеся розповсюдити сценарій (наприклад, ваш сценарій локально збирає дані для моніторингу) на багато серверів, то варіанти використання SMO та SQLServer (Plugin voke-sqlcmd) слід враховувати останнім, оскільки для роботи сценарію вам знадобиться Щоб встановити додаткові пакети в системі, чого я хотів би уникнути, з великою кількістю серверів.
У свою чергу, модуль SQLServer для PowerShell надає багатьом іншим командувачам для роботи з SQL Server (ви можете ознайомитись з https: // docs.Microsoft.Com/ru-ru/powershell/module/sqlserver/). Цей модуль містить більшість команд для адміністрування самого SQL Server.
Якщо ваш сценарій не виконує адміністративних завдань (і, наприклад, відповідає за якусь частину логіки бізнесу), то слід розглянути систему.Дані.Sqlclient/smo, оскільки вони надають більш зручні інструменти для розробки. Плюс OLEDB полягає в тому, що він може працювати не тільки з SQL Server, але і, наприклад, з доступом.
