NOTE: This page is no longer being updated. To install the latest version of DSC Resource Kit, please use the PowerShell Gallery. If you need to report issues or would like to contribute to development, check out our GitHub Repositories.

Introduction

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.

Installation

To install xSqlPs module

  • Unzip the content under $env:ProgramFiles\WindowsPowerShell\Modules folder

To confirm installation:

Requirements

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.

Description

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.

Details

xSqlServerInstall resource has following properties:

xSqlHAService resource has following properties:

  • InstanceName:                        The name of the SQL instance
  •  SqlAdministratorCredential: The SQL Server Administrator credential
  • ServiceCredential:                   Domain credential used to run SQL Service

xSqlHAEndpoint resource has following properties:

  • InstanceName:                        The name of the SQL instance
  • AllowedUser:                           Unique name for HA database mirroring endpoint of the sql instance.
  • PortNumber:                            The single port number(nnnn) on which the Sql HA to listen to.

xSqlHAGroup resource has following properties:

  • Name:                                        The name of the SQL Availability Group
  • Database:                                 Array of databases on the local SQL instance.  Each database can belong to only one HA group.
  • ClusterName:                           The name of windows failover cluster for the availability group
  • DatabaseBackupPath:            The net share for SQL replication initialization
  • InstanceName:                         Name of the SQL Instance
  • EndPointName:                        Name of EndPoint to access High Availability sql instance.
  • DomainCredential:                  Domain credential could get list of cluster nodes
  • SqlAdministratorCredential SQL Server Administratory credential

xWaitForSqlHAGroup resource has following properties:

  • Name:                                         The name of SQL High Availability Group
  • ClusterName:                            The name of windows failover cluster for the availability group.
  • RetryIntervalSec:                      Interval to check the HA group existency
  • RetryCount:                               Maximum number of retries to check HA group existency
  • InstanceName:                          The name of SQL instance
  • DomainCredential:                   Domain credential could get list of cluster nodes
  • SqlAdministratorCredential:   SQL Server Administrator credential

Renaming Requirements

When making changes to these resources, we suggest the following practice:

  1. Update the following names by replacing MSFT with your company/community name and replacing the "x" with "c" (short for "Community") or another prefix of your choice:
    • Module name (ex: xSqlPs becomes cSqlPs)
    • Resource folder (ex: MSFT_xSqlHAGroup becomes Contoso_cSqlHAGroup)
    • Resource Name (ex: MSFT_xSqlHAGroup  becomes Contoso_cSqlHAGroup)
    • Resource Friendly Name (ex: xSqlHAGroup becomes cSqlHAGroup)
    • MOF class name (ex: MSFT_xSqlHAGroup becomes Contoso_cSqlHAGroup)
    • Filename for the <resource>.schema.mof (ex: MSFT_xSqlHAGroup.schema.mof becomes Contoso_cSqlHAGroup.schema.mof)
  2. Update module and metadata information in the module manifest
  3. Update any configuration that use these resources

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.

Versions

1.0.0.0

1.1.0.0

 

 

1.1.1.0

 

 

1.1.2.0

1.1.3.1

 

 

 

Example: Install SQL on a Single Node.

This example installs SQL Server on a single node.  Note: this sample has prequisites that must be met before it can be run.  It assumes that the .Net 3.5 source is present under C:\Software\sxs, and that the SQL full enterprise installer is present under C:\Software\sql, and local self singed certificate is prepared.
PowerShell
Edit|Remove
# 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