T-SQL Tuesday #40 Roundup: Files and Filegroups

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 Farley: Filegroups and Non-Clustered Indexes

Rob wrote an absolutely fantastic primer for filegroups, with useful code, that I’ll be pointing people to regularly from here on out.

Jason Brimhall: T-SQL Tuesday #040: File and Filegroup Wisdom

Jason had a “filegroup not online” issue just in time for T-SQL Tuesday! He walks us through his solution here.

Merrill Aldrich: T-SQL Tuesday #040: Files, Filegroups and Visualizing Interleaved Objects

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 Keisler: T-SQL Tuesday #40 – Proportional Fill within a Filegroup

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 Davis: Day 30 of 31 Days of Disaster Recovery (T-SQL Tuesday #40): Using Partial Availability and Initialize from Backup to Replicate a Partial Database

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 Pusateri -: Moving A Database to New Storage With No Downtime

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 Yates: T-SQL Tuesday #040: File and Filegroup Wisdom

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 Fradenburg: T-SQL Tuesday #040: File and Filegroup Wisdom [Powershell alert!!]

Chris has an interesting take on this month’s topic:  How Powershell treats filegroups and files!

Thomas Stringer: T-SQL Tuesday #40: Get Filegroup Count and Default Configuration with PowerShell

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 Damecharla: T-SQL Tuesday #040: File and Filegroup Wisdom (Quick Introduction to FILESTREAM)

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 Jones: T-SQL Tuesday #40– File and Filegroups

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.

Sean McCown: T-SQL Tuesday: A file delete exercise

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.