SqlServerDsc: Need new fully working examples

[IlleNilsson]

Hi All,

I've managed to set up two SQL Servers with multiple instances. Enabling network services and firewalls successfully, but it was a hard time getting there. Error messages are not informative.

Now I'm of to xFailoverCluster, a new world for me. I cant fail the xFailoverCluster resource since I know so little about clusters.

But I've been reading along and first most it seams that you have to have your network in order and be able to provision networks, New world for me, I'm from development and software architecture. By that said. GIVE ME A WORKING SQL Server.

Well, I'm patient so I read on about Clusters. So here I am, a student a gain. When I'm finished I'll post my scripts and configs.

[IlleNilsson]

And as far as I've gotten..

Configuration QSP {
param(
[PSCredential]$setupCredential,
[string[]]$sqlAdmins,
[PSCredential]$sqlSvcAccount,
[PSCredential]$agtSvcAccount
)

Import-DscResource -ModuleName PSDesiredStateConfiguration
Import-DscResource -ModuleName xFailoverCluster
Import-DscResource -ModuleName xSqlServer

Node $AllNodes.NodeName {
    foreach($sqlInstance in $Node.SqlInstances) {
        xSqlServerSetup "$($sqlInstance.NodeName)" {
            PsDscRunAsCredential = $setupCredential
            SourcePath = $Node.SqlSourcePath
            Action = "Install"
            # FailoverClusterGroupName = $Node.FailoverClusterGroupName
            # FailoverClusterIPAddress = $Node.FailoverClusterIPAddress
            # FailoverClusterNetworkName = $Node.FailoverClusterNetworkName
            SqlSysAdminAccounts = $sqlAdmins
            InstanceName = $sqlInstance.NodeName
            SqlSvcAccount = $sqlSvcAccount
            AgtSvcAccount = $agtSvcAccount
            Features = $sqlInstance.Features
            # DependsOn = @("[xCluster]PrepareCluster")
        }
        xSqlServerNetwork "$($sqlInstance.NodeName)" {
            PsDscRunAsCredential = $setupCredential
            SqlServer = $Node.NodeName
            InstanceName = $sqlInstance.NodeName
            ProtocolName = "TCP"
            TcpPort = $sqlInstance.TcpPort
            IsEnabled = $True
            RestartService = $True
            DependsOn = @("[xSqlServerSetup]$($sqlInstance.NodeName)")
        }
        xSqlServerFirewall "$($sqlInstance.NodeName)" {
            PsDscRunAsCredential = $setupCredential
            SourcePath = $Node.SqlSourcePath
            InstanceName = $sqlInstance.NodeName
            Features = $sqlInstance.Features
            Ensure = "Present"
            DependsOn = @("[xSqlServerNetwork]$($sqlInstance.NodeName)")
        }
        xSQLServerAlwaysOnService "$($sqlInstance.NodeName)" {
            PsDscRunAsCredential = $setupCredential
            SQLServer = $Node.NodeName
            SQLInstanceName = $sqlInstance.NodeName
            Ensure = "Present"
            DependsOn = @("[xSqlServerFirewall]$($sqlInstance.NodeName)")
        }
        xSQLServerAlwaysOnAvailabilityGroup $("$sqlInstance.NodeName") {
            PsDscRunAsCredential = $setupCredential
            SqlServer = $Node.NodeName
            SqlInstanceName = $sqlInstance.NodeName
            Name = "QS"
            AvailabilityMode = "SynchronousCommit"
            FailoverMode = "Automatic"
            Ensure = "Present"
            DependsOn = @("[xSQLServerAlwaysOnService]$($sqlInstance.NodeName)")
        }
        xSQLServerAvailabilityGroupListener "$($sqlInstance.NodeName)" {
            PsDscRunAsCredential = $setupCredential
            Name = $Node.FailoverClusterGroupName
            AvailabilityGroup = $Node.FailoverClusterGroupName
            NodeName = $Node.NodeName
            InstanceName = $sqlInstance.NodeName
            Ensure = "Present"
            DependsOn = @("[WindowsFeature]WSCS")
        }
    }
    Service "SqlBrowser" {
        Name = "SQLBrowser"
        StartupType = "Automatic"
        State = "Running"
        Ensure = "Present"
        DependsOn = @($Node.SqlInstances | % { "[xSqlServerFirewall]$($_.NodeName)" })
    }
    if ($Node.NodeType -eq "Primary") {
        xCluster PrepareCluster {
            PsDscRunAsCredential = $setupCredential
            DomainAdministratorCredential = $setupCredential
            Name = $Node.FailoverClusterNetworkName
            StaticIPAddress = $Node.FailoverClusterIPAddress
            DependsOn = @("[WindowsFeature]WSCS")
        }
     } else {
        xCluster PrepareCluster {
            PsDscRunAsCredential = $setupCredential
            DomainAdministratorCredential = $setupCredential
            Name = $Node.FailoverClusterNetworkName
            StaticIPAddress = $Node.FailoverClusterIPAddress
            DependsOn = @("[WindowsFeature]WSCS", "[xWaitForCluster]WaitForCluster")
        }
        xWaitForCluster WaitForCluster {
            PsDscRunAsCredential = $setupCredential
            Name = $Node.FailoverClusterNetworkName
            RetryIntervalSec = 3
            RetryCount = 3
            DependsOn = @("[WindowsFeature]WSCS")
        }
    }
    Service "Cluster" {
        Name = "ClusSvc"
        StartupType = "Automatic"
        State = "Running"
        Ensure = "Present"
        DependsOn = @("[xCluster]PrepareCluster")
    }
    WindowsFeature "WSCS" {
        Name = "Failover-Clustering"
        Ensure = "Present"
        DependsOn = @("[WindowsFeature]WSCSAutSrv", "[WindowsFeature]WSCSCmdLets", "[WindowsFeature]WSCSCmdLine")
    } 
    WindowsFeature "WSCSAutSrv" {
        Name = "RSAT-Clustering-AutomationServer"
        Ensure = "Present"
    } 
    WindowsFeature "WSCSCmdLets" {
        Name = "RSAT-Clustering-PowerShell"
        Ensure = "Present"
    } 
    WindowsFeature "WSCSCmdLine" {
        Name = "RSAT-Clustering-CmdInterface"
        Ensure = "Present"
    } 
    LocalConfigurationManager {
        DebugMode = "All"
        RebootNodeIfNeeded = $True
        ConfigurationMode = "ApplyAndAutoCorrect"
        AllowModuleOverwrite = $True
        RefreshMode = "Push"
        ActionAfterReboot = "ContinueConfiguration"           
    }
}

}

[IlleNilsson]

And here is the config

@{
AllNodes = @(
@{
NodeName = "QS0"
NodeType = "Primary"
},
@{
NodeName = "QS1"
NodeType = "Secondary"
},
@{
NodeName = "*"
WinSourcePath = "D:\Sources\Sxs"
SqlSourcePath = "E:"
PSDscAllowDomainUser = $True
PsDscAllowPlainTextPassword = $True
FailoverClusterGroupName = "QS"
FailoverClusterIPAddress = "10.0.0.50"
FailoverClusterNetworkName = "QS"
SqlInstances = @(
@{
NodeName = "INTHTLCNT"
Features = "SQLENGINE"
TcpPort = 11433
},
@{
NodeName = "INTSHPCNT"
Features = "SQLENGINE"
TcpPort = 12433
}#,
# @{
# NodeName = "INTSHPPVT"
# Features = "SQLENGINE" # Install role PowerPivot?
# TcpPort = 13433
# },
# @{
# NodeName = "EXTSHPCNT"
# Features = "SQLENGINE"
# TcpPort = 14433
# }
)
}
)
}

[johlju]

Please have a look at xFailOverCluster for more information on how to setup up Failover Clustering. You should first have a working failover cluster before you try to install a clustered instance of SQL Server.

[johlju]

We need new examples for this resource module since the old ones are obsolete (see root of Examples folder), and also see the issue #462.
Happy to see new fully working examples.

[IlleNilsson]

Well, some of it does, not Cluster and AOAG. But we will get there.

[stale]

This issue has been automatically marked as needs more information because it has not had activity from the community in the last 30 days. It will be closed if no further activity occurs within 10 days. If the issue is label with any of the work labels (e.g bug, enhancement, documentation, or tests) the issue will not auto-close.