March 19, 2016 at 4:44 am
Hello Experts,
we are embarking on the design of a SQL Server infrastructure for a VLDB which will grow to about 20TB, I want to know if there is going to be any performance if we decide to use multiple file groups for the database?
Thanks
March 19, 2016 at 11:29 pm
Multiple file groups in the same drive, Not So Much but If you can divide those file groups in multiple locations (Drives) there is huge positive impact in the performance and also it can provide a better data availability in case of any one drive failure.
March 20, 2016 at 12:05 am
Thank you so much for the clarifications
March 24, 2016 at 5:00 am
20TB is definitely a VLDB, and there are a lot of issues beyond just looking at multiple file groups that you need to consider. A DB of that size will require a large budget for hardware, DR, and admin staff. You will get some advice from within this forum, but remember that it is given for free by people who do not know the full technical details of what you want to achieve, so the advice will be cherry-picking the easy bits and not looking at the whole problem.
I recommend you talk to Microsoft about what you want to do, and ask them to give you a list of partner organisations in your country/region that have the expertise to design the hardware and software environment you need to support your VLDB. Getting a good consultant on board for a month may cost you GBP 10k or more, but this will be a tiny amount compared to your whole budget, and an even smaller amount compared to the cost of fixing a bad implementation.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 24, 2016 at 5:06 am
Performance, probably not, but trying to keep 20TB in one filegroup is probably not a good idea for backup/restore reasons.
Designing a filegroup layout for that is complicated though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2016 at 5:07 am
helper_10 (3/19/2016)
Multiple file groups in the same drive, Not So Much but If you can divide those file groups in multiple locations (Drives) there is huge positive impact in the performance
Might be, not will be. Depends on a whole pile of things. If, for example, the performance bottleneck is CPU, multiple files isn't going to help at all
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2016 at 11:13 am
Thank You!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply