Данный материал является переводом оригинальной статьи "MSSQLTips : Retrieve a List of SQL Server Databases and their Properties using PowerShell".
В прошлый раз вы увидели, как можно использовать Windows PowerShell и SQL Server Management Objects (SMO) для администрирования баз данных SQL Server. Хотелось бы перевести на него некоторые скрипты Transact-SQL для каждодневного использования, начиная с простейших, таких как получение списка БД и их свойств, в целях аудита.
Одна из вещей, которую делают Администраторы БД, это получение списка БД и их свойств для аудита и отчетности. Мы проверяем такие свойства, как модель восстановления, доступное свободное место, авто-сжатие и другие, и создаем действия на их основе. Мы уже смотрели, как получить доступ к объекту Server – его свойствам и методам - используя SMO. Мы углубимся в объектную иерархию и посмотрим на разные члены объекта Server. Экземпляр SQL Server можно описать, используя разные свойства, такие как имя экземпляра, логины, настройки, все из которых – члены объекта Server.
Что может быть главным интересом в этой статье – так это свойство Databases. Это свойство воспроизводит коллекцию объектов БД, определенную на экземпляре SQL Server, неважно - системные это или пользовательские БД. Начнем с запроса всех членов свойства Databases. Скрипт ниже такой же, как и один из предыдущих скриптов с добавлением командлета Get-Member для получения списка членов для свойства Databases, указания Property как -MemberType, что лишь означает извлечение всех свойств БД. Не смущайтесь понятиями - свойство Databases объекта Server, это коллекция объектов БД, а сами по себе объекты БД имеют свои собственные свойства.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2000"
$dbs=$s.Databases
$dbs | Get-Member -MemberType Property
Из списка свойств из объекта Database, выберем самые общие :
$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
Заметьте, что это весьма просто и прямолинейно. Раз переменная $dbs содержит коллекцию объектов БД, теперь можно передать результаты прямо в командлет Select-Object, который мы вызываем, просто как SELECT. Теперь можно сделать скрипт динамичней, вводя переменные и сохраняя все, как скрипт PowerShell, который был сделан в предыдущей статье.
Чтение из текстового файла
Есть пара способов вывести список управляемых вами серверов, но, ради простоты, используем обычный текстовый файл. Что мы сделаем, так это сохраним имена экземпляров SQL Server, которые вы администрируете, в текстовый файл, названный "SQL_Servers.txt" и передадим содержимое в переменную с именем $instance. Используем командлет Get-Content для чтения содержимого из текстового файла и командлет ForEach-Object для итерации через коллекцию. Скрипт может выглядеть, как здесь ниже. Можно либо записать его в файл скрипта, либо набрать его прямо в консоли PowerShell.
ForEach-Object ($instance in Get-Content "D:\SQL_Servers.txt")
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$dbs=$s.Databases
$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
}
Создаём отчёты с помощью Microsoft Excel
Подтвердим, что Microsoft Excel является популярнейшим приложением, чтобы создавать таблицы и отчеты, поэтому имеет смысл извлечь из него максимальную пользу с любыми отчетами, которые нужны нам для генерации. ИТ-менеджеры хотели бы видеть цветные графики, а не только числовые значения в отчетах. Что мы сделаем, это используем PowerShell для автоматизации создания отчетов по нашему аудиту БД с помощью Excel. Есть несколько ключевых моментов, которые надо понимать о процессе. Первое, мы будем вызывать Excel через COM-автоматизацию, а не как .NET-приложение. Мы использовали командлет New-Object для создания экземпляра объекта Excel, параметр -ComObject для создания нового COM-объекта типа Excel.Application, который есть ProgID объекта, что мы хотим создать и назначили его переменной $Excel.
$Excel = New-Object -ComObject Excel.Application
Вам может быть интересно выяснить, какие иные COM-компоненты доступны на используемой Windows-машине. Для получения значений их ProgID, можно запросить системный реестр одной строкой, как эта ниже (взята из PowerShell team blog):
dir "REGISTRY::HKEY_CLASSES_ROOT\CLSID" -include PROGID -recurse | foreach {$_.GetValue("")}
Так много COM-объектов, тогда как нам надо только Excel.Application на этот раз. Устанавливаем свойство Visible объекта Excel в значение True, чтобы видеть, что происходит.
$Excel.visible = $True
Далее, используем метод Add() для добавления новой книги в экземпляр объекта Excel и используем свойство Item для создания ссылки на объект на первом листе книги. Переменная $Sheet будет хранить указатель на лист и на нее будем ссылаться на протяжении всего кода.
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
Можно добавить столько книг, сколько хочется, но для наших целей одной достаточно. Теперь, чтобы действительно работать в Excel, нам надо ссылаться на номера строк и столбцов. Чтобы сделать это, вызываем свойство Cells объекта Excel для возврата ячейки или ячеек на активном листе. Мы можем затем изменять ячейки, задавая значение, меняя свойство Font и т.д. Пример задания значения в ячейку row 1, column 1 и изменения свойства Font на Bold показан ниже.
$Sheet.Cells.Item(1,1) = "SQL SERVER INSTANCE NAME:"
$Sheet.Cells.Item(1,1).Font.Bold = $True
Сохранение кода и его запуск в PowerShell откроет лист Excel, выглядящий примерно так, как показано ниже.
Можно, либо вызвать метод SaveAs() объекта Worksheet в $Sheet для сохранения листа Excel, либо просто оставить так.
Представьте, что вы используете динамические значения строк и столбцов, поскольку на каждом экземпляре SQL Server будет достаточное количество баз данных. Ниже скрипт PowerShell, который учитывает то, что у нас есть, лишь используя SMO и Excel для создания необычного отчета.
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
#Counter variable for rows
$intRow = 1
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "D:\SQL_Servers.txt")
{
#Create column headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,2) = $instance
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$intRow++
$Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
$Sheet.Cells.Item($intRow,2) = "COLLATION"
$Sheet.Cells.Item($intRow,3) = "COMPATIBILITY LEVEL"
$Sheet.Cells.Item($intRow,4) = "AUTOSHRINK"
$Sheet.Cells.Item($intRow,5) = "RECOVERY MODEL"
$Sheet.Cells.Item($intRow,6) = "SIZE (MB)"
$Sheet.Cells.Item($intRow,7) = "SPACE AVAILABLE (MB)"
#Format the column headers
for ($col = 1; $col -le 7; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow++
#######################################################
#This script gets SQL Server database information using PowerShell
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Create an SMO connection to the instance
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$dbs = $s.Databases
#$dbs | SELECT Name,Collation,CompatibilityLevel,AutoShrink,RecoveryModel,Size,SpaceAvailable
#Formatting using Excel
ForEach ($db in $dbs)
{
#Divide the value of SpaceAvailable by 1KB
$dbSpaceAvailable = $db.SpaceAvailable/1KB
#Format the results to a number with three decimal places
$dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable
$Sheet.Cells.Item($intRow, 1) = $db.Name
$Sheet.Cells.Item($intRow, 2) = $db.Collation
$Sheet.Cells.Item($intRow, 3) = $db.CompatibilityLevel
#Change the background color of the Cell depending on the AutoShrink property value
if ($db.AutoShrink -eq "True")
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 4) = $db.AutoShrink
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 5) = $db.RecoveryModel
$Sheet.Cells.Item($intRow, 6) = "{0:N3}" -f $db.Size
#Change the background color of the Cell depending on the SpaceAvailable property value
if ($dbSpaceAvailable -lt 1.00)
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 7) = $dbSpaceAvailable
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor
$intRow ++
}
$intRow ++
}
$Sheet.UsedRange.EntireColumn.AutoFit()
cls
Выполнение скрипта в PowerShell создаст лист Excel, аналогичный тому, что показан ниже, подсвечивая свойства, требующие незамедлительного внимания, используя разные цвета из определений здесь.
RSS - Записи
Добавить комментарий