[REQUEST] SSDs for Microsoft SQL database

[Slaters Kustum Machines]

I am looking over the ChiroTouch (LOB app for chiropractors) system requirements as we have an upcoming server replacement project and noticed under the "Not Supported section" it states: "SSDs for Microsoft SQL database*"

* Using SSDs for Microsoft SQL databases requires a specialized configuration. Please consult Microsoft and your IT professional for proper setup of SSDs for Microsoft SQL databases.

Anybody know what difference there is in configuration? I would have thought it would be a standard install, just faster. We'll be using SQL Express.


http://help.mychirotouch.com/Content/Introduction/System_Requirements.htm

 

[Sky-Knight]

As far as I know there is nothing to configure, but SSD endurance is a thing and SQL databases are aggressive. I've only done virtual SQL installs on SSD arrays and by the time the hypervisor is done with it, the writes are nice and even. But you still need good drives if you want the lifespan.

If you're doing Dell, that's what the write intense drives are for.

 

[Slaters Kustum Machines]

As far as I know there is nothing to configure, but SSD endurance is a thing and SQL databases are aggressive. I've only done virtual SQL installs on SSD arrays and by the time the hypervisor is done with it, the writes are nice and even. But you still need good drives if you want the lifespan.

If you're doing Dell, that's what the write intense drives are for.

Good to know. We will be using Dell. I was looking at mixed use drives, but will go with the write intensive drives instead.

 

[Sky-Knight]

If you fit within the free version of SQL, the mixed use drives would probably do just fine. That said, CYA says use Write Intense, if it's a VM or SQL workload.

 

[Markverhyden]

I'll say the big question is how many drives and users? More drives is better. While SSD reliability is improving I'd still want, at a minimum, RAID 6 + a hot spare if it was all SSD. RAID 10 would be better but that might drive up the price with more SSD's.

 

[Slaters Kustum Machines]

I'll say the big question is how many drives and users? More drives is better. While SSD reliability is improving I'd still want, at a minimum, RAID 6 + a hot spare if it was all SSD. RAID 10 would be better but that might drive up the price with more SSD's.

They are currently running two RAID 1's on 10k SAS drives. I was planning on doing the same thing with SSD's in the new server. There are 6 computers, but typically only 1-2 users at a time accessing the database. The 2 dr.s each have two computers and there are two reception computers.

 

[Sky-Knight]

I've done RAID 10 on every unit I've deployed, but they were also intended to be hypervisors. I don't like RAID 0,5,or 6, I tend to stick to 1 and 10, but again... hypervisors first. I haven't done a bare metal rig in years.

 

[YeOldeStonecat]

We recently did one, did an R10, I'm taking it that they're good with RAID when talking about that specialized configuration.
I'm thinking they deal with a lot of smaller chiropractic offices that have workgroup setups and a workstation as a server....and SQL would beat up a consumer grade single SSD. (although those WD Blue spindles are much more common and worse)

 

[Slaters Kustum Machines]

I am going to be using the same size drives for all 4 drives. I know I've heard @YeOldeStonecat mention going RAID 10 a number of times and @Sky-Knight here as well. There won't be much load on this machine. Should I be doing RAID 10 over RAID 1? This was going to be a Server 2016 Essentials box on bare metal.

I know there has been lots of back and forth on if Essentials allows Hyper-V or not. I didn't see much benefit on this to virtualize it since they won't be adding any more VM's. I could do ESXi, but it seems like it makes this simple scenario more complicated than it needs to be on the management side for just 1 VM.

 

[YeOldeStonecat]

With spindle drives...I always (in the past) preferred to do a smaller RAID 1...and a larger RAID 1 for smaller servers...(or larger RAID 10 for a big server)
The smaller RAID 1 for the OS drive, and the larger RAID 1 (or even larger RAID 10) for the data. Or on Hypervisors...I'd still do a R1 up front for the hypervisor OS...plus some space for a few smaller volumes of guest virtual drives...and a second RAID for more virtual guest drives. I found improved performance for guests by separating their spindles. I always preferred having a server have its OS drive..and data drives...on separate spindles. Versus having them on separate partitions on the same spindle. You get much improved performance..concurrent hits, spanning the pagefile.sys across multiple spindles. Pro...performance. Con...a harder time efficiently using space, potentially wasted space...on the host drives.

However...with SSDs..that performance difference is pretty much gone. One Big RAID 10 of SSDs...so freaking fast...I'm sure "benchmark wise" there may still be a slight improvement in performance, but...it's already so crazy fast...I'm good with OBR10. And it's easier to manage space on a hypervisor with one big volume.

 

[YeOldeStonecat]

