SQL SERVER2008 Mirroring All Raiders

--To create database mirroring in a non-domain controlled environment, we must use certificates to create database mirroring. The approximate steps include:

--Perform the following steps on each server instance that is configured for database mirroring:

--In the master database, create the database master key.
--In the master database, create an encryption certificate for the server instance.
--Create an endpoint for that server instance using the server instance's certificate.
--Back up the certificate to a file and securely replicate it to other systems.


--then perform these steps for each partner that is configured for database mirroring. In the master database:

--Create a login name for the other system.
--Create a user that uses the login name.
--Gets the certificate for the mirroring endpoint of the other server instance.
--Associate the certificate with the user created in step 2.
--Grant CONNECT permission to the login name of the mirroring endpoint.


/* Query all current database names
Select SERVERPROPERTY (N ' servername ')
SELECT
Dtb.name as [name]
From
Master.sys.databases as DtB
ORDER by
[Name] Asc
*/
Pre--------------------------------------------------------Preparation
--View Database version
SELECT @ @VERSION
-----Configure Database Mirroring transaction Security level
ALTER DATABASE testmirroring SET SAFETY full
-----Change the primary database to use the full recovery model (if the non-full recovery model can use the following SQL to change to the full recovery model)
Use master
ALTER DATABASE testmirroring
SET RECOVERY full with no_wait

/* Database mirroring is not supported for cross-database transactions and distributed transactions
*/

