| Author |
Topic |
|
urmas80
Starting Member
20 Posts |
 Posted - 2014-07-30 : 04:37:17
|
| 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
38200 Posts |
Posted - 2014-07-30 : 12:43:09
|
| We use mount points, like this:F:\Backups (though NAS device is preferable)F:\DataF:\LogF:\TempdbDataF:\TempdbLogTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-07-30 : 15:21:23
|
| 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 |
|
|
|
andrew321
Starting Member
5 Posts |
Posted - 2014-07-31 : 01:52:03
|
| 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. |
|
|
|
urmas80
Starting Member
20 Posts |
Posted - 2014-07-31 : 03:39:38
|
| 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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-31 : 12:21:57
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2014-08-05 : 14:22:22
|
| 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... |
|
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-08-06 : 16:35:25
|
| 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 helpsRegardsShankyhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
|
Moving Data and Log files