Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Unanswered: Disaster Recover , Please recomend

    We are a small organisation , we have sql server 2008 standard installed on in house production server and we have one remote server ( which we are planning to use in case of disaster)

    we have about 20 databases, which one of the following is best suited
    Largest Database size is not more than 40 gb shall i set up


    1. Logshipping or
    2. Mirroring

    for all the databases?? will that use a lot of resources?? please suggest

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    I have a similar size setup to yourself, also using 2008 standard on our production server - 25 databases ranging from 3 up to 50GB in size.

    Which option you go for depends on your recovery needs - do you need to be able to recover to an immediate state or in a failover situation can you lose 10-15 minutes or so of work.

    In my setup I take log backups every 15 minutes on each database and ship them to the secondary server which checks for new logs periodically and restores them. In a failure situation we can bring the database online on the new server and lose at most 15 minutes worth of data - not a big deal in my situation, but if it was we could always bring the log backups closer - every 5 minutes for example.

    You may have very different requirements however and synchronous mirroring in high-availability mode might suit your needs better to give you more immediate automatic failover with only minor loss of data, however it incurs a performance cost since transactions have to be committed to both the primary and the mirror before confirmation is returned to the client. Given you're talking about a remote server you would need to determine if you have a sufficiently fast (and reliable) network link between your in-house and remote backup servers to make this a viable option.

  3. #3
    Join Date
    Sep 2010
    Posts
    153
    If you have two servers then why don't you go for clustering instead of log shipping or mirroring? There is every chance of data getting corrupted and time management problem in mirroring.

  4. #4
    Join Date
    Aug 2009
    Posts
    73
    @Sunny_007 Clustering is not possible in SQL 2008 Standard edition

  5. #5
    Join Date
    Aug 2009
    Posts
    73
    @EngadaSQL I cannot afford to use Synchronous mirroring, as we have second server at remote location, so any kind of network delays or disconnection will affect,
    i havent done mirroring before so was just wondering, as we are taking full backups on production twice a week, so do i need to restore these full backups twice and again restore the mirroring session ??? when i say mirroring i mean asynchronous mirroring

  6. #6
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Quote Originally Posted by amitwadhawan123 View Post
    @EngadaSQL I cannot afford to use Synchronous mirroring, as we have second server at remote location, so any kind of network delays or disconnection will affect,
    i havent done mirroring before so was just wondering, as we are taking full backups on production twice a week, so do i need to restore these full backups twice and again restore the mirroring session ??? when i say mirroring i mean asynchronous mirroring
    Mirroring works outside the backup cycle - you can continue to take your two weekly full backups and any other log backups without the need to restore them and restart your mirroring session.

    If you have to recover your primary database with a restore then you will need to restart your mirroring and if you take log backups with the norecovery option that will kill the mirroring session also - neither of those should be happening often, or prefereably even ever.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by amitwadhawan123 View Post
    @Sunny_007 Clustering is not possible in SQL 2008 Standard edition
    A 2-node cluster is available in Standard edition. I run several of them.

  8. #8
    Join Date
    Aug 2009
    Posts
    73
    @MCrowley i am new to clustering as i havent implemented it before , though i have read that two servers should have identical configuration, but is it possible that one server having windows 2003 and other having windows 2008 R2, do you have any step by step documentation to set up windows clusterig , as we dont have a windows admin in our company , its a small company, i will appreciate if you can send me any document, my email address is manishaasthag@gmail.com

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You can not cluster a 2003 and 2008 machine together. There were too many changes between clustering services in 2003 and 2008. In fact, it is best to keep the two nodes at the same patch level.

    There is a white paper you can download from here which details how to set up a cluster. You can stop before the file and print services are configured. Books Online (BOL) should have steps on how to create a two-node cluster, as well.

  10. #10
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    You also, to my knowledge, cannot cluster two machines unless they are on the same domain and subnet.

    The OP has specified the second server is at a remote location, and given they are a small organisation probably means a hosted data centre rather than a second directly linked office. This as far as I am aware would preclude clustering from being a viable option.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There have been rumblings of plans for a geographically heterogeneous cluster option for Windows. I know the company that makes DoubleTake software, also has a GeoCluster package that can be used for such a case. Never used either, though.

  12. #12
    Join Date
    Aug 2009
    Posts
    73
    @EngadaSQL and @MCrowley yes our remote server is at datacenter which is located in Atlanta, we are in Florida , but this remote server is part of our domain, but main issue is connectivity , if their is any slight network disconnection , i am not sure how its going to work, do u guys still think clustering is a good solution

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would go with mirroring in this situation. Just don't configure an automatic failover.

  14. #14
    Join Date
    Aug 2009
    Posts
    73
    as we have only synchronous mirroring available , so transaction wont commit on principal server , till they got commited on mirror server, Network problem can affect this too............ how about if we have both principal and mirror server in Florida.... and i replicate the mirror server to Atlanta.... is it possible ??? so that network issues are minimised...thoughts???

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sporadic short network connection problems are not a big problem for mirroring. Are you intending to go with FULL transaction safety? The reliability of the circuit is going to feed into that decision. If the circuit is unreliable, i would tell the business folks that all I could deliver with confidence is a snapshot of the database, and even then, it could be a day or two out of date. Simply take a backup of the database to local disk (you can even do differential backups during the day, too), and copy them any old way to the server in Atlanta.

    Keep in mind, the mirror database is in a loading state, and you would need Enterprise edition in order to make a read-only snapshot of that database. I somewhat doubt the company is willing to spend $20K on SQL Server licenses, and only $1k on network connections to sufficiently support it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •