README.md

SqlServerDsc

The SqlServerDsc module contains DSC resources for deployment and configuration of SQL Server.

This project has adopted this code of conduct.

Branches

master

This is the branch containing the latest release - no contributions should be made directly to this branch.

dev

This is the development branch to which contributions should be proposed by contributors as pull requests. This development branch will periodically be merged to the master branch, and be released to PowerShell Gallery.

Contributing

Regardless of the way you want to contribute we are tremendously happy to have you here.

There are several ways you can contribute. You can submit an issue to report a bug. You can submit an issue to request an improvement. You can take part in discussions for issues. You can review pull requests and comment on other contributors changes. You can also improve the resources and tests, or even create new resources, by sending in pull requests yourself.

• If you want to submit an issue or take part in discussions, please browse the list of issues. Please check out Contributing to the DSC Resource Kit on how to work with issues.
• If you want to review pull requests, please first check out the Review Pull Request guidelines, and the browse the list of pull requests and look for those pull requests with label 'needs review'.
• If you want to improve this resource module, then please check out the following guidelines.
  • The specific Contributing to SqlServerDsc guidelines.
  • The common Style Guidelines & Best Practices.
  • The common Testing Guidelines.
  • If you are new to GitHub (and git), then please check out Getting Started with GitHub.
  • If you are new to Pester and writing test, then please check out Getting started with Pester.

If you need any help along the way, don't be afraid to ask. We are here for each other.

Installation

From GitHub source code

To manually install the module, download the source code from GitHub and unzip the contents to the '$env:ProgramFiles\WindowsPowerShell\Modules' folder.

From PowerShell Gallery

To install from the PowerShell gallery using PowerShellGet (in PowerShell 5.0) run the following command:

Find-Module -Name SqlServerDsc | Install-Module

To confirm installation, run the below command and ensure you see the SQL Server DSC resources available:

Get-DscResource -Module SqlServerDsc

Requirements

The minimum Windows Management Framework (PowerShell) version required is 5.0 or higher, which ships with Windows 10 or Windows Server 2016, but can also be installed on Windows 7 SP1, Windows 8.1, Windows Server 2008 R2 SP1, Windows Server 2012 and Windows Server 2012 R2.

Examples

You can review the Examples directory in the SqlServerDsc module for some general use scenarios for all of the resources that are in the module.

Change log

A full list of changes in each version can be found in the change log.

Resources

• SqlAG resource to ensure an availability group is present or absent.
• SqlAGDatabase to manage the database membership in Availability Groups.
• SqlAgentOperator resource to manage SQL Agent Operators.
• SqlAGListener Create or remove an availability group listener.
• SqlAGReplica resource to ensure an availability group replica is present or absent.
• SqlAlias resource to manage SQL Server client Aliases.
• SqlAlwaysOnService resource to enable always on on a SQL Server.
• SqlDatabase resource to manage ensure database is present or absent.
• SqlDatabaseDefaultLocation resource to manage default locations for Data, Logs, and Backups for SQL Server
• SqlDatabaseOwner resource to manage SQL database owners.
• SqlDatabasePermission resource to manage SQL database permissions.
• SqlDatabaseRecoveryModel resource to manage database recovery model.
• SqlDatabaseRole resource to manage SQL database roles.
• SqlRS configures SQL Server Reporting. Services to use a database engine in another instance.
• SqlScript resource to extend DSC Get/Set/Test functionality to T-SQL.
• SqlScriptQuery resource to extend DSC Get/Set/Test functionality to T-SQL.
• SqlServerConfiguration resource to manage SQL Server Configuration Options.
• SqlServerDatabaseMail resource to manage SQL Server Database Mail.
• SqlServerEndpoint resource to ensure database endpoint is present or absent.
• SqlServerEndpointPermission Grant or revoke permission on the endpoint.
• SqlServerEndpointState Change state of the endpoint.
• SqlServerLogin resource to manage SQL logins.
• SqlServerMaxDop resource to manage MaxDegree of Parallelism for SQL Server.
• SqlServerMemory resource to manage Memory for SQL Server.
• SqlServerNetwork resource to manage SQL Server Network Protocols.
• SqlServerPermission Grant or revoke permission on the SQL Server.
• SqlServerReplication resource to manage SQL Replication distribution and publishing.
• SqlServerRole resource to manage SQL server roles.
• SqlServerSecureConnection resource to enable encrypted SQL connections.
• SqlServiceAccount Manage the service account for SQL Server services.
• SqlSetup installs a standalone SQL Server instance.
• SqlWaitForAG resource to wait until availability group is created on primary server.
• SqlWindowsFirewall configures firewall settings to allow remote access to a SQL Server instance.

SqlAG

This resource is used to create, remove, and update an Always On Availability Group. It will also manage the Availability Group replica on the specified node.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.
• 'NT SERVICE\ClusSvc' or 'NT AUTHORITY\SYSTEM' must have the 'Connect SQL', 'Alter Any Availability Group', and 'View Server State' permissions.

Parameters

• [String] Name (Key): The name of the availability group.
• [String] ServerName (Required): Hostname of the SQL Server to be configured.
• [String] InstanceName (Key): Name of the SQL instance to be configured.
• [String] Ensure (Write): Specifies if the availability group should be present or absent. Default is Present. { Present | Absent }
• [String] AutomatedBackupPreference (Write): Specifies the automated backup preference for the availability group. Default is None. { Primary | SecondaryOnly | Secondary | None }
• [String] AvailabilityMode (Write): Specifies the replica availability mode. Default is 'AsynchronousCommit'. { AsynchronousCommit | SynchronousCommit }
• [UInt32] BackupPriority (Write): Specifies the desired priority of the replicas in performing backups. The acceptable values for this parameter are: integers from 0 through 100. Of the set of replicas which are online and available, the replica that has the highest priority performs the backup. Default is 50.
• [Boolean] BasicAvailabilityGroup (Write): Specifies the type of availability group is Basic. This is only available is SQL Server 2016 and later and is ignored when applied to previous versions.
• [Boolean] DatabaseHealthTrigger (Write): Specifies if the option Database Level Health Detection is enabled. This is only available is SQL Server 2016 and later and is ignored when applied to previous versions.
• [Boolean] DtcSupportEnabled (Write): Specifies if the option Database DTC Support is enabled. This is only available is SQL Server 2016 and later and is ignored when applied to previous versions. This can't be altered once the Availability Group is created and is ignored if it is the case.
• [String] ConnectionModeInPrimaryRole (Write): Specifies how the availability replica handles connections when in the primary role. { AllowAllConnections | AllowReadWriteConnections }
• [String] ConnectionModeInSecondaryRole (Write): Specifies how the availability replica handles connections when in the secondary role. { AllowNoConnections | AllowReadIntentConnectionsOnly | AllowAllConnections }
• [String] EndpointHostName (Write): Specifies the hostname or IP address of the availability group replica endpoint. Default is the instance network name.
• [String] FailureConditionLevel (Write): Specifies the automatic failover behavior of the availability group. { OnServerDown | OnServerUnresponsive | OnCriticalServerErrors | OnModerateServerErrors | OnAnyQualifiedFailureCondition }
• [String] FailoverMode (Write): Specifies the failover mode. Default is 'Manual'. { Automatic | Manual }
• [UInt32] HealthCheckTimeout (Write): Specifies the length of time, in milliseconds, after which AlwaysOn availability groups declare an unresponsive server to be unhealthy. Default is 30000.
• [Boolean] ProcessOnlyOnActiveNode (Write): Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server Instance.

Read-Only Properties from Get-TargetResource

