Real-time SQL Server 2005 mirroring configuration whole process

Source: Internet
Author: User

Basic concepts of SQL Server 2005 mirroring configuration

I understand that the SQL Server 2005 mirroring configuration is actually a guaranteed data environment consisting of three servers (or three SQL instances of the same server), namely: master server, slave server, witness server.

Primary server: Where data is stored

From server: Where the data is backed up (i.e., the mirror of the primary server)

Witness: Dynamically provision a primary/slave server to a third-party server

Environment Introduction

Let's start by introducing the configured environment:

This configuration uses three separate servers (a, B, c three computers).

A: master server, ip:192.168.0.2

B: From the server, ip:192.168.0.3

C: witness server, ip:192.168.0.4

Three computers are in the same LAN, the system is Windows Server 2003, the database is SQL Server 2005

Start SQL Server 2005 Mirroring configuration

One, in a, B, c new configuration of a user (DBUser), the user to have all the use of SQL Server permissions, I am here to add the user to the Administrators group.

Second, execute the following SQL statement in a, B, C:

Creating objects in A, B, C

1USE Master
2GO
3
4CREATE ENDPOINT endpoint_mirroring
5 state = STARTED
6 as TCP (
7 Listener_port = 5022--Listener port, optionally specified (three server ports are best consistent)
8, Listener_ip = All--listening IP address, all the addresses in the network


authentication = Windows-authentication mode, Windows
Role = All-all roles
13);
14GO

Third, execute the following SQL statement in a, B, C:

1GRANT CONNECT on endpoint::endpoint_mirroring to [testdb\administrators];

Iv. New Database in A (TestDB), Then back up the database to get the bak file (Testdb.bak), and then back up the transaction log of the database to get the trn file (testdb.trn), the Bak and trn files sent to B, by B restore, when using Enterprise Manager restore, in the "Options" in the " Recovery Status Select the second item, that is: do not perform any operations on the database, do not roll out uncommitted transactions, you can restore other transaction logs (A). (RESTORE with NORECOVERY).

V. Execute the following SQL statement in A and B:

Add individual servers to the environment to

Execute in a server (master server):
2ALTER DATABASE TestDB SET PARTNER = N ' tcp://192.168.0.3:5022 '; --adding from the server to the environment
3ALTER DATABASE TestDB SET WITNESS = N ' tcp://192.168.0.4:5022 '; --Add the witness server to the environment
4
5--b Server (from server) execution:
6ALTER DATABASE TestDB SET PARTNER = ' tcp://192.168.0.2:5022 '; --Connect from the server to the master server

"Editor's recommendation"

    1. SQL Server uses indexes for data access optimization
    2. on how to generate scripts in SQL Server
    3. SQL Server 05 Database is set as a "suspect" workaround
    4. Explanation of SQL Server version differences and selection
    5. Best SQL Server version selection to meet database requirements

Real-time SQL Server 2005 mirroring configuration whole process

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.