March 18, 2009 at 4:45 pm
People make sure your executing that against Principle database (aka database that is active).
Thanks.
Mohit.
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]
March 19, 2009 at 4:13 am
Yes, I am executing from Principal database.
---------------------------------------------------
Thanks,
Satheesh.
March 19, 2009 at 3:36 pm
Have a look at:
http://support.microsoft.com/?kbid=940254
Try using the IP address.
Thanks.
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]
March 19, 2009 at 10:38 pm
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 🙂
March 23, 2009 at 4:26 pm
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.
March 24, 2009 at 12:12 am
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.
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]
March 25, 2009 at 4:18 am
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.
March 26, 2009 at 7:44 am
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
April 7, 2009 at 2:11 pm
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.
April 8, 2009 at 2:54 pm
Take a look at these articles.
April 8, 2009 at 8:36 pm
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.
April 9, 2009 at 2:50 am
Is there any use to use Witness and mirror on the same instance?
April 9, 2009 at 2:29 pm
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.
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]
April 22, 2009 at 9:58 am
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.
April 22, 2009 at 11:51 am
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.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply