Solved

How do I add additional disk to MS SQL server?

Posted on 2014-11-28
11
79 Views
Last Modified: 2014-12-10
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?
0
Comment
Question by:CousinDupree
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 78

Expert Comment

by:arnold
ID: 40470991
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.
0
 

Author Comment

by:CousinDupree
ID: 40471003
It's all on an IBM fiber channel SAN.  The first data drive is 200 GB, I added a second 200 GB drive.
0
 
LVL 78

Expert Comment

by:arnold
ID: 40471016
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?
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

Author Comment

by:CousinDupree
ID: 40471018
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.
0
 
LVL 78

Expert Comment

by:arnold
ID: 40471032
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.
0
 

Author Comment

by:CousinDupree
ID: 40471046
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?
0
 
LVL 78

Expert Comment

by:arnold
ID: 40471066
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 ......
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40473283
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.
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 40475568
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:

ALTER DATABASE [YOURDB] ADD FILE
( NAME = N'yourdb_data2', FILENAME = N'C:\yourdb_data2.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB ) 
TO FILEGROUP [PRIMARY];

ALTER DATABASE [YOURDB] ADD LOG FILE
( 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.
http://msdn.microsoft.com/en-us/library/bb522469.aspx

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.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40475577
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.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40475619
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.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question