Voice of the DBA

Tag: administration

  • The Right Connection

    Travis-CI had some staffers connect to the wrong database and truncate production tables. Needless to say this caused an outage and disrupted their business. Hopefully they didn’t lose too many customers, but they certainly did not help their reputation. I’m sure there are more than a few customers trying to decide if they continue to trust the company or move their Continuous Integration (CI) processes to another platform.

    I’ve done this before. Not shut down a company, but I have actually truncated a production table by mistake. Well, not TRUNCATE, I mean, who runs that. But I have run a DELETE without a WHERE clause and killed a lookup table in a production database. Fortunately I had a copy of the table elsewhere and could rebuild it in minutes. Only a few customers had their work interrupted and only for a portion of our system. The point is that I’ve been a very good DBA, with a lot of success and experience, and I still make mistakes.

    Often this type of mistakes comes about because we get busy, and we keep connections open to different systems. When we might be developing code against a schema that is close to production, it’s easy to forget which database we’re working on. Someone calls with a problem or we fight a fire, and we run some code. We fix the issue, stress bleeds away and we go back to work, but forget to switch connections or tabs. Then we run some code that would be fine in development, but causes issues in production.

    SSMS has colors for a connection. SQL Prompt has tab coloring by system and database, as do some other products., which can help, but it isn’t perfect. One thing I’ve found with colors is that if I use them constantly, my mind starts to filter out the color. I don’t always realize the outline of the tab is a different color. This is especially true if I have the need to switch back and forth between both production and non-production systems. I’ve tried running two instances of SSMS, which helps, but at times I’ll forget which one I’m working with and make a connection to a production server from a non-production instance of SSMS.

    Ultimately, we need to be careful. I know one friend that has no access to production and must hop through an RDP session and connect to a production database. However, if you run your RDP session in full screen, how often would you forget that you’re in the SSMS on the hop system and not in SSMS on your local machine.

    I don’t know if there’s a good solution. Many of the convenience features that make life easier, like reconnecting tabs when I restart SSMS are great, however, they can compromise security and safety. I don’t know if there is a good solution, but I’d certainly like more checks against ad hoc issues occurring in production systems. Maybe some sort of lock against certain instances that prevents destructive execution on certain instances or databases without some confirmation. I love SQL Prompt preventing me from running code without WHERE clauses, but that isn’t always enough. At least not for me.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Remove-DbaBackup with dbatools

    I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

    One important item for any system administrator to manage is the removal of old files that aren’t useful. I know most of us hate to delete data, but there are log files, backups, and more that will clog up a drive over time if they’re not managed. I’ve had SQL Servers stop because old copies backups filled the disk and I’ve had IIS servers start throwing errors because 2 years worth of logs were on stored on the C drive.

    Maintenance plans had a way to remove files and we have xp_delete_file, but there are limitations to ensure that only backup files are deleted. I think those are silly, but it wasn’t my decision to include restrictions, and I don’t get a vote on future changes.

    In any case, dbatools has a cmdlet that can help: Remove-DbaBackup. I was interested to see if this worked on it’s own or had restrictions, but it seems to work wonderfully for me.

    Required Parameters

    Most cmdlets will allow quite a few parameters to be optional. In this case, however, there are some requirements. First, you need a path for the backup files. That makes sense and no big deal.

    However, you also need a retention period. You can’t skip this, as if you do, you get a prompt.

    2018-04-10 17_29_56-cmd - powershell

    The retention periods aren’t obvious, but not that hard to remember. There’s a numeric counter and a one character time period item. They are:

    • h for hours
    • d for days
    • w for weeks
    • m for months

    That’s it and not a big deal, though for testing I need to play with my system clock a bit.

    In any case, after this parameter, you need a backup extension. This is the file extension, without the period. You can put in anything, which is cool.

    There are some other params, but not required.

    For testing, I copied some backups and then changed some extensions. As you can see, my test folder has SQL backups with various extensions I’ve encountered as well as a few text documents.

    2018-04-10 17_27_38-Copies

    If I run Remove-DbaBackup with some options, I’ll see what will happen with the –WhatIf parameter. I see plenty of files being marked for deletion as long as I have the right extension.

    2018-04-10 19_37_32-cmd - powershell

    This is handy, and it makes perfect sense when you read it. This is exactly the type of maintenance job that you want to set up on a server to remove old files. I don’t know that I’d use this for general cleaning of files that I might need soon for a backup, since I always want to be sure that I have a good backup before I remove old files, but for managing very old files, this is helpful.

    And, a little scripting logic would show you how to find the date of the most recent full backup and then remove files older than that. Or maybe older than the last two fulls.

  • Is the DBA Title Dying?

    Not is the DBA joib dying, though we could argue about that as well. Instead, I’m asking if the title of Database Administrator is going away. Are there going to be people that really want to send out resumes and apply to be Database Administrators beyond the next few years? As I look at my resume, I wonder if that’s a job or title I want to keep around.

    Think about that for a few minutes. We’re in the age of complex systems with cloud platforms, automated backup software, PowerShell and other scripting, Chef, Puppet, containers, self-service clones, automatic indexing, query store plan fixing, and more monitoring options than you would want to spend time evaluating. We have plenty of tools to assist, or takeover, much of the daily administration of database platforms. Even security can be mostly outsourced to AD, AAD, other directory services. While there is some level of what we might consider administration, is that the core of many people’s jobs? Will it still be in five years?

    I read a few of the posts from T-SQL Tuesday #100, including Adam Machanic and Brent Ozar. They both think the DBA is dying, and I tend to agree. I know that inertia slows change in many large organizations, and I’d bet there will be a delay just because of HR. After all, if you want to be a data reliability engineer or data professional, most organizations don’t list that job title and would have no idea how to hire you. Even if they loved your resume, they’d end up hiring you as a DBA or developer (developer III maybe) just because they like those nicely labeled buckets for the HR systems. They might not even know how to pay someone in some new role.

    However, the more I talk with people, the less they seem to be doing administration. They still tune queries, but often they’re helping do more database development or even database architecture than administration. They may to more reporting or ETL work, which can easily fall under development as well. Security is still a part of their jobs, but that doesn’t seem to change as often as it used to. Many people have moved to database roles and AD integration for authentication. Then security becomes more of a set-it-and-let-someone-else-manage-it. I even see Slack bots or other tools that let people self-service requests, and others quickly (or automatically) approve them.

    We still do some work, as things like HA/DR still aren’t quite a smooth as they could be, but that’s changing. Cloud services, and the evolving Azure Stack may mean that more and more of our work will be done by templates and patterns. We may have to create the template, but that feels more like architecture and less like basic administration. I expect at some point we’ll just link a grid of machines, on premises or in the cloud, and let people self-service their requests for systems. The “template” will let a service deploy HA databases across the machines as it sees fit, providing an address to connect to, implementing monitoring, and even solving many simple problems without human intervention. I expect that security and auditing features and capabilities will also grow rapidly, becoming more automated, or at least automatic, and request less administration.

    I used to joke I was a data janitor in many jobs. Just dealing with whatever situations people couldn’t, or wouldn’t, bother working on with databases. I often cleaned up messes made by developers that weren’t sure how to build a database that scaled beyond “their machine”. These days I think I’m no less of a data janitor, but I call us data professionals. It’s a better catch-all term, and certainly sounds more appropriate. We do our best to ensure the safety, accuracy, and availability of data in whatever way works best in our environment. We’ll run your T-SQL, R, python, machine learning, SSIS, SSRS, or whatever code for you, and audit the actions.

    I think that as we do that, we’ll do more development, cleansing, and analysis than actual administration.

    Steve Jones

    The Voice of the DBA Podcast

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

  • dbatools is Growing Up

    The dbatools.io project is growing up rapidly. I went to check something the other day and I was amazed at the command list. This used to fit on one page. Now it’s not close. Here’s the first page:

    In fact, it doesn’t fit on two pages. Here’s the part below the fold.

    It’s crazy how quickly this project has grown, and how many cmdlets are available to help you not only migrate objects and settings between instances, but perform useful operations on your systems and test or change their configuration. I’m starting to think that this needs to be required for most people that manage systems. The ways that you can easily manage your systems in a programmatic manner has dramatically changed with this module.

    I was surprised the other day when I saw a blog post for a cmdlet I didn’t know existed. I went to try it on my system and it wasn’t available. I’d updated the module the previous week, but apparently there are new changes on a regular basis. I might need to update my module more often, and there’s a cmdlet to help me do that!

    Those of us that have a lot of experience with SQL Server might find some of these tasks easier in T-SQL. I certainly spend more time inside the platform than out of it, but there are definitely advantages to using PowerShell to enforce consistency and easily work with multiple machines is nice. These utilities are often like scripts or procedures I’ve written to handle simple tasks in a wrapper-like fashion. dbatools do that from PoSh, and allow me to easily string together disparate comands. I won’t give up T-SQL anytime soon, but I’ll do more with dbatools than I might do with PoSh otherwise.

    If you haven’t tried this project, you ought to. This will make it easier to learn PoSh and work with SQL Server. Maybe you’ll enjoy it enough to built your own module and contribute to the project. There are still holes, especially in the SSIS area, though I expect new cmdlets to appear any day.

    Steve Jones

    The Voice of the DBA Podcast

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