Article 3-Part 2-Article 4 configure an SQL Server image-non-Domain environment

This article builds the mirror demonstration for the non-domain environment, for the domain environment establishment, can refer to above: http://blog.csdn.net/dba_huangzj/article/details/28904503Source: http://blog.csdn.net/dba_huangzj/article/details/27652857

We have demonstrated how to set up an image in a domain environment. This article will use a non-Domain environment to set up an image. Similarly, we will first build an image in a high-security (synchronous) mode without a witness server, the asynchronous mode is demonstrated, and the high security mode with the witness server is demonstrated.

Prerequisites

Server role	Machine name/Instance name	Version	IP
Main server	RepA	Windows Server 2008R2 x64	192.168.1.2
Backup storage	RepB	Windows Server 2008R2 x64	192.168.1.3
Witness Server	Win7	Win7 Enterprise Edition	192.168.1.4

 

Note: Rep is the abbreviation of Replication. RepA and RepB are built for Replication demonstration at the beginning. This article uses these three servers.

SQL Server

SQL Server 2008 R2 Enterprise Edition X64

Demo Database

AdventureWorks2008R2

 

Step 1: Check the environment

 

In a non-domain environment, many checks are required. The following conditions are tested one by one based on the demo environment:

Windows Account:

In image building, Windows accounts are mainly involved in shared folders. certificates are required to build images in non-domain environments. In addition, for small libraries, Backup recovery is generally used, that is, you need to transfer the backup files in the primary database to the backup file on the backup file server. These files must be used in Windows accounts to operate shared folders. For ease of demonstration, this article uses Administrator as the Windows Account and as the best practice. We recommend that you use a dedicated Windows account and ensure that you have sufficient permissions.

Whether the network is connected and the port is available:

Non-standalone high availability is heavily dependent on the network, the network is disconnected, and everything is useless. Therefore, we must first ensure that the network can communicate with each other. The following tests whether the master server and backup server used in this example can communicate with each other:

Ping RepB on RepA (in this example, IP address 192.168.1.3)

Ping RepA on RepB (in this example, IP address 192.168.1.2)

It can be seen that the port can be pinged. For convenience, the firewall has been disabled in this example. Therefore, you do not need to check the port. If the problem occurs in the production environment, you need to check with the network administrator whether the port is enabled. You can run the Telnet command to check the port. If the following error occurs after entering Telnet:
'Telnet 'is not recognized as an internal or external command, operable program or batch file.

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

You can go to "start"> "Control Panel"> "program" and "programs and functions" and click "open or close Windows function" to go to the Windows function Setting dialog box. Find and check "Telnet client" and "Telnet Server", and then "OK ". The activation method varies depending on different versions. For specific versions, find the search engine solution.

Whether the disk configurations of the master server and backup server are correct:

In the formal environment, there is usually not only one disk. In this example, only the system disk is retained due to the resource restrictions of the physical machine, that is, the C disk is used for demonstration. Next, check the drive letters and directories of the data files and log files of the demo Library (AdventureWorks2008R2) on the primary 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, go to the mirror server, that is, RepB to check whether the drive letter and directory exist. If it does not exist, you need to manually create it. Here are the folders created manually:





Note that during subsequent restores, check whether the file path also points to the same directory during the restore. The file names must also be consistent.

Does the SQL Server version and patch meet the mirroring requirements:
This example uses the same installation files, both of which are 2008R2 (OS and SQL), and there are no network updates, so you can basically ensure that the versions and patches are consistent. If it is a formal environment, you need to consider that although mirroring has been supported since 2005 SP1, the real complete support for mirroring functions is from 2005 SP2. In addition to the SQL Server version, the version and patch of Windows must also be checked. The exact OS must also be exactly the same, but the consistent version will be less unusual.



Recovery mode and compatibility level of SQL Server database:
To check the recovery mode and compatibility level, you can use the following statement:

USE master
go
SELECT name [database name],
        recovery_model_desc [recovery model],
        CASE WHEN [compatibility_level] = 90 THEN '2005'
             WHEN [compatibility_level] = 100 THEN '2008'
             WHEN [compatibility_level]> 100 THEN '2008+'
             ELSE '2000 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 commands:

