Shulou(Shulou.com)06/01 Report--

Detailed procedure of SQL server 2005 database mirroring

1. Add a complete domain name suffix to restart the system

Right-click computer = > Properties = = > change Settings (computer name) = > change = > hq.com

2.SQL service startup account to create the same user account password must also be the same and join the administrator group

Configure username and password: net user hqsqluser HqsqlQWE19832 / add

Add users to the group: net localgroup administrators hqsqluser / add

View the command net user hqsqluser

Delete users group net localgroup users hqsqluser / del

3. Configure the SQL service and the agent service to start the service with the hqsqluser user you just created

Press the WIN+R key to open the running window

Enter "services.msc" and enter

Locate the SQL Server (MSSQLSERVER) service. Double-click to open = > Click to log in = > Select this account "enter the account you just created"

Restart the service

Locate the SQL Server Agent (MSSQLSERVER) service. Double-click to open = > Click to log in = > Select this account "enter the account you just created"

Restart the service

4. Set the user password to never expire and the user cannot change the password

5. Add IP to the hosts file on the three SQL servers for the full name of the server.

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 in with the full server name]

1) create a database (HQDB)

CREATE DATABASE HQDB

GO

USE HQDB

GO

CREATE SCHEMA hr

GO

2) create a file table

CREATE TABLE [dbo]. [Y_ file]

(

[Y _ No.] [int] NOT NULL

[Y_ name] [nvarchar] (6) NULL

[Y_ attribute] [nvarchar] (4) NULL

[Y_ ground code] [nvarchar] (4) NULL

[Y_ house number] [nvarchar] (6) NULL

[y _ unit] [nvarchar] (18) NULL

[Y_ population] [float] NULL

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

[Y_ Power] [nvarchar] (2) NULL

)

3) right-click the database = = > attribute

Click "options" on the left > recovery model "complete"

8 back up the database first

1) create two backup devices

Exec sp_addumpdevice 'disk',' BackupOrginal','c:\ BackupOrginal.bak'

2) back up the database

Backup database HQDB to BackupOrginal with format

9 restore on the mirror database server

Restore database "database name must be the same"

Option

Select do not perform any action on the database, do not roll back uncommitted transactions, you can restore other transaction logs (A). (RESTORE WITH NORECOVERY)

10 configure database mirroring

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

11 add the user hqsqluser of Windows to the login user name of SQL server 2005

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

Click on the upper right corner of the pop-up dialog box (search) = > Advanced of the new dialog box = > find now = > find hqsqluser = > OK = > OK

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

12 right databases that need to be mirrored = > tasks = > mirroring

Configure security = >

13 without a witness, the following code can be executed directly. Start mirroring

Alter Database HQDB

Set Partner= 'TCP://sql2.hq.com:5022'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.