Teach you how to operate SQL Server Images

MSSQL has launched the image function since 2005.

The SQL Server image is unreadable. The entire database is in the norecovery mode.

SQL Server images can only be 1-to-1 images, but images and logshipping can be used to achieve one-to-many and image chains.

SQL Server image authentication is divided into certificates and windows authentication. windows authentication must be implemented in one domain.

Not every company's production environment is a domain. The advantage of the domain is that it can implement failover clustering + image hybrid implementation.

The following describes the authentication method image.

Environment: win2008 + mssql2008

1. Create a database master key on the master database

 
 

  
  
create master key encryption  by password ='123!@#abc'; 
 
 

2. Create a certificate

 
 

  
  
create certificate host_105with subject='host_105_c' 
 
 

3. Create an endpoint

 
 

  
  
create endpoint endport_mirrorstate=started 
  
  
as tcp(listener_port=1522, listener_ip=all)
  
  
for database_mirroring(authentication=certificate host_105,
  
  
 encryption=required algorithm AES, role=all ); 
 
 

4. Back up the certificate and copy the certificate to the backup server.

 
 

  
  
backup certificate host_105 to file='c:\host_105.cer'; 
 
 

5. Repeat steps 1-4 on the backup machine to replace all certificates with host_105 with host_106)

6. Create an SQL verification account on the host and bind it with the certificate

 
 

  
  
create login mirror_b with password='xwj1234!@#$'
  
  
create user mirror_b 
  
  
for login mirror_bcreate certificate host_106
  
  
authorization mirror_bfrom file='c:\host_106.cer'grant 
  
  
connect on endpoint:: endport_mirror to mirror_b  
 
 

7. Create an SQL verification account on the image machine and bind it with the certificate

 
 

  
  
create login mirror_a with password;='xwj1234!@#$'    
  
  
create user mirror_a for login mirror_a;    
  
  
grant connect on endpoint::   
  
  
endport_mirror to mirror_a;  
  
  
create certificate host_105authorization mirror_afrom file='c:\host_105.cer'; 
 
 

8. Create the mongo_test Database Log mode in the master database and set it to full mode and restore it on the mirror database. The Restore Mode is norecovery.

9. Synchronize Images

 
 

  
  
--- Run alter database pai_test on the backup storage.
  
  
Set partner = 'tcp: // 192.168.1.105: 8080'
  
  
--- Run alter database pai_test on the host
  
  
Set partner = 'tcp: // 192.168.1.106: 8080'
  
  
--- Set to high-performance mode alter database pai_testset safety off
 
 

10 tests

 
 

  
  
-- Run on host
  
  
Create table test (id int );
  
  
Insert into test select 1;
  
  
-- Execute
  
  
Create database pai_test_snp
  
  
On primary
  
  
(Name = 'mirror _ test', filename = 'C: \ pai_test.mdf ')
  
  
As snapshot of snapshot _test;
  
  
Use cmd_testselect * from test
 
 

11 open the Image Monitor

11. mirror + logshipping

Original article title: MSSQLMiRROR

Link: http://www.cnblogs.com/xwj1985/archive/2010/08/08/1795225.html