SQL Server 2005 database mirroring detailed procedures

SQL Server 2005 database mirroring detailed procedures

1. Add a full domain name suffix restart system

Right-click Computer ==> Properties ==> Change settings (computer name) ==> Change ==> hq.com

2.SQL Service startup account Create the same user account password must also be the same and join the Administrators group

Configure User name password: NET user hqsqluser Hqsqlqwe19832/add

Adding users to the group: net localgroup Administrators Hqsqluser/add

View the command net user Hqsqluser

Delete the Users group net localgroup users Hqsqluser/del

3. Configure the SQL Service and Agent service to start the service with the Hqsqluser user just created

Press "Win+r" key to open the Run window

Input "Services.msc", enter

Locate the SQL Server (MSSQLSERVER) service. Double hit open ==> Click Login ==> Select this account "Enter the account you just created"

Restart Service

Locate the SQL Server Agent (MSSQLSERVER) service. Double hit open ==> Click Login ==> Select this account "Enter the account you just created"

Restart Service

4. Set user Password Never expires user cannot change password

5. Add IP to the server's full name in the Hosts file on three SQL Servers.

Path: C:\Windows\System32\drivers\etc

172.16.1.88 sql1.hq.com

172.16.1.89 sql2.hq.com

6. Install the database. Upgrade database to SP3 version number: 9.00.4035.00

7 The recovery model of the database must be complete.

online database [must be logged on with the full server name]

1) Create DATABASE (HQDB)

CREATE DATABASE hqdb;

GO

Use HQDB;

GO

CREATE SCHEMA HR;

GO

2) Create a file table

CREATE TABLE [dbo]. [Y_ Archives]

(

[Y_ number] [INT] Not NULL,

[Y_ name] [nvarchar] (6) NULL,

[Y_ Properties] [nvarchar] (4) NULL,

[Y_ Ground Code] [nvarchar] (4) NULL,

[Y_] [nvarchar] (6) NULL,

[Y_ units] [nvarchar] (+) NULL,

[Y_ Demography] [Float] Null

[Y_ Water supply] [nvarchar] (2) NULL,

[Y_ power supply] [nvarchar] (2) NULL

)

3) Right-click Database ==> Properties

Click on the left "options" ==> recovery mode "full"

8 Back up the database first

1) Create two backup devices

EXEC sp_addumpdevice ' disk ', ' backuporginal ', ' C:\BackupOrginal.bak '

2) backing Up the database

Backup Database Hrdb to backuporginal with format

9 restoring on a mirrored database server

Restore DATABASE "Database name must be the same"

Options

Select Do not perform any operations on the database, and you can restore other transaction logs (A) without rolling back the uncommitted transactions. (RESTORE with NORECOVERY)

10 Configuring Database Mirroring

Disconnect the database. Connect to the database with the full server name

11 Adding Windows user Hqsqluser to the logon user name in SQL Server 2005

Connect to the database with the full server name ==> security ==> login ==> New Login ==>

Then pop up the dialog box at the top right corner click (Search) ==> New dialog box Advanced ==> Find Now ==> find Hqsqluser ==> ok ==> OK

Server role ==> checked (sysadmin) ==> OK

12 Right-click a mirrored database ==> task ==> Mirror

Configuring Security ==>

13 No witness server is able to execute the following code directly. Start mirroring

Alter Database hqdb

Set partner= ' tcp://sql2.hq.com:5022 '

SQL Server 2005 database mirroring detailed procedures