SqlAGs.adoc

DSC Resource 'SqlAGs'

SqlAGs creates and configures SQL Server Always On Availability Groups.

When to use 'SqlAGs'

Use this resource when you need to create Availability Groups for SQL Server high availability and disaster recovery. AGs provide database-level failover with multiple readable secondary replicas. Enable Always On services first with SqlAlwaysOnServices, and ensure endpoints are configured with SqlEndpoints.

Source	https://github.com/dsccommunity/CommonTasks/tree/main/source/DSCResources/SqlAGs
DSC Resource	SqlServerDsc
Documentation	SqlServerDsc Wiki SqlAG

Requirements

• Target machine must be running Windows Server 2012 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.

Table 1. Attributes of category 'SqlAGs'

Parameter	Attribute	DataType	Description	Allowed Values
DefaultInstanceName		String	Default SQL InstanceName If the InstanceName of a value is not explicitly set, the DefaultInstanceName will be used.	Default: MSSQLSERVER
Values	Mandatory	Hashtable[]	List of SQL availability groups.

Table 2. Attributes of category 'SqlAGs/Values'

Parameter	Attribute	DataType	Description	Allowed Values
Name	Key	String	Specifies the name of the availability group.
InstanceName	Key	String	The name of the SQL Server instance to be configured.
ServerName	Mandatory	String	The host name of the SQL Server to be configured. Default value is the current computer name.
Ensure		String	Determines whether the availability group should be added (Present) or removed (Absent).	Present (default) Absent
AutomatedBackupPreference		String	Specifies the automated backup preference for the availability group. When creating a group the default is None.	Primary SecondaryOnly Secondary None (default)
AvailabilityMode		String	Specifies the replica availability mode. When creating a group the default is AsynchronousCommit.	AsynchronousCommit (default) SynchronousCommit
BackupPriority		UInt32	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. When creating a group the default is 50.	Default: 50
BasicAvailabilityGroup		Bool	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.	True False
DatabaseHealthTrigger		Bool	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.	True False
DtcSupportEnabled		Bool	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 not be altered once the availability group is created and is ignored if it is the case.	True False
ConnectionModeInPrimaryRole		String	Specifies how the availability replica handles connections when in the primary role.	AllowAllConnections AllowReadWriteConnections
ConnectionModeInSecondaryRole		String	Specifies how the availability replica handles connections when in the secondary role.	AllowNoConnections AllowReadIntentConnectionsOnly AllowAllConnections
EndpointHostName		String	Specifies the hostname or IP address of the availability group replica endpoint. When creating a group the default is the instance network name.
FailureConditionLevel		String	Specifies the automatic failover behavior of the availability group.	OnServerDown OnServerUnresponsive OnCriticalServerErrors OnModerateServerErrors OnAnyQualifiedFailureCondition
FailoverMode		String	Specifies the failover mode. When creating a group the default is Manual.	Automatic Manual (default)
HealthCheckTimeout		UInt32	Specifies the length of time, in milliseconds, after which AlwaysOn Availability Groups declare an unresponsive server to be unhealthy. When creating a group the default is 30000.	Default: 30000
ProcessOnlyOnActiveNode		Bool	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.	True False
EndpointUrl		String	Returns the URL of the availability group replica endpoint.
EndpointPort		UInt32	Returns the port the database mirroring endpoint is listening on.
Version		UInt32	Returns the major version of the SQL Server instance.
IsActiveNode		Bool	Returns if the current node is actively hosting the SQL Server instance.	True False

Example

SqlAGs:
  Values:
    - Name: AGTest
      InstanceName: INSTANCE1
      ServerName: SQL1
      AvailabilityMode: SynchronousCommit
      FailoverMode: Automatic
    - Name: AGTest
      InstanceName: INSTANCE2
      ServerName: SQL1
      AvailabilityMode: SynchronousCommit
      FailoverMode: Automatic

See also

• SqlAlwaysOnServices

• SqlEndpoints

• SqlAGReplicas

• SqlAGDatabases

• SqlAGListeners