Implementing usability Analysis with SQL SERVER 2005 database Mirroring _mssql2005

Let's first take a look at what data mirroring is:

Now almost all applications are based on the database, then the load of the database is relatively large, in 24 hours a day, any time there will be data to be saved to the database, or from the database to read data. Any time a user will be connected to our database server, dozens of, hundreds of or even tens of thousands of users to connect to use our database, so whether the planned downtime or unplanned failure will cause a certain loss. To our users or enterprises with a great loss, especially with the advent of the data age, users of the use of data put forward a higher demand, then as a DBA, it is necessary to do to reduce this loss to the lowest, precisely because of this demand, database mirroring technology appears! The concept of database mirroring is proposed for the first time in SQL SERVER2005. Characteristics:

Software-based high-availability solutions that are fully software-based high-availability solutions. No need to increase hardware costs, i.e. low hardware costs

A quick failover recovery, one of the main highlights, is rapid failover recovery. 3 seconds (especially attractive for users or DBAs) typically 10 seconds for large amounts of data.

In this database mirroring technology there is a database server we call the primary database. It is responsible for the user's connection and data processing. Another is from the server, exactly, this should be called the mirror server, there is also a database, called the mirror database, this database is used to store a hot backup of our primary database. That is to say, it does not connect to the user, but it changes the data on the primary server, changes Ah, can do a hot backup, that is, if the user updated the content of the main database, then the primary database will be based on the image technology update to the mirror server, This ensures that the data between the primary server and the server is consistent. So if, for some reason, our primary server or primary database is unavailable, for example, network outages, system failures, etc., the client redirects to the mirror server, the client can still read the data, write the data, He doesn't feel the primary database service is down. Therefore, the use of database mirroring technology, the user for the increased availability, and for the recovery time is shortened. Then the customer can still write data to the mirror database. Read data, update related transactions, this is a process of applying database mirroring. To implement this process, there are a number of roles to be involved:

Server roles in database mirroring: These roles have just been graphically introduced, so there are three types of server roles in 2005, respectively

Principal server: Hosting principal database

Accept user connections and transaction requests that is, the principal server is normally the principal server to provide services

Mirrored server: Hosting a mirrored database

Backed up as a principal database the so-called hot backup is that the change on the principal database will immediately react to the hard drive mirror database.

Accept user connections only after a failover, processing transaction requests

Witness server: Monitor server status and connectivity for automatic failover that is, the witness server monitors the status and connectivity of two servers at all times, and when the principal server is down or unavailable, the witness immediately enables failover, switching the mirror server to the principal server. Continue to provide server for users

This is a three-server role in database mirroring, but be aware that the three roles are not fixed and can be changed:

The principal and mirror databases are partners for each other:

The body and mirror can be converted to each other

Partner roles changed after failover

When the principal server is normal, all the user's connection and data updates are sent directly to the principal server, but the principal server then backs up the data to the mirror server, but the principal server is unavailable and the role changes. The mirror server becomes the principal server. So if the original principal server is back to normal, then what happens is that it becomes the mirror server. So their role is radically changed. Then if this server is not available again. Then again a process of conversion.

One question that everyone may have to ask is how these three characters know which one is available and which one is not:

Individual server instances are monitored by pinging messages to each other. With the DOS command, the ping principle is similar, but the function is more powerful than the ping in DOS, the ping in DOS only checks the connectivity of the network, and the ping here to monitor the connectivity of the network, this is the first step, but also to monitor the operation of the database server instance, the server is normal, There is also the database on this server is not normal.

Summarize the process of working with database mirroring:

Normally, when database mirroring is configured, the user can only connect to the principal database, but the mirrored database is not available at this time. It's no use for users to go up. When a user can only use the principal server, the principal server writes the data to its own database and, on the other hand, passes the transaction log to the mirror server and writes to the mirror server's database, at which point the principal server enters a waiting state. Wait for the mirror server to confirm, that is, when the mirror server's data successfully written to the mirror database will send a message to the principal database, said I have now completed the update of the data is the mirror server to perform a redo process. This is a confirmation. When the principal server receives this confirmation, it will give the client a response, saying that the data update operation has been completed

So why is it possible to implement a quick recovery mechanism that is inseparable from one of the 2005 mechanisms?

But SQL 2005 is not unnecessary wait until the rollback end as long as after the redo can be used, as for the undo operation, in the user's use of the process you continue to undo, so when the principal server occurred data updates, the mirror server will be the shortest time to update, So that if the principal data fails, the mirror server is right to take over the primary server in the shortest amount of time.

Here are three modes of operation in database mirroring:

