Moving User Databases

[Timmkatt]

SQL 2005...Question; I have a free partition on my SQL server with lots of space. I would like to move only my user databases to that partition on the SQL server. Is there any reason to move the system databases also or is it fine to leave them in the default location? Will this have any effect on performance or is it insignificant that the user and system databases will be in seperate locations on the same server. Thank you

 

[gmmastros]

If they are different partitions on the same drive, there won't be any benefit to moving them (other than the obvious space issue).

There is a big benefit when you put a database and it's associated log file on to separate drives.

This article from Brent Ozar does a really good job of explaining this.

http://www.brentozar.com/archive/2009/02/when-should-you-put-data-and-logs-on-the-same-drive/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Timmkatt]

Not sure that answered my question...I only want to move my user database files to the free partition and leave the master, model, msdb and temp db where they are in the default location C:\program files\Microsoft SQL Server\MSSQL\MSSQL.1\Data. So basically my question is should I move them all, system databases and user databases to the same location on the free partition D:\SQLDATA or is just moving the user data bases ok and in doing so will not create any performance decline?

Thanks!

 

[gmmastros]

I would recommend that you only move the user databases. Moving the 4 system databases is probably more trouble than it's worth.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[katekis]

I have a similar question

We have one SAN with the following volumes. We have both the Database (.mdf) and the Log (.ldf) on the same volume mapped to the same drive.

Current Setup Example:

Server1 - MiscDBs - e:\

Server2 - MiserBA - g:\
Server2 - AppXndr - h:\
Server2 - MiscDBs - i:\

Server3 - MiscDBs - e:\
Server3 - ApproDB - f:\
Server3 - AkcelDB - g:\
Server3 - SharePointDB - h:\
Server3 - OfficerDB - i\

Server4 - MiscDBs - i:\

Currently the Large Databases have their own volume. There is just one SAN. Should the large databases with high activity levels have thier database/log on Separate Drives? Would this example setup be better?

New SAN Volume Drives Setup??:

Server1 - MiscDBs - e:\
Server1 - MiscDBs_Logs - f:\

Server2 - MiscDBs - i:\
Server2 - MiscDB_Log - j:\
Server2 - MiserBA - m:\
Server2 - MiserBA_Logs - n:\
Server2 - AppXndr - s:\
Server2 - AppXndr_Logs - t:\

Server3 - MiscDBs - e:\
Server3 - MiscDB_Logs - f:\
Server3 - ApproDB - g:\
Server3 - ApproDB_Log - h:\
Server3 - AkcelDB - i:\
Server3 - AkcelDB_Log - j:\
Server3 - SharePointDB - k:\
Server3 - SharePointDB_Log - l:\
Server3 - OfficerDB - m:\
Server3 - OfficerDB_Log - n:\

Server4 - MiscDBs - i:\
Server4 - MiscDB_Logs - j:\

Currently we have the system databases (master, tempdb, model, msdb) all on the MiscDB Drive. Would it be any more efficient for these to be different? Any comments?

 

[Rene1024]

I also have a similar question. My server ran our of space, so I copied all my .mdf and .ldf files to a NAS, I then mapped a drive on my SQL server to the folder on the NAS, but I need to know how to point my SQL 2000 server to the new location of the files.

is that possible?

Best Regards,

Rene

 

[mrdenny]

Rene,
That isn't a supported configuration. SQL Server doesn't support using NAS storage for SQL Server databases. You'll need to purchase more local storage for the SQL Server to use.

katekis,
Yes large databases should have their mdf and ldf files on different physical LUNs which have been presented from the SAN. These LUNs should be built on different spindles within the storage array. If you use a storage array where this isn't possible (like an IBM VIX, 3PAR, etc.) then that is fine, these arrays are designed to spread the load over all the disks. System databases has little to no IO load, and can pretty much go anywhere. The exception would be if you attempt to restore a database via the UI the msdb database will be hit very hard, especially on a system that has been around for a while and takes frequent log backups.

Timmkatt,
Is the D drive a partition on the same physical spindles as the C drive, or is it different media? Having the user databases on one drive and the system databases on another will not impact performance in a negative way.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog