How to configure database mirroring < a >

First, Introduction

Database mirroring is a software solution introduced in SQL Server 2005 that is primarily used to increase database availability. Mirroring is performed on a per-database basis and applies only to databases that use the full recovery model . Database mirroring is not supported in the simple recovery model and the bulk-logged recovery model.

Database mirroring works by maintaining a hot standby server. During a typical mirroring session, if a production server fails, the client application can quickly recover by reconnecting to the standby server.

Second, the premise conditions

1, prepare the server, ensure that the database version is at least SQL Server 2005 SP1, of course, the best is the latest version, the old version of the test not!

2. Ensure that the database to be mirrored is the full recovery model

3, configure the domain environment, database mirroring using a domain environment will be more convenient, feel better than the use of certificates to save trouble

4. How does the SQL Server service run as a domain account?

5, select the mirroring scheme, the image service can use three DB instances (master, mirror, witness) can also use two DB instance (primary, mirror), each instance in actual usage environment should be run on different server. The advantage of using a witness is that automatic failover is possible, but 3 servers are required, and at least 2 of them are available, because if the mirror server and the witness are simultaneous, there is no problem with the immediate master server and the service cannot be provided externally. I'm just here to mirror the database, make a real-time database backup so that the witness server is not used, if the primary server is down, you need to manually switch the mirror server to the primary server.

Third, the specific steps

1, the primary server full backup requires a mirrored database, on the mirror server to "do not perform any operations on the database, do not roll the uncommitted transactions." (Restore with NORECOVERY) "State recovery. Then back up the transaction log with the same recovery state on the mirror server to recover the transaction log.

2, in the main server, select the mirror database needs to be mirrored, click "Configure Security"

3. Select No, not including the witness server instance

4. Select the principal server instance

5. Select the mirror server instance

6, if the primary server instance and the mirror server instance is running with the different domain account, needs to enter the corresponding account, I use the same account so blank, two have the establishment account footstep

7. Complete configuration

8. Configure Endpoints

9. Click "Start Mirror" to complete

10, if no error is prompted, the primary server's database will display "Principal, synchronized," The mirror server's database will display "mirror, synchronized, restoring ...", the Database Mirroring service configuration is complete

Iv. failover

1, host machine interchange
Press the "Fail over" button on the console to

2, if the host is down, you need to enable the mirror server when the primary server
Performed on the mirror server
Use master;
ALTER database name SET PARTNER Force_service_allow_data_loss;
Then press the "Fail over" button to do it.

V. Errors encountered

1, "msg 1418, Level 16, State 1, Row 1th server network address" tcp://machine name or IP address: port number "is unreachable or does not exist. Check the network address name and check that the ports on the local and remote endpoints are working correctly. ”

It is most likely that when the mirror server recovers the database, there is no option to "do nothing to the database and not roll out uncommitted transactions." (RESTORE with NORECOVERY) ", should be caused by no permissions, that is, the server and the mirror server did not set the correct login name or settings but prompted a warning!

Detailed solutions See < two >

2. The mirror Database "house" contains insufficient transaction log data to preserve the log backup chain of the principal database. This can happen if you do not log backups from the principal database or if you do not restore the log backups on the mirrored database. (Microsoft SQL Server, error: 1478)

This error occurs because the transaction log is not backed up and restored

3, by default, database mirroring is disabled.

This is because there is no patching reason, thought that SQL Server 2005 was just introduced when the mirroring function is used as an evaluation, so the default is closed, SP1 can be formally used

4. Neither the partner server instance nor the witness server instance of the database "House" is available. Please re-issue the command at least one of the instances becomes available.

This problem is more strange, with the virtual machine experiment completed in the production environment subordinates this problem, in the online search, also did not find a solution, finally in a forum see also added tracking flag 1400 unexpectedly passed, but I have played SP3, really do not understand.

Six, tracking signs

Trace flags are used to temporarily set the characteristics of a particular server or to turn off specific behavior. For example, if trace flag 3205 is set when you start an instance of SQL Server 2005, hardware compression for the tape drive is disabled. Trace flags are frequently used to diagnose performance problems, or to debug stored procedures or complex computer systems. See MSDN for details
Http://msdn.microsoft.com/zh-cn/library/ms188396.aspx, but MSDN does not seem to have 1400 of the instructions, may be older than the 1400 this sign, the Internet search for a bit

1400
Enables the creation of database mirroring endpoints, which must be used when setting up and using Database mirroring. Enables the creation of database mirroring endpoints, which must be used when setting up and using Database mirroring
Yes. You can set this trace flag only if you use the-t trace# startup option when you start the server instance.

Setup method:

DBCC TRACEON (1400) or the database service plus startup parameters-t1400

Transfer from 51:http://dqw3721.blog.51cto.com/112726/390327/

How to configure database mirroring <;