October 17, 2011 at 4:36 am
Hi,
I want to perform SQL2KR2 Mirroring Configuration with witness server. for automatic failover perform.
1. Principle Server - (port 5022 opened) and installed SQL2KR2 on default instance, IP Address- 10.4.54.XXX, database is set full recovery option
2. Mirror Server - (port 5023 opened) and installed SQL2KR2 on default instance, IP Address- 10.4.54.XXX, database is full recovery option with Norecovery, database is restoring mode
3. witness server - Port 5024 opened nd installed SQL2KR2 on default instance, IP Address- 10.4.52.XXX
Please tell me, default instance both principle & Mirror & witness servers can able to perform database Mirroring also witness server have different Network?
Thanks
ananda
October 17, 2011 at 6:34 am
that seems like a decent setup.
Using the default instances shouldn't make a difference and as long as the witness can see the other two servers it should work.
October 17, 2011 at 9:43 am
ananda.murugesan (10/17/2011)
Hi,I want to perform SQL2KR2 Mirroring Configuration with witness server. for automatic failover perform.
1. Principle Server - (port 5022 opened) and installed SQL2KR2 on default instance, IP Address- 10.4.54.XXX, database is set full recovery option
2. Mirror Server - (port 5023 opened) and installed SQL2KR2 on default instance, IP Address- 10.4.54.XXX, database is full recovery option with Norecovery, database is restoring mode
3. witness server - Port 5024 opened nd installed SQL2KR2 on default instance, IP Address- 10.4.52.XXX
Please tell me, default instance both principle & Mirror & witness servers can able to perform database Mirroring also witness server have different Network?
Thanks
ananda
are you mirroring for a workgroup or a domain?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 17, 2011 at 10:03 am
The network won't matter as long as all the machines can communicate over the specified ports.
Are you getting an error?
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
October 20, 2011 at 1:00 am
I have successfully configure database mirroring setup without witness server and manual fail-over, high-protection mode.
Check for mirroring endpoints with the following query:
select name,type_desc,state_desc,port,is_dynamic_port,
ip_address from sys.tcp_endpoints
nametype_descstate_descportis_dynamic_portip_address
Dedicated Admin ConnectionTSQLSTARTED01NULL
TSQL Default TCPTSQLSTARTED01NULL
MirroringDATABASE_MIRRORINGSTARTED50220NULL
Please help me, for the testing purpose how to do that change mirror server to principal server.
Thanks
ananda
October 20, 2011 at 1:21 am
You have to do a manual fail-over
On the mirror
ALTER DATABASE <database name> SET PARTNER OFF
RESTORE DATABASE <database name> WITH RECOVERY
when the principal is back online you have to re-establish the mirroring session.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
October 20, 2011 at 2:55 am
Thanks for reply...
I have doubt in this case, alreday application connect to Principal server IP, after change mirror server to principal server by using
Alter database <databasename> set partner off
Restore database <databasename> with recovery
Again i have to change IP address of (existing mirror server) application side at web.config file.
thanks
ananda
October 20, 2011 at 3:08 am
This will remove the mirror session!
kapfundestanley (10/20/2011)
You have to do a manual fail-overOn the mirror
ALTER DATABASE <database name> SET PARTNER OFF
RESTORE DATABASE <database name> WITH RECOVERY
when the principal is back online you have to re-establish the mirroring session.
With safety set to FULL you may test the failover from one database to another use the following at the principal
ALTER DATABASE MyDB SET PARTNER FAILOVER
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 20, 2011 at 3:57 am
Thanks Perry Whittle for reply
It is working fine manual failover after run this command as below,
In principal server
ALTER DATABASE MyDB SET PARTNER FAILOVER. after that mirror server become a principal server. and principal server become a mirror server.
please suggestion me, What changes required in ASP web application for database connectivity? whenever modify ALTER DATABASE MyDB SET PARTNER FAILOVER.
Thanks
ananda
October 20, 2011 at 4:01 am
Oh,I had missed the last part...Thanx.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
October 20, 2011 at 4:04 am
It depends with how your application connects.If you are using SQ L Server native client,it will know that the server has changed.Otherwise you will have to manually change the connection string.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
October 20, 2011 at 4:06 am
ananda.murugesan (10/20/2011)
please suggestion me, What changes required in ASP web application for database connectivity? whenever modify ALTER DATABASE MyDB SET PARTNER FAILOVER.Thanks
ananda
The instance name in the connection string would obviously need to change 😉
before failover
Data Source=Myserver1\INST1;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
after failover
Data Source=Myserver2\INST2;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 20, 2011 at 4:09 am
ananda.murugesan (10/20/2011)
please suggestion me, What changes required in ASP web application for database connectivity? whenever modify ALTER DATABASE MyDB SET PARTNER FAILOVER.Thanks
ananda
The instance name in the connection string would obviously need to change 😉
before failover
Data Source=Myserver1\INST1;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
after failover
Data Source=Myserver2\INST2;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
kapfundestanley (10/20/2011)
It depends with how your application connects.If you are using SQ L Server native client,it will know that the server has changed
Only if you use the FailOverPartner option within the connection string
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 20, 2011 at 4:18 am
Thanks for all reply..
Mr. Perry said - Only if you use the FailOverPartner option within the connection string.
One last question, For the automatic failover option if I am using witness server, this case also using FailOverPartner option within the connection string?
thanks
ananda
October 20, 2011 at 4:21 am
high availability is the only mode that can use FailOverPartner as the other modes are manually failed over, with the witness it's automatic and so requires the connection string option above. This is only available through ADO.NET or SQL native client
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply