SQL Server - best practices for SMB
-
Quick question on SQL Server.
I'm installing it on OBR10, so no issues there. I will install the OS on it's own partition (C:). SQL Server will also be installed there (C:\program files). I will then create at least one extra partition for the database(s).
Is it best practice to install databases on a single partition (E:), or should I create separate partitions for data, logs and tempdb (E:,F:,G:).
This isn't a performance issue, obviously (unless it effects fragmentation in any way?). But from an admin perspective, is there any benefit to creating extra partitions? I would have thought that one of biggest risks with a database is running out of space and keeping an eye on space on one partition is easier than three.
I've googled and can't find any answers. Lots of discussions on the merits of physically separating data and logs, but not much on logically separating them. I guess this means it doesn't matter too much, but I need to make a decision one way or the other.
-
@Carnival-Boy said:
Quick question on SQL Server.
I'm installing it on OBR10, so no issues there. I will install the OS on it's own partition (C:). SQL Server will also be installed there (C:\program files). I will then create at least one extra partition for the database(s).
Is it best practice to install databases on a single partition (E:), or should I create separate partitions for data, logs and tempdb (E:,F:,G:).
This isn't a performance issue, obviously (unless it effects fragmentation in any way?). But from an admin perspective, is there any benefit to creating extra partitions? I would have thought that one of biggest risks with a database is running out of space and keeping an eye on space on one partition is easier than three.
I've googled and can't find any answers. Lots of discussions on the merits of physically separating data and logs, but not much on logically separating them. I guess this means it doesn't matter too much, but I need to make a decision one way or the other.
We have our split up to OS, Data, and Logs. Not sure if it is best practice or not, it was recommended by both of our ERP vendors.
-
@coliver said:
@Carnival-Boy said:
Quick question on SQL Server.
I'm installing it on OBR10, so no issues there. I will install the OS on it's own partition (C:). SQL Server will also be installed there (C:\program files). I will then create at least one extra partition for the database(s).
Is it best practice to install databases on a single partition (E:), or should I create separate partitions for data, logs and tempdb (E:,F:,G:).
This isn't a performance issue, obviously (unless it effects fragmentation in any way?). But from an admin perspective, is there any benefit to creating extra partitions? I would have thought that one of biggest risks with a database is running out of space and keeping an eye on space on one partition is easier than three.
I've googled and can't find any answers. Lots of discussions on the merits of physically separating data and logs, but not much on logically separating them. I guess this means it doesn't matter too much, but I need to make a decision one way or the other.
We have our split up to OS, Data, and Logs. Not sure if it is best practice or not, it was recommended by both of our ERP vendors.
We're in the same boat. Though I'm sure the split is from old school separate drive groups (separate RAID'ed drives).
-
@Dashrender said:
@coliver said:
@Carnival-Boy said:
Quick question on SQL Server.
I'm installing it on OBR10, so no issues there. I will install the OS on it's own partition (C:). SQL Server will also be installed there (C:\program files). I will then create at least one extra partition for the database(s).
Is it best practice to install databases on a single partition (E:), or should I create separate partitions for data, logs and tempdb (E:,F:,G:).
This isn't a performance issue, obviously (unless it effects fragmentation in any way?). But from an admin perspective, is there any benefit to creating extra partitions? I would have thought that one of biggest risks with a database is running out of space and keeping an eye on space on one partition is easier than three.
I've googled and can't find any answers. Lots of discussions on the merits of physically separating data and logs, but not much on logically separating them. I guess this means it doesn't matter too much, but I need to make a decision one way or the other.
We have our split up to OS, Data, and Logs. Not sure if it is best practice or not, it was recommended by both of our ERP vendors.
We're in the same boat. Though I'm sure the split is from old school separate drive groups (separate RAID'ed drives).
Yep, I was thinking the same thing, when the performance of an individual drive/array wouldn't be enough for the entire database server.
-
I always install SQL to C and then make a new virtual disk for the data and logs. then a third vdisk for the local SQL backups.
Strictly for ease of seeing usage at a glance. No performance concerns.
-
I've planned on only one vdisk. I hadn't considered creating more than one.
-
@Carnival-Boy said:
I've planned on only one vdisk. I hadn't considered creating more than one.
Both approaches are completely valid. I like keeping the partitions separate, but I only like it a little. So I lean that way, but keeping them all in one is perfectly fine too.
-
We had a rather large MS Dynamic GP deployment for ERP that handled both employee data as well all citizen's data and issues. it was ran on a VM fine with SQL and the storage was and iSccsi Vdisk (connected to esx not windows) to the SANs and we had no issues with that.
-
@scottalanmiller said:
@Carnival-Boy said:
I've planned on only one vdisk. I hadn't considered creating more than one.
Both approaches are completely valid. I like keeping the partitions separate, but I only like it a little. So I lean that way, but keeping them all in one is perfectly fine too.
If you only create one virtual disk and then partition it (eg
for the OS, E: for data) and you later want to increase the size of C:, I believe you can't.If you create separate disks then you can. Go into Windows Disk Management, right-click C:, and select 'Extend Volume'.
For this reason you should always create separate disks, I think. Correct me if I'm wrong.
-
@Carnival-Boy said:
If you create separate disks then you can. Go into Windows Disk Management, right-click C:, and select 'Extend Volume'.
For this reason you should always create separate disks, I think. Correct me if I'm wrong.
You can do it with a single vdisk, but you have to go through a lot more work and you have to use third party disk partitioning tools to do it.
-
@scottalanmiller said:
DBAs aren't the ones who should be making these decisions anyway, they don't know systems. They know databases.
You're giving a lot of DBAs too much credit even with this.
-
@tonyshowoff said:
@scottalanmiller said:
DBAs aren't the ones who should be making these decisions anyway, they don't know systems. They know databases.
You're giving a lot of DBAs too much credit even with this.
I've worked with pretty decent ones. Not too many DBAs anywhere outside of the super big teams.
-
@scottalanmiller said:
@Carnival-Boy said:
I've planned on only one vdisk. I hadn't considered creating more than one.
Both approaches are completely valid. I like keeping the partitions separate, but I only like it a little. So I lean that way, but keeping them all in one is perfectly fine too.
One reason and one reason only to do it as a separate partitions for data/logs/install.
If it fills up the disk, it wouldn't take down the OS in the process. Ive seen it happen, although Windows is usually resilient on that. But the only thing that would happen if you have separate partitions would be that the DBs couldn't write, halting the instance but recoverable by logging in and fixing it.
If you never expect to fill up a disk, make it huge and put it on one. But since it only takes a few minutes, and it's a real bitch to move data once in place to another drive, it's just easier to do this ahead of time to expect it.
-
@scottalanmiller said:
@tonyshowoff said:
@scottalanmiller said:
DBAs aren't the ones who should be making these decisions anyway, they don't know systems. They know databases.
You're giving a lot of DBAs too much credit even with this.
I've worked with pretty decent ones. Not too many DBAs anywhere outside of the super big teams.
A competent DBA is a rarity like an honest mechanic. I've known a couple of good ones, but most I've known either never worked because they weren't needed or were just stupid.
-
@tonyshowoff said:
@scottalanmiller said:
@tonyshowoff said:
@scottalanmiller said:
DBAs aren't the ones who should be making these decisions anyway, they don't know systems. They know databases.
You're giving a lot of DBAs too much credit even with this.
I've worked with pretty decent ones. Not too many DBAs anywhere outside of the super big teams.
A competent DBA is a rarity like an honest mechanic. I've known a couple of good ones, but most I've known either never worked because they weren't needed or were just stupid.
I knew a DBA who we had to teach how to save a word document while she worked with us on some stuff.. and she was also the Database & Programming (C++, VB.NET, Java) professor at a local college (very well know, especially for football) Enough said. But, I've heard most fail end up taking the class online and transferring the credit in to the college as they fail her classes since she doesn't even know it to teach it.
-
@thecreativeone91 said:
I knew a DBA who we had to teach how to save a word document .....and she was also .....professor at a local college
Ding ding ding. I think we know where the problem was.
-
@thecreativeone91 said:
I knew a DBA who we had to teach how to save a word document while she worked with us on some stuff.. and she was also the Database & Programming (C++, VB.NET, Java) professor at a local college
Sounds like someone who failed upwards into that position of DBA
