Setup Database Mirroring using Windows Authentication (T-SQL)

Generally, database mirroring involves two SQL Server instances, synchronously or asynchronously replicating transaction logs generated on the first server, known as Principal Server, to the second server, known as Mirror server and replay them on the Mirror server; also we can configure a third server, known as Witness server use which mirroring can automatically failover from Principal to Mirror server. For more information, see Database Mirroring Overview in the SQL Server Books Online.

The following blog post will work you through the steps how to setup the a database mirroring session using Windows Authentication (T-SQL) (without Witness configured in this example).

1. Prepare the Principal and Mirror database via running the following T-SQL script.

Note: You need to enable SQLCMD mode in the SQL Server Management Studio (SSMS).

	-- Run the following query under SQLCMD mode
	:SETVAR principal_server "PRINCIPAL"
	:SETVAR mirror_server "MIRROR"   -- On the principal server
	-- Create the principal database
	-- Change the database recovery mode to FULL (required for database mirroring)
	-- Perform the full and log backup of the principal database
	:CONNECT $(principal_server)
	USE master;
	GO
	CREATE DATABASE DBMirroringTest
	ON PRIMARY (
			NAME = 'DBMirroringTest',
			FILENAME = 'E:\Workspace\Databases\DBMirroringTest_data.mdf',
			SIZE = 100MB,
			MAXSIZE = UNLIMITED,
			FILEGROWTH = 100MB
		)
	LOG ON (
			NAME = 'DBMirroringTest_log',
			FILENAME = 'E:\Workspace\Databases\DBMirroringTest_log.mdf',
			SIZE = 100MB,
			MAXSIZE = UNLIMITED,
			FILEGROWTH = 100MB
		);
	GO
	ALTER DATABASE DBMirroringTest SET RECOVERY FULL;
	GO   BACKUP DATABASE DBMirroringTest
	TO DISK = 'E:\Workspace\Backups\DBMirroringTest_full.bak'
	WITH INIT, FORMAT, STATS = 10;
	GO
	BACKUP LOG DBMirroringTest
	TO DISK = 'E:\Workspace\Backups\DBMirroringTest_log.bak'
	WITH INIT, FORMAT, STATS = 10;
	GO   -- On the mirror server
	-- Restore the full and log backups of the principal database
	:CONNECT $(mirror_server)
	USE master;
	GO
	RESTORE DATABASE DBMirroringTest
	FROM DISK = 'E:\Workspace\Backups\DBMirroringTest_full.bak'
	WITH 
		MOVE 'DBMirroringTest'
		TO 'E:\Workspace\Databases\SQL08R2\DBMirroringTest_data.mdf',
		MOVE 'DBMirroringTest_log'
		TO 'E:\Workspace\Databases\SQL08R2\DBMirroringTest_log.ldf',
	REPLACE, NORECOVERY;
	GO   RESTORE LOG DBMirroringTest
	FROM DISK = 'E:\Workspace\Backups\DBMirroringTest_log.bak'
	WITH NORECOVERY, STATS = 10;
	GO

2. Create mirroring endpoint on both Principal and Mirror. Please note that we only need to configure one mirroring endpoint for one single server instance which will server all database mirroring sessions on the server. If you have already had one mirroring endpoint in place, then you do no need to create that again.

	-- Run the following query under SQLCMD mode
	:SETVAR principal_server "PRINCIPAL"
	:SETVAR mirror_server "MIRROR"   -- On the principal server, create an Endpoint that supports all roles
	-- using port 7022
	:CONNECT $(principal_server)
	 CREATE ENDPOINT EndPoint_Mirroring
		STATE = STARTED
		AS TCP (LISTENER_PORT = 7022)
		FOR DATABASE_MIRRORING ( ROLE = ALL);
	GO   :CONNECT $(mirror_server)
	-- On the mirror server, create an Endpoint that supports all roles
	-- using port 7023
	 CREATE ENDPOINT EndPoint_Mirroring
		STATE = STARTED
		AS TCP (LISTENER_PORT = 7023)
		FOR DATABASE_MIRRORING ( ROLE = ALL);
	GO   -- On the mirror server, set the principal server as partner
	:CONNECT $(mirror_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest
		SET PARTNER = 'TCP://PRINCIPAL:7022';
	GO   -- On the principal server, set the principal server as partner
	:CONNECT $(principal_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest
		SET PARTNER = 'TCP://PRINCIPAL:7023';
	GO

3. Now, after step 2, the database mirroring should be set up, you can check the mirroring database in the Object Explorer of SSMS; or running the following T-SQL script in either server:

	SELECT 
		d.name,
		dm.mirroring_state_desc,
		dm.mirroring_role_desc,
		dm.mirroring_partner_instance,
		dm.mirroring_witness_state_desc
	FROM 
		sys.database_mirroring dm 
		JOIN sys.databases d 
		ON (dm.database_id=d.database_id) 
	WHERE 
		mirroring_guid IS NOT NULL

4. Manually failover test via the following T-SQL script:

	-- Run the following under SQLCMD model   :SETVAR mirror_server "MIRROR"   -- Connect to principal server
	:CONNECT $(mirror_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest SET PARTNER FAILOVER;
	GO   :SETVAR principal_server "PRINCIPAL"   -- Connect to principal server
	:CONNECT $(principal_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest SET PARTNER FAILOVER;
	GO

5. Remove the mirroring session via the following T-SQL script:

	-- Run the following query under SQLCMD mode
	:SETVAR principal_server "PRINCIPAL"
	:SETVAR mirror_server "MIRROR"   -- Connec to either server, in this case using the principal server
	:CONNECT $(principal_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest SET PARTNER OFF;
	GO
	DROP DATABASE DBMirroringTest;
	GO
	DROP ENDPOINT EndPoint_Mirroring;
	GO   -- Connect to the mirror server
	:CONNECT $(mirror_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest WITH RECOVERY;
	GO
	DROP DATABASE DBMirroringTest;
	GO
	DROP ENDPOINT EndPoint_Mirroring;
	GO

Update: I forgot to mention that we need to make sure that the each server’s service account must be a login in other’s as a login, and have CONNECT permission on the mirroring endpoint. If the SQL Server is running under the Local System or Network Service built-in Windows account, please add the Computer account (Domain\MYCOMPUTER$) of each server instead. For more information, see my another blog post: https://ifcsong.wordpress.com/2011/05/23/configuring-sql-server-service-account-security-for-network-resources-access/.

I will talk about how to setup the database mirroring session using Certificates (T-SQL) in a future blog post.

Hope this helps 🙂

Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Database Mirroring and tagged . Bookmark the permalink.

2 Responses to Setup Database Mirroring using Windows Authentication (T-SQL)

  1. Pingback: FAQs: Database backup and restore for a mirrored database « Alex Feng's Weblog

  2. herring1952 says:

    Hi Alex,

    Excellent article on SQL Server mirroring.

    I have a few questions.

    After a failover occurs and the secondary server becomes the principal server, what are the steps to make the original principal server the primary server again?
    Is it necessary to step the SQL Server processes and configure the mirroring, witness and secondary servers/databases again?
    We had updates applied to our primary server and the secondary server has now become the principal after the principal server was rebooted.

    Thank you very much,
    Mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s