Chapter three--the second part--fourth article configuring SQL Server Mirroring--Non-domain environment

Text: Third--Part II--the fourth article configuring SQL Server Mirroring--Non-domain environment

In this paper, the non-domain environment to build a mirror demonstration, for the domain environment, can be referenced above: http://blog.csdn.net/dba_huangzj/article/details/28904503 Source: http://blog.csdn.net /dba_huangzj/article/details/27652857

The image set in the domain environment is shown earlier in this paper, and this article uses a non-domain environment to build the image, as well as the High security mode (synchronous) without the witness, then demonstrates the asynchronous pattern, and finally demonstrates the high security mode with the witness server.

Prepare the Conditional server

Server role	Machine name/Instance Name	Version	Ip
Principal server	Repa	Windows Server 2008R2 English x64	192.168.1.2
mirror server	Repb	Windows Server 2008R2 English x64	192.168.1.3
Witness server	Win7	WIN7 Enterprise Edition	192.168.1.4

Note: Rep is an abbreviation for replication (copy), Repa and REPB are built to do a copy demo, this article borrows 3 servers.

SQL Server

are using SQL Server R2 Enterprise Edition English X64

Demo Database

Adventureworks2008r2

First step: Check the Environment

Because in a non-domain environment, so the need to do a lot more checks, following the demo environment, test the following conditions:

1. Windows account.
2. Whether the network can be connected, and the port is available.
3. The disk configuration of the principal server and the mirror server is correct.
4. Whether the SQL Server version or patch meets the mirroring requirements.
5. Recovery model, compatibility level for SQL Server databases.
6. Whether there are regular backup jobs on SQL Server, especially log backups.
7. Whether the principal server and the mirror server can interoperate with SQL Server.
8. Whether there are shared folders on the principal server and the mirror server.

Windows account:

In the image, the main Windows account is in the shared folder, the non-domain environment requires a certificate to set up the image, in addition, for the small library, the general use of backup restore, that is, you need to transfer the backup file on the principal database to the mirror server, These all require Windows account operations to share folders. For the convenience of demonstration, this article uses administrator as the Windows account, as a best practice, it is recommended to use a dedicated Windows account when actually building, and to ensure that sufficient permissions are available.

The network is connected, and the port is available:

Non-single-use high-availability is heavily dependent on the network, the network does not pass, everything is useless. So first of all make sure the network is able to exchange visits. Let's test whether the principal and mirror servers used in this example can exchange visits:

Ping REPB on Repa (This example IP address 192.168.1.3)

Ping Repa on REPB (This example IP address 192.168.1.2)

Visible is can ping through, for convenience, this example has turned off the firewall, so the port problem does not need to check, if in the production environment, you need and network administrator to confirm whether the port has been turned on. Check the port to use the Telnet command. If you enter Telnet, the following error occurs:
English: ' Telnet ' is not recognized as a internal or external command, operable program or batch file.

Chinese: ' Telnet ' is not an internal or external command, nor a program or batch file that can be run.

You can find and click "Turn Windows features on or off" in the "start" → "Control Panel" → "Programs", "programs and Features" to enter the Windows Feature Settings dialog box. Locate and tick "Telnet client" and "Telnet Server", and finally "OK". Depending on the version, the opening method will be different, the specific version of the search engine to find the solution.

The disk configuration of the principal and mirror servers is correct:

In a formal environment, often there is not only one disk, this example due to the physical machine resource constraints, so only the system disk, that is, the C drive to do the demo. The following first checks the drive letter and directory where the data files and log files of the demo library (ADVENTUREWORKS2008R2) are located on the principal server (REPA):

Use master go SELECT  physical_name--physical file path from    sys.master_files WHERE   database_id = db_id (' Adventureworks2008r2 ')

The results of this example are as follows:

Next to the mirror server, that is, REPB to check for the presence of this drive letter and directory, if it does not exist, to create manually. The following is a manually created folder:

Be aware that when you restore later, check that the file path also points to the same directory when you restore. The file name should also be consistent.

Whether the SQL Server version or patch meets the mirroring requirements:

This example uses the same installation files, both 2008r2 (OS and SQL), and does not have a networked update, so you can basically ensure that the versions and patches are consistent. If the formal environment, need to consider, although from 2005 SP1 to support the image, but the true full support mirroring function is from 2005 SP2, in addition to the SQL Server version, Windows version, patch to check, Although it is not exactly specified that the OS must be exactly the same, the consistent version is less unusual.

Recovery model, compatibility level for SQL Server databases:

Check the recovery model and compatibility level, which can be implemented using the following statement:

