Sql-server – Moving DBs (including System DBs) to new LUNs on a single node cluster

clusteringsql-server-2008-r2

We have a single node cluster (SQL2K8 R2) currently utilizing storage on our old SAN. We'd like to migrate to storage on the new SAN without rebuilding or moving the user DB. The infrastructure team's idea is to present the new LUNs to the server/cluster and then move all the files to these new LUNs.

The obvious problem here is that the system DBs can't simply be moved via the methods user DBs can be moved. And, while I could use the documented methods to move the system DBs, under SQL2K8 and later, the Resource DB can't be moved at all. So, it doesn't seem like this is possible in a straightforward way.

I'm thinking this would possible using sort of a shell game of shutting down SQL Server, copying all files to the new LUNs, renaming the old LUNs, renaming the new LUNs to the original old LUN names, and then bringing up SQL Server. I've not tried this before but it seems like as long as the new LUNs eventually had the original name of the old when the instance came back up (and we'd properly presented the LUNs to the cluster), this might work.

I know this isn't ideal. Ideally, I'd move all these DBs to the existing "real" cluster on the new SAN but changing client connectivity is a challenge right now.

Any gotchas to consider? There's no failover to test since it's a single-node cluster.

Best Answer

As long as the path names remain the same for your database files, this approach should work. This assumes that when you say "copying all files", your are ONLY moving the database files (data/log) and not the SQL Server binaries. If your SQL binaries and/or resourcedb need to be moved, you have a much stickier task ahead of you.

Your biggest gotcha should be maintaining the directory and database file permissions. I would handle this using ROBOCOPY with the /copyall switch to move the files while keeping all perms and attributes intact.

While outside of scope, if your end goal is to move the database to your full cluster and you're hurdle is client connectivity, have you considered using a DNS Alias to redirect traffic? I understand that the connectivity change could be larger in scope, but it could give you a path to consolidating your databases to where you want them.

Related Question