Clustered SQL Server backup & local drives

  • Mateus Espadoto

    Mr or Mrs. 500

    Points: 564

    Sorry if I didin't get the point here, but

    I disagree with the affirmation that the backup drive must be in the SQL cluster group to be visible by SQL Server.

    I have a 2-node cluster on which I do the backups of the active node on a local drive, not shared, because of space limitations on

    our storage array. Then we do full server backups on a daily basis to maintain all the dumps, in a case of a failure.

    IMHO, this is a SQLEM limitation.

    Thanks.

  • David Burrows

    SSC Guru

    Points: 64445

    I have no experience or knowledge of clustered servers so I found the question impossible to answer. Took a guess and got it wrong

    I looked on the web and found the following on another site

    http://www.sql-server-performance.com/q&a79.asp

    which seems to disprove the correct answer!!

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Randy Rabin

    Ten Centuries

    Points: 1350

    Agreed with mespadoto. SQL can *backup* to any drive anywhere on the network, as long as it has proper permissions to a share on that drive. I thought this was a trick question (and got it wrong anyway <g>) turns out I guess I tried to read too much into it.

    I want my money back .. oh wait, it's free.... 🙂

  • Brian Knight

    One Orange Chip

    Points: 27249

    Just retested the answer again. Opened up EM for a SQL Server in a cluster, tried to backup to the C: drive and it bombed saying folder doesn't exist. Interesting thing was though that it did work in Query Analyzer. Pulled up a list of drives also to backup through EM and the C drive couldn't be seen there as well. The workaround would be to backup to the C drive via QA but you would never want to do that. Glad it's causing some debate!

    Brian Knight

    bknight@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bknight

  • Frank Kalis

    SSC Guru

    Points: 111183

    Hi David,

    quote:


    I have no experience or knowledge of clustered servers so I found the question impossible to answer. Took a guess and got it wrong


    I have also no experience on MSCS and haven't found it in BOL. From what I have read, I have explained it to myself this way:

    There can be more than one server in a cluster, right?

    So, when there can be more than one, who decides which C:\ drive to take?

    So it sounds logical to me, to disallow Backups to C:\.

    And I guessed right.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Mateus Espadoto

    Mr or Mrs. 500

    Points: 564

    quote:

    So, when there can be more than one, who decides which C:\ drive to take?

    Frank,

    SQL Server uses the C: drive of the active node on this case. It works that way for any drive not included in the SQL Server cluster group.

    So the backup is *not* disallowed by the server on local drives. The point is that EM blocks this for some reason.

  • Allen Cui-55137

    SSC Guru

    Points: 51653

    You can backup database to the local drive at active node but the backup will be inaccesable once SQL Server fails over to another node. For best practice, I would backup the database to the share drivers.

    Edited by - Allen_Cui on 06/05/2003 08:47:37 AM

  • David Burrows

    SSC Guru

    Points: 64445

    Hi Frank,

    Not to labour this point but I did read some articles on web regarding clustering which stated that each server had a C:\ drive where the software (ie SQL) was installed and that all the servers in the cluster must have the same software folder (e.g C:\Program Files\MSSQL7). This may be a difference between active/passive and active/active options, I don't know, I'm only a pleb where clusters are concerned.

    I attached a url in my first post that seemed to dispute the statement that you cannot backup to C:, it stated that there could be a bug or feature of EM that does not show C: in it's list, but you can specify the full path insteads and it would work.

    However since Brian has tried this and must know all about clustering, the info I found must be out of date or wrong.

    Nice to know someone has done this as my boss keeps mentioning such things to me as if I am the fountain of all knowledge (which I ain't).

    Oh! to be a guru and know all the answers eh!

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Mateus Espadoto

    Mr or Mrs. 500

    Points: 564

    Exactly.

    As I said, I use this approach due to a disk space limitation.

    I don't think that this is the best

    solution. Far from that...

    I was just saying that it is possible.

    Thanks

  • Frank Kalis

    SSC Guru

    Points: 111183

    Hi mespadoto,

    quote:


    SQL Server uses the C: drive of the active node on this case. It works that way for any drive not included in the SQL Server cluster group.


    thanks for explanation!

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • locklin

    SSC-Addicted

    Points: 449

    I agree with the argument that this is just a limitation of Enterprise Manager (albeit maybe intentionally to save people from themselves). SQL Server CAN in fact see local drives in a cluster. While I don't send my backups there, I do have other processes that interact with the C drive. It just happens to be the C drive of whichever machine happens to be running the cluster at the time. As others have pointed out, there are ways to send backups to the C drive in a cluster if you really want to. I think that the question/answer is a little misleading and could use some clarification.

  • Johan Bijnens

    SSC Guru

    Points: 134254

    OK, this all said. The quizz question was regarding EM. So for most of us points not won. btw that's not equal to points lost

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • jbheather

    SSC-Addicted

    Points: 409

    Maybe I'm dumb, but this thread just got me confused. So I tried a backup to c: on my cluster (2 nodes, SQL2K). It works just fine for me from EM. I did create the folder on c: first.

  • swhalen

    SSC Journeyman

    Points: 83

    I agree with those that think this question is badly flawed. As others have noted, SQL Server can in fact "see" local drives, so the "correct" answer is "incorrect" ... None of the other answers is worth writing home about so I think this cluster question should be tossed, and a better one on clustering included later ...

  • Brian Knight

    One Orange Chip

    Points: 27249

    Keep in mind that the question was on why can't Enterprise Manager see the local drives.

    Brian Knight

    bknight@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bknight

Viewing 15 posts - 1 through 15 (of 18 total)

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