SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Moving Data and Log files
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

urmas80
Starting Member

18 Posts

Posted - 07/30/2014 :  04:37:17  Show Profile  Reply with Quote
Hi All,

I have an old SQL server 2008 R2, hosted on HyperV, which is going out of disc space. all Databases and logs are set on C:/ in the default drive and I have about 248 files in that folder.

What might be a best practice to change the architecture of the server from one drive to 4 drives? (C:system, D: data, E: Logs, F: Backups)?

Thank you in advance

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/30/2014 :  12:43:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
We use mount points, like this:

F:\Backups (though NAS device is preferable)
F:\Data
F:\Log
F:\TempdbData
F:\TempdbLog

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 07/30/2014 :  15:21:23  Show Profile  Reply with Quote
urmas80 that is what we use with subfolders similar to Tara's suggestion. However our virtual is not setup properly and I am in the process of getting them to change that.

djj
Go to Top of Page

andrew321
Starting Member

USA
5 Posts

Posted - 07/31/2014 :  01:52:03  Show Profile  Reply with Quote
U can put 2 quantity on 1 raid and more than 2 or 4 on raid 10 means make C:/Windows, Backups, Batch Processing, Full Text Catalogs, Storage etc, D:/ for user defined databases and E:/ System Databases (TempDB), Transaction Logs etc according to your file size.
Go to Top of Page

urmas80
Starting Member

18 Posts

Posted - 07/31/2014 :  03:39:38  Show Profile  Reply with Quote
Guys,

You are taking this topic to a folder submission discussion.
My question is regarding a migration of the files to already submitted location. What is the best Practice to move the files to those folders from the default locations on C:\

*does it really matters if i set separate HD for each set of files?

Thank you in advance,

Uri

Edited by - urmas80 on 07/31/2014 03:40:21
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/31/2014 :  12:21:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by urmas80

Guys,

You are taking this topic to a folder submission discussion.




No those are not folders. They are mount points and point to their own disks. They are the same as using different drive letters. Starting with version 2005, SQL Server supports mount points.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
799 Posts

Posted - 08/05/2014 :  14:22:22  Show Profile  Reply with Quote
To move the files there are a couple of options:

1) Alter the database files (ALTER DATABASE ... MODIFY FILE (...) - setting the file names (both mdf and ldf) to the new locations. Once that is done, take the database offline, copy the files, bring database online.

2) Detach/Attach - detach the database, copy the files to the new location, attach database (note: reset owner of database after attaching)

3) Backup/Restore - backup the existing database, restore over existing database moving the files to the new locations.

When you create the new volumes, make sure they are formatted correctly with a 64K allocation unit. Whether or not you setup those as individual drives or mount points is up to you and your storage admin. There are advantages/disadvantages to each approach - and will depend on how those volumes are actually carved out of the storage for that host server.

Good luck...
Go to Top of Page

Shanky
Yak Posting Veteran

United Kingdom
73 Posts

Posted - 08/06/2014 :  16:35:25  Show Profile  Reply with Quote
To move data and log files please only use TSQL command. Alter database modify file command. Check this Technet article for sample query http://technet.microsoft.com/en-us/magazine/gg452698.aspx.

Hope this helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000