Mar 112014
 

T-SQL Tuesday LogoThis post is part of T-SQL Tuesday #52, which is being hosted this month by Michael J. Swart (@MJSwart). Michael is asking us to argue against a popular opinion, and I’m more than happy to do so, as this is a belief that I’ve kept to myself for quite a while.

SQL Server’s row compression feature can be an amazing tool. Not only is it lightweight on CPU usage, especially when compared to page compression, but it can save a significant amount of disk space as well. Your data also remains compressed while in the buffer pool, meaning more rows can be stored in memory, reducing the need to make slower requests to disk. On top of all that, some queries (especially those involving index scans) can see dramatic performance improvements.

In fact, row compression is so good that Microsoft’s whitepaper actually states “If row compression results in space savings and the system can accommodate a 10 percent increase in CPU usage, all data should be row-compressed.”

Yes, row compression is a wonderful thing, and the databases I maintain frequently benefit from its use.

But I hate it.

Why? Because all too often, features designed to help make things easier also make people lazy.

By far, the biggest issue that row compression addresses is poor data typing, the use of a data type that isn’t appropriate for the values at hand. For example, if a column is only going to store the values 1 through 5, an integer data type is not necessary. A tinyint data type would be just as effective, and would consume only one quarter of the space. However if you are unable to change the data type, perhaps because the database is part of an application written by a third party, row compression can be a big help.

Row compression allows the storage engine to treat fixed-width data types as if they were variable-width. This means the disk space that isn’t needed to store a value in a fixed-width column and would typically go unused can be put to work. The savings can be tremendous, and SQL Server’s data compression features are completely transparent to end users or applications – all the magic happens behind the scenes, which is why a third party application would be none the wiser.

But what if you are able to change those data types, and just don’t feel the need to do so anymore? Data compression gives you most of the advantages of proper data typing, but all you have to do to get them is flip a switch – no pesky forethought necessary. And that’s why it’s terrible. Because for every person out there who designs their databases and data types with care, there are many more who aren’t interested, don’t care, or don’t even realize it’s an option. Features like row compression that mask these issues aren’t going to interest anyone in solving them the right way.

So while row compression is a wonderful tool and can do amazing things when used properly, don’t forget it’s also an enabler.

Apr 262013
 

A huge thank you to all who took the time to write posts for T-SQL Tuesday #41. I was overwhelmed at the number of replies! I had a ton of them to read, as well as back-to-back SQL Saturdays in Madison and Chicago that have kept me busy the past few weeks, hence the delay in posting this. Without any further ado, here are all the great replies in the order they appeared in the comments section.

Boris Hristov (@BorisHristov) tells the tale of SQL Saturday #152 in Sofia, where he had several obstacles to overcome, including sleeping in, his first attempt at presenting on a MacBook Pro, and having to migrate everything to his manager’s PC when he ran into issues. Despite the technical difficulties, Boris still believes this was one of the best presentations he’s ever given.

Michael J. Swart (@MJSwart) says that he’s “not much of a presenter” and that he’s actually giving his third presentation _ever_ the same day that his post went live. (I had no idea!) He also gives us advice in the form of things he’s done to prepare for it.

Rob Farley (@Rob_Farley) admits that presenting is his drug and he’s addicted to it. Rob loves presenting because “at least one of the people in the audience will benefit from it. And that’s addictive.” I couldn’t agree more, Rob!

Matt Velic (@mvelic) got started presenting by reading at his church, and later through music and debate in high school and college. He credits presenting with helping to boost his self-confidence, and likens it to a tool for helping others.

Richard Douglas (@SQLRich) was also giving a presentation the night his post went live, so Michael J. Swart is in good company! Richard answered all my questions individually; he too says some of his earliest “presentations” came in the form of musical performances. He also says that attending SQLBits was a real game-changer for him.

