Servers

Principle server main.db.local 192.168.100.1
Mirror server mirror.db.local 192.168.100.2

[wp_ad_camp_1]

Steps on build SQL Server mirroring

  1. Jion Windows servers into same Domain/Active directory
    • Active Directory or Domain is not necessary. BUT DB service and Agent service must run with same account, which means, the account name and password MUST be same.
    • Without Domain/AD, we need specify DNS suffix on all DB servers, and add all “computer name + DNS suffix” into hosts file for all DB servers. restart servers after changes.
      dnssuffix” />

      # hosts file
      192.168.100.1  main.db.local
      192.168.100.2  mirror.db.local
  2. Turn off firewall, or let TCP port 5022 and 1433 reachable for other servers. Check with command “telnet <other_server> 5022”.
  3. Shutdown BB first, Databases must be in recovery mode.
    use master;
    ALTER DATABASE BBLEARN SET RECOVERY FULL;
    ALTER DATABASE BBLEARN_admin SET RECOVERY FULL;
    ALTER DATABASE BBLEARN_cms SET RECOVERY FULL;
    ALTER DATABASE BBLEARN_cms_doc SET RECOVERY FULL;
    ALTER DATABASE BBLEARN_stats SET RECOVERY FULL;
  4. Backup DB from main server and restore to mirror DB server with *RESTORE WITH NORECOVERY* 
  5. Create certificate for “main” server and “mirror” server.
    • “main” server
      use master;
      CREATE MASTER key encryption by password ='password';
      CREATE CERTIFICATE MAIN_cert WITH SUBJECT = 'MAIN certificate' , start_date='01/01/2014';
      BACKUP CERTIFICATE MAIN_cert TO FILE = 'c:\main_cert.cer';
    • “mirror” server
      use master;
      CREATE MASTER key encryption by password ='password';
      CREATE CERTIFICATE MIRROR_cert WITH SUBJECT = 'MIRROR certificate' , start_date='01/01/2014';
      BACKUP CERTIFICATE MIRROR_cert TO FILE = 'c:\mirror_cert.cer';
  6. Recreate endpoint for mirroring on both servers;
    • Check endpoint on both servers
      use master;
      select * from sys.endpoints;

      [wp_ad_camp_3]
      Output for endpoint:

      name endpoint_id principal_id protocol protocol_desc type type_desc state state_desc is_admin_endpoint
      EP_Mirroring 65540 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0
    • Drop and create endpoint on “main” server
      DROP endpoint EP_Mirroring;
      CREATE endpoint EP_Mirroring state=STARTED as tcp (listener_port=5022) for database_mirroring(AUTHENTICATION = CERTIFICATE MAIN_cert, ENCRYPTION = required algorithm AES, role=ALL);
    • Drop and create endpoint on “mirror” server
      DROP endpoint EP_Mirroring;
      CREATE endpoint EP_Mirroring state=STARTED as tcp (listener_port=5022) for database_mirroring(AUTHENTICATION = CERTIFICATE MIRROR_cert, ENCRYPTION = required algorithm AES, role=ALL);
  7. Exchange certificate, create login, link certification with login
    • Exchange certificates and Copy certificate to c:\
    • “main” server
      CREATE LOGIN MIRROR_login WITH PASSWORD = 'password';
      CREATE USER MIRROR_user for LOGIN MIRROR_login;
      CREATE CERTIFICATE MIRROR_cert AUTHORIZATION MIRROR_user FROM FILE='c:\mirror_cert.cer';
      GRANT CONNECT ON ENDPOINT::EP_Mirroring TO [MIRROR_login];
    • “mirror” server
      CREATE LOGIN MAIN_login WITH PASSWORD = 'password';
      CREATE USER MAIN_user for LOGIN MAIN_login;
      CREATE CERTIFICATE MAIN_cert AUTHORIZATION MAIN_user FROM FILE='c:\main_cert.cer';
      GRANT CONNECT ON ENDPOINT::EP_Mirroring TO [MAIN_login];
  8. Enable DB mirroring
    • set DB partner from mirror server first
      ALTER DATABASE myDB SET PARTNER = 'TCP://main.db.local:5022';
    • Then on main db server, enable mirroring
      ALTER DATABASE myDB SET PARTNER = 'TCP://mirror.db.local:5022';
  9. If there is witness server, do create certificate and following steps on witness server.http://msdn.microsoft.com/en-us/library/ms179306.aspx