• [String] EndpointUrl (Read): Gets the Endpoint URL of the availability group replica endpoint.
• [UInt32] EndpointPort (Read): Gets the port the database mirroring endpoint is listening on
• [UInt32] Version (Read): Gets the major version of the SQL Server instance.
• [Boolean] IsActiveNode (Read): Determines if the current node is actively hosting the SQL Server instance.

Examples

• Add a SQL Server Always On Availability Group
• Add a SQL Server Always On Availability Group and explicitly defines its properties
• Remove a SQL Server Always On Availability Group

Known issues

All issues are not listed here, see here for all open issues.

SqlAGDatabase

This resource is used to add and/or remove databases to a specified availability group.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.
• Target machine must be running Windows Management Framework (WMF) 5 or later.

Parameters

• [String] DatabaseName (Required): The name of the database(s) to add to the availability group. This accepts wildcards.

• [String] ServerName (Key): Hostname of the SQL Server where the primary replica of the availability group lives. If the availability group is not currently on this server, the resource will attempt to connect to the server where the primary replica lives.

• [String] InstanceName (Key): Name of the SQL instance where the primary replica of the availability group lives. If the availability group is not currently on this instance, the resource will attempt to connect to the instance where the primary replica lives.

• [String] AvailabilityGroupName (Key): The name of the availability group in which to manage the database membership(s).

• [String] BackupPath (Required): The path used to seed the availability group replicas. This should be a path that is accessible by all of the replicas.

• [String] Ensure (Write): Specifies the membership of the database(s) in the availability group. The options are:

  • Present: The defined database(s) are added to the availability group. All other databases that may be a member of the availability group are ignored.
  • Absent: The defined database(s) are removed from the availability group. All other databases that may be a member of the availability group are ignored.

  The default is 'Present'. { Present | Absent }

• [Boolean] Force (Write): When used with "Ensure = 'Present'" it ensures the specified database(s) are the only databases that are a member of the specified Availability Group. This parameter is ignored when 'Ensure' is 'Absent'.

• [Boolean] MatchDatabaseOwner (Write): If set to $true, this ensures the database owner of the database on the primary replica is the owner of the database on all secondary replicas. This requires the database owner is available as a login on all replicas and that the PSDscRunAsCredential has impersonate login, impersonate any login, or control server permissions. If set to $false, the owner of the database will be the PsDscRunAsCredential. The default is '$false'.

• [Boolean] ProcessOnlyOnActiveNode (Write): Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server Instance.

Read-Only Properties from Get-TargetResource

• [Boolean] IsActiveNode (Read): Determines if the current node is actively hosting the SQL Server instance.

Examples

• Add a database to an Availability Group
• Remove a database from an Availability Group
• Enforce only specified databases as member in an Availability Group

Known issues

All issues are not listed here, see here for all open issues.

SqlAgentOperator

This resource is used to add/remove SQL Agent Operators. You can also update the operators email address.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] Name (Key): The name of the SQL Agent Operator.
• [String] Ensure (Write): Specifies if the SQL Agent Operator should be present or absent. Default is Present. { Present | Absent }
• [String] ServerName (Key): The host name of the SQL Server to be configured. Default is $env:COMPUTERNAME.
• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] EmailAddress (Write): The email address to be used for the SQL Agent Operator.

Examples

• Add a SQL Agent Operator
• Remove a SQL Agent Operator

Known issues

All issues are not listed here, see here for all open issues.

SqlAGListener

No description.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.
• Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module.
• Requires that the Cluster name Object (CNO) has been delegated the right Create Computer Object in the organizational unit (OU) in which the Cluster Name Object (CNO) resides.

Parameters

• [String] InstanceName (Key): The SQL Server instance name of the primary replica.
• [String] AvailabilityGroup (Key): The name of the availability group to which the availability group listener is or will be connected.
• [String] ServerName (Write): The host name or FQDN of the primary replica.
• [String] Ensure (Write): If the availability group listener should be present or absent. Default value is 'Present'. { Present | Absent }.
• [String] Name (Write): The name of the availability group listener, max 15 characters. This name will be used as the Virtual Computer Object (VCO).
• [String[]] IpAddress (Write): The IP address used for the availability group listener, in the format 192.168.10.45/255.255.252.0. If using DHCP, set to the first IP-address of the DHCP subnet, in the format 192.168.8.1/255.255.252.0. Must be valid in the cluster-allowed IP range.
• [Uint16] Port (Write): The port used for the availability group listener.
• [Boolean] DHCP (Write): If DHCP should be used for the availability group listener instead of static IP address.

Examples

• Adds an Availability Group listener with the same name as the Availability Group VCO
• Adds an Availability Group listener with a different name than the Availability Group VCO
• Removes an Availability Group listener with the same name as the Availability Group VCO
• Removes an Availability Group listener with a different name than the Availability Group VCO
• Adds an Availability Group listener using DHCP on the default server subnet
• Adds an Availability Group listener using DHCP with a specific subnet

Known issues

All issues are not listed here, see here for all open issues.

SqlAGReplica

This resource is used to create, remove, and update an Always On Availability Group Replica.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.
• 'NT SERVICE\ClusSvc' or 'NT AUTHORITY\SYSTEM' must have the 'Connect SQL', 'Alter Any Availability Group', and 'View Server State' permissions.

Parameters

• [String] Name (Key): The name of the availability group replica. For named instances this must be in the following format ServerName\InstanceName.
• [String] AvailabilityGroupName (Key): The name of the availability group.
• [String] ServerName (Required): Hostname of the SQL Server to be configured.
• [String] InstanceName (Key): Name of the SQL instance to be configured.
• [String] PrimaryReplicaServerName (Write): Hostname of the SQL Server where the primary replica is expected to be active. If the primary replica is not found here, the resource will attempt to find the host that holds the primary replica and connect to it.
• [String] PrimaryReplicaInstanceName (Write): Name of the SQL instance where the primary replica lives.
• [String] Ensure (Write): Specifies if the availability group replica should be present or absent. Default is Present. { Present | Absent }
• [String] AvailabilityMode (Write): Specifies the replica availability mode. Default is 'AsynchronousCommit'. { AsynchronousCommit | SynchronousCommit }
• [UInt32] BackupPriority (Write): Specifies the desired priority of the replicas in performing backups. The acceptable values for this parameter are: integers from 0 through 100. Of the set of replicas which are online and available, the replica that has the highest priority performs the backup. Default is 50.
• [String] ConnectionModeInPrimaryRole (Write): Specifies how the availability replica handles connections when in the primary role. { AllowAllConnections | AllowReadWriteConnections }
• [String] ConnectionModeInSecondaryRole (Write): Specifies how the availability replica handles connections when in the secondary role. { AllowNoConnections | AllowReadIntentConnectionsOnly | AllowAllConnections }
• [String] EndpointHostName (Write): Specifies the hostname or IP address of the availability group replica endpoint. Default is the instance network name.
• [String] FailoverMode (Write): Specifies the failover mode. Default is 'Manual'. { Automatic | Manual }
• [String] ReadOnlyRoutingConnectionUrl (Write): Specifies the fully-qualified domain name (FQDN) and port to use when routing to the replica for read only connections.
• [String[]] ReadOnlyRoutingList (Write): Specifies an ordered list of replica server names that represent the probe sequence for connection director to use when redirecting read-only connections through this availability replica. This parameter applies if the availability replica is the current primary replica of the availability group.
• [Boolean] ProcessOnlyOnActiveNode (Write): Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server instance.

Read-Only Properties from Get-TargetResource

• [Uint16] EndpointPort (Read): Output the network port the endpoint is listening on. Used by Get-TargetResource.
• [String] EndpointUrl (Read): Output the endpoint URL of the Availability Group Replica. Used by Get-TargetResource.
• [Boolean] IsActiveNode (Read): Determines if the current node is actively hosting the SQL Server instance.

