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 xSQLServer 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 resources for setup of SQL Server that is fully compliant with the requirements of System Center, as well as resources for configuration of SQL Server feature specific firewall rules, and configuration of SQL Server Reporting Services secure connection level. The xSQLServer module contains the following resources:

  • xSQLServerSetup: for installation of a standalone SQL Server instance
  • xSQLServerFirewall: configure firewall settings to allow remote access to a SQL Server instance
  • xSQLServerRSSecureConnectionLevel: set the secure connection level for SQL Server Reporting Services
  • xSQLServerFailoverClusterSetup: for installation of SQL Server failover cluster instances
  • xSQLServerRSConfig: configure SQL Server Reporting Services to use a database engine in another instance

 

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 xSQLServer 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 xSQLServer 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 the xSQLServer module

  • If you are using WMF4 / PowerShell Version 4: Unzip the content under $env:ProgramFiles\WindowsPowerShell\Modules folder
  • If you are using WMF5 Preview: From an elevated PowerShell session run ‘Install-Module xSCSR’

To confirm installation:

  • Run Get-DSCResource to see that xSQLServerSetup, xSQLServerFirewall, xSQLServerRSSecureConnectionLevel, and xSQLServerRSConfig are among the DSC Resources listed.

Requirements

This module requires 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.

Details

The xSQLServer enables setup of SQL Server, fully compliant with the requirements of System Center, as well as configuration of SQL Server feature specific firewall rules, and configuration of SQL Server Reporting Services secure connection level. This module contains the following resources:

xSQLServerSetup resource has the following properties:

  • SourcePath: REQUIRED - UNC path to the root of the source files for installation.
  • SourceFolder: Folder within the source path containing the source files for installation.
  • SetupCredential: REQUIRED - Credential to be used to perform the installation.
  • Features: KEY - SQL features to be installed.
  • InstanceName: KEY - SQL instance to be installed.
  • InstanceID: SQL instance ID, if different from InstanceName.
  • PID: Product key for licensed installations.
  • UpdateEnabled: Enabled updates during installation.
  • UpdateSource: Source of updates to be applied during installation.
  • SQMReporting: Enable customer experience reporting.
  • ErrorReporting: Enable error reporting.
  • InstallSharedDir: Installation path for shared SQL files.
  • InstallSharedWOWDir: Installation path for x86 shared SQL files.
  • InstanceDir: Installation path for SQL instance files.
  • SQLSvcAccount: Service account for the SQL service.
  • SQLSvcAccountUsername: Output username for the SQL service.
  • AgtSvcAccount: Service account for the SQL Agent service.
  • AgtSvcAccountUsername: Output username for the SQL Agent service.
  • SQLCollation: Collation for SQL.
  • SQLSysAdminAccounts: Array of accounts to be made SQL administrators.
  • SecurityMode: SQL security mode.
  • SAPwd: SA password, if SecurityMode=SQL.
  • InstallSQLDataDir: Root path for SQL database files.
  • SQLUserDBDir: Path for SQL database files.
  • SQLUserDBLogDir: Path for SQL log files.
  • SQLTempDBDir: Path for SQL TempDB files.
  • SQLTempDBLogDir: Path for SQL TempDB log files.
  • SQLBackupDir: Path for SQL backup files.
  • FTSvcAccount: Service account for the Full Text service.
  • FTSvcAccountUsername: Output username for the Full Text service.
  • RSSvcAccount: Service account for Reporting Services service.
  • RSSvcAccountUsername: Output username for the Reporting Services service.
  • ASSvcAccount: Service account for Analysus Services service.
  • ASSvcAccountUsername: Output username for the Analysis Services service.
  • ASCollation: Collation for Analysis Services.
  • ASSysAdminAccounts: Array of accounts to be made Analysis Services admins.
  • ASDataDir: Path for Analysis Services data files.
  • ASLogDir: Path for Analysis Services log files.
  • ASBackupDir: Path for Analysis Services backup files.
  • ASTempDir: Path for Analysis Services temp files.
  • ASConfigDir: Path for Analysis Services config.
  • ISSvcAccount: Service account for Integration Services service.
  • ISSvcAccountUsername: Output username for the Integration Services service.

xSQLServerFirewall has the following properties:

  • Ensure: KEY - An enumerated value (Present, Absent) that describes if the SQL firewall rules are is expected to be enabled on the machine.
  • SourcePath: REQUIRED - UNC path to the root of the source files for installation.
  • SourceFolder: Folder within the source path containing the source files for installation.
  • Features: KEY - SQL features to enable firewall rules for
  • InstanceName: KEY - SQL instance to enable firewall rules for.
  • DatabaseEngineFirewall: Is the firewall rule for the Database Engine enabled?
  • BrowserFirewall: Is the firewall rule for the Browser enabled?
  • ReportingServicesFirewall: Is the firewall rule for Reporting Services enabled?
  • AnalysisServicesFirewall: Is the firewall rule for Analysis Services enabled?
  • IntegrationServicesFirewall: Is the firewall rule for the Integration Services enabled?

