xSQLServer

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

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

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 improvment. 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 the resources or tests, or create a new resource, then please check out the following guidelines.
  • The Contributing to the DSC Resource Kit guidelines.
  • The specific Contributing to xSQLServer guidelines.
  • The common Style Guidelines.
  • The common Best Practices guidelines.
  • 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

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

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

Find-Module -Name xSQLServer -Repository PSGallery | Install-Module

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

Get-DscResource -Module xSQLServer

Requirements

The minimum Windows Management Framework (PowerShell) version required is 4.0, which ships in Windows 8.1 or Windows Server 2012 R2 (or higher versions). But Windows Management Framework (PowerShell) 4.0 can also be installed on Windows Server 2008 R2. The preferred Windows Management Framework (PowerShell) version 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 xSQLServer 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

• xSQLAOGroupEnsure resource to ensure availability group is present or absent
• xSQLAOGroupJoin resource to join a replica to an existing availability group
• xSQLServerAlias resource to manage SQL Server client Aliases
• xSQLServerAlwaysOnAvailabilityGroup resource to ensure an availability group is present or absent.
• xSQLServerAlwaysOnService resource to enable always on on a SQL Server
• xSQLServerAvailabilityGroupListener Create or remove an availability group listener.
• xSQLServerConfiguration resource to manage SQL Server Configuration Options
• xSQLServerDatabase resource to manage ensure database is present or absent
• xSQLServerDatabaseOwner resource to manage SQL database owners
• xSQLServerDatabasePermission resource to manage SQL database permissions
• xSQLServerDatabaseRecoveryModel resource to manage database recovery model
• xSQLServerDatabaseRole resource to manage SQL database roles
• xSQLServerEndpoint resource to ensure database endpoint is present or absent
• xSQLServerEndpointPermission Grant or revoke permission on the endpoint.
• xSQLServerEndpointState Change state of the endpoint.
• xSQLServerFailoverClusterSetup installs SQL Server failover cluster instances.
• xSQLServerFirewall configures firewall settings to allow remote access to a SQL Server instance.
• xSQLServerLogin resource to manage SQL logins
• xSQLServerMaxDop resource to manage MaxDegree of Parallelism for SQL Server
• xSQLServerMemory resource to manage Memory for SQL Server
• xSQLServerNetwork resource to manage SQL Server Network Protocols
• xSQLServerPermission Grant or revoke permission on the SQL Server.
• xSQLServerRole resource to manage SQL server roles
• xSQLServerReplication resource to manage SQL Replication distribution and publishing.
• xSQLServerRSConfig configures SQL Server Reporting Services to use a database engine in another instance.
• xSQLServerRSSecureConnectionLevel sets the secure connection level for SQL Server Reporting Services.
• xSQLServerScript resource to extend DSCs Get/Set/Test functionality to T-SQL
• xSQLServerSetup installs a standalone SQL Server instance
• xWaitForAvailabilityGroup resource to wait till availability group is created on primary server

xSQLAOGroupEnsure

No description.

This resource is deprecated. The functionality of this resource has been replaced with * xSQLServerAlwaysOnAvailabilityGroup. Please do not use this resource for new development efforts.

Requirements

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

Security Requirements

• The credentials provided in the parameter SetupCredential must have the right Create Computer Object in the origanization unit (OU) in which the Cluster Name Object (CNO) resides.

Parameters

• [String] Ensure (Key): Determines whether the availability group should be added or removed. { Present | Absent }.
• [String] AvailabilityGroupName(Key): Name for availability group.
• [String] AvailabilityGroupNameListener (Write): Listener name for availability group.
• [String[]] AvailabilityGroupNameIP (Write): List of IP addresses associated with listener.
• [String[]] AvailabilityGroupSubMask (Write): Network subnetmask for listener.
• [Unint32] AvailabilityGroupPort (Write): Port availability group should listen on.
• [String] ReadableSecondary (Write): Mode secondaries should operate under (None, ReadOnly, ReadIntent). { None | ReadOnly | ReadIntent }.
• [String] AutoBackupPreference (Write): Where backups should be backed up from (Primary, Secondary). { Primary | Secondary }.
• [Uint32] BackupPriority (Write): The percentage weight for backup prority (default 50).
• [Uint32] EndPointPort (Write): The TCP port for the SQL AG Endpoint (default 5022).
• [String] SQLServer (Write): The SQL Server for the database.
• [String] SQLInstance (Write): The SQL instance for the database.
• [PSCredential] SetupCredential (Required): Credential to be used to Grant Permissions on SQL Server, set this to $null to use Windows Authentication.

