Post reply

DB Mirroring question?

  • October 17, 2011 at 4:36 am

    #244734

    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

    #1395272

    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

    #1395417

    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

    #1395431

    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
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.

  • October 20, 2011 at 1:00 am

    #1397065

    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

    name type_desc state_desc port is_dynamic_port ip_address

    Dedicated Admin Connection TSQL STARTED 0 1 NULL

    TSQL Default TCP TSQL STARTED 0 1 NULL

    Mirroring DATABASE_MIRRORING STARTED 5022 0 NULL

    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

    #1397076

    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

    #1397130

    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

    #1397142

    This will remove the mirror session!

    kapfundestanley (10/20/2011)

    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.

    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

    #1397173

    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

    #1397177

    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

    #1397180

    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

    #1397182

    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

    #1397183

    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

    #1397192

    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

    #1397194

    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