Jim McLeod (@Jim_McLeod) was not a fan of presenting at first. (I think most of us fall into that bucket.) His opinion changed though when he started working at a laser tag centre, and had to give pre-game briefings to groups of up to 60 people. Technical presentations are of course a bit more involved than laser tag, but he still really enjoys them. Jim’s advice is to respect the audience – they are giving you their time so make sure it’s worth it.

Frank Gill (@SkreebyDBA) tells us that his volunteering as a tour guide at the Art Institute of Chicago has been a tremendous experience and he’s learned something new every time he gives a tour. He says his experiences with SQL Saturdays and user groups have been every bit as enjoyable.

Jes Schultz Borland (@grrl_geek) tells us about the steps she takes to ensure that her presentations are successful. These include writing out detailed goals of the presentation before working on slides or demos, and lots of iterative practicing. As Jes puts it: “Simply, be prepared.” If it’s good enough for the Boy Scouts and for Jes, it’s good enough for me!

Ted Krueger (@onpnt) makes excellent points about choosing topics and practicing. Picking topics that interest us aren’t necessarily what will interest an audience – striking a balance is key. Similarly practicing does not ensure an error-free presentation – plenty of things outside of your control can go wrong, or the audience may not respond well. Being quick on your toes and adapting your talk to keep everyone interested can be very beneficial.

Richie Rump (@Jorriss) wrote about how he was inspired to start presenting while at TechEd 2011, where he realized the he really enjoyed the networking opportunities. After receiving more encouragement on twitter, he submitted to speak at the 2011 South Florida IT Camp, was accepted, and had a blast!

Stuart Moore (@napalmgram) felt like his career was stagnating. After discovering the rest of his team had similar feelings, they all decided to start giving presentations at work about the systems they each were responsible for. Stuart says it was awesome, but the thrill disappeared after a while, but then he discovered his local SQL user group, which he really enjoyed presenting at. At this point he has presented at 2 user group meetings and has submitted abstracts for a few SQL Saturdays.

Steven Ormrod (@SQLAvenger) made note of the interesting fact that he hasn’t participated in a T-SQL Tuesday since the last time I hosted, nearly two years ago. Glad I can inspire you to take part!! Steven has an impressive list of experiences, including some time as a high school teacher. Back in the IT world, Steven wasn’t all that happy with how his career was progressing until he discovered PASS, and got hooked on speaking after presenting at his first user group meeting.

David Gardiner (@DavidRGardiner) thinks his first time in front of a group was probably when he taught Sunday School as a teenager. He makes a great point by saying that presenting can be a two-way street. “Sometimes you end up learning just as much from those you’re presenting to as you hope they did from you.”

Allen White (@SQLRunr) got his start in public performance in the theatre, and I was surprised to find out he was a theater major in college! He started attending Cleveland SQL Server User Group meetings about 8 years ago, and was presenting soon after that. Allen considers presenting to be “one of those magic activities in which everyone benefits”, and I couldn’t agree more!

Lance England (@lanceengland) explains how he prepared to give a recent presentation on MDX, and how much he enjoyed learning more about the language and building his confidence in the process. Sometimes getting forced out of one’s comfort zone is a great way to really learn a subject, because as Lance mentioned, “if you can’t explain it, you don’t know it.”

Mickey Stuewe (@SQLMickey) tells us how she got her start right out of college teaching Microsoft Office and became an MCT soon after. She also mentions how she wanted to motivate people to ask questions, so she started tossing out candy to those that did. (Great minds think alike, Mickey – I’ve actually used this method at a few SQL Saturdays!) She says the reason she loves presenting is the same as it’s been all along – she loves answering questions and helping people.

Robert Pearl (@PearlKnows) makes some excellent points, like accepting constructive criticism from the audience will make you a better presenter, and that just becuase you’re speaking doesn’t mean you are the smartest person in the room. Also on the topic of preparedness I’ve seen a few times now, Robert mentions that “when [unforeseen events] occur, it’s all about how you handle them.”

