Example: Setting Up Database Mirroring Using Windows Authentication (Transact-SQL)

This example shows all the stages required to create a database mirroring session with a witness using Windows Authentication. The examples in this topic use Transact-SQL. Note that as an alternative to using Transact-SQL steps, you can use the Configure Database Mirroring Security Wizard for database mirroring setup. For more information, see Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio).

Prerequisite

The example uses the AdventureWorks sample database, which uses the simple recovery model by default. To use database mirroring with this database, you must alter it to use the full recovery model. To do this in Transact-SQL, use the ALTER DATABASE statement, as follows:

USE master;  
GO  
ALTER DATABASE AdventureWorks   
SET RECOVERY FULL;  
GO  

For information on changing the recovery model in SQL Server Management Studio, see View or Change the Recovery Model of a Database (SQL Server).

Permissions

Requires ALTER permission on the database and CREATE ENDPOINT permission, or membership in the sysadmin fixed server role.

Example

In this example, the two partners and the witness are the default server instances on three computer systems. The three server instances run the same Windows domain, but the user account (used as the startup service account) is different for the example's witness server instance.

The following table summarizes the values used in this example.

Initial mirroring role	Host system	Domain user account
Principal	PARTNERHOST1	<Mydomain>\<dbousername>
Mirror	PARTNERHOST5	<Mydomain>\<dbousername>
Witness	WITNESSHOST4	<Somedomain>\<witnessuser>

1. Create an endpoint on the principal server instance (default instance on PARTNERHOST1).

   CREATE ENDPOINT Endpoint_Mirroring  
       STATE=STARTED   
       AS TCP (LISTENER_PORT=7022)   
       FOR DATABASE_MIRRORING (ROLE=PARTNER)  
   GO  
   --Partners under same domain user; login already exists in master.  
   --Create a login for the witness server instance,  
   --which is running as Somedomain\witnessuser:  
   USE master ;  
   GO  
   CREATE LOGIN [Somedomain\witnessuser] FROM WINDOWS ;  
   GO  
   -- Grant connect permissions on endpoint to login account of witness.  
   GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\witnessuser];  
   --Grant connect permissions on endpoint to login account of partners.  
   GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername];  
   GO  
   

2. Create an endpoint on the mirror server instance (default instance on PARTNERHOST5).

   CREATE ENDPOINT Endpoint_Mirroring  
       STATE=STARTED   
       AS TCP (LISTENER_PORT=7022)   
       FOR DATABASE_MIRRORING (ROLE=ALL)  
   GO  
   --Partners under same domain user; login already exists in master.  
   --Create a login for the witness server instance,  
   --which is running as Somedomain\witnessuser:  
   USE master ;  
   GO  
   CREATE LOGIN [Somedomain\witnessuser] FROM WINDOWS ;  
   GO  
   --Grant connect permissions on endpoint to login account of witness.  
   GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\witnessuser];  
   --Grant connect permissions on endpoint to login account of partners.  
   GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername];  
   GO  
   

3. Create an endpoint on the witness server instance (default instance on WITNESSHOST4).

   CREATE ENDPOINT Endpoint_Mirroring  
       STATE=STARTED   
       AS TCP (LISTENER_PORT=7022)   
       FOR DATABASE_MIRRORING (ROLE=WITNESS)  
   GO  
   --Create a login for the partner server instances,  
   --which are both running as Mydomain\dbousername:  
   USE master ;  
   GO  
   CREATE LOGIN [Mydomain\dbousername] FROM WINDOWS ;  
   GO  
   --Grant connect permissions on endpoint to login account of partners.  
   GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername];  
   GO  
   

4. Create the mirror database. For more information, see Prepare a Mirror Database for Mirroring (SQL Server).

5. On the mirror server instance on PARTNERHOST5, set the server instance on PARTNERHOST1 as the partner (making it the initial principal server instance).

   ALTER DATABASE AdventureWorks   
       SET PARTNER =   
       'TCP://PARTNERHOST1.COM:7022'  
   GO  
   

6. On the principal server instance on PARTNERHOST1, set the server instance on PARTNERHOST5 as the partner (making it the initial mirror server instance).

   ALTER DATABASE AdventureWorks   
       SET PARTNER = 'TCP://PARTNERHOST5.COM:7022'  
   GO  
   

7. On the principal server, set the witness (which is on WITNESSHOST4).

   ALTER DATABASE AdventureWorks   
       SET WITNESS =   
       'TCP://WITNESSHOST4.COM:7022'  
   GO  
   

Related Tasks

• Prepare a Mirror Database for Mirroring (SQL Server)

• Start the Configuring Database Mirroring Security Wizard (SQL Server Management Studio)

• Set Up a Mirror Database to Use the Trustworthy Property (Transact-SQL)

• Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL)

• Allow a Database Mirroring Endpoint to Use Certificates for Inbound Connections (Transact-SQL)

• Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)

See Also

ALTER DATABASE (Transact-SQL)
The Database Mirroring Endpoint (SQL Server)
Transport Security for Database Mirroring and AlwaysOn Availability Groups (SQL Server)
Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)
Security Center for SQL Server Database Engine and Azure SQL Database