SQL Server

Using an #Azure Logic App to create @AzureDevOps Work Items from a SQL Server dataset

In a previous post, I described how to use an Azure Logic App to update an Azure DevOps work item; in an effort to add additional automation to our processes, I’m starting to move more and more notifications and work items directly to Azure DevOps using Logic apps. For example, for one of our websites, we have an SSRS report that queries the database to determine if employees are compliant with password policies. We get a weekly email that we then have to copy and paste into Azure DevOps (to track the work), and then we do the work. I want to eliminate the email step.

The workflow is very straightforward compared to updating an existing work item; there’s no need to manipulate a REST API for this. Connectors are all built in.

  1. Set up a schedule using the Recurrence item.
  2. Execute a stored procedure. This is the item that can be difficult to set up, especially if you have an on-premises SQL Server. In our case, I had to download and set up an on-premises data gateway, and then configure a connection to my SQL Server. Once that was done, I had to identify the database and stored procedure that contains the result set I wanted to add to the work item.
  3. The result set from the stored procedure is in JSON format; parsing the JSON allows it to be defined as individual elements that can be referenced by additional actions.
  4. I then take those elements and focus on the items of interest to construct an HTML table.
  5. I then create an Azure DevOps work item by adding the HTML table to the description field.

BONUS: I added a timestamp to the work item title by using the formatDateTime() function.

formatDateTime(utcNow(),'D')

Uploading Multiple RDL files to SSRS

My QA folks have a problem; development has been working on migrating a bunch of reports from a legacy 3rd-party application to SQL Server Reporting Services. Development has finished their “sprint” (*cough* waterfall), and handed over 52 reports to QA, and told the to load them into their QA server. The problem? The web interface only loads 1 report at a time.

Me, being the DevOps guy that I am, thought “well, that’s just silly; there has to be a way to upload all of the reports at once, rather than clicking on some silly buttons over and over again”. I googled, and the best explanation I found was at this blogpost: https://basic-ssrs.blogspot.com/2015/12/upload-multiple-rdl-files-from-local.html (Kudos; this is a VERY well written walk-through).

Unfortunately, I did exactly what the blog suggested, and ran into two problems. The first? Running the .bat file opened and closed a command window in seconds. No feedback. Nothing. I went back and made a minor change to the .bat file; namely, I added the PAUSE command to the end:

“E:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\rs.exe” -i C:\report_upload\Upload_Multiple_RDL_files.rss -s “http://localhost/reportserver” -v FILE_NAME=”C:\report_upload\ConfigurationFile.txt”
pause

This allowed me to discover the second (more important) issue; I ran the .bat file, and now I get the following error:

Unhandled exception:
The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: ‘.’, hexadecimal value 0x00, is an invalid character. Line 2563, position 10.

Ummmmm, what?

Back to the Bing. I found another article that describes basically the same process, but at the bottom of the comment section, there was this little gem:

Hello, I was getting the invalid content error:

“….or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: ‘.’, hexadecimal value 0x00, is an invalid character….”

Made some research and found that arrays in vb are created as N+1 long size, and filled with 0x00, so you need to change the script to:

definition = New [Byte](stream.Length – 1) {}

 to get it working.

https://www.mssqltips.com/sqlservertip/3255/sql-server-reporting-services-ssrs-rsexe-utility/

BINGO! Went back to the .rss file and changed the below code block to

 ‘ Deploying the Reports

                    Try

                        Dim stream As FileStream = File.OpenRead(fileFullPath)

                        Dim NameWithExt As String = fileFullPath.Replace(path, “”)

                        Dim NameOnly As String = NameWithExt.Replace(“.rdl”, “”)

                        definition = New [Byte](stream.Length-1) {}

                        stream.Read(definition, 0, CInt(stream.Length))

Ran the.bat, and in just a few minutes, I had a ton of reports loaded to my directory. Now I just gotta fix the datasources.

**Edit – based on comment below, I’ve embedded the entire revised script.