Mike Donnelly (@SQLMD) states quite simply that speaking makes him happy. He shares a point from a blog he reads which says “Novelty and challenge bring happiness”, and public speaking gives him all those things.

Sean McCown (@MidnightDBA) who always makes me smile, walks us through a presentation from the idea to the abstract, and the big day itself. He also manages to mention Blazing Saddles and The A-Team, both of which I watched at way too young an age. After reading this it’s clear that Sean and I both love it when a plan comes together, and it sounds like that’s just what happened in this presentation.

Gethyn Ellis (@gethyn_ellis) tells how speaking didn’t come easy to him at first, but he now makes a living teaching SQL Server learning courses. He offers the following advice: 1) you will always be nervous. 2) Nerves are a good thing – they make you prepare. 3) Being prepared is important – without it you will fail.

Tim Ford (@SQLAgentMan) recalls the tale of how he was thrust into the spotlight at the 2005 PASS Summit when the host for the Quizbowl suddenly had to cancel. Since then he’s presented at many events, both large and small, and even started SQL Cruise! Sounds to me like a great example of the awesome things that can come from bring thrown a curveball. Thanks, Tim!

Bradley Ball (@SQLBalls) got his first taste of the SQL community through 24 hours of PASS, and attended SQL Saturday Orlando shortly thereafter. His first speaking experience was at SQL Saturday in Tampa, and from there he was hooked. He also apparently worked at the White House, which I’ll totally have to ask him about sometime.

Kendal Van Dyke (@SQLDBA) didn’t think his first presentation about SQL Server XML in 2006 was a very big success, but it ended up earning him the ability to speak at SQL Saturday #1. Now he’s on the PASS Board and is Director of the SQL Saturday portfolio! Kendal says his career is very different than it was back in 2006, and that’s largely due to presenting. He advises new presenters to “be careful, because you might just walk away wanting to do it again!”

Mark Broadbent (@retracement) gave his first-ever presentation at SQLBits 7, and considers it to be “one of the most frightening, exhilarating, scary and rewarding experiences” he has ever had. Mark also credits the preparation for that event with improving his technical skills exponentially from where they were before. He has presented at every SQLBits since then.

Steve Wake (@stevewake) is contributing to T-SQL Tuesday for the first time! Steve got his start doing technical presenting at the Denver Visual Studio User Group and received lots of positive comments. From there he sought out other user groups and found the Denver SQL Server User Group. Since then he’s also started doing the SQL Saturday circuit, and presenting is now an integral part of his career.

Ed Watson (@SQLGator) tells us that he’s not in love with presenting – yet – but he’s getting there. He used to avoid it like the plague, but the SQL community and the great experiences he’s had at SQL Saturdays have really been helping him conquer his aversion. Thanks for sharing, Ed!

Paul Timmerman (@mnDBA) is not only making his first T-SQL Tuesday blog post, but his first blog post in general! Paul says he’s never really been afraid of speaking, just often found himself lacking the motivation to get up and do it. The SQL community has changed his mind, though, and he recently presented at both the PASSMN user group and SQL Saturday Madison. He is happy to report having excellent experiences both times, and I’m sure will be presenting more in the future!

Jim Dorame (@DBAJD) is also making his first T-SQL Tuesday contribution. He recently gave his first presentation at SQL Saturday in Madison and was surprised by the positive reaction he got from attendees. He’s presenting again at SQL Saturday in Fargo this weekend and really looking forward to giving back to the community that has helped him throughout the years.

Wayne Sheffield (@DBAWayne) cut his teeth presenting when he was in the US Navy and had to conduct training for his entire division. (That sounds it was in front of a lot of people, Wayne!) Later he was writing articles for SQLServerCentral.com and was encouraged to turn an particularly popular one into a presentation. Not only was the presentation a success, but Wayne discovered that he really enjoys watching others learn.

