Help for Database Mirroring

Question

Post reply

Help for Database Mirroring

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

People make sure your executing that against Principle database (aka database that is active).

Thanks.

Mohit.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 2

Yes, I am executing from Principal database.

---------------------------------------------------
Thanks,
Satheesh.

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 3

Have a look at:

http://support.microsoft.com/?kbid=940254

Try using the IP address.

Thanks.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

eng_mgomaa Right there with Babe Points: 796 More actions · Answer 4

1- Be sure that you created Endpoint at witness server, this is the script fo that

CREATE ENDPOINT [endpointmirroring]

STATE=STARTED

AS TCP (LISTENER_PORT = 1450, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = REQUIRED ALGORITHM RC4)

and becarefull with word (Role=witness)

2- try to ping Witness server from principle server or even try to use telnet for checking connection through this port.

3- try also to check if there is firewall between witness and principle server ((even if it is windows firewall).

please tell me if there is no result with you , we will think again 🙂

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 5

I thank everyone who were trying to help me.

As eng_mgomaa spoted, I had given the ROLE as Partner instead of witness. But after changing it also I am getting the same error as,

The ALTER DATABASE command could not be sent to the remote server instance 'TCP://WitnessServer:5023'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

I tried using the IP address and FQDN but same error.

I telnet the port and run the netstat and found the port is established.

Principal, Mirror and Witness are running with same domain account and the account is sysadmin and administrator of the machine. Inspite, I had granted connect permission for the account.

In hosts file at drivers\etc\hosts, I had added the IPaddress and hostname of the servers.

My configuration are Principal(Enterprise Edition), Mirror (Enterprise Edition), Witness (Express Edition). I had enabled Remote connection with Tcp/Named pipes in Express.

My code on witness,

CREATE ENDPOINT [MirrorEndPoint]

STATE=STARTED

AS TCP (LISTENER_PORT =5023)

FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES)

On Principal I am executing the below,

ALTER DATABASE Mirroring_db1

SET Witness = 'TCP://WitnessServer:5023'

---------------------------------------------------
Thanks,
Satheesh.

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 6

I am sorry you are still having this issue. I found another artilce where they resolved the issue using the DNS settings as mentioned in the KB article I had posted above..

KB Article: http://support.microsoft.com/?kbid=940254

Another Ref site with this issue: http://developmentalmadness.blogspot.com/2006/07/adventures-in-mirroring.html

Please check the DNS settings on your witness box...

Thanks.

Mohit.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 7

Hi Mohit,

As per the site http://developmentalmadness.blogspot.com/2006/07/adventures-in-mirroring.html, I had changed the Primary and Secondary DNS server IP to be same in both Principal and Mirror/Witness Server but the result is same.

I had tried with DEV edition instead of Express edition as witness, still the same problem.

To try the microsoft recommendation http://support.microsoft.com/?kbid=940254, I have to chase my Network team. And the changes is to be done on principal server. Since my principal is in production I have to follow some formalities. I will let you know the status.

---------------------------------------------------
Thanks,
Satheesh.

S. Kusen SSChampion Points: 10936 More actions · Answer 8

I had this similar problem.

Try this:

Take a fresh backup (in full recovery) on the primary DB.

Restore the DB on the mirrored location (MAKE SURE TO SPECIFY NORECOVERY).

Try starting your mirroring session then.

This is a misleading error message.

You have to think of mirroring as "automated log shipping" and you are continuously restoring logs, so the DB on the secondary site must be brought online in norecovery mode, as if you are going to apply more T-logs to it.

Here are my steps:

1. Set DB to full recovery model on primary system.

2. Take full backup.

3. Restore full backup with norecovery (important)

4. Ensure named pipes is enabled on both systems.

5. Configure mirroring.

6. Start mirroring.

Please let me know if that helps.

I did the same exact runaround as you are doing, and hopefully this is the simple thing you missed.

Steve

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 9

Steve - I was doing the same way how you had suggested all these times. Anyway I had removed the mirroring and re-configured in the same way how you had suggested but no luck. Same error.

I tried to setup the mirroring in different way. All this while I am setting the mirroring as ServerA/Instance01 as Principal, ServerB/Instance01 as Mirror, ServerB/Instance02 as Witness.

I changed this to ServerA/Instance01 as Mirror, ServerB/Instance01 as Principal and ServerB/Instance02 as Witness. In this way I can able to setup the witness successfully. The manual failover is also happenning fine. But if I make the principal down (by stopping the service) the Mirror is getting disconnected.

Through this I had understand the problem is on witness instance to look the principal instance.

1. Since the mirror and witness are in the same server and the principal and mirror can work without any problem there cannot be a network issues.

2. I can telnet the principal server from witness and witness from principal on their respective port and also I can find the port is open through netstat.

3. Principal, Mirror and Witness are running under same domain account which is a machine and sql administrator.

I am unable to figure out the problem. I am going mad on this. Can anyone throw some light on this?

---------------------------------------------------
Thanks,
Satheesh.

mitsucars00 Newbie Points: 3 More actions · Answer 10

Take a look at these articles.

http://msdn.microsoft.com/en-us/library/bb510479.aspx

sayfrend SSCommitted Points: 1733 More actions · Answer 11

Make sure you do not use same port numbers for different instances on the same box.

You mentioned you have Mirror and Witness on the same box. Do not use same port numbers for Mirror and Witness.

Use different port numbers for Mirror and Witness Server.

~IM.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 12

Is there any use to use Witness and mirror on the same instance?

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 13

You can do that but I wouldn't recommend it; because in a 3-partner (Principal-Mirror-Witness) configuration; if two partners go down then the remaining partner database become inaccessible.

So in this case if your mirror server ever dies your principal will not be able to serve the database without you breaking mirroring. But if your principal dies then it will failover as per normal operations.

Thanks.

Mohit.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 14

Sorry guys for replying late, I got struck with the auditing.

sayfrend - I am sure that I am trying with different port numbers for mirror and witness.

Krishna Potlakayala - If I try witness with different server also I'm getting the same error.

---------------------------------------------------
Thanks,
Satheesh.

sayfrend SSCommitted Points: 1733 More actions · Answer 15

SK (3/26/2009)
4. Ensure named pipes is enabled on both systems.

I believe, Named Pipes has nothing to do with Database Mirroring.

Database Mirroring is done by TCP-IP, Yes you need to make sure that TCP-IP is enabled at both principal and Mirror SQL Server instance.

~ IM.