Voice of the DBA

Tag: DevOps

More SQL Server Updates

This was a busy week for updates. I finished adding new builds to the build lists for 2017, 2016, and 2014, the first time I’ve done all three platforms on the same day. To be fair, two updates were on the 19th and one on the 20th, but that’s pretty close to the same day. In the past I’ve had updates for different versions during different months.

That will not be the case in the future. SQL Server 2017 is on the CU model where we get monthly updates to the product. No more service packs for this version. If you hadn’t heard that, be ready for the new servicing model that consists of just the monthly updates. I wasn’t sure if I liked this at first, but I do appreciate that we don’t get confused with trying to determine which CU1 is needed as there were different patches for RTM, SP1, SP2, etc. Now I’ve gotten used to expecting an update each month and applying them after a few days. So far, Microsoft has mostly been sticking to monthly releases, though the December release got delayed into January. With holidays, that’s expected.

This week also saw the update of SSMS to v 17.6. You can freely use this version and download the full install or just the update package if you are running 17.x. We are seeing updates on a fairly regular basis to the tool, every couple of months. I’m hoping we continue to see more fixes and improvements aside from new functionality as SSMS has been neglected for too long. I don’t know how stable this version is, but 17.4 was on my machines for a long time without an ill effects, and I think this seems fine for now.

Updates have always been a part of software, but often they are fairly rare, with interruptions occurring just a few times a year. That’s changing. Redgate releases software every week, with almost every product updating once or twice a month. For someone that uses relatively few pieces of software, all of a sudden it seems like I have a regular set of tools to update across my SQL Server development platform between SQL Server, SSMS, VS, Redgate tools and more.

That’s good, as I get fixes and increased functionality, but across a set of tools, I might find myself updating something every day. While the software often updates itself if I click OK, I have to make a decision and the process is disruptive and time consuming, especially when I run into required updates, which happens more than I’d like. I like the idea of DevOps, but one of the unintended consequences is that if everyone does it, there are a lot of changes and updates for users to deal with.

I don’t have a solution, but I do find myself delaying updates more and more, often moving to only take every 3 or 4 updates. Maybe if everyone had an easy way to apply patches with PoSh across systems, or smoother built-in mechanisms I’d feel differently, but for now I probably click “skip” more often than “apply”.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.7MB) podcast or subscribe to the feed at iTunes and Libsyn.

March 22, 2018
Are You a Traffic Cop?

There’s a joke that a DBA’s favorite word is “no”. I think that was certainly true for the individual from whom I first learned about Oracle and SQL Server administration. He constantly pushed back on developers and management who requested changes. He wasn’t rude or abrasive, though I’ve met my share of those individuals. Instead, he patiently would explain and debate the changes and his reasons for not wanting to make them. He had come onboard after a particularly bad deployment affected a significant portion of the company and while management listened to him, development wasn’t pleased.

One of the analogies I’ve seen for DBAs is that they are a traffic cop. They implement the rules necessary for the safety, protection, and availability of databases systems. This is sometime at odds with the pressure developers feel to implement new features and functions in their applications that require a database. This can, and often is, resolved by using some methodology like DevOps, where the DBA, the developers, and operations staff discuss changes in advance and collaborate to ensure that functionality will not negatively impact data quality, performance, or availability.

However, this can easily take a negative turn. People often have short memories, and we are all usually busy at work. When a DBA lets a change go through that causes problems, they are reluctant to let the next one go without a thorough review. They slow down and want to double check every new line of code, or worse, they start to second guess their own decisions to allow changes, which can further exacerbate the issues.

Are you a traffic cop as a DBA? I saw this referenced as a great analogy recently, where the DBA enforces rules, perhaps taking on a bit too much power, and using these rules to limit change. They lock down too much access, even preventing metrics and data from providing feedback to developers. They may even seek to strictly enforce security by the letter of the rule, not the spirit, upsetting and angering others.

I think good security is important, and often bypassing security for the sake of convenience or expediency is a source of issues, including data breaches. There are policies that are outdated or may not apply in specific situations, and I’d say the sign of a good DBA is that they know when an exception is needed, they document and manage the process, and may even seek to have the guidance changed. They work with others to get things done, constantly looking to push work out to customers, while protecting systems. They may slow work slightly, but a good DBA works with everyone to minimize delays.

If you’re a traffic cop, I’d argue your employment days are likely numbered. The world is changing, and while security grows more important, the ability to get work done securely and quickly, while working with others, will become more important over time.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

March 20, 2018
Moving Lookup Data with ReadyRoll