Examples

None.

xSQLAOGroupJoin

No description.

Requirements

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

Parameters

• [String] Ensure (Key): If the replica should be joined ('Present') to the Availability Group or not joined ('Absent') to the Availability Group. { Present | Absent }.
• [String] AvailabilityGroupName (Key): The name Availability Group to join.
• [String] SQLServer (Write): Name of the SQL server to be configured.
• [String] SQLInstanceName (Write): Name of the SQL instance to be configured.
• [PSCredential] SetupCredential (Required): Credential to be used to Grant Permissions in SQL.

Examples

None.

xSQLServerAlias

No description.

Requirements

• Target machine must be running Windows Server 2008 R2.

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

xSQLServerAlwaysOnAvailabilityGroup

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.
• 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

• Name (Key): The name of the availability group.
• SQLServer (Required): Hostname of the SQL Server to be configured.
• SQLInstanceName (Key): Name of the SQL instance to be configued.
• Ensure (Write): Specifies if the availability group should be present or absent. Default is Present. { Present | Absent }
• AutomatedBackupPreference (Write): Specifies the automated backup preference for the availability group. Default is None. { Primary | SecondaryOnly | Secondary | None }
• AvailabilityMode (Write): Specifies the replica availability mode. Default is 'AsynchronousCommit'. { AsynchronousCommit | SynchronousCommit }
• 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.
• 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.
• ConnectionModeInPrimaryRole (Write): Specifies how the availability replica handles connections when in the primary role. { AllowAllConnections | AllowReadWriteConnections }
• ConnectionModeInSecondaryRole (Write): Specifies how the availability replica handles connections when in the secondary role. { AllowNoConnections | AllowReadIntentConnectionsOnly | AllowAllConnections }
• EndpointHostName (Write): Specifies the hostname or IP address of the availability group replica endpoint. Default is the instance network name.
• FailureConditionLevel (Write): Specifies the automatic failover behavior of the availability group. { OnServerDown | OnServerUnresponsive | OnCriticalServerErrors | OnModerateServerErrors | OnAnyQualifiedFailureCondition }
• FailoverMode (Write): Specifies the failover mode. Default is 'Manual'. { Automatic | Manual }
• HealthCheckTimeout (Write): Specifies the length of time, in milliseconds, after which AlwaysOn availability groups declare an unresponsive server to be unhealthy. Default is 30000.

Examples

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

xSQLServerAlwaysOnService

No description.

Requirements

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

Parameters

• [String] SQLServer (Key): The hostname of the SQL Server to be configured.
• [String] SQLInstance (Key): Name of the SQL instance to be configured.
• [String] Ensure (Required): An enumerated value that describes if SQL server should have AlwaysOn property present or absent. { Present | Absent }.
• [Sint32] RestartTimeout (Write): The length of time, in seconds, to wait for the service to restart. Default is 120 seconds.

Examples

None.

xSQLServerAvailabilityGroupListener

No description.

Requirements

