MS SQL2005 Database Mirroring Setup

First, the preparatory work:

3 servers with the same version, hard disk partition size, install the same version of the database software. 3 server IP and host names are labeled in host. The database is created on the principal server and the database and database transactions are fully backed up. Copy the backup file to the mirror server to restore, restore overwrite the original database, do not do anything to the transaction. After successful construction, use the website to test its usability. (the database is added after creating the database image: Create the database, then overwrite the database file created by the database file, use the offline method to overwrite, create the connection database account to delete the original account and then create.) Second, the mirror server can not automatically manage the connection database account, need to execute on the mirror server: use master; exec sp_addlogin@loginame = ' sql_2_login ',    // Website Connection Database Account @passwd = ' [email protected] ',    //website connection database Password @sid =  0xc28f0312bafbe84ab553c40cfad2a32a ;  //The host server is configured on the Site Connection database account SID number on the principal server: Use Master;select sid,name from syslogins;    //View login account   Statements executed on principal server: Use Master; create master key encryption by password = ' [email  Protected] '; Create certificate sql_1_cert with subject = ' Sql_1 certificate ', start_date= ' 08/20/2014 ', expiry_date = ' 08/ 20/3000 ';  create ENDPOINT endpoint_mirroring    state = STARTED     as TCP (listener_port= 10000,LISTENER_IP = All)      for database_mirroring (  authentication = Certificate sql_1_cert  , encryption = REQUIRED algorithm aes , ROLE = partner)   backup certificate Sql_1_cert to file= ' d:\sql_1_cert.cer '; &n Bsp Use Master;create login sql_2_login with password= ' [email protected] '; Use Master;create login sql_3_login with password= ' [email protected] '; Create user sql_2_user for login sql_2_login; Create user Sql_3_user for login sql_3_login;create Certificate Sql_2_cert authorization Sql_2_user from file= ' D:\sql_2_c Ert.cer '; Create certificate Sql_3_cert authorization Sql_3_user from file= ' D:\sql_3_cert.cer '; Grant Connect on Endpoint :: endpoint_mirroring to Sql_2_login;grant Connect in endpoint::endpoint_mirroring to Sql_3_login; use master; Select Sid,name from syslogins;    //View login account  alter Database mydb set SAFETY full alter database mydb set partner= ' tcp://sql-2:10000 ';    //Execute  alter database mydb set witness= ' tcp://sql-3:10000 ' after the mirror server executes;   &NBSP;//executes the statement after executing the above statement     the mirror server executes the statements: Use Master; create MasterKey encryption by Password = ' [email protected] '; Create certificate sql_2_cert with subject = ' sql_2 certificate ', STA Rt_date= ' 08/20/2014 ', expiry_date = ' 08/20/3000 ';  create ENDPOINT endpoint_mirroring    state = STARTED & nbsp;   as TCP (listener_port=10000,listener_ip = All)      for database_mirroring (  AUTH entication = Certificate sql_2_cert , encryption = REQUIRED algorithm aes , ROLE = partner)   backup Certificate Sql_2_cert to file= ' D:\sql_2_cert.cer ',  use master;create login sql_1_login with password= ' [email  protected] '; Use Master;create login sql_3_login with password= ' [email protected] '; Create user sql_1_user for login sql_1_login; Create user Sql_3_user for login sql_3_login;create Certificate Sql_1_cert authorization Sql_1_user from file= ' D:\sql_1_c Ert.cer '; Create certificate Sql_3_cert authorization Sql_3_user from file= ' D:\sql_3_cert.cer '; Grant Connect on Endpoint :: Endpoint_mirroring toSql_1_login;grant Connect on endpoint::endpoint_mirroring to Sql_3_login; use master; exec sp_addlogin@loginame = ' Sql_2_login ',    //website Connection Database Account @passwd = ' [email protected] ',    //website connection database Password @sid =   0xc28f0312bafbe84ab553c40cfad2a32a;  //The Site Connection database account configured on the principal server SID number  alter mydb set partner= ' tcp://sql-1:10000 '; ALTER DATABASE mydb SET SAFETY full  statement executed on the witness server: Use Master; create master key encryption by password = ' [email&n Bsp;protected] '; Create certificate sql_3_cert with subject = ' Sql_3 certificate ', start_date= ' 08/20/2014 ', expiry_date = ' 08/20/3000 ';  create ENDPOINT endpoint_mirroring    state = STARTED     as TCP (listener_p ORT=10000,LISTENER_IP = All)      for database_mirroring (  authentication = Certificate SQL_3_CERT&N Bsp , encryption = REQUIRED algorithm aes , ROLE = WITNESS)   backup certificate sql_3_cert to file= ' d:\sql_3_ Cert.cer ';  use master;create LoGin Sql_1_login with password= ' [email protected] '; Use Master;create login sql_2_login with password= ' [email protected] '; Create user sql_1_user for login sql_1_login; Create user Sql_2_user for login sql_2_login;create Certificate Sql_1_cert authorization Sql_1_user from file= ' D:\sql_1_c Ert.cer '; Create certificate Sql_2_cert authorization Sql_2_user from file= ' D:\sql_2_cert.cer '; Grant Connect on Endpoint :: endpoint_mirroring to Sql_1_login;grant Connect in endpoint::endpoint_mirroring to sql_2_login;   someone would say, Two databases, the IP address is different, how to write the connection code? Do you want to manually change the code after a failure? In fact, using ADO or SQL Native client to automatically connect to the failover partner, the connection string is as follows:  connectionstring= "Datasource= A; Failover partner=b;initial catalog=adventureworks;integrated security=true; " Datasource= a;  This setting, the client can automatically switch the database   The mirrored high-availability configuration instance for SQL Server 2008 is complete.  <connectionStrings>    <add name= "Defaultdb" connectionstring= "Data source=192.168.1.104; Failover partner=192.168.1.106;initial catalog=imagetest; User Id=sA password=1234; "Providername=" System.Data.SqlClient "/>  </connectionstrings>192.168.1.104 is the Lord, 192.168.1.106 is a mirror image.  -------------------------Test--------------------------------1, primary and standby interchange  --host to stop the SQL service   --2, Primary server down, standby emergency start and start service-standby machine execution: use master; ALTER DATABASE s_c_sc SET PARTNER force_service_allow_data_loss; ALTER DATABASE S_C_SC Set online  --3, open the host's SQL service, the original primary server recovery, can continue to work, need to re-set the mirror-standby machine execution: use master; ALTER DATABASE s_c_sc SET PARTNER RESUME; --Restore mirror alter DATABASE S_C_SC SET PARTNER FAILOVER; --Switch to host   --4, the original primary server recovery, can continue to work on whether the settings are successfully tested  --------due to the disadvantage of mirroring: data cannot be queried on the mirror server. Need to test if it can be successful. (database replication is available)  --------by creating a database snapshot on a mirrored database, you can indirectly read a mirrored database at a point in time  --------test process:   Execute on--------Host:    use master;       alter DATABASE testmirroring SET SAFETY full;-----switch to high-safety mode otherwise performing manual switchover fails     go   alter DATABASE testmirroring SET PARTNER FAILOVER  ---Manual primary and Standby switchover   performed on------mirror server:  & nbsp Use master;      alter DATABASE testmirroring SET PARTNER force_service_allow_data_loss---Perform a forced switchover on the mirror machine ( When primary server data is down)      -------if the original primary server resumes, you can continue to work and need to reset mirroring  ----standby (mirror server) to perform:  --Restore image        use master;      alter database testmirroring SET PARTNER RESUME    --switch main standby   alter database T Estmirroring SET PARTNER FAILOVER   ------------------------Delete database mirroring    ALTER DB Testmirroring set PARTNER OFF  -----------Pause Database mirroring session     alter DB testmirroring SET PARTNER SUSP END   -----Recovering a Database mirroring session     alter 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.   Turn off transaction security to switch the session to asynchronous run mode, which allows for optimal performance.  */ --transaction Security, Sync mode     use master;      alter DATABASE testmirroring SET PARTNER SAFETY full   --transaction insecure, asynchronous mode    & nbsp 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 connect to at least one other server instance,  --  The mirror server or witness, 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, we recommend that you always set the witness server to OFF. The only 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      /*    automatic failover requirements       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, and the mirror server and witness will retain the quorum. However, if all server instances have interrupted communication,      The witness and mirror servers reestablish communication later, automatic failover does not occur.  &nBsp D, the mirror server has detected the loss of the principal server    E, the way the mirror server detects the failure of the principal server depends on whether the fault is a hard or soft fault.      automatic failover principle      A, if the principal server is still running, change the state of the principal database to disconnected and disconnects all client connections to 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 completes the roll forward of the mirrored database    D, the previous mirror database as the new online principal database, and 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 will synchronize 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;    --without the construction of the mirror server, or the environment can not achieve the construction of the mirror server. Similar mirroring capabilities can be implemented with 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  & nbsp;        sqlConnection = New System.Data.SqlClient.SqlConnection (primaryserverlocation)            cmd = NewSystem.Data.SqlClient.SqlCommand ()          & nbsp   ' Test connection           Sqlconnection.open ()         &NB Sp Sqlconnection.close ()       Catch ex as Exception           Secondaryco Nnection ()       End Try   end Sub      public Sub secondaryconnection ()       ' used as the failover secondary serverif prim Aryis down.       Try           sqlConnection = New System.Data.SqlClient.SqlConnection (secondaryserverlocation)           cmd = NewSystem.Data.SqlClient.SqlCommand ()              ' Test connection            Sqlconnection.open ()           Sqlconnection.close () & nbsp;     Catch ex as Exception       End Try   end Sub    &nb sp;  -----C # code  --================= to view the configuration status of database mirroring =================     --1. From Management Studio Object Explorer, view the principal database, mirror database state   --2. View the status   --3 through the database properties in Management Studio Object Explorer. 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         & nbsp   WHERE name = ' testmirroring ')    SELECT * from sys.database_mirroring_witnesses     &nbsp There are three modes of operation for;           mirroring:    1, High Performance (asynchronous): Commit the changes on the primary server first, It is then transferred to the mirror server.    2, High Security (synchronous) without automatic failover: 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.  

MS SQL2005 Database Mirroring setup