SQL Server Performance

Inexpensive SQL 2000 Failover

Discussion in 'SQL Server Clustering' started by Invader Zog, Feb 20, 2004.

  1. Invader Zog New Member

    My client wishes to achieve automated failover with SQL 2000 but they are trying to operate on a tight budget.

    I don't believe that purchasing cluster-certified hardware (including a supported shared disk array) is going to be a viable option for them.

    As I understand, Veritas has their own clustering software that is more forgiving about hardware, but I hvaen't had a chance to look into their costs -- its easy to imagine that the cost of the software could be not too far off from what it would cost to purchase a supported disk array.

    I looked into some of the Windows 2003 clustering options for geographically disparate systems (since they would not need a shared disk), but this configuration looks extremely complicated.

    I did some more research and found an offering from a company Incepto called SQL UP! that claims to cluster stand-a-lone SQL servers. I am in the process of setting up a test environment to evaluate the product, but I am nervous at not being able to find much information about its production usage.

    Does anyone else have any suggestions?
  2. Argyle New Member

    I have no experience with the other solutions you mention so I can't give any input on those. Don't think any of them are supported from a Microsoft point of view though but they could be worth a try.
  3. satya Moderator

    As suggested by Argyle, if you opt for a third party solution for cluster then choose carefully as most of them will prove expensive in due course.

    For information review these Technet articles :
    http://www.microsoft.com/technet/tr...hnol/sql/deploy/confeat/sqlha/rag/harag05.asp
    http://www.microsoft.com/technet/tr...hnol/sql/deploy/confeat/sqlha/rag/harag04.asp

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. bradmcgehee New Member

    The real only "inexpensive" option is to use log shipping. Virtually all other options are expensive.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  5. Invader Zog New Member

    I got back preliminary pricing information on the SQLUP product. To implement a cluster of two single processor boxes would cost ~$5K. This is approximately the same cost as purchasing a Dell external storage array.

    Log shipping looks like the best way to prepare another SQL server to be a hot standby, but is it safe to assume that there is no way to automate the failover?

    I've never tried to implement log shipping -- what sort of impact does this have on the performance? How often should you synch the transactions? Their application is consuming 2-40% (8% on average) CPU on a 1.4Ghz P4 Xeon right now.

    I assume log shipping is set up per individual database. If the database contains some unrelated tables (i.e. not likely to be joined) that have different performance impacts (i.e. some of their ad-serving data changes rapidly but has a lower time-sensitive $ value compared to e-commerce transactions), would it be smart to split them out into another database and put them on a different log ship schedule?

    Thanks for the advice.

  6. Luis Martin Moderator

  7. satya Moderator

    Log shipping involves continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database.

    It doesn't affect any performance if unrelated tables are not used by any query, only transaction log will be backed up and it seems with the current H/w at your end will not have any issues with Log shipping performance.

    Also take help from books online for LS information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. bradmcgehee New Member

    In regard to log shipping, I don't know of any way to fully automate it. But if set up correctly, failover can be performed manually very quickly.

    Log shipping doesn't generally affect performance too much, especially if your system is not overburdened.

    Log shipping is done on an individual basis. I wouldn't add the complexity of splitting tables into multiple databases.



    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com

Share This Page