SSMS modification:


T-SQL modification:
USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY FULL WITH NO_WAIT
GO


I suggest using T-SQL modification, because when the server is relatively busy, using the graphical interface operation will be slow or even time out. And a DBA should use these T-SQL commands. Otherwise it would be too unprofessional.

Execute the check script again, and you can see that the recovery mode has changed back to Full:



Are there regular backup jobs on SQL Server, especially log backups:
This step is not a demonstration. You can check it by opening the SQL Server Agent. In addition, the person who builds the mirror should have the ability to see if there is a regular backup.

Can the SQL Server of the principal server and the mirror server communicate with each other:
In the previous second step, the network of the OS was mainly checked, but the fact that the OS can communicate does not mean that the SQL Server can communicate, so it is necessary to check whether the SQL Server can be interconnected. The method is very simple, open SSMS separately, and enter the SQL Server IP / instance name of the partner server. This example first uses SA to check:

Connect RepB on RepA:


Connect RepA on RepB:
 

Are there shared folders in the principal and mirror servers:
As mentioned earlier, in a non-domain environment, you need to use a certificate to build an image, and you need to transfer backup files. These will use shared folders. Of course, you can use other methods, but shared folders may be the simplest. the way. In this example, I will create a shared folder on the principal server (RepA) so that RepB can access it. However, if conditions permit, I recommend creating shared folders on fault-tolerant disks (such as RAID, SAN, etc.), so that even if the principal server crashes, it will not affect the mirror server's operation of the shared folders.

Now let's do it briefly: Create a folder:


Grant Read and Write permissions to Everyone:
Again, for a formal environment, it is strongly recommended to use a dedicated account and appropriately control permissions, such as allowing full control of the folder during the build process, but only allowing "read" operations during formal operation.



Build success:
Check if you can access:
In this step, you can enter the UNC path in RepB, as in this example: \\ RepA \ ShareFolders



So far, preparations have been completed. Let's start the second step.

 

Step 2: Use the certificate to configure the image, and back up and restore the database
In this step, we will do two things. The first is to configure the image using a certificate. The second is to back up and restore the database. In a non-domain environment, a certificate must be used to build the image, so I put the certificate in the first step. Some materials will put the backup and restore operation before the certificate is established, but according to personal experience, when the disk IO and network performance are not good, backup, transfer, and restore will waste a lot of time (personal operation over 2 hours), and The server was almost inoperable during this period. At this time, I will choose to build it first, restore it, and then synchronize immediately.

 

Create the certificate:
If the server uses Local System as the SQL Server service account, certificate authorization is required. Certificate authorization can also be used when your server cannot access the other server through the account of another server or you do not want to authorize Windows login.

The steps for using a certificate to build an image are as follows:

Step 1: Create the database master key
The use of the master key here is used to encrypt the certificate, of course, the master key does more than that. Be careful of the database master key's password and storage protection. This is an object of strength level and has a wide range of influences. You can use the following statement to create:

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa $$ w0rd';
 
/ *
-Delete the master key
USE master;
DROP MASTER KEY
* /


Create the database master key on the mirror server in the same way.

Step 2: Create a certificate and encrypt it with the master key
When creating a certificate, it expires one year after the creation date by default, so pay attention to the expiration time for certificate creation. Here is the creation of the HOST_A_cert certificate created on the "Principal Server"

USE master
GO
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
* /


Use the same method to create the HOST_B_cert certificate on the mirror server

Step 3: Create the endpoint
You can use the following code to create an endpoint in the principal server, and specify the use of 5022, the port, the port does not force the use of a specific port during the mirroring configuration process (except for occupied or specific ports such as 1433).

-Use the Host_A_Cert certificate to create the endpoint
IF NOT EXISTS (SELECT 1
                FROM sys.database_mirroring_endpoints)
    BEGIN
        CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP (LISTENER_PORT = 5022,
            LISTENER_IP = ALL) FOR DATABASE_MIRRORING (AUTHENTICATION =
            CERTIFICATE Host_A_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE =
            ALL);
    END


