Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    44

    Unanswered: Moving SQL server DB's - Need advice.

    I've got a situation where I need to regularly (maybe each month), detach a DB, copy its files from their highspeed SAN location to a slower NAS, then re-attach it and make it available on-line. We're doing this for our DB's as they age to > 3 years.

    Just wondering if any of you have scripts you can point me to so I don't have to re-invent the wheel.

    Also, after you re-attach, how do you verify the NAS DB is 100% ok before deleting the original from its SAN location?

    Any help/advice is appreciated.

    Thanks,

    Isaac

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Where to start. Well, first off, SQL Server is not supported on a NAS. A few lost packets, and you have a corrupt database. Sure, you can be lucky for a while, but luck tends to take long lunches without warning.

    Secondly, if the business is not willing to shell out the cash to support all this data, then the business had better get used to the idea of getting rid of that data.

    Lastly, the whole "as they age to > 3 years" makes the architect side of my head ask all sorts of questions like "Is this a situation where a new database spawns every year?" DBAs really love those for the reasons you are just now seeing. Probably the original developer thought it was a fantastic idea.

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by ieruiz
    I've got a situation where I need to regularly (maybe each month), detach a DB, copy its files from their highspeed SAN location to a slower NAS, then re-attach it and make it available on-line. We're doing this for our DB's as they age to > 3 years.

    Just wondering if any of you have scripts you can point me to so I don't have to re-invent the wheel.

    Also, after you re-attach, how do you verify the NAS DB is 100% ok before deleting the original from its SAN location?

    Any help/advice is appreciated.

    Thanks,

    Isaac

    Although it may take a bit more time, I would do a backup/restore and add a verify in there to make sure all is well, you just have to deal with the time of backing up and restoring. If time is of the essence, scratch this idea.

  4. #4
    Join Date
    Dec 2003
    Posts
    44
    For further info, my DB's are read-only and serve analytical purposes only - no transactions or ods type of activity. So I'm not sure how the DB can get corrupted when write activity isn't possible - if you can say more about that, it would be appreciated.

    Also SQL server can access a NAS device. You just need to turn a trace flag on, and you can then refer to the location via a full UNC name. We've already successfully tested that.

    In terms of "architecture" your observation is correct. New DB's are spawned monthly (this is a legacy architecture going back a decade). There are plans to revamp, but funding has yet to materialize for the effort. We're trying to age our data out of expensive SAN and onto cheaper NAS. Eventually it will be permanently "shredded" but this is step one.

    The backup-restore idea sounds promising. This will be a weekend running process so I'm not worried about a little extra time if it means more reliability.

    How else can this be done?

    Thanks,

    Isaac

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    One way (ironically) is by backing up the database. On any FULL or DIFFERENTIAL backup, all the page headers get updated to mark when the backup happened. But, since you got as far as the trace flag, I am going to guess you already know the risks.

  6. #6
    Join Date
    Dec 2003
    Posts
    44
    I did get as far as the trace flag, but what I've learned about the risks is that they apply to databases you're actually updating. Is that not true?

    What can go wrong with users querying a database marked "READ-ONLY" that's residing on a NAS device? Can you say more about that?

    Thanks for your reply. Much appreciated.

    Isaac

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by ieruiz
    For further info, my DB's are read-only and serve analytical purposes only - no transactions or ods type of activity. So I'm not sure how the DB can get corrupted when write activity isn't possible - if you can say more about that, it would be appreciated.

    Also SQL server can access a NAS device. You just need to turn a trace flag on, and you can then refer to the location via a full UNC name. We've already successfully tested that.

    In terms of "architecture" your observation is correct. New DB's are spawned monthly (this is a legacy architecture going back a decade). There are plans to revamp, but funding has yet to materialize for the effort. We're trying to age our data out of expensive SAN and onto cheaper NAS. Eventually it will be permanently "shredded" but this is step one.

    The backup-restore idea sounds promising. This will be a weekend running process so I'm not worried about a little extra time if it means more reliability.

    How else can this be done?

    Thanks,

    Isaac

    You can always use a DTS/SSIS Job to copy DB objects, but having a hate of DTS to do this type of operation, I would avoid it.

  8. #8
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Backup/Restore...keep it simple

  9. #9
    Join Date
    Dec 2003
    Posts
    44
    What are the risks (if any) of having a read-only SQL database on a NAS device?

  10. #10
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Based on my experience...

    1. I don't care if it's read only or not. Physical disk issues cause corruption. NAS devices tend to have a lower MTF than SAN or local disk arrays. Also depends on how your NAS is configured. Raid 0? Raid 5? Raid 10?

    2. Again, on the disk config, how slow is it compared to other arrays? Any latency issues with the application? Timeouts?

    3. How's your connectivity? You can't read what you can't connect to. Any minor network issue = no data for you. And, you'll have to restart SQL when the network does come back up, dropping connections to any other DBs that instance may be serving, whether they're on NAS or not.

    I'm sure there are other, and better reasons not to put DBs on a NAS. Those are the first few that came to my mind. In my opinion, that's enough.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

Posting Permissions

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