Luke Jian (@sensware) says “a journey of a thousand miles begins with a single step” which is an excellent quote, though I must admit the first time I heard it was when Wilson said it on Home Improvement. In Luke’s case this single step was at SQLskills IE1 2011 in Chicago, which I was very fortunate to be attending. I remember Luke getting up there and schooling us all in the ways of SSDs and think it’s awesome how many events he’s presented at since.

Erin Stellato (@erinstellato) and I have even more in common than I previously thought – it turns out she was a campus tour guide in college just like me! As a graduate student in Kinesiology, Erin had the opportunity to teach an introductory course, which helped her realize that she loved teaching. Watching one student “get it” during her first rotation was particularly gratifying. Erin also makes the comment that the term “teaching” is a lot less scary than “presenting”, and I totally agree!

Mike Fal (@Mike_Fal) tells us about the “hook”, which for him is more than a catchy Blues Traveler song. After his first ever presentation at the Denver SQL User Group he was told that “I’ve been a DBA for over twenty years. You taught me something new tonight.” Not only is that excellent, but in my opinion that’s exactly what the SQL community is all about.

Julie Koesmarno (@MsSQLGirl) started doing one-on-one tutoring for high school students, and later found herself doing the same for college students. After graduating, she started presenting and found she loved the discussion aspect and how we can all learn from each other.

Steve Jones (@way0utwest) was apprehensive about speaking in front of strangers as many of us were, but much of this was overcome by working in the restaurant business and constantly meeting (and talking to) new people. His first technical presentation was at a PASS Summit, and while he didn’t jump in right away, a few years later he was presenting regularly. Steve really enjoys the chance to teach people and help them improve their careers.

Doug Lane (@thedouglane) argues that presenting is essentially the same as performing, which he did a lot of when younger, especially through band. He makes the point that presenting is a lot like doing an improv solo – there’s a framework (key, tempo, measures) but the rest is filled in on the fly.

Robert L. Davis (@SQLSoldier) says he has never loved presenting more than when someone pinged him for advice. He was able to give guidance on how to submit, and after the person was selected he advised them on giving the actual presentation as well. Robert also was honored to be asked to sit in on the presentation for moral support, which he did. Not only does Robert mention bacon, but he also references Revenge of the Nerds, another movie I watched at way too young an age. (My grandmother let me get away with murder when I would spend Saturdays at her house.)

Jason Strate (@StrateSQL) had no interest in presenting in front of groups for any reason until he went to work for his first consulting company, where one of his co-workers gave him the necessary kick in the rear to try presenting at the Minnesota SQL Server User Group (PASSMN). Jason says this presentation is widely regarded as the worst talk in the history of the group, but he was convinced that he could do better than that, so he kept speaking.

And last but not least, me! Yes, I participated in the T-SQL Tuesday I hosted.

Once again, thank you to everyone who participated! By my count there were 38 posts in all. Of those, 3 were from people participating in T-SQL Tuesday for the first time, 1 was a first blog post ever, and 2 were by people who were giving presentations the same day their post went live. How awesome is that?!?

Apr 092013
 

This month’s T-SQL Tuesday is hosted by…ME! I asked everyone to share stories of how they came to love presenting, and I have not one but two tales of my own. They both took place at about the same time: my freshman year at the University of Illinois. I can’t remember which one came first, so you get to read both!

Physics Van

Shortly after classes began, my Physics 111 lecture was visited by Professor Mats Selen, who got everyone’s attention by putting a small amount of liquid nitrogen into a soda bottle. He capped it, threw it into a large plastic garbage can, and fastened the lid. Seconds later the lid touched the 20+ foot ceiling from the force of the explosion. He then explained if we were interested in doing things like this more often, come speak to him about joining the crew of the Physics Van. I was at his office within a day or two.

Demonstrating the power of atmospheric pressure with Magdeburg Hemispheres

The Physics Van is an outreach program that brings a free traveling science show to elementary schools and community groups, typically within an hour’s drive of campus. The hour-long program gets kids excited about science by demonstrating basic physics experiments, many with audience participation. Of course no science show would be complete without a few explosions thrown in as well!