‘ Script Starting Point
' Script to deploy report to report server
' EXECUTE VIA COMMAND LINE
‘ Original script: https://basic-ssrs.blogspot.com/2015/12/upload-multiple-rdl-files-from-local.html
‘ Upload_Multiple_RDL_files.rss

DIM definition As [Byte]() = Nothing
DIM warnings As Warning() = Nothing

Public Sub Main()

' Variable Declaration
        Dim TextLine As String = ""
        Dim LocalDir As String = ""
        Dim ServerDir As String = ""
        Dim definition As [Byte]() = Nothing
        'Dim warnings As Warning() = Nothing

' Reading Configuration Text file and assigning the values to variables
        If System.IO.File.Exists(FILE_NAME) = True Then
            Dim objReader As New System.IO.StreamReader(FILE_NAME)
            Do While objReader.Peek() <> -1
                TextLine = objReader.ReadLine()
                Dim parts As String() = TextLine.Split(New Char() {","c})
                'TextLine & objReader.ReadLine() '& vbNewLine
                LocalDir = parts(0)
                ServerDir = parts(1)

                Dim path As String = LocalDir
                Dim fileEntries As String() = Directory.GetFiles(path)
                Dim fileFullPath As String = ""
                For Each fileFullPath In fileEntries


 ' Deploying the Reports
                    Try
                        Dim stream As FileStream = File.OpenRead(fileFullPath)
                        Dim NameWithExt As String = fileFullPath.Replace(path, "")
                        Dim NameOnly As String = NameWithExt.Replace(".rdl", "")
                        definition = New [Byte](stream.Length-1) {}
                        stream.Read(definition, 0, CInt(stream.Length))

      warnings = rs.CreateReport(NameOnly, ServerDir, True, definition, Nothing)

      If Not (warnings Is Nothing) Then
        DIM warning As Warning
        For Each warning In warnings
        Console.WriteLine(warning.Message)
        Next warning
      Else
       Console.WriteLine("Report: {0} PUBLISHED!", NameOnly)
      End If

     Catch e As IOException
      Console.WriteLine(e.Message)
     End Try
    Next fileFullPath
   Loop
         Else

            Dim MsgBox as String = "File Does Not Exist"

        End If
End Sub

'End of the Script

Checking for #RedGate SCA databases

We’re starting a large scale conversion of our development processes with some very old database servers. Some of these databases were developed over 10 years ago, and they were never checked into source control. As our team develops new features, they’ve been using Red Gate’s SQL Change Automation to develop database projects along side their applications; it’s an impressive tool, and we’re getting ready to tackle older databases in an effort to improve our code base.

But, first, we need to determine what databases are already source control and which ones are not. Most of these databases and applications are minimally documented, so most of our development team doesn’t know what’s actually in production. To discover what databases are already part of SCA, I created a simple SQL script. It looks for the existence of the [dbo].[__MigrationLog] table, an artifact of the SCA process. I can then tell when a snapshot was last deployed, and what version was given.

CREATE TABLE #tmp (dbname varchar(1000), completedate Datetime, version varchar(200))

DECLARE @sql varchar (MAX)

SET @SQL = ' USE ?;
If exists (SELECT * from sys.tables t WHERE t.name = ''__MigrationLog'')
SELECT db_name() dbName, MAX(complete_dt) completeDate, MAX(version) version
FROM dbo.__MigrationLog
'

INSERT INTO #tmp
exec sp_msforeachdb @SQL

SELECT *
FROM #tmp

DROP TABLE #tmp

The DBA is dead; long live the DBA!

Been reading some interesting arguments over the future of DBA jobs lately, and as usual, I find a lot of truth somewhere in the middle. Let me try to sum up the positions of the three authors that impressed me the most:

Thomas LaRock – Why I’m Learning Data Science:

Tom kind of kicked off this discussion with his post; the three main takeaways I got from it are:

  • The traditional role of the DBA is being automated away, right in front of our keyboards.
  • As computers get better about self-tuning, it will be difficult to justify the expenditure of a dedicated database administrator
  • Computers are only good at providing answers; humans are good at asking questions.

