Mirror Setup

Hello,

I'm trying to setup database mirroring between 3 servers and the details are as follows:

1. All 3 machines reside in the same domain.

2. Server1 consists of SQL 2005 Ent Edition without SSAS, Server 2 consists of SQL 2005 Ent Ed without SSAS & SSRS, Server 3 consists of SQL 2005 Express Ed.

3. All the 3 machines are having their individual service domain accounts.

4. Server 1 consists of 5 databases which needs to be mirrored to Server 2. I took full backups and tlog backups and restored the full backups in NO Recovery mode on Server 2 and then applied the tlog backups to sync them with Server 1.

5. I created the logins on Server 1 (Server 2 & 3), Server 2 (Server 1 & 3) and Server 3 (Server 1 & 2) and gave "sa" permissions respectively.

6. I kept the first database on Server 1 to full recovery mode, created an endpoint with the default port and granted connection to Server 2 & 3 logins.

7. I created an endpoint on Server 2 with the default port number and granted connection to Server 1 & 3 logins.

8. I created an endpoint on Server 3 with the default port number and granted connection to Server 1 & 2 logins.

9. On Server 2 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 1>:<Port No>'

10. On Server 1 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 2>:<Port No>'

ALTER DATABASE <dbname>

SET WITNESS = 'TCP://<Server Network Address of Server 3>:<Port No>'

On Step 9 & 10, I'm getting an error message "Msg 1416, Severity 16, State 2 Database <dbname> is not configured for mirroring" in the query window but when I see the error logs individually on Server 1 & 2 I'm seeing "Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'dbname'. This is an informational message only. No user action is required."

I would appreciate if any one can look into the problem I'm facing. I've googled and could not come up with a solution.

Thanks

I just have one question, did you restore the tran logs from principal to mirror using with norecovery option?.......coz you need to restore atleast 1tran log in mirror with norecovery option prior to configuring mirroring......|||

Hello Deepak,

I have restored 2 sets of tlog backups from principal to mirror -- first with norecovery mode and the second one with recovery mode. Still I'm getting the same errors.

Query Window

===========

Msg 1416, Level 16, State 31, Line 3

Database " " is not configured for database mirroring.

Error Log

=======

Error: 1443, Severity: 16, State: 2.

Database mirroring has been terminated for database ' '. This is an informational message only. No user action is required.

Do you have any other workaround for this problem?

Thanks

|||

Hello Deepak,

I have even tried with a sample database on the same set of servers and encountering the same set of errors in query window and error log. Is it something to do with any sort of configuration which I need to do before implementing database mirroring?

Thanks

|||

here is the mistake,

1. you should restore the full backup in the mirror using with norecovery clause

2.you need to restore one tran log in mirror using with norecovery clause

the idea behind restoring the full backups and tran log backups using with norecovery is that you can apply further transaction logs

since you have applied one with norecovey and other with recovery option further tran logs cannot be applied in the mirror...that seems to be the error.........perform the 2 steps as above and then start mirroring.....

|||

Hello Deepak,

I took 1 full backup and 2 tlog backups from the principal server.

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode

These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

Thanks

|||

abybody out there who can troubleshoot my problem?

TIA

|||

well as i said earlier you need to restore the full and subsequent tran log backups in mirror using with norecovery option.

this step has to be performed prior to configuring mirroring....further adding to your problem the error says, that the "database is not configured for mirroring".....which is a clear indication that either your mirror is not restored with norecovery or it is not in Full Recovery i believe.....this is what i know.....

|||

Hello Deepak,

It seems you haven't gone through my earlier messages and here it is

I took 1 full backup and 2 tlog backups from the principal server.

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode

These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

Thanks

|||

refer this,

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode > this has to be in norecovery mode but u have restored it in recover mode other steps are fine

|||

Hello Deepak,

Thanks for pointing the problem and I could setup mirroring successfully..