Join Log in
Score:0
BWFC avatar Server

How Should FSx Shares be Created for SQL Server Drives on EC2?

in flag
BWFC

We're in the process of planning the migration of our whole platform into AWS. The current thinking is that we will host our instances on EC2 and use FSx as the file system. However, I can't find anything about whether a separate FSx instance is recommended for each drive needed by SQL Server. It's good practice to split data files, log files and tempdb out on to different drives to minimise contention but I'm really not clear about how this split is best managed with FSx. I've seen the EC2 instance storage recommended for tempdb but should I use a different share from a 'central' FSx instance or is a discrete instance for each drive best.

The extension to this is whether a single large FSx instance for all EC2 instances is okay or is it best to use a discrete FSx for each EC2? I expect all of this will crash on the rocks of cost but I'm struggling to get my head around it.

52
0 + 0
Score:0
Tim avatar Server
gp flag
Tim

Have you considered using AWS RDS rather than EC2 / FSx? In the cloud it's generally best to use services where they're available unless they can't do what you need.

I don't think you need to split across FSx file systems for performance, since FSx is a managed service rather than a device / disk. You could put everything onto one FSx file system and dial up or down the performance. However I think other factors probably outweigh this, as I describe below.

Looking at FSx pricing you pay for storage and "throughput capacity" separately. Because of that I would probably keep each database on a separate FSx instance to reduce reliance on one file system, which may increase availability. That also means non-production databases can be on lower performance FSx instances.

Another view is if your databases are not always busy at the same time it might be cheaper to have one large drive and share "throughput capacity" between them. However to me that pushes things together in a way that increases the chance for downtime. For example human error deleting the file share could take out multiple databases rather than one. I probably wouldn't do this unless price was critical and reliably required was low.

If you want to combine storage you can prototype this fairly easily.

0 + 0
BWFC avatar
in flag
BWFC
We had considered RDS and it would probably be our choice as an organisation. However, for one reason or another, we on an extremely elderly creaking system and a government (UK) contract. They said, to get things done quickly, they wanted a 'lift and shift' approach to get us upgraded and migrated as soon as possible, hence EC2 etc. Once we're migrated there is the option to move on to RDS and the wider AWS platform but for now it's simply get moved.
0
Reply
Tim avatar
gp flag
Tim
It might be easiest to rehost (lift and shift). Replatform can be quite easy with AWS Database Migration Service, I've used it recently and it worked well - but we had a fairly simple database, no stored procedures. You can use DMS from on-premise or EC2 so no matter either way. https://aws.amazon.com/blogs/enterprise-strategy/6-strategies-for-migrating-applications-to-the-cloud/
0
Reply
BWFC avatar
in flag
BWFC
There's different definitions of 'easiest' being used by the various parties involved and the party that pays the bills is saying the easiest approach is to use EC2 etc. There is a *lot* of literal politics involved with this so they are extremely risk averse. I think it's pretty much set in stone that RDS is out (for now) but, personally, I agree with you completely on this. We do have some AWS consultants available and if they say do it differently then maybe there's a possibility of changing the approach.
0
Reply
Tim avatar
gp flag
Tim
I'm an AWS consultant (not working for AWS) often working with government, sometimes it's easiest to just do what they want rather than what they need then move towards it slowly later. I'll tweak my answer around FSx slightly, I think you should just try it.
0
Reply
BWFC avatar
in flag
BWFC
Thanks for your input @Tim. Even though you've not actually given me any pointers about the database file split which is the crux of my question, there's definitely some food for thought here. I'm starting to err towards the FSx instance per server cluster approach because that will certainly cut down the risk of human error. Whether there's an instance for data files and another for logs is subject to testing for now. I also think there's benefits to being able to provision different instances for different purposes.
0
Reply
Tim avatar
gp flag
Tim
I don't think you need to split across FSx file systems for performance, since FSx is a managed service rather than a device / disk.
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.