Solved

How do I add additional disk to MS SQL server?

Posted on 2014-11-28
11
74 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
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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
 

Author Comment

by:CousinDupree
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:CousinDupree
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now