Learn how to operate SQL Server Image _ MySQL

MSSQL has launched the image function since 2005. SQLServer images are unreadable. the entire library is in the norecovery mode. SQLServer images can only be 1-to-1, but images and logshipping can be used to achieve one-to-many and image chains. SQLServer Image Authentication is divided into certificates and w SQL Server

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 (replace all the 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!@#
 
 

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

 
 

  
  
create login mirror_a with password;='xwj1234!@#
 
 

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.
  
  
SetPartner = 'tcp: // 192.168.1.105: 8080'
  
  
--- Run alter database pai_test on the host
  
  
SetPartner = 'tcp: // 192.168.1.106: 8080'
  
  
--- Set to high-performance mode alter database pai_testset safety off
 
 

10 tests

 
 

  
  
-- Run on host
  
  
Create TableTest (idInt);
  
  
Insert IntoTestSelect1;
  
  
-- Execute
  
  
Create DatabasePerformance_test_snp
  
  
On Primary 
  
  
(Name= 'Mirror _ test', filename = 'C: \ pai_test.mdf ')
  
  
AsSnapshotOfPerformance_test;
  
  
Use cmd_testselect *FromTest
 
 

11 open the image monitor

498) this. style. width = 498; "/>

11. mirror + logshipping

498) this. style. width = 498; "/>

Original article title: MSSQLMiRROR

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

CreateUser created _ B

ForLogin cmd_bcreate certificate host_106

Authorization _bfrom file = 'C: \ host_106.cer'Grant

Connect OnEndpoint: endport_mirrorToPerformance_ B

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

___FCKpd___5

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

___FCKpd___6

10 tests

___FCKpd___7

11 open the image monitor

498) this. style. width = 498; "/>

11. mirror + logshipping

498) this. style. width = 498; "/>

Original article title: MSSQLMiRROR

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

CreateUser performance_aForLogin logs _a;

Grant Connect OnEndpoint ::

Endport_mirrorToPerformance_a;

CreateCertificate host_105authorization pai_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

___FCKpd___6

10 tests

___FCKpd___7

11 open the image monitor

498) this. style. width = 498; "/>

11. mirror + logshipping

498) this. style. width = 498; "/>

Original article title: MSSQLMiRROR

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

CreateUser created _ B

ForLogin cmd_bcreate certificate host_106

Authorization _bfrom file = 'C: \ host_106.cer'Grant

Connect OnEndpoint: endport_mirrorToPerformance_ B

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

___FCKpd___5

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

___FCKpd___6

10 tests

___FCKpd___7

11 open the image monitor

498) this. style. width = 498; "/>

11. mirror + logshipping

498) this. style. width = 498; "/>

Original article title: MSSQLMiRROR

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