Avatar of CousinDupree
Flag for United States of America asked on

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?
Microsoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.

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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

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.

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?
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

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ão

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.

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

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.