--The database version of the primary server witness server mirror server must be the same
--Backup primary database (full backup)
BACKUP DATABASE [testmirroring] to DISK = N ' C:/wenzhongfiles/testmirror.bak ' with Noformat, Noinit,
NAME = N ' testmirroring-full database backup ', SKIP, Norewind, nounload, STATS = 10
--Backup the full log file of the primary database
BACKUP LOG [testmirroring] to DISK = N ' C:/wenzhongfiles/testmirrorlog.bak ' with Noformat, Noinit,
NAME = N ' testmirroring-transaction log backup ', SKIP, Norewind, nounload, STATS = 10
--Restore on the mirror server (restore option norecovery (i.e. do not perform any operations on the database)
RESTORE DATABASE [testmirroring] from DISK = N ' f:/testmirror.bak ' with FILE = 1, MOVE N ' Testmirror '
To N ' E:/program files/microsoft SQL server/mssql10. Mssqlserver/mssql/data/testmirroring.mdf ', MOVE N ' Testmirror_log '
To N ' E:/program files/microsoft SQL server/mssql10. Mssqlserver/mssql/data/testmirroring_1.ldf ', NORECOVERY, Nounload, REPLACE, STATS = 10
--The full log file of the backup is executed between log text on the mirror server (restore option norecovery (i.e. do not perform any operations on the database)
RESTORE LOG [testmirroring] from DISK = N ' F:/testmirrorlog.bak ' with FILE = 1, NORECOVERY, nounload, STATS = 10


--------------officially started (the code is just for example on the master server)

--------------------------------------------------------------------------performed separately on the primary, mirror, and witness servers:
------Creating a database symmetric key
Use master;
--drop MASTER KEY
CREATE MASTER KEY Encryption by PASSWORD = ' Wenzhong ';
GO
------Create a Database certificate (note: Certificate expiration date)
--drop CERTIFICATE Host_a_cert
CREATE CERTIFICATE Host_a_cert
with SUBJECT = ' host_a certificate ', start_date = ' 01/01/2011 ', expiry_date = ' 12/31/2099 ';
GO
------Create a mirrored endpoint for the server instance with the certificate created
--drop ENDPOINT endpoint_mirroring
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 = PARTNER
)
GO
------Back up the certificates separately and then interchange (that is, the certificates created on the primary server are guaranteed to be present on the mirror and the witness server, and the certificates created on the primary and the witness servers exist on the mirror server;
--The certificate created on the primary and mirror servers exists on the witness server
BACKUP CERTIFICATE host_a_cert to FILE = ' F:\Host_A_cert.cer '
------Create a login name for each of the other two servers on the respective server
Use master;
--drop LOGIN Host_b_login
CREATE LOGIN host_b_login with PASSWORD = ' Wenzhong '
GO
Use master;
--drop LOGIN Host_c_login
CREATE LOGIN host_c_login with PASSWORD = ' Wenzhong '
GO
------Create a user who uses the login created above
--drop USER Host_b_user
CREATE USER host_b_user for LOGIN Host_b_login;
GO--drop USER Host_c_user
CREATE USER host_c_user for LOGIN Host_c_login;
GO
------Associate a certificate with a user
--drop CERTIFICATE Host_b_cert
CREATE CERTIFICATE Host_b_cert
AUTHORIZATION Host_b_user
From FILE = ' f:/host_b.cer '
GO
--drop CERTIFICATE Host_c_cert
CREATE CERTIFICATE Host_c_cert
AUTHORIZATION Host_c_user
From FILE = ' f:/host_c.cer '
GO
------Grant the logon name to the connection (Connect) permission for the remote mirroring endpoint
GRANT CONNECT on endpoint::endpoint_mirroring to [Host_b_login]
GO
GRANT CONNECT on endpoint::endpoint_mirroring to [Host_c_login]
GO

-----------------Avoid "orphaned users." The disadvantage of the mirror server is that it does not automatically maintain logins and requires manual maintenance.
-------find the appropriate user name and the corresponding SID number in the primary server
Use master
Select Sid,name from syslogins
-------Create the corresponding user name and SID on the mirror server (on standby)
Use master
EXEC sp_addlogin
@loginame = ' <LoginName> ',
@passwd = ' <Password> ',
@sid = <sid>
-------can back up the primary database and log and restore operations at this time, or you can perform backup and restore operations before the official start (SQL see above)


-----------------------------------------------------------------------------Last
--------set the primary database to its partner on the mirror server (must be executed first, the order cannot be reversed)
ALTER DATABASE testmirroring
SET PARTNER = ' tcp://192.168.0.211:5022 '
GO
--------set the mirror server to its partner on the primary database server
ALTER DATABASE testmirroring
SET PARTNER = ' tcp://192.168.0.111:5022 '
GO
--------set up the witness server on the primary database server.
ALTER DATABASE testmirroring
SET WITNESS = ' tcp://192.168.0.192:5022 '
GO

-----------------------------------------------------------------------------whether the settings were tested successfully
--------because of the disadvantage of mirroring: data cannot be queried on the mirror server. Need to test if it can be successful. (The database replication feature is available)
--------can indirectly read a mirrored database at a point in time by creating a database snapshot on the mirrored database
--------Test Process:
Execute on--------Host:
Use master;

ALTER DATABASE testmirroring SET SAFETY full;-----switch to high-safety mode otherwise performing manual switchover will fail

GO
ALTER DATABASE testmirroring SET PARTNER FAILOVER---Manual primary and Standby switchover
Execute on------mirror server:
Use master;
ALTER DATABASE testmirroring SET PARTNER force_service_allow_data_loss---Perform a forced switchover on the mirror (when the primary server data goes down)


-------if the original primary server resumes, you can continue to work and need to re-set the mirror
Execute on----standby (mirror server):
--Restore image
Use master;
ALTER DATABASE testmirroring SET PARTNER RESUME
--Switch Master and Standby
ALTER DATABASE testmirroring SET PARTNER FAILOVER
------------------------Removing Database Mirroring
ALTER DATABASE testmirroring SET PARTNER OFF
-----------pausing a database mirroring session
ALTER DATABASE testmirroring SET PARTNER SUSPEND
-----Recovering a Database mirroring session
ALTER DATABASE testmirroring SET PARTNER RESUME
ALTER DATABASE testmirroring SET PARTNER SUSPEND
-----Close the witness server
ALTER DATABASE testmirroring SET WITNESS OFF





/*

By default, the transaction security level is set to full, which is synchronous run mode, and SQL Server 2005 Standard Edition only supports synchronous mode.
Turning off transaction security switches the session to asynchronous run mode, which enables optimal performance.
*/
--transaction security, synchronous mode
Use master;
ALTER DATABASE testmirroring SET PARTNER SAFETY full
--transaction insecure, asynchronous mode
ALTER DATABASE testmirroring SET PARTNER SAFETY OFF;

--------in high-performance mode, the witness server has an adverse effect on availability. If the witness is configured for a database mirroring session, the principal server must be connected to at least one other server instance.
-Either the mirror server or the witness server, or connect to both servers. Otherwise, the database will not be available and the service cannot be forced (data may be lost).
-Therefore, for high-performance mode, it is recommended that the witness server always be set to OFF.
--The unique role of the witness server is to support automatic failover. is not available for the database, it is an optional instance of SQL Server.
--It enables the mirror server in a high-security mode session to identify whether to initiate an automatic failover (the role of the witness is to initiate an automatic failover).
ALTER DATABASE testmirroring SET PARTNER OFF

/*
Conditions required for automatic failover

A, the database mirroring session must be running in high-security mode, and the witness server must be processed.
B, the mirror database must already be synchronized. This guarantees that all logs sent to the mirror server have been written to disk.
C, the principal server has interrupted communication with the rest of the database mirroring configuration, while the mirror server and the witness will retain the quorum. However, if all server instances have interrupted communication,
Automatic failover does not occur when the witness and the mirror server re-establish communication later.
D, the mirror server has detected that the principal server has been lost
E, the way the mirror server detects the failure of the principal server depends on whether the fault is a hard fault or a soft fault.

Principle of automatic fail-over

A. If the principal server is still running, change the state of the principal database to disconnected and disconnect all clients from the principal database.
B, the witness server and the mirror server register the principal server as unavailable.
C, if there are any waiting logs in the redo queue, the mirror server will complete the operations of rolling forward the mirrored database
D, the previous mirror database, as the new online principal database, restores all of these transactions by rolling back the uncommitted transactions as soon as possible. Locks will isolate these transactions.
E, when a principal server is re-joined to a session, it will assume that its failover partner now has a principal role. The previous principal server takes over the mirrored role and takes its database as a mirrored database.
The new mirror server synchronizes the new mirror database with the principal database as soon as possible. After the new mirror server resynchronize the database, it can perform a failover again, but in reverse:
*/

--------------------Epitaxy
-----using ADO or SQL Native client to automatically connect to a failover partner, the connection string is as follows:
connectionstring= "Datasource= A; Failover partner=b;initial catalog=adventureworks;integrated security=true;

--If there is no mirror server construction, or the environment can not implement the construction of the mirror server. You can implement a mirroring-like function as in the following code

-----C # code


Imports System.Data.SqlClient
Imports System.Data

Public Class Dbconn
Private primaryserverlocation as string= "SERVER=PRIMARYADDRESS;DATABASE=YOURDB; User Id=youruserid; Password=yourpassword; "
Private secondaryserverlocationasstring= "SERVER=SECONDARYADDRESS;DATABASE=YOURDB; User Id=youruserid; Password=yourpassword; "


Public SqlConnection Assqlconnection
Public cmd Assqlcommand

Public Sub primaryconnection ()
Try
SqlConnection = New System.Data.SqlClient.SqlConnection (primaryserverlocation)
cmd = NewSystem.Data.SqlClient.SqlCommand ()

' Test connection
Sqlconnection.open ()
Sqlconnection.close ()
Catch ex as Exception
Secondaryconnection ()
End Try
End Sub

Public Sub secondaryconnection ()
' Used as the failover secondary serverif primaryis down.
Try
SqlConnection = New System.Data.SqlClient.SqlConnection (secondaryserverlocation)
cmd = NewSystem.Data.SqlClient.SqlCommand ()

' Test connection
Sqlconnection.open ()
Sqlconnection.close ()
Catch ex as Exception
End Try
End Sub


-----C # code
--================= View the configuration status of database mirroring =================

--1. View the principal database, mirror database status through Management Studio Object Explorer
--2. View status through Database properties in Management Studio Object Explorer
--3. View database mirroring configuration through the system catalog view

Use master
Go
SELECT * FROM sys.database_mirroring_endpoints
SELECT * from sys.database_mirroring
WHERE database_id = (SELECT database_id from sys.databases
WHERE name = ' testmirroring ')
SELECT * from sys.database_mirroring_witnesses






There are three modes of operation of the image:

1. High performance (asynchronous): Commit the changes on the primary server before transferring them to the mirror server.

2. High security without automatic failover (synchronous): The process always submits changes on the primary service and the mirror server.

3. High security with automatic failover (synchronous): Requires a witness server instance. If both the primary server and the mirror server are available, commit the changes made above them and mirror them. If the primary server is unavailable, the witness will control automatic failover to the mirror server.

SQL SERVER2008 Mirroring All Raiders