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

Source: http://blog.csdn.net/dba_huangzj/article/details/28904503

This article will demonstrate how to deploy images in a domain environment. It is much easier to deploy images in a domain, but many enterprises do not actually use the domain to manage servers (my company is one of them ), therefore, it is necessary to demonstrate non-domain environments and focus on non-domain environments. However, as a practical experience and best advice, Domain environment management is strongly used. A non-Domain environment will demonstrate in Section 4: http://blog.csdn.net/dba_huangzj/article/details/27652857. This article only focuses on building a domain environment.

Either method can be divided into two parts: preparation and implementation.

Preparations:

 

In addition to OS-level configuration, the following steps must be performed in advance:

 

1. Set the database recovery mode. The image requires the database recovery mode to complete. You can use the following statement to modify it:

 

ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL;

 

2. Check whether the database compatibility level is 90 or above, that is, whether it is 2005 or more SQL Server. If not, use the following script to modify it:

 

ALTER DATABASE AdventureWorks2008R2 SET COMPATIBILITY_LEVEL=100；--2008 SP_DBCMPTLEVEL @DBNAME=’’,@NEW_CMPTLEVEL=90  --2005

 

3. configure all required logon accounts, including the subject and image. You can use the following method:

 

1. log on to SSIS Transmission

2. Script generation

3. manual synchronization

4. configure external resources, such as SSIS packages, maintenance plans, and shared files.

 

Configuration before installation:

 

1. Disable full backup and log backup tasks on the server.

2. perform a full backup and copy the backup file to the backup server.

3. Use NORECOVERY to restore the backup to the backup storage,

4. Execute log backup and copy it to the backup image server. Likewise, use NORECOVERY to restore logs.

 

If NORECOVERY is not used, an error is reported during the configuration process and needs to be restored. Therefore, this is very important.

 

Configure the image in the domain environment:

 

Environment: the virtual machine uses hyper-v and installs a simple domain environment (this part is not in the scope of demonstration and you can find relevant information on your own). The OS is Windows Server 2008R2 x64.

SQL Server 2008R2 x64 Enterprise Edition. Create a domain account MirrorAdmin with a password of Pa $ w0rd (where 0 is a number not a letter o ).

 

IP address on DC:

 

 

SQL-A IP:

 

SQL-B IP:

 

 

Add a domain user to SQL Server:

 

Log On with the Windows Account Added during domain administrator or installation, or log on with sa:

 

 

Then add the newly created domain account to SQL Server:

Add a Windows Account:

 

Add the SQL Server role. For demonstration convenience, select the sysadmin role:

 

 

After successful addition, log on to Windows with the added Windows account, and then log on to SQL Server with Windows Authentication:

 

 

Start building:

 

The following describes how to use the Microsoft demo database AdventureWorks2008R2:

 

Files are stored in local C: \ DB \

 

 

 

First, perform operations such as the recovery mode and full backup of the database according to the previous preparations. First, create a new shared folder to store the complete backup, for transmission to the mirror server is the SQL-B used in this article, pay attention to select [NORECOVERY] recovery status,

 

After restoration, you can see it in the image database.

 

 

Now return to the main server and enable the image option:

 

Select configure security ]:

 

 

No witness server is set up here, so select no ]:

 

If you do not need to change other information for this step, click [next ]:

Select backup storage ]:

If the image server cannot be connected, for example, the following error is reported:

 

 

You can choose to disable the firewall or enable the required ports such as 5022 until they can be connected:

 

Select Service account ]:

 

Complete the Configuration:

 

 

 

 

After the configuration is complete, the page will immediately pop up whether to [start image]. If everything is ready and needs to be enabled, click [start image ]:

 

 

After successfully starting the image process, you can see the following:

 

 

After the setup, check the database status of the two servers:

 

The following is a test of Failover ):

 

Refresh the databases of the two servers. The following statuses are displayed:

 

 

The SQL Server image function provides a [database image monitor] that can be opened in the middle. This tool is useful in Image Monitoring. How to use this tool will be described in the following article:

After opening, you can check the image environment information:

 

In addition to failover, you can also switch the running mode:

First, check the original running mode:

It can be seen that the current high-security mode (Safety is FULL ).

Then perform the mode conversion and check the status:

 

It can be seen that the current high-performance mode (Safety is OFF ).

 

Add a witness Server:

 

Here you need to select [Yes ]:

 

Click next:

 

 

Connect to the witness server in the main server, that is, the [SQL-CORE] instance in this article:

 

Account in the image environment. Because the account is in the domain environment, you can use the same domain account:

 

 

After the building, you can see the following:

 

 

We can verify by stopping the service of the principal server (SQL-A:

 

 

It can be seen that the master database has been switched to the original mirror server (SQL-B:

 

 

Restart the SQL-A, and then stop the SQL-B service:

 

 

Visible body library back to the SQL-A:

Now, the image creation demonstration in the domain environment has been completed.

 

The SQL Server image has an important patch. This patch is mainly used to fix the log problem in the 2005 image environment and does not need to be processed for 2008: http://support.microsoft.com/kb/947462. When you build and use the 2005 image function, if an error is reported: when you start the database image Session, the error message in SQL Server 2005 is: "communication with remote Server instance TCP: // <computer name>: <port number> A fault occurred before the database image is fully started ". It means you need to use this patch to fix it.

Building images is especially simple in the domain environment. This series focuses on automated deployment, monitoring, and optimization, so there will be no excessive deployment demonstrations.

Below is the establishment of non-Domain environment: http://blog.csdn.net/dba_huangzj/article/details/27652857