فهرست Instanceهای SQL Server با PowerShell و SMO

راهنمای گام‌به‌گام: ایجاد و نگهداری فهرست جامع Instanceهای SQL Server برای متخصصان

مدیریت و ردیابی تمامی Instanceهای SQL Server در یک محیط سازمانی گسترده می‌تواند یکی از چالش‌برانگیزترین وظایف برای مدیران پایگاه داده (DBA) باشد. چه برای مسائل مربوط به لایسنسینگ، اعمال پچ‌ها، یا بررسی‌های عمومی سلامت سیستم، داشتن یک فهرست (اینونتوری) به‌روز و دقیق از تمامی Instanceها حیاتی است. این مقاله به شما کمک می‌کند تا یک سیستم قوی برای جمع‌آوری و نگهداری فهرست Instanceهای SQL Server خود با استفاده از PowerShell و SQL Server Management Objects (SMO) ایجاد کنید.

چرا به فهرست Instanceهای SQL Server نیاز داریم؟

تصور کنید ده‌ها یا حتی صدها Instance از SQL Server در سرتاسر شبکه شما پراکنده شده‌اند. بدون یک دید جامع، پاسخ به سوالاتی مانند: “چند Instance از SQL Server 2019 داریم؟”، “کدام Instanceها نیاز به پچ امنیتی دارند؟” یا “کدام Instanceها روی سرورهای با حافظه کمتر از حد استاندارد اجرا می‌شوند؟” بسیار دشوار و زمان‌بر خواهد بود. یک فهرست متمرکز، مدیریت کارآمد، برنامه‌ریزی بهتر برای ارتقا و اطمینان از رعایت استانداردهای سازمانی را تسهیل می‌کند.

ابزارها و رویکرد ما

ما عمدتاً از PowerShell به دلیل قابلیت‌های قدرتمند اسکریپت‌نویسی و یکپارچگی بی‌نقص آن با SMO استفاده خواهیم کرد. SMO دسترسی برنامه‌نویسی به اشیاء SQL Server را فراهم می‌کند و به ما امکان می‌دهد اطلاعات دقیق در مورد هر Instance را جمع‌آوری کنیم. برای ذخیره این اطلاعات، یک پایگاه داده مرکزی SQL Server ایجاد خواهیم کرد.

ساخت پایگاه داده مرکزی برای فهرست

اولین قدم، ایجاد یک پایگاه داده در یک سرور SQL Server مرکزی برای ذخیره اطلاعات Instanceها است. سپس یک جدول برای نگهداری ویژگی‌های کلیدی هر Instance تعریف می‌کنیم. این جدول شامل ستون‌هایی برای نام Instance، نسخه، Edition، سطح محصول، وضعیت کلاسترینگ و سایر جزئیات مهم خواهد بود.

در ادامه، کد SQL برای ایجاد جدول `dbo.SqlServerInstances` را مشاهده می‌کنید که برای نگهداری اطلاعات مربوط به Instanceهای SQL Server طراحی شده است:

CREATE TABLE dbo.SqlServerInstances (
    InstanceID INT IDENTITY(1,1) PRIMARY KEY,
    InstanceName NVARCHAR(255) NOT NULL UNIQUE,
    ServerName NVARCHAR(255) NOT NULL,
    IPAddress NVARCHAR(50),
    Version NVARCHAR(50),
    Edition NVARCHAR(100),
    ProductLevel NVARCHAR(50),
    Collation NVARCHAR(100),
    IsClustered BIT,
    PhysicalMemoryMB INT,
    ProcessorCount INT,
    LastRestartTime DATETIME,
    CollectionDate DATETIME DEFAULT GETDATE()
);

این جدول، اطلاعات حیاتی مانند `InstanceName` (نام منحصر به فرد Instance)، `Version` (نسخه SQL Server)، `Edition` (ویرایش SQL Server)، `IsClustered` (آیا Instance کلاستر شده است)، `PhysicalMemoryMB` (حافظه فیزیکی بر حسب مگابایت)، و `ProcessorCount` (تعداد پردازنده‌ها) را برای هر Instance نگهداری می‌کند. ستون `CollectionDate` نیز زمان آخرین جمع‌آوری اطلاعات را ثبت می‌کند.

اسکریپت PowerShell برای جمع‌آوری اطلاعات

اکنون، زمان آن رسیده است که یک اسکریپت PowerShell بنویسیم که سرورها را پیمایش کرده، به Instanceهای SQL Server متصل شده و جدول فهرست ما را پر کند. این اسکریپت از SMO برای جمع‌آوری ویژگی‌های مختلف Instance استفاده کرده و سپس داده‌ها را در پایگاه داده مرکزی وارد یا به‌روزرسانی می‌کند.

پیش از اجرای اسکریپت، مطمئن شوید که ماژول `SqlServer` برای PowerShell نصب شده است. اگر نصب نیست، می‌توانید آن را با دستور `Install-Module -Name SqlServer -AllowClobber` نصب کنید.

در ادامه، نمونه اسکریپت PowerShell برای جمع‌آوری و درج اطلاعات Instanceها را مشاهده می‌کنید:

# Requires SQL Server Management Objects (SMO) to be installed
# Install-Module -Name SqlServer -AllowClobber

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

$centralInventoryServer = "YOUR_CENTRAL_DB_SERVER" # نام سرور پایگاه داده مرکزی خود را اینجا وارد کنید
$centralInventoryDatabase = "InventoryDB" # نام پایگاه داده مرکزی خود را اینجا وارد کنید

# لیستی از سرورهایی که باید اسکن شوند (می‌تواند از فایل، Active Directory و غیره خوانده شود)
$servers = @("Server1", "Server2\NamedInstance", "Server3")

foreach ($server in $servers) {
    Write-Host "Processing server: $server"
    try {
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $server
        $instanceName = $srv.Name
        $version = $srv.VersionString
        $edition = $srv.Edition
        $productLevel = $srv.ProductLevel
        $collation = $srv.Collation
        $isClustered = $srv.IsClustered
        $physicalMemory = $srv.PhysicalMemory
        $processorCount = $srv.ProcessorCount
        $lastRestartTime = $srv.ServerLastStartupTime

        # درج یا به‌روزرسانی در پایگاه داده فهرست
        $connectionString = "Server=$centralInventoryServer;Database=$centralInventoryDatabase;Integrated Security=True;"
        $query = @"
            IF EXISTS (SELECT 1 FROM dbo.SqlServerInstances WHERE InstanceName = @InstanceName)
            BEGIN
                UPDATE dbo.SqlServerInstances
                SET ServerName = @ServerName, IPAddress = @IPAddress, Version = @Version, Edition = @Edition,
                    ProductLevel = @ProductLevel, Collation = @Collation, IsClustered = @IsClustered,
                    PhysicalMemoryMB = @PhysicalMemoryMB, ProcessorCount = @ProcessorCount,
                    LastRestartTime = @LastRestartTime, CollectionDate = GETDATE()
                WHERE InstanceName = @InstanceName;
            END
            ELSE
            BEGIN
                INSERT INTO dbo.SqlServerInstances (InstanceName, ServerName, IPAddress, Version, Edition,
                                                    ProductLevel, Collation, IsClustered, PhysicalMemoryMB,
                                                    ProcessorCount, LastRestartTime)
                VALUES (@InstanceName, @ServerName, @IPAddress, @Version, @Edition,
                        @ProductLevel, @Collation, @IsClustered, @PhysicalMemoryMB,
                        @ProcessorCount, @LastRestartTime);
            END
"@

        $command = New-Object System.Data.SqlClient.SqlCommand($query, (New-Object System.Data.SqlClient.SqlConnection($connectionString)))
        $command.Parameters.AddWithValue("@InstanceName", $instanceName)
        $command.Parameters.AddWithValue("@ServerName", $srv.NetName) # فرض می‌شود NetName نام شبکه سرور است
        $command.Parameters.AddWithValue("@IPAddress", (([System.Net.Dns]::GetHostAddresses($srv.NetName) | Select-Object -ExpandProperty IPAddressToString | Where-Object { $_ -notlike "*:*" })[0]))
        $command.Parameters.AddWithValue("@Version", $version)
        $command.Parameters.AddWithValue("@Edition", $edition)
        $command.Parameters.AddWithValue("@ProductLevel", $productLevel)
        $command.Parameters.AddWithValue("@Collation", $collation)
        $command.Parameters.AddWithValue("@IsClustered", $isClustered)
        $command.Parameters.AddWithValue("@PhysicalMemoryMB", $physicalMemory)
        $command.Parameters.AddWithValue("@ProcessorCount", $processorCount)
        $command.Parameters.AddWithValue("@LastRestartTime", $lastRestartTime)

        $command.Connection.Open()
        $command.ExecuteNonQuery()
        $command.Connection.Close()
        Write-Host "Successfully processed $instanceName"
    }
    catch {
        Write-Warning "Could not connect to or process $server. Error: $($_.Exception.Message)"
    }
}