I’ve been using ReadyRoll for a small project and wanted to move some lookup data. There are some tables where I want to keep data in sync across environments, so if my build and release pipeline can do this, great. ReadyRoll makes this easy, and this is a short post to show how.

The Current Environment

I’ve got a table in my dev database that has SQL Server versions. I can see the list here:

In my QA and staging systems, I see this (only one is shown, but it’s the same in both).

The goal is to have this data migrate with other changes in a build and release pipeline.

Adding Data to the Project

I’ve already built this as a ReadyRoll project and set up a build and release pipeline in VSTS. Now I want to include my data.

In the ReadyRoll tool window, I have refreshed the project and I see everything in sync. That’s good, and that’s how I want to be starting a small section of development.

I want to find my table, so I need to expand the “Identical Objects”. When I do that, I see a list of all objects in the database.

I can scroll down to find my table. When I do, I’ll right click it to get a few options. One of these is “Include Table Data”. We want to pick this one.

Once I do that, I get a note to refresh again. This is so RR can determine what table data needs to be added to a migration script.

My changes show that I need to get 11 rows of data into a new script.

I click Import and generate script, which will build my migration script and add it to the project. In my case, this is script 4.

Note that this script has a few things in it, based on a call to SQL Data Compare in the background. First, it sets a dateformat. Next, it does the insert for an empty table, since that is the situation I’m in. Last, this uses the SET IDENTITY_INSERT option.

If you don’t like these options, change them here. You can alter this script to suit your environment. Remove the IF, let the identities be what they are, make the changes that matter to you.

The only thing I’ll do is click the script name twice to edit it.

The numeric sequencing is important. The rest, not so much. I’ll choose something simple here.

Build the Project

I can build locally, and I always should to be sure things work. Once I see this and I’ve tested a few things, I’ll let the “system” do more work.

The nice thing about ReadyRoll is that I can include my database project alongside application projects, if I want. For application developers or hybrid developers, I can use a consistent interface for saving changes to version control.

In Team Explorer, I’ll check changes, add a comment, and then Commit and Push.

If I go to VSTS, I’ll see the build in progress. I’m using a local build agent and usually within 15 seconds, my build will start. Sometimes it’s really fast.

If I click the build number, I can see I’ve barely caught this before it finished.

The build completes, so now I need to check releases. I have a CD trigger that will deploy to a local QA instance when the build succeeds. If I go look at the release, I’ll see that’s occurred.

This worked, so let’s check the QA instance. As you can see, my data was deployed.

If you were watching closely, you see I have a small data issue. I need to correct that, but that’s for another post.

ReadyRoll is a part of the SQL Toolbelt, and if you’re a customer, I’d urge you to start a PoC and see what you think of the tool. ReadyRoll Core, with limited features, is included with Visual Studio Enterprise, and if you’re a customer of Microsoft, you can try that. We also have 14 day trials if you’d like to do this on your own.

February 8, 2018
No Magical DevOps

I saw this comment in a post a short while back. A reader said “I’m still trying to wrap my head around “devops” as it pertains to database deployment. … how do you deal with those potentially dangerous/destructive changes without manually coding? … what tools are there that help in that regard? That’s what database developers really need. The only tools I’ve found that’ [are] useful so far are experience and care.”

We have some tools over at Redgate software to help with database DevOps, but there aren’t any magic solutions that can alter your database in some way that doesn’t impact users and preserves all data. In fact, if anyone claimed to have a tool, I wouldn’t trust it because there are just too many variables in database deployments. I certainly wouldn’t trust a tool with automated rollbacks. Ultimately, experience and care are needed to build better software.

And, by the way, DevOps doesn’t solve deployment issues. All DevOps does is help you automate the tasks you’d run manually in a reliable, repeatable fashion. DevOps teaches you to learn from your actions (gaining experience) and experimenting with small changes so that your mistakes aren’t as impactful (developing with care). DevOps isn’t magic, it’s good, solid, learning from what works and what doesn’t, altering our habits, and striving to be better. DevOps is often just providing a reliable, repeatable way of automating the things you do manually now, not inventing new processes.

I do believe in DevOps and I think that it can work well, but it requires balance, commitment, and huge culture changes. Automating code deployment is easy with some tools. Getting people to write better code is hard. I can help with the former, and I try to help with the latter here at SQLServerCentral, but it’s really up to most of you to make an effort to become better at your craft. Getting that to work in any size organization requires that your management supports teams learning to be better and tolerating a few mistakes here and there.

Many successful developers and DBAs have been practicing for many years (even decades) what the media and high profile companies have been calling DevOps for mere months. You can join them by looking to improve all parts of your software development pipeline through collaboration and automation. Just don’t forget to also learn to write better code along the way.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

January 3, 2018