SQL migration from one lun to another

Dear Experts,

Using SQL 2014 Enterprise

What is the correct procedure to relocate the sql database and log from a smaller lun to a bigger one

I only know that with the cmd, I must execute xcopy /X /E e:\*.* to g:\*.*
LVL 1
Anonymous KHIT EngineerAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
  1. Stop the SQL Server service
  2. Present the new lun to the machine
  3. Copy the files from the old lun to the new lun
  4. Remove old lun from the machine
  5. Give old lun's drive letter to the new lun
  6. Start SQL Server service
  7. Check if database are up and running
1
 
Anonymous KHIT EngineerAuthor Commented:
Hi!

What I am currently doing is

1) detach database
2) In E:\MSSQLSERVER01> xcopy /E /X from E:\*.* to L:\MSSQLSERVER01\*.*
3) Then I attach back the database

Is there a faster way or is there a way to move the whole chunk like xcopy /X /E from E:\*.* L:\*.* ??
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The faster way is how I told you.
If you're doing as you just told me, you'll need to detach all databases and attach them back. My method doesn't need to detach and attach databases because you stop the SQL Server service and maintain the drive letter so when you start the SQL Server again it will find the databases in the expected path.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Anonymous KHIT EngineerAuthor Commented:
Hi! Vitor,

I forgot to tell you that the server is receiving SQL replications from other Production servers

The stopping of SQL service is it at the services.msc?

I am worried stopping the SQL service will get [Recovery... Pending]
0
 
Anonymous KHIT EngineerAuthor Commented:
Just to let you know, I have zero or little knowledge of SQL and how it runs
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, if you're going to detach databases then Replication will raise errors.

I am worried stopping the SQL service will get [Recovery... Pending]
I'm expecting you to do this during no database activities period. Detaching a database or stopping SQL Server service during high workloads will make you loose important data and might raise the behaviour you just described.

Oh, I also didn't say but I'm expecting you to perform a full backup of all databases before proceed with this operation.
1
 
Pawan KumarDatabase ExpertCommented:
Please try this if the above suggestion did not work.

Create new LUN
Stop SQL server
Copy data from old LUN to new LUN
Confirm copy correct
Disconnect new LUN {DO NOT DISTROY!}
Take old LUN out of SQL server dependancy
Take the drive out of cluster with snapmanager
Rename old lun to another name
Rename new lun to match you naming standard
Mount New LUN into cluster with snapmanger and old LUNs drive letter
Add as SQL dependancy
Start SQL server
Confirm every thing is good
Delete old LUN

Open in new window


from - http://community.netapp.com/t5/Backup-and-Restore-Discussions/How-to-migrate-SQL-Server-database-from-one-LUN-to-another-in-same-Volume/td-p/10688
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Pawan, what's that differ from my provided solution?
0
 
arnoldCommented:
Which SAN is involved,
I'm with Pawan's, suggestion SAN level copy is much more resource ....
Instead of data flowing San => Windows => back to San new LUN.
San copy will/could

Detach/attach is the reason.
Stopping, altering drive letter assignment achieves the same thing ......
Point being Pawan's original system's filesystem layout remains the same, yours  presumes both LUNs remain.

The question is narrow.
If the asker has the option or intends to keep both LUNs, a combination...

Migrating some data from the current LUN to the new LUN ......
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
We don't know the disk system or systems used by the author that's why my point 3 it's only copy the files between LUNs. Depending on the complexity it can be OS copy level, DISK copy level (if the LUNs belong to the same storage solution) or even storage migration (if LUNs are from different storage solutions).
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Before you do anything maybe its a good idea to take a full backup of your database/s...you never know when things can go bad...
0
 
Anonymous KHIT EngineerAuthor Commented:
We are using the the nimblestorage CS1000

When you say stop the sql service, is it stop the MS SQL Service agent only?
0
 
Anonymous KHIT EngineerAuthor Commented:
I realised after detach/attach, the sql replication could not run anymore as they some UPDATE rows were missing as I did the detach when the sql is still updating.
0
 