• Target machine must be running Windows Server 2008 R2.
• 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] NodeName (Write): The host name or FQDN of the primary replica.
• [String] Ensure (Write): If the availability group listener should be present or absent. { 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 DCHP, 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

None.

xSQLServerConfiguration

No description.

Requirements

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

Parameters

• [String] SQLServer (Key): The hostname of the SQL Server to be configured.
• [String] SQLInstanceName (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.
• [Sint32] RestartTimeout (Write): The length of time, in seconds, to wait for the service to restart. Default is 120 seconds.

Examples

None.

xSQLServerDatabase

No description.

Requirements

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

Parameters

• [String] SQLServer (Key): The SQL Server for the database
• [String] SQLInstanceName (Key): The SQL instance for the database
• [String] Name (Key): Database to be created or dropped
• [String] Ensure (Write): If the values should be present or absent. Valid values are 'Present' or 'Absent'. Default Value is 'Present'. { Present | Absent }.

Examples

None.

xSQLServerDatabaseOwner

No description.

Requirements

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

Parameters

• [String] Database (Key): The SQL Database
• [String] Name (Required): The name of the SQL login for the owner
• [String] SQLServer (Write): The SQL Server for the database
• [String] SQLInstance (Write): The SQL instance for the database

Examples

• Set database owner

xSQLServerDatabasePermission

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).

Requirements

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

Parameters

• [String] Ensure (Write): If the permission should be granted (Present) or revoked (Absent). { Present | Absent }.
• [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[]] 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] PermissionState (Key): The state of the permission. { Grant | Deny }.
• [String] SQLServer (Key): The host name of the SQL Server to be configured. Default values is 'env:COMPUTERNAME'.
• [String] SQLInstanceName (Key): The name of the SQL instance to be configured. Default value is 'MSSQLSERVER'.

Examples

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

xSQLServerDatabaseRecoveryModel

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.
• Target machine must be running SQL Server Database Engine 2008 or later.

Parameters

• [String] Name (Key): The SQL database name.
• [String] SQLServer (Key): The host name of the SQL Server to be configured.
• [String] SQLInstanceName (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

xSQLServerDatabaseRole

No description.

Requirements

• Target machine must be running Windows Server 2008 R2.
• 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] SQLServer (Key): The SQL server on which the instance exist.
• [String] SQLInstanceName (Key): The SQL instance in which the database exist.
• [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

None.

xSQLServerEndpoint

No description.

Requirements

• Target machine must be running Windows Server 2008 R2.
• 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 enumerate logins, create the endpoint, and alter the permission on an endpoint.

Parameters

• [String] EndPointName (Key): Name for endpoint to be created on SQL Server
• [String] Ensure (Write): An enumerated value that describes if endpoint is to be present or absent on SQL Server. { Present | Absent }.
• [Uint32] Port (Write): Port Endpoint should listen on
• [String] AuthorizedUser (Write): User who should have connect ability to endpoint
• [String] SQLServer (Write): The SQL Server for the database
• [String] SQLInstance (Write): The SQL instance for the database

Examples

None.

xSQLServerEndpointPermission

No description.

Requirements

• Target machine must be running Windows Server 2008 R2.
• 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 SQL Server instance name.
• [String] NodeName (Required): The host name or FQDN.
• [String] Ensure (Write): If the permission should be present or absent. { 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

None.

xSQLServerEndpointState

No description.

Requirements

• Target machine must be running Windows Server 2008 R2.
• 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 SQL Server instance name.
• [String] NodeName (Required): The host name or FQDN.
• [String] Name (Required): The name of the endpoint.
• [String] State (Write): The state of the endpoint. Valid states are Started, Stopped or Disabled. { Started | Stopped | Disabled }.

Examples

None.

xSQLServerFailoverClusterSetup

This resource is deprecated. The functionality of this resource has been merged with xSQLServerSetup. Please do not use this resource for new development efforts.

Requirements

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

Parameters

• [String] Action (Key): Prepare or Complete. { Prepare | Complete }.
• [String] InstanceName (Key): SQL instance to be installed.
• [String] Features (Required): SQL features to be installed.
• [PSCredential] SetupCredential (Required): Credential to be used to perform the installation.
• [String] FailoverClusterNetworkName (Required): Network name for the SQL Server failover cluster.
• [PSCredential] SQLSvcAccount (Required): Service account for the SQL service.
• [String] SourcePath (Write): UNC path to the root of the source files for installation.
• [String] SourceFolder (Write): Folder within the source path containing the source files for installation.
• [PSCredential] SourceCredential (Write): Credential to be used to access SourcePath
• [Boolean] SuppressReboot (Write): Suppresses reboot
• [Boolean] ForceReboot (Write): Forces Reboot
• [String] InstanceID (Write): SQL instance ID, if different from InstanceName.
• [String] PID (Write): Product key for licensed installations.
• [String] UpdateEnabled (Write): Enabled updates during installation.
• [String] UpdateSource (Write): Source of updates to be applied during installation.
• [String] SQMReporting (Write): Enable customer experience reporting.
• [String] ErrorReporting (Write): Enable error reporting.
• [String] FailoverClusterGroup (Write): Name of the resource group to be used for the SQL Server failover cluster.
• [String] FailoverClusterIPAddress (Write): IPv4 address for the SQL Server failover cluster.
• [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] 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): SQL security mode.
• [PSCredential] SAPwd (Write): SA password, if SecurityMode=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] 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.
• [PSCredential] ISSvcAccount (Write): Service account for Integration Services service.
• [String] ISFileSystemFolder (Write): File system folder for Integration Services.

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] ASSvcAccountUsername (Read): Output user name for the Analysis Services service.
• [String] ISSvcAccountUsername (Read): Output user name for the Integration Services service.