Examples

• Add a SQL Server Always On Availability Group Replica
• Remove a SQL Server Always On Availability Group Replica

Known issues

All issues are not listed here, see here for all open issues.

SqlAlias

No description.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.

Parameters

• [String] Name (Key): The name of Alias (e.g. svr01\inst01).
• [String] ServerName (Key): The SQL Server you are aliasing (the NetBIOS name or FQDN).
• [String] Ensure (Write): Determines whether the alias should be added or removed. Default value is 'Present'. { Present | Absent }.
• [String] Protocol (Write): Protocol to use when connecting. Valid values are 'TCP' or 'NP' (Named Pipes). Default value is 'TCP'. { TCP | NP }.
• [Uint16] TCPPort (Write): The TCP port SQL is listening on. Only used when protocol is set to 'TCP'. Default value is port 1433.
• [Boolean] UseDynamicTcpPort (Write): The UseDynamicTcpPort specify that the Net-Library will determine the port dynamically. The port specified in Port number will not be used. Default value is '$false'.

Read-Only Properties from Get-TargetResource

• [String] PipeName (Read): Named Pipes path from the Get-TargetResource method.

Examples

• Add an SQL Server alias
• Remove an SQL Server alias

Known issues

All issues are not listed here, see here for all open issues.

SqlAlwaysOnService

Enables or disabled SQL Server Always On high availability and disaster recovery (Always On HADR).

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.

Parameters

• [String] ServerName (Key): The hostname of the SQL Server to be configured.
• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] Ensure (Required): An enumerated value that describes if the SQL Server should have Always On high availability and disaster recovery (HADR) property enabled ('Present') or disabled ('Absent'). { Present | Absent }.
• [SInt32] RestartTimeout (Write): The length of time, in seconds, to wait for the service to restart. Default is 120 seconds.

Read-Only Properties from Get-TargetResource

• [Boolean] IsHadrEnabled (Read): Returns the status of AlwaysOn high availability and disaster recovery (HADR).

Examples

• Enable SQL Server Always On
• Disable SQL Server Always On

Known issues

All issues are not listed here, see here for all open issues.

SqlDatabase

This resource is used to create or delete a database. For more information about database, please read:

• Create a Database.
• Delete a Database.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] ServerName (Key): The host name of the SQL Server to be configured.
• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] Name (Key): The name of database to be created or dropped.
• [String] Collation (Write): The name of the SQL collation to use for the new database. Defaults to server collation.
• [String] Ensure (Write): When set to 'Present', the database will be created. When set to 'Absent', the database will be dropped. { Present | Absent }.

Examples

• Create a Database
• Delete a database

Known issues

All issues are not listed here, see here for all open issues.

SqlDatabaseDefaultLocation

This resource is used to configure default locations for user databases. The types of default locations that can be changed are Data, Log, and Backup. For more information about database default locations, please read the article Changing the Database Default Locations.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] ServerName (Key): The host name of the SQL Server to be configured.
• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] Type (Key): The type of database default location to be configured. { Data | Log | Backup }
• [String] Path (Required): The path to the default directory to be configured.
• [Boolean] RestartService (Write): If set to $true then SQL Server and dependent services will be restarted if a change to the configuration is made. The default value is $false.
• [Boolean] ProcessOnlyOnActiveNode (Write): Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server Instance.

Read-Only Property from Get-TargetResource

• [Boolean] IsActiveNode (Read): Determines if the current node is actively hosting the SQL Server instance.

Examples

• Set database default locations

Known issues

All issues are not listed here, see here for all open issues.

SqlDatabaseOwner

This resource is used to configure the owner of a database. For more information about database owner, please read the article Changing the Database Owner.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] Database (Key): The name of database to be configured.
• [String] Name (Required): The name of the login that will become a owner of the desired sql database.
• [String] ServerName (Write): The host name of the SQL Server to be configured.
• [String] InstanceName (Key): The name of the SQL instance to be configured.

Examples

• Set database owner

Known issues

All issues are not listed here, see here for all open issues.

SqlDatabasePermission

This resource is used to grant, deny or revoke permissions for a user in a database. For more information about permissions, please read the article Permissions (Database Engine).

Note! When revoking permission with PermissionState 'GrantWithGrant', both the grantee and all the other users the grantee has granted the same permission to, will also get their permission revoked.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] ServerName (Key): The host name of the SQL Server to be configured. Default values is '$env:COMPUTERNAME'.
• [String] InstanceName (Key): The name of the SQL instance to be configured. Default value is 'MSSQLSERVER'.
• [String] Database (Key): The name of the database.
• [String] Name (Key): The name of the user that should be granted or denied the permission.
• [String] PermissionState (Key): The state of the permission. { Grant | Deny | GrantWithGrant }.
• [String[]] Permissions (Required): The permissions to be granted or denied for the user in the database. Valid permissions can be found in the article SQL Server Permissions.
• [String] Ensure (Write): If the permission should be granted (Present) or revoked (Absent). { Present | Absent }.

Examples

• Grant Database Permission
• Revoke Database Permission
• Deny Database Permission

Known issues

All issues are not listed here, see here for all open issues.

SqlDatabaseRecoveryModel

This resource set the recovery model for a database. The recovery model controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: full, simple, and bulk-logged. Read more about recovery model in this article View or Change the Recovery Model of a Database

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] Name (Key): The SQL database name.
• [String] ServerName (Key): The host name of the SQL Server to be configured.
• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] RecoveryModel (Required): The recovery model to use for the database. { Full | Simple | BulkLogged }.

Examples

• Set the RecoveryModel of a database

Known issues

All issues are not listed here, see here for all open issues.

SqlDatabaseRole

This resource is used to add or remove role for a login in a database. Read more about database role in this article CREATE ROLE (Transact-SQL)

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] Name (Key): The name of the login that will become a member, or removed as a member, of the role(s).
• [String] ServerName (Key): The host name of the SQL Server to be configured.
• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] Database (Key): The database in which the login (user) and role(s) exist.
• [String] Ensure (Write): If 'Present' (the default value) then the login (user) will be added to the role(s). If 'Absent' then the login (user) will be removed from the role(s). { Present | Absent }.
• [String[]] Role_(Required): One or more roles to which the login (user) will be added or removed.

Examples

• Add Role of a database
• Remove Role of a database

Known issues

All issues are not listed here, see here for all open issues.

SqlRS

Initializes and configures SQL Reporting Services server.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Reporting Services 2008 or later.
• To use parameter UseSSL target machine must be running SQL Server Reporting Services 2008 R2 or later.
• If PsDscRunAsCredential common parameter is used to run the resource, the specified credential must have permissions to connect to the SQL Server instance specified in DatabaseServerName and DatabaseInstanceName, and have permission to create the Reporting Services databases.

Parameters

• [String] InstanceName (Key): Name of the SQL Server Reporting Services instance to be configured.
• [String] DatabaseServerName (Required): Name of the SQL Server to host the Reporting Service database.
• [String] DatabaseInstanceName (Required): Name of the SQL Server instance to host the Reporting Service database.
• [String] ReportServerVirtualDir (Write): Report Server Web Service virtual directory. Optional.
• [String] ReportsVirtualDir (Write): Report Manager/Report Web App virtual directory name. Optional.
• [String[]] ReportServerReservedUrl (Write): Report Server URL reservations. Optional. If not specified, 'http://+:80' URL reservation will be used.
• [String[]] ReportsReservedUrl (Write): Report Manager/Report Web App URL reservations. Optional. If not specified, 'http://+:80' URL reservation will be used.
• [Boolean] UseSsl (Write): If connections to the Reporting Services must use SSL. If this parameter is not assigned a value, the default is that Reporting Services does not use SSL.

Read-Only Properties from Get-TargetResource

• [Boolean] IsInitialized (Read): Output is the Reporting Services instance initialized.

Examples

• Default configuration
• Custom virtual directories and reserved URLs
• Custom virtual directory and using SSL
• Complete example with two instances in default configuration

Known issues

• This resource does not currently have full SSL support, please see issue #587 for more information.

All issues are not listed here, see here for all open issues.

Error messages

Error: The parameter is incorrect (HRESULT:-2147024809)

This is for example caused by trying to add an URL with the wrong format i.e. 'htp://+:80'.

Error: The Url has already been reserved (HRESULT:-2147220932)

This is caused when the URL is already reserved. For example when 'http://+:80' already exist.

Error: Cannot create a file when that file already exists (HRESULT:-2147024713)

This is caused when trying to add another URL using the same protocol. For example when trying to add 'http://+:443' when 'http://+:80' already exist.

SqlScript

Provides the means to run a user generated T-SQL script on the SQL Server instance. Three scripts are required; Get T-SQL script, Set T-SQL script and the Test T-SQL script.

Scripts

Get T-SQL Script (GetFilePath)

The Get T-SQL script is used to query the status when running the cmdlet Get-DscConfiguration, and the result can be found in the property GetResult.

Test T-SQL Script (TestFilePath)

The Test T-SQL script is used to test if the desired state is met. If Test T-SQL raises an error or returns any value other than 'null' the test fails, thus the Set T-SQL script is run.

Set T-SQL Script (SetFilePath)

The Set T-SQL script performs the actual change when Test T-SQL script fails.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server 2008 or later.
• Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module.

Note: There is a known problem running this resource using PowerShell 4.0. See issue #273 for more information.

Parameters

• [String] ServerInstance (Key): The name of an instance of the Database Engine. For a default instance, only specify the computer name. For a named instances, use the format ComputerName\InstanceName.
• [String] SetFilePath (Key): Path to the T-SQL file that will perform Set action.
• [String] GetFilePath (Key): Path to the T-SQL file that will perform Get action. Any values returned by the T-SQL queries will also be returned by the cmdlet Get-DscConfiguration through the GetResult property.
• [String] TestFilePath (Key): Path to the T-SQL file that will perform Test action. Any script that does not throw an error or returns null is evaluated to true. The cmdlet Invoke-Sqlcmd treats T-SQL Print statements as verbose text, and will not cause the test to return false.
• [PSCredential] Credential (Write): The credentials to authenticate with, using SQL Authentication. To authenticate using Windows Authentication, assign the credentials to the built-in parameter PsDscRunAsCredential. If both parameters Credential and PsDscRunAsCredential are not assigned, then SYSTEM account will be used to authenticate using Windows Authentication.
• [UInt32] QueryTimeout (Write): Specifies, as an integer, the number of seconds after which the T-SQL script execution will time out. In some SQL Server versions there is a bug in Invoke-Sqlcmd where the normal default value 0 (no timeout) is not respected and the default value is incorrectly set to 30 seconds.
• [String[]] Variable (Write): Specifies, as a string array, a scripting variable for use in the sql script, and sets a value for the variable. Use a Windows PowerShell array to specify multiple variables and their values. For more information how to use this, please go to the help documentation for Invoke-Sqlcmd.

Read-Only Properties from Get-TargetResource

• [String] GetResult (Read): Contains the values returned from the T-SQL script provided in the parameter GetFilePath when cmdlet Get-DscConfiguration is run.

Examples

• Run a script using SQL Authentication
• Run a script using Windows Authentication
• This example shows one way to create the SQL script files and how to run those files

Known issues

All issues are not listed here, see here for all open issues.

SqlScriptQuery

Provides the means to run a user generated T-SQL script on the SQL Server instance. Three scripts are required; Get T-SQL script, Set T-SQL script and the Test T-SQL script.

Scripts

Get T-SQL Script (GetQuery)

The Get T-SQL script is used to query the status when running the cmdlet Get-DscConfiguration, and the result can be found in the property GetResult.

Test T-SQL Script (TestQuery)

The Test T-SQL script is used to test if the desired state is met. If Test T-SQL raises an error or returns any value other than 'null' the test fails, thus the Set T-SQL script is run.

Set T-SQL Script (SetQuery)

The Set T-SQL script performs the actual change when Test T-SQL script fails.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server 2008 or later.
• Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module.

Parameters

• [String] ServerInstance (Key): The name of an instance of the Database Engine. For a default instance, only specify the computer name. For a named instances, use the format ComputerName\InstanceName.
• [String] SetQuery (Key): Query that will perform Set action.
• [String] GetQuery (Key): Query that will perform Get action. Any values returned by the T-SQL queries will also be returned by the cmdlet Get-DscConfiguration through the GetResult property.
• [String] TestQuery (Key): Query that will perform Test action. Any script that does not throw an error or returns null is evaluated to true. The cmdlet Invoke-Sqlcmd treats T-SQL Print statements as verbose text, and will not cause the test to return false.
• [PSCredential] Credential (Write): The credentials to authenticate with, using SQL Authentication. To authenticate using Windows Authentication, assign the credentials to the built-in parameter PsDscRunAsCredential. If both parameters Credential and PsDscRunAsCredential are not assigned, then SYSTEM account will be used to authenticate using Windows Authentication.
• [UInt32] QueryTimeout (Write): Specifies, as an integer, the number of seconds after which the T-SQL script execution will time out. In some SQL Server versions there is a bug in Invoke-Sqlcmd where the normal default value 0 (no timeout) is not respected and the default value is incorrectly set to 30 seconds.
• [String[]] Variable (Write): Specifies, as a string array, a scripting variable for use in the sql script, and sets a value for the variable. Use a Windows PowerShell array to specify multiple variables and their values. For more information how to use this, please go to the help documentation for Invoke-Sqlcmd.

Read-Only Properties from Get-TargetResource

• [String] GetResult (Read): Contains the values returned from the T-SQL script provided in the parameter GetQuery when cmdlet Get-DscConfiguration is run.

Examples

• Run a script using SQL Authentication
• Run a script using Windows Authentication
• This example shows one way to create the SQL script files and how to run those files

Known issues

All issues are not listed here, see here for all open issues.

SqlServerConfiguration

This resource manage the SQL Server Configuration Options on a SQL Server instance.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] ServerName (Key): The hostname of the SQL Server to be configured.
• [String] InstanceName (Key): Name of the SQL instance to be configured.
• [String] OptionName (Key): The name of the SQL configuration option to be checked. For all possible values reference MSDN or run sp_configure.
• [SInt32] OptionValue (Required): The desired value of the SQL configuration option.
• [Boolean] RestartService (Write): Determines whether the instance should be restarted after updating the configuration option.
• [UInt32] RestartTimeout (Write): The length of time, in seconds, to wait for the service to restart. Default is 120 seconds.

Examples

• Configure two instances on the same server to have CLR enabled
• Configure a instance to have 'Priority Boost' enabled

Known issues

All issues are not listed here, see here for all open issues.

SqlServerDatabaseMail

Resource to manage SQL Server Database Mail.

Note: Database Mail XPs can be enabled using the resource SqlServerConfiguration.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.
• Target machine must be running SQL Server Agent.
• Target machine must have enabled Database Mail XPs.

Parameters

• [String] AccountName (Key): The name of the Database Mail account.
• [String] ServerName (Write): The hostname of the SQL Server to be configured. Defaults to $env:COMPUTERNAME.
• [String] InstanceName (Key): Name of the SQL instance to be configured.
• [String] Ensure (Write): Specifies the desired state of the Database Mail. When set to 'Present', the Database Mail will be created. When set to 'Absent', the Database Mail will be removed. Default value is 'Present'. { Present | Absent }.
• [String] ProfileName (Required): The name of the Database Mail profile.
• [String] Description (Write): The description for the Database Mail profile and account.
• [String] EmailAddress (Required): The e-mail address from which mail will originate.
• [String] DisplayName (Write): The display name of the originating e-mail address. Default value is the same value assigned to the EmailAddress parameter.
• [String] ReplyToAddress (Write): The e-mail address to which the receiver of e-mails will reply to. Default value is the same e-mail address assigned to parameter EmailAddress.
• [String] MailServerName (Required): The fully qualified domain name of the mail server name to which e-mail are sent.
• [UInt16] TcpPort (Write): The TCP port used for communication. Default value is port 25.
• [String] LoggingLevel (Write): The logging level that the Database Mail will use. If not specified the default logging level is 'Extended'. { Normal | Extended | Verbose }.

Examples

• Enable Database Mail
• Disable Database Mail

Known issues

All issues are not listed here, see here for all open issues.

SqlServerEndpoint

This resource is used to create an endpoint. Currently it only supports creating a database mirror endpoint which can be used by, for example, AlwaysOn.

Note: The endpoint will be started after creation, but will not be enforced. Please use SqlServerEndpointState to make sure the endpoint remains in started state. To set connect permission to the endpoint, please use the resource SqlServerEndpointPermission.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Security Requirements

• The built-in parameter PsDscRunAsCredential must be set to the credentials of an account with the permission to create and alter endpoints.

Parameters

• [String] EndpointName (Key): The name of the endpoint.
• [String] Ensure (Write): If the endpoint should be present or absent. Default values is 'Present'. { Present | Absent }.
• [Uint16] Port (Write): The network port the endpoint is listening on. Default value is 5022.
• [String] ServerName (Write): The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] IpAddress (Write): The network IP address the endpoint is listening on. Defaults to '0.0.0.0' which means listen on any valid IP address.
• [String] Owner (Write): The owner of the endpoint. Default is the login used for the creation.

Examples

• Create an endpoint with default values
• Create an endpoint with specific port and IP address
• Remove an endpoint

Known issues

All issues are not listed here, see here for all open issues.

SqlServerEndpointPermission

This resource is used to give connect permission to an endpoint for a user (login).

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] ServerName (Required): The host name of the SQL Server to be configured.
• [String] Ensure (Write): If the permission should be present or absent. Default value is 'Present'. { Present | Absent }.
• [String] Name (Required): The name of the endpoint.
• [String] Principal (Key): The login to which permission will be set.
• [String] Permission (Write): The permission to set for the login. Valid value for permission are only CONNECT. { Connect }.

Examples

• Add connect permission to an Endpoint
• Remove the connect permission for an Endpoint
• Add connect permission to both an Always On primary replica and an Always On secondary replica, and where each replica has a different SQL service account
• Remove connect permission to both an Always On primary replica and an Always On secondary replica, and where each replica has a different SQL service account

Known issues

All issues are not listed here, see here for all open issues.

SqlServerEndpointState

This resource is used to set the state of an endpoint.

Note: Currently this resource can only be used with Database Mirror endpoints.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.
• Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module.

Parameters

• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] ServerName (Write): The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
• [String] Name (Key): The name of the endpoint.
• [String] State (Write): The state of the endpoint. Valid states are Started, Stopped or Disabled. Default value is 'Started'. { Started | Stopped | Disabled }.

Examples

• Make sure that an endpoint is started
• Make sure that an endpoint is stopped

Known issues

All issues are not listed here, see here for all open issues.

SqlServerLogin

No description.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.
• When the LoginType 'SqlLogin' is used, then the login authentication mode must have been set to Mixed or Normal. If set to Integrated and error will be thrown.

Parameters

• [String] ServerName (Key):The hostname of the SQL Server to be configured.
• [String] InstanceName (Key): Name of the SQL instance to be configured.
• [String] Name (Key): The name of the login.
• [String] Ensure (Write): The specified login is Present or Absent. { Present | Absent }.
• [PSCredential] LoginCredential (Write): If LoginType is 'SqlLogin' then a PSCredential is needed for the password to the login.
• [String] LoginType (Write): The type of login to be created. If LoginType is 'WindowsUser' or 'WindowsGroup' then provide the name in the format DOMAIN\name. Default is WindowsUser. Unsupported login types are Certificate, AsymmetricKey, ExternalUser, and ExternalGroup. {SqlLogin | WindowsUser | WindowsGroup }
• [Boolean] LoginMustChangePassword (Write): Specifies if the login is required to have its password change on the next login. Only applies to SQL Logins. Default is $true.
• [Boolean] LoginPasswordExpirationEnabled (Write): Specifies if the login password is required to expire in accordance to the operating system security policy. Only applies to SQL Logins. Default is $true.
• [Boolean] LoginPasswordPolicyEnforced (Write): Specifies if the login password is required to conform to the password policy specified in the system security policy. Only applies to SQL Logins. Default is $true.
• [Boolean] Disabled (Write): Specifies if the login is disabled. Default is $false.

Examples

• Add a login
• Remove a login

Known issues

All issues are not listed here, see here for all open issues.

SqlServerMaxDop

This resource set the max degree of parallelism server configuration option. The max degree of parallelism option is used to limit the number of processors to use in parallel plan execution. Read more about max degree of parallelism in this article Configure the max degree of parallelism Server Configuration Option

Formula for dynamically allocating max degree of parallelism

• If the number of configured NUMA nodes configured in SQL Server equals 1, then max degree of parallelism is calculated using number of cores divided in 2 (numberOfCores / 2), then rounded up to the next integer (3.5 > 4).
• If the number of cores configured in SQL Server are greater than or equal to 8 cores then max degree of parallelism will be set to 8.
• If the number of configured NUMA nodes configured in SQL Server is greater than 2 and the number of cores are less than 8 then max degree of parallelism will be set to the number of cores.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] ServerName (Write): The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
• [String] Ensure (Write): When set to 'Present' then max degree of parallelism will be set to either the value in parameter MaxDop or dynamically configured when parameter DynamicAlloc is set to $true. When set to 'Absent' max degree of parallelism will be set to 0 which means no limit in number of processors used in parallel plan execution. { Present | Absent }.
• [Boolean] DynamicAlloc (Write): If set to $true then max degree of parallelism will be dynamically configured. When this is set parameter is set to $true, the parameter MaxDop must be set to $null or not be configured.
• [SInt32] MaxDop (Write): A numeric value to limit the number of processors used in parallel plan execution.
• [Boolean] ProcessOnlyOnActiveNode (Write): Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server instance.

Read-Only Property from Get-TargetResource

• [Boolean] IsActiveNode (Read): Determines if the current node is actively hosting the SQL Server instance.

Examples

• Set SQLServerMaxDop to 1
• Set SQLServerMaxDop to Auto
• Set SQLServerMaxDop to Default

Known issues

All issues are not listed here, see here for all open issues.

SqlServerMemory

This resource sets the minimum server memory and maximum server memory configuration option. That means it sets the minimum and the maximum amount of memory, in MB, in the buffer pool used by the instance of SQL Server The default setting for minimum server memory is 0, and the default setting for maximum server memory is 2147483647 MB. Read more about minimum server memory and maximum server memory in this article Server Memory Server Configuration Options.

Formula for dynamically allocating maximum memory

The formula is based on the SQL Max Memory Calculator website. The website code is in the sql-max GitHub repository maintained by @mirontoli. The dynamic maximum memory (in MB) is calculate with this formula: SQL Max Memory = TotalPhysicalMemory - (NumOfSQLThreads*ThreadStackSize) - (1024*CEILING(NumOfCores/4)) - OSReservedMemory.

