Invoke-DbaDbMirroring

Synopsis

Creates and configures database mirroring between SQL Server instances with full validation and setup

Description

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:

• Verifies that mirroring is possible between the specified instances and databases
• Sets the recovery model to Full if needed (required for mirroring)
• Creates and restores full and log backups to initialize the mirror database if it doesn’t exist
• Sets up database mirroring endpoints on all participating instances
• Creates logins and grants CONNECT permissions to service accounts on all endpoints
• Starts endpoints if they’re not already running
• Establishes the mirroring partnership between primary and mirror
• Configures witness server if specified for automatic failover scenarios

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.

Syntax

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>]

Examples

Example: 1

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.

Example: 2

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.

Example: 3

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.

Example: 4

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.

Required Parameters

-Mirror

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.

Optional Parameters

-Primary

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.

-PrimarySqlCredential

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.

-MirrorSqlCredential

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.

-Witness

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.

-WitnessSqlCredential

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.

-Database

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.

-EndpointEncryption

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.

-EncryptionAlgorithm

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.

-SharedPath

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.

-InputObject

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.

-UseLastBackup

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.

-Force

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.

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

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.