SQL Server on Linux용 SQL 평가 API 사용

아티클
07/12/2024

적용 대상: SQL Server - Linux

SQL 평가 API는 모범 사례를 위해 SQL Server 구성을 평가하는 메커니즘을 제공합니다. API는 SQL Server 팀에서 권장하는 모범 사례가 포함된 규칙 세트와 함께 제공됩니다. 이 규칙 세트는 새 버전의 출시로 향상되었습니다. SQL Server 구성이 권장 모범 사례와 일치하는지 확인하는 것이 유용합니다.

Microsoft에서 제공하는 규칙 집합은 GitHub에서 이용할 수 있습니다. 샘플 리포지토리에서 전체 규칙 세트를 볼 수 있습니다.

이 문서에서는 SQL Server on Linux 및 컨테이너용 SQL 평가 API를 실행하는 두 가지 방법을 살펴봅니다.

Azure Data Studio용 SQL 평가 익스텐션(프리뷰)
PowerShell을 사용한 SQL 평가 API

Azure Data Studio용 SQL 평가 익스텐션(프리뷰)

Azure Data Studio용 SQL 평가 익스텐션(프리뷰)은 모범 사례를 위해 SQL Server 구성을 평가하는 메커니즘을 제공합니다.

이 프리뷰 버전에서는 다음을 작업을 수행할 수 있습니다.

기본 제공 규칙을 사용하여 SQL Server, Azure SQL 데이터베이스 또는 Azure SQL Managed Instance 및 해당 데이터베이스 평가
인스턴스 및 해당 데이터베이스에 적용할 수 있는 모든 기본 제공 규칙 목록 가져오기
평가 결과 및 해당 규칙 목록을 스크립트로 내보내 SQL 테이블에 저장
평가 결과에 대한 HTML 보고서 만들기

Azure Data Studio의 SQL 평가 익스텐션을 보여주는 스크린샷.

SQL 평가 시작

SQL 평가 익스텐션을 설치한 후 서버 목록을 확장하고 평가하려는 서버 또는 데이터베이스를 마우스 오른쪽 단추로 클릭하고 관리를 선택합니다.
그런 다음 일반 섹션에서 SQL 평가를 선택합니다. 평가 탭에서 평가 호출을 선택하여 선택한 SQL Server 또는 Azure SQL 데이터베이스에 대한 평가를 수행합니다. 결과를 사용할 수 있게 되면 필터링 및 정렬 기능을 사용할 수 있습니다.
테이블로 삽입 형식으로 결과를 가져오려면 스크립트로 내보내기를 선택합니다. HTML 보고서 만들기를 선택하여 평가 결과를 HTML 파일로 저장할 수도 있습니다. 일부 평가 규칙은 특정 SQL Server 구성을 위한 것이고 일부는 다른 SQL Server 구성을 위한 것입니다. 데이터베이스 규칙의 경우에도 마찬가지입니다. 예를 들어 SQL Server 2016(13.x) 또는 tempdb 데이터베이스에만 적용되는 규칙이 있습니다.
적용 가능한 규칙 보기 단추는 평가 호출을 선택한 후 서버 및 데이터베이스의 평가를 수행하는 데 사용되는 평가 규칙을 표시합니다. SQL Server 및 SQL 평가 API에 대한 정보를 보려면 정보를 선택합니다. 평가 세션 결과는 기록 탭에서 검토할 수 있습니다.

PowerShell을 사용한 SQL 평가 API

두 번째 옵션은 PowerShell을 사용하여 SQL 평가 API 스크립트를 실행하는 것입니다.

필수 조건

Linux에 Azure PowerShell을 설치했는지 확인합니다.
SqlServer 사용자로 실행하여 PowerShell 갤러리에서 mssql PowerShell 모듈을 설치합니다.
```
su mssql -c "/usr/bin/pwsh -Command Install-Module SqlServer"
```

평가 설정

SQL 평가 API 출력은 JSON 형식으로 제공됩니다. 다음과 같이 SQL 평가 API를 구성하려면 다음 단계를 수행해야 합니다.

