Database image creation

I. Overview

Database Mirroring is a new technology used by SQL Server 2005 to improve Database Availability. The database image transfers Transaction log records directly from one server to another, and can be quickly transferred to the backup server when a fault occurs. You can write the client program to automatically redirect the connection information, so that once a Failover occurs, you can automatically connect to the backup server and database.

Advantage: database images can be quickly transferred without losing committed data,No special hardware requiredAnd easy to configure and manage.

2. Environment preparation

Operating System: Window 2003 enterprise SP2 (at least two, three required to enable automatic failover)

SQL version: MSSQL Server 2005 SP3

Check the SQL Server version:

Exec xp_msver

Select serverproperty ('productlevel ')

Database preparation: Prepare a database: ccerp_jzt. Back up the database and restore it to another machine. The other one must beWith no recovery

Here we assume that the server A, B, and C

A is the master server, B is the backup server, and C is the witness server.

Server

Use master

Go

Restore filelistonly from disk = n'f: \ databak \ ccerp_jzt_backup_200911250100.bak'

Restore database ccerp_jzt from disk = n' F: \ databak \ ccerp_jzt_backup_200911250100.bak 'with replace, recovery,

Move 'ccerp _ ydswzip_data 'to 'd: \ data \ ccerp_jzt.mdf ',

Move 'ccerp _ ydswzip_log 'to 'd: \ data \ ccerp_jzt_log.ldf'

Exec sp_helpdb 'ccerp _ jzt'

Backup database ccerp_jzt to disk = n'f: \ databak \ SK. Bak 'with init

-- Change recovery mode

Alter database ccerp_jzt set recovery full

Server B:

Create Database ccerp_jzt

On

(Name = sales_dat,

Filename = 'd: \ data \ ccerp_jzt.mdf ',

Size = 10

)

Log On

(Name = 'ccerp _ jzt_log ',

Filename = 'd: \ data \ ccerp_jzt_log.ldf ',

Size = 5 MB

)

Go

Restore filelistonly from disk = n' F: \ xxzx \ data \ SK. Bak'

Use master

Go

Restore database ccerp_jzt from disk = n' F: \ xxzx \ data \ SK. Bak 'with replace, norecovery,

Exec sp_helpdb 'ccerp _ jzt'

C server only needs to be installed with SQL Server 2005. No additional preparation is required.

After the preparation is complete, as shown in:

Construction of three modes

To establish a required trust relationship for a database image, you can establish a trust relationship in a win environment in three ways: domain account, Certificate Trust, and anonymous Windows login, the configuration of the first two modes is described.

3.1 domain account mode:

3.1.1 change the MSSQLServer service logon method to domain account logon:

Log on to the Windows service console and change the service Logon account so that the domain account has the permission to change the service status of MSSQL Server.

Grant the SysAdmin role to the domain account

3.1.2 create an endpoint:

Create an endpoint through the graphic interface:

Start sqlwb and follow the steps shown below

Log in with a domain account

If yes:

3.2 certificate Mode

3.2.1 create a certificate and an endpoint

The servers that participate in the database image session must trust each other. For local communication, such as intra-Domain Communication, trust means that the account for logging on to the SQL server instance must have the permission to connect to other image servers, including endpoints. First, use the create LOGIN command on each server, and then use the grant connect on endpoint command. certificates must be used for communication between untrusted domains. If you use the create certificate statement to create a self-signed certificate, basically all data image certificates can meet the requirements. Make sure to mark the certificate as active for in the create certificate statement
Begin_dialog.

1. Create a certificate:

Run the following command on the backup storage:

Use master;

Create master key encryption by Password = 'test ';

Create certificate host_a_cert with subject = 'host _ A certificate ', start_date = '2017-03-10 ';

Run the following command on the main server:

Use master;

Create master key encryption by Password = 'test ';

Create certificate host_ B _cert with subject = 'host _ B certificate ', start_date = '2017-03-10 ';

Run the following command on the witness Server:

Use master;

Create master key encryption by Password = 'test ';

Create certificate host_c_cert with subject = 'host _ C Certificate ', start_date = '2017-03-10 ';

2. Create an endpoint:

Run the following command on the backup storage:

-- Create mirror endpoint on primary

Create endpoint endpoint_grouping ing

State = started

TCP (listener_port = 5022, listener_ip = All)

For database_grouping ing

(Authentication = certificate host_a_cert, encryption = required algorithm AES, role = All );

Run the following command on the main server:

-- Create endpoint on mirror server B

Create endpoint endpoint_grouping ing

State = started

As

TCP (listener_port = 5022, listener_ip = All)

For

Database_processing ing

(Authentication = certificate host_ B _cert, encryption = required algorithm AES, role = All );

Run the following command on the witness Server:

-- Create endpoint on witness server C

Create endpoint endpoint_grouping ing

State = started

As

TCP (listener_port = 5022, listener_ip = All)

For

Database_processing ing

(Authentication = certificate host_c_cert,

Encryption = required algorithm AES, role = witness );

Select * From SYS. database_mongoing_endpoints;

Certificate mutual backup:

Run the following command on the backup storage:

-- Backup Certificate

Backup certificate host_a_cert to file = 'e: \ host_a_cert.cer'

Executed on the main server

-- Backup Certificate

Backup certificate host_ B _cert to file = 'e: \ host_ B _cert.cer'

Run the following command on the witness Server:

Backup certificate host_c_cert to file = 'e: \ host_c_cert.cer'

Replace the certificates backed up, that is, copy host_a_cert.cer to E: \ of machine B and copy host_ B _cert.cer to e :\of machine A, that is, each server has three certificates.

3. Create a login user:

Run the following command on the backup storage:

-- Create user

Create login host_ B _login with Password = 'test ';

Create user host_ B _user for login host_ B _login;

Create certificate host_ B _cert authorization host_ B _user from file = 'e: \ host_ B _cert.cer ';

Grant connect on endpoint: endpoint_sorting ing to [host_ B _login];

Create login host_c_login with Password = 'test ';

Create user host_c_user for login host_c_login;

Create certificate host_c_cert authorization host_c_user from file = 'e: \ host_c_cert.cer ';

Grant connect on endpoint: endpoint_sorting ing to [host_c_login];

Grant connect on endpoint: endpoint_sorting ing to [host_a_login];

-- Query user Sid

Select loginname, name, Sid from syslogins

Run the following command on the main server:

-- Create user

Create login host_a_login with Password = 'test ';

Create user host_a_user for login host_a_login;

Create certificate host_a_cert authorization host_a_user from file = 'e: \ host_a_cert.cer ';

Grant connect on endpoint: endpoint_sorting ing to [host_a_login];

-- Add witness user

Create login host_c_login with Password = 'test ';

Create user host_c_user for login host_c_login;

Create certificate host_c_cert authorization host_c_user from file = 'e: \ host_c_cert.cer ';

Grant connect on endpoint: endpoint_sorting ing to [host_c_login];

Grant connect on endpoint: endpoint_sorting ing to [host_ B _login];

-- Query Sid

Select loginname, name, Sid from syslogins

Run the following command on the witness Server:

-- Create user

Create login host_a_login with Password = 'test ';

Create user host_a_user for login host_a_login;

Create certificate host_a_cert authorization host_a_user from file = 'e: \ host_a_cert.cer ';

Grant connect on endpoint: endpoint_sorting ing to [host_a_login];

-- Add user host_ B _login to have pemission to access witness

Create login host_ B _login with Password = 'test ';

Create user host_ B _user for login host_ B _login;

Create certificate host_ B _cert authorization host_ B _user from file = 'e: \ host_ B _cert.cer ';

Grant connect on endpoint: endpoint_sorting ing to [host_ B _login];

Grant connect on endpoint: endpoint_sorting ing to host_c_login

Use master;

Exec sp_addlogin

@ Loginame = 'host _ B _login ',

@ Passwd = 'test ',

@ SID = 0x1a914ca3d1d00c4793ebc96e4c4f4352;

Alter database ccerp_jzt set partner = 'tcp: // 192.168.137.32: 8080 ';

4. Create an image:

Run the following command on the backup storage:

Alter database ccerp_jzt set partner = 'tcp: // 192.168.137.44: 8080 ';

Then the main server executes:

Alter database ccerp_jzt set partner = 'tcp: // 192.168.137.32: 8080 ';

Alter database ccerp_jzt set witness = 'tcp: // 192.168.137.49: 8080 ';

Now the certificate has been created.

Iv. Test operations

1. Master-slave swap

-- Host execution:

1use
Master;
2 alter
Database <databasename> set partner failover;
3

2. When the master server is down, the slave server is started and the service starts.

-- Run the following command on the slave machine:

1use
Master;
2 alter
Database <databasename> set partner force_service_allow_data_loss;
3

3. The original master server is restored and can continue working. You need to reset the image.

1 -- slave machine execution:
2use
Master;
3 alter
Database <databasename> set partner resume; -- restore the image
4 alter
Database <databasename> set partner failover; -- Switch master/backup
5

4. The original master server is restored and can continue to work.

-- By default, the transaction security level is set to full, that is, synchronous running mode, and SQL Server 2005 Standard Edition only supports synchronous mode.

-- To disable transaction security, you can switch sessions to asynchronous running mode, which can achieve the best performance.

1use
Master;
2 alter
Database <databasename> set partner safety full; -- transaction security, synchronization mode
3 alter
Database <databasename> set partner safety off; -- insecure transactions, asynchronous mode

Error description:
Message 1498, level 16, status 3, 1st rows

By default, database images are disabled. The currently provided database images are only for evaluation and should not be used in the production environment. To enable a database image for evaluation purposes, use the tracking flag 1400 during startup. For more information about Trace flags and startup options, see SQL Server books online.

Solution: no patch above SP1 is provided. We strongly recommend that you use SP3.

Message 1475, level 16, status 2, 1st rows

The database image cannot be enabled because the "ccerp_jzt" database may have a large-capacity log record change that has not been backed up. The last log backup of the primary database must be restored on the image.

On the Subject: backup log ccerp_jzt to disk = 'e: \ log. trn' with no_truncate

Image: Restore log ccerp_jzt from disk = 'e: \ log. trn' with norecovery