Examples

None.

xSQLServerFirewall

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

Default rules for default instance

Feature	Component	Enable Firewall Rule	Firewall Name
SQLENGINE	Database Engine	Application: sqlservr.exe	SQL Server Database Engine instance MSSQLSERVER
SQLENGINE	Database Engine	Service: SQLBrowser	SQL Server Browser
AS	Analysis Services	Service: MSSQLServerOLAPService	SQL Server Analysis Services instance MSSQLSERVER
AS	Analysis Services	Service: SQLBrowser	SQL Server Browser
RS	Reporting Services	Port: tcp/80	SQL Server Reporting Services 80
RS	Reporting Services	Port: tcp/443	SQL Server Reporting Services 443
IS	Integration Services	Application: MsDtsSrvr.exe	SQL Server Integration Services Application
IS	Integration Services	Port: tcp/135	SQL Server Integration Services Port

Default rules for named instance

Feature	Component	Enable Firewall Rule	Firewall Name
SQLENGINE	Database Engine	Application: sqlservr.exe	SQL Server Database Engine instance <NAMED_INSTANCE>
SQLENGINE	Database Engine	Service: SQLBrowser	SQL Server Browser
AS	Analysis Services	Service: MSOLAP$INSTANCE
AS	Analysis Services	Service: SQLBrowser	SQL Server Browser
RS	Reporting Services	Port: tcp/80	SQL Server Reporting Services 80
RS	Reporting Services	Port: tcp/443	SQL Server Reporting Services 443
IS	Integration Services	Application: MsDtsSrvr.exe	SQL Server Integration Services Application
IS	Integration Services	Port: tcp/135	SQL Server Integration Services Port

Requirements

• Target machine must be running Windows Server 2008 R2.

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 parmeter 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

xSQLServerLogin

No description.

Requirements

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

Parameters

