dbatoolsGet-DbaAgDatabaseReplicaState
View SourceSynopsis
Retrieves the runtime state of databases participating in availability groups across all replicas.
Description
Retrieves comprehensive health monitoring information about databases participating in SQL Server availability groups, similar to the SSMS AG Dashboard. This function returns detailed database replica state information for all replicas in the availability group.
The class Microsoft.SqlServer.Management.Smo.DatabaseReplicaState represents the runtime state of a database that’s participating in an availability group. This database may be located on any of the replicas that compose the availability group.
Use this command to monitor availability group health, troubleshoot synchronization issues, verify failover readiness, identify data loss risks, and generate detailed operational reports showing the state of each database on each replica in your availability groups.
Syntax
Get-DbaAgDatabaseReplicaState
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-AvailabilityGroup] <String[]>]
[[-Database] <String[]>]
[[-InputObject] <AvailabilityGroup[]>]
[-EnableException]
[<CommonParameters>]
Examples
Example: 1
PS C:\> Get-DbaAgDatabaseReplicaState -SqlInstance sql2017a
Returns database replica state information for all databases in all availability groups on sql2017a
Example: 2
PS C:\> Get-DbaAgDatabaseReplicaState -SqlInstance sql2017a -AvailabilityGroup AG101
Returns database replica state information for all databases in the availability group AG101 on sql2017a
Example: 3
PS C:\> Get-DbaAgDatabaseReplicaState -SqlInstance sql2017a -AvailabilityGroup AG101 -Database AppDB
Returns database replica state information for the AppDB database in the availability group AG101 on sql2017a
Example: 4
PS C:\> Get-DbaAvailabilityGroup -SqlInstance sqlcluster -AvailabilityGroup SharePoint | Get-DbaAgDatabaseReplicaState
Returns database replica state information for all databases in the availability group SharePoint on server sqlcluster
Example: 5
PS C:\> Get-DbaAvailabilityGroup -SqlInstance sqlcluster -AvailabilityGroup SharePoint | Get-DbaAgDatabaseReplicaState -Database Sharepoint_Config
Returns database replica state information for the Sharepoint_Config database in the availability group SharePoint on server sqlcluster
Optional Parameters
-SqlInstance
The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-SqlCredential
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-AvailabilityGroup
Specifies which availability groups to query for database replica state information. Accepts multiple availability group names.
Use this to limit results to specific availability groups when you have multiple AGs on the same instance.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-Database
Specifies which availability group databases to return replica state information for. Accepts multiple database names.
Use this to focus on specific databases when troubleshooting AG issues or monitoring particular applications.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-InputObject
Accepts availability group objects from Get-DbaAvailabilityGroup via pipeline input.
Use this when you want to chain commands to get database replica state details from already retrieved availability groups.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
-EnableException
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Outputs
PSCustomObject
Returns one object per database on each replica in the availability group. For example, a database on an AG with two replicas returns two objects - one for the primary and one for the secondary.
Properties returned:
- ComputerName: The computer name of the SQL Server instance (string)
- InstanceName: The SQL Server instance name (string)
- SqlInstance: The full SQL Server instance name (computer\instance format) (string)
- AvailabilityGroup: Name of the availability group (string)
- PrimaryReplica: Server name of the primary replica (string)
- ReplicaServerName: Server name of this replica (string)
- ReplicaRole: Role of this replica - Primary or Secondary (AvailabilityReplicaRole enum)
- ReplicaAvailabilityMode: Availability mode of this replica - Asynchronous or Synchronous (AvailabilityReplicaAvailabilityMode enum)
- ReplicaFailoverMode: Failover mode of this replica - Automatic or Manual (AvailabilityReplicaFailoverMode enum)
- ReplicaConnectionState: Connection state of this replica - Connected, Disconnected, or Failed (ReplicaConnectionState enum)
- ReplicaJoinState: Join state of this replica - Joined or NotJoined (ReplicaJoinState enum)
- ReplicaSynchronizationState: Rollup synchronization state for all databases on this replica (SynchronizationState enum)
- DatabaseName: Name of the database (string)
- SynchronizationState: Database synchronization state on this replica - Synchronized, Synchronizing, NotSynchronizing, Reverting, or Initializing (SynchronizationState enum)
- IsFailoverReady: Boolean indicating if the database is ready for failover (bool)
- IsJoined: Boolean indicating if the database has joined the availability group (bool)
- IsSuspended: Boolean indicating if data movement is suspended for this database (bool)
- SuspendReason: Reason why data movement was suspended - None, UserAction, PartnerSuspended, etc. (SuspendReason enum)
- EstimatedRecoveryTime: Estimated time to recover the database (TimeSpan)
- EstimatedDataLoss: Estimated amount of data loss in case of failover (TimeSpan)
- SynchronizationPerformance: Synchronization performance level - NotApplicable, High, Medium, Low (SynchronizationPerformance enum)
- LogSendQueueSize: Size of the unsent log queue in KB (long)
- LogSendRate: Rate at which log records are being sent in KB/sec (long)
- RedoQueueSize: Size of the redo queue in KB (long)
- RedoRate: Rate at which redo records are being applied in KB/sec (long)
- FileStreamSendRate: Rate at which FILESTREAM records are being sent in KB/sec (long)
- EndOfLogLSN: Log sequence number (LSN) of the end of the log (string)
- RecoveryLSN: LSN for recovery point (string)
- TruncationLSN: LSN for truncation point (string)
- LastCommitLSN: LSN of the last committed transaction (string)
- LastCommitTime: Timestamp when the last transaction was committed (DateTime)
- LastHardenedLSN: LSN that was last hardened to disk (string)
- LastHardenedTime: Timestamp when the last record was hardened to disk (DateTime)
- LastReceivedLSN: LSN of the last received log record (string)
- LastReceivedTime: Timestamp when the last log record was received (DateTime)
- LastRedoneLSN: LSN of the last redo operation (string)
- LastRedoneTime: Timestamp when the last redo operation completed (DateTime)
- LastSentLSN: LSN of the last sent log record (string)
- LastSentTime: Timestamp when the last log record was sent (DateTime)