Brent Ozar – Twitter Posts

Brent responded via twitter to “a couple of emails” stating that “the DBA career has a time bomb”. The three key points I got out of the thread are:

  • The tools are getting better, but the problems are getting harder.
  • SQL Server still ships with some legacy baggage that require hands-on experience to adjust; if computers were smart, why hasn’t that been fixed?
  • Even as the technology gets better, adoption is slow.

Grant Fritchey – There Is A Magic Button, A Rant

Grant took the humorous route, telling the tale of the “Run Really Fast” button that all database administrators know about once they achieve a certain level of competence.

  • Tools are getting better, but they can’t fix problems with design (both software and hardware).
  • Automation can reduce drudgery, but design is fundamental.
  • DBA’s are all secret alchemists.

Where I sit…

All three of these guys are smart folks that I respect a lot, and honestly, I see a lot of truth in what they’re all saying. The tools ARE getting better, and I do believe that automation is going to significantly change a lot of different jobs in IT, including database administrators. The role of a DBA is particularly susceptible to this because of the hybrid nature of the work. There’s elements of design (system and software), development, and operations associated with that role, and the stack is getting increasingly complicated. The Microsoft Data Platform now sits over relational and non-relation data, and encompasses analytics, visualization, reporting, integration services, high availability, disaster recovery, and much more. The stack is getting too complicated for the average DBA to be a master of all of it.  However, it’s hard to deny the necessity of expertise, particularly with all of the technical debt associated with a product like SQL Server.

But what if we sliced the stack differently?

The cloud paradigm talks about breaking the computing stack up into various services, each acting as a black box to the level above it; Software as a Service is built on top of a Platform as a Service, which in turn is built on top of an Infrastructure as a Service. As enterprises begin to embrace the cloud, they will reorganize resources along these lines. Why? Because it lays the foundation for consolidating resources where it counts, and allows for future portability. In other words, companies can start at the bottom of the stack, and port their Platform and Software services over to cloud providers without significant alteration of those upper level. Likewise, as technology matures, migrating the Software layer to a new Platform provider will get easier over time (we’re not there yet, but it’s coming).

I would argue that the current role of database administration straddles the line between software and platform; traditional maintenance and server configuration task are part of the Platform layer, and database design are part of the Software layer. The term DBA will mean multiple things to multiple people, depending on where that role sits along this divide. In other words, a DBA that works at the Software layer will tend to focus on questions of database design, data software performance tuning, and architectural issues associated with the ever expanding set of options for databases beyond just the relational db. DBA’s at this layer need to become full-fledged member of the development team, which may eventually lead to a fuzzier distinction between application and database developers. DBA’s at this layer will need to be well-versed in the concepts of multiple data management technologies (and possibly other development technologies). Opportunities should abound here, but diversity should be valued over full-stack mastery of a single product.

DBA’s at the Platform level will change roles as well; they’ll no longer need to be steward of data, or responsible for tuning bad code. Their job will be to make the Platform support contracted levels of performance, and identify and correct resource utilization and configuration issues. Automation will have a huge impact here; Platform DBA’s will be responsible for supporting multiple instances of SQL Server, including support for high availability and disaster recovery. Scripting skills are highly desirable, as is mastery knowledge of specific products. I would expect job opportunities to slow in this area, but experts will still be needed in the future.

In short, I don’t think the role of a DBA is going away, but I do think that it’s going to split. That’s exciting, because it means that people have options.  We still need experts, and there will still be opportunities for folks who love data to find meaningful work; their expertise will just become part of a different structure than we’re accustomed to now.

Now, where’d I put that Philosopher’s Stone?

#SQLSatATL, #DevOps, #Cloud, & the Future of the DBA

Last weekend was SQLSaturday Atlanta 2017, and I was not only an organizer, but a presenter. In the future, I’ll need to balance that a little better (especially when we’re dealing with a lot of unknowns for the day, like a new building). Overall, I think my presentation went well; had a lot of great hallway conversations with folks later, and got some good feedback. You can find the slide deck here, or look on the Code, etc tab above.

