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