평가하려는 인스턴스에서 SQL 인증을 사용하여 SQL Server 평가에 대한 로그인을 만듭니다. 다음 T-SQL(Transact-SQL) 스크립트를 사용하여 로그인과 강력한 암호를 만들 수 있습니다. <*PASSWORD*>를 원하는 강력한 암호로 바꾸세요.
```
USE [master];
GO

CREATE LOGIN [assessmentLogin] WITH PASSWORD = N'<*PASSWORD*>';
ALTER SERVER ROLE [CONTROL SERVER] ADD MEMBER [assessmentLogin];
GO
```
CONTROL SERVER 역할은 대부분의 평가에서 작동합니다. 그러나 sysadmin 권한이 필요할 수 있는 몇 가지 평가가 있습니다. 이러한 규칙을 실행하지 않는 경우 CONTROL SERVER 권한을 사용하는 것이 좋습니다.
다음과 같이 로그인하기 위한 자격 증명을 시스템에 저장하고 <*PASSWORD*>를 이전 단계에서 사용한 암호로 다시 바꿉니다.
```
echo "assessmentLogin" > /var/opt/mssql/secrets/assessment
echo "<*PASSWORD*>" >> /var/opt/mssql/secrets/assessment
```
mssql 사용자만 자격 증명에 액세스할 수 있도록 하여 새 평가 자격 증명을 보호합니다.
```
chmod 600 /var/opt/mssql/secrets/assessment
chown mssql:mssql /var/opt/mssql/secrets/assessment
```

평가 스크립트 다운로드

다음은 이전 단계에서 만든 자격 증명을 사용하여 SQL 평가 API를 호출하는 샘플 스크립트입니다. 스크립트는 /var/opt/mssql/log/assessments 위치에 JSON 형식의 출력 파일을 생성합니다.

참고 항목

SQL 평가 API는 CSV 및 XML 형식으로 출력을 생성할 수도 있습니다.

이 스크립트는 GitHub에서 다운로드할 수 있습니다.

이 파일을 /opt/mssql/bin/runassessment.ps1으로 저장할 수 있습니다.

[CmdletBinding()] param ()

$Error.Clear()

# Create output directory if not exists

$outDir = '/var/opt/mssql/log/assessments'
if (-not ( Test-Path $outDir )) { mkdir $outDir }
$outPath = Join-Path $outDir 'assessment-latest'

$errorPath = Join-Path $outDir 'assessment-latest-errors'
if ( Test-Path $errorPath ) { remove-item $errorPath }

function ConvertTo-LogOutput {
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline = $true)]
        $input
    )
    process {
        switch ($input) {
            { $_ -is [System.Management.Automation.WarningRecord] } {
                $result = @{
                    'TimeStamp' = $(Get-Date).ToString("O");
                    'Warning'   = $_.Message
                }
            }
            default {
                $result = @{
                    'TimeStamp'      = $input.TimeStamp;
                    'Severity'       = $input.Severity;
                    'TargetType'     = $input.TargetType;
                    'ServerName'     = $serverName;
                    'HostName'       = $hostName;
                    'TargetName'     = $input.TargetObject.Name;
                    'TargetPath'     = $input.TargetPath;
                    'CheckId'        = $input.Check.Id;
                    'CheckName'      = $input.Check.DisplayName;
                    'Message'        = $input.Message;
                    'RulesetName'    = $input.Check.OriginName;
                    'RulesetVersion' = $input.Check.OriginVersion.ToString();
                    'HelpLink'       = $input.HelpLink
                }

                if ( $input.TargetType -eq 'Database') {
                    $result['AvailabilityGroup'] = $input.TargetObject.AvailabilityGroupName
                }
            }
        }

        $result
    }
}

function Get-TargetsRecursive {

    [CmdletBinding()]
    Param (
        [Parameter(ValueFromPipeline = $true)]
        [Microsoft.SqlServer.Management.Smo.Server] $server
    )

    $server
    $server.Databases
}

function Get-ConfSetting {
    [CmdletBinding()]
    param (
        $confFile,
        $section,
        $name,
        $defaultValue = $null
    )

    $inSection = $false

    switch -regex -file $confFile {
        "^\s*\[\s*(.+?)\s*\]" {
            $inSection = $matches[1] -eq $section
        }
        "^\s*$($name)\s*=\s*(.+?)\s*$" {
            if ($inSection) {
                return $matches[1]
            }
        }
    }

    return $defaultValue
}

try {
    Write-Verbose "Acquiring credentials"

    $login, $pwd = Get-Content '/var/opt/mssql/secrets/assessment' -Encoding UTF8NoBOM -TotalCount 2
    $securePassword = ConvertTo-SecureString $pwd -AsPlainText -Force
    $credential = New-Object System.Management.Automation.PSCredential ($login, $securePassword)
    $securePassword.MakeReadOnly()
    
    Write-Verbose "Acquired credentials"

    $serverInstance = '.'

    if (Test-Path /var/opt/mssql/mssql.conf) {
        $port = Get-ConfSetting /var/opt/mssql/mssql.conf network tcpport


        if (-not [string]::IsNullOrWhiteSpace($port)) {
            Write-Verbose "Using port $($port)"
            $serverInstance = "$($serverInstance),$($port)"
        }
    }

    # IMPORTANT: If the script is run in trusted environments and there is a prelogin handshake error,
    # add -TrustServerCertificate flag in the commands for $serverName, $hostName and Get-SqlInstance lines below.
    $serverName = (Invoke-SqlCmd -ServerInstance $serverInstance -Credential $credential -Query "SELECT @@SERVERNAME")[0]
    $hostName = (Invoke-SqlCmd -ServerInstance $serverInstance -Credential $credential -Query "SELECT HOST_NAME()")[0]

    # Invoke assessment and store results.
    # Replace 'ConvertTo-Json' with 'ConvertTo-Csv' to change output format.
    # Available output formats: JSON, CSV, XML.
    # Encoding parameter is optional.

    Get-SqlInstance -ServerInstance $serverInstance -Credential $credential -ErrorAction Stop
    | Get-TargetsRecursive
    | ForEach-Object { Write-Verbose "Invoke assessment on $($_.Urn)"; $_ }
    | Invoke-SqlAssessment 3>&1
    | ConvertTo-LogOutput
    | ConvertTo-Json -AsArray
    | Set-Content $outPath -Encoding UTF8NoBOM
}
finally {
    Write-Verbose "Error count: $($Error.Count)"

    if ($Error) {
        $Error
        | ForEach-Object { @{ 'TimeStamp' = $(Get-Date).ToString("O"); 'Message' = $_.ToString() } }
        | ConvertTo-Json -AsArray
        | Set-Content $errorPath -Encoding UTF8NoBOM
    }
}

참고 항목

신뢰할 수 있는 환경에서 이전 스크립트를 실행할 때 사전 로그 핸드셰이크 오류가 발생하면 코드의 $serverName, $hostName, Get-SqlInstance줄 명령에 -TrustServerCertificate 플래그를 추가합니다.

평가 실행

스크립트가 mssql에 의해 소유되고 실행 가능한지 확인합니다.

chown mssql:mssql /opt/mssql/bin/runassessment.ps1
chmod 700 /opt/mssql/bin/runassessment.ps1

로그 폴더를 만들고 해당 폴더의 mssql 사용자에게 적절한 권한을 할당합니다.

mkdir /var/opt/mssql/log/assessments/
chown mssql:mssql /var/opt/mssql/log/assessments/
chmod 0700 /var/opt/mssql/log/assessments/

이제 첫 번째 평가를 만들 수 있지만 후속 평가를 통해 cron 또는 systemd를 통해 보다 안전하게 자동으로 실행될 수 있도록 mssql 사용자로 수행해야 합니다.
```
su mssql -c "pwsh -File /opt/mssql/bin/runassessment.ps1"
```
명령이 완료되면 출력이 JSON 형식으로 생성됩니다. 이 출력은 JSON 파일 구문 분석을 지원하는 모든 타사 도구와 통합될 수 있습니다. 이러한 예시 도구 중 하나는 Red Hat Insights입니다.

다음을 통해 공유

SQL Server on Linux용 SQL 평가 API 사용

Azure Data Studio용 SQL 평가 익스텐션(프리뷰)

SQL 평가 시작

PowerShell을 사용한 SQL 평가 API

필수 조건

평가 설정

평가 스크립트 다운로드

평가 실행

피드백

피드백

추가 리소스

다음을 통해 공유

SQL Server on Linux용 SQL 평가 API 사용

Azure Data Studio용 SQL 평가 익스텐션(프리뷰)

SQL 평가 시작

PowerShell을 사용한 SQL 평가 API

필수 조건

평가 설정

평가 스크립트 다운로드

평가 실행

관련 콘텐츠

피드백

피드백

추가 리소스