Not only did I have an amazing time and meet some wonderful people, but Physics Van taught me a ton about stage presence and engaging an audience. I also learned how to be comfortable speaking in front of large groups, and that making something appear spontaneous takes lots of rehearsing. I’ll admit that getting up and talking in front of hundreds of people is much easier when your audience is in the 4th grade or younger. It’s also nearly impossible to not get kids that age excited about whatever you’re going to be doing. Whether they really enjoyed the show or were just happy to not be in class, we always saw tons of smiles from the audience. In my mind, it was the perfect environment to get comfortable with speaking.

Bonus! You may or may not find yours truly (circa 2003) in this video of a show. Disclaimer: it appears to require Internet Explorer (again, this was 2003.)

Being a Campus Tour Guide

One morning early in the year, my roommate and I were startled awake by the door to our room being unlocked. It opened quickly, we heard an “OHMYGODIMSORRY!”, it closed in an instant and we heard lots of people being shuffled out of the hallway. My dorm room had previously been the “model” room shown to tour groups (I knew this because I saw it when I cam for tours) and apparently some of the tour guides never got the memo.

My lava lamp collection was always a hit with tour groups. (And hey, check out those books I had even 12 years ago!)

This really didn’t bother me or my roommate. In fact, I went to the Campus Visitors Center and encouraged them to send tour groups to our room, as we could show them what a real, lived-in dorm room looks like. They liked that idea, so our room became a regular stop on the campus tour. The model room looked so fake anyway, with perfect furnishings all straight from the store. Ours may have been totally cluttered, but at least it was authentic!

Over the course of my freshman year, I made more contacts in the Admissions and Records office, and applied for a job as a tour guide for the following year (they don’t allow freshmen to give tours.) This ended up being my job for the next 4 years, and I loved it. Getting paid to share my love of the University with prospective students and their parents was awesome, and there were never any dull moments. I remember one time in particular where I was walking a tour group down the street and a good friend of mine saw us going by. She came up to me and started hitting on me like crazy in front of them. Told me to come by her place because she needed my help studying for her anatomy exam that night. The look on everyone’s faces was priceless! Another time I was taking my tour group into the dorm to show them my room, and my neighbor was standing at the front desk wearing nothing but a towel. He asked if he could speak to the group for a second, and then explained that his current state was a prime example of why you never forget your keys when going to shower, as his roommate had left and locked the door, leaving him waiting for the front desk to get him a copy!

Tour guides at U of I developed some great skills, like the ability to talk very loudly for an hour or more while walking backwards and not running into people or obstacles. We also had to be good at answering unexpected questions. Before or after each tour there was a formal question/answer session where several tour guides went in front of a room full of people and answered literally any question about the school that the audience could throw at us. As I’m sure you can believe, most questions weren’t simple ones, like “What’s a good place to eat?” The kids would ask questions like “Can we have members of the opposite sex in our dorm room?” Parents would ask “There seem to be a lot of bars near campus – how do I know that my Timmy isn’t going to become a drunkard?” It was an excellent exercise in coming up with appropriate answers with a positive spin, no matter how much we really wanted to say that as long as your roommate is cool with it, resident advisors aren’t going to care if your significant other spends the night or not.

So those are my stories. In both cases I had very positive experiences getting up in front of groups of people and speaking about things I love: science and the University of Illinois. When I think about it that way I guess it’s no surprise that I enjoy giving presentations about SQL Server as well.

Apr 042013
 

It’s been almost two years since I last hosted T-SQL Tuesday, and I’m very happy to be doing so again! If you’re not already familiar, T-SQL Tuesday was the brainchild of Adam Machanic (@AdamMachanic), who prophesized that we all could benefit from “a recurring, revolving blog party” with a new topic given each month. This month’s T-SQL Tuesday will take place on Tuesday, April 9, 2013.

What’s the Topic?

This month the prompt is how did you come to love presenting? What was the first time you gave a presentation in front of a group and really enjoyed it? Was it something that was required of you in school? Something you did in the workplace? Were you inspired by other SQL community members and thought “I think I can do that too”? Whatever your story is, I’d love to hear it. Not a presenter? Not a problem! Feel free to chime in with whatever you like that’s related to either presenting or SQL Server in general.

The Fine Print

- Your post must be published between 00:00 GMT Tuesday April 9, 2013 and 00:00 GMT Wednesday April 10, 2013.
- Your post must contain the T-SQL Tuesday logo at the top (see above) and the image must link back to this blog post.
- Trackbacks should work, but if not please put a link to your post in the comments section so everyone can see your contribution!

Extra Credit

The following are not required, but might be a good idea.

- Include “T-SQL Tuesday #41″ in your blog post’s title.
- Tweet about your post using the #tsql2sday hashtag.
- Make one or more references to bacon or other pork products.
- Contact Adam Machanic and tell him you’d like to host a T-SQL Tuesday from your blog. If you’ve kept up your blog monthly for the last 6 months and participated in at least 2 previous T-SQL Tuesdays you are eligible.

I’ll be reading everyone’s post shortly thereafter and will create a summary of what everyone wrote about. Happy writing!

Mar 122013
 

T-SQL Tuesday LogoIt’s once again the second Tuesday of the month, meaning it’s T-SQL Tuesday! This month’s topic comes to us from Jen McCown (blog | @JenniferMcCown). Jen is asking us to share our knowledge about filegroups, and it just so happens that I have a story about migrating to new storage that involved a large number of file and filegroup operations.

The Problem

At work we have been very fortunate to get a new SAN, and I was charged with moving data from the old SAN to the new one. This data falls under 2 main groups: large numbers of small files which can be moved at any time without consequence, and data files from the production SQL Server database. I covered how I moved the non-database files in a previous post on the Microsoft FCIV. This time I’ll cover the larger task of moving the databases. To make things much more difficult interesting, I did not have the luxury of being able to take any downtime to accomplish this move. Making use of pre-scheduled maintenance windows was fine, but those are typically 2 hours every 2-3 months, not nearly enough time to move the 22TB of data that was in need of migration.

Just to be clear, the instance of SQL Server was not moving or changing at all. The server was connected to both the old and new SANs, and the objective was to move a particular database from old to new. Yes, there are other databases on the server, but they are located on other storage and did not need to be moved. We use SQL Server 2008 R2 Enterprise Edition, so at least I had the full arsenal of functionality available to me.

I began listing out all the ways to move data that I could think of, and started disqualifying from there:

Copy the files. This would be easy and relatively fast, but requires taking the database offline to move the files. Since uptime is paramount, this is a no-go.

Online index rebuilds. Using enterprise edition means it’s possible to rebuild tables and indexes online without interrupting users. It is also possible to move objects to a new filegroup online as well. This is very useful, but comes with some limitations. Prior to SQL Server 2012, indexes containing LOB (large object) information such as columns of type image, ntext, text, varchar(max), nvarchar(max), varbinary(max) and xml can’t be rebuilt online. SQL Server 2012 has improved this feature so this limitation only applies to image, ntext, and text columns, which are deprecated anyway. But even though SQL Server 2012 lets you perform online rebuilds of tables with LOB data, it still won’t let you move the LOB data to a different filegroup. This solution gives us the uptime we require, but unfortunately can’t move everything.

Mirroring. Much like a rolling upgrade, mirroring can be used to migrate a database between instances. You can set up a mirroring session with the mirror server on the instance you want to migrate to, manually fail over, and you’re now on the new instance.  Unfortunately it won’t work in this case because the primary and mirror databases can’t reside on the same instance.

