Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Changing Storage for your SQL Server

A friend pinged me the other day with a question on SQL Server storage. He works as a Windows consultant, a fairly high end Windows/Hyper-V/Exchange guy, and knows a lot. However he likes to double check his plan with an area expert, which in this case was me. It’s a good plan, and it’s one that I use as well. If I need to make Windows changes, or network changes, I’ll call a friend that is an expert in that area.

Anyway.

In this case, he was replacing the storage for a SQL Server. The instance was on a Windows Host and a new set of LUNs from a new SAN were being presented to the server. This was a SAN replacement, and the equivalent of adding new drives to your machine, and yanking the old ones.

Fortunately this wasn’t a simultaneous replacement (add/remove in one step). He plan was:

  • backup all user databases
  • shut down SQL Server.
  • add new LUNs, map to new drives in windows
  • copy SQL Server mdf/ldf files over
  • remove old LUNs
  • map new LUNs to the same drive letters as the old ones
  • start SQL Server.

In this case the system dbs and SQL Server itself were installed on local drives, so they were unaffected. He wanted to make a minimum of effort and changes to the system.

I replied this was a good plan. SQL Server depends no Windows to present storage to it, using simple drive letters or mount points. If you change the underlying storage, but keep the drive letters or mount points the same and SQL Server will just pick up where it left off. As long as the path is there, SQL Server will be fine.

I still would recommend that you detach the databases first and then attach them after. Changes like this shouldn’t matter, but just in case, I like to make sure I can recover to the new storage easily.

Comments

Posted by Glenn Berry on 18 March 2010

I agree that detaching the databases first would be a good idea. I have seen Windows get unhappy about the rights on the data and log files if you just copy the data and log files from somewhere else.

Posted by Steve Jones on 18 March 2010

Excellent point on rights. Depending on how things are set, that could be an issue.

Posted by Anonymous on 18 March 2010

Pingback from  Creating a SQL 2000 database setup file?

Posted by stephen.lappe on 20 March 2010

I have recently done this on clustered servers.  Using the cluster recovery tool makes the job very simple.  Stop SQL Server, use the tool to replace your LUNs in the cluster, copy the files to the new LUN and restart SQL Server.  Done!

Posted by adam on 21 March 2010

I have recently done. Stop the SQL Server and Chnge the Database File Location in Services. start the SQl Server.It is Working fine

now

Posted by FADBA on 21 March 2010

If you take the detach / attach route.  Be certain to review the default database for the logins.  Sometimes they revert to a different DB like master where the login may not have permisions.

Posted by Greg Edwards on 21 March 2010

I'm building a new server, and just wondering if you've ever turned on MPIO on a W2008 server and mapped multiple LUNS for a drive? From what I've seen, this seems to be a way to push more data around very quickly, and wondering if you've done this, what your experience was.

Greg E

Posted by Dukagjin Maloku on 21 March 2010

From the post and from professional's comments I just take a professional experience and very useful info about SQL Server configuration with Storage Area.

Posted by Steve Jones on 22 March 2010

I haven't, Greg. When I worked with the SANs, it was W2003. I know that the technology changes, and I'd be curious if this helps you.

Leave a Comment

Please register or log in to leave a comment.