Modify the certificate name slightly on the mirror server to create the endpoint of the mirror server.

Step 4: Back up the certificate
The purpose of backing up the certificate is to send it to another server and import the certificate so that other servers can access this server (the principal server) through the certificate.

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

 
Similarly, repeat it on the mirror server, paying attention to the certificate name and path. After backup, you can see a cer file in the destination folder:




Here is a suggestion, create a separate folder Certifications locally in RepA and RepB, and then use it to store the certificate of this server and partner server. It is not reasonable to keep the certificate in the shared folder. This example creates a Certifications folder on the C drive of this machine and stores all certificates.



Step 5: Create a login account
Create a separate server login account for each server. Here you only need to create a login to the mirror server:

CREATE LOGIN Host_B_Login WITH PASSWORD = 'Pa $$ w0rd';

In the same way, Host_A_Login is created on the mirror server to the principal server.

Step 6: Create a user and map it to the login account created in Step 5.
Run on the principal server:

CREATE USER Host_B_User For Login Host_B_Login;

The same applies to the mirror server.

Step 7: Authorize users with certificates
Create a new certificate, import it using the certificate copied from the partner server, and map the account in step 6 to this new certificate.

CREATE CERTIFICATE Host_B_Cert
AUTHORIZATION Host_B_User
FROM FILE = 'C: \ Certifications \ Host_B_Cert.cer';

Note that the same is true on the mirror server.

Step 8: Authorize the login account in Step 5 to access the port
GRANT CONNECT ON ENDPOINT :: [DatabaseMirroring] TO [Host_B_Login];

The same goes for mirror servers.
At this point, the steps for configuring the image have been completed, and subsequent configuration scripts will be given as much as possible.



Backup restore database:
In this step, the demo database on the principal server (RepA) is backed up and restored to RepB for initialization:

Step 1: Full backup:
Step 2: Restore the database on the mirror server (RepB) and use Nonrecovery:
Note the path and restored file name:


Step 3: Backup and restore logs:
Also restore in Nonrecovery mode:

Step 3: Start mirroring
The first two steps are mainly to prepare the configuration of the image. Let's start the image officially:

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


Step 2: Select [Configure mirroring]: In this step, we mainly obtain the network address of the main server. See the red box
Step 3: Execute the following script on the mirror server (RepB):
Note the order, first execute on RepB

ALTER DATABASE AdventureWorks2008R2
    SET PARTNER = 'TCP: // RepA: 5022';
GO


Step 4: Execute the following script on the principal server (RepA) to add RepB as a partner of RepA
ALTER DATABASE AdventureWorks2008R2
    SET PARTNER = 'TCP: // RepB: 5022';
GO


After execution, you can see the mirror configuration on RepA:
 
Step 5: Switch modes
The setup in steps 3 ~ 4 is to use the high security mode. If you want to use the high performance mode (again, this example does not use a witness server, so you cannot use the high security mode of automatic failover), you can use the following script on RepA achieve:

ALTER DATABASE AdventureWorks2008R2
    SET PARTNER SAFETY OFF
GO


Open it again, you can see that the operating mode is already high-performance mode:



Step 6: Verify failover
Let's use the statement to test whether it can fail over. First check the status of the two libraries. Here is a little trick, using [register server],




Then create a new registration:



 
 
Similarly, add RepB:

 
Then open a query window for one time
Query two servers, provided they have sufficient permissions. This example uses sa to connect:

 
Note the pink part. If (1/2) occurs, it means that a server cannot connect successfully:

 
The results are as follows: we only focus on a small part of the content:

 
Now switch back to the query window of RepA and enter:

ALTER DATABASE AdventureWorks2008R2 SET PARTNER FAILOVER;-Execute on the principal server


Then go to [Registration Manager] and check again. You can see that RepB is now the Principal, which is the principal server:



 
The reader can use the GUI interface operation, so not too much demonstration here.



