SQL Administration Woes
-
@PSX_Defector said in SQL Administration Woes:
@travisdh1 said in SQL Administration Woes:
- No. Splitting the disks like that is a historical oddity once you're virtualized. OBR (One Big RAID) is the way to go on the host, and then everything else is transparent to the hosted machines.
Da fuq?
TempDB grows too fast, fills up the C:\ drive, boom, you fucked. Logs fill up the drive, need to perform a clean up by running a backup, can't because C:\ is full. Everything is on C:\ but you need to extend the drive, oops you f***ed that up now you are toast. Then of course we forget about the absolute stupidity of putting all the binaries on the C:\ drive. Hope you never need to cluster.
Breaking them down per drive is what I've done for a long time and what is considered best practice even now. And given that I would never put backups on any of my SSD tiers, why the hell wouldn't I split off that? Letting TempDB grow on their own volume would prevent it from taking down the OS.
Unless of course you don't care about tiering, better disk management, and so forth.
The confusion, I think, comes from calling partitions disks. It's not four disks, four partitions. Which mostly makes sense. But four totally separate disks would be crazy.
-
@Tim_G said in SQL Administration Woes:
There's no reason to put backups on the more expensive storage tier, or temp files. What matters is the database and logging.
Actually temp matters a lot for performance. It's where query scratch work is done and can have insanely high I/O demands that the rest of the system has to wait on. It's actually the portion that some people consider moving to RAM disk, it is so I/O sensitive.
-
@PSX_Defector Thanks for the link and info!
-
@scottalanmiller said in SQL Administration Woes:
The confusion, I think, comes from calling partitions disks. It's not four disks, four partitions. Which mostly makes sense. But four totally separate disks would be crazy.
I don't have it setup as four partitions - I created four separate vmdks for this server (besides the fifth disk which is disk 0; the OS vmdk).
-
@Shuey said in SQL Administration Woes:
@scottalanmiller said in SQL Administration Woes:
The confusion, I think, comes from calling partitions disks. It's not four disks, four partitions. Which mostly makes sense. But four totally separate disks would be crazy.
I don't have it setup as four partitions - I created four separate vmdks for this server (besides the fifth disk which is disk 0; the OS vmdk).
VMDKs are "volumes" in the modern sense. That's an LVM layer. A partition, volume, LUN, array... they are all "disks" but we don't call them that as it is confusing like we mean separate physical devices. Making a separate VMDK for each is identical to making an LVM volume for each... just managed at the VMware layer instead of inside of the OS.
-
@Shuey said in SQL Administration Woes:
@scottalanmiller said in SQL Administration Woes:
The confusion, I think, comes from calling partitions disks. It's not four disks, four partitions. Which mostly makes sense. But four totally separate disks would be crazy.
I don't have it setup as four partitions - I created four separate vmdks for this server (besides the fifth disk which is disk 0; the OS vmdk).
This article can help with what I was talking about...
http://www.smbitjournal.com/2016/06/what-is-drive-appearance/
-
@scottalanmiller said in SQL Administration Woes:
VMDKs are "volumes" in the modern sense. That's an LVM layer. A partition, volume, LUN, array... they are all "disks" but we don't call them that as it is confusing like we mean separate physical devices. Making a separate VMDK for each is identical to making an LVM volume for each... just managed at the VMware layer instead of inside of the OS.
Ah ha! moment Thanks Scott! That's at least the third time you've taught me something cool that should've been more obvious but I totally missed it until you shed new light on it for me! : )
-
How many users use the software and connect to the database? What's the load going to be like?
-
@Tim_G said in SQL Administration Woes:
How many users use the software and connect to the database? What's the load going to be like?
We're licensed for five total, but only four people will be connected to it every day (until/unless they end up hiring a 5th member to join the accounting department). I'm not sure what kind of a load the server will have, but I can say that the previous server (also a guest VM in our ESXi environment) only had 4 vCPUs and 8GB of RAM dedicated to it, and a 130GB vmdk.
-
@Shuey said in SQL Administration Woes:
@Tim_G said in SQL Administration Woes:
How many users use the software and connect to the database? What's the load going to be like?
We're licensed for five total, but only four people will be connected to it every day (until/unless they end up hiring a 5th member to join the accounting department). I'm not sure what kind of a load the server will have, but I can say that the previous server (also a guest VM in our ESXi environment) only had 4 vCPUs and 8GB of RAM dedicated to it, and a 130GB vmdk.
Hard to imagine many cases where four users will need many resources. It can happen, but generally, it's hard. How much database work can an accounting system need to do?
-
@scottalanmiller said in SQL Administration Woes:
Hard to imagine many cases where four users will need many resources. It can happen, but generally, it's hard. How much database work can an accounting system need to do?
Right! That's why I'm still considering just leaving everything as is, and just do my usual two full backups per day (one at noon and one at 7pm - the staff work from 8am to 6pm). And I could also incorporate actual SQL backups each day as well. It's still new territory for me, so I'm definitely gonna need a little time to get my bearings.
-
@Shuey just create a sql job to make a bak file nightly and be done from the SQL side. You backup software will get them when it snaps the VM.
If your backup solution does not understand things inside the VM, you current do not have a valid backup unless you get lucky.
-
@JaredBusch said in SQL Administration Woes:
@Shuey just create a sql job to make a bak file nightly and be done from the SQL side. You backup software will get them when it snaps the VM.
If your backup solution does not understand things inside the VM, you current do not have a valid backup unless you get lucky.
Cool, sounds good - Thanks Jared! : )
-
@Shuey Your workload being as tiny as it is means that the other guy told you wrong. there is near zero need for the level of detail that @PSX_Defector and I were talking about.
-
@JaredBusch said in SQL Administration Woes:
@Shuey Your workload being as tiny as it is means that the other guy told you wrong. there is near zero need for the level of detail that @PSX_Defector and I were talking about.
I'm glad my initial hunch was right then. Thanks for confirming. I tend to overwhelm myself anytime I'm in a situation where I'm ignorant and/or inexperienced, and this was one of those times. I just always wanna "get it right" (and ideally "the first time" if possible, lol).
Thanks for all the feedback guys!
-
It doesn't seem like you need to worry much about performance.
Let me first state that you probably don't need to give SQL the recommended 8gb ram, and could set it to use 6gb in SQL, and give your host 2gb. However, I personally would give SQL a minimum of 8gb ram, and the host a minimum of 4 gb ram. That gives the host it's 2gb recommended, and 2 for other tasks/software.
Also, you can try with 2 vCPUs, but it may be better to give 4. I don't know what your host looks like, perhaps you are short on resources, or perhaps not. It's best to give what it needs, and nothing more. (it's a balancing act) It mostly depends on your environment. I've had to give some SQL servers 48gb ram and 4-6 vCPUs, but mostly less. It depends on a lot of things. It's up to you to measure and give what's needed.
The last SQL setup I've done, our SQL/Dynamics SL consultant had me set up 4 disks total:
- OS
- DATA
- LOGS
- Backup
And I distributed them between SSD and HDD appropriately.
If you are being told to stick everything on one disk or one volume (C:), then send your warnings to them in writing stating this is not a supported setup by Microsoft and most likely the software vendor (even if that person says otherwise). Then if they insist on doing it, after you've made your case, then you are covered at least and gave your warnings in the case of any future issues or data loss. If any, I'm positive this would be the reason.
-
With all that said, you're load is extremely small, but I would still split up as per best practice and future proofing, and dish out ram and vcpus appropriately for your needs.
-
@Tim_G said in SQL Administration Woes:
With all that said, you're load is extremely small, but I would still split up as per best practice and future proofing, and dish out ram and vcpus appropriately for your needs.
I would never waste the time configuring something so tiny like that. It probably does not even have full recovery mode enabled anyway.