However, during my presentation, a couple of questions came up that I didn’t have a great answer for; mostly it was revolving around the first bullet point on this slide:

Why, if DevOps as a philosophy encourages better communication between development and operations, do I believe that there will be increased segregation between those roles? I fumbled for an answer during the presentation, but then went back and realized what I left out in my explanation, so I thought I’d take a stab at rebuilding my argument and explain where I was going with this:

DevOps is built on a Service-Oriented Architecture (SOA) model.

Services logically represent business activities; they are self-contained, and the inner workings of each service are opaque to the consumers. Services can be built using other services, but that rule of opacity stays true; when you consume a service, you don’t care what it’s doing under the covers. It just has to provide a consistent output when given a consistent input.

The Cloud Paradigm is also built on a SOA model.

Software-as-a-Service is built on a Platform-as-a-Service, which is in turn built on an Infrastructure-as-a-Service. Communication between service layers must be consistent and repeatable, but processes and procedures within each services should be opaque. Furthermore, the consumers of a service are not the same; for example, if you have a web portal displaying account information to a client. The client consumes Software-as-a-Service; they just want to see their account information. They don’t care how many servers are involved or how the network is laid out. Software-as-a-Service consumes from the Platform layer; they may have a requirement that they use a particular database system, or OS, but specific configuration isn’t exposed to them. Software engineers define performance expectations (e.g, “we need to commit 1000 transactions per second”), and leave it up to the Platform (and Infrastructure) engineers to meet that expectation.

The traditional tasks associated with SQL Server Database Administration can be roughly divided into two roles: Development and Administration (Operations).

From this slide, I outline the general breakdown between skills:

 

SQL Server as a product spans the top two layers of the Cloud Paradigm:

Basically, I believe that traditional development skills belong to the Software-as-a-Service Layer, and traditional administration skills belong to the Platfom layer.

So by segregation of responsibilities, I mean that as companies embrace the Cloud paradigm, the current role of a DBA will fork into both Software-as-a-Service engineering (Dev) and Platform-as-a-Service engineering (Ops). I need to clarify that thought more in future presentations, because I may be using those terms differently than others would.

Thanks for reading, and if you attended, thanks for coming!

-Stu

#DevOps: Embrace the Ops

If you’re at all in touch with the DevOps community, you’re probably aware of the GitLabs Incident on 1/31/2017; I won’t spend too much time rehashing it here, but GitLabs has done a great job of being transparent about the issue and their processes to recover. Mike Walsh (Straight Path Solutions) wrote a great blog post about it entitled DevOps: Don’t Forget The Ops, which covers a lot of ground from a database administration perspective. Mike ultimately ends up with three specific action items for DevOps teams:

  1. Plan to Fail (so you don’t)
  2. Verify Backups (focus on restores, not backups)
  3. Secure your environment (from yourself).

I agree with all of these ideas; I think Mike is spot on about the need to Remember the Ops in DevOps. However, I want to go a step further, and encourage DevOps adoptees to Embrace the Ops.

What do I mean by that? Let me start with this; Brent Ozar posted this on Facebook yesterday (the image will take you to the job description):

Now, it’s obvious that GitLabs had a backup strategy (they detailed it in their notes), so I don’t mean to imply that they didn’t expect administrative tasks from their database people, but I do think we can infer that administrative tasks were not prioritized as much as other tasks (high availability, performance tuning, etc.). Again, we know that GitLabs had strategy for backups, so it appears that this is a cultural issue (at least based on this flimsy evidence and the outage). And to some degree, that’s understandable; one of the longest running challenges on the operations side is being labeled as a cost center as opposed to development being viewed as a revenue generator. This perception is pervasive in traditional IT shops, so it’s probable that even Unicorn shops share some of this mentality. Development (new features) makes money; Operations cost money.

However, in a true DevOps model, the focus is on delivering quality services to customer, faster. New features may bring new clients, but reliable service retains clients; both are revenue generating. So while it may add some cost to deliver quality service to customers, cutting corners in operations risks impacting the bottom line. From this perspective, I’m arguing that DevOps shops should not only remember the ops, they should embrace it. The entire value stream of a business service includes people, procedures, and technology split into teams; the fewer the teams per service, the fewer silos. So how do we embrace the ops?

  1. If Ops is part of the Value Stream, then apply consistent Development principles to it. I’ve written before that “we are all developers“, and I believe that; administrators are creative folk, just like application developers. Operations includes backup, monitoring, and validation. We should apply development principles to these operations, like creating reusable scripts, finding opportunities for automating validation, and logging (and investigating) errors with that pipeline. We should use source control for these tools, and treat the operations pipeline like any other continuous integration project (automate your backup, automate your restores, and log inconsistencies).
  2. Include operational improvements as part of the development pipeline. I’m borrowing a lot from Google’s SRE model; SRE is what you get when you treat operations as if it’s a software problem (see point 1 above).  However, the SRE model is usually a self-contained bubble within operations; they have their own pipelines for toil reduction. I think if DevOps wants to truly embrace operations, developers need to include toil reduction in the service delivery pipeline. If operations folks have to flip 30 switches to bring an app online, development should make it a priority to reduce that (if possible). It goes back to the fundamental rule for DevOps: communicate. Help each other resolve pain points, and commit to improving everything in the value stream.
  3. Finally, balance risk and experimentation with safety. Gene Kim’s The Phoenix Project provides the Three Ways, and the Third Way is all about creating a culture that rewards risk and experimentation. This is great for developers; try something new, and if it breaks, you can deliver a fix within hours. However, as the GitLabs incident shows, some fixes can’t be delivered, and risk needs to be mitigated by secure data handling processes and procedures. While I’m a big fan of controlled failures (e.g., shutting a server down hard in order to see what the impact is), you don’t do that unless you can test it in a lab first and make sure you have good mitigating option (how do you recover? What error messages do you expect to see? Are you sure your backup systems are working?). Don’t forsake basic safety nets while promoting risk; you want competitive advantages, but you also want to stay in business.

Oh, The Places You’ll Go! – #SQLSeuss #SQLPASS

Last week, I had the privilege to speak at the annual PASS Summit; I got to present two different sessions, but the one I’m the most proud of was my Lightning Talk: Oh, the Places You’ll Go! A Seussian Guide to the Data Platform. I bungled the presentation a bit (sorry for those of you who want to listen to it), but I feel pretty good about the content. I’ve presented it below, with the slides that I used for the talk.

The goal of this presentation was to explore the Microsoft Data Platform from the perspective of a SQL Server professional; I found this great conceptual diagram of the platform from this website a while back, and wanted to use it as a framework. I figured the best way to teach a subject was the same way I teach my 3-year-old: a little bit of whimsy.

Enjoy.

You have brains in your head

And SQL Skills to boot

You’ll soar to great heights

On the Data Platform too

You’re on your own, and you know what you know,

And YOU are the one who’ll decide where to go.

You’ve mastered tables, columns and rows, OHHHHH MYYYY

You may even have dabbled in a little B.I.

You’re a data professional, full of zest,

But now you’re wondering “What comes next?”

Data! It’s more than just SQL,

And there’s a slew of it coming, measured without equal.

Zettabytes, YotaBytes, XenoBytes and more

All coming our way, faster than ever before.

So what should we do? How should we act?

Should we rest on our laurels? Should we lie on our backs?

Do we sit idly by, while the going gets tough?

No… no, we step up our game and start learning new stuff!

 

Oh, the places you’ll go!

ARCHITECTURE

Let’s start with the Theories,

The things you should know

Designing systems as services,

Are the route you might go.

Distributed, scalable

Compute on Demand

The Internet of Things

And all that it commands.

Infrastructure is base,

Platform is in line

Software and data

Rest on top of design

Once you’ve grasped this

Once you’ve settled in

You’ve embraced cloud thinking

Even while staying on-prem.

But beyond the cloud, there’s data itself.

Structured, polyschematic, binary, and log

Centralized or on the edge,

Some might say “in the fog”

Big Data, Fast Data, Dark, New and Lost

All of it needs management, all at some cost

There’s opportunity there to discover something new

But it will take somebody, somebody with skills like you.

Beyond relational, moving deep into insight

We must embrace new directions, and bring data to life

And there’s so many directions to go!

ADMINISTRATORS

For those of you who prefer administration

System engineering and server calibration

You need to acknowledge, and you probably do

You’ll manage more systems, with resources few.

Automation and scripting are the tools of the trade

Learn powershell to step up your game.

Take what you know about managing SQL

And apply it to more tech; you’ll be without equal

Besides the familiar, disk memory CPU

There’s virtualization and networking too

In the future you might even manage a zoo,

Clustering elephants, and a penguin or two.

 

But it all hinges on answering things

Making servers reliable and performance tuning,

Monitoring, maintenance, backup strategies

All of these things you do with some ease.

And it doesn’t matter if the data is relational

Your strategies and skills will make you sensational

All it takes is some get up, and little bit of go

And you’re on your way, ready to know.

So start building a server, and try something new

SQL Server is free, Hadoop is too.

Tinker and learn in your spare time

Let your passions drive you and you’ll be just fine

DEVELOPERS

But maybe you’re a T-SQL kind of geek,

And it’s the languages of data that you want to speak

There’s lots of different directions for you

Too many to cover, but I’ll try a few

You could talk like a pirate

And learn to speak R

Statistics, and Science!

I’m sure you’ll go far

Additional queries for XML and JSON

Built in SQL Server, the latest edition.

You can learn HiveQL, if Big Data’s your thing

And interface with Tez, Spark, or just MapReducing

U_SQL is the language of the Azure Data Lake

A full-functioned dialect; what progress you could make!

There’s LINQ and C-Sharp, and so many more

Ways to write your code against the datastores

You could write streaming queries against Streaminsight

And answer questions against data in flight.

And lest I overlook, or lest I forget,

There’s products and processes still to mention yet.

SSIS, SSAS, In-memory design

SSRS, DataZen, and Power BI

All of these things, all of these tools

Are waiting to be used, are waiting for you.

You just start down the path, a direction you know

And soon you’ll be learning, your brain all aglow

And, oh, the places you’ll go.

And once you get there, wherever you go.

Don’t forget to write, and let somebody know.

Blog, tweet, present what you’ve mastered

And help someone else get there a little faster.

Feel free to leave a comment if you like, or follow me on Twitter: @codegumbo

#DevOps “We are all developers”

https://youtu.be/RYMH3qrHFEM

While thinking about the Implicit Optimism of DevOps, I started running through some of the cultural axioms of DevOps; I’m not sure if anyone has put together a comprehensive list, but I have a few items that I think are important. Be good at getting better is my new mantra, and now, I’m fond of saying “We are all developers”. I remember eating lunch at SQL Saturday Atlanta 2016 listening to a database developer describing this perspective to a DBA, and hearing how strongly the DBA objected to that label. I tentatively agreed with the developer, but recently, I’ve gotten more enamored with that statement.

Having worked as both a developer and an administrator, I get it; there’s an in-group mentality. The two sides of the operational silo are often working toward very different goals; developers are tasked with promoting change (new features, service packs, etc). DBA’s are tasked with maintaining the stability of the system; change is the opposite of stability. Most technical people I know are very proud of their work, which means that there’s often a desire for accuracy in the work we do. If a DBA is trying to make a system stable, and you call them a developer (think: change instigator), then it could be perceived as insulting.

It’s not meant to be.

Efficient development (to me) revolves around the three basic principles of:

  1. Reduce – changes should be highly targeted, small in scope, and touch only what’s necessary.
  2. Reuse – any process that is repeated should be repeated consistently; and,
  3. Recycle – code should be shared with stakeholders, so that inspiration can be shared.

From that perspective, there’s lots of opportunities to apply development principles to operational problems. For my DBA readers (all three of you), think about all the jobs you’ve written to automate maintenance. Think about the index changes you’ve suggested and/or implemented. Think about the reports you’ve written to monitor the performance of your systems. Any time you’ve created something to help you perform your job more efficiently, that’s development.

DevOps is built on the principle of infrastructure as code, with an emphasis on giving developers the ability to build the stack as needed. Google calls its implementation of DevOps principles Site Reliability Engineering, and characterizes it as “what you get when you treat operations as if it’s a software problem”. Microsoft is committed to DevOps as part of its application lifecycle management (although it’s notably cloud-focused). When dealing with large-scale implementations, operations can benefit from the application of the principles of efficient development.

We are all developers; most of us have always been developers. We just called it something else.

#BigData is coming; what should SQL Server people do about it?

I’ve been presenting a lot on Big Data (specifically Hadoop) from the perspective of a SQL Server DBA, and I’ve made a couple of recent observations.  I think most people are aware of the fact that data generation is growing at a staggering rate, with some estimates as high as 44 zettabytes by the year 2020; what I think is lacking in the SQL Server community is a rapid movement among database professionals to expand their skills to highly scalable Big Data platforms (like Hadoop) or streaming technologies.  Don’t get me wrong; I think there’s people out there who have made the transition (like Michelle Ufford; SQLFool, now Hadoopsie), and are willing to share their knowledge, but by and large, I think most SQL Server professionals are accustomed to working with our precious relational system.

Why is that?  I think it boils down to three reasons:

  1.  The SQL Server platform is a complex product, with ever increasing opportunities to learn something new.  SQL 2016 is about to drop, and it’s a BIG release; I expect most SQL Server people to wrap themselves up in new features and learn something new soon.  There’s always going to be a need for deep expertise, and as the product continues to mature and grow, it requires deeper knowledge.
  2. Big Data tools are vast, untamed, and very organic.  Those of us accustomed to the Microsoft development cycle are used to having a single official product drop every couple of years; Big Data tools (like Hadoop) are open-source, prone to various forks, and very rapidly developed.  It’s like drinking from a firehose.
  3. It’s not quite clear how it all fits together.  We know that Microsoft has presented some interesting data technologies as of late, but it’s not quite clear how the pieces all work together; should SQL Server pros learn Azure, HDInsight, Hadoop?  What’s this about U-SQL?  StreamInsight, Spark, Cortana Analytics?

The first two reasons aren’t easily solved; they require a willingness to learn and a commitment to study (both of which are difficult resources to commit).  The third issue, however, can be easily addressed by the following graphic.

This is Microsoft’s generic vision of a complete end-to-end analytics platform; for the data professional, it’s a roadmap of skills to learn.  Note that relational engines (and their BI cousins) remain a part of the vision, but they’re only small pieces in an ever-increasing ecosystem of database tools.

So here’s the question for you; what should SQL Server people do about it?  Do we continue to focus on a very specific tool set, or do we push ourselves (and each other) to learn more about the broader opportunities?  Either choice is equally valid, but even if you choose to become an expert on a single platform in lieu of transitioning to something new, you should understand how other tools interact with the relational system.

What are you going to learn today?

Coding naked.

Me, circa 1992. This is not the most embarrassing picture of that time period.

Me, circa 1992. This is not the most embarrassing picture of me from that time period.

True story: when I was in college, I was the lead singer (if you can call it that) for a goth rock Christian rave band called Sandi’s Nightmare.  I had zero musical talent, and relied solely on the strengths of my band-mate (Brad; you still rock); however, while my singing ability was dubious at best, I was pretty theatrical.  We had a total of 3 awesome shows before we moved on to other things, but it was a lot of fun while it lasted.  Our biggest hit was a cover of Lust Control’s Dancing Naked, a fantastic little number about David’s celebration of the ark returning; his passion and excitement overwhelmed his embarrassment.

I promise, I’m not making this stuff up; it was pretty edgy for the Bible belt of  Northeast Louisiana in the early 90’s.  I mean, I had an earring and everything.   The point of this post (besides reveling in my musical abilities and tastes of 25 years ago) is that sometimes we just need to be naked.

