How do I add additional disk to MS SQL server?

I have a MS SQL server with separate system, data and log drives.  My data drive is getting tight on space, so I added a new drive to the server.  Now I need to know how to add the new drive to SQL server so it can be used, and move some databases to it.  How do I accomplish this?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What vendor/model is in use?
If youhave HW Raid controller, you need to first backup the data just in case, you then need to look whether the RAID group that makes up the ... Is expandable online.

If you have software based RAId, you would need to use the disk management interface to add the drive into the group.

MAKE SURE YOU Have good backups prior to make any attempts.
CousinDupreeAuthor Commented:
It's all on an IBM fiber channel SAN.  The first data drive is 200 GB, I added a second 200 GB drive.
How is the original data drive defined on the SAN?
If the LUN is defined as a single drive resource, you would need to go through a two step process to migrate the data. Create a new RAIDed LUN that is then allocated to the server, you would then need to while the sql server is stopped, copy the data on the SAN and then make adjustments to the drive letters on the server, or achieve the same thing migrating the data files (copy rather than move) and then .....

The other option is simply migrate the larger/growing the DBA to the new san allocated resource.

Are the data files growing, or are these other types of files that are using up the data drive?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

CousinDupreeAuthor Commented:
I created a new LUN just for this server.  One database is growing, currently around 145 GB.  I thought I'd move it to the new drive, but I'm not sure of how to best accomplish this task.
How long can this database, application be done?
Detaching this db, copying the data files, and reattaching in the new location.
Make sure to get the login settings db security\logins to make sure that when you reattach, it would function as before.
Make sure to backup the DB prior to an attempt.

Do you have access to the SAN to see how the LUN a is defined?  You should look at the IBM SAN manual on increasing/expanding a LUN size.
CousinDupreeAuthor Commented:
I thought that keeping the size around 200 GB made better use of the disk space, which is why I chose that size.  If this isn't true, I suppose I could just expand it?
Your san has multiple physical drives, which you then combine in a RAID group to present to the system.
Not sure where you got the 200GB limit.  The LUN can be as large as needed.
If you have many active databases, spreading them accross difference LUNS that reside on different physical disks could reduce the IO on a per disk basis.  The paths available to the SAN could also help once additional RAM is added when possible.

Often to optimize performance on a disk where there are going to be large files, one increases the block size from the 4k to 64,128,256MB, 512MB ......
Vitor MontalvãoMSSQL Senior EngineerCommented:
Now I need to know how to add the new drive to SQL server so it can be used, and move some databases to it.  How do I accomplish this?
If it's a stand-alone server then the drive is ready to use. SQL Server recognizes all local drives.
If it's a cluster then the SQL Server will recognize it if the drive is in the same node but if you change the SQL Server service to another node it will lost connection to the drive so you might want to add the drive to SQL Server group in the cluster. Also, you can configure the SQL Server service to be dependent of the drive.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Once you add the disk at system level and it the drive becomes visible, do the following

1>  Create a new primary data and log files on the new drive.  To do this, use the following command such as:

( NAME = N'yourdb_data2', FILENAME = N'C:\yourdb_data2.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB ) 

( NAME = N'yourdb_log2', FILENAME = N'C:\yourdb_log2.ndf' , SIZE = 10400KB , FILEGROWTH = 512KB ) ;

Open in new window

Please read the following for other examples.

2> then Close the old files to prevent them from continuing to expand.  To do that you simply fix the MAXSIZE to their current size +1MB.  You active that by using the ALTER DATABASE MODIFY FILE command.  Please read the following for information
3> You are happy with that you are done.  If you want to entirely migrate, you simply need to empty the old files using the DBCC SHRINKFILE command with EMPTYFILE option then delete the old files using the ALTER DATABASE DROP FILE : this will migrate all of your data to your new drive.

Note that you this operation is ONLINE and does not require a reboot.  Please take a backup before starting.

Hope this helps.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
As Vitor mentioned, if this is a cluster, please mount a new clustered drive first (Important: verify security all prerequisites) then move/expand your database.  You want to make sure that the new drive is visible from SQL Server on all nodes.  Make sure you check all dependencies of the new drive.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
A few word of caution about this kind of operations..

1> Please never assume this is an OFFLINE operation.  Some critical applications simply won't give you that kind of possibility.  Ask your business owner first.  Do it ONLINE.
2> Never, migrate/expand a database by simply playing with letters without knowing all security prerequisites.  Database Files are radically different from basic files and embed a tight security context in them.  
3>  Keep in mind SQL Server always fetches data pages by 8K chunks.  For random queries, use 32K block sizes.  For sequential queries set values to 1024K.
3> Do not use RAID5 for OLTP systems, ever.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.