Backup and restore onto the new SAN. This is a very strong candidate, especially since I can backup the database in about 3 hours and restore it almost as quickly. Restoring a backup on the new storage (with a different database name) can take place while the current database remains online. Cutting over would involve backing up the tail of the transaction log on the old database, restoring it on the new one, and then swapping names. The cutover would be quick enough to fit in a maintenance window, making this a very attractive option. I ended up choosing against it because the new storage would be partitioned differently than the old storage  and adjusting the files and filegroups and then rebuilding objects across them would make the process more complicated than the solution I ended up choosing.

Add and remove files from the filegroup. This approach can be very useful, but rarely gets mentioned. SQL Server allows you to add and remove files from filegroups at almost any time, and this can be leveraged to move data between different storage systems. What makes it awesome is that the operation is completely online with no interruptions to the user, all types of data (including LOB data) is moved, and it works in any edition of SQL Server. As always, there are trade-offs. Shrinking data files is generally frowned upon as it causes high levels of fragmentation. It’s also very slow, and LOB data makes it even slower. Still, fragmentation can be solved using REBUILD or REORGANIZE, and if speed is not important, it could be the way to go.

My Solution

The add/remove file method is best for preserving uptime, but due to its slow speed I decided to use a hybrid solution with 4 parts:

1. All tables & indexes that can be rebuilt online on a new filegroup are moved to a temporary filegroup on the new storage.

2. The remaining tables & indexes are moved via adding & removing files to the original filegroup.

3. The contents of the temporary filegroup are moved back into the original filegroup via online rebuilds.

4. The transaction log and primary filegroup (which cannot be moved by the add/remove method) are moved during a scheduled downtime.

The image below shows our starting state: the new SAN is empty, while the old SAN has a (non-primary) filegroup [FG1] with files A, B, and C in it, each 1GB in size. Most systems will contain more filegroups, but for simplicity I only show one here.

The first step was online index rebuilds. For each filegroup on the old SAN I created a temporary one on the new SAN. For example, in this case of having [FG1] on the old storage, I’d create filegroup [FG1_Temp] on the new storage. Then I did online rebuilds onto the temp filegroup for each table and index that would allow it. This can be an arduous task for filegroups with large numbers of objects, so I created a powershell script to automate this which I’m happy to share. Creating the new filegroup and files can be done via the following T-SQL:

ALTER DATABASE DB_Name ADD FILEGROUP FG1_Temp;

ALTER DATABASE DB_Name ADD FILE
(NAME = 'X', FILENAME = 'path_to_san2\X.ndf', SIZE = 1GB, FILEGROWTH = 1GB),
(NAME = 'Y', FILENAME = 'path_to_san2\Y.ndf', SIZE = 1GB, FILEGROWTH = 1GB),
(NAME = 'Z', FILENAME = 'path_to_san2\Z.ndf', SIZE = 1GB, FILEGROWTH = 1GB)
TO FILEGROUP FG1_Temp;

The powershell script will move objects online from one filegroup to another. Objects which cannot be moved online will be ignored. When you open the script file you will see variables that need to be filled in for the server name, database name, and source and destination filegroups, and a folder to log to. Optionally you can filter by schema name, object name, and size.

I am happy to share this with the community, but please be advised of the following: This script needs to run in sqlps, the SQL Server powershell extension. It was written for and tested on SQL Server 2008 R2. I have not tested it on SQL Server 2012. I do not consider this to be perfect or in any sort of a finished state. And as always it is your responsibility to read and understand this code before running it on your own systems. Always test in a development environment, no warranty is expressed or implied, etc. etc., use at your own risk!

You can download the script here.

After the online filegroup migration, the storage layout will appear as follows, with a temporary filegroup on the New SAN:

Storage 2

Once the migration script has completed, the old storage will contain the original filegroup with all the objects which could not be moved online. At this point all those objects still need to be moved onto the new storage, and the best (and perhaps the only) way to do this in an online fashion is by adding and removing files from the filegroup.

Here’s how it works: SQL Server allows the addition and removal of files from filegroups at almost any time via the ALTER DATABASE command. (You can’t add or remove files while a backup is running.) A filegroup can be extended onto new storage by adding files to it which are located on the new storage. The script below shows how to do this by adding files D, E, and F, located on SAN 2.

