SQL Server: Setup Mirroring Step by Step using TSQL

It is 2014 and people should be using AlwaysOn instead of mirroring. But some clients are still on SQL Server 2008R2 and I am about to setup mirroring for one such customer in a couple of days and posting these steps here won't hurt. There is a handy GUI wizard that lets you create mirroring pretty easily but if you have to mirror around 30 databases, using scripts is lot easier.

Our setup is simple. SQL01 and SQL02 are our two servers - SQL01 is the principal and SQL02 is the mirror. We are not using a witness for this setup.

Steps on Mirror Server (SQL02)

1. On SQL01, take a full backup and also a transaction log backup of the database you would like to mirror. (So your database would have to be in FULL recovery mode, otherwise no mirroring for you!)

2. Restore database on SQL02

Use master;
RESTORE DATABASE [MirrorMe] FROM DISK = N'K:\Backups\MirrorMe_041214.bak' WITH FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10;
RESTORE LOG [MirrorMe] FROM DISK = N'K:\Backups\MirrorMe_log.trn' WITH FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10;
GO

3. Create an endpoint for mirror instance, SQL02. An endpoint is necessary for database mirroring/alwayson to recieve connections from other servers.

CREATE ENDPOINT [endpoint_SQL02] 
 AUTHORIZATION [BINYOGA\yogirk]
 STATE=STARTED
 AS TCP (LISTENER_PORT = 7024, LISTENER_IP = ALL)
 FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4);
GO

4. Create a login for using in mirroring setup and grant connect on the endpoint.

USE [master]
CREATE LOGIN [BINYOGA\iMirror] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GRANT CONNECT on ENDPOINT::endpoint_SQL02 TO [BINYOGA\iMirror];
GO

5. You might want to create and maintain same set of logins/users on both Principal and Mirror server. When you do a failover, you can simply fix orphaned users and your application can keep talking to the database.

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_expiration_checked > 0 then 'ON, ' else 'OFF, ' end + 
  'check_policy = ' + case when l.is_policy_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 in ('myLogin1','myLogin2' );

6. Add the databases to Mirroring. You'll notice that the endpoint is set to 7023 and that is yet to be created on our Primary server, SQL01.

USE master;
GO
--assuming that you have already restored these databases and at least one transaction log backup with NORECOVERY 
ALTER DATABASE MirrorMe01 SET PARTNER = 'TCP://10.10.2.10:7023';
ALTER DATABASE MirrorMe02 SET PARTNER = 'TCP://10.10.2.10:7023';
ALTER DATABASE MirrorMe03 SET PARTNER = 'TCP://10.10.2.10:7023';
ALTER DATABASE MirrorMe04 SET PARTNER = 'TCP://10.10.2.10:7023';  

Steps on Principal Server (SQL01)

1. Now switch to Primary server, in this case SQL01 and create an endpoint

--you can check existing endpoints, if any using below query
SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints;
SELECT name, port FROM sys.tcp_endpoints;
--create an endpoint

CREATE ENDPOINT [endpoint_SQL01] 
 AUTHORIZATION [BINYOGA\yogirk]
 STATE=STARTED
 AS TCP (LISTENER_PORT = 7023, LISTENER_IP = ALL)
 FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4);
GO

2.Mirror login

USE [master]
CREATE LOGIN [BINYOGA\iMirror] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GRANT CONNECT on ENDPOINT::endpoint_SQL02 TO [BINYOGA\iMirror];
GO

3. Add databases to Mirroring

USE master;
ALTER DATABASE MirrorMe01 SET PARTNER = 'TCP://10.10.2.21:7024';
ALTER DATABASE MirrorMe02 SET PARTNER = 'TCP://10.10.2.21:7024';
ALTER DATABASE MirrorMe03 SET PARTNER = 'TCP://10.10.2.21:7024';
ALTER DATABASE MirrorMe04 SET PARTNER = 'TCP://10.10.2.21:7024'; 
GO

Done, you should now see the databases in mirrored state.