Microsoft® SQL Server® 2012 Bible

700

Part V: Enterprise Data Management

FROM DISK = 'C:\AdventureWorksLog.bak'

WITH NORECOVERY;

8. Execute the following code on the mirror server to set the principal server as part-
   ner on the mirror database.
   ALTER DATABASE AdventureWorks
   SET PARTNER =
   'TCP://principal.hol169.local:5091';


9. Execute the following code on the principal server to set the mirror server as a
   partner on the principal database. Executing this statement begins the database
   mirroring session.
   ALTER DATABASE AdventureWorks
   SET PARTNER =
   'TCP://mirror.hol169.local:5091';


10. If you use a witness, set the witness server as follows on the principal server:
    ALTER DATABASE AdventureWorks
    SET WITNESS =
    'TCP://witness.hol169.local:5091';

By default, the database mirroring session is set to run in synchronous mode (safety full) without automatic

failover. To change the transactions safety level to OFF (asynchronous database mirroring), execute the following

command on the principal server:

ALTER DATABASE AdventureWorks SET PARTNER SAFETY OFF;

For confi guring a database mirroring session using certifi cates, refer to SQL Server 2012 Books Online

topic “Using Certifi cates for a Database Mirroring Endpoint.” http://msdn.microsoft.com/

en-us/library/ms191477.aspx

Post-Database Mirroring Configuration

As mentioned earlier, database mirroring applies only the changes that are either captured

in the transaction log or the initial full backup of the principal database. Any database

objects such as logins, jobs, maintenance plans, SSIS packages, and linked servers that

reside outside the principal database need to be manually created on the mirror server.

After confi guring database mirroring, you need to synchronize the mirror server with all

objects that live outside the mirrored database. Most of these objects can be easily scripted

using Management Studio and can be applied on the mirror server. The frequency of apply-

ing the changes must meet the rate of changes in your environment.

One of the ways to synchronize the logins is to create an Integration Services (SSIS) job

that connects to each server and transfers the logins. The frequency of this job depends on

how often new logins are added to your principal server.

c27.indd 700c27.indd 700 7/31/2012 9:50:28 AM7/31/2012 9:50:28 AM

http://www.it-ebooks.info