In the domain environment I did not configure the results, perhaps the reason for domain users, because I am in the production environment, change domain users need to restart SQL Server, so this method is discarded, only in the form of a certificate.
Environment:
Host: 192.168.10.2 (Code a)
Mirror: 192.168.10.1 (code B, for a while to explain the convenience)
(The conditions are limited I don't have a witness server.) Both servers are SQLServer2005.
First configure the Host
The following SQL is executed on the host
Copy Code code as follows:
--Create host Database master key
Use master;
CREATE MASTER KEY Encryption by PASSWORD = ' PASSWORD ';
Go
--Create a certificate for the database instance on 10.2
CREATE Certificate As_a_cert
with SUBJECT = ' As_a_cert ',
start_date = ' 09/02/2011 ',
expiry_date = ' 01/01/2099 ';
Go
--Create a mirrored endpoint for the database instance using the certificate created above on 10.2
CREATE ENDPOINT Endpoint_as
State = Started
As TCP (
listener_port=5022,
LISTENER_IP = All
)
For Database_mirroring (
authentication = Certificate As_a_cert,
encryption = REQUIRED algorithm RC4,
Role = All
);
Go
Note: Be careful to set the mirror port of the database here. 5022.
--Back up the certificate on 10.2 and copy it to 10.1
BACKUP certificate As_a_cert to FILE = ' D:\As_A_cert.cer ';
Go
Note: Back up certificate A and copy certificate A to mirror server B.
Configuring the mirror server
Copy Code code as follows:
Use master;
CREATE MASTER KEY Encryption by PASSWORD = ' PASSWORD ';
Go
--Create a certificate for the database instance on 10.1 b
CREATE Certificate As_b_cert
with SUBJECT = ' As_b_cert ',
start_date = ' 09/2/2011 ',
expiry_date = ' 01/01/2099 ';
Go
--Create a mirrored endpoint for the database instance using the certificate created above on 10.1 b
CREATE ENDPOINT Endpoint_as
State = Started
As TCP (
listener_port=5022
, listener_ip = All
)
For Database_mirroring (
Authentication = Certificate As_b_cert
, encryption = REQUIRED algorithm AES
, role = All
);
Go
--Back up the certificate on 10.1 B and copy it to 10.2 a
BACKUP certificate As_b_cert to FILE = ' D:\As_B_cert.cer ';
Go
Also copy the backup certificate B to the a server.
Establish an account for mirroring logins
Execute on a
--Exchange certificates,
--Synchronous Login
Copy Code code as follows:
CREATE LOGIN b_login with PASSWORD = ' PASSWORD ';
CREATE USER b_user for LOGIN B_login;
CREATE Certificate As_b_cert AUTHORIZATION b_user from FILE = ' D:\As_B_cert.cer ';
GRANT CONNECT on Endpoint::endpoint_bs to [B_login];
Execute on B
Copy Code code as follows:
--Exchange certificates,
--Synchronous Login
CREATE LOGIN a_login with PASSWORD = ' PASSWORD ';
CREATE USER a_user for LOGIN A_login;
CREATE Certificate As_a_cert AUTHORIZATION a_user from FILE = ' D:\As_A_cert.cer ';
GRANT CONNECT on Endpoint::endpoint_as to [A_login];
Remember that port 5022 on both servers is not occupied and that two servers can be connected
The next step is no problem, the mirror is half done.
Next, complete backup of test library on Server A
Copy Code code as follows:
--the host performs a full backup
Use master;
ALTER DATABASE Test SET RECOVERY full;
Go
BACKUP DATABASE Test
to DISK = ' D:\SQLServerBackups\Test.bak '
With FORMAT;
Go
BACKUP LOG Test to DISK = ' D:\SQLServerBackups\Test.bak ';
Go
-Copy the backup file to B.
Be sure to perform a full backup.
Complete Welcome to database on B server
There are a lot of problems here. One by one said.
If we execute the following SQL directly.
Copy Code code as follows:
RESTORE DATABASE Test
From DISK = ' D:\Back\Test.bak '
With NORECOVERY
Go
RESTORE LOG Test
From DISK = ' D:\Back\Test_log.bak '
With File=1, NORECOVERY
Go
[Code]
May be reported:
Message 3154, Level 16, State 4, line 1th
The database backup in the backup set is different from the existing ' Test ' database.
Message 3013, Level 16, State 1, line 1th
It is possible that the backup set names for the two databases were different, and for half a day, the following sp_addumpdevice methods were used.
Use sp_addumpdevice to build a restored device. This ensures that the backup file is the data database.
[Code]
EXEC sp_addumpdevice ' disk ', ' test_backup ',
' E:\backup\Test.bak '
EXEC sp_addumpdevice ' disk ', ' test_log_backup ',
' E:\backup\Test_log.bak '
Go
After the success, we'll perform the recovery.
Copy Code code as follows:
RESTORE DATABASE Test
From Test_backup
With Dbo_only,
Norecovery,stats;
Go
RESTORE LOG Test
From Test_log_backup
With File=1,
NORECOVERY;
Go
Here if you have backed up multiple databases before, Ken will produce more than one backup set. So the file here cannot be specified as 1.
This error may be:
Message 4326, Level 16, State 1, line 1th
The log in this backup set terminates at LSN 36000000014300001, which is too early to be applied to the database
。 You can restore a newer log backup that contains LSN 36000000018400001.
You can use this sentence to query the backup set of the backup file
RESTORE HEADERONLY from disk = ' E:\backup\Test_log.bak '
Find the last ordinal and assign it to file.
Also note that you need to specify NORECOVERY for the first full recovery.
So all the preparations have been completed, and we are starting to perform mirroring.
Execute on the mirror server first
ALTER DATABASE Test SET PARTNER = ' tcp://192.168.10.2:5022 ';
Successful and then executed on the host
ALTER DATABASE Test SET PARTNER = ' tcp://192.168.10.2:5022 ';
In this way the mirrors of the two servers are synchronized.
To remove a mirror:
ALTER DATABASE Test SET PARTNER off
If there is a problem with the host, execute it on the host
Copy Code code as follows:
Use MASTER
Go
ALTER DATABASE Test SET PARTNER FAILOVER
Go
Summarize:
If the step in the middle of creating a mirror goes abroad, you must delete the deleted item when you need to do it again.
--Query mirroring
SELECT * FROM Sys.endpoints
--Delete Port
Drop Endpoint Endpoint_as
--Query Certificate
SELECT * FROM Sys.symmetric_keys
--Delete the certificate, delete the certificate, and then delete the primary key
DROP Certificate As_a_cert
--Delete primary key
DROP MASTER KEY
--Remove mirror
ALTER DATABASE <dbname> SET PARTNER off
--Delete login name
Drop Login <login_name>
Sp_addumpdevice's Grammar
Copy Code code as follows:
sp_addumpdevice [@devtype =] ' Device_type '
, [@logicalname =] ' logical_name '
, [@physicalname =] ' physical_name '
]
Where the parameters are:
@devtype: Device type, the values that can be supported are disk and tape;
Any tape devices that are supported by Windows.
@logicalname: Logical name of the backup device, device name.
@physicalname: Physical name of the backup device, path
Reference:
http://msdn.microsoft.com/zh-cn/library/ms187495 (v=sql.90). aspx
Http://msdn.microsoft.com/zh-cn/library/ms187014.aspx
Http://msdn.microsoft.com/zh-cn/library/ms186289.aspx
