Microsoft® SQL Server® 2012 Bible

699

Chapter 27: Database Mirroring

27

Configuring Database Mirroring Using Transact-SQL

Like most other confi gurations, you can also confi gure database mirroring using Transact-

SQL. The following example shows the basic steps to confi gure a database mirroring session

for the AdventureWorks sample database using Transact-SQL and Windows Authentication.

The assumption is that both the partners and witness run under the same Windows domain

service account. This means that you do not need to create a login for each partner

because it already exists. If the partners and witness use different domain user accounts

for their service, startup accounts create a login for the account of the server using the

create login statement and grant connect permissions on the endpoint to the login

using the grant connect on endpoint command.

1. Connect to the principal server and execute the following code to create an end-
   point for the principal. In this example, the principal uses the TCP port 5091 for its
   endpoint:
   CREATE ENDPOINT Endpoint_Mirroring
   STATE=STARTED
   AS TCP (LISTENER_PORT=5091)
   FOR DATABASE_MIRRORING (ROLE=PARTNER);


2. Connect to the mirror server, and execute the preceding code.


3. If you use a witness, connect to the witness server, and execute the following code:
   CREATE ENDPOINT Endpoint_Mirroring
   STATE=STARTED
   AS TCP (LISTENER_PORT=5091)
   FOR DATABASE_MIRRORING (ROLE=WITNESS);


4. Execute the following code on the principal server to take a full database backup of
   the AdventureWorks database:
   BACKUP DATABASE AdventureWorks
   TO DISK = 'C:\AdventureWorks.bak';


5. Copy the AdventureWorks.bak on the mirror server and execute the following code
   on the mirror server to restore the AdventureWorks database in restoring mode:
   RESTORE DATABASE AdventureWorks
   FROM DISK = 'C:\AdventureWorks.bak'
   WITH NORECOVERY;


6. Execute the following code on the principal server to take a log backup of the
   AdventureWorks database:
   BACKUP LOG AdventureWorks
   TO DISK = 'C:\AdventureWorksLog.bak';


7. Copy the AdventureWorksLog.bak on the mirror server and execute the follow-
   ing code on the mirror server to restore the AdventureWorks database in restoring
   mode:
   RESTORE LOG AdventureWorks

c27.indd 699c27.indd 699 7/31/2012 9:50:27 AM7/31/2012 9:50:27 AM

http://www.it-ebooks.info