SQL 2005 Installation Database Mirroring tutorial

Recently in the SQL 2005 installed database mirroring, encountered a lot of errors, in this summary to facilitate the needs of friends reference.

Directly on the script, the host part:

---Modify the database to the full recovery model
Use master;
ALTER DATABASE Cztest
SET RECOVERY full;
--------Create a certificate
Use master;
Create MASTER key encryption by PASSWORD = ' 23987hxj#kl95234nl0zbe ';--Creates a database key and provides a key usage password
CREATE CERTIFICATE host_a_cert with SUBJECT = ' host_a CERTIFICATE ',
start_date = ' 07/08/2014 ';
--------Create a connection port
CREATE ENDPOINT endpoint_mirroring
State = STARTED
As
TCP (listener_port=5022, listener_ip = All)
For
Database_mirroring
(authentication = CERTIFICATE Host_a_cert, encryption = REQUIRED algorithm AES, ROLE = All);

----------Backup Certificate
BACKUP CERTIFICATE host_a_cert to FILE = ' D:\SQLBackup\HOST_A_cert.cer ';
----------Interchange Certificate
--Operation completed
----------Add a login name and user
CREATE LOGIN login_b with PASSWORD = ' 2014sa ';
CREATE USER user_b for LOGIN Login_b;
CREATE CERTIFICATE host_b_cert AUTHORIZATION user_b from FILE = ' D:\SQLBackup\HOST_B_cert.cer ';
Grant connect on endpoint::endpoint_mirroring to login_b;--allows the Host_b_login login to be connected to the port

-----------Manually synchronizing logins and Passwords
----Login B's SID=0X5AA61C2F8FFA0B45AC27EEB148083CD9
Select Sid,name from syslogins;

----Create a mirror (unsuccessful cause: 1. Full database backup, transaction log also full backup
----2. Create a mirrored SQL to be executed first in the backup library and then in the main library. Order cannot be reversed)
----3. The Database Mirroring Transport feature is disabled in the endpoint configuration: Workaround: DBCC TRACEON (1400,-1) or install SP1

----4. Authentication failed while installing SP1, two instances were displayed incorrectly, one of them is Reporting Services, open Reporting Services Configuration, check database connection, configure your own Account
ALTER DATABASE ceshi SET PARTNER = ' tcp://192.168.137.13:5022 ';
ALTER DATABASE hxcreditbuy SET PARTNER = ' tcp://192.168.137.13:5022 ';
-----The following sentence succeeds
ALTER DATABASE cztest SET PARTNER = ' tcp://192.168.137.13:5022 ';
-------------========== Check Script ======================

DROP LOGIN Host_b_login
DROP USER Host_b_user

DBCC TRACEON (1400,-1)

DROP MASTER KEY
DROP CERTIFICATE Host_a_cert

--Check that the certificate for the host and Standby is present:
Select Name, Start_date, expiry_date from sys.certificates where name isn't like ' ##% '

--Delete the mirror database certificate
--drop the old cert for the principal login
Use master
DROP CERTIFICATE Host_a_cert
GO
Use master
DROP CERTIFICATE Host_b_cert
GO


--Check the mirror port
SELECT * from sys.endpoints where type=4

--Delete Port
Use master
DROP Endpoint endpoint_mirroring
GO
------------------------------------------------------------------Standby Script------------------------------------------

--------------standby Log-in name sa password SA
--------Create a certificate
Use master;
CREATE MASTER KEY Encryption by PASSWORD = ' 23987hxj#kl95234nl0zbe ';
CREATE CERTIFICATE host_b_cert with SUBJECT = ' Host_b CERTIFICATE ',
start_date = ' 07/08/2014 ';

--------Create a connection port
CREATE ENDPOINT endpoint_mirroring
State = STARTED
As
TCP (listener_port=5022, listener_ip = All)
For
Database_mirroring
(authentication = CERTIFICATE Host_b_cert, encryption = REQUIRED algorithm AES, ROLE = All);

---------Backup Certificate
BACKUP CERTIFICATE host_b_cert to FILE = ' C:\SQLBackup\HOST_B_cert.cer ';
---------Interchange Certificate
------Operation completed
------Add logins and users
CREATE LOGIN login_a with PASSWORD = ' 2014sa ';
CREATE USER user_a for LOGIN login_a;
CREATE CERTIFICATE host_a_cert AUTHORIZATION user_a from FILE = ' C:\SQLBackup\HOST_A_cert.cer ';
GRANT CONNECT on endpoint::endpoint_mirroring to Login_a;

-----Manually Synchronizing Logins
Use master;
EXEC sp_addlogin
@loginame = ' Login_b ',
@passwd = ' 2014sa ',
@sid = 0X5AA61C2F8FFA0B45AC27EEB148083CD9;

----Create a mirror (unsuccessful cause: 1. Full database backup, transaction log also full backup
----2. Create a mirrored SQL to be executed first in the backup library and then executed in the main library, in a sequence that is not reversed)
ALTER DATABASE hxcreditbuy SET PARTNER = ' tcp://lidongsh-a2628e:5022 ';
ALTER DATABASE ceshi SET PARTNER = ' tcp://lidongsh-a2628e:5022 ';
-----The following sentence succeeds
ALTER DATABASE cztest SET PARTNER = ' tcp://lidongsh-a2628e:5022 ';
----------===================================================================