Voice of the DBA

Tag: T-SQL Tuesday

T-SQL Tuesday #89–Changing Times

This month the invitation is from Koen Verbeeck and it has to do with the cloud changes coming to the data world, especially SQL Server. That’s an interesting topic, not necessarily technical, but it is an interesting one to think about.

I’m going to be a little short because life is busy, but I think this is an interesting item to keep an eye on for many people. Not necessarily at your current position, but what if you need to move on?

If you’re interested, I host all the T-SQL Tuesday topics at tsqltuesday.com.

The Cloud is Changing Things

I sat in a talk at SQL Bits from Conor Cunningham of Microsoft. He’s one of the principal architects of SQL Server and Azure SQL. In the talk he talked about some interesting ideas in how SQL Server engineering has changed in the last decade. Victoria Holt wrote a short piece on some of the things Conor talked about.

There are a couple interesting things that the cloud is enabling. First, Microsoft runs their cloud without any Ops team, really having developers be responsible for things in production. This is 1.7mm databases, without any DBAs. Why? They gather lots of data, so they learn when things are broken, unstable, or problematic. They do this with the 600TB of telemetry they gather every day.

Of course, you and I won’t have that much information, but the cloud does enable Microsoft to think about how to make SQL Server more stable, and also how to add automation capabilities into the product. We haven’t seen much of this change in current versions, but the Query Store is the start of one thing, and Adaptive Query Processing (coming in v.Next) is another. I wouldn’t be surprised to see more, and that means our jobs as DBAs will change.

I think there will be less, check, configure, verify, maybe even some less tuning work for DBAs. There will always be developer needs, especially with more complex reporting, visualizations, and just understanding large data sets. There will also constantly be the need to write better SQL as the optimizer can only do so much with bad queries.

The cloud interests me and excites me. There are issues, concerns, and challenges. However, I also find working with Azure through Powershell, being able to access different services from various places, keeping some data there (non PII) and avoiding the need to manage infrastructure to be key.

I don’t know if it will happen, but I would hope at some point that the Azure cloud, the AWS cloud, the Google cloud would license their service, or even allow others to resell and manage portions to encourage competition and give us some choice in who we might choose to deal with. If so, they I could see more and more companies just considering moves to the cloud for more data, especially when there could be different levels of service and protection for different needs.

The cloud is changing things, even if you aren’t in the cloud. That can be opportunity if you take advantage of it.

April 11, 2017
TSQLTuesday.com – Quick WordPress Setup in Azure

When I agreed to host the T-SQL Tuesday site, I wasn’t sure what to do. I thought about just putting up a static page, one I’d edit, but in some conversations with Adam Machanic, he wanted to have a few people able to manage the site. That makes sense and means I have less to do.

My first thought had been to build a SQL database, set up a web page, maybe add some logins for people, etc. Sounds great, but that can be a lot of work, and while I don’t mind some projects, I don’t want a big project that’s just a small service. Especially for data that’s really just historical logging.

When I woke up one morning and found a domain transfer request, I felt some pressure to get something up quickly. One of the great things about using domains on the web is that I could build something quick, point the domain to it, and build something else in the background, moving the domain later. There might be redirect issues, but in this case I expect most people just hit the domain, tsqltuesday.com, so I decided to focus there.

Azure Websites

I had experimented a bit with Azure websites, setting a few up, and thought this might be easy. In fact, it was really easy, so I decided to write a bit about the experience. Note, this is what the experience is like in Mar 2017. It could change quarter to quarter, so use the process and images as a rough guide.

To get started, log into your portal. If you don’t have an Azure account, google how to create one, set it up, and then come back.

Once you have an account, click New and choose .

From here, pick “See All” and then find WordPress.

Once you select WordPress, you just click Create.

I tried to keep this simple, and picked these options.

one thing I had to do was pick a size above the free service. This is because the free services don’t allow custom domains, and while tsqltuesday.azurewebsites.net works, it’s not as cool at tsqltuesday.com. Plus Brent had already transferred the domain to me, so I was on the hook.

Once this was done, I had the site created.

Once the deployment completes ,you have a blank WordPress site. You log in with your credentials and start configuring, just as you’d do on wordpress.com or with your own installations.

I took a few guesses on how to organize the site, but I’ll discuss those late. This is really about getting a quick site up, one for your business, for your blog, or whatever you’d like. This is my second WordPress site on Azure, and I’m amazed at how easy it is.

I’

March 24, 2017
T-SQL Tuesday #88 – WTF

It’s T-SQL Tuesday time again, and this is a good one. The host this month is Kennie Pontoppidan, who I had the pleasure of running with one morning in Copenhagen. His theme is The daily (database-related) WTF.