Use master go SELECT  name [database name],         recovery_model_desc [recovery mode], case when         [compatibility_level] = all Then ' 200  5 ' When              [compatibility_level] =              [Compatibility_level] > ' 2008+ '              ELSE ' or ' Lower Version '         END [compatibility level] from    sys.databases WHERE   name = ' ADVENTUREWORKS2008R2 '

In this example, the demo library is in simple mode, so modify it with SSMs or the command:

SSMs Modification:

T-SQL Modification:

Use [master] GO ALTER DATABASE [adventureworks2008r2] SET RECOVERY full with no_wait GO

I recommend the use of T-SQL modification, because when the server is busy, the use of graphical interface operation will be very slow or even timeout. And a DBA should use these T-SQL commands. Otherwise it would be too unprofessional.

The check script is executed again, and the visible recovery mode has been changed back to full:

Is there a regular backup job on SQL Server, especially log backups:

This step does not do a demonstration, open the SQL Server Agent can be checked, the other person should have to build a mirror to see if there is a regular backup capability.

Whether the principal server and the mirror server are SQL Server interoperability:

In the second step, the main is to check the OS network, but the OS connectivity does not mean that SQL Server can connect, so it is necessary to check whether SQL Server can be interconnected. The method is simple, open ssms separately, and enter the SQL Server ip/instance name for the partner server. This example uses SA first to check:

Connect the REPB on the Repa:

Connect the Repa on the REPB:

Whether the principal server and the mirror server have shared folders:

As mentioned earlier, in a non-domain environment, you need to use a certificate to set up the image, and also need to transfer the backup files, these will be used to the shared folder, of course, can be implemented in other ways, but the shared folder may be the simplest way. In this example, I will establish a shared folder on the principal server (REPA) so that REPB can access it. However, if conditions permit, I would recommend creating shared folders on fault-tolerant disks, such as RAID, San, and so on, even if the principal server crashes, and does not affect the operation of the mirror server on the shared folder.

Now for a simple operation: Create a folder:

Grant Everyone read and Write permissions:

Again, for the formal environment, it is highly recommended to use a dedicated account and to control permissions appropriately, such as allowing full control of the folder during the construction process, but only allow "read" operation when the official operation.

Build success:

Check to see if you can access:

This step can be entered in REPB, in the UNC path, as in this example: \\RepA\ShareFolders

So far, the preparatory work has been completed. The second step begins below.

Step Two: Configure the image with a certificate and back up the restore database

In this step, we will do two things, the first is to use the certificate to configure the image, and the second is to restore the database. In a non-domain environment, a certificate must be used to build the image, so I put the build certificate in the first step. Some data will put the backup restore operation before the certificate is built, but according to personal experience, when the disk IO, network performance is poor, backup, transfer, restore will waste a lot of time (personal operation over 2 hours), and during the period the server is almost unable to operate. At such times, I will choose to set up first, then restore, and then immediately synchronize.

To create a certificate:

If the server uses the local system as the SQL Server service account, you will need to use certificate authorization. Certificate authorization can also be used when your server is unable to access the other server through the account of another server or if you do not want to authorize Windows login.

To use a certificate to build the image, follow these steps:

1. Create the Database master key (if the master key does not exist).
2. Create the certificate in the master database and encrypt it with the master key.
3. Create an endpoint (endpoint) with certificate authorization.
4. The backup certificate becomes a certificate file.
5. Create a login account on the server to provide additional instance access.
6. Create a user in the master library and map to the login account in the previous step.
7. Grant the certificate to these users.
8. Authorized on the endpoint.
9. Sets the mirror partner for the principal server.
10. Sets the principal partner of the mirror server.
11. Configure the witness server.

Step 1: Create a database master key

The use of the master key is used here to encrypt the certificate, although the master key is not the only one that works. The password and storage protection of the database master key should be careful, this is the strength level of the object, the impact is very wide. You can use the following statement to create:

Use master GO to CREATE Master KEY encryption by PASSWORD = ' pa$ $w 0rd '; /*--Delete master key use Master;drop master key*/

Use the same method to create the database master key on the mirror server.

Step 2: Create a certificate and encrypt it with the master key

When you create a certificate, the default expires one year after the creation date, so be aware of its expiration time for the creation of the certificate. The following is the creation of the Host_a_cert certificate created on the principal server

Use master GO to CREATE CERTIFICATE host_a_cert  with Subject = ' host_a CERTIFICATE ', expiry_date = ' 2015-1-1 ';--Expiration date/*- -Delete certificate use Master;drop CERTIFICATE host_a_cert*/

Implement the creation of the Host_b_cert certificate on the mirror server using the same method

Step 3: Create an Endpoint

You can use the following code to create an endpoint in the principal server, and specify the use of 5022, port, port in the mirror configuration process does not enforce the use of a specific port (or a specific port, such as 1433 except).

