Tempdb on non clustered disk

Discussion in 'microsoft.public.sqlserver.clustering' started by jan.devylder, May 8, 2009.

  1. jan.devylder

    jan.devylder Guest

    Hi,

    We are running a database on a streched cluster with a SAN. The tempdb
    is on a clustered disk and is replicated to the other site. This seems
    completely wasted to me, since when after failover the database
    restarts on the other site and recreates the tempdb.

    When I search this forum, I find that it wouldn't be possible and that
    someone had a message like this:

    Only formatted files on which the
    cluster resource of the server has a dependency can be used.
    Server: Msg 5184, Level 16, State 1, Line 2

    And of course a dependency is only possible on clustered resources.

    Is there somebody who has found a solution for this? We are usning SQL
    2005 on Windows 2008.

    thanks,
    Jan
     
    jan.devylder, May 8, 2009
    #1
    1. Advertisements

  2. There is no solution at this time. You can split tempdb onto a
    non-replicated disk at the SAN layer.

    I don't find a connect item (connect.microsoft.com) for this at this time,
    but you could ask.
     
    Geoff N. Hiten, May 8, 2009
    #2
    1. Advertisements

  3. jan.devylder

    Clyde Guest

    You can do it by tricking the system.

    I got around this issue by creating a temporary cluster resource disk with
    the letter T:. I then changed the temp DB location to the T: drive, stopped
    the sql service ,removed the temporary cluster disk, and changed the local
    drive to be the T: drive on both servers.

    The tempDB will then be created locally on each server.
     
    Clyde, May 27, 2009
    #3
  4. you could probably do the same sort of trick using a mount point to replace
    the location of tempdb, but I would be very uncomfortable doing this (or
    your suggestion clyde) on a cluster since the mount point dependancy should
    be configured into the cluster and it is unclear to me exactly what hidden
    nasties you are going to introduce. Understand why you would want to though.
     
    Mark Broadbent, May 29, 2009
    #4
  5. jan.devylder

    Linchi Shea Guest

    You could configure the SAN replication of the tempdb drive to be
    asynchronous, if not already. In addition, some SAN replication allows a mode
    that doesn't require acknowledgement from the other side (similar to what you
    get with UDP as opposed to TCP). This gives you performance, but doesn't
    guarantee data integrity. But then since tempdb is rebuilt on restart, you
    don;t care either.

    I tested this before and it works well with tempdb.

    Linchi
     
    Linchi Shea, Jun 2, 2009
    #5
  6. jan.devylder

    Linchi Shea Guest

    I'm with Mark here. You gotta be very careful. Even though SQL Server does
    appears to be checking for whether a drive is clustered only when it attempts
    to create/add a file to a database, the exact detail is not published and you
    just don't know when it'll check again.

    I wonder if you checked with MS on this. If they have no issue, it can be a
    good workaround.

    Linchi
     
    Linchi Shea, Jun 2, 2009
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.