1. Executive Summary
The document describes the steps to configure SQL Server 2005
Database Mirror without Witness Server in Certificate Authentication.
Database Mirror is a transparent to the Datadirect ODBC Driver.
2.1 Physical Connection.
The connection between Master DB and Backup DB shows as
below figure.
Database Mirror can be built on SQL Server 2005 Standard
Edition, Enterprise Edition or Developer Edition except Express Edition (which
can only be as Witness Server).
Use the Transact-SQL as below to search the detail
information about the current SQL Server.
SELECT
@@version;
Note: Set the recovery mode of master DB as Full Recovery
Mode before backup the master database.
Use the Transact-SQL as below to set the recovery mode of
master DB as Full Recovery Mode.
USE
master;
ALTER DATABASE <DatabaseName>
SET RECOVERY FULL;
Backup the Master DB:
BACKUP
DATABASE <DatabaseName>
TO DISK = “D:\<SQLBACKUP>/<Database.bak>”
WITH FORMAT
Backup the transition log of Master DB:
BACKUP
LOG <DatabaseName>
TO
DISK = ”D:\<SQLBACKUP>\<DatabaseLog.bak”
Copy the backup files to backup DB machine.
Note: Just only do the “Master instance and backup instance
exchange setting” configuration once. In other word, if there are 5 databases
need to be Mirror database between the two instances, just do the configuration
once. In a word, the operation just should be done once in each master instance
and backup instance (not database) pair.
2.4.1 Create certificate
Master Machine executes:
US`E master;
CREATE
MASTER KEY ENCRYTION BY PASSWORD = ‘Tibco2012’;
CREATE
CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate',
START_DATE
= ‘19/08/2011’;
Backup Machine executes:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Tibco2012’;
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',
START_DATE = '19/08/2011';
2.4.2 Create the Endpoint
Master Machine executes:
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
Backup Machine executes:
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )
2.4.3 Backup certificates
Master Machine executes:
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';
Backup Machine executes:
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';
2.4.4 Exchange certificates
Copy Host_A_cert.cer to the directory,” D:\SQLBACKUP”, in
backup machine. Copy Host_B_cert.cer to
the directory, “D:\SQLBACKUP”, in master machine.
2.4.5 Add login, user
Master Machine executes:
CREATE LOGIN HOST_B_login WITH PASSWORD = 'Tibco2012';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
Backup Machine executes:
CREATE LOGIN HOST_A_login WITH PASSWORD = 'killkill';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
2.5 Setup Database Mirror
The steps below should be done on every mirroring database.
2.5.1 Synchronize login and password
One weakness of the Database Mirror is that database cannot maintain
logins. So it’s essential to maintain a login manually.
In general, there are many users used to access database. If
there is no relative user in backup database, application cannot access backup
database once backup database changing to master database.
Master Machine executes:
USE master;
select sid,name from syslogins;
It can return all users and SIDs from the instance. Ensure
there is a same user for this SID.
Backup Machine executes:
USE master;
exec sp_addlogin
@loginame = '<LoginName>',
@passwd = '<Password>',
@sid = <sid> ;
2.5.2 Restore backup database.
Restore the database
from the backup database file. Then choose RESOTRE WITH NORECOVERY mode.
If do the restore operation successfully. The backup
database will become like this:
Restore the transaction log of backup database:
2.5.2 Setup Database Mirror
Backup Machine executes:
ALTER DATABASE danieltest SET PARTNER = 'TCP://10.168.6.45:5022';
Master Machine executes:
ALTER DATABASE danieltest SET PARTNER = 'TCP://10.168.6.49:5022';
If do the operations
successfulyl. The backup database will become like
2.6 Test
2.6.1 Exchange
Master Machine executes:
USE master;
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;
2. If master DB downs, backup should DB start.
. Backup Machine
executes:
USE master;
ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
3. If master DB works again, DBA can reset the role.
Backup Machine executes:
USE master;
ALTER DATABASE <DatabaseName> SET PARTNER RESUME;
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;
- 大小: 68.7 KB
- 大小: 29.1 KB
- 大小: 1023 Bytes
- 大小: 24.5 KB
- 大小: 1.1 KB
分享到:
相关推荐
MCTS 70-643 Configuring Windows Server 2008 Application Infrastructure.part1
MCTS 70-643 Configuring Windows Server 2008 Application Infrastructure.part2
■■Part I: Installing and Configuring SQL Server ..................................... 1 ■■Chapter 1: Planning the Deployment ..................................................................... 3...
This second edition is newly-updated to cover the 2016 editions of both SQL Server and Windows Server. Also in this edition is all-new content on troubleshooting and metadata. The book provides a ...
The perfect book for multi-tasked IT managers responsible for securing the latest version of SQL Server 2005. SQL Server is the perfect product for the How to Cheat series. It is an ambitious ...
原版MSCE 考试用书,也可做为参考用书,比较详细MS.Exam.Ref.70-410.Installing.and.Configuring.Windows.Server.2012.R2.0735684
SQL Server administration used to just be the job of a database administrator (DBA), but as SQL Server proliferates throughout smaller companies, many developers have begun to act as administrators ...
6 The Building Blocks of Integration Services...................89 7 Grouping Control Flow with Containers............99 8 Building a Package—Quick Start ..................119 Part III Control Flow ...
Configuring SAP R3 FICO The Essential Resource for Configuring the Financial and Controlling Modules.part3
Syngress - Dr. Tom Shinder's Configuring ISA Server 2004 (2005).pdf
Pro SQL Server 2008 Replication is for Microsoft database developers and administrators who want to learn about the different types of replication and those best suited to SQL Server 2008. This book ...
Prepare for Microsoft Exam 70-410 – and help demonstrate your real-world mastery of implementing and configuring core services in Windows Server 2012 R2. Designed for experienced IT professionals ...
Configuring.Microsoft.Exchange.Server.2010
Configuring Xilinx FPGAs with Serial Flash.pdf
配置 Windows Server 高级功能,其中包括: Configure and manage high availability Configure file and storage solutions Implement business continuity and disaster recovery Configure network services ...
Configuring Hardware and Communication Connection Manual.pdf
Table of Contents Chapter 1: Introduction ....................................................................................... 7 Charitable Donation .................................................