-
Notifications
You must be signed in to change notification settings - Fork 224
SqlAGDatabase
Parameter | Attribute | DataType | Description | Allowed Values |
---|---|---|---|---|
DatabaseName | Required | StringArray[] | The name of the database(s) to add to the availability group. This accepts wildcards. | |
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. | |
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. | |
AvailabilityGroupName | Key | String | The name of the availability group in which to manage the database membership(s). | |
BackupPath | Required | String | The path used to seed the availability group replicas. This should be a path that is accessible by all of the replicas. | |
Ensure | Write | String | Specifies the membership of the database(s) in the availability group. The option 'Present' means that the defined database(s) are added to the availability group. All other databases that may be a member of the availability group are ignored. The option 'Absent' means that 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
|
Force | Write | Boolean | 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' . |
|
MatchDatabaseOwner | Write | Boolean | 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. The default is $false . |
|
ReplaceExisting | Write | Boolean | 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. The default is $false . |
|
ProcessOnlyOnActiveNode | Write | Boolean | 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. | |
StatementTimeout | Write | SInt32 | Set the query timeout in seconds for the backup and restore operations. The default is 600 seconds (10mins). | |
IsActiveNode | Read | Boolean | Returns if the current node is actively hosting the SQL Server instance. |
The SqlAGDatabase
DSC resource is used to add databases or remove
databases from a specified availability group.
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.
- 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.
All issues are not listed here, see here for all open issues.
This example shows how to ensure that the databases 'DB*' and 'AdventureWorks' are members of the Availability Group 'TestAG'.
In the event this is applied to a Failover Cluster Instance (FCI), the ProcessOnlyOnActiveNode property will tell the Test-TargetResource function to evaluate if any changes are needed if the node is actively hosting the SQL Server Instance.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
Node $AllNodes.NodeName
{
SqlAGDatabase 'AddAGDatabaseMemberships'
{
AvailabilityGroupName = 'TestAG'
BackupPath = '\\SQL1\AgInitialize'
DatabaseName = 'DB*', 'AdventureWorks'
InstanceName = 'MSSQLSERVER'
ServerName = $Node.NodeName
Ensure = 'Present'
ProcessOnlyOnActiveNode = $true
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
This example shows how to ensure that the databases 'DB*' and 'AdventureWorks' are not members of the Availability Group 'TestAG'.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
Node $AllNodes.NodeName
{
SqlAGDatabase 'RemoveAGDatabaseMemberships'
{
AvailabilityGroupName = 'TestAG'
BackupPath = '\\SQL1\AgInitialize'
DatabaseName = 'DB*', 'AdventureWorks'
InstanceName = 'MSSQLSERVER'
ServerName = $Node.NodeName
Ensure = 'Absent'
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
This example shows how to ensure that the databases 'DB*' and 'AdventureWorks' are the only members of the Availability Group 'TestAG'.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
Node $AllNodes.NodeName
{
SqlAGDatabase 'TestAGDatabaseMemberships'
{
AvailabilityGroupName = 'TestAG'
BackupPath = '\\SQL1\AgInitialize'
DatabaseName = 'DB*', 'AdventureWorks'
InstanceName = 'MSSQLSERVER'
ServerName = $Node.NodeName
Ensure = 'Present'
Force = $true
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
- Add-SqlDscNode
- Add-SqlDscTraceFlag
- Complete-SqlDscFailoverCluster
- Complete-SqlDscImage
- Connect-SqlDscDatabaseEngine
- ConvertFrom-SqlDscDatabasePermission
- ConvertFrom-SqlDscServerPermission
- ConvertTo-SqlDscDatabasePermission
- ConvertTo-SqlDscServerPermission
- Disable-SqlDscAudit
- Disconnect-SqlDscDatabaseEngine
- Enable-SqlDscAudit
- Get-SqlDscAudit
- Get-SqlDscConfigurationOption
- Get-SqlDscDatabasePermission
- Get-SqlDscManagedComputer
- Get-SqlDscManagedComputerService
- Get-SqlDscPreferredModule
- Get-SqlDscServerPermission
- Get-SqlDscStartupParameter
- Get-SqlDscTraceFlag
- Import-SqlDscPreferredModule
- Initialize-SqlDscRebuildDatabase
- Install-SqlDscServer
- Invoke-SqlDscQuery
- New-SqlDscAudit
- Remove-SqlDscAudit
- Remove-SqlDscNode
- Remove-SqlDscTraceFlag
- Repair-SqlDscServer
- Save-SqlDscSqlServerMediaFile
- Set-SqlDscAudit
- Set-SqlDscDatabasePermission
- Set-SqlDscServerPermission
- Set-SqlDscStartupParameter
- Set-SqlDscTraceFlag
- Test-SqlDscIsDatabasePrincipal
- Test-SqlDscIsLogin
- Test-SqlDscIsSupportedFeature
- Uninstall-SqlDscServer
- SqlAG
- SqlAGDatabase
- SqlAgentAlert
- SqlAgentFailsafe
- SqlAgentOperator
- SqlAGListener
- SqlAGReplica
- SqlAlias
- SqlAlwaysOnService
- SqlAudit
- SqlConfiguration
- SqlDatabase
- SqlDatabaseDefaultLocation
- SqlDatabaseMail
- SqlDatabaseObjectPermission
- SqlDatabasePermission
- SqlDatabaseRole
- SqlDatabaseUser
- SqlEndpoint
- SqlEndpointPermission
- SqlLogin
- SqlMaxDop
- SqlMemory
- SqlPermission
- SqlProtocol
- SqlProtocolTcpIp
- SqlReplication
- SqlRole
- SqlRS
- SqlRSSetup
- SqlScript
- SqlScriptQuery
- SqlSecureConnection
- SqlServiceAccount
- SqlSetup
- SqlTraceFlag
- SqlWaitForAG
- SqlWindowsFirewall