Invoke-DbaDbMirroring - Option to backup to local backup folder and copy

[cmrichardson]

Invoke-DbaDbMirroring

Summary of new feature

For servers that use Local System for the SQL Server service accounts, it seems that it is not impossible to have a network share that is accessible from both servers.

Invoke-DbaDbMirroring -Primary $PrimaryServer -Mirror $MirrorServer -Database $database -PrimarySqlCredential $PrimarySqlCredential -MirrorSqlCredential $MirrorSqlCredential -SharedPath $SharePath -Confirm:$false -WhatIf

results in:- WARNING: [13:54:16][Invoke-DbaDbMirroring] Cannot access \DBA-SQL02\BackupsForMirroring from DBA-SQL02

Note that the Warning is actually referring to the the mirror server's access to the Share location but, in this case, the Share has been created on the Mirror server.

Note that Windows level connections to the Share (eg. using Test-Path) are fine. The problem only relates to SQL Server connections - due to Local System being used for the SQL Server service accounts.

Note that, if the Shared Path is \DBA-SQL02\BackupsForMirroring, I ran two tests from the Primary Server (DBA-SQL01) using the DBATools cmdlet, Test-DbaPath, and it shows the the connection from the Primary Server (DBA-SQL01) to the Share fails but the connection from the Mirror Server (DBA-SQL02) to the Share succeeds. This is as expected and it would appear the Warning message from Invoke-DbaDbMirroring is not describing the problem accurately.

Proposed technical details

An extra option could be developed to backup the full and transaction log backup locally and then copy those files to a share path. In this case, there will be no issue with getting the backups to the mirror server. Two new Parameters would be required, for example:-

[bool]$LocalBackupAndCopy # could be false by default

[string]$LocalBackupPath #On the primary server and is required if $LocalBackupAndCopy is True

If $LocalBackupAndCopy is True, the Invoke-DbaDbMirroring cmdlet would backup to the local backup path and then copy the full and transaction log backup to the share path specified by the existing SharedPath parameter. From this point, the code would be mostly if not completely unchanged.

Regardless of the issue when Local System is used for SQL Server service accounts, Backing up locally first and then copying is often the best option.

Latest version of dbatools as of writing

1.0.97

[wsmelton]

The need here is to have Invoke-DbaDbMirroring broken out with a command(s) that will allow you to do an offline type configuration.

[cmrichardson]

Look forward to that. :-)

[github-actions[bot]]

🚧🚨 This issue is being marked as stale due to 90 days of inactivity. If you would like this issue to remain open:

• Verify the issue/bug is reproduced in the latest version of the module
• Verify the environmental info provided is still accurate
• Add any additional steps you followed to reproduce if necessary 🚨🚧 ⌛️ This issue will be closed in 30 days ⌛️

[kjustin765]

Would this be a good first issue to assist with? I noticed it was pretty stale so not sure if it still applied.