--Create endpoint IF not EXISTS with Host_a_cert certificate (SELECT  1 from    sys.database_mirroring_endpoints)     BEGIN         CREATE ENDPOINT [databasemirroring] state = STARTED as TCP (Listener_port = 5022,             listener_ip = all) for Database_mi Rroring (authentication =             CERTIFICATE Host_a_cert, encryption = REQUIRED algorithm AES, ROLE = all             );     END

Modify the title of the mirror server bluff to create the endpoint of the mirror server.

Step 4: Backup certificate

The purpose of the backup certificate is to send to another server and import the certificate so that other servers can access the server through the certificate (principal server).

BACKUP CERTIFICATE host_a_cert to FILE = ' C:\ShareFolders\Host_A_Cert.cer ';

Similarly, repeat on the mirror server, paying attention to the title and path of the card. After the backup, you can see a CER file on the destination folder:

Here is a suggestion, to create a separate folder certifications in Repa and REPB, and then to store the certificate of the server and the partner server, it is not reasonable that the certificate is kept in the shared folder. This example creates a certifications folder on the local C drive and stores all the certificates.

Step 5: Create a login account

To create a separate server login account for each server, just create a login to the mirror server:

CREATE LOGIN host_b_login with PASSWORD = ' pa$ $w 0rd ';

Similarly, create Host_a_login to the principal server on the mirror server.

Step 6: Create the user and map to the login account created in step 5

Run on the principal server:

CREATE USER host_b_user for Login Host_b_login;

Similarly, the mirror server is also created.

Step 7: Use a certificate to authorize users

Create a new certificate, import it using a certificate copied from the partner server, and then map the account in step 6 to the new certificate.

CREATE CERTIFICATE host_b_cert AUTHORIZATION host_b_user from FILE = ' C:\Certifications\Host_B_Cert.cer ';

Note the same is true on the mirror server.

Step 8: Authorize the access port for the login account in step 5

GRANT CONNECT on endpoint::[databasemirroring] to [host_b_login];

The mirror server is the same.

So far, the steps to configure mirroring are complete, and the configuration scripts that are as automated as possible will be given later.

Backup RESTORE Database:

This step, backup and restore the demo database on the principal server (REPA) to REPB for initialization:

1. Full backup adventurework2008r2 to shared folder C:\ShareFolders
2. Copy the backup file to the mirror server (if the permissions are sufficient, use the shared path directly to restore)
3. Restore ADVENTUREWORK2008R2 to the mirror server with the Nonrecovery option (REPB)
4. Log backup adventurework2008r2, and revert to REPB in the same way

Step 1: Full backup:
Step 2: Restore the database on the mirror server (REPB) and use the Nonrecovery method:

Note the path and the restored file name:

Step 3: Backup and Restore logs:

Also restore in Nonrecovery mode:

Step three: Start mirroring

The first two steps are mainly for the configuration of the mirror, the following begins the official boot image:

Step 1: Right-click the principal database of the principal server and select "Mirror"

Step 2: Select "Configure Image": This step we mainly get the network address of the principal server, see the Red box part

Step 3: Execute the following script on the mirror server (REPB):

Note the order, first to execute on the REPB

ALTER DATABASE adventureworks2008r2     SET PARTNER = ' tcp://repa:5022 '; GO

Step 4: Execute the following script on the principal server (REPA) to add REPB to Repa's partner

ALTER DATABASE adventureworks2008r2     SET PARTNER = ' tcp://repb:5022 '; GO

After execution, you can see the mirroring configuration on the Repa:

Step 5: Toggle Mode

Step-In-the-middle build is built using high-safety mode, if you want to use high-performance mode (again, this example does not use the witness server, so you cannot use the high-safety mode of automatic failover), you can use the following script to implement on Repa:

ALTER DATABASE adventureworks2008r2     SET PARTNER SAFETY offgo

Open again, the visible run mode is already high-performance mode:

Step 6: Verify Failover

And then use the statement to test if you can fail over, first check the status of two libraries, here with a little trick, using "register the server",

Then create a new registration:

Similarly, add the REPB:

Then open a Query window to query two servers at once, provided that you have sufficient permissions, this example uses SA to connect:

Note that the pink part, if it appears (1/2) This situation, indicates that there is a server that cannot connect successfully:

The results are as follows: We focus on only a few things:

Now switch back to the Repa query window and enter:

ALTER DATABASE adventureworks2008r2 SET PARTNER failover;--is executed on the principal server

Then to the "registration manager" and then query, you can see now REPB is already principal, that is, the principal server:

The reader can operate with a GUI interface, so there is no more demonstrations.

Non-domain environment mirroring configuration with a witness server

The following shows how to add a witness to the mirror environment, first, we maintain the previous configuration, that is, to build the main body and the mirror server, and then we use a Win7 system to do the witness server, with SQL Server R2 Enterprise Edition, You can use Express or Workgroup Edition to make a witness server.

Step 1: Verify the network interoperability of the three servers, there is no cumbersome, the reader can refer to the previous method to check. Step 2: Create a master key, certificate, etc. on the witness server according to the previous steps:

--Create the master key use master; CREATE MASTER KEY Encryption by PASSWORD = ' pa$ $w 0rd '; --to demonstrate the need, otherwise do not set such a simple password go/*--delete the master key use master; DROP Master KEY */Use master; Create CERTIFICATE host_c_cert with SUBJECT = ' Host_c CERTIFICATE '--Creates a certificate on the Winess instance, named Host_c_cert, which is a description of the certificate, Expiry_ Date = ' 2015-6-5 ';--Certificate expiration time, can be set appropriately longer, specific to the actual need to set go/*-delete certificate use master;                         DROP CERTIFICATE Host_c_cert */CREATE ENDPOINT endpoint_mirroring state = STARTED as TCP (listener_port=5022 --Using port 5022, this port can be changed to an unused port, but all collaborators in the mirroring process should use the same port, listener_ip = All) for Database_       Mirroring (authentication = CERTIFICATE Host_c_cert--using certificates to authorize endpoints, encryption = REQUIRED algorithm AES , role = All-Indicates that the endpoint can act as any role, including the primary server, the mirror server, and the witness server.    See Books Online for details. ); go/*--Delete the mirroring endpoint IF EXISTS (SELECT * from sys.endpoints e WHERE e.name = N ' endpoint_mirroring ') drop Endpoint [Endpoint_mirr Oring] GO */BACKUP CERTIFICATE host_c_cert to FILE = ' C:\CertificatIons\host_c_cert.cer '; GO

Make sure that Repa, REPB, Win7 have 3 certificates generated by the subject, mirror, and witness on all three machines.

Create a certificate-authenticated account, user name, and endpoint for the principal and mirror server on the witness server.

--Create a login to the principal instance on the witness instance use master; CREATE LOGIN host_a_login with PASSWORD = ' pa$ $w 0rd '; GO--Create a USER host_a_user for login host_a_login to give this login name; GO--Let the account use certificate authorization CREATE CERTIFICATE host_a_cert    AUTHORIZATION host_a_user from    FILE = ' C:\Certifications\HOST_A _cert.cer ' GO-grants this new account Connection endpoint permission grant connect on endpoint::endpoint_mirroring to Host_a_login; go/*--Delete Account Drop Login Host_a_user * *-Create a login on the witness instance to the mirror instance use master; CREATE LOGIN host_b_login with PASSWORD = ' pa$ $w 0rd '; GO--Create a USER host_b_user for login host_b_login to give this login name; GO--Let the account use certificate authorization CREATE CERTIFICATE host_b_cert    AUTHORIZATION host_b_user from    FILE = ' C:\Certifications\HOST_B _cert.cer ' GO-grants this new account Connection endpoint permission grant connect on endpoint::endpoint_mirroring to Host_b_login; go/*--Delete Account drop login Host_b_user */

Execute the following statement in Repa and REPB, respectively, to create a connection endpoint permission for the witness server:

Use master; CREATE LOGIN host_c_login with PASSWORD = ' pa$ $w 0rd '; GO--Create a USER host_c_user for login host_c_login to give this login name; GO--Let the account use certificate authorization CREATE CERTIFICATE host_c_cert    AUTHORIZATION host_c_user from    FILE = ' C:\Certifications\HOST_C _cert.cer ' GO-grants this new account Connection endpoint permission grant connect on ENDPOINT::D atabasemirroring to Host_c_login; GO

Both logins should exist in REPB, and there should be host_b_login and host_c_login two accounts in the Repa:

Then execute the following statement on the principal server to join the witness server:

ALTER DATABASE adventureworks2008r2   SET WITNESS = ' tcp://win7:5022 '

After you have finished, open the REPA mirror configuration and see that the witness server has joined:

We can test, the REPA SQL Server service shutdown, to implement the principal server "failure" to see if REPB can automatically switch:

The first step is to check the status of the REPB:

The second step is to close the Repa service:

The third step is to refresh the status of the REPB:

Visible has switched past, and the status of disconnected, note that even if Repa online again, will not automatically switch to become the principal server, you need to manually switch, this part of the reader can self-test. After you start the Repa again, you can compare the status of the mirror and change from disconnected to synchronized.

So far, the mirrored configuration in the non-domain environment has been completed.

Chapter three--the second part--fourth article configuring SQL Server Mirroring--Non-domain environment