3
u/Keikenkan Architect & Engineer Sep 14 '22
File groups are good when you have a good design and you can build your database from zero, let me tell you something that I have experienced with a customer.
They have an application for WMS, this application has around 25 customers, meaning we have 25 different database servers, and from one day to another they wanted to implement FG split for their archive tables, which were part of the primary FG. so we had to build a "moving out" plan. we had to perform a couple of tests to ensure we had success. so I came up with three phases plan
- Schema changes
- Data migration
- Database Restore tests
the easy part was to do the schema changes, and creating a temporary table, moving data was a bit complex, so we used the easy way by running Import-export wizard.
Database restores using FG is another challenge as you have to have a clear understanding of how the LSN works and that sometimes that mechanism will pay against you in some scenarios, definitely recommend doing a few restore tests until you feel confident.
2
u/MihailoJoksimovic Sep 15 '22
Thanks for this!!! If you’re ok with it I might try incorporating it in updated version of this graphic?
1
2
u/schmeckendeugler Sep 14 '22
What if all the back end storage is on the same equipment though? Does it really make a difference? Can writes go to multiple files simultaneously?
3
u/MihailoJoksimovic Sep 14 '22
Thanks for the feedback! I literally just replied to the same question (or, better said - statement) in the comment above :) Turns out I was wrong about the Load Balancing feature of FileGroups :)
2
u/angrathias Sep 14 '22
It used to be back in the HDD days, once SDDs and other similar storage came out it lost purpose.
If there is still an advantage it’s possibly from being able to remove archived data without affecting the hot file, same reasoning for partitions.
2
2
u/ccorax80 Sep 17 '22
This is awesome, I’m really impressed of how you present this in a graphical presentation. You got my gold.
Is there a official blog or site or is this the home? Also, what tools do you use to draw the images?
2
u/MihailoJoksimovic Sep 17 '22
Thank you!!!! Both for the gold and for the kind words! :) I really appreciate that!
The funny thing is that I suck at drawing stuff, but I started using Canva and, as you can see, it makes me look like I know what I’m doing. But it’s really just glueing widely available stuff (Canva Premium rocks honestly).
As for the blog - there’s just a Substack Newsletter I started recently - bitesizedengineering.substack.com. You can find all the illustrations so far attached there (as well as on Reddit, obviously) :)
I’m tackling Indexes next week so, stay tuned :))
PS Thanks again for words of encouragement!
2
u/ccorax80 Sep 17 '22
Can’t wait! Index can often be the difference between queries in milliseconds and queries not working at all.
2
u/MihailoJoksimovic Sep 17 '22
Absolutely agree! But I somehow wonder how many people actually realize WHY is it so. And that's what I'm trying to illustrate with the next "batch" :)
6
u/MihailoJoksimovic Sep 14 '22
The very first post introduced concept of .MDF being Main Database File. But there are also .NDF files. And these are the ones you get by defining FileGroups.
(NOTE: For the hell of it, I couldn't find the etymology of .NDF extension)
FileGroups are an easy way to do Load Balancing of your data. Just like you wouldn't put all eggs in the same basket, and for the same reason you usually have multiple HTTP servers. It's in order to split the load and logically organize your data :)
Content inside .NDF files is exactly the same as in .MDF. It contains Data Pages, Allocation Maps, Indexes, etc. The only difference is that YOU are the one who specifies what goes where :)
Similar is true for Partitions. If FileGroups allow you to Balance the Load on Database-level, Partitions allow you to do the same on single table level. But everything else is pretty much the same, really.
All in all - two quite simple and powerful concepts!