Art of the DBA Rotating Header Image

community

Validation and Inspiration

I’d like to take a brief break from the technical posts to talk a little bit about community. As I write this, I’m currently heading back to Denver from SQL Saturday Phoenix. As with other SQL Saturdays I have attended, this was a fun event with lots of great learning and camaraderie with my fellow SQL geeks. This is a bit of a love letter to those geeks, but I wanted share with you some of the impact this event had on me.

Validation

As with everything else I’ve done in 2015, my presentation at this event was Powershell related.  I gave a presentation on Powershell Tips and Tricks for SQL Server DBAs for the third time this year and was pretty pleased with my execution. What blew me away was the reception from the audience. I had a ton of positive feedback and comments and could tell the attendees really appreciated what I shared with them.

Why am I telling you this? Because I want to convey to you why you should present and the benefits of it. It’s more than just having your ego stroked or getting that pat on the back (though those don’t hurt). When you share your knowledge, you have an immediate and profound impact on other people’s careers. Each and every one of us has something that others can benefit from.  We need to share it. To know that I showed my audience a better way to do their jobs and help them step up to another level is extremely gratifying.

Much is made of technical presentations being used as a vehicle for advancing your career. They are also a vehicle to advance the careers of your peer group. The great thing is the more we help each other, the more we help ourselves and make our skills and abilities stronger. I could see this in the gratitude of my audience and the feedback they gave me from my session.

Inspiration

What I like most about technical conferences is not just the education and the sessions. These are valuable for both the presenters and attendees, but the true value is gained in the conversations that happen around the event. This is why it’s so important to make time to talk with the other people at these events, to chat with speakers, and to avail yourself of the social aspects. You’ll find inspiration for solving problems at work, developing new strategies for your current position, or defining the next moves in your career.

I had several such conversations while I was at the event. Coming away from this SQL Saturday, I was able to help some of my peers with strategies and ideas for their blogs, their presentations, and their jobs. Beyond that, though, I was inspired for other things I could do to both improve myself and my career.

One example was a conversation with Amy Herold(@texasamy), where we talked a lot about Powershell and automation. She gave me a few ideas that I could further develop and we talked about some projects we could collaborate on. I’ve got some exciting ideas that I hope to work with Amy on over the next few months that will help both of us grow.

It’s hard to have these kind of conversations during our day-to-day jobs. Since we’re usually only one of a couple people (or maybe the lone gunman) doing data work in our jobs, it’s difficult to bounce ideas off of others and get that inspiration. You’d be amazed at what kind of thoughts you will get when you have really smart people to talk with.

Satisfaction

The reason SQL Saturdays are such great events is it allows the greater SQL community to share, connect and learn with one another. If you haven’t been to a SQL Saturday, I strongly encourage you to go. I know it’s sometimes tough, being on a weekend when some of us would rather be getting along with our non-database lives.  I want you to think about what you could do with your career, though, if you gave up that one weekend. Where could you go if you could have that kind of free learning. Most of all, how much better will you be by plugging in to the SQL community and feeding off the energy and knowledge you can find at these events. Building your career is more than just learning, it’s collaborating and sharing. SQL Saturdays give you all of this in spades.

 

Why I Work With SQL Server

Hot on the heels of my NoSQL posts, I wanted to add a counterpoint to the discussion.  After all, even though I see the value of non-relational technologies, I think it’s important not to lose sight of the value relational databases offer.  In the tech world, it’s too easy to chase those squirrels of new tech (though it’s also easy to get stuck in our old patterns as well).  It always helps to take a step back and see the forest for the trees so we can choose the right path for our enterprise.

It is an understood fact that the tech world gets pretty dogmatic:  Oracle vs. SQL Server, Windows vs. Linux, Java vs. C#, etc.  People will dig their heels in about their choices and why those choices are considered “right” when, at the end of the day, each platform is simply a different approach to various higher concepts.  I tend to view most of these debates as Ford vs. Chevrolet and the only real question to answer is what tool is best for the job.

And believe me when I say that I know that this isn’t a groundbreaking opinion, but it is mine.  :)

That being said, we all have good reasons for selecting the platforms we work with.  For relational databases, it’s fairly evident that my choice is SQL Server.  Before I get into that, let’s first talk about why I lean towards relational over non-relational.  Don’t get me wrong, non-relational is an effective tool, but it’s still a very young technology.  The platforms for it are still growing and maturing, where they still are missing a lot of the reliability we’ve come to expect from our relational platforms.

Couple that with the nature of relational databases:  Joins, keys, and constraints do more for us than simply organize data, they provide functionality to implement and control business logic.  Data integrity is extremely important for many applications and a proper database design will provide you with all the rules to keep your data clean and ordered.  Just as with choosing non-relational stores, it’s a matter of choosing the appropriate tool for the job.  Sometimes that job requires tight control over your data, something that you just can’t get in a NoSQL database.

As for SQL Server as my relational platform of choice, there’s a lot of reasons why I favor it over other platforms.  It isn’t just because it is worked I’ve worked with (for the record, I’ve put some serious time in Oracle as well).  There are really three main reasons why I will promote SQL Server as the database I think people should work with.

Maturity

Let’s face it, SQL Server has been around for a while and Microsoft has had a lot of time to refine it.  Over the past 15 year I’ve worked with it, I’ve seen the addition of lots of neat features that enhance the core RDBMS offering.  At the same time, SQL Server is still a solid relational database and gives users a solid, reliable platform for storing their data.  It’s not perfect and I’ll be the last person to tell you it is, but it certainly is on par with Oracle and PostgreSQL.

Adaptability

Microsoft has seen the writing on the wall.  Some of it is from their own hand, some of it is how the data world is evolving.  However, “the cloud”, in memory structures, and big data are ubiquitous in today’s tech landscape.  Looking at the recent version of SQL Server, it’s apparent that Microsoft is trying to mold the product to live in this new realm.  Consider Hekaton, the ability to span databases between Azure and on-premise, and improvements to columnstore (along with other updates). Microsoft is making investments to keep pace with the changes we’re seeing in the larger technology world and I appreciate the vision they have for the product.

Accessibility

This is the big one for me.  The other two basically tell me that, in going with SQL Server, I’m going to have an established RDBMS platform I can rely on along with Microsoft continuing to improve things to keep pace with other products.  What sets SQL Server apart is the fact that it’s so much easier to work with, for both new folks and seasoned professionals.

First, let’s look at the fact that it’s Windows.  Now while we all lament SQL Server’s default settings, the fact is that almost anyone with minimal experience can get their own SQL Server instance up and running in short order.  This means that the door is open a little wider for people wanting to get into the database world than those who don’t have supporting skill sets for Linux or hacking the registry.  SQL Server ships with wizards and graphical tools to get folks going.  Just make sure you talk to a professional before getting to far.  :)

And that’s the second thing to talk about.  Maybe I’m biased because I’ve been involved in the SQL Server community for so long, but I’m continually amazed by the amount of free material for training and best practices provided by this community, from blogs to Twitter to a user group likely nearby where you can ask questions of people using SQL Server in your area.  It’s so easy to get started with SQL Server.

Yeah, I know I sound like a fanboy at this point (squee!).  Just so we’re on the level, I am well aware of SQL Server’s flaws.  There’s a lot of things that aren’t perfect or were added a couple versions ago but were never finished up (*cough* Management Data Warehouse).  And let’s not get into what’s in Standard Edition versus Enterprise.  Trust me, I get it.  Even with that, though, I feel that SQL Server is the preferred offering at this point for companies that are looking for a solid relational platform. 

Speaking Out

Every January, many talk about what they want to accomplish in the New Year.  Goal, resolutions, attempts to improve both personally and professionally.  Within the community, a lot of my friends have set goals for public speaking, aiming to talk at user group meetings, presenting to their peers at their jobs, or larger aspirations.

Time and again, we hear the refrain about how presenting can boost your career.  I know I’ve spoken about it myself on a number of occasions.  The problem for most is their first presentation and how daunting it can be.  Sometimes someone’s not sure where they could first chance to speak.  Other times it’s a question of finding the “right” topic to speak on.  Not surprisingly, I’ve had a number of conversations in the past months with community members who are grappling with these issues.  The desire is there, but they need a little guidance in order to start down the path.

Finding the audience

I think the issue of finding a venue is the easier problem to handle.  Over the past two years I’ve become more involved with the Professional Association for SQL Server and I’ve gotten to know many of the local and regional organizers.  Recently, I’m became one of those people as well, joining the board of the Denver SQL Server group this past January.  Over this time, I’ve learned that your local user groups are always looking for speakers and typically have several different ways to help new speakers get started.

For example, we have three established groups here in Colorado, with fourth one getting started.  The three established groups all provide the same general format for speaking slots:

  • A 30 minute “lead off” slot, where the meeting will usually have someone speaking on an introductory topic
  • A 60 minute “main event” slot, typically featuring a local or national name on a more in-depth topic.