این اسکریپت ابتدا اسمبلی‌های SMO را بارگذاری می‌کند. سپس یک لیست از نام‌های سرورها را تعریف کرده و برای هر سرور تلاش می‌کند تا یک شیء `Server` از SMO ایجاد کند. با استفاده از این شیء، اطلاعات مختلفی مانند نام Instance، نسخه، Edition و غیره جمع‌آوری می‌شود. سپس یک اتصال به پایگاه داده مرکزی برقرار شده و از یک کوئری SQL برای درج یا به‌روزرسانی اطلاعات در جدول `dbo.SqlServerInstances` استفاده می‌شود. این اسکریپت با مدیریت خطا نیز همراه است تا Instanceهایی که قابل دسترسی نیستند را گزارش دهد.

گزارش‌گیری از فهرست Instanceها

هنگامی که داده‌ها جمع‌آوری شدند، می‌توانید از کوئری‌های SQL ساده برای استخراج گزارش‌های مفید استفاده کنید. این گزارش‌ها به شما کمک می‌کنند تا دید جامعی از محیط SQL Server خود داشته باشید و تصمیمات آگاهانه‌تری بگیرید.

برای مثال، جهت مشاهده تمامی Instanceهای SQL Server 2019 در محیط خود، می‌توانید از کوئری زیر استفاده کنید:

SELECT InstanceName, Version, Edition, ProductLevel, Collation
FROM dbo.SqlServerInstances
WHERE Version LIKE '15.%' -- SQL Server 2019 version starts with 15
ORDER BY InstanceName;

این کوئری تمام ستون‌های مشخص‌شده را برای Instanceهایی که نسخه آن‌ها با ’15.’ شروع می‌شود (که نشان‌دهنده SQL Server 2019 است) انتخاب کرده و نتایج را بر اساس نام Instance مرتب می‌کند.

برای بررسی Instanceهایی که حافظه RAM کمتر از 4 گیگابایت دارند و ممکن است نیاز به ارتقا داشته باشند، کوئری زیر کاربردی است:

SELECT InstanceName, Edition, PhysicalMemoryMB, ProcessorCount
FROM dbo.SqlServerInstances
WHERE PhysicalMemoryMB < 4096
ORDER BY PhysicalMemoryMB DESC;

این کوئری Instanceهایی را نشان می‌دهد که حافظه فیزیکی آن‌ها (PhysicalMemoryMB) کمتر از 4096 مگابایت (4 گیگابایت) است و نتایج را بر اساس میزان حافظه به صورت نزولی مرتب می‌کند.

نتیجه‌گیری

با پیاده‌سازی این سیستم قوی فهرست‌برداری، یک منبع واحد و قابل اعتماد برای محیط SQL Server خود خواهید داشت. این امر وظایف مدیریتی را ساده کرده، برنامه‌ریزی برای ارتقا و نگهداری را بهبود می‌بخشد و انطباق با استانداردهای سازمانی را تضمین می‌کند. نگهداری یک فهرست دقیق و به‌روز از Instanceهای SQL Server یک گام اساسی در مسیر تبدیل شدن به یک DBA متخصص و کارآمد است. با خودکارسازی این فرآیند، می‌توانید زمان بیشتری را به وظایف استراتژیک‌تر اختصاص دهید و اطمینان حاصل کنید که محیط پایگاه داده شما همیشه بهینه و پایدار است.