High availability: The most commonly used.

High-level protection

Performance

Let's take a look at these three modes separately, of course, the most important is high availability, which is a more widely used model

High Availability mode:

Server role: Principal server Mirroring Server Witness

Application Scenario:

Requirements for high availability occasions such as stock trading securities trading banks.

Automatic failover Required

Ensure the integrity of the data: requirements as long as the user submitted to the server data, that is afraid to say that the data just submitted to the main server has failed, but also to ensure that the data will not be lost. Data is not lost after failover to ensure database integrity

High-level protection mode:

We can also see from the name that it focuses on a kind of protection of the data, rather than on the implementation of usability

Server role: Principal server Mirroring Server

Application Scenario:

High data integrity requirements

No automatic failover required

Low availability requirements for services that is, the main database is down, but the loss of the data is unacceptable, so you can use the High level protection mode in this situation

Because there is no witness server, automatic failover is not possible. If the principal server is unavailable, then you want failover to be done manually, so the availability of the server is less demanding

High-Performance mode:

Server role: Principal server Mirroring Server

Application Scenario:

The principal and mirror servers are more than 10 kilometers or two cities away from the distance.

There is a significant delay in communication links

Performance requirements are higher than the integrity of the data

The principle is: when the principal server receives the user's action, the transaction is passed to the mirror server, so there is a significant delay, so he will not wait for the mirror server to confirm, that is, regardless of whether this data is written to the mirror server, so this mode is to respond to the user's request as soon as possible, That is, the user has a higher performance requirements, this requirement is higher than the integrity of the data.

This mode will have the loss of data, that is, if the principal server is down, we will be the mirror server as the principal server, but there is no guarantee that the data in this and the principal server is consistent with the data, because there may be lost.

We have a brief introduction to several concepts:

Transaction security:

The mode of synchronous transmission of the full body and the mirrored database.

The principal waits for the mirror to confirm after sending the log

Body and mirror log exactly the same

Off

The principal and after sending the log do not wait for the mirror to confirm and continue processing the subsequent operation.

Some data may be lost on the mirror when the principal fails

Arbitration: Arbitration is required in high availability or high level protection mode. To determine which server is the principal server,

A change in the arbitration will result in a failover, such as a failure of the principal server, a change in the quorum, and the mirror server as the principal server.

The form of arbitration generally has so many kinds:

Let's take a look at How to configure Database mirroring : This should be the people feel very excited, because listen to me west to pull the talk for half a day. Finally no longer have to suffer. In fact, the configuration is very simple, just pay attention to a few steps on the line.

preparing A mirrored database to prepare a mirrored database on the mirror server

To create a database mirroring endpoint to configure the mirroring endpoint on each server

Configuring security

Starting database Mirroring

Let's take a specific look at how to do it and what needs to be noted:

The point to mention here is that when SQL SERVER2005 just released the database mirroring service is turned off by default and is not supported. When the official version of SQL SERVER2005 was just released, it was considered that the technology of database mirroring was immature and needed to be perfected. So if you're using a formal version, you won't be able to use this technique.

Then you need to download SP1 or more patches.

· Version number	SQL Server version
9.00.1399	SQL Server 2005 (initial version)
9.00.2047	SQL Server SP1
9.00.3042	SQL Server SP2

We are directly here to play SP2 Patch: slightly

Prepare the database:

Conditions are important:

The principal database must be a full recovery model

Creating a mirrored database

Make a full backup on the principal database and restore the principal database using the Norecover option on the mirror server.

Resuming subsequent log backups (Norecover) Norecover is important

Metabase mirroring endpoint (ENDPOINT)

The database mirroring endpoint implements the communication of the mirroring session, which is the entry point of each server, somewhat similar to the port number. But not. This means that after you create this endpoint, the TCP protocol can be used to communicate between the servers. Each mirror endpoint listens on a unique TCP port number, and generally uses port No. 5022.

To create a database mirroring endpoint:

Need to create on each instance

Only members of the Administrators group can have permissions.

There is a partner endpoint, or a witness endpoint, that must be specified to set the endpoint role.

Activation endpoints are not available by default, so you want to activate them.

Now let's take a look at creating endpoints using T-SQL statements

CREATE ENDPOINT dbmirroring

As TCP (listener_port=5022) can of course use other ports as long as they are not used

For database_mirroring (role=partner,encryption=supported) go

--Creates a database mirroring endpoint, the role is a partner, and the communication process is encrypted.

ALTER ENDPOINT dbmirroring state=started Go-activation

At this point, the endpoint starts listening.

Create the endpoint of the witness: activates the endpoint when it is created.

