SQL2005 미러링 AD구성없이 인증서 사용구성

host파일 수정
192.168.0.1 sqlm1.test.com
192.168.0.2 sqlm2.test.com
192.168.0.3 monitor.test.com

sqlm1에서 DB 풀백업
backup database test to disk='c:\test.bak'
backup log test to disk='c:\test_log.bak'

sqlm2에서 DB 복원
RESTORE DATABASE test
FROM DISK='c:\test.bak' WITH NORECOVERY
RESTORE LOG test
FROM DISK='c:\test_log.bak'  WITH FILE=1, NORECOVERY

각 서버별 인증서 파일생성

sqlm1-----------
create master key encryption by password ='*****'

create certificate database_a_cert with subject='catabase a certificate', start_date='2009/1/1', expiry_date='2100/12/31'

create endpoint endpoint_mirroring state=started
as tcp(listener_port=5022, listener_ip=all)
for database_mirroring(authentication=certificate database_a_cert, encryption=required, role=all)

backup certificate database_a_cert to file='c:\database_a_cert.cer'

sqlm2--------------
create master key encryption by password ='*****'

create certificate database_b_cert with subject='catabase b certificate', start_date='2009/1/1', expiry_date='2100/12/31'

create endpoint endpoint_mirroring state=started
as tcp(listener_port=5022, listener_ip=all)
for database_mirroring(authentication=certificate database_b_cert, encryption=required, role=all)

backup certificate database_a_cert to file='c:\database_b_cert.cer'

monitor--------------
create master key encryption by password ='*****'

create certificate database_c_cert with subject='catabase c certificate', start_date='2009/1/1', expiry_date='2100/12/31'

create endpoint endpoint_mirroring state=started
as tcp(listener_port=5022, listener_ip=all)
for database_mirroring(authentication=certificate database_c_cert, encryption=required, role=all)

backup certificate database_a_cert to file='c:\database_c_cert.cer'

생성한 인증서를 각 서버의 C:\에 모두 복사

로그인 계정 생성 및 Endpoint설정
sqlm1-----------
create login administrator with password='*******'

create user administrator from login administrator

create certificate database_b_cert
authorization administrator
from file='c:\database_b_cert.cer'

create certificate database_c_cert
authorization administrator
from file='c:\database_c_cert.cer'

grant connect on endpoint::endpoint_mirroring to administrator

sqlm2-----------
create login administrator with password='*******'

create user administrator from login administrator

create certificate database_a_cert
authorization administrator
from file='c:\database_a_cert.cer'

create certificate database_c_cert
authorization administrator
from file='c:\database_c_cert.cer'

grant connect on endpoint::endpoint_mirroring to administrator

monitor-----------
create login administrator with password='*******'

create user administrator from login administrator

create certificate database_a_cert
authorization administrator
from file='c:\database_a_cert.cer'

create certificate database_b_cert
authorization administrator
from file='c:\database_b_cert.cer'

grant connect on endpoint::endpoint_mirroring to administrator

미러링 설정(마법사 설정가능)