Chapter three--Part two--third article configuring SQL Server Mirroring--domain environment

Original source: http://blog.csdn.net/dba_huangzj/article/details/28904503

This article demonstrates how to deploy mirroring in a domain environment, where deployment in a domain is relatively straightforward, but many enterprises do not really use domains to manage servers (which is one of my companies), so it is necessary to demonstrate a non-domain environment and focus on non-domain environments. However, as a practical experience and the best advice, strong use of domain environment management. The non-domain environment will be demonstrated in the fourth article: http://blog.csdn.net/dba_huangzj/article/details/27652857. This article only focuses on building under the domain environment.

Either way, it can be divided into two parts, the first part is the preparatory work, the second part is the implementation of the work

Preparatory work:

In addition to OS-level configuration, the following steps require early action:

1. To set the recovery model of the database, mirroring requires the recovery model of the database to be full mode, which can be modified using the following statement:

ALTER DATABASE adventureworks2008r2 SET RECOVERY full;

2. Check that the compatibility level of the database is 90 or higher, that is, 2005 or more SQL Server, if not, you can use the following script to modify:

ALTER DATABASE adventureworks2008r2 SET compatibility_level=100;--2008 sp_dbcmptlevel @DBNAME = ", @NEW_CMPTLEVEL =90  --2005

3. To configure all required logins, principals and mirrors, you can use the following method:

1. SSIS Transport Login

2. How to generate Scripts

3. Manual Sync

4. Configure external resources such as SSIS packages, maintenance plans, shared files, and so on.

Pre-installation configuration:

1. Disables full backup and log backup jobs on the server.

2. Perform a full backup and copy the backup files to the mirror server

3. Use NORECOVERY to restore the backup to the mirror server.

4. Perform a log backup and copy to the mirror server, as well as restore the log with NORECOVERY

If you do not use NORECOVERY, the configuration process will be error-free and need to be re-restored, so this is very important.

Configure mirroring in the domain environment:

Environment: Virtual machines use Hyper-V, a simple domain environment is installed (this is not part of the presentation, readers can find the relevant information themselves), the OS is Windows Server 2008R2 x64.

SQL Server 2008R2 x64 Enterprise Edition. Create a domain account mirroradmin with a password of pa$ $w 0rd (where 0 is the number is not the letter O).

IP on DC:

IP of Sql-a:

IP of Sql-b:

To add a domain user to SQL Server:

First use the domain administrator or the Windows account you added when you installed or log in with SA:

Then add the newly created domain account to SQL Server:

Add Windows account:

To add the SQL Server server role, select the sysadmin role here for demonstration convenience:

After the add succeeds, log in to Windows with this added Windows account, and then log on to SQL Server as Windows authentication:

Start building:

Here's how to start using Microsoft Sample database Adventureworks2008r2 as a demo:

Files are stored locally C:\DB\

First of all, according to the previous preparation of the library's recovery model, full backup and other operations first done, full backup we first create a new shared folder for transmission to the mirror server is the sql-b used in this article, pay attention to select "NORECOVERY" recovery status,

After restore, you can see in the mirrored database

Now go back to the principal server and open the mirroring option:

Select "Configure Security":

The witness server is not built here, so select No:

This step if you do not need to change other information, you can directly click "Next":

Select "Mirror Server":

If you find that the server is not photogenic, such as the following error:

You can choose to turn off the firewall or turn on the required ports of 5022 until you can connect:

Select "Service Account":

To complete the configuration:

After the configuration is complete, the interface will immediately pop up if you need to "start mirroring", and if everything is ready and does need to be turned on, click "Start Mirroring":

After the successful start of the mirroring process, you can see the following, proof that the build is complete, you can use the Red box section of the "Failover" to failover, the principal database of the active session to the mirror database:

After the build is complete, check the database status of two servers:

Test failover (Failover) below:

To refresh the database of two servers, the following status can be seen:

The SQL Server mirroring feature provides a database mirroring monitor that can be opened in place, which is useful for monitoring mirroring, and how to use this tool will be described in a later article:

After opening, you can check some information about the mirroring environment:

In addition to failover, you can switch the operating mode:

First check the original operating mode:

The current high-safety mode is visible (safety is full).

Then make the mode transition, and then check the status:

This is now a high-performance mode (safety is off).

To add a witness server:

Here you need to select "Yes":

Then click Next:

Connect the witness server in the principal server, which is the "Sql-core" instance of this article:

Specify the account in the mirrored environment, because in a domain environment, you can use the same domain account:

You can see the following after the construction is complete:

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

The visible principal database has been switched to the original mirror server (sql-b):

Restart Sql-a, and then stop the Sql-b service:

The visible main library is back in the sql-a:

At this point, the domain environment of the mirror to build the demonstration has been completed.

SQL Server mirroring has an important patch that fixes the log issue in the 2005 mirroring environment, and does not require processing for 2008: http://support.microsoft.com/kb/947462. Error message when you build and use the 2005 mirroring feature when you start a database mirroring session in SQL Server 2005: "Communication with the remote server instance TCP://< computer name >:< port number > The database mirroring has not been fully started before the failure occurs. " That means you need to use this patch to fix it.

Building images, especially in a domain environment, is very simple, and the series focuses on automating deployment, monitoring, and optimization, so there are not too many deployment demos.

The following is the construction of a non-domain environment: http://blog.csdn.net/dba_huangzj/article/details/27652857