| Author | Chrissy LeMaire (@cl), netnerds.net |
| Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Invoke-DbaDbMirroring on GitHub.
Want to see the Bill Of Health for this command? Check out Invoke-DbaDbMirroring.
Creates and configures database mirroring between SQL Server instances with full validation and setup
Creates database mirroring configurations between SQL Server instances, handling the complete end-to-end setup process that would normally require dozens of manual T-SQL commands and careful validation steps. This function eliminates the complexity and potential errors involved in manually configuring database mirroring partnerships.
The function performs comprehensive validation before setup and handles all the technical requirements:
This saves DBAs significant time when setting up high availability solutions and reduces the risk of configuration errors that can cause mirroring setup failures. The function can work with existing backups or create fresh ones as needed.
NOTE: If backup/restore is performed, the backup files will remain on the network share for your records.
Invoke-DbaDbMirroring
[[-Primary] <DbaInstanceParameter>]
[[-PrimarySqlCredential] <PSCredential>]
[-Mirror] <DbaInstanceParameter[]>
[[-MirrorSqlCredential] <PSCredential>]
[[-Witness] <DbaInstanceParameter>]
[[-WitnessSqlCredential] <PSCredential>]
[[-Database] <String[]>]
[[-EndpointEncryption] <String>]
[[-EncryptionAlgorithm] <String>]
[[-SharedPath] <String>]
[[-InputObject] <Database[]>]
[-UseLastBackup]
[-Force]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> $params = @{
>> Primary = 'sql2017a'
>> Mirror = 'sql2017b'
>> MirrorSqlCredential = 'sqladmin'
>> Witness = 'sql2019'
>> Database = 'pubs'
>> SharedPath = '\\nas\sql\share'
>> }
>>
PS C:\> Invoke-DbaDbMirroring @params
Performs a bunch of checks to ensure the pubs database on sql2017a
can be mirrored from sql2017a to sql2017b. Logs in to sql2019 and sql2017a
using Windows credentials and sql2017b using a SQL credential.
Prompts for confirmation for most changes. To avoid confirmation, use -Confirm:$false or
use the syntax in the second example.
PS C:\> $params = @{
>> Primary = 'sql2017a'
>> Mirror = 'sql2017b'
>> MirrorSqlCredential = 'sqladmin'
>> Witness = 'sql2019'
>> Database = 'pubs'
>> SharedPath = '\\nas\sql\share'
>> Force = $true
>> Confirm = $false
>> }
>>
PS C:\> Invoke-DbaDbMirroring @params
Performs a bunch of checks to ensure the pubs database on sql2017a
can be mirrored from sql2017a to sql2017b. Logs in to sql2019 and sql2017a
using Windows credentials and sql2017b using a SQL credential.
Drops existing pubs database on Mirror and restores it with
a fresh backup.
Does all the things in the description, does not prompt for confirmation.
PS C:\> $map = @{ 'database_data' = 'M:\Data\database_data.mdf' 'database_log' = 'L:\Log\database_log.ldf' }
PS C:\> Get-ChildItem \\nas\seed | Restore-DbaDatabase -SqlInstance sql2017b -FileMapping $map -NoRecovery
PS C:\> Get-DbaDatabase -SqlInstance sql2017a -Database pubs | Invoke-DbaDbMirroring -Mirror sql2017b -Confirm:$false
Restores backups from sql2017a to a specific file structure on sql2017b then creates mirror with no prompts for confirmation.
PS C:\> Get-DbaDatabase -SqlInstance sql2017a -Database pubs |
>> Invoke-DbaDbMirroring -Mirror sql2017b -UseLastBackup -Confirm:$false
Mirrors pubs on sql2017a to sql2017b and uses the last full and logs from sql2017a to seed. Doesn't prompt for confirmation.
Specifies the SQL Server instance(s) that will serve as the mirror server(s) in the mirroring partnership.
This is where the mirrored database copies will be created and maintained.
Supports multiple mirror instances for creating mirror partnerships with different servers.
| Alias | |
| Required | True |
| Pipeline | false |
| Default Value |
Specifies the SQL Server instance that will serve as the primary (principal) server in the mirroring partnership.
Use this when setting up mirroring from scratch rather than piping database objects from Get-DbaDatabase.
Must be paired with the Database parameter to identify which databases to mirror.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Alternative credentials for connecting to the primary SQL Server instance.
Required when the current user context doesn't have sufficient permissions on the primary server.
Accepts PowerShell credential objects created with Get-Credential for SQL Authentication or domain accounts.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Alternative credentials for connecting to the mirror SQL Server instance(s).
Required when the current user context doesn't have sufficient permissions on the mirror server.
Accepts PowerShell credential objects created with Get-Credential for SQL Authentication or domain accounts.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the SQL Server instance that will serve as the witness server for automatic failover scenarios.
Optional parameter that enables high safety mode with automatic failover when all three servers can communicate.
Leave empty if you only need high safety mode without automatic failover or high performance mode.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Alternative credentials for connecting to the witness SQL Server instance.
Required when the current user context doesn't have sufficient permissions on the witness server.
Accepts PowerShell credential objects created with Get-Credential for SQL Authentication or domain accounts.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies which database(s) on the primary server to set up for mirroring.
Required when using the Primary parameter instead of piping from Get-DbaDatabase.
Supports multiple database names to set up mirroring for several databases in a single operation.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Controls the encryption requirement for database mirroring endpoints created during setup.
Default is 'Required' which enforces encrypted communication between all mirroring partners.
Use 'Supported' to allow both encrypted and unencrypted connections, or 'Disabled' to prevent encryption.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | Required |
| Accepted Values | Disabled,Required,Supported |
Specifies the encryption algorithm used by database mirroring endpoints for secure communication.
Default is 'Aes' which provides strong encryption with good performance.
Consider 'AesRC4' or 'RC4Aes' for compatibility with older SQL Server versions in mixed environments.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | Aes |
| Accepted Values | Aes,AesRC4,None,RC4,RC4Aes |
Network share path accessible by all SQL Server service accounts for backup and restore operations.
Required when the mirror database doesn't exist and needs to be initialized from backups.
Must have read/write permissions for the service accounts running SQL Server on primary and mirror instances.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Accepts database objects piped from Get-DbaDatabase to set up mirroring for specific databases.
Use this approach when you want to filter databases first or work with existing database objects.
Alternative to using the Primary and Database parameters.
| Alias | |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
Uses the most recent full and log backups from the primary server to initialize the mirror database.
Avoids creating new backups when recent ones already exist and are sufficient for mirroring setup.
Requires the primary database to be in Full recovery model with existing backup history.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Drops and recreates the mirror database even if it already exists, using fresh backups from the primary.
Use this when you need to completely reinitialize mirroring or when the existing mirror database is corrupted.
Requires either SharedPath for new backups or UseLastBackup to use existing ones.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Shows what would happen if the command were to run. No actions are actually performed.
| Alias | wi |
| Required | False |
| Pipeline | false |
| Default Value |
Prompts you for confirmation before executing any changing operations within the command.
| Alias | cf |
| Required | False |
| Pipeline | false |
| Default Value |