Good day all, and happy T-SQL Tuesday #40! Yes, this event has officially hit middle age now. I fully expect it’ll go get itself a fast car in the upcoming months.
We had a great turnout for this month. “Files and Filegroups” is an open enough topic that I got exactly what I wanted: A nice variety of blog posts, all over the spectrum. We got introductions and issues, solutions, Powershell, mysteries, and more. Dig it! There’s not a bad blog in the lot!
Rob wrote an absolutely fantastic primer for filegroups, with useful code, that I’ll be pointing people to regularly from here on out.
Jason had a “filegroup not online” issue just in time for T-SQL Tuesday! He walks us through his solution here.
Merrill gives us an early lesson in storage internals, demonstrating the file proportional fill algorithm. He’s made us a demo, so you can follow along at home! (Bonus points for his Lord of The Rings reference.)
Patrick gives us another take on the proportional fill algorithm, with an emphasis on space used. I like having the two perspectives on this topic!
Robert used T-SQL Tuesday as day 30 of his “31 days of Disaster Recovery”…awesomesauce!
He discusses the pros and cons of a scenario: we want to replicate only part of a very large database (without initializing from snapshot, or copying and restoring the whole database backup).
Bob tells us his story about migrating a large number of files to new storage, with no downtime. He considers his options in detail, and goes with a hybrid solution. Bob demonstrates this with scripts and diagrams, with “circles and arrows and a paragraph on the back of each one…” This is a great blog in a group of great blogs here, dig into it!
Chris talks about one simple but lovely premise: “You can achieve performance gains by created non clustered indexes on a different filegroup if the filegroups are using different physical drives.”
Chris has an interesting take on this month’s topic: How Powershell treats filegroups and files!
Another Powershell blog! I wasn’t expecting that. Thomas explores a method to find out how many filegroups a database has, and whether the PRIMARY filegroup is default.
Hemanth takes this opportunity to give us an introduction to FILESTREAMs (it counts, because it needs its own file group).
(By the way, if you’d like an extension on this topic, take a look at my blog “FileTable: SQL Server 2012's little gasp-maker“.)
Steve gives us another introductory Filegroups and Filestream piece that dovetails nicely with Hemanth’s. It’s got a nice walkthrough, and links to Steve’s talks on the subject.
Wrapping up this month’s T-SQL Tuesday is our very own fellow MidnightDBA, Sean….who gives us homework! Well, not exactly, but we hear about assignments he’s given over the years, with surprising conculsions. And of course, the repro.