Re: Essentials and Hyper-V...I believe starting with Essentials 2019 the licensing includes installing a Hyper-V instance and nesting the Essentials guest in it. I have virtualized Essentials server before but the client was already purchasing Server Standard for something else like a member SQL server or Remote Desktop Server....so I have that covered there.

 

[Sky-Knight]

Essentials 2012 R2 onward has the virtualization rights for a single instance. Now, I haven't worked with 2016 or 2019 essentials but with 2012 R2 essentials the platform wouldn't work unless you put the essentials role on it, which licenses that platform for production...

So I think you might have to get the free version of Microsoft HyperV server, to install the host, unless Microsoft fixed that. There should be no active roles on the host other than HyperV.

Alternately, you can get the free version of VMWare, Dell supports that too, and just install Windows Server on that... given the patching issues with Server 2016, I'm afraid I may have to start going that route. I'm sick of servers installing updates at 1am, only to have them reboot at 4am because the host took 3 hours to patch.

But yes, all servers I deploy are virtual, a single RAID 10 on the in built drives, and the production environments are guests. That way I can use Datto or whatever to image them off, and have them ready to move to new hardware for emergencies and future migration possibilities. Bare metal production just means that much more to go wrong when the excrement hits the fan.

 

[HCHTech]

We have a very-busy chiropractor client using Chirotouch. We did a server 3 years ago (so spinning drives), but spent quite a bit of time & effort making the performance adequate. Dedicated server, RAID10 with a hot-spare. 10Gb NIC to switch, etc. We even swapped SSDs into all of the exam room computers (expensive at the time). My sense from dealing Chirotouch over the years is that most of their clients are smaller than my 20-employee setup. As soon as it became clear that their recommended setup wasn't going to be adequate, we were on our own.

One of the issues while we were working this out was that the changes to the database made by the doctor after they finished with the patient wouldn't populate fast enough to generate the invoice by the time the patient had walked back to the front desk to pay. Our nickel solution to the dime problem was to block one of the exit paths so the patient had to walk farther (taking more time) to the front desk!! Bumping the server NIC to 10Gb and getting a new switch fixed that.

 

[brandonkick]

SQL Express has some pretty big limitations as well.. 10GB DB size, low memory consumption, weird (don't remember off the top of my head) processor consumption restrictions (IIRC).

I personally wouldn't be using SQL Express, but I know that M$ business licensing isn't cheap either... however, maybe you are already aware of all this and SQL Express still easily fits within your needs.

As I understand it, there are "enterprise grade" SSD's out there. Made basically to be more "durable" in terms of writes than the typical consumer grade SSD's. I've never seen any studies done, but I'd doubt even consumer grade SSD's in a nice fat raid 10 (like 4 drives) should help by "spreading the load" so to speak. I've never dug much into more durable SSD's, so I know that for example the Samsung "PRO" line has 5 year warranty vs 3 for EVO lines... more durable in terms of how much can be written to them. I'd imagine these days there are SSD's built with the idea that the I/O workload would be high (server / SQL loads).

 

[Sky-Knight]

Samsung Pro is still consumer grade, though I have used them in servers before. But, the mixed use server grade drives are still more durable.

 

[Slaters Kustum Machines]

SQL Express has some pretty big limitations as well.. 10GB DB size, low memory consumption, weird (don't remember off the top of my head) processor consumption restrictions (IIRC).

I personally wouldn't be using SQL Express, but I know that M$ business licensing isn't cheap either... however, maybe you are already aware of all this and SQL Express still easily fits within your needs.

As I understand it, there are "enterprise grade" SSD's out there. Made basically to be more "durable" in terms of writes than the typical consumer grade SSD's. I've never seen any studies done, but I'd doubt even consumer grade SSD's in a nice fat raid 10 (like 4 drives) should help by "spreading the load" so to speak. I've never dug much into more durable SSD's, so I know that for example the Samsung "PRO" line has 5 year warranty vs 3 for EVO lines... more durable in terms of how much can be written to them. I'd imagine these days there are SSD's built with the idea that the I/O workload would be high (server / SQL loads).

In this case there will not be a high load with a max of 2 people at once hitting the DB. SQL express is recommended by the software manufacturer and seems to work fine on their current server. We will be using Dell provided drives for the next business day warranty. I want it to be completely covered by them. I have used Samsung drives as well for other out of warranty servers and they seem to work fine.

 

[Markverhyden]

We will be using Dell provided drives for the next business day warranty. I want it to be completely covered by them.

Same here. Customer's ask me if I can do these and I tell them it's not economical to offer any kind of warranty like that. Besides my experience with HP and Dell commercial warranty work is the techs are fairly responsive. Meaning I can have a conversation with them so everyone is clear on the objectives and work flow.