arnoldCommented:
Copy, requires that every access to the files is stopped, all SQL service components have to be stopped. Sqlserver, SQL agent, etc.
0
 
Anonymous KHIT EngineerAuthor Commented:
I remembered my colleague told me to

1. Stop the sql service - he demoted from a failover cluster and also reminded me not to stop the sql service from services.msc. So I am confused on how to stop the sql service on a non-clustered server.

2. Xcopy the contents of the smaller Lun to the bigger Lun

3. Swap the drive letters of both Lun

4. Start up the sql service again

This method should work.
0
 
arnoldCommented:
IN a cluster, you would go to cluster admin manager, and stop the SQL application there.
In a cluster environment, you would attach the new larger LUN as a storage resource of the cluster.

You have to make sure the new LUN is properly zoned to be accessible on all nodes of the cluster.

I would suggest you contact nimble storage to look at whether they have examples on transitioning SQL from LUN to a larger LUN.
The access reads and writes from and to the storage will fast, your issue as mentioned when you are using the active node the data is read from lunA into node memory and is copied out to LUN B.
There must be a process on the nimble storage that could facilitate the storage level copy of LUN data which would be significantly faster ....
0
 
arnoldCommented:
Which OS is running the storage?
See if the following can provide you the option of growing the exiting LUN.

Make sure you have good (meaning backed up and successfully restored) before trying.

http://uploads.nimblestorage.com/wp-content/uploads/2015/05/22100302/bpg_nimble_storage_linux6.pdf
0
 
Anonymous KHIT EngineerAuthor Commented:
The nimble storage is running on linux.

I will contact Nimble if there is afster copying solution.
0
 
arnoldCommented:
Depending on which OS the storage is running in, the link to the PDF may help you expand the existing LUN in size eliminating the need to copy, switch LUNs.
Test first and make sure to backup before trying.
Lvm based storage allocation can be expanded in production.
0
 
Anonymous KHIT EngineerAuthor Commented:
Contacted Nimble Storage and they said that the copy have to be done at the server end.
0
 
Pawan KumarDatabase ExpertCommented:
Thats good and a safe place.
0
 
arnoldCommented:
up to you which way to proceed.
You may wish to contact them in the event you did not provide a full context of what it is you need. I.e. often, the question you ask is the answer you get.
IMHO, provide the full context of what it is you have and what it is you wish to accomplish, and then see what the appropriate process they recommend.
I can understand the response to use the server to perform the copy,

not sure if you have sqlio/replaced by diskspd
https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

Use it to create a benchmark of a 2Gb DB
then copy this test DB from one lun to the next and using that information you can approximate how long it will take you to copy the real DB files.
0
 
Anonymous KHIT EngineerAuthor Commented:
Hi!

I did this.



    Stop the SQL Server service
    Present the new lun to the machine
    Copy the files from the old lun to the new lun
    Remove old lun from the machine
    Give old lun's drive letter to the new lun
    Start SQL Server service
    Check if database are up and running


What happened was that the SQL replication failed.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please provide the Replication error.
0
 
Anonymous KHIT EngineerAuthor Commented:
We used another method

1. Expand SQL Server Agent
2. Expand Jobs
3. Right click each of the 3 jobs per database and Disable them
4. After that stop them - Scroll down further to launch Job Activity Monitor
5. Detach database
6. Xcopy /X /E e: to L:
7. Attach back database
8. Enable 3 jobs
9. But only start Job 1 and Job 3.
10. Do not start Job 2 as it is a snapshot job (unrequired)
0
 
Anonymous KHIT EngineerAuthor Commented:
We used another method

1. Expand SQL Server Agent
2. Expand Jobs
3. Right click each of the 3 jobs per database and Disable them
4. After that stop them - Scroll down further to launch Job Activity Monitor
5. Detach database
6. Xcopy /X /E e: to L:
7. Attach back database
8. Enable 3 jobs
9. But only start Job 1 and Job 3.
10. Do not start Job 2 as it is a snapshot job (unrequired)
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Kevin, if your issue is solved please close this question by accepting the comment or comments that helped you resolving this issue.
Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.