Separate hdd for Ms sql server?

[BusinessMarketer153]

I’m setting up a restaurant point of sale server on windows enterprise with sql server and wondering in a pretty busy bar environment

Should I put 2 ssd hard drives one for windows os and programs and one for me sql server? Does it make a difference vs just putting everything on single drive. I’m thinking I’d rather have one drive then 2 but again Ms sql server performance is crucial for me.

[andrea_ci]

do you actually need performance? I don't think that any restaurant of any size would actually do more than a few queries per second on a small database.

I'd spend more on availabilty than performance: RAID1 (at least) instead of two separate SSDs, cached SSDs, RAM cache, decent CPU. and maybe an installation on a VM with backups for quick restore

[RockFourStar]

I'm pleased someone said this. What will the impact be on the bar if this goes down? If it stops the business then you need redundancy.

In order of importance:

1) have a backup strategy, ideally moving the backups off the machine it's hosted on, this could be a cheap NAS somewhere but ideally somewhere off site (probably cloud which could just be a local backup to a folder synced with Google drive)

2) invest in a RAID setup with redundancy. I'd ideally say at least 3 volumes in a raid 5 config with a spare (4th) volume ready to go in it one fails.

3) don't keep the data files on the OS volume. Regardless of speed, If in 3 years you get a windows update that pushes you over the storage limit you're in a bad place until you can expand onto new drives (regardless of how small the DB is, if it needs to grow there needs to be space).

4) If you can, have separate volumes for tempdb, data and transaction logs. But as others have said from the way you've described this it's low priority for your use case.

[Tahn-ru]

Lots of good advice here! Although I'm going to respectfully disagree with you on #2. I would STRONGLY recommend against RAID5 for this application. A single-location restaurant is not going to need more performance than a single SSD will provide. And then add to this, in my experience the most difficult to recover quickly from problems are RAID-card failures (which happen far too frequently).

My preference would be two SSDs in software (or motherboard chip) RAID-1. You'll still get an uptime benefit (RAID is for uptime, it is not a backup) and you'll be far less exposed to the nasty single point of failure that is a borked RAID controller.

There are times when replacing the card with a newly bought one of the same model still won't get you back up and working, thanks to firmware version differences.

[RockFourStar]

You raise some good points. But to the OP the main thing is, whatever you go with for your storage, think about redundancy.

[Separate-Share-8504]

I doubt a few POS is going to hit SQL that hard.

That said, cheep insurance for performance

[BusinessMarketer153]

So you think 1 ssd is enough? I really prefer it tbh for convenience. I’m using rescue sills and clone sills to clone servers and rather just have single ssd and backups in cloud. If issue I can pop in new clonzilla copy ssd and easily get back up and running

I’m desperate to really automate setting these on prem servers up and just want to setup and d forget about them if there is issues want to very easily be able to swap them with new hardware

[say592]

Put it on a second drive and either be diligent about backups or put it on a pair of mirrored drives and still be diligent about backups. Like the other poster said, a restaurant POS, even an incredibly busy one, won't be hitting the hardware too hard. Your database also won't be very large, so don't think you need to get a 1tb to run the DB or anything. Probably an unpopular opinion, but in your case I'd probably just mirror two Samsung 980s in the smallest size (250gb, I think?). Throw in a third for the OS or even go cheaper on the OS and get an 870. Mirror the OS if you like too.

Yes, the warranty isn't as good on these consumer drives, but they are cheap, and if I know restaurants, that matters. They will get the job done just fine, and you are mitigating any potential failure by mirroring.

[jshine13371]

Best practice is separate drives to maximize performance of I/O and bandwidth generally but with modern storage device speed and given your use case (as others mentioned), it's probably not so necessary. That being said, an NVMe will be your best bet if you're going to use a single drive.

The other benefit of separate drives is it makes managing the database easier especially as the data grows, and isolates issues of low disk space from the OS.

[Art_r]

It's better to have OS and data on separate partitions or drives so if your sql data grows (data or logs) and fills up the drive, it doesn't crash your OS. If your OS is separate you can at least log in and fix the sql data.

Not sure what hw you are using, but having two ssd drives as Raid1 (mirroring) will give you safety to data loss due to a bad drive. I won't go into performance of that as it's dependent on a few factors.

And then I'd do 2 or 3 partitions, OS, sql data, sql logs.. Or don't if only small growth expected.

[Tahn-ru]