When I started my own presentation path, I got my feet wet with the 30 minute intro slot.  60 minutes is a bit much to take in for a first presentation, both for assembling material and also for the intimidation of talking for a full hour.  Also, when it’s the first of two presentations in the evening, it takes some of the pressure off because there will be someone else speaking after you.

I also know that many local groups are looking at other options with their formats in order to promote new speakers.  With the success of lightening talks at the Summit, many user groups have been talking about implementing that format within their own meetings as a way to give new speakers an even easier way to get started.  For those unfamiliar with the format, several short presentations (8-10 minutes each) are lined up next to each other.  Topics are fairly limited, as there’s only so much material you can cover.  In Denver, we’re planning on using this format to open our March meeting and having only new speakers

Speaking….in the Cloud?

Unfortunately, local user groups only meet once a month and aren’t always convenient for everyone.  The good news is there are other speaking opportunities outside of these meetings for new presenters to make use of, found in the PASS Virtual Chapters.  There are many of these groups built around various areas of interest within SQL Server and they’re always looking for speakers.  The great thing about these meetings is that they’re held online, so that many of the scheduling and possible travelling difficulties are avoided.

I personally had the opportunity to present to two virtual chapters last year and they were great experiences.  It took a little while to get used to the limited audience interaction, but it also meant that I was a little more control of the flow of things.  For new presenters who may be intimidated by the audience, this is a great in between step.  Also, you’ll have a meeting moderator who can assist you getting things going, which helps expand the comfort zone because you basically have someone backing you up.

We always talk about the Cloud and how it will change our careers.  This is yet another way that it’s impacting us.  Through virtual chapters, we have even more opportunities to present and reach a larger audience.  Certainly, we hear every day how many of the top consultants are reaching out to the community through free training and it’s easy to observe the success they’re having.  There’s no reason new speakers can’t have the same success with these very same tools.

Yeah, I’m Region Wide

If you’re involved the community, it’s hard at this point to have not heard about SQL Saturday.  I love these events and I’ve been very pleased to see the explosion in the number of SQL Saturdays over the past year.  One of the reasons these events were started was to grow the SQL Server speaker base and, by necessity of the sheer number of these mini-conferences, they are continually in need of new speakers.

While it may be a little intimidating to start speaking at one of these events, the benefits are amazing.  Even if you have had a chance to speak once or twice already, it can’t be understated how important it is to speak at one of these, even if you have to drive a couple hours or plan a quick weekend getaway.  It’s not just about the opportunity to speak, but also to network.  While attendees get a chance to meet local SQL Server professionals, speakers have a chance to talk with regional and national speakers that are also in town for the event.  For example, if you were speaking at SQL Saturday Albuquerque, you’d have a chance to chat with Aaron Bertrand, Steve Jones, and Denny Cherry.

Keeping it in house

Lastly, the easiest place to present could be no further than your workplace.  Presenting within your company has several advantages, the biggest being that you are probably already familiar with your audience.  Also, you can probably have an easier time scheduling your presentation, which becomes more convenient for you.  Overall, presenting to your co-workers provides you a more comfortable experience, which might be an easier first step if you’re not sure about getting up in front of a bunch of strangers.

The Longest Talk

Whether you speak at a user group meeting, online, or to your team at work, you have plenty of options for a venue.  “I don’t know where I could speak” is not an excuse that’s available to any SQL professional.  I used this excuse for a while, but then when I spoke with my local speaker wrangler for the Denver SQL User Group, it committed me.  Suddenly, I had a time and a place where I had to speak and I couldn’t back out.  Well, I could, but what would that say?  We’re in the tech world because we love challenges, we take on new problems, and we push ourselves outside of our comfort zones.  This is just another challenge, so grasp it and help your career go further.

Next week, come back and I’ll provide some additional information on the second hurdle:  How to choose a topic.

A Look Back

The end of 2012 has arrived, despite the best efforts of the Mayan calendar.  It’s been quite a year for me, with a lot of ups and some downs, but overall pretty pleased with where I am regarding my career progress and community involvement.  For those of you who were around this time last year, I had laid out a series of goals for this year.  In order to keep myself honest, I thought it would be appropriate to hold myself to those goals.

Adjectives

My first goal was to get people to associate me with several adjectives.  For review, these adjectives are:

  • Smart
  • Creative
  • Reliable
  • Professional

Considering a lot of the comments I get from friends and colleagues, I’m pretty happy with my progress here.  I’ve had co-workers tell me that I’m looked to for creative approaches to solving problems, both using current and new aspects of SQL Server.  Many people, both in and out of my job, look to me for help and advice.

My biggest struggle, though, is still being “professional”.  I wear my emotions on my sleeve and sometimes that gets away from me, particularly when there’s friction between me and others I work with.  I need more focus in this area, but I also need to keep the other adjectives on my mind.  I’m on the right track here and need to keep moving forward.

Speaking

This is where I had my biggest accomplishments.  My goal was to speak at 4 SQL Saturdays and SQL Rally and, fortunately, I managed to do all that plus an additional SQL Saturday, several virtual chapter meetings, and a handful of user group presentations.  All of these went very well and I got plenty of great feedback.

What I’m most proud of was my SQL Rally presentation: Eating the Elephant – Understanding and Implementing SQL Server Partitioning.  I had a full room with 100+ people in attendance and got positive feedback from most of the audience (though I did get one guy saying he found a great place for a $3 Coke).  I was nervous right before I started, but once I was “on”, things went very smoothly.  I really want to thank folks out there in the community for their support, because without them I wouldn’t have gotten so far.  It can only go up from here.

Focus

I’ve only had moderate success with getting focused here.  Certainly, I have learned a lot more about automation and monitoring in SQL Server.  In reviewing my blog posts over the year, I’m definitely happy with what I’ve written on regarding this.  But I could have done more.  I think my biggest disappointment is how I didn’t blog nearly as much on this topic as I could have.  I have more ideas on my head and I need to be more disciplined about putting them to “paper”.

Summing it up

Self-evaluation is a huge part of personal growth.  We need to set measurable goals for ourselves and then periodically check where we are against those goals.  If I were to grade myself for 2012, I’d probably rate a B.  I should have blogged more, but made great progress with speaking and self-learning.  I have definitely have stretched myself professionally and feel positioned myself well to take things further.

This is an ongoing process, though.  Doing well in 2012 only means I need to stay on track and set goals for the next year.  There’s been a lot of thoughts floating around in my head over the past few weeks, trying to sort out what is both reasonable and measurable for 2013.  I should be ready to commit to those goals and share them with folks next week.

T-SQL Tuesday #36 (#tsql2sday) – Coolness factor

“Come in, sit down, I want to show you something cool.”

This (paraphrased) is what Kevin Kline(@kekline) shared as part of the Summit 2012 Women In Technology panel.  While the session was intended to talk about how we can bring more women in to technology careers, it really struck me as a fantastic descriptor of the SQL Server community in general.  Many fields and groups invite people in, but there’s some “thing” about the SQL Server community that makes it the most open and supportive technology community I’ve ever had the pleasure to participate in, and I think it’s this desire to share that sits at the core of our success.

The Summit is built on this sharing mindset.  From the speakers to the board members to the participants, the people who put this on are volunteers.   Hundreds of professionals hope to be selected to speak at this event, even if just for a lightening talk, because they have something cool to share.  People hang out in the halls just to talk about what can help the next guy out.  What’s especially noticeable is how the community reaches out to the first timers.  I know some folks were a little adverse to wearing those ribbons, but if you had a first timer ribbon, alumni of the convention would reach out and make sure you were welcome.

It seems that typically, people hoard knowledge as a means of job security and a way to emphasize their superiority over others.  With SQL Server, it’s more that we want others to have the fun we’re having.  We work in tech because it’s fun and cool and share our knowledge so others can see the cool stuff we do.  Because when all is said and done, if we’re all having fun, we’re all going further.

So come in, sit down, and let me show you something cool.

(This month’s T-SQL Tuesday is brought to you by Chris Yates(@YatesSQL).

T-SQL Tuesday #33 Wrap-up (#tsql2sday)

Last week I had the opportunity to host this month’s SQL blog party, T-SQL Tuesday.  I hope folks enjoyed the topic (it seemed like it), because the idea of experimenting with something is very much a core part of the learning process.  SQL Server is so deep and has a number of nooks and crannies.  To become proficient at it (let alone an “expert”), we have to keep pushing our boundaries and asking “What happens when….?”

I want to again thank everyone who participated.  Here’s a quick round up of the posts:

Rob Farley(b|t) peels back the cover on recursive Common Table Expressions (CTEs).  If you haven’t worked with them yet, CTEs are an incredibly useful tool in the SQL arsenal.  Using them recursively is just one example of how powerful they can be to a developer.

Jeffrey Verheul (b|t) chimes in with a big picture discussion on using cursors versus set based operations.  While we database folks prefer set based operations, sometimes it’s to our advantage to use a cursor.

Jim McLeod (b|t) jumps in with a great little way to use SQL Profiler to gain some insight into the workings of SQL Server by tracing Management Studio.  Not a lot of people are aware that almost everything that happens in Management Studio is a query that can be scripted out, and profiler lets us see that.

Oliver Asmus (b|t) shares some of his knowledge gained from putting together preventative maintenance tasks (a vital yet often overlooked part of being a DBA).

Aaron Bertrand (b|t) provides a cool trick to swap tables around using schemas.  A lot of SQL DBAs overlook the power of schemas in databases, but Aaron’s trick really gives as an efficient way to handle offline updates and other resource intensive tasks.

Nigel Sammy (b|t) pokes at the Transaction log with DBCC SQLPERF.  It’s important for DBAs to really understand their transaction log usage, and Nigel’s trick can give a DBA some real insight.

Tracy McKibben (b|t) gives us a trick using one of my favorite system views, sys.partitions.  There are times I lament not being able to see how long a query or process will take (I’d LOVE to have something like Oracle’s V$SESSION_LONGOPS view), but Tracy shows us how to use sys.partitions for insight into index rebuilds.

Chris Shaw (b|t) gives us some information on cluster failovers. While we want our operations to appear seamless to our users, as DBAs it’s important that we’re always aware of where our instances are living within our environment.

Rob Volk (b|t) really gets down the rabbit hole, using one of the Sysinternals tools to crack open SQL Server.  I’ve seen some folks do some really cool and powerful stuff with Sysinternals, so Rob’s post is a great way to introduce people to this awesome tool suite.  (Plus, how many guys even get to use the word “hekaton”?!?)

Jennifer Salvo (b|t) adds a little business intelligence to our topic, giving us a cool SSAS trick to manage currency conversions.  Businesses will ALWAYS care about their money, so having a good way to manage multiple currencies is a must.

Finally, first timer Jose Rivera (b|t) brings us full circle with his own post on using recursive CTEs to solve a tricky problem for drug costs.

(Oh, and look at this post if you want to see my contribution.  :D  )

I really appreciate everyone’s participation.  This is such a great part of the SQL Family and I hope that if you’re reading this you’ll consider participating and, perhaps, hosting a T-SQL Tuesday in the future.

T-SQL Tuesday #32 – A Day in the Life (#tsql2sday)

This month’s T-SQL Tuesday is brought to us by one of the more statistically important data professionals out there, Erin Stellato(b|t).  The assignment is simple enough: Record your day on either Wednesday 7-11 or Thursday 7-12.  Easy enough, but leave it to me to screw it up, right?  Anyway, I was travelling on Thursday (heading down to Albuquerque to present at the local 505 user group), so I cheated and recorded my activities for Tuesday, 7-10.  It was an average enough day, so a good cross section for the series.  So, without further adieu:

  • 6:55 AM – 7:10 AM Check on I/O trace – I can work remotely, so commonly when I get up I’ll check in on things just to make sure everything is ok.   This time, I had set a profiler trace to run overnight to give me some info on I/O issues we were having.  All I did here was log in and make sure it ran, I was going to drill in to the detail later.
  • 8:15 AM – 8:30 AM Review alerts from previous night – Still at home, but I did a quick glance over the alerts from last night just to make sure there weren’t any fires.  Everything was cool, so I hit the road to get in to the office.
  • 9:00 AM – 9:20 AM Arrive in the office, get my day started – This is administrative time, responding to emails and getting my tea (I hate coffee.  There.), settling in for the day.  This bleeds into the next part….
  • 9:20 AM – 9:40 AM General maintenance work – This was basically some file clean up and responding to some of the alerts I saw from over night.  Nothing major.
  • 9:40 AM – 10:40 AM I/O research – So we’re having an I/O issue in our lower environments.  We’ve got a LUN on one of our instances that is getting slammed.  This was what I was using my trace to research and discovered that a whole lot of work was going through tempdb.  I spent this time reviewing my data and then talking with the relevant developers and QA engineers.  Once I had my info collected, I reported out to the systems team, DBA team, and the dev guys.  Unfortunately, this is a situation where not much can be done.  There really wasn’t any alternatives for spreading out the I/O load (at least none worth pursuing for a lower environment system) and the proper way to fix it was to have the dev team file things away for code fixes.  Still, with the info I collected we could come back to that with a better strategy.
  • 10:40 AM – 11:00 AM TempDB cleanup – Got some additional space for one of our dev instances to allow us more tempdb space, so I cleaned that up and arranged the files.
  • 11:00 AM – 12:00 PM CLR Research – So I’ve never really done much CLR work.  We had a legacy sproc that we used that was reporting incorrectly, so I was doing some research as to why. Really didn’t have much luck, but since I was used to the WMI in Powershell, I figured I’d try and rewrite the CLR logic using that.
  • 12:00 PM – 1:00 PM Lunch – Every Tuesday we go to this awesome thai place down the road.  Basil chicken for the win!
  • 1:00 PM – 5:00 PM CLR Research – I basically spent the rest of my day fighting with CLR.  Keep in mind, I’m a DBA with a sys admin background.  I’ve dabbled in .Net code, but I’m very rusty and my code is less than elegant.  However, it was a good learning experience, and taught me several things:
    1. CLR only supports a limited set of libraries.
    2. The System.Management libraries apparently have a lot of dependencies on forms and drawing (I have no idea why).
    3. CLR is a real pain to debug, depending on local security policies.

Honestly, this was one of my lighter days.  Probably because we had just come out of a holiday week where we had locked systems down and allowed minimal change, meaning we also did have much breaking.  This is what makes the job enjoyable: not every day is a fire drill and the ones that aren’t afford me an opportunity to experiment and learn.  Because of this day, I’m a whole lot more comfortable with the concepts of CLR (even though I still haven’t built a successful CLR function) and it’s made me a stronger DBA.

Thanks to Erin for hosting T-SQL Tuesday #32!   Make sure you visit the master post for other great blogs!

T-SQL Tuesday #29 2012 Party: Dancing on the FileTable (#tsql2sday)

For this month’s edition of T-SQL Tuesday, Nigel Sammy(b|t) asks us to talk a bit about a feature of SQL 2012 that we’re excited about. Now, while my most favoritist SQL 2012 feature is the ability to install on Server Core, I’ve already written about that and I want to talk about something that hasn’t gotten quite so much publicity. Enter the File Table, new functionality that exposes more of the Windows file system to SQL Server. With it, we get some additional tools for file handling that before we had to do with other applications and workarounds like xp_cmdshell.

A File Table is a special type of table in SQL 2012. It’s got a fixed set of columns and points at a directory managed by SQL’s FILESTREAM technology. If you do a SELECT from the File Table, you’ll get a listing of all the files within that directory. What’s cool is that “inserting” rows into this table is as copying files into the directory. Once a file is in there, we can use use SQL to do other operations, like copying files between two different File Tables, or cleaning up old files based on the file dates or archive flags.

Setting up a File Table doesn’t take much, but since it uses FILESTREAM it can be a little tricky. The first thing you need to do is actually enable FILESTREAM for your SQL Server instance. To do so, just follow the steps from MSDN. For the purposes of this, I’m going to set up my share name as my instance name, SQL2012. This means that to get to my FILESTREAM objects and my File Tables, I’m going to be using the UNC path of \\SHION\SQL2012 for my laptop.

Now that we’ve got FILESTREAM going, we can go ahead and create our File Table. The first step is to set our database options to allow non-transactional access and a default virtual directory for the FILESTREAM.

--Set database specific options for FILESTREAM
ALTER DATABASE demoFileStream
SET FILESTREAM (non_transacted_access=FULL,directory_name=N'FSDemo')

Next, we need to add a FILESTREAM filegroup to the database, which defines the physical directory where our file tables will exist. Note, this directory will be a physical directory, but SQL Server must create it so it cannot already exist.

--Filegroup for the filestream
ALTER DATABASE demoFileStream
ADD FILEGROUP fgFileStream CONTAINS FILESTREAM

--Directory for the filestream filegroup
ALTER DATABASE demoFileStream
ADD FILE (NAME=flFileStream,FILENAME=N'C:\FileStream')
TO FILEGROUP fgFileStream

Finally, we can now create our file table with a simple CREATE TABLE statement. Note, we won’t declare any columns as those are pre-defined for the file table structure.

CREATE TABLE FT_Test
AS FILETABLE

And we’re done! We can now browse to the following UNC filepath: \\SHION\sql2012\FSDemo\FT_Test. This directory is the physical location of our file table. For now, I’ll put a couple test files in there.

At this point, we’ll just run a simple select and, bam! There’s a listing of our files.

SELECT
  name,
  file_type,
  creation_time,
  last_write_time
FROM FT_TEST


Voila! A happy little file table, ready for our use. I’ll be honest, there’s some limited use here and having file tables doesn’t solve a lot of problems. It’s very useful, though, for certain use cases and can be a very powerful tool in your tool kit. If you’re trying to think of situations where you need it, consider times where you need to work with files on the file system:

  • Managing backup files: If you’re like me, you write your backups to a unique filename for each backup. This means I usually need some other tool to clean up my old files. With File Tables, I can manage that clean up completely within SQL.
  • Restores: If you’ve got a lot of transaction logs to restore and you want an easy way to parse through files to find the ones you need, you can now do that using File Tables.
  • Basic ETL: If you need a simple ETL solution, File Tables can give you some additional leverage so you can keep your entire solution within SQL.

Thanks again to Nigel Sammy for hosting this month’s T-SQL Tuesday. There’s so much new with SQL 2012, it’s good for Nigel to give the community a chance to share some of the new features that are now out. Enjoy the rest of T-SQL Tuesday #29!

So about that time management…

Yesterday I read an interesting post by the inimitable Brent Ozar (b|t) on time management.  Good stuff, much of which I already do or try to do.  Shortly after reading it, I had a brief Twitter exchange with Brent and Steve Jones(b|t), essentially on whether or not these were just rules for consultants or if they worked for us corporate folks as well.  Personally, it’s been my experience that not only can you follow these rules, but that you really should if you don’t want to burn out and still accomplish your goals.

First off, if you haven’t read Brent’s post, go read it.  I’ll wait.

<Time passes>

Cool, now that you have context, I want to basically give my thoughts on each of the rules and how I put them into practice in my own work day.

#1 Decide you want to be incredible – One of my favorite tweets from @FAKEGRIMLOCK is “BE SELF.  ITERATE UNTIL AWESOME.”  The first rule is really that simple.  You don’t have to change who you are, you don’t have tap into some hidden fount of knowledge…you just need to be yourself, awesomely.  This is something that can be done in a corporate job as easily as anywhere.

#2 Never budget less than whole day increments of time – Let’s not take this one quite so literally.  What I take from this is to devote blocks of time to my tasks and reduce distractions.  Whether it’s 4 hours or 2 or 8 (if I can get away with it), I use that block ONLY for the designated task.  Don’t multi-task, because it becomes harder to focus and get things done.  The key is to reduce/eliminate distractions.

Distractions come in many forms:  Meetings, on-call pages, emergency 20 hour conference bridges, people coming by your cube to chat, etc.  We can’t plan for them and they are probably the biggest threat to getting stuff done.  This is the balancing act, the biggest challenge for time management.  I’m actually going to steal some NoSQL terminology to outline how I approach this:

  • Map – Map out my time, schedule my work.  If I’ve got 2 hours, I plan to put 2 hours into a project.  Don’t work on anything else.  Keep in mind, sometimes the work is planning out your other work.
  • Reduce – Do work that reduces your other work.  Can you build better maintenance for your systems?  Can you communicate with people outside of meetings?  Take control of your time and put effort into removing the distractions that could disrupt you.

#3 Leave one whole day per week to do absolutely nothing – For me, this translates to one word:  disconnect.  We live in an a world and a culture where it’s easy to always be working, where we’re checking our email constantly, looking at alerts, responding to internal customers, monitoring our servers.  Why?  The world and the company will go on without you for a while, take a break for yourself when you need to.

IT has a reputation for burning people out, but I think that’s because people let the job control them instead of the other way around.  Take a break, breathe, and do something else.  Life is too short, you need to remember that we work so that we can do the stuff we enjoy.  Fortunately for many of us, we enjoy our work, but that can’t be the entire definition of who we are.  And if you’re working a job where your boss expects that?  Time to get a new job.  There are companies out there that respect the work/balance, go work for them.  You’ll feel better for it.

#4 Leave one more solid day to pounce on incredible opportunities – Not much else to say here, though I would add that incredible opportunities can happen inside your company, they don’t just come from without.  Also, some opportunities are self-generated.  An example from my work:  My job doesn’t have any formal SLA’s defined for the database team.  I saw the need and am driving that project.  My bosses and co-workers are taking note of my initiative.  Sometimes pouncing on an opportunity is simply being a leader.

#5 If the incredible opportunity runs more than a few weeks, it’s work – Maintain your balance.  When you take on additional projects or tasks, make sure they fit into the overall scheme that is your work/life pie chart.

#6 Say no early and forcefully to everything else – Much has been written about the necessity of the 40 hour work week.  Since we can’t do everything, at some point we need to be able to say “no”.  This is the hardest piece, especially in a corporate environment where you have a team that depends on you or management you want to impress.  However, you’re only human and sometimes work just isn’t going to get done.  This actually loops back to #3, because if we take on more than we’re capable of doing, everything else suffers.

Let’s not confuse this with neglecting what the company requires of us.  There are times we’re going to need to work on things we may not like (stupid SSRS).  What we need, as corporate employees, is the freedom to cry “uncle!” when there’s too much on our plates.  Sometimes it’s having a teammate take on that extra project, others it’s pushing out deadlines.  Really, all we’re trying to manage here is our overall workload so we’re not trying to do too much.

And if you’re working a job that won’t let you say “no”, that doesn’t trust you enough to accept a “no”, then maybe it’s time for a new job.  Just sayin’.

I think Brent’s rules are a great and hopefully this puts a little more perspective around them.  While my personal approach is worded differently, I think the main principles are the same.  What’s important is that there’s no reason why you can’t follow these rules in a corporate world, that these aren’t just for consultants.  Sure, you won’t follow them to the letter, but that’s not the point.  The goal is that too often we let our time manage us when it should be the other way around.  Turn that corner, take control, and you’ll find everything changes for the better.

(Yeah, cheesy end, but sometimes it’s like that.  Thanks for reading!)

T-SQL Tuesday #28 (#tsql2sday): Jacks and Aces

DBA. I’ll be honest, there are times I *hate* that title. People toss it around without really understanding what it means. Heck, how many of us specialize in backups, monitoring, and high availability solutions only to get a call from a recruiter who has an “immediate need” for an expert in SSRS and cube design. Unfortunately, this is a direct result of non-database folks not really understanding what we do. They simply see “database” on our resume and figure that if a database is involved, we know how to handle it.

We’re often considered a Jack-of-all-trades, the IT handyman. This has evolved from the roles we have to play in our careers and how so many of “fell” into a database career. The rule of thumb is that the smaller the shop, the greater our breadth of knowledge needs to be. While I’ve been a SQL Server administrator for over ten years now, my job responsibilities have required me to learn something about networking, Windows server administration, .Net development, report writing, and Oracle administration (amongst many, many other topics). Because so many things touch a database, we’re expected to understand all of these different pieces as well as the database itself.

The problem with all this is that it’s more than any one person can really know. With SQL Server becoming more and more complex (a good thing, by the way), it’s hard enough for us to keep up with that platform alone. Since it’s rarely an option to tell our boss “No, I can’t do that”, there’s a couple things that I’ve found help me stay ahead of the game:

  • Building your personal network. This is more than just the SQL Server community (fantastic as it is). Sure, attending user group meetings has helped me find experts in areas of SQL Server I’m not as knowledgeable in. But you’ll need more than that. I have Oracle DBA friends, hardware geek friends, sysadmin friends…..you get the idea. By building out this network, I always have someone I can go to if I’m out of my depth.
  • Stay educated. Sure, we spend a lot of time learning about SQL Server, but remember why we get into this business in the first place. Technology is cool, so keep learning about it. Pay attention to trends and tech so that when your boss comes to you about something, you won’t be caught by surprise. And if you *are* surprised, don’t sweat it, but make some time that day to read up on whatever they were talking about.
  • Write it down. When you do something, document it! So many people bemoan documentation, but the cold hard truth is you’re going to forget something when your focus gets completely shifted the next day. Being a small shop DBA means you’re going to get bounced around on a lot of different things, so you need to record what you do so that you don’t have to relearn it later.

Being a small shop DBA can be a tough gig, but it’s where most of us cut our teeth. Hindsight being 20/20, there’s a lot I’d do differently. Fortunately, Argenis Fernandiz (b|t) has given us this great T-SQL Tuesday topic for us to share with our SQL family and help others learn from our experiences.

The biggest lesson I would take out of all of this is that, while our job requires us to generalize most of the time, we can really only advance our careers when we specialize. If you’re simply treading water at your current job, pick some part of the SQL Server platform that interests you and focus on it. Once you start becoming an expert at something, opportunities will open up for you along with more interesting work and learning. That will start you along the path of moving beyond being a jack of all trades and becoming an ace in the database deck.