xSQLServerRSSecureConnectionLevel has the following properties:

  • InstanceName: KEY - SQL instance to set secure connection level for.
  • SecureConnectionLevel: KEY - SQL Server Reporting Service secure connection level.
  • Credential: REQUIRED - Credential with administrative permissions to the SQL instance.

xSQLServerFailoverClusterSetup resource has the following properties:

  • Action: Key: Options are Prepare or Complete
  • SourcePath: REQUIRED - UNC path to the root of the source files for installation.
  • SourceFolder: Folder within the source path containing the source files for installation.
  • Credential: REQUIRED - Credential to be used to perform the installation.
  • Features: REQUIRED - SQL features to be installed.
  • InstanceName: KEY - SQL instance to be installed.
  • InstanceID: SQL instance ID, if different from InstanceName.
  • PID: Product key for licensed installations.
  • UpdateEnabled: Enabled updates during installation.
  • UpdateSource: Source of updates to be applied during installation.
  • SQMReporting: Enable customer experience reporting.
  • ErrorReporting: Enable error reporting.
  • FailoverClusterGroup: Name of the resource group to be used for the SQL Server failover cluster.
  • FailoverClusterNetworkName: REQUIRED - Network name for the SQL Server failover cluster.
  • FailoverClusterIPAddress: IPv4 address for the SQL Server failover cluster.
  • InstallSharedDir: Installation path for shared SQL files.
  • InstallSharedWOWDir: Installation path for x86 shared SQL files.
  • InstanceDir: Installation path for SQL instance files.
  • SQLSvcAccount: Service account for the SQL service.
  • SQLSvcAccountUsername: Output username for the SQL service.
  • AgtSvcAccount: Service account for the SQL Agent service.
  • AgtSvcAccountUsername: Output username for the SQL Agent service.
  • SQLCollation: Collation for SQL.
  • SQLSysAdminAccounts: Array of accounts to be made SQL administrators.
  • SecurityMode: SQL security mode.
  • SAPwd: SA password, if SecurityMode=SQL.
  • InstallSQLDataDir: Root path for SQL database files.
  • SQLUserDBDir: Path for SQL database files.
  • SQLUserDBLogDir: Path for SQL log files.
  • SQLTempDBDir: Path for SQL TempDB files.
  • SQLTempDBLogDir: Path for SQL TempDB log files.
  • SQLBackupDir: Path for SQL backup files.
  • ASSvcAccount: Service account for Analysus Services service.
  • ASSvcAccountUsername: Output username for the Analysis Services service.
  • ASCollation: Collation for Analysis Services.
  • ASSysAdminAccounts: Array of accounts to be made Analysis Services admins.
  • ASDataDir: Path for Analysis Services data files.
  • ASLogDir: Path for Analysis Services log files.
  • ASBackupDir: Path for Analysis Services backup files.
  • ASTempDir: Path for Analysis Services temp files.
  • ASConfigDir: Path for Analysis Services config.
  • ISSvcAccount: Service account for Integration Services service.
  • ISSvcAccountUsername: Output username for the Integration Services service.
  • ISFileSystemFolder: File system folder for Integration Services.

xSQLServerRSConfig resource has the following properties:

  • InstanceName: KEY - Name of the SQL Server Reporting Services instance to be configured.
  • RSSQLServer: REQUIRED - Name of the SQL Server to host the Reporting Service database.
  • RSSQLInstanceName: REQUIRED - Name of the SQL Server instance to host the Reporting Service database.
  • SQLAdminCredential: REQUIRED - Credential to be used to perform the configuration.
  • IsInitialized: Output is the Reporting Services instance initialized.

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: xSQLServer becomes cSQLServer)
    • Resource folder (ex: MSFT_xSQLServerSetup becomes Contoso_cSQLServerSetup)
    • Resource Name (ex: MSFT_xSQLServerSetup becomes Contoso_cSQLServerSetup)
    • Resource Friendly Name (ex: xSQLServerSetup becomes cSQLServerSetup)
    • MOF class name (ex: MSFT_xSQLServerSetup becomes Contoso_cSQLServerSetup)
    • Filename for the <resource>.schema.mof (ex: MSFT_xSQLServerSetup.schema.mof becomes Contoso_cSQLServerSetup.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_SQLServerSetup" or "SQLServerSetup"). If the next version of Windows Server ships with "MSFT_SQLServerSetup" resources, 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.2.1.0

  • Increased timeout for setup process to start to 60 seconds.

1.2.0.0

  • Updated release with the following new resources
    • xSQLServerFailoverClusterSetup
    • xSQLServerRSConfig

1.1.0.0

  • Initial release with the following resources
    • xSQLServerSetup
    • xSQLServerFirewall
    • xSQLServerRSSecureConnectionLevel

Examples

Examples for use of this resource can be found with the System Center resources, such as xSCVMM, xSCSMA, and xSCOM