Power Mirrors - Leveraging PowerShell against SQL Server Mirrors

This simple PowerShell script will build a high-performance or high-safety mirror (with witness), when provided with principal and mirror instance names. It will examine the recovery model of the database, and change it to full (when flagged), create a full backup, and a log backup, and then restore to the mirror instance. Finally, given the instance endpoints, it will build the mirror and optionally add the witness.

Prerequisites
Endpoints on all instances to be mirrored
Share accessible by all involved instances
Folder for logging

Limitations
This current version is only good for databases with two database files. While it is easy enough to accommodate for more DB files manually in the script, ideally, the db files would be enumerated and handled within a loop. SQL Server instances should be running the same version, unless this script is being used to migrate upgrades via mirrors.

Configuration
Ideally, mirroring will be established between servers that are consistent in physical storage locations. The locations of the data files, log files, central share and log directory are required for moving the databases. The primary and secondary instances are identified independently in the configuration, to accommodate physical storage differences between the instances. (e.g. Server A's data is on D:\MSSQL\DATA, but Server B's data is on M:\DATA).

Execution
The script accepts parameters or takes defaults, if the primary instance, secondary instace and database name are known and have valid endpoints, execution of Create-Mirror will build a NORECOVERY database on Server B and establish mirroring. A witness may also be provided for high-safety \ auto-failover functionality.

Notes
Be sure to use the SQL Native Client for mirrors

Last edited Oct 10, 2012 at 12:36 AM by edinor, version 1

Comments

No comments yet.