SQL Server Performance

LS Failover Clients....

Discussion in 'SQL Server Log Shipping' started by sql_jr, Apr 24, 2007.

  1. sql_jr New Member

    Hi, forum:

    I have the log-shipping part down part, but need help w/the failover.
    Obviously, its a manual failover, but what are some best practice/real world solutions for automating this as much as possible. Specifically, re-connecting the clients to the sql server.

    1)If, side A goes down, and its IP is no longer available, how can I use an alias to have it reconnect? Does this require each client to have the SQL Server client util installed?

    2) Is just renaming the secondary server a feasible option, what is impact of changing that (sysservers)?

    Any other suggestions and ideas are welcome. Many thanks!
  2. satya Moderator

    Log shipping do not have such feature of providing automated feature, in any case the restore of latest log or checkup about logins is required.
    1. You have to specify the secondary server name in the connect string, if your application or client can connect to primary then it should be ok for Secondary server too.
    2. Do not attempt changing or updating system tables directly, which will create other problems .

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. sql_jr New Member

    thx, Satya:

    I guess I'm looking for ideas to "trick" or reroute the already existing client app to the secondary, say using DNS, etc. This is probably out of the sql server realm, but figured others might have done this. Regards.
  4. MohammedU New Member

    I believe you can alias server name in DNS..

    http://support.microsoft.com/kb/168322

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. satya Moderator

    .. Few legacy languages do not have such feature or using such DSN will tamper the automated process, by default Log shipping failover involves atleast 30 minutes time to complete the process (in my exp.) and you can cut it based on the practices on your database, that might take sometime depending upon the sizes & number of logins.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. sql_jr New Member

    Thx all for replies. LAST Question (hopefully <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />If we have MYSERVER and MYSERVER_DR, and as part of the Failover process is to rename MYSERVER_DR --&gt; MYSERVER, then all i need to do is run the sp_dropserver,sp_addserver, and restart SQL - Correct? This assumes the original is down/offline, and would eliminate the need for aliases. (All the IP stuff is handled by our DR Plan) tx
  7. satya Moderator

    There is no need to drop the server and add the server, as you have 2 servers acting as primary and secondary. Check this http://www.sql-server-performance.com/sql_server_log_shipping.asp for more information.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  8. MohammedU New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by sql_jr</i><br /><br />Thx all for replies. LAST Question (hopefully <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />If we have MYSERVER and MYSERVER_DR, and as part of the Failover process is to rename MYSERVER_DR --&gt; MYSERVER, then all i need to do is run the sp_dropserver,sp_addserver, and restart SQL - Correct? This assumes the original is down/offline, and would eliminate the need for aliases. (All the IP stuff is handled by our DR Plan) tx<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />If you are using the method of renaming the server as part of fail over...<br />1. Rename the server/ip<br />2. Reboot the server.<br />3. Clear all dns cache/entries..<br />4. Run sp_dropserver and sp_addserver <br />5. Restart sql serivices...<br />6. Test the server before allowing web traffic..<br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />

Share This Page