migrating SQL data to new storage.

Question

Currently I have an old storage solution connected by a 2008R2 2-node active/passive cluster running an instance of SQL. It has a dynamic S: drive with separate dynamic volumes mapped to database , temp and logs accordingly. There is the quorum as well but moving it shouldn't be a problem. What I'm having problems with is how to move all the data to my new fiber storage keeping the mappings intact. When checking Disk Manager I see like 7 small online unallocated disks which I'm not sure what they are and how to check. On the storage side there are identically sized LUNs. Just not sure if their status as Unassigned really means that or are they somehow put together to form the Data volume? There is also a Basic disk with Healthy in status but doesn't have any letter assigned nor path and no option to Open. I can't match the size it shows to match it to any of the storage based LUNs. Some of the volumes are mapped to folders within the S: drive related to SQLs temp and logs files. But for what I understand I can only map a new volume to an empty folder. Does it mean I'll have to create corresponding folders for temp and logs, move the content and delete the originals to correct the naming?

Answer 1

Hi @YaroC ,

migrating SQL data to new storage...

Please reference the reply from this case: sql-database-migration-on-new-storage

Just not sure if their status as Unassigned really means that or are they somehow put together to form the Data volume?

You can check the Failover Cluster Manager to make sure whether the storage is assigned or not.
execute code as next:

use dbname  
go  
  
SELECT  
   current_storage_size_mb,  
   max_storage_size_mb,  
   actual_state_desc,  
   readonly_reason,  
   stale_query_threshold_days,  
   size_based_cleanup_mode_desc  
FROM sys.database_query_store_options;  
GO  

More information: nodes-storage, check-sql-server-query-store-storage-utilization-with-tsql

Does it mean I'll have to create corresponding folders for temp and logs, move the content and delete the originals to correct the naming?

Yes. You need to firstly create the new file and then move data.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

I don't have access to the DB to run the query. I have a listing though of all the LUNs with their sizes. I was thinking of robocopy as the data migration tool. I can create a new storage and assign say say T: letter to it then mirror my S: .Logs and temp on the storage side are separate LUNs though which on the server side are mapped to corresponding folders within the MSSQL folder structure which I can confirm with mountvol tool where I see tempdb and defaultlog references. I suppose with SQL offline I'd need to create empty folders in the same location with similar names to map the new LUNs to them and then robocopy the data between the folders. Once done I think I could then change the letter of the S: drive to something else and convert my T: to S:, rename tempdb to like tempdb.old and same for defaultlog, rename the tempdb_new to just tempdb same for defaultlog (lots of renaming going on here) and then get SQL started. In theory this should work unless SQL is doing something funky to somehow recognize that a swap happened and not liking the new setup.

Answer 3

Hi. It greatly helped me to plan for what need to be done although still missing some bits I asked in a separate thread.