Basic concepts of SQL Server 2005 mirroring configuration
I understand that the SQL Server 2005 mirroring configuration is actually a guaranteed data environment consisting of three servers (or three SQL instances of the same server), namely: master server, slave server, witness server.
Primary server: Where data is stored
From server: Where the data is backed up (i.e., the mirror of the primary server)
Witness: Dynamically provision a primary/slave server to a third-party server
Environment Introduction
Let's start by introducing the configured environment:
This configuration uses three separate servers (a, B, c three computers).
A: master server, ip:192.168.0.2
B: From the server, ip:192.168.0.3
C: witness server, ip:192.168.0.4
Three computers are in the same LAN, the system is Windows Server 2003, the database is SQL Server 2005
Start SQL Server 2005 Mirroring configuration
One, in a, B, c new configuration of a user (DBUser), the user to have all the use of SQL Server permissions, I am here to add the user to the Administrators group.
Second, execute the following SQL statement in a, B, C:
Creating objects in A, B, C
1USE Master 2GO 3 4CREATE ENDPOINT endpoint_mirroring 5 state = STARTED 6 as TCP ( 7 Listener_port = 5022--Listener port, optionally specified (three server ports are best consistent) 8, Listener_ip = All--listening IP address, all the addresses in the network
authentication = Windows-authentication mode, Windows Role = All-all roles 13); 14GO |
Third, execute the following SQL statement in a, B, C:
1GRANT CONNECT on endpoint::endpoint_mirroring to [testdb\administrators]; |
Iv. New Database in A (TestDB), Then back up the database to get the bak file (Testdb.bak), and then back up the transaction log of the database to get the trn file (testdb.trn), the Bak and trn files sent to B, by B restore, when using Enterprise Manager restore, in the "Options" in the " Recovery Status Select the second item, that is: do not perform any operations on the database, do not roll out uncommitted transactions, you can restore other transaction logs (A). (RESTORE with NORECOVERY).
V. Execute the following SQL statement in A and B:
Add individual servers to the environment to
Execute in a server (master server): 2ALTER DATABASE TestDB SET PARTNER = N ' tcp://192.168.0.3:5022 '; --adding from the server to the environment 3ALTER DATABASE TestDB SET WITNESS = N ' tcp://192.168.0.4:5022 '; --Add the witness server to the environment 4 5--b Server (from server) execution: 6ALTER DATABASE TestDB SET PARTNER = ' tcp://192.168.0.2:5022 '; --Connect from the server to the master server |
"Editor's recommendation"
- SQL Server uses indexes for data access optimization
- on how to generate scripts in SQL Server
- SQL Server 05 Database is set as a "suspect" workaround
- Explanation of SQL Server version differences and selection
- Best SQL Server version selection to meet database requirements
Real-time SQL Server 2005 mirroring configuration whole process