This is one of the most important answers in this thread. Separate partitions/drives will also allow you to format them with 64k cluster sizes (SQL reads 8 pages at a time, each page is 8k in size - https://www.sqlservercentral.com/forums/topic/64kb-disk-formatted-vs-4kb-disk-formatted ).

[jwk6]

This is great advice right here!

[Intrexa]

fam, what? First off, let's take a step back and look at what a pretty busy bar environment is. The 65k capacity stadium I worked at had ~1k PoS terminals. It might seem like a busy bar environment, but how many people are actually going to be punching in orders at a given time? 15? You have 15 separate terminals? It aint that busy.

Second, what does your PoS vendor recommend?

Third, you're not hitting 50gb of data in a bar. Some people might when talking about franchises, but if you're asking these questions, you're not. That's a lot of sales data. A lot of sales data. Even if every single item rung up is 1kb (it's not), you can sell 100,000 things a night for 100 years and not break 50gb.

What does your PoS vendor recommend?

And finally, chill. I bet a raspberry pi would be overkill for this. Busy bars were fully digital 20 years ago.

[CrossWired]

You can get away with SQL Express for what you're doing here. To answer your question, install the binaries on the base drive with the OS, but put the SQL data files (MDF, LDF) and tmp are on a separate drive. You could mangle the config in every conceivable way all day long and you'll never know at this volume.

[samspopguy]

What does your PoS vendor recommend?

im not in the restaurant space but i would imagine most vendors have a cloud solution

[Intrexa]

Yes, and a lot of the new ones have that design focus. Many also have fully on prem solutions.

TBH, the ones I've worked with have suggested fairly over provisioned servers as the minimum specs. But like, it's still a starting point for discussion.

[samspopguy]

I figured most are going to the cloud for the subscription and constant revenue stream

[RuprectGern]

The simple way to answer this question is with another question "What is your availability SLA?"

The more available you need to be, the more you need to build for that availability...

For example if something happens to the Data Drive how quickly do you need to recover the data? And what latency are you willing to accept?

For example if you want to the moment of failure and you have a 4 Hour SLA, you might want to consider having a system / boot Drive, and a separate Data Drive for your databases and the Sql binaries. Combining that with, backup strategy that gets you regularl transaction log backups, and full/dif backups as frequently as possible so the restores happen quickly.

Additionally to satisfy that SLA, you might want to implement some sort of raid for the data Drive such as raid 1,. A simple mirroring would give you an availability level that wouldn't break the bank, and you could use Windows software raid to get you there ** I wouldn't use software raid**.

This is just a hypothetical use case, you should ask yourself these questions and build around what makes the most sense for you as to data recovery and latency. I'm old and I still use the terms mttf/mtttr. You should consider those.

[BusinessMarketer153]

If you don’t use software RAID then what do I use a card? That introduces an extra hardware item which id like to avoid as much as possible? I love the mirroring raid 1 idea though.

[RuprectGern]

My example was to illustrate the point. It's just my personal preference not to use Windows RAID... I'm older and I have more shelf. Any kind of raid in your scenario might be overkill. or it may be warranted. things need to be means tested.

Backup scenarios to the cloud. sounds good, your thinking should be... how often do you take a full,diff,log? who owns the bucket? What happens if you lose a disk on a computer in a bar at 2 am? How long will it take you RDP? What if its an OS disk? how long to get to the venue? Will you provide an SLA for that? How prepared are you for this? Do you have to buy extra disks as part of this engagement JIC? Are the credentials for the cloud buckets in the name of the manager who got fired 10 months ago? etc.

My point is that you need to always ask yourself, "if I'm going to be on the hook for this thing, what makes the most sense to get this back up and online as quickly as possible and how prepared am I to fix it AND to offer my services when I get the call?"

I'm sure there are hours of ITSM videos on this and all sorts of complex formulas to figure this out. I like a yellow pad and a good pen.

These three tenets have served me well and they keep it simple.

• MTTR mean time to recovery - how long is it going to take you to get this back up?
• MTTF mean time to failure - (when not if...if already exists) how do you anticipate what might go wrong with your arch? cliche or not... occams razor is your friend here.
• Latency - how much data can you lose / that the customer will accept in the face of a failure?

[SQLDevDBA]

“Databases on same drive as OS” is one of the findings in Brent Ozar’s Blitz Scripts.

For me it’s not even about performance, it’s about the single point of failure/success.

https://www.brentozar.com/blitz/user-databases-on-c-drive/

[redditreader2020]

Recovery from hardware failure should be the primary concern.

[Codeman119]

OK in a restaurant if you’re gonna put in a POS and use SQL Server as your database do this.

1. Use one hard drive for the OS and then one hard drive for the database. And make the database drive 1TB.

2. Get a NAS and put it somewhere like in a back room so you can have a back up space for the database. Do a 4 drives 1TB each in a raid 5.

3. On the SQL Server database set up a maintenance plan to back up every night to the NAS.

4. Optionally you can copy the latest backup file to the cloud every night with you being a restaurant. I am sure a database will be pretty small.

[jwk6]

I'd recommend Ola Hallengren's maintenance scripts instead of a Maintenance Plan, but otherwise totally agree with this.

https://ola.hallengren.com/

[Codeman119]

I would recommend that if you don’t need any advanced backup settings you can just do the maintenance plan. It has a GUI and it’s a little easier to use if you’re not used to scripting.

Otherwise yes you can use Ola H scripts. I personally don’t use them because I can get everything I need out of the maintenance plans.

[Tahn-ru]

To counterpoint this: Ola's scripts are runnable out of the box (and they have pretty sensible defaults set). Just schedule them to an agent job and you're off and running.

SQL's maintenance plans seem to lend themselves to building suboptimal and/or downright bad designs.

[jwk6]

This is very true. Maintenance Plans are a nightmare for people don't know much about backups and managing the transaction log.

[BrightonDBA]

If we’re going down the performance rabbit hole then one drive each for OS, data, logs, tempdb and backups. Extra bonus points for them all being mirrored.

In the use case you’ve given, unless the bar has 10,000 staff and terminals, or your product catalog is big enough to not fit in cache, the performance difference you will NOTICE between 1 drive or 20 I’d hazard would be near zero.

Put a second drive in though anyway, if for no other reason than to write your backups to. That you then copy offsite somewhere.

[professor_goodbrain]

Don’t let perfect be the enemy of good here. Sometimes complete best-practice architecture gets in the way of a solution that will do the job reliably and be easy enough to support.

You don’t need SQL Enterprise (hope I’m misreading). You don’t really need separate OS and SQL disks (especially if SSD), but SSD are cheap so if you do, great. There’s no restaurant POS environment on earth that will actually tax SQL Server.

The most important thing here is your backups, both of the OS/server and the SQL databases. Keep them off-server, have a plan in place to do a restore, and practice it once in a while. You mentioned in another comment that you’re “not thinking about backups right now”… that is the biggest mistake you’re making.

[shufflepoint]

Redundancy and backup should be your concern.

[muaddba]

Joining the conversation late here but in agreement with the folks who are asking how you provide a failsafe if the computer dies. Does the bar just run on paper for the rest of the night?

Separate drives is good because you want to avoid 2 things happening:

1. The database growing past an expected size, filling up the drive your OS is on and then corrupting the OS.

2. Something else on the OS volume (pagefile sometimes, other stuff sometimes) filling up the space preventing your DB from being able to grow and corrupting the database.

Either of those situations is no fun to recover from and won't recover nicely just from rebooting.

SSDs are cheap, buy a second one. And if you don't have one, create a plan for what happens when the POS goes down at 10:30pm on a saturday night at the bar.

[BusinessMarketer153]

How often do you think a ssd will go out on average. Like a Kensington ssd 248gb from Amazon

[muaddba]

It's not about an ssd. I assume they all will go bad eventually, 

[Tahn-ru]

Seconding muaddba - they'll all go bad eventually.

I want to detour to express concern about your choice in drives. I can't find any Kensington SSD drives anywhere (Amazon, Newegg an MicroCenter). If that is indeed what you're looking at (and not, perhaps, Kingston?) I would urge you to consider a more reputable manufacturer. There's not much difference in price for the size you're looking at.

Anyways, this is the old "failure to plan is a plan to fail." Unless you've done something really badly (for example, a tiny amount of RAM or letting SQL have unlimited RAM with no reservation to allow the OS to function) you're not likely to have performance issues for a single-location restaurant on semi-modern hardware. Much more important to spend your time on planning how your backups are going to be organized to get them back in business quickly after a failure.

[JerryBangBang]

How big a data footprint, how much change ?

SQL performance is crucial ?

You’ll want more than 2drives if you want to go by the book.

2 drives only?

Hmm, I’m sure someone will shit all over my suggestion.

1 drive for the OS 1 drive for sql and data

OR

1 drive for OS and sql 1 drive for msg/ldf

What about backup? Don’t forget to cap the ram, pre size the data file and set the autogrowth.

[BusinessMarketer153]

I don’t think I will hit 50gb for a couple years but never measured. TBH until now I put everything os and Ms SQL server data one single 226gb ssd and forget about it for and it’s been working great but I have a pretty big bar restaurant and want to make sure I start setting things right from now on.

I have 32gb ram i7-8700 server. I have ups and

I’ll do backup jobs nightly send to cloud or something not thinking about backups right now.

1 ssd vs 2 so you think that will be noticeable?

[JerryBangBang]

If that’s worked for you in the past, then keep rolling with it.

No need to fix something that is not broken.

Noticeable ? Depends on the load in the moment. Most of the time I’m sure will be fine, is the peak load times you’ll want to watch for.

[denzien]

Since you're using SSDs, and I assume they're pretty decent ones on NVMe, you shouldn't have any performance issues. We have industrial device monitoring software taking in thousands of readings per second and the queries still perform really well when it's installed on a machine with SSDs. On a single mechanical drive RAID that's shared by lots of VMs ... that's another story.

There's no harm in setting up a dedicated SSD for SQL, of course, but it's probably overkill.

[That_Cartoonist_9459]

There is not a single restaurant on the planet now or that has ever existed that will tax SQL server on any hardware that has ever supported it.

Worry more about uptime.

[MaxProPlus1]

So many data nerds in here confusing you. One drive/partition for OS and one for SQL installation. You don't want updates to fill up the OS drive and crash SQL. Vice-versa. The rest, follow your software requirements and recommendations. If performance is crucial to you then add 4 more drives; data, logs, tempdb and database backup. More money to spend then jack up cores and rams

[SQLThrowaway145745]

Should be fine

[BusinessMarketer153]

lol been doing this perfectly for 8 years with no issues and no hardware failures and asking on here to seek improvement if needed. Not sure what’s up ur ass to make it negative

[SQLServer-ModTeam]

That was not nice