How to Set up DB Mirroring using T-SQL

Depending on requirements of the solution; we use asynchronous mode for performance reasons (which excludes the two-phase commit), and we use synchronous mode for data protection reasons. The below how to article will step you through the setup of both of these SQL mirroring solutions using T-SQL only.

Note; when using the GUI to establish mirroring SQL Server will complain if trying to establish a mirror between two different editions of SQL Server (ie Standard vs Enterprise). Configuring this via T-SQL gets around this problem.

Create initial database on mirror server

The first thing you need to do when setting up Database Mirroring is perform a full backup followed by a transaction log backup on the principal server. You then must restore these (and any subsequent log backups which may have occurred) to the mirror server using the WITH NORECOVERY flag set.

Create endpoints on both servers

CREATE ENDPOINT EndPointName 
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL) 
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

Set partner and setup job on mirror server

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'
EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute

Set partner, set synchronous / asynchronous mode, and setup job on principal server

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY OFF -- Asynchronous mode (High Speed)
ALTER DATABASE DatabaseName SET SAFETY ON -- Synchronous mode (High Safety)
EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute