SQL Administration Woes
-
It's still a best practice to set up those extra drives, especially if you have it virtualized.
For example, on your hypervisor, you might have an SSD RAID and an HDD RAID. You would put the "Database" and "Logs" virtual disks on the SSD RAID, and the "Backups" and "Temp" virtual disks on the HDD RAID.
There's no reason to put backups on the more expensive storage tier, or temp files. What matters is the database and logging. Generally, you should follow vendor best practices no matter what you see on forums... If Microsoft and your Sage software vendors say to set it up a certain way so they will properly support you, than that's what you do. (generally)
-
@Tim_G said in SQL Administration Woes:
It's still a best practice to set up those extra drives, especially if you have it virtualized.
For example, on your hypervisor, you might have an SSD RAID and an HDD RAID. You would put the "Database" and "Logs" virtual disks on the SSD RAID, and the "Backups" and "Temp" virtual disks on the HDD RAID.
There's no reason to put backups on the more expensive storage tier, or temp files. What matters is the database and logging. Generally, you should follow vendor best practices no matter what you see on forums... If Microsoft and your Sage software vendors say to set it up a certain way so they will properly support you, than that's what you do. (generally)
Unfortunately, the "support" person who setup SQL for us was just following a script. When I told her about the drives that I had provisioned, she said "I don't know anything about that" and refused to install to one of them. She also ran into an issue when installing and said she was going to uninstall the software and re-install it! During the re-install, she came across an error and had no idea what to do. I ended up Googling the error and found a solution and implemented it myself while she watched O_o... So as far as best-practice goes, our vendor seems to have no clue what that is.
As far as the types of storage we have, I work for a company who still lives in the dark ages in many regards. So on that note, our "storage" in our ESXi hosts is just some old 10K rpm SAS drives, and our storage arrays where we keep our backups are all running SATA drives! (all our storage arrays are old MSA 60s).
-
@travisdh1 said in SQL Administration Woes:
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?
Because the backup isn't the local file created, it's what goes to the normal backup client. The local file is only because most backup software can't handle creating backups of active databases.
Da fuq?
Even NT Backup can handle SQL backups. Any modern VSS aware backup product can handle SQL backups.
Which doesn't even touch the fact that you are filling up the drive with junk that can easily be put off the machine, and really should be because WTF?
Unless of course you don't care about tiering, better disk management, and so forth.
This is all handled at the hypervisor level. If you really need tiering, just setup a different virtual disks for the VM with different priority levels.
But you just put everything onto one VHD. There is no management, you have to put it in the fastest tier because of SQL binaries and the MDF/LDF files.
-
@Shuey said in SQL Administration Woes:
I've heard legendary tales of SysAdmins who have done SQL backups all for free, but I'm still way out of my league on this topic, so I'd like some advice if possible...
ANy good SQL DBA can setup maintenance plans to handle indexing, cleanup, backups, etc. all from within SQL server.
Any good SQL DBA CAN do all that, but I'm not a SQL DBA, lol. So I either gotta pay somebody to take care of it, or I gotta hope I can learn and implement it while I'm in the interim (and hope it doesn't all go south during that time) :-S.
https://msdn.microsoft.com/en-us/library/ms187510.aspx
USE <<DATABASE>>;
GO
BACKUP DATABASE <<DATABASE>>
TO DISK = '<<PATH.BAK>>'
WITH FORMAT,
MEDIANAME = 'Full_<<DATABASE>>',
NAME = 'Full Backup';
GORun as SQL job, done.
That will be $500 for DBA work.
-
@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.
