Friday, July 20, 2012

SQL Mirroring Scripts for SQL Server 2008 R2 (SMME)


I have been setting up SQL mirror`s at customer sites for some time now and I have always been annoyed by the fact that you have to mirror each database separately.
One day I decided that I should make some SQL scripts to do just this.
After some time of developing I’ve come up with the SMME (SQL Mirroring Made Easy) Scripts.
Now I have used these scripts a few times in development and production environments and I’m pretty happy with them so I decided to share them with world :-)
If you guys/girls have any suggestion or you find something wrong with the scripts please let me know. So I can make them better for you and the rest of the world.

P.S.
I`m not an DBA, just an SQL enthusiast.

Script Usage:

The zip package contains the following files:

- 1. Create End-Points\SMME_EndPointScript.sql
-- This script creates the mirroring endpoints for the SQL environment.
- 2. Mirror databases\SMME_MirrorScript.sql
-- This script mirrors a single database if an database is already mirrored it will break the mirror
-- and delete the mirror database on the partner server.
- 2. Mirror databases\SMME_MirrorAlldbsScript.sql
-- This script mirrors all databases and can be restricted by the use of an prefix if an database is
-- already mirrored it will break the mirror and delete the mirror database on the partner server.
- 3. Additional actions\SMME_FailOverAllScript.sql
-- This script fails all de mirrored databases over to the partner instance.
- 3. Additional actions\SMME_BreakAllMirrorScript.sql
-- This script breaks all the mirrors and deletes mirror databases on the partner server.

First before we can begin with the mirroring process we have to setup TCP end-points on all the SQL Servers that are going to be a part of the mirror.
In my environment I’ve got the following SQL servers:

- SQLPRINCIPAL
- SQLMIRROR
- SQLWITNESS

And the following file server:

- FILESERVER

With the following share to save the SQL backup files to(This location must be accessible from the Principal and the Mirror servers SQL Agent service account):
\\FILESERVER\SQLBackup$

On all the SQL servers in my environment the SQL Agent is running under the network service account.
To setup the endpoints we need to run the SMME_EndPointScript.sql script on all the SQL Servers and adjust the following settings:


Setting
Explanation
@EndPointPort
The endpoint port number e.g. 7022
@EndPointName
The endpoint name e.g. EndPoint_Mirroring
@SQLAgentPrincipalServiceAccount
The principal SQL Server Agent service account
@SQLAgentMirrorServiceAccount
The mirror SQL Server Agent service account
@SQLAgentWitnessServiceAccount
The witness SQL Server Agent service account
@MirrorRole
The role of the current server where the script is executing


In my environment the settings look like this :

DECLARE @EndPointPort VARCHAR(250) = '7022';
DECLARE @EndPointName VARCHAR(250) = 'EndPoint_Mirroring';
DECLARE @SQLAgentPrincipalServiceAccount VARCHAR(250) = 'MYDOMAIN\SQLPRINCIPAL$';
DECLARE @SQLAgentMirrorServiceAccount VARCHAR(250) = 'MYDOMAIN\SQLMIRROR$';
DECLARE @SQLAgentWitnessServiceAccount VARCHAR(250) = 'MYDOMAIN\SQLWITNESS$';

And the @MirrorRole setting differs per SQL envrionment.
--Principal server
DECLARE @MirrorRole VARCHAR(250) = 'PRINCIPAL';
--Mirror server
DECLARE @MirrorRole VARCHAR(250) = 'MIRROR';
--Witness server
DECLARE @MirrorRole VARCHAR(250) = 'WITNESS';

Once we've ran this script the mirroring endpoints will be created.

Now we can begin with the mirroring of the databases.
For this we are going to use the SMME_MirrorScript.sql script or the SMME_MirrorAlldbsScript.sql script, the difference between these scripts is that the first one will only mirror one database and the second one will mirror all databases.
For the mirror scripts we need to adjust the following settings:


Setting
Explanation
@DBBackupLoc
Set your backup location and make sure that the location is accessible and writable for the SQL Server agent`s
@SQLPrincipal
Set your SQL Principal server end-point
@SQLMirror
Set your SQL Mirror server end-point
@SQLWitness
Set your SQL Witness server end-point. If there is no witness available please set the @WitnessIsAvailible Server variable to 'FALSE'
@WitnessIsAvailible
Set if you have a witness available (TRUE/FALSE)
@MirrorServer
Set your Mirror server name
@ShrinkLogFiles
Set if you want to shrink the log files before mirroring (TRUE/FALSE)
@DBPrefix (Only availible in the SMME_MirrorAlldbsScript.sql script)
Set the database prefix for the databases you want you mirror. Leave empty for all databases.


In my environment the settings look like this :

DECLARE @DBBackupLoc VARCHAR(1000) = '\\FILESERVER\SQLBackup$';
DECLARE @SQLPrincipal VARCHAR(1000) = 'TCP://SQLPRINCIPAL:7022';
DECLARE @SQLMirror VARCHAR(1000) = 'TCP://SQLMIRROR:7022';
DECLARE @SQLWitness VARCHAR(1000) = 'TCP://SQLWITNESS:7022';
DECLARE @WitnessIsAvailible VARCHAR(5) = 'TRUE';
DECLARE @MirrorServer VARCHAR(1000) = 'SQLMIRROR';
DECLARE @ShrinkLogFiles VARCHAR(5) = 'TRUE';

The following setting can only be used when the SMME_MirrorAlldbsScript.sql script is used.
DECLARE @DBPrefix VARCHAR(1000) = '';
This setting can be useful if you`ve got multiple applications in one SQL environment and the databases for the different applications are specified like APP01_Something1, APP01_Something2 and you only want to mirror the databases for that application.

If you are using the SMME_MirrorScript.sql (Single database) script to mirror your database you`ve got to run this script under the database you want to mirror.



If you are using the SMME_MirrorAlldbsScript.sql (Multiple databases) script to mirror your databases you`ve got to run this script under the master database.


And for the additional scripts you don’t have to define any settings just run them under the master database.
One little side note for the SMME_FailOverAllScript.sql script you need to run this on the SQL Server where the databases are active, so if my databases are in principal mode on the mirror server I need to run this script on the Mirror server and vice versa.
Download scripts:
SMME.zip