Post reply

Log shipping and database mirroring

  • July 21, 2010 at 1:12 am

    #141328

    Log shipping and database mirroring

    Hi,

    I have tried with the log shipping and worked fine.

    I have also done the manual failover.

    This i have tried at backend.

    I have a question, how does the client application comes to know the failover has happened

    and need to be connected to the secondary server which will now be acting as Primary Server.

    What piece of code / logic they implement to redirect the transactions on to new server.

    Will be there any continuous pinging logic written within an IF condition.

    I beleive the pseudo code some what looks like below

    IF(PrimaryIsAlive)

    -- connect to Primary Server

    ELSE

    -- connect to secondary server

    I have the same question in mind with respect database mirroring as well?

    How will the client is redirected to mirrored server when Principal is down?

    Any good links on this and regarding the role_change process?

    Thanks in Advance.

  • July 21, 2010 at 2:34 am

    #1196008

    Hi,

    The application connection string will contain the initial partner name and the failover partner name provided.

    This helps to identify the failover server if the initial server is not reachable.

    Below is a nice link to know more about it.

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

    Thanks,

    lakshmi:-)

  • July 21, 2010 at 3:41 am

    #1196032

    Thanks Laxmi.

    Is there anything similar for log shipping ?

  • July 21, 2010 at 6:39 am

    #1196111

    no.

    the favorite method is for the application to point to a DNS alias rather than a hardcoded server name. Then on failover the DNS Alias is repointed to the new server.

    ---------------------------------------------------------------------

  • July 21, 2010 at 11:01 am

    #1196309

    Hi George,

    Would like to know more on this. This is what i understand. Correct me if am wrong.

    The primary and Secondardy has their own static ip addresses and hostnames.

    Are you refering to the hostnames as alias names / DNS names which will be used inside the .Net application or whatever for that case ?

    How do we swap the alias names as Primary server name to Secondary Server name and vice versa?

    Am trying to understand what exactly we ll mention inside the application?

    Any Code example/pictorial representation in JPEG/ GIF, will be easy to understand.

    Thanks

  • July 21, 2010 at 11:27 am

    #1196335

    Hi

    Automatic Failover is possible only with DB Mirroring. You need to specify the secondary server as Failover Partner in the app connection string.

    For Log Shipping Automatic Failover is not possible possible. You have to do manual failover. There are 3 options.

    1. Use DNS Alias as George mentioned.

    Suppose that app connects to the primary Server A with IPAddress A and the Secondary server for the log shipping is Server B with IPAddress B.

    Now Use DNS alias with a DNS entry for the Primary server i.e Application uses DNS alias name (Server A -- Which points to IP Address A) but not the actual server name to connect.

    If primary is down change the DNS Alias Server A to point to IPAddress B which automatically redirects all the connections to the secondary server.

    This is the best method. Nothing needs to be changed from the App end.

    2. Use SQL Server Client alias Server A on the box that hosts the application.

    If primary is down change the SQL Server Client Alias Server A to point to IPAddress B which automatically redirects all the connections to the secondary server.

    With this approach, you need to configure aliases on all the client machines that connect to the Server A. Nothing needs to be changed from the App end.

    3. Manually update the application connection string with the Secondary server name.

    Thank You,

    Best Regards.

    SQLBuddy

  • July 21, 2010 at 11:55 am

    #1196362

    Last question.

    Which one of the above mentioned is normally used in most scenarios?

    Again, we post a down time to application users during the failover and perform either of the above steps right? Trying to understand how industry follows..!!!

    Thanks so much.

  • July 21, 2010 at 12:34 pm

    #1196414

    1 is the best method. If 1 is not possible go for 2. Don't use 3.

    Best Regards,

    SQL Buddy

  • July 21, 2010 at 12:39 pm

    #1196420

    Also……If the application uses hardcoded IPAddress to connect to the server then you have no other option than choosing 3.

    Thank you,

    Best regards,

    SQLBuddy

  • July 21, 2010 at 12:51 pm

    #1196435

    Thank You!!! 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

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