• [String] SQLServer (Key):The hostname of the SQL Server to be configured.
• [String] SQLInstanceName (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.

Examples

None.

xSQLServerMaxDop

No description.

Requirements

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

Parameters

• [String] SQLInstance (Key): The SQL instance where to set MaxDop
• [String] Ensure (Write): An enumerated value that describes if Min and Max memory is configured. { Present | Absent }.
• [Boolean] DyamicAlloc (Write): Flag to indicate if MaxDop is dynamically configured
• [Sint32] MaxDop (Write): Numeric value to configure MaxDop to
• [String] SQLServer (Write): The SQL Server where to set MaxDop

Examples

None.

xSQLServerMemory

No description.

Requirements

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

Parameters

• [String] SQLInstance (Key): The SQL instance for the database
• [Boolean] DyamicAlloc (Key): Flag to indicate if Memory is dynamically configured
• [String] Ensure (Write): An enumerated value that describes if Min and Max memory is configured. { Present | Absent }.
• [Sint32] MinMemory (Write): Minimum memory value to set SQL Server memory to
• [Sint32] MaxMemory (Write): Maximum memory value to set SQL Server memory to
• [String] SQLServer (Write): The SQL Server for the database

Examples

None.

xSQLServerNetwork

No description.

Requirements

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

Parameters

• [String] InstanceName (Key): name of SQL Server instance for which network will be configured.
• [String] ProtocolName (Required): Name of network protocol to be configured. Only tcp is currently supported. { tcp }.
• [Boolean] IsEnabled (Write): Enables/Disables network protocol.
• [String] TCPDynamicPorts (Write): 0 if Dynamic ports should be used otherwise empty. { 0 }.
• [String] TCPPort (Write): Custom TCP port.
• [Boolean] RestartService (Write): If true will restart SQL Service instance service after update. Default false.

Examples

None.

xSQLServerPermission

No description.

Requirements

• Target machine must be running Windows Server 2008 R2.
• 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 SQL Server instance name.
• [String] NodeName (Required): The host name or FQDN.
• [String] Principal (Required): The login to which permission will be set.
• [String] Ensure (Write): If the permission should be present or absent. { Present | Absent }.
• [String[]] Permission (Write): The permission to set for the login. Valid values are AlterAnyAvailabilityGroup, ViewServerState or AlterAnyEndPoint. { AlterAnyAvailabilityGroup | AlterAnyEndPoint | ViewServerState }.

Examples

None.

xSQLServerRole

No description.

Requirements

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

Parameters

• [String] SQLInstanceName (Key): SQL Instance for the login
• [String] Name (Key): Name of the SQL Login to create
• [String] SQLServer (Required): SQL Server where login should be created
• [String[]] ServerRole (Required): Type of SQL role to add. { bulkadmin | dbcreator | diskadmin | processadmin | public | securityadmin | serveradmin | setupadmin | sysadmin }.
• [String] Ensure (Write): If the values should be present or absent. Valid values are 'Present' or 'Absent'. { Present | Absent }.

Examples

• Add a server role to a login
• Remove server role from a login

xSQLServerReplication

No description.

Requirements

• Target machine must be running Windows Server 2008 R2.
• 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' - Instace will be configure with remote distributor (remote distributor needs to be already configured for distribution).
• [PSCredentials] 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

None.

xSQLServerRSConfig

No description.

Requirements

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

Parameters

• [String] InstanceName (Key): Name of the SQL Server Reporting Services instance to be configured.
• [String] RSSQLServer (Required): Name of the SQL Server to host the Reporting Service database.
• [String] RSSQLInstanceName (Required): Name of the SQL Server instance to host the Reporting Service database.
• [PSCredential] SQLAdminCredential (Required): Credential to be used to perform the configuration.

Read-Only Properties from Get-TargetResource

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

Examples

None.

xSQLServerRSSecureConnectionLevel

No description.

Requirements

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

Parameters

• [String] InstanceName (Key): SQL instance to set secure connection level for.
• [Uint16] SecureConnectionLevel (Key): SQL Server Reporting Service secure connection level.
• [PSCredential] SQLAdminCredential (Required): Credential with administrative permissions to the SQL instance.

Examples

None.

xSQLServerScript

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.

T-SQL Script	Description
Get	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	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	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.
• 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.
• [String[]] Variable (Write): Specifies, as a string array, a sqlcmd scripting variable for use in the sqlcmd 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

• 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

xSQLServerSetup

Installs SQL Server on the target node.

Requirements

• Target machine must be running Windows Server 2008 R2.

Parameters

• [String] Action (Write): The action to be performed. Defaults to 'Install'. { Install | InstallFailoverCluster | AddNode | PrepareFailoverCluster | CompleteFailoverCluster }
• [String] InstanceName (Key): SQL instance to be installed.
• [PSCredential] SetupCredential (Required): Credential to be used to perform the installation.
• [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.
• [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.
• [PSCredential] ISSvcAccount (Write): Service account for Integration Services service.
• [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. Defaults to '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.

Read-Only Properties from Get-TargetResource

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

Examples

None.

xWaitforAvailabilityGroup

No description.

Requirements

• Target machine must be running Windows Server 2008 R2.

Parameters

• [String] Name (Key): Name for availability group
• [Uint64] RetryIntervalSec (Write): Interval to check for availability group
• [Uint32] RetryCount (Write): Maximum number of retries to check availability group creation

Read-Only Properties from Get-TargetResource

None.

Examples

None.