Voice of the DBA

Category: Editorial

  • Lessons for all of us

    I write a fair amount about career issues. It’s one of the largest tags in my word cloud, and I’m proud of that. I’ve seen too many people in bad jobs for too long, had a few myself, watched my wife work far too hard at times, and lost friends because of a lack of time. All of those experiences have caused me to really think hard about life and how I approach work.

    Don’t get me wrong. I like work, I enjoy what I do, I have a great time writing code and queries and think technology is a great career choice. That being said, it can be a hard job. There are demands to work long hours and holidays, often to deploy changes when the workload is low. Some years, I’ve worked as many holidays and weekends in this business as I used to when working in restaurants.

    That being said, we often think of this as an “easy” job in many ways. We work in offices. We’re well compensated. We don’t have a lot of physical demands, and we can do this job will into our later years if we choose. If you’ve ever worked in a job that requires more physical effort such as construction, you appreciate the ease with which our days pass. If you’ve ever had a boring job, such as staring at the x-ray screen in airport security, you’ll likewise realize most of us are lucky that we get to exercise our brains.

    That doesn’t mean this job isn’t hard. In fact, burnout and high stress are real problems in technology. While some industries might worry about their people getting enough done when working remote, our industry has too many people that don’t know how to stop working. I thought about this as I was reading a post on burnout and looking out for yourself.

    Many of us that work in technology are too sedentary. We sit at desks, we work odd hours and often subsist on poor diets that we’ve built during a lifetime of late night coding sessions. We also accept more blame, demands, stress, and accountability than we should. While I don’t know too many people that have had serious health issues from this work, I do know a few. I also know far too many people that have passed away before they reached 50.

    The downsides of this work can creep up on you. I know I felt a little burned out last year, from too much travel, too many balls being juggled, too much pressure. I made a conscious effort to slow things down and I’m much happier this year. I try to exercise and eat (slightly) better on a regular basis, balancing that out as best I can.

    We work to live, not the other way around. Whether you need fewer responsibilities, more exercise, new hobbies, better connections with friends and family, strong mental health care, or something else, make sure you take care of yourself in this life. It’s the only one you get.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Pause and Backtrack

    One of the main functions for anyone that manages a database is ensuring that they can recover the system in the event of any issues. My view is that restores are the most important skill and task that need to be performed on a database. Since restores require backups, I’d then rank backups as a 1a important task. They’re a dependency and necessity to ensure that we can restore data. Having a set of the data, in a transactionally consistent state just feels critically important to me, over everything else..

    I saw this new feature from Amazon Aurora for their MySQL compatible database. You can use Backtrack to rewind your database to a previous point in time. On one hand that’s an amazing feature. Make a mistake, have an error, click a few buttons and get the database restored back to the minute (or second) when you made a mistake. On the other hand, if you delete a table, do you want to roll all tables back to that point in time?

    This seems like an amazing feature. Amazon takes some of the hassles of managing some backups backups. You determine how far back you want to go, in hours, up to three days. Depending on the activity in your database, they charge differently. To me, that’s interesting. It makes sense to me as a customer. I do more, they track more, I pay more. This also seems to be a way to capture more money for Amazon by cutting some of the consumer surplus that exists with flat fee pricing, which is something many of us prefer.

    The way this works is also different than Azure. The Azure point in time feature allows you to go back, but you can’t restore on top of your existing database. You’d need to restore elsewhere, then play the rename game or move data between databases. While that seems inconvenient, if you’ve ever had someone restore a local SQL Server backup over a database you needed, you might appreciate the safeguards of not allowing a restore on top of an existing database. While the process might seem like a hassle, this does help prevent mistakes during a stressful situation.

    Which of these do I like? I prefer the Azure one, though I’d like the restores to be more granular than a minute. The reason is that I rarely want to restore in a disaster over the existing database. In most applications I’ve managed, there are updates to multiple parts of the database. A mistake in one table doesn’t necessarily mean that data changed in other tables should be discarded. Even during deployments, when things go wrong, I’ve often just broken one set of tables and rolling back the entire database in a restore is painful. Usually I’d prefer to undo what I can and get the any missing data from a restored copy of my database.

    Perhaps it’s just me, but I find the idea of allowing clients, or even many technical people, to easily roll back an entire database after a mistake to be very dangerous. By the time we recognize the mistake, verify data, notify others, we might have lots of changes in many tables. Abandoning that data for the sake of convenience is something that’s unnecessary. I also worry many people trying this feature don’t think through the implications of rolling back an entire database. If you feel differently, let me know. There are cases this is certainly helpful, but I think I’d rather have a “restore to a new db and rename both” automated task instead of AWS Backtrack.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Checking CHECKDB

    One of the recommendations for SQLServer is that you run a DBCC CHECKDB regularly on your system. Those individuals that have worked with SQL Server for some time and worked on learning more about the system know to schedule this check, and even ensure that you restore backups to check them on a regular basis. Note, running these checks on secondary systems may or may not be valid. You might want to read Paul Randal’s post on this.

    Experienced DBAs know that when corruption strikes, you can find yourself in a very problematic situation. Corruption can be captured in backups, which means that if it appears, it’s entirely possible that if this has been in your system for some time, all the backup files you have contain the same corruption. If this is inside a table, you might end up losing data, which is never what any of us want.

    Those that might not be familiar with SQL Server, or have never learned about regular maintenance might not realize that DBCC is needed. In fact, they might not know if CHECKDB has ever been run on their system. It used to be hard to find this, but things became easier over the years. When checkdb runs, it does write a note in the error log, but that’s not a great way to track this information for administrators, especially if the instance has been restarted. Without a set of tools in place, this becomes a project for anyone that starts working with a new system to track down.

    That changes in SQL Server 2016 SP2. There’s a new property for your database, a parameter for DatabasePropertyEx(). The value is LastGoodCheckDbTime, which in this case, you can get the last datetime when a checkdb was run. To me, this should be some sort of alert that your monitoring system has in place that lets you know if this value is too old.

    The problem is that for many of us, we may run DBCC CHECKDB on another machine, perhaps on a restored copy of production, so how can we track this? Is there a way that would make sense? I’d like to think that perhaps any script testing a backup should connect back to the primary database and update this value. There isn’t a specific place for this, but I certainly could see using extended properties for this. At least then we could more easily determine if we haven’t been regularly checking a particular database for corruption. If you have other ideas, I’d be interested in how one might actually track this.

    For now, use this property to ensure you’re checking those databases where you do execute CHECKDB, and if you aren’t sure if you should do this, you should. Go learn about what CHECKDB does and why it’s important for your production systems.

    Steve Jones

     

  • A Great Case for Powershell

    I think PowerShell is a great addition to the Microsoft stack. Given the previous versions of VBScript, Perl and Python ports, and more on Windows, PowerShell is a great improvement. Even for someone that spent a lot of time in the C Shell and Bourne Shell as a student and young professional, I think PowerShell is an improvement. There are certainly still things that make me crazy about PoSh, such as the -eq, but I’m getting more comfortable with the structures and flow.

    In SQL Server, we had the old SQLPS module, which was, well, a start. Then we got the SqlServer module, which is better. However, the best thing I’ve seen for us data professionals is the dbatools project from Chrissy LaMaire (@cl) and company. To me, this is incredibly useful for anyone that wants to use PoSh with SQL Server, but it’s really indespensible for SQL Server migrations from instance to instance. I’m not sure I’d even try anything else at this point.

    Not everyone wants to use PoSh with SQL Server, and that’s fine. There are certainly plenty of places where T-SQL works very well to manage and interact with SQL Server. I still prefer it for many things, but the more I work outside a database, whether for administrative actions or manipulating parts of the platform away from SSMS, PowerShell has some advantages. Not the least of which are cleaner file operations and certainly better string manipulation (IMHO).

    Recently I saw post from Drew Furgiuele about finding linked servers in your object code with PoSh. It’s a long, somewhat convoluted post, and some of you might wonder if it’s worth the effort. Hint, it’s not worth the effort because Drew has done it for you. He’s got the function on Github, and if you need to move linked servers, just use his code to help.

    My point is, however, that doing this in T-SQL would be a nightmare. Just going through search isn’t helpful, though SQL Search is a great tool. The work to find the objects would be hard, and this isn’t something you want to get wrong in a migration. It would be bad enough to have errors for non-existent linked servers, but it might be worse if you had code pointing to the wrong database. This is something you’d want to fix, and PoSh helps work with a complex problem here.

    There are other issues like this, other problems or requirements that look across instances or more intensely at parts of our systems outside of what we want to do with T-SQL. Certainly anything at scale, that might be something you do for many instances is better with PoSh, and certainly any infrastructure as code items that work to stand-up and configure SQL Server really should use PoSh.

    Don’t make PowerShell a hammer, and try to solve all issues, but for the future, you really ought to be sure you are comfortable reading PoSh scripts and know how to use them with SQL Server. Learn when PoSh is a good alternative, and certainly know where you can get scripts or help with your code.

    Steve Jones

    The Voice of the DBA Podcast

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