SqlAGDatabases.adoc

DSC Resource 'SqlAGDatabases'

SqlAGDatabases adds or removes databases from SQL Server Always On Availability Groups.

When to use 'SqlAGDatabases'

Use this resource when you need to protect databases with Always On by adding them to an Availability Group, or to remove them. The Availability Group must already be configured using SqlAGs. Databases must meet AG prerequisites (full recovery model, full backup taken).

When a replica has Automatic seeding on Automatic, no restore is use for that replica. When all replicas are on automatic seeding, no backup is made, unless the database has never been backuped.

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

Requirements

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

Table 1. Attributes of category 'SqlAGDatabases'

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

Table 2. Attributes of category 'SqlAGDatabases/Values'

Parameter	Attribute	DataType	Description	Allowed Values
DatabaseName	Mandatory	String[]	The name of the database(s) to add to the availability group. This accepts wildcards.
InstanceName	Key	String	Name of the SQL Server 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.
ServerName	Key	String	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.
AvailabilityGroupName	Key	String	The name of the availability group in which to manage the database membership(s).
BackupPath	Mandatory	String	The path used to seed the availability group replicas. This should be a path that is accessible by all of the replicas.
Ensure		String	Determines whether the availability group database membership should be added (Present) or removed (Absent).	Present (default) Absent
Force		Bool	When parameter Ensure is set to 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 parameter Ensure is set to Absent.	True False
MatchDatabaseOwner		Bool	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 any login, control server, impersonate login, or control login permissions. If set to False, the owner of the database will be the username specified in PsDscRunAsCredential.	True False (default)
ReplaceExisting		Bool	If set to True, this adds the restore option WITH REPLACE. If set to False, existing databases and files will block the restore and throw error.	True False (default)
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
IsActiveNode		Bool	Returns if the current node is actively hosting the SQL Server instance.	True False

Example

SqlAGDatabases:
  Values:
    - DatabaseName: DB1
      AvailabilityGroupName: AGTest1
      BackupPath: \\Server1\Backup
      InstanceName: MSSQLSERVER
      ServerName: SQL1
      ProcessOnlyOnActiveNode: true
    - DatabaseName: DB2
      AvailabilityGroupName: AGTest2
      BackupPath: \\Server1\Backup
      InstanceName: MSSQLSERVER
      ServerName: SQL1
      ProcessOnlyOnActiveNode: true

See also

• SqlAGs

• SqlAGReplicas

• SqlAGListeners

• SqlDatabases