NumOfSQLThreads

• If the number of cores is less than or equal to 4, the number of SQL threads is set to: 256 + (NumberOfCores - 4) * 8.
• If the number of cores is greater than 4, the number of SQL threads is set to: 0 (zero).

ThreadStackSize

• If the architecture of windows server is x86, the size of thread stack is 1MB.
• If the architecture of windows server is x64, the size of thread stack is 2MB.
• If the architecture of windows server is IA64, the size of thread stack is 4MB.

OSReservedMemory

• If the total physical memory is less than or equal to 20GB, the percentage of reserved memory for OS is 20% of total physical memory.
• If the total physical memory is greater than 20GB, the percentage of reserved memory for OS is 12.5% of total physical memory.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] ServerName (Write): The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
• [Boolean] DynamicAlloc (Write): If set to $true then max memory will be dynamically configured. When this is set parameter is set to $true, the parameter MaxMemory must be set to $null or not be configured. Default value is $false.
• [String] Ensure (Write): When set to 'Present' then min and max memory will be set to either the value in parameter MinMemory and MaxMemory or dynamically configured when parameter DynamicAlloc is set to $true. When set to 'Absent' min and max memory will be set to default values. { Present | Absent }.
• [SInt32] MinMemory (Write): Minimum amount of memory, in MB, in the buffer pool used by the instance of SQL Server.
• [SInt32] MaxMemory (Write): Maximum amount of memory, in MB, in the buffer pool used by the instance of SQL Server.
• [Boolean] ProcessOnlyOnActiveNode (Write): Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server instance.

Read-Only Properties from Get-TargetResource

• [Boolean] IsActiveNode (Read): Determines if the current node is actively hosting the SQL Server instance.

Examples

• Set SQLServerMaxMemory to 12GB
• Set SQLServerMaxMemory to Auto
• Set SQLServerMinMemory to 2GB and SQLServerMaxMemory to Auto
• Set SQLServerMaxMemory to Default

Known issues

All issues are not listed here, see here for all open issues.

SqlServerNetwork

This resource is used to change the network settings for the instance.

Read more about the network settings in the article TCP/IP Properties (IP Addresses Tab).

Note: Currently only TCP is supported.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] ProtocolName (Required): The name of network protocol to be configured. Only tcp is currently supported. { tcp }.
• [String] ServerName (Write): The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
• [Boolean] IsEnabled (Write): Enables or disables the network protocol.
• [Boolean] TcpDynamicPort (Write): Specifies whether the SQL Server instance should use a dynamic port. Value cannot be set to $true if TcpPort is set to a non-empty string.
• [String] TcpPort (Write): The TCP port(s) that SQL Server should be listening on. If the IP address should listen on more than one port, list all ports separated with a comma ('1433,1500,1501'). To use this parameter set TcpDynamicPort to $false.
• [Boolean] RestartService (Write): If set to $true then SQL Server and dependent services will be restarted if a change to the configuration is made. The default value is $false.
• [Uint16] RestartTimeout (Write): Timeout value for restarting the SQL Server services. The default value is 120 seconds.

Examples

• Enable TCP/IP with static port and restart SQL Server
• Enable TCP/IP with dynamic port

Known issues

All issues are not listed here, see here for all open issues.

SqlServerPermission

This resource sets server permissions to a user (login).

Note: Currently the resource only supports ConnectSql, AlterAnyAvailabilityGroup, AlterAnyEndPoint and ViewServerState.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.
• Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module.

Parameters

• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] Principal (Key): The login to which permission will be set.
• [String] Ensure (Write): If the permission should be present or absent. Default value is 'Present'. { Present | Absent }.
• [String] ServerName (Write): The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
• [String[]] Permission (Write): The permission to set for the login. Valid values are ConnectSql, AlterAnyAvailabilityGroup, ViewServerState or AlterAnyEndPoint. { ConnectSql, AlterAnyAvailabilityGroup | AlterAnyEndPoint | ViewServerState }.

Examples

• Add server permission for a login
• Remove server permission for a login

Known issues

All issues are not listed here, see here for all open issues.

SqlServerReplication

This resource manage SQL Replication distribution and publishing.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server 2008 or later.

Parameters

• [String] InstanceName (Key): SQL Server instance name where replication distribution will be configured.
• [String] Ensure (Write): (Default = 'Present') 'Present' will configure replication, 'Absent' will disable replication.
• [String] DistributorMode (Required): 'Local' - Instance will be configured as it's own distributor, 'Remote' - Instance will be configure with remote distributor (remote distributor needs to be already configured for distribution).
• [PSCredential] AdminLinkCredentials (Required): - AdminLink password to be used when setting up publisher distributor relationship.
• [String] DistributionDBName (Write): (Default = 'distribution') distribution database name. If DistributionMode='Local' this will be created, if 'Remote' needs to match distribution database on remote distributor.
• [String] RemoteDistributor (Write): (Required if DistributionMode='Remote') SQL Server network name that will be used as distributor for local instance.
• [String] WorkingDirectory (Required): Publisher working directory.
• [Boolean] UseTrustedConnection (Write): (Default = $true) Publisher security mode.
• [Boolean] UninstallWithForce (Write): (Default = $true) Force flag for uninstall procedure

Examples

• Configure a instance as the distributor
• Configure a instance as the publisher

Known issues

All issues are not listed here, see here for all open issues.

SqlServerRole

This resource is used to create a server role, when Ensure is set to 'Present'. Or remove a server role, when Ensure is set to 'Absent'. The resource also manages members in both built-in and user created server roles. For more information about server roles, please read the below articles.

• Create a Server Role
• Server-Level Roles

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] ServerRoleName (Key): The name of of SQL role to add or remove.
• [String] ServerName (Key): The host name of the SQL Server to be configured.
• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] Ensure (Write): An enumerated value that describes if the server role is added (Present) or dropped (Absent). Default value is 'Present'. { Present | Absent }.
• [String[]] Members (Write): The members the server role should have. This parameter will replace all the current server role members with the specified members.
• [String[]] MembersToInclude (Write): The members the server role should include. This parameter will only add members to a server role. Can not be used at the same time as parameter Members.
• [String[]] MembersToExclude (Write): The members the server role should exclude. This parameter will only remove members from a server role. Can only be used when parameter Ensure is set to 'Present'. Can not be used at the same time as parameter Members.

Examples

• Add server role
• Remove server role
• Add members to server role
• Members to include in server role
• Members to exclude from server role

Known issues

All issues are not listed here, see here for all open issues.

SqlServerSecureConnection

Configures SQL connections to be encrypted. Read more about encrypted connections in this article Enable Encrypted Connections.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• You must have a Certificate that is trusted and issued for ServerAuthentication.
• The name of the Certificate must be the fully qualified domain name (FQDN) of the computer.
• The Certificate must be installed in the LocalMachine Personal store.
• If PsDscRunAsCredential common parameter is used to run the resource, the specified credential must have permissions to connect to the SQL Server instance specified in InstanceName.

Parameters

• [String] InstanceName (Key): Name of the SQL Server instance to be configured.
• [String] Thumbprint (Required): Thumbprint of the certificate being used for encryption. If parameter Ensure is set to 'Absent', then the parameter Certificate can be set to an empty string.
• [String] ServiceAccount (Required): Name of the account running the SQL Server service.
• [String] Ensure (Write): If Encryption should be Enabled (Present) or Disabled (Absent). { Present | Absent }. Defaults to Present.
• [Boolean] ForceEncryption (Write): If all connections to the SQL instance should be encrypted. If this parameter is not assigned a value, the default is, set to True, that all connections must be encrypted.

Examples

• Force Secure Connection.
• Secure Connection but not required.
• Secure Connection disabled.

Known issues

All issues are not listed here, see here for all open issues.

SqlServiceAccount

Manage the service account for SQL Server services.

Requirements

• Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module.

Parameters

• [String] ServerName (Key): The host name of the SQL Server to be configured.
• [String] InstanceName (Key): The name of the SQL instance to be configured.
• [String] ServiceType (Key): The service type for InstanceName. { DatabaseEngine | SQLServerAgent | Search | IntegrationServices | AnalysisServices | ReportingServices | SQLServerBrowser | NotificationServices }
• [PSCredential] ServiceAccount (Required): The service account that should be used when running the service.
• [Boolean] RestartService (Write): Determines whether the service is automatically restarted when a change to the configuration was needed.
• [Boolean] Force (Write): Forces the service account to be updated. Useful for password changes. This will cause Set-TargetResource to be run on each consecutive run.
• [String] VersionNumber (Write): The version number of the SQL Server, mandatory for when IntegrationServices is used as ServiceType. Eg. 130 for SQL 2016.

Read-Only Properties from Get-TargetResource

• [String] ServiceAccountName (Read): Returns the service account username for the service.

Examples

• Run service under a user account
• Run service with a virtual account

Known issues

All issues are not listed here, see here for all open issues.

SqlSetup

Installs SQL Server on the target node.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• For configurations that utilize the 'InstallFailoverCluster' action, the following parameters are required (beyond those required for the standalone installation). See the article Install SQL Server from the Command Prompt under the section Failover Cluster Parameters for more information.
  • InstanceName (can be MSSQLSERVER if you want to install a default clustered instance).
  • FailoverClusterNetworkName
  • FailoverClusterIPAddress
  • Additional parameters need when installing Database Engine.
    • InstallSQLDataDir
    • AgtSvcAccount
    • SQLSvcAccount
    • SQLSysAdminAccounts
  • Additional parameters need when installing Analysis Services.
    • ASSysAdminAccounts
    • AsSvcAccount
• The parameters below can only be used when installing SQL Server 2016 or later:
  • SqlTempdbFileCount
  • SqlTempdbFileSize
  • SqlTempdbFileGrowth
  • SqlTempdbLogFileSize
  • SqlTempdbLogFileGrowth

Note: It is not possible to add or remove features to a SQL Server failover cluster. This is a limitation of SQL Server. See article You cannot add or remove features to a SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012 failover cluster.

Feature flags

Feature flags are used to toggle functionality on or off. One or more feature flags can be added to the parameter FeatureFlag, i.e. FeatureFlag = @('DetectionSharedFeatures').

NOTE: The functionality, exposed with a feature flag, can be changed from one release to another, including having breaking changes.

Feature flag	Description
DetectionSharedFeatures	A new way of detecting if the shared features is installed or not. This was implemented because the previous implementation did not work fully with SQL Server 2017.

Credentials for running the resource

PsDscRunAsCredential

If PsDscRunAsCredential is set, the installation will be performed with those credentials, and the user name will be used as the first system administrator.

SYSTEM

If PsDscRunAsCredential is not assigned credentials then installation will be performed by the SYSTEM account. When installing as the SYSTEM account, then parameter SQLSysAdminAccounts and ASSysAdminAccounts must be specified when installing feature Database Engine and Analysis Services respectively.

Credentials for service accounts

Service Accounts

Service account username containing dollar sign ('$') is allowed, but if the dollar sign is at the end of the username it will be considered a Managed Service Account.

Managed Service Accounts

If a service account username has a dollar sign at the end of the name it will be considered a Managed Service Account. Any password passed in the credential object will be ignored, meaning the account is not expected to need a '*SVCPASSWORD' argument in the setup arguments.

Parameters

• [String] Action (Write): The action to be performed. Defaults to 'Install'. Note: AddNode is not currently functional. { Install | InstallFailoverCluster | AddNode | PrepareFailoverCluster | CompleteFailoverCluster }
• [String] InstanceName (Key): SQL instance to be installed.
• [String] SourcePath (Write): The path to the root of the source files for installation. I.e and UNC path to a shared resource. Environment variables can be used in the path.
• [PSCredential] SourceCredential (Write): Credentials used to access the path set in the parameter SourcePath. Using this parameter will trigger a copy of the installation media to a temp folder on the target node. Setup will then be started from the temp folder on the target node. For any subsequent calls to the resource, the parameter SourceCredential is used to evaluate what major version the file 'setup.exe' has in the path set, again, by the parameter SourcePath. To know how the temp folder is evaluated please read the online documentation for System.IO.Path.GetTempPath(). If the path, that is assigned to parameter SourcePath, contains a leaf folder, for example '\server\share\folder', then that leaf folder will be used as the name of the temporary folder. If the path, that is assigned to parameter SourcePath, does not have a leaf folder, for example '\server\share', then a unique GUID will be used as the name of the temporary folder.
• [Boolean] SuppressReboot (Write): Suppresses reboot.
• [Boolean] ForceReboot (Write): Forces reboot.
• [String] Features (Write): SQL features to be installed.
• [String] InstanceID (Write): SQL instance ID, if different from InstanceName.
• [String] ProductKey (Write): Product key for licensed installations.
• [String] UpdateEnabled (Write): Enabled updates during installation.
• [String] UpdateSource (Write): Path to the source of updates to be applied during installation.
• [String] SQMReporting (Write): Enable customer experience reporting.
• [String] ErrorReporting (Write): Enable error reporting.
• [String] InstallSharedDir (Write): Installation path for shared SQL files.
• [String] InstallSharedWOWDir (Write): Installation path for x86 shared SQL files.
• [String] InstanceDir (Write): Installation path for SQL instance files.
• [PSCredential] SQLSvcAccount (Write): Service account for the SQL service.
• [PSCredential] AgtSvcAccount (Write): Service account for the SQL Agent service.
• [String] SQLCollation (Write): Collation for SQL.
• [String[]] SQLSysAdminAccounts (Write): Array of accounts to be made SQL administrators.
• [String] SecurityMode (Write): Security mode to apply to the SQL Server instance. 'SQL' indicates mixed-mode authentication while 'Windows' indicates Windows authentication. Default is Windows. { Windows | SQL }
• [PSCredential] SAPwd (Write): SA password, if SecurityMode is set to 'SQL'.
• [String] InstallSQLDataDir (Write): Root path for SQL database files.
• [String] SQLUserDBDir (Write): Path for SQL database files.
• [String] SQLUserDBLogDir (Write): Path for SQL log files.
• [String] SQLTempDBDir (Write): Path for SQL TempDB files.
• [String] SQLTempDBLogDir (Write): Path for SQL TempDB log files.
• [String] SQLBackupDir (Write): Path for SQL backup files.
• [PSCredential] FTSvcAccount (Write): Service account for the Full Text service.
• [PSCredential] RSSvcAccount (Write): Service account for Reporting Services service.
• [PSCredential] ASSvcAccount (Write): Service account for Analysis Services service.
• [String] ASCollation (Write): Collation for Analysis Services.
• [String[]] ASSysAdminAccounts (Write): Array of accounts to be made Analysis Services admins.
• [String] ASDataDir (Write): Path for Analysis Services data files.
• [String] ASLogDir (Write): Path for Analysis Services log files.
• [String] ASBackupDir (Write): Path for Analysis Services backup files.
• [String] ASTempDir (Write): Path for Analysis Services temp files.
• [String] ASConfigDir (Write): Path for Analysis Services config.
• [String] ASServerMode (Write): The server mode for SQL Server Analysis Services instance. The default is to install in Multidimensional mode. Valid values in a cluster scenario are MULTIDIMENSIONAL or TABULAR. Parameter ASServerMode is case-sensitive. All values must be expressed in upper case. { MULTIDIMENSIONAL | TABULAR | POWERPIVOT }.
• [PSCredential] ISSvcAccount (Write): Service account for Integration Services service.
• [String] SqlSvcStartupType (Write): Specifies the startup mode for SQL Server Engine service. { Automatic | Disabled | Manual }
• [String] AgtSvcStartupType (Write): Specifies the startup mode for SQL Server Agent service. { Automatic | Disabled | Manual }
• [String] AsSvcStartupType (Write): Specifies the startup mode for SQL Server Analysis service. { Automatic | Disabled | Manual }
• [String] IsSvcStartupType (Write): Specifies the startup mode for SQL Server Integration service. { Automatic | Disabled | Manual }
• [String] RsSvcStartupType (Write): Specifies the startup mode for SQL Server Report service. { Automatic | Disabled | Manual }
• [String] BrowserSvcStartupType (Write): Specifies the startup mode for SQL Server Browser service. { Automatic | Disabled | Manual }
• [String] FailoverClusterGroupName (Write): The name of the resource group to create for the clustered SQL Server instance. Default is 'SQL Server (InstanceName)'.
• [String[]] FailoverClusterIPAddress (Write): Array of IP Addresses to be assigned to the clustered SQL Server instance. IP addresses must be in dotted-decimal notation, for example 10.0.0.100. If no IP address is specified, uses 'DEFAULT' for this setup parameter.
• [String] FailoverClusterNetworkName (Write): Host name to be assigned to the clustered SQL Server instance.
• [UInt32] SqlTempdbFileCount (Write): Specifies the number of tempdb data files to be added by setup.
• [UInt32] SqlTempdbFileSize (Write): Specifies the initial size of each tempdb data file in MB.
• [UInt32] SqlTempdbFileGrowth (Write): Specifies the file growth increment of each tempdb data file in MB.
• [UInt32] SqlTempdbLogFileSize (Write): Specifies the initial size of each tempdb log file in MB.
• [UInt32] SqlTempdbLogFileGrowth (Write): Specifies the file growth increment of each tempdb data file in MB.
• [UInt32] SetupProcessTimeout (Write): The timeout, in seconds, to wait for the setup process to finish. Default value is 7200 seconds (2 hours). If the setup process does not finish before this time, and error will be thrown.
• [String[]] FeatureFlag (Write): Feature flags are used to toggle functionality on or off. See the documentation for what additional functionality exist through a feature flag.