Non-domain environment mirroring configuration with witness server
The following demonstrates how to add the witness server to the mirroring environment. First, we maintain the previous configuration, that is, set up the main body and the mirror server, and then we use a Win7 system as the witness server with SQL Server 2008 R2 Enterprise Edition installed on it You can use Express or Workgroup as the witness server.



Step 1: Verify the network connectivity of the three servers. There is no burden here. The reader can refer to the previous method to check. Step 2: According to the previous steps, create the master key, certificate, etc. on the witness server:
--Create a master key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa $$ w0rd';-required for demonstration, 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'-Create a certificate on the Wiiness instance, named HOST_C_cert. This option describes the certificate
   , EXPIRY_DATE = '2015-6-5';-certificate expiration time, can be set a little longer, set according to actual needs
GO
/ *
--Delete certificate
USE master;
DROP CERTIFICATE HOST_C_cert
* /
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT = 5022 --Use port 5022, which can be changed to an unused port, but all partners in the mirroring process should use the same port
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE HOST_C_cert-use certificate to authorize endpoint
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL-indicates that this endpoint can act as any role, including the master server, mirror server, and witness server. See online books for details.
   );
GO
/ *
-Delete mirrored endpoint
IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring ')
DROP ENDPOINT [Endpoint_Mirroring]
GO
* /
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'C: \ Certifications \ HOST_C_cert.cer';
GO


Make sure that RepA, RepB, and Win7 have three certificates generated by the subject, mirror, and witness.

 

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

-Create a login name on the Witness instance for the Principal instance
USE master;
CREATE LOGIN HOST_A_login WITH PASSWORD = 'Pa $$ w0rd';
GO
--Create a login name for this
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO
-Let this account use certificate authority
CREATE CERTIFICATE HOST_A_cert
   AUTHORIZATION HOST_A_user
   FROM FILE = 'C: \ Certifications \ HOST_A_cert.cer'
GO
--Grant this new account permission to connect to the endpoint
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_A_login;
GO
/ *
--Delete account
DROP LOGIN HOST_A_user
* /
-Create a login name for the Mirror instance on the Witness instance
USE master;
CREATE LOGIN HOST_B_login WITH PASSWORD = 'Pa $$ w0rd';
GO
--Create a login name for this
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
-Let this account use certificate authority
CREATE CERTIFICATE HOST_B_cert
   AUTHORIZATION HOST_B_user
   FROM FILE = 'C: \ Certifications \ HOST_B_cert.cer'
GO
--Grant this new account permission to connect to the endpoint
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_B_login;
GO
/ *
--Delete account
DROP LOGIN HOST_B_user
* /


Execute the following statements in RepA and RepB to create permissions for the witness server to connect to the endpoint:

USE master;
CREATE LOGIN HOST_C_login WITH PASSWORD = 'Pa $$ w0rd';
GO
--Create a login name for this
CREATE USER HOST_C_user FOR LOGIN HOST_C_login;
GO
-Let this account use certificate authority
CREATE CERTIFICATE HOST_C_cert
   AUTHORIZATION HOST_C_user
   FROM FILE = 'C: \ Certifications \ HOST_C_cert.cer'
GO
--Grant this new account permission to connect to the endpoint
GRANT CONNECT ON ENDPOINT :: DatabaseMirroring TO HOST_C_login;
GO


These two logins should exist in RepB, and there should be two accounts Host_B_Login and Host_C_Login in RepA:





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

ALTER DATABASE AdventureWorks2008R2 SET WITNESS = 'TCP: // win7: 5022'


After finishing, open the mirror configuration of RepA, you can see that the witness server has joined:





We can test it by shutting down the SQL Server service of RepA to realize the "failure" of the main server and see if RepB can switch automatically:

The first step is to check the status of RepB:


The second step is to shut down the RepA service:
The third step is to refresh the status of RepB:


It can be seen that it has been switched over and the status is Disconnected. Note that even if RepA is online again at this time, it will not automatically switch to become the main server. Manual switching is required. This part of the reader can test it by himself. After starting RepA again, you can compare the status of the mirror, from Disconnected to Synchronized.





Everywhere, the mirroring configuration in a non-domain environment has been completed.