Let me explain; I made choices that in hindsight are difficult to defend (like my haircut). However, there was a reason for the choice at the time, and even though it may not have been the greatest idea, I’ve learned something from every experience I had.  While there are things that I would do differently if given the choice and knowledge I have today, I don’t regret many things.  That’s what the metaphor of nakedness is all about; being open and honest about not only your successes, but your choices along the way.  You can’t change the past, but you can learn from it.

Becoming a Naked Developer

I’ve spent most of my professional career as a SQL developer working for one company (13 years in November);  I was the first developer hired by that company, and my job was to build an integration platform transforming syslog data into reports, all on a shoestring budget.  I strung something together in a couple of weeks, and in the first month, I took a process that normally took two weeks to do by hand down to a day; my boss threatened to kiss me (I was flattered, but declined).

Was it pretty (the code, not the boss)?  No.  Was it successful?  Yes.

Fast forward a couple of years to 2004: I’m a member of small team building a solution to analyze more data than I’ve ever seen in my life (terabytes of data from firewalls and Windows machines); we had to import and semantically normalize data in near-real-time.  We built a solution using DTS, and then moved it to .NET and MSMQ.

Was it pretty? No.  Was it successful?  Yes.  Part of that code is still being used today, and my company has thrived off of it.  We were acquired by a larger company 8 years ago, and we’ve kept moving forward.

Every few years, we’d talk about doing a rewrite, and we’d bring in some outside expertise to evaluate what we’ve done and help us come up with a migration plan.  Some consultant would come in, look at what we did, and shake their heads at us.  Each time, we (the remaining original developers) would attend meeting after meeting as our code was dissected and judged; we sat naked (metaphorically) in front of a team of experts who told us everything that was wrong with the choices that we made.  At the end of each visit, we’d regroup and complain about the agony of judgement. In all honesty, we would have done things differently, given our current experience (and modern technology).  However, the simple truth is: we won the day.  We continue to win the day.

During some of these initial code reviews, the consultants were jerks.  They’d spend a lot of time bemoaning how horrid everything was; I’d be embarrassed and try to explain what I would have done differently if only I had the time.  The consultants would eventually propose some complex solution that would cost hundreds of thousands of dollars to implement.  Our company would consider the offer, stick it in a file somewhere, and we’d keep on keeping on.   Clunky code that works is better than expensive code with the same outcome.

Over time, I learned to live with the embarrassment (as you can tell from this post); I started listening to what the consultants would say and could quickly figure out which ones had good advice, and which ones were there to just be seagulls.  People that focused on solving the immediate problems were much easier to talk to than the people who wanted to rip out everything and start over.  Don’t get me wrong; sometimes you need to start over, but there is  a cost associated with that decision.  It’s usually much easier to start where you are, rather than trying to reinvent the wheel.

What’s my point?

First and foremost, don’t dwell on your past mistakes.  If you can honestly say that you built something that worked within the constraints of the time period, no matter how clunky or silly it seems now, then it was the right decision.  Working software works.  Laugh at it, learn from it, and let it go; repair what you can, when you can, but keep moving forward.

Second, learn to code naked.  Whatever it is that you’re trying to do at the moment, share it.  Open yourself up to code review, to criticism; exposure brings experience.  Granted, you’ll meet some seagulls along the way, but eventually, you’ll figure out who’s there to help you grow as a developer.  Foster those relationships.

Third, encourage people to grow; recognize that the same grace you desire for your coding mistakes is applicable to others as well.  Be kind, be gentle, but be honest; seek to understand why they made the design choices they did, and be sensitive to the fact that if it works, it works.  Sometimes we get caught up on trying to figure out the right way to do something, and ultimately end up doing nothing because it’s too difficult to implement it according to the textbook.

Life is too short to not enjoy what you do; try to share that joy without embarrassment.

THIS is the most embarrassing picture of me from that time period.  I thought being an actor was the best way to meet girls… Yes, those are tights I am wearing…