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?!?

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.

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!

Last week while at work I received a phone call from a recruiter. It was pretty standard until they decided to turn into a jerk. I sent the following letter to the recruiter’s agency. I doubt I’ll get a reply, but if I do I will be sure to share it here. Identifying info has been removed to protect the not-so-innocent.

Dear <redacted>,

I received a phone call at work last week from <redacted>, one of your recruiters. Getting phone calls from recruiters during the work day is a fairly common occurrence, but I’m sure you will agree that the rest of this call was very uncommon.

<Redacted> said their records showed I’ve been working for Northwestern University for nearly two years, and they were curious if I was interested in pursuing any new opportunities. I told them I was not interested at this time, but if they would be willing to send me a job description with a salary range I would be happy to forward it to others I know. I am fortunate to have lots of contacts in the SQL Server world, and I know a few who are currently seeking out new positions.

Most recruiters are happy to take me up on offers like this, and I have generated some interviews through this manner in the past. <Redacted> was clearly not as enthusiastic about this, as they snapped back with a “Well why aren’t you interested? How do you even know you’re not interested if I haven’t even told you about the position yet?” They proceeded to explain the position – a production DBA for a respected local company that’s been in the <X> industry for over <Y> years. I was happy to hear this was at least a position relevant to my experience – I’ve received calls in the past for things like “an exciting position staffing the overnight help desk at a company in [some other state]. And if you play your cards right, you’ll have the chance to move into a junior developer role after a few years!”

Still I told them I wasn’t interested, and this is where things got really strange. (I will paraphrase as best I can.) “Let me give you a piece of advice”, they said. “I’ve been an IT recruiter for <Z> years, and as long as you work for a University, I know for a fact that your career is going nowhere. I’m sure it’s comfortable there, but you’re not doing anything interesting, you’re not growing your skills, and you will find yourself with a whole lot less respect the next time you are looking to change employers.”

Wow. I told them I’m very sorry they feel that way, and that in fact I’m doing some incredibly interesting projects right now. “So what is it that you’re doing? I want to hear this” they said, their voice dripping with sarcasm. “Well you already told me what I’m doing is not interesting, so why should I waste my time explaining it?” I said. I thanked them for taking the time to call me, and said I needed to get back to not growing my skills. <Redacted> hung up before I had the opportunity to end our conversation. How professional of them.

Is this really the way you do business? I am no expert in the field of recruiting, but I’m pretty sure that insulting your product (yes, I do realize that I am the product, not the client) is not going to result in any placements or productive relationships in the future. If <redacted> has managed to stay in the business for <Z> years while acting like that, that’s pretty pathetic.

Despite what <redacted> told me, I happen to be furthering my career right now in ways that weren’t even possible in my previous positions. While I enjoy my current role and have no plans to leave it in the short term, I don’t plan to stay there forever either. There will come a time where I’ll find myself in need of new challenges. I sincerely hope that my next career move is to the type of position that’s not posted anywhere and hence I won’t be needing the services of a recruiter. But if that doesn’t happen, I am absolutely sure that I will never, ever, do business with <redacted> or your agency again. Please remove me from all of your directories and lists immediately.

Sincerely,

Bob Pusateri
SQL Server DBA with a career that’s “going nowhere”

Moral of the Story

First of all, the vast majority of recruiters are not like this. It is by far in a recruiter’s best interest to build a positive relationship with you, because even if the job they’re contacting you about isn’t something you’re interested in, chances are good that in the future they’ll find something that does interest you.

Second, there are lots of recruiters and job placement agencies out there. If you get a cold call from someone who’s being a jerk, I wouldn’t hesitate to tell them to remove you from their records and take your business elsewhere. Others will be more than happy to assist you in your job search.

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:

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:

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.

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;

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!

For now though, let’s talk about copying files. Lots of files. Server logs, audit logs, things like that. Mostly they were small in size but large in quantity. I ended up with a handful of directories with several thousand files in them that needed to move from SAN A to SAN B. Windows gives us several ways to do this:

• Copy in Windows Explorer (ewww)
• The good ol’ DOS Copy command
• Xcopy, which offers a few more features
• Robocopy, the most advanced of the MS copy utilities (though I believe it prefers to be called Murphy)

All of these will do a fine job of copying your files, though Robocopy will probably be the fastest due to its multithreading capabilities. But how do you know they all reached their destination intact? Copy and Xcopy offer the option of verification (both using the /v parameter) but sadly Robocopy does not. I’m not sure if verification is just built-in to Robocopy and can’t be disabled, or if it doesn’t exist at all. Either way I didn’t want to risk errors in moving all this data, so I decided to go the extra mile and use another tool to make sure. It didn’t take me long to find the Microsoft File Checksum Integrity Verifier (“FCIV” for short), a nifty little unsupported command-line utility that does exactly what I was looking for.

FCIV In A Nutshell

Basically, FCIV calculates MD5 or SHA-1 hash values for files and outputs them either to the screen or to an XML file. It can also compare files to those checksums saved in XML and tell you if anything differs or is missing. A demo is worth a lot of words, so let’s see it in action!

• Download Microsoft FCIV and extract the executable wherever you like – for this demo I put it in G:\
• Download the demo files and extract them. I put mine in G:\demofiles
• Use FCIV to generate checksums of all files in the folder and save to an XML file with the following syntax:

fciv.exe -add G:\demofiles -wp -sha1 -xml G:\hashdb.xml

-wp means we’re saving only the file names in the XML file, not their full path
-sha1 specifies to calculate a SHA-1 hash on each file. The default is MD5.
-xml means output the checksums to an XML file, in this case the G:\hashdb.xml that follows it.

Let’s open up that XML file and see what it contains:

As you can see it’s very simple, just the file names and a checksum for each. Now let’s make a few changes.

• Change the name of the directory the files are in. I changed mine from “demofiles” to “demofiles2″.
• Delete fileE.txt
• In fileD.txt, delete the line that says “***DELETE THIS LINE***”

Now let’s use FCIV to verify our files against the checksums we captured in the XML file. Change the current directory to demofiles2 (it won’t work unless you do this) and then run

G:\fciv.exe -v -sha1 -xml G:\hashdb.xml

-v means we’re now in verification mode, so it will verify checksums in the current directory against those in the XML file
-sha1 again specifies we’re using the SHA-1 hash
-xml is the file we’re comparing our calculated checksums against

Here’s the output it produces:

As you can see, FCIV is telling us that the contents of fileD have changed and fileE is missing. It’s really that easy!

Final Thoughts

I think FCIV is a great utility to keep in your toolbox. Some people may argue that checksum verification isn’t necessary – that Windows does it for you behind the scenes. That may be entirely true, but I wasn’t able to find any concrete documentation proving that it does. Then 10 minutes I spent finding this program online and figuring it out is a very small price to pay for some extra peace of mind in knowing that thousands of files made it to their destination intact.

Others may raise the point that both the MD5 and SHA-1 checksums both suffer from collision vulnerabilities and there are better alternatives out there that this application doesn’t support. They’re totally correct, but it’s also important to remember that we’re using these checksums to detect changes, not for cryptography or protecting secrets. Any form of verification is better than none, and for my purposes FCIV has proven to be very helpful.

Let’s say you have a database that receives daily imports from your company’s accounting system so users can query it and create reports. This setup becomes popular, and management asks you to add weekly loads from the payroll software. You begin importing payroll into the same database and all is good until you get a request to add hourly updates from the inventory application. These requests continue, and after a few additional systems you now have a really large database on your hands. VLDBs are great when they have a purpose and fulfill a need. If a database is huge only because more and more unrelated things kept being added to it, however, that purpose starts going the way of the dodo.

Bigger than necessary

So we now have a database that contains data from multiple systems that are being updated at different frequencies. This is where things have the potential to get hairy. Records in the database’s copy of the accounting system, which is loaded daily, may not have corresponding entries in its copy of the payroll system, which is only loaded weekly. Even if all the systems were loaded into the database at the exact same rate there is no guarantee that all the corresponding records would be available at the same time across systems. Backups are also starting to take a considerable amount of time due to the volume of data they contain. At this point it’s probably worth taking a step back and looking at how the system is designed. A single database may be meeting our needs, but when manageability starts to become difficult we might be better served by making some changes.

SQL Server implements many features at the database level, however the core functionalities of a database in my opinion are logical consistency and recovery. Logical consistency (the “C” in ACID) guarantees that the database will always be in a consistent state. This state is enforced through transactions made possible by the database’s transaction log. In our scenario the database is doing all this work to ensure everything is always in a consistent state, however there’s no consistency between the different systems being stored within the database.

Backups, restores and recovery also take place in terms of the entire database. You can recover to any point in time (assuming the database is using the full recovery model), but you’re recovering the entire database to that point in time. Restoring our example database may bring us to a point in time where the payroll system was fully loaded, however the accounting system’s load was only halfway complete. There’s no way to recover only part of the database further ahead in time than the rest.

In this situation since the separate systems within the database will never be consistent with each other I would lean towards splitting this database up into multiple smaller databases, one for each system. There are many pros and cons to this – here are some of the larger points:

Pros:

• Individual databases are smaller and more manageable than a single large database. Maintenance tasks such as integrity checks will take less time due to the smaller size.
• Backups and restores will be smaller and will take less time.
• Databases can be recovered individually. Should one database need to be taken offline the others will be unaffected. All downtimes are bad, but a partial downtime is often better than having everything unavailable!
• The systems won’t have to share a transaction log when in separate databases. This can result in increased performance when the transaction logs are placed on separate disks.

Cons:

• You now have more databases to maintain.
• It’s more difficult (but not impossible) to figure out the state of all the different systems at a particular point in time when they’re in separate databases.
• Users will most likely be required to connect to multiple databases.
• Setting up security will involve a bit more work, especially in the likely situation of having to accomodate joins across databases.

In many of the cases I’ve seen, splitting things up into multiple databases has been worth the trouble. VLDBs that were getting too large for their own good were transformed into several smaller databases that were much easier to manage. Having to connect to multiple databases proves to be trivial for most users, and things can be further simplified by creating views where appropriate.

Final Thoughts

VLDBs aren’t a bad thing. As time progresses it will only become more common to have a lot of closely-related data that warrants a very large database. But when a database is huge for no good reason it may be better off being split up. This is especially true in cases where the database contains data from multiple disparate sources. When faced with bringing a new dataset into your environment it’s an excellent time to think about whether this data belongs in its own database or not. While it’s easy to keep adding to a giant “one stop shop” database, bigger isn’t always going to be better.