The xSqlPs module is a part of the Windows PowerShell Desired State Configuration (DSC) Resource Kit, which is a collection of DSC Resources produced by the PowerShell Team. This module contains the xSqlServerInstall, xSqlHAService, xSqlHAEndpoint, xSqlHAGroup, and xWaitForSqlHAGroup resources. This DSC Resource allows you to rename a computer and add it to a domain or workgroup.
All of the resources in the DSC Resource Kit are provided AS IS, and are not supported through any Microsoft standard support program or service. The ""x" in xSqlPs stands for experimental, which means that these resources will be fix forward and monitored by the module owner(s).
Please leave comments, feature requests, and bug reports in the Q & A tab for this module.
If you would like to modify xSqlPs module, feel free. When modifying, please update the module name, resource friendly name, and MOF class name (instructions below). As specified in the license, you may copy or modify this resource as long as they are used on the Windows Platform.
For more information about Windows PowerShell Desired State Configuration, check out the blog posts on the PowerShell Blog (this is a good starting point). There are also great community resources, such as PowerShell.org , or PowerShell Magazine . For more information on the DSC Resource Kit, check out this blog post.
To install xSqlPs module
To confirm installation:
This module requires the latest version of PowerShell (v4.0, which ships in Windows 8.1 or Windows Server 2012R2). To easily use PowerShell 4.0 on older operating systems, install WMF 4.0. Please read the installation instructions that are present on both the download page and the release notes for WMF 4.0.
The xSqlPs module contains the xSqlServerInstall, xSqlHAService, xSqlHAEndpoint, xSqlHAGroup, xWaitForSqlHAGroup DSC Resources. These DSC Resources allow you to install a SQL Server from software stored on a network or local share, enable SQL high avialability service(HA), configure SQL HA endpoint.
The xSqlServerInstall resource is responsible for installing SQL Enterprise on target machine. The xSqlHAService resource is responsible for enabling SQL high availability (HA) service on a given SQL instance. The xSqlHAEndpoint resource is responsible for configuring the given instance of SQL high availability service to listen port 5022 with given name, and assigning users that are allowed to communicate through the SQL endpoint. The xSqlHAGroup resource is responsible for configuring an SQL HA group. If the HA group does not exist it will create one with the given name on given SQL instance and add the HA group database(s) to local SQL instance. The xWaitForSqlHAGroup resource is responsible for waiting for SQL HA group to be ready by checking the state of the HA group of a given name in a given interval till either the HA group is discoverable or the number of retries reached its maximum.
Note: these resources assume familiarity with certain aspects of the SQL Server install process. SQL Server Enterprise installer requires
.NET 3.5 to be installed, therefore DSC resource that installs SQL Enterprise requires Net 3.5 sources to be present on the machine.
xSqlServerInstall resource has following properties:
xSqlHAService resource has following properties:
xSqlHAEndpoint resource has following properties:
xSqlHAGroup resource has following properties:
xWaitForSqlHAGroup resource has following properties:
When making changes to these resources, we suggest the following practice:
We reserve resource and module names without prefixes ("x" or "c") for future use (e.g. "MSFT_SqlHAGroup" ). If the next version of Windows Server ships with a "SqlHAGroup" resource, we don't want to break any configurations that use any community modifications. Please keep a prefix such as "c" on all community modifications.
1.0.0.0
1.1.0.0
1.1.1.0
1.1.2.0
1.1.3.0
# Configuration to install Sql server database engine and management tools. # # A. Prepare a local self signed certificate with the following steps: # 1. Install MakeCert.exe (Microsoft SDK 8.1 http://msdn.microsoft.com/en-us/windows/desktop/bg162891.aspx) # 2. Open console with Administrator elevation, run the following: # makecert -r -pe -n "CN=DSCDemo" -sky exchange -ss my -sr localMachine # B. Prepare software and run the configuration. # 1. On the machine, create a folder as Software # 2. On the machine, copy Windows Server 2012 R2 source\sxs to C:\Software\sxs # 3. copy sql full enterprise installation software to C:\Software\sql # 4. copy xSqlPs to $env:ProgramFiles\WindowsPowershell\Modules # 5. Copy this file (sql101.ps1) to c:\DSCDemo # 6. in powershell with administrator elevation, go to c:\DSCDemo, run .\sql101.ps1 $certSubject = "CN=DSCDemo" $keysFolder = Join-Path $env:SystemDrive -ChildPath "Keys" $cert = dir Cert:\LocalMachine\My | ? { $_.Subject -eq $certSubject } if (! (Test-Path $keysFolder )) { md $keysFolder | Out-Null } $certPath = Export-Certificate -Cert $cert -FilePath (Join-Path $keysFolder -ChildPath "Dscdemo.cer") $ConfigData= @{ AllNodes = @( @{ NodeName = "localhost" CertificateFile = $certPath Thumbprint = $cert.Thumbprint } ) } Configuration Sql101 { param( [Parameter(Mandatory=$true)] [ValidateNotNullorEmpty()] [PsCredential] $credential ) Import-DscResource -Module xSqlPs Node $AllNodes.NodeName { # Install SQL Server WindowsFeature installdotNet35 { Ensure = "Present" Name = "Net-Framework-Core" Source = "c:\software\sxs" } xSqlServerInstall installSqlServer { InstanceName = "PowerPivot" SourcePath = "c:\software\sql" Features= "SQLEngine,SSMS" SqlAdministratorCredential = $credential DependsOn = "[WindowsFeature]installdotNet35" } LocalConfigurationManager { CertificateId = $node.Thumbprint RebootNodeIfNeeded = $true } } } Sql101 -ConfigurationData $ConfigData -OutputPath .\Mof -credential (Get-Credential -UserName "sa" -Message "Enter password for SqlAdministrator sa") Set-DscLocalConfigurationManager .\Mof Start-DscConfiguration -Path .\Mof -ComputerName localhost -Wait -Verbose
# Configuration to install Sql server database engine and management tools. # # A. Prepare a local self signed certificate with the following steps: # 1. Install MakeCert.exe (Microsoft SDK 8.1 http://msdn.microsoft.com/en-us/windows/desktop/bg162891.aspx) # 2. Open console with Administrator elevation, run the following: # makecert -r -pe -n "CN=DSCDemo" -sky exchange -ss my -sr localMachine # B. Prepare software and run the configuration. # 1. On the machine, create a folder as Software # 2. On the machine, copy Windows Server 2012 R2 source\sxs to C:\Software\sxs # 3. copy sql full enterprise installation software to C:\Software\sql # 4. copy xSqlPs to $env:ProgramFiles\WindowsPowershell\Modules # 5. Copy this file (sql101.ps1) to c:\DSCDemo # 6. in powershell with administrator elevation, go to c:\DSCDemo, run .\sql101.ps1 $certSubject = "CN=DSCDemo" $keysFolder = Join-Path $env:SystemDrive -ChildPath "Keys" $cert = dir Cert:\LocalMachine\My | ? { $_.Subject -eq $certSubject } if (! (Test-Path $keysFolder )) { md $keysFolder | Out-Null } $certPath = Export-Certificate -Cert $cert -FilePath (Join-Path $keysFolder -ChildPath "Dscdemo.cer") $ConfigData= @{ AllNodes = @( @{ NodeName = "localhost" CertificateFile = $certPath Thumbprint = $cert.Thumbprint } ) } Configuration Sql101 { param( [Parameter(Mandatory=$true)] [ValidateNotNullorEmpty()] [PsCredential] $credential ) Import-DscResource -Module xSqlPs Node $AllNodes.NodeName { # Install SQL Server WindowsFeature installdotNet35 { Ensure = "Present" Name = "Net-Framework-Core" Source = "c:\software\sxs" } xSqlServerInstall installSqlServer { InstanceName = "PowerPivot" SourcePath = "c:\software\sql" Features= "SQLEngine,SSMS" SqlAdministratorCredential = $credential DependsOn = "[WindowsFeature]installdotNet35" } LocalConfigurationManager { CertificateId = $node.Thumbprint RebootNodeIfNeeded = $true } } } Sql101 -ConfigurationData $ConfigData -OutputPath .\Mof -credential (Get-Credential -UserName "sa" -Message "Enter password for SqlAdministrator sa") Set-DscLocalConfigurationManager .\Mof Start-DscConfiguration -Path .\Mof -ComputerName localhost -Wait -Verbose