Configure SQL Mirroring

There are 3 different types of SQL Mirroring options available for use:

1. High Performance – Asynchronous (this requires the Enterprise Edition)
2. High Safety without Automatic Failover
3. High Safety with Automatic Failover (this requires a witness server running the same SQL version)

All three require roughly the same steps:
* Ensure the same login accounts are present on the Principal and the Mirror
* Backup the Principal DB and Logs
* Restore on the Mirror
* Configure the Mirroring Endpoints
* Set the partner on the Mirror
* Set the partner on the Principal (and the witness if necessary)

So lets work through the steps above……

Get our login’s in order

To get our accounts in order between our two servers we can run the following SQL script to give us an output to run on our mirror.

SELECT ‘create login [‘ + p.name + ‘] ‘ + case when p.type in(‘U’,’G’) then ‘from windows ‘ else ” end + ‘with ‘ + case when p.type = ‘S’ then ‘password = ‘ + master.sys.fn_varbintohexstr(l.password_hash) + ‘ hashed, ‘ + ‘sid = ‘ + master.sys.fn_varbintohexstr(l.sid) + ‘, check_expiration = ‘ + case when l.is_policy_checked > 0 then ‘ON, ‘ else ‘OFF, ‘ end + ‘check_policy = ‘ + case when l.is_expiration_checked > 0 then ‘ON, ‘ else ‘OFF, ‘ end + case when l.credential_id > 0 then ‘credential = ‘ + c.name + ‘, ‘ else ” end else ” end + ‘default_database = ‘ + p.default_database_name + case when len(p.default_language_name) > 0 then ‘, default_language = ‘ + p.default_language_name else ” end FROM sys.server_principals p LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id LEFT JOIN sys.credentials c ON  l.credential_id = c.credential_id WHERE p.type in(‘S’,’U’,’G’) AND p.name <> ‘sa’ AND p.name NOT LIKE ‘##%’

Once executed run the output on the mirror server to create all the logins.

Backup the Primary and Restore on the mirror

We can run the below script on primary to take a file and log backup of all our user databases

DECLARE @name VARCHAR(4000) — database name DECLARE @path VARCHAR(4000) — path for backup files  DECLARE @fileName VARCHAR(4000) — filename for backup  DECLARE @logfileName VARCHAR(4000) — logfilename for backup  DECLARE @fileDate VARCHAR(20) — used for file name declare @sql nvarchar(4000)     SET @path = ‘C:\SQLbackups\’

SELECT @fileDate = ‘InitialMirror’

DECLARE db_cursor CURSOR FOR  SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)  –WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’INCLUDE YOUR DB’s)’

CREATE TABLE #TEMPRESTORE ( CMD VARCHAR(400) ) OPEN db_cursor   FETCH NEXT FROM db_cursor INTO @name;        WHILE @@FETCH_STATUS = 0   BEGIN          SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’         SET @logfileName = @path + @name + ‘_’ + @fileDate + ‘_Log.BAK’                              SET @sql = ‘ALTER DATABASE “‘ + @name + ‘” SET RECOVERY FULL’         exec sys.sp_executesql @sql;

BACKUP DATABASE @name TO DISK = @fileName with format;         BACKUP log @name TO DISK = @logfileName with format;                 INSERT INTO #TEMPRESTORE VALUES (‘RESTORE DATABASE “‘+@name+’”        FROM DISK = ”’+@fileName+”’ WITH NORECOVERY’)        INSERT INTO #TEMPRESTORE VALUES (‘go’)        INSERT INTO #TEMPRESTORE VALUES (‘RESTORE LOG “‘+@name+’”        FROM DISK = ”’+@logfileName+”’ WITH NORECOVERY’)          INSERT INTO #TEMPRESTORE VALUES (‘go’)        FETCH NEXT FROM db_cursor INTO @name   END

CLOSE db_cursor   DEALLOCATE db_cursor SELECT * FROM #TEMPRESTORE DROP TABLE #TEMPRESTORE

The output of this script will provide you with the restore commands to run on your mirror.  Just make sure you copy all the SQL backup files from the location specifed at the top of the script to the exact same location on your mirror.

Configure the mirror endpoints and setting up the mirror

Right click on a database you want to mirror

Configure further databases to mirror

You can either carryout the wizard above again or run the following script on the mirror.
DECLARE @name VARCHAR(4000) — database name  DECLARE @sql nvarchar(4000) DECLARE @mirrorendpoint varchar(4000)
SET @mirrorendpoint =’TCP://primaryserverFQDN:5022′ SET @name = ‘DatabaseNameToMirror’
SET @sql = ‘ALTER DATABASE “‘ + @name + ‘” SET PARTNER = ”’+@mirrorendpoint+”” exec sys.sp_executesql @sql;
then run this on the primary
DECLARE @name VARCHAR(4000) — database name
DECLARE @sql nvarchar(4000)
DECLARE @mirrorendpoint varchar(4000)
DECLARE @witnessendpoint varchar(4000)
SET @mirrorendpoint =’TCP://yourmirrorFQDN:5022′
SET @witnessendpoint =’TCP://yourwitnessFQDN:5022′
SET @name = ‘DatabaseNameToMirror’
SET @sql = ‘ALTER DATABASE “‘ + @name + ‘” SET PARTNER = ”’+@mirrorendpoint+”” exec sys.sp_executesql @sql; SET @sql = ‘ALTER DATABASE “‘ + @name + ‘” SET WITNESS = ”’+@witnessendpoint+”” exec sys.sp_executesql @sql;