ALTER DATABASE DB_Name ADD FILE
(NAME = 'D', FILENAME = 'path_to_san2\D.ndf', SIZE = 1GB, FILEGROWTH = 1GB),
(NAME = 'E', FILENAME = 'path_to_san2\E.ndf', SIZE = 1GB, FILEGROWTH = 1GB),
(NAME = 'F', FILENAME = 'path_to_san2\F.ndf', SIZE = 1GB, FILEGROWTH = 1GB)
TO FILEGROUP FG1;

After executing that, filegroup [FG1] is now spread across the old and new storage with 3 files on each SAN, as well as the 3 files containing the temporary filegroup [FG1_temp] on the new SAN:

Storage 3

At this point, files D, E, and F contain very little data, if any at all. Merely creating a file doesn’t mean it will be populated with existing data, though SQL Server will start to make use of it for operations that allocate space on disk from here on out. The objective now is to remove files A, B, and C, so the filegroup has no presence on the Old SAN. This is done using the DBCC SHRINKFILE command with the EMPTYFILE argument, which will remove all of the data from the file by distributing it across the other files in the filegroup. Depending on the size of the file and the amount of data it contains, this operation can take a long time. Fortunately if you have to stop it, the work that has already been performed will not be lost. Once a file has been emptied in this manner it can be removed by using ALTER DATABASE. The code to accomplish this is as follows:

DBCC SHRINKFILE (A, EMPTYFILE);
ALTER DATABASE DB_NAME REMOVE FILE A;

DBCC SHRINKFILE (B, EMPTYFILE);
ALTER DATABASE DB_NAME REMOVE FILE B;

DBCC SHRINKFILE (C, EMPTYFILE);
ALTER DATABASE DB_NAME REMOVE FILE C;

When this is complete, filegroup [FG1] will only exist on the new SAN, however there is now 2 filegroups and we started with 1. Filegroup [FG1_Temp] can be removed once all objects in it have been moved back into filegroup [FG1], again using the online migration script. I realize this method involves rebuilding some objects more than once, however in my testing it was still faster than moving everything by adding/removing files from the filegroup. I would imagine your mileage will vary depending on your environment and the number of tables containing LOB data, but this is what worked best for me. On my system about 80% of objects could be moved online, leaving only 20% that needed to be moved via the slower method.

Storage 4

Once the online rebuild script completes, things will be nearly complete. All data will be in its original filegroup on the new SAN. Filegroup [FG1_Temp] and its files can now be removed via the following script:

ALTER DATABASE DB_Name REMOVE FILE X;
ALTER DATABASE DB_Name REMOVE FILE Y;
ALTER DATABASE DB_Name REMOVE FILE Z;

ALTER DATABASE DB_Name REMOVE FILEGROUP FG1_Temp;

Storage 5

Once this is repeated for each filegroup in the database, the job will be almost complete. The primary filegroup and transaction log cannot be moved using either method just discussed, so a downtime will be required to move them to the new storage. I was able to make use of existing maintenance windows to detach the database, copy the files to the new SAN,and reattach it. Everything came back up without incident.

At this point the only issue left to tackle is fragmentation. The data that was moved via online rebuilds is not affected, but tables and indexes moved via the add/remove files method will be very highly fragmented. The quickest way to resolve high levels of fragmentation is to rebuild an index, but these are the indexes which cannot be rebuilt online! If an offline rebuild is not possible (and since uptime was very important in my case it wasn’t possible) the next best method is to use the REORGANIZE argument of the ALTER INDEX statement. REORGANIZE is typically slower than REBUILD, but it will allow the index to remain online for users while it runs.

Final Thoughts

I don’t consider this method to be perfect by any means, but it worked rather well for me given the uptime requirements I had to meet. Thank you very much Jen for the excellent topic, and thank you to all of you who kept reading to the end! I hope this proves helpful!