This month’s T-SQL Tuesday comes from Todd Kleinhans (blog | twitter) who wants to know what we’re doing with databases outside of work.
I’m curious- outside of work and learning, what do you personally use databases for? Tracking books you have, recipes, collections, etc? While it can be said using databases for personal use could be either overkill or a hammer in search of nails on the other hand, it is exactly what they are for- storing data.
This month’s T-SQL Tuesday comes from Shane O’Neill (blog | twitter). He starts us off with this:
Dipping into the Cookie Jar is about when the going gets tough and you don’t think you can handle anymore, then you think back about your accomplishments and take some sustenance from them. You dip back into that cookie jar and use whatever energy that provides to keep going.
So tell me about a time when you had an accomplishment that can keep you going.
I. Love. Cookies.
I feel like this could describe my entire career. I cannot tell you how many times I’ve found myself staring looking at a new project, thinking “how on earth am I going to pull this off? This is nuts!”
I do exactly what Shane describes above – I just didn’t know there was a name for it. I have to remind myself “hey, you’ve said this same thing before, and it worked out. Breathe, slow down, and you will figure it out. You always do.”
For the really gnarly problems in the past, I’ve hidden away for a few days to just hammer away at code. So, what do I do when I get to the next one? I remind myself that if I do the same thing, I’ll get through.
Here’s the thing
In this business, you should never be doing the same thing over and over. Those are the things you automate so you can move on to more interesting problems.
There should be something coming along new every week. If there isn’t, make something new happen. If all you’re doing is things you’ve done before, things that you already know exactly how to do, you’re going to stagnate. You’re not going to learn. You’re not going to grow.
For me, it’s all about breaking the problem down in two ways:
Find parts that you know you know how to do
Find ways to experiment with the parts that you don’t know how to do
The first one is your cookie jar. Break that project down and tackle the stuff you have seen before. Maybe you’ve even got a time-tested script or checklist to get you through it.
On to the stuff that’s new. This is the exciting part! You won’t get it right on the first try. Everyone falls the first time. That’s how we learn. That stuff that’s old hat to you in the paragraph above? There was a time you didn’t know how to do that, either. And now look at you – you’ve mastered those parts.
An Example
Right, Shane wanted real examples, didn’t he? Time for me to go back to the well that is my 8000-database migration. Let’s break that down:
Upgrading from SQL Server 2008R2 to a newer version? High-level, I’ve done that before. Had a sysadmin doing most of the heavy lifting, but I was right there with him for most of it. 🍪
Oh, but wait! I worked on an upgrade from 2000 to 2008R2 before that. We kept the whole workflow document and checklists around from that so we built on that. 🍪
When I did that previous migration, I successfully copied things in groups, I didn’t have to do it all at once. Just like the big migration to 2016. 🍪
As an organization, we’d previously moved our 2008R2 setup to new server hardware, with minimal issues. We learned from the breakage we had and documented those lessons. 🍪
When I stood up the new test 2016 instance, I copied everything but the databases from the 2008R2 test instance and that worked out OK. 🍪
So, what was I left with here for the big migration? I’ve been through version upgrades. I’ve been through scripted migrations. I’ve copied everything except databases from 2008R2 to 2016. We’ve changed hardware before. So many cookies!
The only part that I was really worried about was attaching the databases themselves. As a result, I was able to test that out a couple dozen times to make sure it would work properly. And no, I didn’t get it right on the first attempt – it took probably a dozen attempts before I got the attach script working properly. But I knew it would be OK, because I’d worked on plenty of other projects where the first attempts were unsuccessful as well.
Keep at it
You didn’t give up in the past, and you found success. So keep plugging away and you’ll keep succeeding.
Malathi Mahadevan (b|t) is hosting this month’s T-SQL Tuesday, a monthly blog party for the SQL Server community.
Malathi has asked us to:
Pick one thing you want to learn that is not SQL Server. Write down ways and means to learn it and add it as another skill to your resume. If you are already learning it or know it – explain how you got there and how it has helped you. Your experience may help many others looking for guidance on this.
The timing for this works out well for me, as I’ll be working on my 2019 goals very soon and education is part of those. But right now, we’re kind of a homogenous shop, which makes stretching beyond SQL Server a bit more effort. I’m occasionally indecisive, so I’m going to throw out a few ideas of things to learn in 2019.
Need to learn for work: Postgres on AWS. We have a pilot project starting up using this and eventually, my team will need to support it. So, we need to at least understand the basics – backup & restore, basic monitoring/troubleshooting.
Want to learn on my own: A document database of some description. I’ll probably land on MongoDB due to convenience and cost (or lack thereof). Runner-up: DynamoDB.
Stretch goal: I still don’t entirely get the concepts behind Docker or Kubernetes (although I’m close on Docker). I’ve tried to run SQL Server 2017 on my Mac via Docker, but Docker itself seems to make the machine unstable. Kubernetes is a complete mystery to me; I know a few people who are doing “stuff” with it, but haven’t talked with them about it yet.
But you may be asking “Andy, where’s Azure? CosmosDB is a document database on that platform. And PostGres is on Azure too, why not learn it there?” My shop is starting to do new work on AWS, so the opportunities are more available there and I can apply what I learn at work more readily. So it’ll cost me nothing out of pocket if I go with that or MongoDB. As for the learning plan:
I’m going to have to do this one for work regardless, so step one will be setting aside time at the office to work through the AWS tutorials and documentation. Then set up a few test databases, throw some data in, and play around.
I know we have a few MongoDB experts locally and a user group, so hopefully they can get me started in the right direction. I’ve got a Raspberry Pi 3B as well as a Pi Zero W that I can put to use here.
Apparently one can run MongoDB on Docker with Kubernetes so maybe I can kill two birds with one stone? It could be an excuse to pick up a couple more Pis. Or maybe fire up some VMs on the rack mount server that’s been sitting in my basement for 5 years.
MongoDB & Docker/Kubernetes are completely new ground for me to cover, so just figuring out where and how to get started may be a challenge. By the time publishes, I may already be starting to read the MongoDB manual en route to PASS Summit – and I know I can talk to people about this stuff at Summit to get ideas on where to start.
Addendum
This post was written published early by accident. My reading of the MongoDB manual didn’t happen last Tuesday as I was not feeling well on my flight to Seattle.
This month’s T-SQL Tuesday is hosted by Jeff Mlakar and he asks us to write about a project that went horribly wrong. My story isn’t really worthy of the name “death march” but it was a pretty rough project.
The project started sometime in mid-2003. I was working as a web developer (Classic ASP) for an insurance company and they wanted to modernize the underwriting process with a web-based “workflow” application.
A platform was selected, the team was picked, and we set about customizing the devil out of it (it was more of a “framework” than turnkey application) and integrating with our existing systems. Of course, being an insurance company everything ultimately had to land in the mainframe.
The tech team was pretty large – upwards of a dozen and a half programmers across various disciplines. Four of us were kind of off on our own building the ASP front end and COM-based middleware – myself and Ned on the ASP side, Tony & Phil (all names changed) on the other side of the cube walls working on COM objects. The four of us worked really well together, with Phil & I each taking the lead in our respective disciplines. Our PM was great; we got along well and she knew the right questions to ask me to nudge me in the right direction.
As Phil & I dug into the platform API, we realized we were going to be implementing more features with our own code than expected as they weren’t actually built into the API yet.
We brought a few consultants from a large company you’ve certainly heard of to help us work out a UI design. After 5 weeks, we presented our design proposals. Every – I mean every – proposal put forth by the consultants and me was shot down. The response was “those are very nice, but we want what we described to you 2 months ago, so build that.” I was flabbergasted. I asked the consultants if they’d ever seen anything like that and they admitted that they hadn’t. We just spent five weeks and a bunch of money to produce nothing.
February was approaching and while Phil, Tony, Ned and myself were firing on all cylinders, others weren’t doing as well. We were supposed to launch in March, but it was becoming apparent that we’d miss that date. The release date slipped to mid-April.
The four of us spent the last half of February and all of March tightening up our code. We had some performance concerns but hadn’t figured out where they were coming from yet. Acceptance testing hadn’t started yet; the project was expected to be “nearly complete” before that’d start.
Acceptance testing started in late March. Ned & I knew our code inside and out, and I could suss out pretty easily if a reported issue was there or in the COM objects so I could throw those bugs over the wall to Tony & Phil as necessary.
I returned from lunch on the last Friday of March and Ned said to me “I’m leaving at the end of the day.” Um…OK, thanks Ned, I am too. Then he clarified. See, Ned was a contractor and he he was ending his contract at the end of the day – and starting a new gig in Miami on Monday! We were unable to bring in a replacement, so I was flying solo for the rest of the project.
I spent April fixing bugs as fast as I could, re-implementing some features because they sounded good on paper but once seen in action, they weren’t acceptable. Mid-April came around and the project release date was pushed back by another month.
I went to the PM and expressed my concerns about the release date changes. I reminded her that I was getting married Memorial Day weekend and I’d be unavailable for 2 weeks due to that and the honeymoon. She assured me that this was the last reschedule, and even if that turned out to not be true, I wouldn’t be asked to change my plans.
Early in May was the final blow for me. We had an all-project meeting with several people from the highest levels in the company in attendance. The missed deadlines, the incredible strain placed on not only the development team but their families, the importance of the project to the company, all brought up. People laid some very personal feelings out. The response from one of the top-level people was cold, harsh, uncaring, impersonal, tone-deaf, and completely disheartening.
The project went live 10 days before I left for my wedding and honeymoon.
Epilogue
No project implementation is perfect out of the gate. Right before we went live, Phil figured out what was causing our performance issues and over the next month or two, more or less re-implemented that portion of the vendor’s API himself. We pushed out an updated version as quickly as we could; users were complaining about performance from day one. We got a lackluster “yeah, I guess it’s better now” but no real acknowledgement of the improvement Phil had made so quickly. It was too late; the perception was that this system is slow and that wasn’t going to change, now matter how fast we made it.
The project was started in the summer or early Fall of 2003 and went live in the last third of May 2004. By the end of the first quarter of 2005, the company had decided to go in a different direction and the system was on track to be mothballed; no new business was being scanned into it, and once the last piece of business that was in it was processed, everything was shut down.
The system spent more time under development than it did in use.
This month’s T-SQL Tuesday is hosted by Bert Wagner and he asks us to write about code we’ve written that we would hate to live without.
First off, “hate” is a pretty strong word so let’s go with “code you couldn’t bear to live without”. The first bit of code I couldn’t live without is reviled in some circles. More often it’s misunderstood and lamented, or…well, I’ll just show it to you.
Yes, you read that right. It’s an SSMS Snippet that generates a cursor. I use cursors so often that I decided to create a snippet so I don’t have to rewrite them or copy/paste from a template file all the time.
Yes, really. Don’t @ me, come at me bro, whatever it is the kids are saying these days. I am dependent upon cursors every day and would be lost without them.
Wow Andy, you must be pretty bad at your job if you’re running cursors all the time! Don’t you know that’s terrible for performance? What’s up with that?
If we’ve met, either in-person or virtually on the SQL Community Slack, you probably know that I manage an instance hosting mumblemumblethousand databases. I don’t mean “a couple” thousand databases; we’re looking at Michael Swart’s 10 Percent Rule in the rearview mirror. I regularly have to look for a particular value across the same table in a few dozen/hundred/thousand databases, or pull a report across as many databases, or run the same data change for a big list of databases. Most often, I’ll be given a list of databases or be asked “run this for all the databases that meet these criteria.” And the only way to do that easily is via a cursor because I have to first collect the list of databases from another table. There are 3rd party tools I could use but doing the setup to run against an arbitrary list of databases is tedious, error-prone, and I haven’t quite worked out a way to improve it yet.
Processing a table or result set RBARis a performance concern. But to crank through a long list of databases and execute the same query against each it’s the only way to go, as far as I know. sp_msforeachdb doesn’t cut it for my purposes because I don’t want to hit every database on my instance.
My second piece of code is more of a technique or design pattern. In a stored procedure or large script with dynamic SQL, I’ll often create two variables (they’re parameters, in the case of stored procedures – choose a sensible default!) called @Debug and @ImSure. They’re just bit types but I use them to control the output of debgging information and code execution.
By doing this, I don’t have to comment/uncomment sections of code all the time just to see what dynamic SQL I’m generating. I also have a failsafe which prevents changes from being executed until I’ve made sure that everything is solid.
Those are probably the two pieces of code that I can share which I couldn’t be without. Honorable mentions which I didn’t write but find indispensable:
QUOTENAME – pretty basic T-SQL function but with all that dynamic SQL I’m writing, I need it to keep my SQL clean and safe.
dbatools – I’ve written about this PowerShell module quite a bit here but suffice to say for doing bulk administrative tasks, collecting metadata about the environment for analysis, and moving databases or entire instances around, it’s a lifesaver.
Brent Ozar Unlimited’s First Responder Kit – I run sp_blitzcache & sp_blitzindex daily looking for places we can tweak our code or indexes to make things run better.
Adam Machanic’s sp_whoisactive – Gives me a great lightweight snapshot of what’s going on right now.
Thanks to Eugene Meidinger (b|t) for nudging me in the direction of posting this. Ever since this month’s T-SQL Tuesday was announced, work and non-work has been a complete whirlwind and I have failed miserably at reaching sqlibrium.
I’ll expand on this in later posts and link back. Really.
It’s time for T-SQL Tuesday and this month’s edition is hosted by Ewald Cress (b|t). It’s non-technical this month because we’re all recovering from PASS Summit. Ewald asks us to:
give a shout-out to people (well-known or otherwise) who have made a meaningful contribution to your life in the world of data.
This post is both difficult and easy. Difficult because there are so many people in the community whom I’ve learned from. But easy because there are a
I met Allen in the winter of 2011. Allen was a consultant and my employer engaged with his firm for help with a SQL Server migration/upgrade and DBA services. Allen introduced me to PASS, the SQL Server community, and helped me discover a passion for the field. I had previously dabbled with SQL Server, and tried to pick up what I could on my own and from random internet searches, but I spent a lot of time fumbling around and learning bad habits. Allen got me pointed in the right direction and drew me into the #SQLFamily. He is one of the first people I look for every time I go to SQL Saturday Cleveland or PASS Summit.
A little over a year after I met Allen, Kendal joined his team and I started working with him. Kendal became our primary DBA and when we had difficulties with SQL Server (or, more accurately, our application was giving SQL Server agitation) Kendal was our first call. I worked on a number of issues with Kendal; he knows SQL Server inside and out, and I was deeply familiar with the application that was giving it trouble so we made a good combination and I learned a lot about troubleshooting/debugging and some creative ways to resolve/work around application issues that can’t be fixed at the application layer. Kendal even hosted my first-ever blog post! Every now and then, something pops up on Twitter and we reminisce about the good ol’ days.
I met Matt when I started attending my local user group meetings (or maybe it was at PASS Summit 2012, after which I started going to the local meetings). He somehow talked me into helping him run the local SQL Saturday, then talked me into taking over running SQL Saturday. As I described in my post earlier this year about how I became a DBA, he gave me that final nudge to say “yes, I want to be a DBA now. It’s time to make it happen.”
I met Chris through our local user group and it turns out we know a lot of the same people despite never having worked together; there aren’t a lot of SQL Server folks in town! Chris took a new job around the same time I changed jobs earlier this year so we’ve been out for quite a few lunchtime runs and walks where we discuss various technical (and non-technical) challenges we face at work.
Chrissy LeMaire, Rob Sewell, Constantine Kokkinos, Shawn Melton
Newcomers to this list. This group (and really, I can’t single any one of them out) has welcomed me into the dbatools team and been very patient with me as I’ve learned GitHub, learned how to work within the goals/parameters of dbatools, and let me fire off a lot of pull requests just to do things like spelling corrections. It’s a whole new piece of the SQL Server community they’ve helped me get exposure to, and at Summit this year I was shocked when I had people coming up to me saying “hi, I wanted to meet you” or “hey, you work on dbatools? I need some help…” My Impostor Syndrome has been working overtime here; it’s been a few months now and I still can’t believe they let me work on this stuff. I’ve been honing my own PowerShell skills, learning more about dbatools, and reinforcing what I thought I already knew by reading so much of their code as I work on my various fix-ups – and even making real code changes!
To everyone named here, and the whole SQL family, thank you. If you have ever attended or organized a SQL Server-related event – SQL Saturday, Summit, User Group meeting, webinar, or blog party – you have had an effect on me. My career would not be where it is today without you.
This month’s T-SQL Tuesday is hosted by Rob Sewell and he’s posed the following question:
What are you going to automate today with PowerShell?
I’m cheating a little bit in that this is something I did a couple weeks ago, but it was immensely helpful. I’d been working on building out a new instance to migrate our test databases onto, but the developers had an urgent need to do some testing in isolation so they “borrowed” that new instance. But we had an additional requirement – the configuration needed to match production as closely as possible, more than our current test instance. Of course, I reached for Powershell and dbatools.
I started with Get-DbaSpConfigure to retrieve the settings available from sp_configure as these were the most important to my comparison. I ran this against production as well as each of my test instances and saved the results of each to a variable. Because accessing my production instance requires either jumping through hoops or using SQL Authentication, I passed -SqlCredential (get-credential -Message "Prod" -UserName MySQLLogin) so I’d be prompted for that password instead of using Windows Authentication.
My configurations saved for reference, I can now look at one of the objects returned to see which properties need to be compared:
ServerName : TEST1
ConfigName : AdHocDistributedQueriesEnabled
DisplayName : Ad Hoc Distributed Queries
Description : Enable or disable Ad Hoc Distributed Queries
IsAdvanced : True
IsDynamic : True
MinValue : 0
MaxValue : 1
ConfiguredValue : 0
RunningValue : 0
DefaultValue : 0
IsRunningDefaultValue : True
Looks like I want to be checking out ConfigName and RunningValue. ConfigName is the same name that you’d pass to sp_configure. PowerShell comes with a handy function Compare-Object which (you guessed it!) lets you compare two objects and reports the differences.
Hmm…that’s no good. I know there are differences between test and production – for one, production has about 24 times the amount of RAM test has. I took to the SQL Community Slack for help, and was reminded that Compare-Object by default doesn’t do a “deep” comparison on PSCustomObjects, so you have to specify which property(ies) you want compared. In this case, RunningValue. So, passing both ConfigName and RunningValue into Compare-Object (the former so that I’d know what was being compared), then sorting the output, I was able to readily see the differences.
The value corresponding to the left-pointing arrow is what came from the reference object, and the right-pointing arrow is the value from the difference object (which instance is the “reference” in this case isn’t terribly important, as long as you remember which is which). So MaxDOP and MaxServerMemory are both higher in production – which is expected.
If we really want to get crazy, we can even make this a one-liner. But I don’t recommend it.
Running this against my second test instance as well let me quickly deliver the news to the developers that the instances were configured as closely as possible, with any differences being limited to the hardware/environments they were in which is not something we were in a position to address.
This month’s T-SQL Tuesday is hosted by Raul Gonzalez and he’s asked everyone to share things we might be a bit embarrassed about:
For this month, I want you peers to write about those important lessons that you learned the hard way, for instance something you did and put your systems down or maybe something you didn’t do and took your systems down. It can be also a bad decision you or someone else took back in the day and you’re still paying for it…
In the stress/performance testing portion of an upgrade of a critical system, we were short on disk space. So, rather than having a separate set of VMs for the performance testing (as we needed to be able to get back to functional testing quickly), we decided to just take VM snapshots of all the servers. Testing was delayed a day or two – but we didn’t switch off the snapshots. Then we started testing and performance was terrific…for about five minutes. Then everything came to a screeching halt. Panicked, we thought we were going to need a pile of new hardware until the VMWare admin realized that our disks were getting hammered and we still had those active snapshots. Lesson learned: If you take VM-level snapshots of your database server and let them “soak” for an extended period, you’re gonna have a bad time. Unless you need to take a snapshot of the host OS or instance configuration itself, use a database snapshot instead of a VM-level snapshot.
A couple of times, I’ve had under-performing VMs running SQL Server. As I hadn’t been involved in the configuration, I thought everything had been provisioned properly. Turns out…not so much. Memory reservations, storage configuration, power profiles, all set up for suboptimal performance. Lesson learned: Ask your VMWare admin if they’ve perused the best practices guide and review things yourself before going down the rabbit hole of SQL Server configuration & query tuning. If the underlying systems aren’t configured well, you’ll spin your wheels for a long time.
In doing a configuration review of a rather large (production) instance, I noted that at least one configuration option was still set to the default value – Cost Threshold for Parallelism was stuck at 5. Running sp_BlitzCache, I found that I had quite a few simple queries going parallel and huge CXPACKET waits. CXPACKET isn’t bad per se, but if you’ve got a low-cost query that’s going parallel and waiting on threads where it could be running faster overall single-threaded (verified this was the case for several of the top offenders), increasing the cost threshold can help. I did some checking, verified that it was a configuration change I could make on the fly, and set the value to 50. And then everything. Slowed. Down. When I made this configuration change on the test instance, it wasn’t much a problem. But that was a much smaller instance, with much less traffic. What I failed to fully comprehend was the impact of this operation. I overlooked that changing this setting (and a number of others I wasn’t aware of) blows out the plan cache. In the case of this instance, about 26Gb of plan cache. Not only was performance impacted while the plan cache was re-filled, we took a hit while all the old plans were being evicted from cache. Lesson learned: Even if it seemed OK in test, those “low impact” changes can have a much larger impact on production unless you can make test mirror production in every way. So plan when you make these changes accordingly.
We learn the most from our mistakes. We can learn almost as much from the mistakes of others. Learn from mine.
This month’s T-SQL Tuesday is hosted by Brent Ozar and he’s asked everyone to find interesting bug or enhancement requests in Microsoft Connect related to SQL Server.
The Connect item doesn’t have to have anything to do with T-SQL – it could be about the engine, SSRS, R, the installer, whatever. Now, more than ever, Microsoft has started to respond to Connect requests and get ’em fixed not just in upcoming versions of SQL Server, but even in cumulative updates for existing versions.
Confession time: At the moment, I’m not a DBA (I’m a dev who occasionally does some DBAish stuff), and I don’t spend a lot of time on Microsoft Connect.
However, I do spend a bunch of time in Powershell, fetching data to process/analyze/mangle for people on an ad-hoc basis, running scheduled tasks, or doing research trying to find things in an application’s code (stored in the database). And I frequently find myself using Invoke-SqlCmd.
I found two open items which, although addressed by Invoke-SqlCmd2, would be of benefit to the community at large if resolved in the SqlServer module.
Powershell invoke-sqlcmd should support parameterized queries – It’s 2017 and we’re still reading about SQL injection attacks caused by developers improperly escaping text input. Parameterized queries have been around for a very long time and go a long way toward mitigating this vulnerability. Unfortunately, with Invoke-SqlCmd‘s current state it can only accept a string as the query to be executed (or an input file), and that query is frequently created by Powershell users by either concatenating strings or doing text replacement. Even in cases where I’ve created all of the content being concatenated or replaced, I still don’t fully trust my input.
Invoke-Sqlcmd does not seem to meaningfully support the -Verbose or -Debug switches – Most Powershell cmdlets allow you get additional information about the cmdlet’s execution sent to the Verbose and Debug output streams, but Invoke-SqlCmd does nothing. Seeing the connection string and other details about the query’s execution would be helpful in some cases. I’d like to see this request expanded to add the -WhatIf switch as well so that when called from scripts that properly support SupportsShouldProcess, the query isn’t actually executed if that switch is specified.
I had planned on logging a Connect item very similar to Mike Fal’s (b|t) request for passing a connection string to Invoke-SqlCmd, but logging a duplicate would be pointless that’s already been implemented (I warned you that I don’t spend a lot of time on Connect, I didn’t even remember it was out there)! I still wouldn’t mind being able to pass in just an Application Name to append to the connection string, but that may lead down a path of making too many options to fiddle with in the cmdlet. It’s a tough balancing act when you have an audience as large as the SQL Server team’s.