Read-Only Properties from Get-TargetResource

• [String] SQLSvcAccountUsername (Read): Output user name for the SQL service.
• [String] AgtSvcAccountUsername (Read): Output user name for the SQL Agent service.
• [String] FTSvcAccountUsername (Read): Output username for the Full Text service.
• [String] RSSvcAccountUsername (Read): Output username for the Reporting Services service.
• [String] ASSvcAccountUsername (Read): Output username for the Analysis Services service.
• [String] ISSvcAccountUsername (Read): Output user name for the Integration Services service.

Examples

• Install a default instance on a single server
• Install a named instance on a single server
• Install a named instance on a single server from an UNC path using SourceCredential
• Install a named instance as the first node in SQL Server Failover Cluster
• Install a named instance as the second node in SQL Server Failover Cluster
• Install a named instance with the Agent Service set to Disabled
• Install a default instance on a single server (Sql Server 2016 or Later)

Known issues

All issues are not listed here, see here for all open issues.

SqlWaitForAG

This resource will wait for a cluster role/group to be created. This is used to wait for an Availability Group to create the cluster role/group in the cluster.

Note: This only evaluates if the cluster role/group has been created and when it found it will wait for RetryIntervalSec a last time before returning. There is currently no check to validate that the Availability Group was successfully created or that it has finished creating the Availability Group.

Requirements

• Target machine must be running Windows Server 2008 R2 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.
• Target machine must have access to the Failover Cluster PowerShell module.

Security Requirements

• The account running this resource must have permission in the cluster to be able to run the cmdlet Get-ClusterGroup.

Parameters

• [String] Name (Key): Name of the cluster role/group to look for (normally the same as the Availability Group name).
• [Uint64] RetryIntervalSec (Write): The interval, in seconds, to check for the presence of the cluster role/group. Default value is 20 seconds. When the cluster role/group has been found the resource will wait for this amount of time once more before returning.
• [UInt32] RetryCount (Write): Maximum number of retries until the resource will timeout and throw an error. Default value is 30 times.

Read-Only Properties from Get-TargetResource

• [Boolean] GroupExist (Read): Returns $true if the cluster role/group exist, otherwise it returns $false. Used by Get-TargetResource.

Examples

• Wait for a cluster role/group to be available
• Wait for multiple cluster roles/groups to be available

Known issues

All issues are not listed here, see here for all open issues.

SqlWindowsFirewall

This will set default firewall rules for the supported features. Currently the features supported are Database Engine, Analysis Services, SQL Browser, SQL Reporting Services and Integration Services.

Firewall rules

Database Engine (SQLENGINE) - Default instance

Firewall Rule	Firewall Display Name
Application: sqlservr.exe	SQL Server Database Engine instance MSSQLSERVER
Service: SQLBrowser	SQL Server Browser

Database Engine (SQLENGINE) - Named instance

Firewall Rule	Firewall Display Name
Application: sqlservr.exe	SQL Server Database Engine instance <INSTANCE>
Service: SQLBrowser	SQL Server Browser

Analysis Services (AS) - Default instance

Firewall Rule	Firewall Display Name
Service: MSSQLServerOLAPService	SQL Server Analysis Services instance MSSQLSERVER
Service: SQLBrowser	SQL Server Browser

Analysis Services (AS) - Named instance

Firewall Rule	Firewall Display Name
Service: MSOLAP$<INSTANCE>	SQL Server Analysis Services instance <INSTANCE>
Service: SQLBrowser	SQL Server Browser

Reporting Services (RS)

Firewall Rule	Firewall Display Name
Port: tcp/80	SQL Server Reporting Services 80
Port: tcp/443	SQL Server Reporting Services 443

Integration Services (IS)

Firewall Rule	Firewall Display Name
Application: MsDtsSrvr.exe	SQL Server Integration Services Application
Port: tcp/135	SQL Server Integration Services Port

Requirements

• Target machine must be running Windows Server 2008 R2 or later.

Parameters

• [String] Features (Key): SQL features to enable firewall rules for.
• [String] InstanceName (Key): SQL instance to enable firewall rules for.
• [String] Ensure (Write): Ensures that SQL firewall rules are Present or Absent on the machine. { Present | Absent }.
• [String] SourcePath (Write): UNC path to the root of the source files for installation.
• [String] SourceCredential (Write): Credentials used to access the path set in the parameter 'SourcePath'. This parameter is optional either if built-in parameter 'PsDscRunAsCredential' is used, or if the source path can be access using the SYSTEM account.

Read-Only Properties from Get-TargetResource

• [Boolean] DatabaseEngineFirewall (Read): Is the firewall rule for the Database Engine enabled?
• [Boolean] BrowserFirewall (Read): Is the firewall rule for the Browser enabled?
• [Boolean] ReportingServicesFirewall (Read): Is the firewall rule for Reporting Services enabled?
• [Boolean] AnalysisServicesFirewall (Read): Is the firewall rule for Analysis Services enabled?
• [Boolean] IntegrationServicesFirewall (Read): Is the firewall rule for the Integration Services enabled?

Examples

• Create inbound firewall rules
• Remove inbound firewall rules

Known issues

All issues are not listed here, see here for all open issues.