Varshini S
asked on
How to assign appropriate drives to the system DB, temp DB, Log files directory (LDF), Backup Drive, Application DB to existing SQL Server 2017 standard server?
How to assign appropriate drives to the system DB, temp DB, Log files directory (LDF), Backup Drive, Application DB to existing SQL Server 2017 standard server?
During installation, we haven't assigned appropriate folders for the above. Now the server is in live and I need to assign the appropriate drive and move the file to that folder.
How to achieve this?
Here are the drives and current DB files folder.
During installation, we haven't assigned appropriate folders for the above. Now the server is in live and I need to assign the appropriate drive and move the file to that folder.
How to achieve this?
Here are the drives and current DB files folder.
ASKER
Hi Mate Farkas,
Thank you for the information.
During installation, we haven't assigned appropriate folders for the above. Now the server is in live(production) and I need to assign the appropriate drive and move the file to that folder.
How to achieve this?
Thank you for the information.
During installation, we haven't assigned appropriate folders for the above. Now the server is in live(production) and I need to assign the appropriate drive and move the file to that folder.
How to achieve this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dustin Saunders: The physical disks at RAID 5
And is it all one physical data store with virtual partitions (RAID 5 with those drive sizes, guessing the answer is yes)? What's the speed of these disks? 10k SAS?
If so, are there also other virtual servers and drives on this physical partition? If they're all on the same datastore, you aren't going to gain performance benefits from splitting up the drives-- just benefits like managing drive allocations, etc.
Honestly, it really depends on requirements, most small businesses with a SQL driven app are fine existing in that structure. RAID5 isn't a big deal if you have read heavy databases that do little write activity and are in SIMPLE logging mode-- but you will have a write penalty on calculating and writing parity which can cause performance degredation if there is high activity. So are we talking tens of users, or hundreds of users?
If you have high write activity, RAID10 is generally preferred. Based on the nature of the question, guessing rebuilding the array is a non-starter. Extra bays in the server?
If your SQL Backup drive is also on the same physical array, then please do be sure that data is being backed up at least daily (more if not a daily SIMPLE) to another device somewhere...
If so, are there also other virtual servers and drives on this physical partition? If they're all on the same datastore, you aren't going to gain performance benefits from splitting up the drives-- just benefits like managing drive allocations, etc.
Honestly, it really depends on requirements, most small businesses with a SQL driven app are fine existing in that structure. RAID5 isn't a big deal if you have read heavy databases that do little write activity and are in SIMPLE logging mode-- but you will have a write penalty on calculating and writing parity which can cause performance degredation if there is high activity. So are we talking tens of users, or hundreds of users?
If you have high write activity, RAID10 is generally preferred. Based on the nature of the question, guessing rebuilding the array is a non-starter. Extra bays in the server?
If your SQL Backup drive is also on the same physical array, then please do be sure that data is being backed up at least daily (more if not a daily SIMPLE) to another device somewhere...
ASKER
Dustin Saunders: Thank you for the explanation. My database has both read and write activity. Which RAID do you recommend?
RAID10 is the recommended level for high I/O databases. You will gain a performance boots to both write and read operations as the "10" is implying a combination of RAID0 and RAID1 and you're getting the benefits of each. In the event of a drive failure, the rebuild times are also tiny. 15k SAS is great, but 10k SAS is a minimum.
You'll lose 50% of disk space on RAID10 but you'll have single drive fault tolerance per mirrored drive set.
Just to note again, RAID levels aren't a substitute for backups, so you can dump backups to a local drive but be sure they are getting backed up from that location to a different physical (or geo) location.
You'll lose 50% of disk space on RAID10 but you'll have single drive fault tolerance per mirrored drive set.
Just to note again, RAID levels aren't a substitute for backups, so you can dump backups to a local drive but be sure they are getting backed up from that location to a different physical (or geo) location.
- The goal is to get the best performance for data loading and querying
- or the goal is to use available disk spaces the most efficient way
- or the goal is to have the best availability of databases and backups
Some general guidelines:
1. Separate Data and Log drives of user databases
2. Put data and log files of master databases (master, msdb, model) to system drive (C:)
3. Put data and log files of tempdb on a separate disk (if possible, if not then put them on the same disk as user databases are)
4. Put backup of all databases on a separate disk
Most important is to have regular backups of all databases (system and user) on a disk separated from everything else