You can read about T-SQL Tuesday at tsqltuesday.com and see all the archived invitations. Join in the fun by watching the site, or the #tsql2sday hashtag on Twitter. If you want to catch up on old invites, feel free.

WTF

I’m not sure if I have a great story, but I certainly have had a few “WTF” items in my career. Some of them caused by me, some by others, and often not a good thing.

The one item that I’ll relate here goes back quite some years in history, but it was one that affected me for many years in my career. I do quite a bit of work with DevOps and software development these days, trying to help people build better software.

Part of that is ensuring your code, including your database code, is in a version control system (VCS). I’ve worked in a variety of environments, using different source code control methods, and I have to relate a story that taught me how important it can be to manage code and track the versions in production.

A long time ago I went to work for a small startup. The company had been in business for 5 or 6 years, and had a number of customers that depended on our service. However, our developers and admins were a mess. I was brought in to try and straighten out the the database and infrastructure side of our codebase.

One of the first things I found was that all our stored procedures in the production server were encrypted. I wasn’t sure why, since we hosted our machines, but that wasn’t a big deal.

Until it was.

One day we had an issue on one of our SQL Server 2000 servers (we had two, supposedly identical). In troubleshooting and putting some sample data in both systems for a fake customer, we got different results. Hmmm, not what I wanted to see.

I checked the VCS (SourceSafe at the time) and checked out the code. I then loaded my test data and … got a third, different result. Now I was concerned as this was a production bug that was delaying work for a customer.

This was early in my tenure at the company, so I asked the other DBA for some information. He had been passed over in favor of me by management and wasn’t a great deal of help. He told me that he likely had the source code for server 1 on his machine.

You might start to guess why this is a WTF post. Rather than get upset, I wanted to see the code, which he produced. His version was one one of the servers, but not the other. Where was that code? He thought it was on the lead developer’s machine, who conveniently, was on vacation. We checked, and it wasn’t in his checked out folder structure.

What about our former developers, who had moved out of state? We still had their machines, which were supposedly going to be shipped to them so they could continue working for us. The checkout folder wasn’t right, but we found 2 other folder structures, copies of the source code tree, with different code. At this point, I just needed to fix the issue, and managed to find a copy of the stored procedure that matched my issue, built a correction, and deployed it to both servers. Unencrypted.

Detective Time

At this point, I paused some work that the DBAs were assigned. We spent a day or so pouring over all developer’s machines, finding 6 or 7 different copies of source code (plus our SourceSafe install). Multiple files were different, and we essentially had no idea what was running in production.

Apparently our developers had decided that when they checked in code, they would leave it on their machines. If they wanted a branch, in essence, they’d copy this folder over, make changes, and sometimes copy things back, but mostly just assume they knew what was good, and leave the updates on disk, uncommitted to the VCS.

I couldn’t come up with a rational reason for this. Back then, we didn’t have any sort of integration like SQL Source Control and we checked code in and out of the VCS manually. I had previously set all my machines to remove the code from my local disk, ensuring I’d go to the VCS to get code when I needed to change something. I suspected this was one way of creating ”job security” by a few developers and DBAs. Hint: it didn’t work with me.

Solving the Issues

This necessitated a complete reboot of our system. Together with a new lead developer (you can guess where the old one went), we paused development for 3-4 days. I found a routine to decrypt SQL Server 2000 stored procedures, and we all spent time decrypting the stored procedures from both instances and committing them back to a new tree in SourceSafe. If we had an discrepancies, then we had at least two developers examine the code, merge the differences and commit code.

After a week of lost work, we had a clean source code tree of what was running in production, and both database servers synchronized. From there, we could start new development. Learning from the issues, I also would only deploy changes from the VCS (manually back then), which ensured the developers had an incentive to commit their changes and not just alter the database objects on the development server.

Of course they still did, and we had numerous deployment issues. If only we’d had a few better tools back then for CI and CD in the database world.

March 14, 2017
TSQLTuesday.com

I’ve hosted a page on this blog that has all the T-SQL Tuesday blog invitations and roundups linked. I did this mainly because I was trying to ensure I have hosted a few, I wanted to know what others had chosen. Since I didn’t see a good list, I created one.

And I’ve maintained it, which seems crazy since it’s been 6+ years.

Recently on a Twitter conversation someone suggested getting a central site, using a real domain. I thought that was a good idea, since my page isn’t necessarily the most intuitive item to find.

Somehow I ended up getting a domain transfer from Brent Ozar and getting talked into hosting the site. It’s live, now at:

TSQLTuesday.com

I did this with a simple Azure website, using WordPress. That’s another story, and I should blog about it, but for now, the site is live and running there. I’ve added Adam and Brent as administrators, so they can help maintain things.

I’m also open to suggestions on how to better organize things. I’d like to get a page for each entry, but we’ll have to see how that works.

February 28, 2017