CREATE ENDPOINT dbmirroring

State=started as TCP (listener_port=5022)

For database_mirroring (role=witness,encryption=supported)

Configure security:

Windows Authentication or certificate-based authentication (untrusted domains) must be trusted between instances in database mirroring, and for the sake of simplicity, we use Windows authentication.

Gives the service account permission to connect to the endpoint.

Here we all use the same username password

After we create the endpoint we will start the database mirroring, note that the order is important

Specifies that the partner of the mirrored database operates on the mirror server

Specifies that the principal database partner operates on the principal server

Specify that the witness operate on the witness server

Specify transaction security options full or off

The corresponding statements are:

ALTER DATABASE nothwind SET partner=n ' tcp:/server1h: 5022 '

–-performed on SERVER2 (mirroring)

ALTER DATABASE nothwind SET partner=n ' tcp:/server2: 5022 '

--Execute on SERVER1 (main)

ALTER DATABASE nothwind SET witness=n ' tcp:/server3: 5022 '

--Execute on SERVER1 (main)

ALTER DATABASE nothwind SET SAFETY full;

--Perform high availability on SERVER1 (body)

Of course, you can also use SMSS

Then how do I see if the database mirroring is complete, and there are two ways to do this:

SMSS Database Properties---mirror state

T-SQL

SELECT * from SYS. Database--mirroring

SELECT * from SYS. Database--mirroring--witness

Let's look at the configuration of high availability database mirroring

We can clearly see the configuration process using T-SQL.

Now let me introduce the environment we use:

SERVER1 as principal server

SERVER2 as mirror server

SERVER3 for the witness server

First we have to

Prepare the database : One is the backup principal database and one is recovered on the mirror server.

So

On the SERVE1:

BACKUP DATABASE NORTHWIND to disk= ' C:\NW. BAK '

On the SERVER2:

RESTORE DATABASE NORTHWIND from disk= ' C:\NW. BAK ' with NORECOVERY

To create a database endpoint:

1. Create a database mirroring endpoint on SERVER1 for partner communications

Create endpoint Dbmirrep as TCP (listener_port=5022)

For database_mirroring (role=partner,encryption=supported);

Alter Endpoint Dbmirrep state=started

Through the graphical interface can be found

2. Create database endpoints on SERVER2, also for partner communications

Create endpoint Dbmirrep as TCP (listener_port=5022)

For database_mirroring (role=partner,encryption=supported)

Alter Endpoint Dbmirrep state=started

3. Create a mirrored endpoint on the SERVER3 to witness the traffic

CREATE ENDPOINT Dbmirrep as TCP (listener_port=5022)

For database_mirroring (role=witness,encryption=supported)

ALTER ENDPOINT dbmirrep State =started

4. Check endpoint Configuration

SELECT * from SYS. Database_mirroring_endpoints

can also be viewed through the graphical interface

Metabase Mirroring security: Specifies which users can use this endpoint. Must be the administrator, the general user does not let him visit.

Execute separately:

Grant Connect on endpoint:: "Dbmirrep" to "Server1\dufei"

Grant Connect on endpoint:: "Dbmirrep" to "Server2\dufei"

Grant Connect on endpoint:: "Dbmirrep" to "Server3\dufei"

The last one is to start database mirroring. Note: The order is first configured from the Mirror service

On the SERVER2, specify the partner endpoint:

ALTER DATABASE itet SET partner= ' tcp://server1:5022 '

On the SERVER1, specify the partner endpoint:

ALTER database itet SET partner= ' tcp://server2:5022 '-View databases

This is the high level protection model we described earlier. Data integrity can be achieved, but high availability cannot be achieved. So continue, that is to say, not to witness the server, but not the automatic failover:

On SERVER1, specify the witness server endpoint:

Alter database Itet set witness=n ' tcp://server3:5022 '

To set the database mirroring transaction security level:

ALTER DATABASE itet SET SAFETY full

The experiment is over, but make sure you pay attention to details.

Finally, take a look at database mirroring role switching: How to implement Failover

Automatic failover:

For high Availability mode only

Safety=full

Test: Disable the master server's network card, view the library status, and then enable and then view

We've come here to know how to implement database mirroring, and then how users can use: Clients are connected to the principal server for work. Then if the principal server is unavailable, then it will cause a failure of the user connection, how it knows to automatically connect to the mirror server, this is generally used ADO technology, such as asp.net or Microsoft borrowed the connection tool.

Here we use the Windows Clustering feature: to test:

SERVER1 and SERVER2 are configured as Windows clusters:

This is the end of the experiment!

This article comes from "Duffy" blog