Test-SqlAGDatabase.ps1

$ErrorActionPreference = "Stop"
Set-StrictMode -Version "Latest"

function Create-Login {
    $loginSetup = "
        IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'LAB\CHWK01$') DROP LOGIN [LAB\CHWK01$];
        CREATE LOGIN [LAB\CHWK01$] FROM WINDOWS;
        GRANT CONTROL SERVER TO [LAB\CHWK01$];
    "
    "SEC1N1", "SEC1N2", "SEC1N3", "DAC1N1", "DAC1N2" | New-DbConnection | New-DbCommand $loginSetup | Get-DbData
}

function Create-PrimaryDatabase {
    param (
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        $DatabaseName
    )

    $setupDatabase = "
        EXECUTE AS LOGIN = 'sa';
        CREATE DATABASE DatabaseName;
        BACKUP DATABASE DatabaseName TO DISK = 'NUL';
        REVERT;
    ".Replace("DatabaseName", $DatabaseName)

    "SEC1N1" | New-DbConnection | New-DbCommand $setupDatabase | Get-DbData
}

function Remove-Database {
    param (
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        $DatabaseName
    )

    $removeDatabase = "
        IF EXISTS (SELECT * FROM sys.availability_databases_cluster WHERE database_name = 'DatabaseName') AND DATABASEPROPERTYEX('DatabaseName', 'Updateability') = 'READ_WRITE' AND DATABASEPROPERTYEX('DatabaseName', 'Status') = 'ONLINE'
        BEGIN
            ALTER AVAILABILITY GROUP AG1 REMOVE DATABASE DatabaseName;
            WAITFOR DELAY '00:00:05';
        END
    ".Replace("DatabaseName", $DatabaseName)

    "SEC1N1", "SEC1N2", "SEC1N3", "DAC1N1", "DAC1N2" | New-DbConnection | New-DbCommand $removeDatabase | Get-DbData

    $dropDatabase = "
        IF DB_ID('DatabaseName') IS NOT NULL
        BEGIN
            DECLARE @Spid INT
            DECLARE @Command NVARCHAR(100)

            DECLARE KILLER CURSOR FOR
            SELECT session_id FROM sys.dm_exec_sessions des WHERE is_user_process = 1 AND database_id = DB_ID('DatabaseName')
            UNION
            SELECT request_session_id FROM sys.dm_tran_locks dtl JOIN sys.dm_exec_sessions des ON dtl.request_session_id = des.session_id AND des.is_user_process = 1 WHERE resource_type = 'DATABASE' AND resource_database_id = DB_ID('DatabaseName')

            OPEN KILLER
            FETCH NEXT FROM KILLER INTO @Spid

            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @Command = 'KILL ' + CONVERT(NVARCHAR(100), @Spid)
                EXEC sp_executesql @Command
                FETCH NEXT FROM KILLER INTO @Spid
            END

            CLOSE KILLER
            DEALLOCATE KILLER

            DROP DATABASE [DatabaseName]
        END
    ".Replace("DatabaseName", $DatabaseName)

    "SEC1N1", "SEC1N2", "SEC1N3", "DAC1N1", "DAC1N2" | New-DbConnection | New-DbCommand $dropDatabase -CommandTimeout 120 | Get-DbData
}

$setResourceParam = @{
    ModuleName = @{ ModuleName = "SqlServerDsc"; RequiredVersion = "12.2.0.0"; }
    Name = "SqlAGDatabase"
    Method = "Set"
    Property = @{
        AvailabilityGroupName = "AG1"
        BackupPath = "\\CHDC01\Temp"
        # DatabaseName = @()
        # MatchDatabaseOwner = $false
        ServerName = "SEC1N1"
        InstanceName = "MSSQLSERVER"
        Ensure = "Present"
    }
    # Verbose = $true
}

function Get-Owner {
    param (
        $ServerInstance,
        $DatabaseName
    )

    New-DbConnection $ServerInstance master | New-DbCommand "SELECT SUSER_SNAME(owner_sid) FROM sys.databases WHERE name = @DatabaseName;" -Parameters @{ DatabaseName = $DatabaseName; } | Get-DbData -OutputAs Scalar
}

Describe "Adding databases to an Availability Group" {
    BeforeAll {
        Create-Login
    }

    It "Should work without MatchDatabaseOwner" {
        $databaseName = "Normal"
        Remove-Database $databaseName

        Create-PrimaryDatabase $databaseName
        $setResourceParam.Property.DatabaseName = @($databaseName)
        Invoke-DscResource @setResourceParam | Should -Be $false

        Get-Owner "SEC1N1" $databaseName | Should -Be "sa"
        "SEC1N2", "SEC1N3", "DAC1N1", "DAC1N2" | ForEach-Object {
            Get-Owner $_ $databaseName | Should -Be "LAB\CHWK01$"
        }
    }

    It "Should work with MatchDatabaseOwner but doesn't" {
        $databaseName = "MatchDatabaseOwner"
        Remove-Database $databaseName

        Create-PrimaryDatabase $databaseName
        $setResourceParam.Property.DatabaseName = @($databaseName)
        $setResourceParam.Property.MatchDatabaseOwner = $true
        Invoke-DscResource @setResourceParam | Should -Be $false
    }

    It "Should work without MatchDatabaseOwner on the new version" {
        $databaseName = "NewNormal"
        Remove-Database $databaseName

        Create-PrimaryDatabase $databaseName
        $setResourceParam.ModuleName = @{ ModuleName = "SqlServerDsc"; RequiredVersion = "12.3.0.0"; }
        $setResourceParam.Property.DatabaseName = @($databaseName)
        $setResourceParam.Property.MatchDatabaseOwner = $false
        Invoke-DscResource @setResourceParam | Should -Be $false

        Get-Owner "SEC1N1" $databaseName | Should -Be "sa"
        "SEC1N2", "SEC1N3", "DAC1N1", "DAC1N2" | ForEach-Object {
            Get-Owner $_ $databaseName | Should -Be "LAB\CHWK01$"
        }
    }

    It "Should work with MatchDatabaseOwner on the new version" {
        $databaseName = "NewMatchDatabaseOwner"
        Remove-Database $databaseName

        Create-PrimaryDatabase $databaseName
        $setResourceParam.ModuleName = @{ ModuleName = "SqlServerDsc"; RequiredVersion = "12.3.0.0"; }
        $setResourceParam.Property.DatabaseName = @($databaseName)
        $setResourceParam.Property.MatchDatabaseOwner = $true
        Invoke-DscResource @setResourceParam | Should -Be $false

        Get-Owner "SEC1N1" $databaseName | Should -Be "sa"
        "SEC1N2", "SEC1N3", "DAC1N1", "DAC1N2" | ForEach-Object {
            Get-Owner $_ $databaseName | Should -Be "sa"
        }
    }
}