Solved

How do I add additional disk to MS SQL server?

Posted on 2014-11-28
11
77 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 77

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 77

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 77

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 77

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 47

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Creating a summarized report 19 35
insert wont work in SQL 14 22
T-SQL Default value in Select? 5 27
SQL invalid column name 5 13
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…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

777 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