Art of the DBA Rotating Header Image

community

The 2018 SQL Backup Survey

Last year I joined on to Rubrik to help them sell their SQL Server database product. It’s been a fun challenge and definitely interesting working in the vendor space instead of having to field 3AM calls because something broke. This means that I talk to a lot of people about SQL backups and restores, especially about the challenges that your average data professional runs into. However, I based a lot of this conversation off of my anecdotal experience as a DBA. Being a data person, it felt a little awkward talking about these things in the absence of data.

This leads me to last week. In order to have some data, I decided to run an informal backup survey targeted at the SQL community. The results floored me: 344 of you decided to take my short survey. This really helps me understand some of the trends out there and now I want to share those results with you.

Before I get started, I want to first thank each and every person who responded from the bottom of my heart. This data is the result of your participation. Secondly, I want to underscore the “informal” nature of this. There’s a lot of holes that can probably be poked in the process, but I think the data is still useful and can give people insight into the trends.

I’ve posted raw data along with a few tools out on GitHub, where you are welcome to download and play with it. The tools include:

  • Two SQL scripts that create and process the data into a simple data warehouse schema.
  • An SSIS package (created by the SQL import wizard) that loads the raw data into the staging table
  • A PowerBI report that has a couple basic charts built off of the data.
  • A SQL 2016 SP1 backup of the database where I processed the .csv.

Note, my data warehousing/PowerBI skill set is nowhere as strong as others. I built something that was familiar to me and let me get at the data. I’m sure someone can build something better. Which is why I’ve made it public.

The Environment

As far as parsing out the data, let’s look at the questions and how people responded. The first questions were around how many SQL Servers(instances) and databases most people manage:

I think what’s interesting here is that the vast majority of folks out there manage a LOT of servers (36% have 100+ servers). Contrast this to a more balanced distribution across the database counts. This becomes even more interesting when contrasted against the data volumes:

The majority of shops out there manage less than 25TB of total data, with most of these databases clocking in at 5TB or less. While 5TB is still a lot of data, this does mean that (when comparing this to the numbers above) most of the respondents manage “wide” environments, with more databases that are smaller in size. This becomes even more interesting when looking at the size of DBA teams:

As you can see, the VAST majority (80%+) of companies employ teams of 5 DBAs or less. This means that in these wider environments, DBAs are responsible for a lot of objects. To me, this means that we have a lot of touchpoints to manage from a data protection standpoint.

SQL Use

Other interesting tidbits filter in with the questions on how SQL is used. Starting with just the versions of SQL in use, it’s not really surprising that Microsoft’s mainstream supported versions are in use out there:

I was a little surprised at the SQL 2000/2005 number, but not shocked. I know how hard it is to phase out some of those legacy platforms.

Nothing really surprising when it comes to feature use either:

I did hope to see compression higher because of the performance impacts it has, but the rest of these values file in line with my expectations.

When it comes the High Availability/Disaster Recovery options, we also shouldn’t be too surprised:

Availability Groups have been a strong offering from Microsoft and something they’ve marketed hard. This has resulted in solid adoption, but it’s only a little higher than Failover Cluster instances. I’m also not surprised by the Log Shipping use, because let’s be honest. It just works.

The final piece of data around backup tooling is of significant importance to me:

Almost 35% of you use some sort of community scripts, over 15% more than the next entry. This is significant because it means that most shops out there are relying on code that the SQL community provides for free. Think about that in context of other software platforms. Most are going to rely on purchased tools, but so many of us are comfortable with these community scripts that we entrust our company’s most important asset to them.

I was a little surprised at how high Enterprise Platform usage was, considering how many DBAs take a dim view of them. I think what also surprised me was that Vendor Software (Idera, Red Gate, etc.) was so low. Overall, we can definitely see where DBAs find value for running and managing their backups.

The Pain

The last question was more of a free form entry around what’s bugging people when managing backups. I’ll let folks browse it on their own, but here’s what I found interesting:

  • ~21% of the responses referenced concerns about having enough space for their backups.
  • Several respondents were frustrated by needing to build out reporting/management solutions.
  • There were many concerns about having access to backups or being able to restore them in an emergency (mostly due to lack of space to either store enough backups or test them).

Wrapping It All Up

There’s probably a lot of different possible conclusions that can be leapt to from this data, certainly outside the ones I made. I’m certainly not claiming this survey as anything definitive, merely some interesting observations made from a healthy community response. Hopefully you also find the data interesting and I hope it sheds some light on to what’s happening out there in the wide world of SQL Server.

Before I go, a couple shout outs:

  • Meagan Longoria (@mmarie) for telling me how and why my data visualizations sucked. Seriously, though, don’t hold what you find in the PowerBI report against her. :)
  • Chris Lumnah (@clumnah) for helping me refine the survey questions.
  • All the folks who shared the survey link, helping me get to 344 responses.
  • The 344 of you who DID respond, helping make this survey into something worthwhile

Defending Invoke-SqlCmd

Twitter. It can be annoying, it can be frivolous, but you’d be surprised at how many times it gives me a blog topic. This time around, I was having a back and forth with some folks about Invoke-SqlCmd. I had recommended someone try using it, which was followed by a lot of people griping about the cmdlet while suggesting other community tools or scripts should be used.

While I understand where a lot of these folks come from, I wanted to share a little, in a longer form, about why I use and recommend Invoke-SqlCmd. I think it still gets a bad rap from the PowerShell power users for some of its…quirks. I want to stand up for this handy little cmdlet, which I think is likely the most useful part of the SqlServer PowerShell module.

The .NET Root

When we start talking about Invoke-SqlCmd, what are we trying to do? At the core, PowerShell scripters need something to execute SQL commands within the context of a .ps1 script. I often speak of how PowerShell is a framework where you can tie different parts of the Windows stack together, so this ability to run SQL is needed.

One of the strongest elements of PowerShell is that it is built on .NET, which means we can always use .NET code and objects. In the case of SQL, we can use traditional ADO .NET code to execute SQL against a SQL Server instance:

$Datatable = New-Object System.Data.DataTable    
$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString = "server=localhost;database=AdventureWorks2012;trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = 'select top 10 BusinessEntityID,PersonType,FirstName,LastName from person.Person'

$DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $Command
$Dataset = new-object System.Data.Dataset
$DataAdapter.Fill($Dataset)
$Connection.Close()
$Dataset.Tables[0]

As you can see, this is pretty verbose.  We have to create a connection object and set its command string, then open the connection, execute the SQL, populate the data set, close the connection, then finally return the data set. No matter how you slice it, this is a lot of code for a simple SELECT statement.

SMOing it up

To avoid this verbosity, most programmers wouldn’t rely on the raw .NET unless they absolutely needed to. Instead, they would use existing libraries (or write their own if they had to). This is where the SQL Server Management Objects (SMO) come into play. I’ll skip the long boring explanation of what SMO is and just focus on the fact that we can use these existing libraries to simplify our call:

$smosrv = New-Object Microsoft.SqlServer.Management.Smo.Server
$query = 'select top 10 BusinessEntityID,PersonType,FirstName,LastName from person.Person'
$results = $smosrv.Databases['AdventureWorks2012'].ExecuteWithResults($query)
$results.Tables[0]

The SMO lets us reduce twelve lines of code (ignoring whitespace) to four for our SELECT output. This is the power of using existing libraries. We don’t have to reinvent the wheel for our work,which means we can focus more of our effort on everything around the SQL statement.

But what if we could make it even easier?

Keep It Simple, Sir (or Ma’am)

Three lines of code isn’t that much. The problem is that our code is a little less intuitive. Let’s keep in mind that most PowerShell scripters are not .NET programmers. Understanding dot notation isn’t necessarily difficult, but if it doesn’t come naturally to you there’s a learning curve that you have to climb.

This is where Invoke-SqlCmd starts to make sense. First off, it wraps up all the functionality of the above .NET code so we can use it with the cmdlet call. Also, it follows the founding concepts of the PowerShell language, where the syntax is verbose enough to understand what you’re passing as arguments. Let’s look at our simple SELECT now using Invoke-SqlCmd:

Invoke-Sqlcmd -ServerInstance localhost -Database 'AdventureWorks2012' `
    -Query 'select top 10 BusinessEntityID,PersonType,FirstName,LastName from person.Person'

If we break this down, you can see we have a clear set of syntax for calling the cmdlet. We specify an instance, a database, and a query. The output is an array of data rows, though it could be a datatable if you’re using the most current version. Most scripters are not going to need much more than that. It’s a straightforward solution to a simple problem.

So why not?

Why do people gripe so much about Invoke-SqlCmd then? Well, to understand this is to understand the history of SQLPS. For a long while, the SQL Server module for PowerShell was klunky and buggy. There were a lot of challenges with loading it and using it, such that many scripters decided to throw it out and write their own functions. In many cases, PowerShell folks would skip Invoke-SqlCmd not because it was bad, but because it came packaged with the rest of SQLPS and they wanted to avoid the entire module.

Now that the SQL Tools team has been reworking the module as SqlServer, this has become less of a concern. The module is less of a burden to load and the other components do not get in the way. There are also improvements and updates to the code to make it work better and serve more needs.

There are other reasons as well, but it usually boils down to the use case. Invoke-SqlCmd is great, but it can’t do everything. My anecdotal evidence is that the cmdlet will handle upwards of 90% of what I need it for, but there will still be edge cases. For these edge cases, we always have the more code intensive options available to us. This is why PowerShell is so great, there’s always another way, whether it’s writing more detailed code or using a community function.

To touch on this last point, this is where I also bump up against folks. There are some fantastic community functions out there. Seriously, check out DBATools if you haven’t yet. These tools enhance and expand your PowerShell experience and I make heavy use of these tools. They also have better alternatives that address the quirks of the core SqlServer module.

The rub is that you can’t always rely on community tools being available to you. Sometimes a corporate environment won’t let you install those tools or scripts. It might be that you go into a gig and they haven’t even heard of these tools. I can tell you that as consultant, I have to be very considerate of my client’s environments before I start bringing in outside code. However, because Invoke-SqlCmd is part of Microsoft’s tool set, I can rely on it being there. There are less steps and hurdles to making use of it.

Making Sense Of It All

There are a lot of reasons for using this little cmdlet. There are also reasons not to use it, depending on your situation or use case. My goal here is to not let the baby get thrown out with the proverbial bathwater. Invoke-SqlCmd is one of my favorite cmdlets in my toolbox and (most of the time) it does exactly what I need it to. Whether I’m writing scripts or working adhoc on a server, I usually don’t need a lot of fluff, just a way to execute a SQL query and sometimes get something back.

I hope you enjoyed my little love letter to Invoke-SqlCmd. I understand if it doesn’t suite your needs, but maybe you should give it another look? Or, if you’re a PowerShell newbie, you can see why using it can make your life a little easier. Wherever you go next after this blog post, I hope it is with a clearer view of how Invoke-SqlCmd can fit into your own PowerShell habits.

Be Like Water

“You must be shapeless, formless, like water. When you pour water in a cup, it becomes the cup. When you pour water in a bottle, it becomes the bottle. When you pour water in a teapot, it becomes the teapot. Water can drip and it can crash. Become like water my friend.

Bruce Lee

 

If you look around the internet, you will come across this famous quote. Often cited by arm chair philosophers (myself included), these simple sentences speak on being flexible and adaptable within your life and how being too rigid can limit us. These limits will make it difficult to truly grow and improve ourselves, whether it is our professional or personal lives.

We can break this quote down in many ways, but I’d like to focus on how it impacts technology professionals. One thing I love about working in technology is how often things change. The joy comes from the constant learning we must do to keep pace. If we’re not pushing ourselves to explore and discover, we risk falling behind and losing our way.

Empty Your Mind

If we look at the IT career path through the lens of Lee’s quote, the idea of flexibility becomes obvious. Through my time in the IT industry, I’ve seen the rise of the Internet, the cloud, concepts like Agile development and DevOps, and other changes that range between large and subtle. If I’ve learned anything, it is that defining myself as a DBA, sysadmin, or developer limits what I am willing to do and narrows the opportunities available to me.

When someone asks you to take on a new task, how do you approach it? Do you look at it through the lens of your job role? Or, instead, consider how it relates to your career path? Do you determine what you want to work on based on what you know and are comfortable with, or do you let the opportunities shape your growth?

As technology professionals, it’s important not to limit ourselves. Today’s experiment might be tomorrow’s trend. A hobby we tinker with could easily become our driving passion. We need to be receptive to what’s around us, be formless and fill the demands that are presented to us. Do not close yourself off to challenges because you haven’t done them, but be prepared to know what it takes to fill those vessels.

H Two O

This probably sounds daunting because there’s so much going on in the technology world. Should we be able to do anything? Be ready to build networks, write applications, design databases, and so on? How do we keep up with the overwhelming number of disciplines and developing technologies in our world?

I’ve long held that the “full stack developer” is a myth. We live in a world of specialization and trying to have deep knowledge of all disciplines is impossible. Sure, we can understand something of everything (being a jack of all trades, master of none), but it becomes difficult to bring the full weight of our expertise to bear if we only know a little bit of a lot of things.

At some point in our careers we need to understand what makes up our “water”. This brings me to another long held view of mine: technologies change, but concepts remain the same. The example I like to use is relational databases. Over the years we’ve had platforms like Microsoft SQL Server, Oracle, MySQL, PostGREs…the list goes on and on. Each of these platforms introduces new features every few years, trying to one up each other.

At the core, though, each of these platforms is a relational database. The relational model was codified by E.F. Codd back in 1969. Think about that for a moment. While Microsoft SQL Server is adding new features every few years, the foundational concepts that we all work with are forty seven years old. Almost half a decade of a technological principle that we build our careers on.

I don’t consider myself a SQL Server professional. My skill set is not limited to just SQL Server as a platform, but relational database design and data management. I am strongest with Microsoft’s offering, but I can adapt to another platform because I understand and own the foundation. The core concepts are what I build upon, which is what makes me stronger and more prepared.

Be Like Water

Becoming like water is more than just being adaptable, it’s about understanding what defines you. Job roles and requirements are just vessels we fill with the knowledge and experience we have. If we choose to be like water, we remain fluid and can, at once, fill the needs of a role as well as be more than that.

Be the sum of the things you have learned, not the tasks you can accomplish. My challenge to you for the new year is to build a deeper, more fulfilling career. Flow and adapt, learn and absorb. Empty your mind and be open to the numerous possibilities in front of you.

Be like water, my friend.

T-SQL Tuesday #84: Getting Ready for your Presentation

boy-speech-lettersI’ve been super busy lately, but I wanted to at least post something for this month’s T-SQLTuesday. The topic is about encouraging new speakers, something I’m very passionate about. I think that speaking is one of the best things you can do to boost your career. If you are reading this and are considering speaking, I encourage you to reach out to your local user group and see if you can get started with a 15 or 30 minute session. Take a shot, I’ll bet you’ll be surprised.

What I want to share are some tips for the day you give your first presentation. A lot of folks are going to talk to you about building and preparing your presentation, but that is only half the battle. What you should do when you actually GIVE the presentation is often glossed over, even though this is the most high pressure moment of the whole cycle.

How do we reduce or relieve some of this pressure? Well, let’s start with a list of things that could possibly go wrong when you present. Think about the following list:

  • You’re presenting and you get an on-call page.
  • Your demo blows up spectacularly.
  • While giving your presentation, your computer attempts to apply updates.
  • You start 10 minutes late because you have issues with your video or sound.
  • During your presentation, someone sends you a picture on your favorite IM client:

oh_hai

Any of these will easily throw an experienced presenter off their game. For a new speaker, it can spell disaster. I’ve got a routine that I go through on the day of my presentation, which is designed to reduce that risk of disaster. And who doesn’t like reduced risk?

Getting Ready

Step 1: At the beginning of the day, well before my presentation, I make sure my presentation machine has been updated with Windows and other corporate software. This is SUPER important if it’s a Tuesday (when Microsoft releases updates). Doing this avoids any update surprises while I’m presenting or right before I go on stage.

Step 2: A couple of hours or so before my presentation, I will walk through my presentation. I open up the PowerPoint slide deck and step through it. When I get to demos, I will walk through my demo scripts. I test EVERYTHING, and do it in order. If I encounter an error, fix it, and then start over. This helps me insure that the flow works and that I understand what the step dependencies are in my demo.

Step 3: About an hour before my presentation, I will turn off everything on my presentation machine unnecessary to the presentation. Programs like Skype, Google, unneeded local SQL instances, Virtual Machines….so on and so forth. I only want what I need running to make sure that I have enough resources for my demos, along with keeping possible distractions shut down.

Step 4: At least 15 minutes before I’m due to present, I go to my room and hook up my presentation machine. I test the video and make sure my adapter works. This way I can address any tech issues that could hamper the presentation. I will display PowerPoint and also my scripts and demos to make sure everything looks ok.

I also usually duplicate my screen to the projector. This is important because if I extend, this means the only way (typically) that I can see what’s on my screen is to look back at it. This is distracting for your audience. If you duplicate, you only have to look down at your screen, which maintains contact with the audience.

Step 5: Right before I present, I turn my phone OFF. Then I put it in my bag. I get it away from me. I don’t want to get calls, I don’t want to have to worry about silencing it, and I don’t want it buzzing in my pocket if I’ve got a lot of notifications. The phone is off and away.

It’s GO time

At this point, I’m free and clear to do my presentation. Does that mean that nothing will go wrong? Of course not. However, performing these steps puts me in the best position to give my presentation without disruption. It is a foundation for success. Just like we build out our database solutions to minimize the option of failure, we need to approach our presentations with a similar sort of care to help guarantee our own success.

I want to thank Andy Yun(@sqlbek) for hosting this month’s T-SQL Tuesday. It’s a great topic and I hope folks get a lot out of it. If you’re considering stepping into the speaking world (and you should!), this month’s blog posts should give you all the tools to succeed. Good luck!

Sharing My Summit 2016 Submission Results

Sharing your abstract results for 2016 has become a Thing(TM) that Brent Ozar(@BrentO) started this week. At first I wondered about the value, but as I read others posts it made sense to share because it helps pull the curtain back a little to how the process works for speakers. It also gives us insight into the kind of work the volunteers are doing. As such, I want to share some thoughts on what I’ve seen and how it can be improved.

First off, let’s get the fine print out of the way:

  • Thank you to Allen White(@sqlrunr) and the speaker selection team for managing this cat herd. It’s a tough job, but it shouldn’t be thankless. We appreciate your hard work.
  • Thanks to the volunteers who gave of their time to read and offer feedback on the abstracts. While I have some thoughts on the nature of the feedback, I recognize and appreciate the time it took to write those comments.

Let’s go over the general thoughts. I’ve been deeply intrigued by the speaker selection process over the past five years. With each year, the speakers who submit get better feedback and the process is improving with each iteration. Just like the software we work on, it still has “bugs”, but I think we all need to recognize the strides this process makes and how PASS is doing its best to balance all the factors to create an annual Summit with a variety of voices and a solid conference offering.

On this, my biggest complaint is the overall subjective nature of many of the comments. I’ll address mine specifically when I list my abstracts, but as I read others feedback, it seems like many of the reviewers let their bias color things a bit too much. It might be that we have different perceptions of session levels or that there’s a misconception about a tool, technology, or technique. My suggestion back to the abstract teams is that, as you’re reviewing, step back a moment and be careful that your reviews do not contain too much subjectivity.

(Yes, even THAT might be considered a subjective judgement. Take it for what it’s worth.)

Now, let’s look at my abstracts (All General Session submissions):

Powershell Jumpstart for SQL Server DBAs – Level 100

(Accepted)

Abstract:
Powershell can be intimidating. There are many challenges to using it, especially for those not familiar with its nuances. Once understood, the language can be used for effective and robust automation that brings together SQL Server with other Windows components, such as the file system or Active Directory.

In this session, we will cover the Powershell fundamentals, how you can use Powershell itself to learn about concepts and syntax, and techniques for using Powershell with SQL Server. We will focus on core language patterns that you can use immediately, take the mystery out of the code, and help you get started writing your own PowerShell scripts. Attendees will not only acquire an understanding of Powershell, but see practical examples of how it can be used with SQL Server, along with a set of resources they can use to learn more about the language.

Prereqs:
None

Goals:

  • Introduce attendees to Powershell, what it is and where it came from. Cover basic syntax of Powershell and how it is written.
  • Demonstrate specifics of how Powershell can be used to manage and automate tasks around SQL Server.
  • Provide attendees learning resources for Powershell.

Feedback:

  • abstract – detailed but not compelling
    topic – level too low
    subjective rating: not interesting
  • The outline seems to clearly describe the contents of the presentation.  The topic and goals should be compelling to attendees.  The target audience should be big enough to support this session.  There appears to be a reasonable amount of live demonstrations in relation to the topic being presented.
  • Attendants would be interested this session because it is a beginners session.

My Thoughts:
This is where I have an issue with the subjective nature of the feedback. Maybe I’m a little too sensitive about DBA’s perception of PowerShell (I have the debate of “Why bother?” more than I care to count), but the “not compelling” comment is off base. A basic PowerShell session may not be compelling to the reviewer, but we have a LOT of DBAs out there who want this kind of information. I know because I talk to them a lot. I also take issue with the “level too low”, because this session is specifically designed to make PowerShell easy to grasp for those who haven’t worked with it.

I guess I shouldn’t complain too much since this was the one selected. I’m proud of it and the content. It has been very well received at other venues, so this is a case where I think the reviewer was letting their own personal bias of the content color the abstract review a little too much.

Automating Azure SQL Management with Powershell – Level 200

(Not Accepted Popular topic, higher rated session selected)

Abstract:
Azure is still a new frontier for many database administrators. While cloud environments bring a lot of flexibility and freedom, there is a lot to learn and a lot to keep on top of. The current portal provides a functional GUI experience, but the most effective way to manage Azure environments is by using Powershell. In this session, we will look at the fundamentals of using Powershell with Azure SQL databases. We will dive into the tools that can be used to deploy and manage these environments, as well as patterns and processes to automate these tasks. Attendees will leave with a solid foundation to build and administer SQL database environments in Azure.

Prereqs:
Basic familiarity with Powershell and Azure.

Goals:

  • Review Powershell Azure cmdlets and how to use them. Includes connecting to your Azure account and setting your subscription.
  • Examine the Azure cmdlets specifically built for Azure SQL Databases and how they apply to the Azure SQL Database operating model.
  • Demonstrate how to use the cmdlets to create, modify, and remove an Azure SQL Database. Discuss patterns for using these and automating Azure SQL Database deployments.

Feedback:

  • Abstract-  well written, compelling
    topic –  topic and title clear. Topic rarely covered
    subjective rating – interesting
  • Great topic. I would like to attend.

My thoughts:
I think this was just a full field and I had another session accepted. The feedback was pretty sparse, but there is an interest here. I’ll probably submit this one again.

Benchmarking SQL Server with Powershell – 300 Level

(Not Accepted Other sessions selected based on building a balanced program for track coverage, speaker coverage, topic coverage, and session rating.)

Abstract:
Performance is key for any database system. As professionals, we are constantly challenged to to keep our servers performing at peak condition. The struggle is many administrators do not understand their current profile, so it is hard to know when performance degrades. Benchmarking SQL Servers is a vital task that is often overlooked because it is either not understood or difficult to manage. This session will cover the important metrics to measure for an effective benchmark and will also demonstrate how a benchmark can be captured using Powershell. Attendees will leave with a clear plan of how they can measure their SQL Server performance, how that measurement can be automated, and how to develop an action plan to dynamically manage these tasks at scale.

Prereqs:
Understanding of SQL Server performance fundamentals, such as perfmon counters and wait statistics. Basic understanding of Powershell.

Goals:

  • Review what constitutes a good SQL Server benchmark. This will cover perfmon counters and wait statistics.
  • Demonstrate how this benchmark can be collected with Powershell.
  • Show how the benchmark can be automated and reported on.

Feedback:

  • abstract: one grammar error in abstract, but detailed;
    topic interesting
    subject rating: I like this topic
  • I would attend this session.
  • Topic is a good and relevant option.
    Level is good for prereqs and goals.
    Abstract is ok, with a few wording and grammar choices that could be improved/changed to make reading easier (ie. ‘performing at peak condition’ and  ‘The struggle is (that) many… ‘.

My thoughts:
Again, full field. This is a session of mine that has evolved over the past year. It started as more PowerShell focused and has become more “How to do a benchmark” focused with PowerShell the supporting actor. The grammar comments are something I continue to work on, because I tend to get waaaaaaay too verbose when I write and put myself into grammatical corners (see what I did there?). I would submit this again, because it appears to have been well received.

SQLPS In Depth – Level 200

(Not Accepted Higher rated session selected)

Abstract:
A big hurdle for using Powershell and SQL Server together is the SQLPS module. Both old and new users of Powershell don’t completely understand its capabilities. There’s also the concern of how Powershell, SQLPS, and SQL Server all interact with one another. In this session, we’ll talk about the cmdlets you may not know about, tricks to save time using the provider, and even a few gotchas on how the provider works that can save you some time and energy. We will go in depth on what happens in SQL Server when commands are run using the SQLPS module. When we’re finished, you will have a deeper understanding of how you can use SQL Server and Powershell together, along with best practices to integrate the module with your automated tasks.

Prereqs:
Minimal familiarity with Powershell, basic familiarity with SQL Server

Goals:

  • Review the SQLPS provider functionality and go over what it is and how it works.
  • Cover the cmdlets in SQLPS, how they are used and gotchas in their implementation.
  • Demonstrate practical applications for both the provider and cmdlets for SQL Server automation.

Feedback:

  • I would like to attend this session.
  • The outline seems to clearly describe the contents of the presentation.  The target audience may not be big enough to support this session.  The subject matter does not appear to match the experience level (somewhat advanced topic for a 200 level session).
  • abstract: detailed
    topic: goals not compelling
    Subjective Rating : so and so

My thoughts:
The biggest take away here is “The target audience may not be big enough to support this session.” I think that’s true, and the reason is a double edged sword. Many view PowerShell as a niche topic, so there might not be as much demand for this. However, the whole reason I talk about PowerShell and SQL Server (and I submit sessions like this) is to try and move it beyond being a niche topic. This session is intended to get more people excited about PowerShell by seeing what it can do and how to get over some of the hurdles of working with it.

That being said, I had an advanced PowerShell session at last Summit that was deeply technical and still had 300 people in the audience. Maybe it’s not quite as niche as I thought. However, I believe I need to focus on sessions with more general appeal for future Summits.

Three Things I Want From My Job

I’ve been off for a while and I apologize for that. The beginning of the year has been crazy and I am just now getting my feet back under me. The short version is I recently changed jobs and have started working for UpSearch(@UpSearchSQL) as a SQL Server Consultant. This is an exciting change for me, full of new challenges and opportunities.

Blah blah blah blah, right?

Without making light of the change, I know you all hear this every time someone starts a new gig. This post is not about that, but about some reflections I had about why I changed jobs, what satisfies me at work, and the reasons we do this stuff in the first place. I want to share these thoughts with you to hopefully help you broaden your perspective on why you are at your job and what it is that keeps you going back (besides the obvious paycheck).

Three Things

When it comes to our jobs, whether we’re interviewing for something new or looking back on our current position, we should have a list of what keeps us happy. After all, we measure and track statistics around our databases and compare them to a baseline. It should be no different for our careers. This list can be as detailed or broad as suits you, but you should have something. My list is actually pretty simple, consisting of three questions that I ask myself:

  • Am I compensated fairly?
  • Am I working on cool s#!t?
  • Am I respected?

These questions do not have simple answers, but they do have answers. As we go over each question, hopefully you start to see how they might apply to you.

Compensation

This probably seems the simplest. After all, we all get paid and we want to make sure our salary is competitive to the market, right? This definitely is something we should be thinking about and reviewing. It also takes a bit of self honesty about our capabilities and how they match to what is being paid for. Are we senior or mid-level talent? How much do we drive value for our employer? Salary is very much a two way street.  If we want to be paid fairly, we need to demonstrate our worth.

Of course, we also need to keep in mind that compensation is far more than just salary. I’ve worked jobs where I got paid pretty well, but had to fight to go to conferences like the PASS Summit. Some jobs had liberal vacation policies while others offered the minimum. When you consider your compensation, you have to ask yourself what keeps you happy from a larger perspective.

Coolness Factor

I’m the kind of guy that needs to be challenged. It is why I spend time blogging and presenting, as well as playing with different kinds of technology. This is how my work fulfills me. I could simply punch the clock, work from 8 to 5, and collect my check, but this wears thin pretty quickly. I need more in a job than just going through tasks by rote so I can go home at the end of the day.

This is why it is important that I get interesting work. I need problems to solve and challenges to overcome.  To be clear, I understand that I will have to do the tedious stuff as part of any job. Work like this needs to be balanced out, though. Heck, sometimes the challenge of managing the tedious stuff in a more efficient manner is engaging in and of itself. The key is that I want my job to help me grow, not allow me to tread water. Job satisfaction is measured on this growth.

R-E-S-P-E-C-T

This last item is the linchpin. For the longest time, I was happy with the first two items on this list. Then one day, I sat up and realized that I was getting worn down by my job. I couldn’t quite place my finger on why, because I was getting what I wanted out of compensation and the work was definitely cool and challenging. The problem was I was struggling with going into work every day and my motivation was rapidly draining.

The key, I realized, was a matter of respect. Whether it is peers, team members, or management, I need the people I work with to respect my time and my knowledge. This may seem a little arrogant, but it is important to recognize your own value. Companies hire me to be a database expert, and an expensive one when you boil it down. This means my time and opinion carry a certain amount of cost, and when that cost is being wasted because others in the company do not recognize that value, it becomes wearing.

Without trying to sound too negative, we need to understand our professional value. As with the compensation question, it takes a lot of self honesty to value ourselves appropriately. It is important to recognize that within ourselves. This is because it is an important factor for job satisfaction, because not only should we recognize that value in ourselves, we need others to realize it and respect it.

Can I get a little satisfaction over here?

Job satisfaction, while ultimately a binary yes or no answer, has a lot of degrees of measurement. After all, I can think of jobs I’ve had where the pay was fantastic but the environment sucked. Or where the entire team thought I was a rock star, but I was not working on interesting or engaging work. When I felt my job satisfaction going down, I could always track it back to one of these three items.

What is important here is that by understanding what satisfies you at a job, it gives you a tangible item to resolve. It might simply be a matter of going to your boss and telling him what the problem is. This gives you something tangible to work on with your employer and is fair to them, because chances are they don’t want to lose you and will work with you. It also may mean you need to find another job that fills that gap. In this case, when you sit down in an interview, you can have a conversation with the person across the table about what fulfills you.

Whatever your reasons are, it is important that you know why you get up in the morning (or other time) and go to work. Your reasons are probably different than mine, as these tend to be pretty personal. I hope that by giving you some insight into what job satisfaction means to me, it can help you build your own list of questions. Measure your job satisfaction, know what makes you happy, and then figure out how to get it.

Giving Thanks for Powershell Resources

turkey-rice-krispiesIt seems an appropriate time to write this post as this week is the American holiday for Thanksgiving, a time when we show appreciation for that which makes our lives better. I would like to thank some folks for the Powershell resources they’ve provided, resources that I’ve used to figure out the language. We all succeed by standing on the shoulders of giants and I can not thank these giants enough. Since I also get a lot of questions from folks about what are good resources to use for Powershell, I want to help them out by providing this list.

Don Jones(@concentrateddon) and Jeff Hicks(@JeffHicks)

Let’s face it: You are not going to get very far into Powershell without running into these two names. Don and Jeff have contributed so much to the Powershell community. While we could go on and on about all of their material, let me give you the top three hits.

Powershell in a Month of Lunches is the go-to book for learning Powershell. For a language that takes users time to get their brains around, this book makes it easy. By breaking Powershell fundamentals into 25+ one hour lessons, it will give you a gradual learning curve for understanding and using Powershell. If you don’t have it and are starting your Powershell journey, get it.

Powershell in Depth is a much larger book, but that’s because it is true to its title. Where Powershell in a Month of Lunches is a starter, easing the Powershell noob into how the language works, Powershell in Depth is a detailed reference manual. The book has many chapters that explore different aspects of the language, from basics of control flow and operators to the more involved topics of workflows and background jobs. I have this book for when I know what I want to do, but need to know how to make it work.

Powershell.Org is the Powershell community’s beating heart. While the other two resources I’ve listed can be used for studying and getting down the basics, this site provides a hub that contains articles, forums, and other helpful material for new and experienced users. The Powershell community is young, but growing rapidly. The best part is that the community has plenty of helpful folks willing to lend a hand.

Allen White(@sqlrnr)

Allen is a long standing member of the SQL community and has been a Microsoft MVP for many years. While he’s busy with his new job at SQL Sentry, his many blog posts on SQLBlog.com will still provide great code and patterns to DBAs looking to use Powershell. I’ve learned so much from Allen’s work that ‘thank you’ isn’t enough, but I try.

The great thing about Allen’s posts is that they are clear and understandable. The problem with Powershell is, much like other languages, scripts can easily spin out of control and become so convoluted that the purpose of them can be lost. Allen’s code is broken down so that you can understand and analyze each piece, taking away the valuable information and not getting drowned in if/else statements and SMO methods.

Ed Wilson(@ScriptingGuys)

If you’ve done any internet searches on Powershell, you have come across the Scripting Guys blog at some point. This fountain of information is a necessary stop on the World Wide Web for all Powershell enthusiasts. Ed often will provide nice little code snippets that we can learn from and build upon. Add into that the constant flow of other notable Powershell folks as guest bloggers, and you will learn numerous tips and tricks you can incorporate into your own code.  Ed’s conversational writing style makes the material less intimidating and the way he breaks posts up between tips and full discussions makes it easy to digest.

Microsoft Virtual Academy

We all learn in different ways, whether it is getting our hands dirty, studying books so we can digest information at our own pace, or listening to a lecture on the topic and watching an expert provide step-by-step examples. Most of us probably mix all three of these methods in one way or another. For those who prefer webinars and lectures, Microsoft’s Virtual Academy has many free videos for you that can walk you through many different topics within Powershell.

Sounds great, right? What’s even better is these sessions are given by none other than Jeffrey Snover(@jsnover) and Jason Helmick(@theJasonHelmick). Jeffrey, of course, is the father of Powershell and Technical Fellow at Microsoft. Jason is the CFO of Powershell.org and one of the original community members of the Powershell movement. Through the MVA courses, you are getting free training from two of the biggest names in and around Powershell.

Let me say that again so it sinks in: Through the MVA courses, you are getting free training from two of the biggest names in and around Powershell. (Hint:free)

The Tip of the Iceberg

This is by no means an exhaustive list of Powershell resources available to you. Instead, it is an answer to the question I hear after almost every presentation I give: Where do I start? There’s a lot to learn with Powershell and, like any other technical subject, you will be continuously improving your game. I just want to get you started in much the way I started.

Good luck!

Helping Your #Powershell With Twitter

Today I had a bit of a Twitter conversation with some SQL folks I know that were looking for Powershell help. It’s a common enough thing, of course. The trick is, we SQL people are so used to having the #sqlhelp hashtag as our first line of defense for figuring out problems that it’s weird for us to not have something like that for our other hurdles. Where most of these hash tags bombard you with spam and noise, this one is almost pure signal, so we often forget how good we have it.

PoSHHelp_TweetDeckOther communities would like to capture that magic as well. I know those of us using Powershell want our own hash tag for help. Well, now we’re going to try. Some of the notables already use #PoSHHelp, but not a lot of people pay attention. A group of us want to change that. I’ve already added a column for it in my TweetDeck and will keep my eye out for Powershell questions I can help with. Folks like Shawn Melton(@wsmelton), Adam Bertram(@adbertram), Derik Hammer(@SQLHammer), Rob Sewell(@fade2black), Boe Prox(@proxb), and others will be looking for your Powershell problems and try to assist you over Twitter with the same care and grace as SQLHelp.

It’s going to take some work, but we’ll try and apply the same ground rules to this hash tag:

  1. Questions should fit into 140 characters.
  2. If they don’t, put your question and information on another site (like ServerFault.com) and link to it.
  3. DO NOT SPAM THE HASH TAG. This is important, because in order to make it useful it needs to be kept clean. Don’t use it to advertise your blog posts or articles, but only for Q&A.
  4. Don’t be a dick, a.k.a. Wheaton’s Law. It’s all too easy to let the anonymity of the internet get the better of us. Be polite and respectful to those using and accidentally mis-using the hash tag.

Also, there are some people who also use #PowershellHelp. I’m going to watch this too, but I would encourage you to use #PoSHHelp instead. Mostly because it’s shorter and will give you more room for your question.

So, if you have a problem…

And if no one else can help…

And if you have a Twitter account, maybe you can tweet your question to…

nRhfnZ0

 

#SQLPASS 2015 Board of Director’s Elections

Apologies for the unplanned blogging hiatus, but I’m back. While I’ve got a few technical post ideas bouncing around in my head, some events over the past two weeks have arisen that I want to blog on. If you’re hoping for more Powershell posts, do not despair! They will be along shortly.

Recently PASS took nominations for the annual Board of Directors election. This is an opportunity for members of the community to step up and volunteer their efforts on a global level to help shape and guide PASS, as well as act as a conduit for the community’s voice. Every year has a solid slate of candidates and this year is no different. In no particular order, they are:

As a community, we’ve got some tough decisions, as all four candidates are outstanding members of our #SQLFamily. Thanks to all of them for devoting so much time and effort to make our organization better. No matter the outcome of this election, the PASS community will be well served.

With all this being said, I wanted to share a little more with you about the new candidates and voice my support for them in this election. This is not to tell you how to vote, but to provide you some insight to who these gentlemen are so you can make a better informed decision. To be honest, I’m going to have a tough time myself.

Ryan_400x400Ryan

Ryan Adams is a PASS member I’ve known for a couple years now and very few people can compete with his passion and drive for building the community. Ryan is the President of the Performance PASS virtual chapter, helping guide and build that outlet into one of the stronger virtual resources out there. This is in addition to being a board member for the North Texas SQL Server Users Group, one of the largest PASS chapters out there. Add to this the fact that Ryan is an active community speaker, sharing his knowledge and passion at numerous PASS events. This makes it no surprise that he’s an active Microsoft MVP, as he’s an excellent community voice for SQL Server and the people who support it.

But that’s all out there on the Googles for you to find. You probably want to know what sets Ryan apart. I can tell you that there are very few people as driven and energetic about PASS as Ryan is. I’ve had several conversations with him and his passion is infectious. Combine this with his grassroots organizational experience and it is no surprise that he has thrown his name into the hat. Ryan is someone who not only knows what needs to be done, but also how to make it happen.

ArgenisFernandez250sqArgenis

Full disclosure before I get started: Argenis Fernandez is a close friend that I’ve known for several years. He’s had a tremendous impact on both my professional career and community participation. This shouldn’t discount any of want I would say about him and, hopefully, only strengthens these views.

I think most people have probably heard of Argenis at this point. He’s a Microsoft Certified Master and MVP. For a long time he’s been one of the strongest technical voices in the community. What sets Argenis apart is that he has a fantastic ability to connect with others. Anyone who has talked with Argenis knows that he genuinely cares about the success of those around him. He constantly mentors other and builds up those he talks with. In sports terms, he’s that super start that makes his team mates better.

My personal story relating to this is from two years ago, as I was just getting started with my speaking career. Argenis was in Denver on a consulting engagement and we were going to dinner. The conversation meandered, but at one point I mused about possibly taking the Microsoft certifications. Argenis simply looked at me and said “Dude, just take them. You can pass them.” As with most mentors, he didn’t just nudge or suggest, he outright shoved me. A year later, I had all five certs to get the MCSE Data Platform. I could tell you at least 10 other stories like this where Argenis motivated someone (not just myself).

The Election

This upcoming election, as I said, will be tough. While I spoke specifically about Ryan and Argenis, Tim and Jen have both demonstrated their commitment to PASS. Hopefully I have been able to fill in some gaps about why the new candidates should also receive your consideration.

What’s most important is that you vote. Last year I blogged about some issues I had with the direction PASS is going. This is your chance to influence that direction. The candidates have the opportunity to mold and shape PASS over the next several years, so these elections have tremendous impact. Over the next few weeks I’m sure you’ll get a chance to learn more about all four candidates, more than I have shared here. Take that opportunity. If you’re on Twitter, you can interact with them there and learn about the candidates yourself. And vote.

The PASS Summit 2015 Call for Speakers

Here we are, the annual community tradition of submitting sessions to the PASS Summit. This is the big time, the real deal, the event everyone wants to be seen at (as far as SQL Server goes). We all have hopes and dreams of standing on that stage, sharing what we love about SQL Server with the greater community.  Now that the submission deadline has passed, I wanted to post a few thoughts I had on this year’s process.

And yes, I want to do it before the OTHER annual community tradition of “bitching about why I didn’t get selected” starts.

Too Many Topics

The first thing that struck me was the excessive number of topics in each track. I didn’t count them all, but it was a little overwhelming. What made the process especially difficult was that there’s a lot of overlap with these subcategories, making it confusing about which to select.  This is especially a challenge for me with Powershell submissions, because many of the abstracts I created could have easily gone under two or three subcategories.

One example is my abstract on automating SQL Server builds with Powershell. Now, there’s a Powershell/SMO subcategory and one for Installing/Upgrading SQL Server. Which do I choose? I went with Powershell because that’s the focus of my session, but it might have been more applicable to the other.  This is a problem.

Suggestion: Either PASS needs to cull the overall list of topics or allow you to select multiple topics for your abstract.  Personally, I’d prefer the former.

Too Many Submissions

We have a good problem in the SQL Server community: tons of great speaking talent. There a lots of folks at the local and regional level who give great presentations at local user groups and SQL Saturdays. There’s also a fair amount of mediocre talent as well. However, all of these speakers can submit to Summit, regardless of their experience level. This places an overwhelming burden of work on the program committee.

I don’t want to make speaking at the Summit an exclusive club. I think all community members should have the opportunity to present at the Summit, but we need to balance that with consideration for the program committee volunteers and trying to provide the best product for Summit attendees. As such, the process should focus on standards that encourage both active community participation and a focus on public speaking.

Suggestion: PASS needs to start placing restrictions on who can and can’t submit. They already do this for the precons. My initial thought is a speaker should have four public SQL speaking events in the past calendar year. With opportunities like local user groups, SQL Saturdays, and virtual chapters, this is not a difficult number to hit.  It ensures that the speaker is an active community participant, someone who is contributing and practicing their craft.

We need more faces

In general, this isn’t a big deal, but we do have many popular speakers get multiple sessions at the Summit. I understand these folks are a draw and will put butts in seats, but the reality is that every person who gets doubled up means one less other speaker who will be selected. Again, considering the depth of speaking talent in the PASS community, this strikes me as problematic in general and a wasted opportunity to increase the variety of speakers at the Summit.

Suggestion: All speakers are limited to a single session, no exceptions.

More Transparency Around the Selection

I know this is a constant refrain regarding PASS, but it is needed. This is not me piling on the organization for being closed and secretive, but simply reminding them that we still need to know more about what’s going on. I’ve been paying attention to the selection process for the past two years and it IS getting better. I like how open PASS has been with how the program committee works.

The challenge is that the program volunteers don’t make the actual selections. They make recommendations that are passed to the selection committee. The selection committee then performs “levelling” and uses that to select sessions. This is the black box, because I don’t know what the levelling process entails and how things are judged. I’m not advocating some grand conspiracy theory around who gets selected, but there will always be questions and criticism until we are better informed about why.

Suggestion: PASS needs to continue what they have been doing and communicate more about the process. As I said, I recognize that the process of improvement is ongoing and it is getting better, but it can’t stop. The community needs communication, especially as we grow larger and larger.

Videos Don’t Add Value

I understand that PASS is trying to gather as much information about a speaker as possible. Videos are not the way to do it. There are too many issues with recording quality and software that make this a reliable method. Additionally, many videos won’t actually display a candidate’s public speaking skills, only their ability to sit down and chat in front of a camera. These combine to make video recordings more subjective than objective, and we need to focus on objective quantifiers.

Suggestion: Just ditch the video submission.

You Don’t Deserve To Speak At The Summit

This one is directed to the entire community, the people submitting. Every year I see the same tweets and posts griping about how certain people did not get selected or others did. How the process is horribly broken. To be fair, when you craft abstracts and then wait two to three months for a response, the resulting rejection is disappointing.

I think, because of all the other opportunities in the community, being rejected from a speaking event is a bit of a shock. The reality is that competition is tight for the Summit and speaking slots are limited. Getting turned down really is not that surprising. What bothers me most is that I see an attitude of entitlement from some of community members concerning presenting at the Summit. None of us are owed a speaking slot and the burden is on each individual speaker to prove their worth.

I view speaking at the Summit a lot like free agency in a professional sport. There’s a lot of competition to get noticed and a lot of reward if you do. Some people have an edge because they’re really good at what they do and demonstrate that on the public stage. Some others are hidden gems, waiting for their opportunity to get noticed. However, the market is driven by demand and selections are made based on that demand, not by attribution or “right”. We as speakers should always be striving to prove ourselves to the community and our selection should be based on ability, presence, and contribution, all of which will drive up demand.

Suggestion: Chill out if you don’t get selected. Focus on what you can do locally and regionally, through your own user group and SQL Saturdays.The Summit is awesome and I love going, but the truth is I have MUCH more of an impact speaking at smaller events. I encourage all community members to have a presence at these events, to focus on what they can do in their city. Build your community resume and skill set so that when you do submit to the Summit, you’ll be better prepared when you do make it to that stage.

In Conclusion…

I’ve already shared some of this with some members of the PASS board privately, but I wanted to share this publically as well. This is our community and we all have the ability to affect this change by being involved and reaching out. What has always impressed me about PASS is how grass roots it is and how members feed off each other. We need to focus on that, because it is what makes our community stronger than any other technical community out there.