Voice of the DBA

Category: Editorial

  • GUI or CLI

    There’s a debate over on Scott Hanselman’s blog about the GUI v CLI in the comments for his Azure CLI post. It’s a blend of people complaining about one or the other, mixed with a few (I think) rational people that recognize the GUI and CLI are both valuable. In fact, there’s one person that mentions SQL Server and the SSMS GUI that can generate scripts for later use.

    I like the command line, but sometimes it’s hard. I don’t remember the syntax for a variety of commands, especially PowerShell where I use tab completion constantly. I don’t even remember options for many T-SQL functions, because I don’t use them very often. I may find myself depending on Google or BOL for a quick refresher, but often I’ll just use use SSMS if I can to pick some items in the GUI, click Script, and then examine the code before executing it.

    I really don’t mind someone wanting to use the GUI the vast majority of the time they’re working, but someone should be comfortable with a CLI interface. If there is a task that need repeating multiple times, then I really want everyone using the CLI. It doesn’t matter how you’ve written the code, executing something from a CLI is much more reliable than having to click around a GUI, trying to be consistent and quick, over and over. The CLI just works better.

    I’d like to think that most computer professionals these days are able to use a CLI, even if they aren’t too comfortable. The growth of PoSh as a wrapper around so many features and functions in the MS stack certainly contributes to this, as well as fact we seee so much code as code, not as images from a GUI. Github and collaboration, as well as lots of code samples in articles should mean that many people are comfortable working with code and executing it from some CLI.

    I’m sure there are plenty of exceptions. Someone that works mostly with SQL Server might be happy running code in a query window and use SSMS for everything else. They might not even be aware that they can build things like SQL Agent Jobs from a CLI. I get it, the GUI is quick and easy. I use it for jobs, for Extended Events, and various other tasks where the code is complex and cumbersome. However, if something is easier from a command line, I like using it. Perhaps that’s why my console of choice, ConEmu, is always just a CTRL+~ away.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Change Your SQL Server Oil

    One of the things I learned early in life was that some regular car maintenance goes a long way. I didn’t need to be fanatical about checks or ensure that I did the every recommended maintenance item at every interval, but one thing I’ve always done is change my oil regularly. I could let brake fluid, or coolant, or even tires go a touch longer than I’d like if funds were tight, but changing the oil was critical. I’ve lived by that with most of my vehicles over the years, and they’ve tended to last a long time.
    I read a post from Glenn Berry that reminded me of this recently. Glenn talks about the need to perform maintenance on your SQL Server instances in the form of regular patches. I think that’s a good analogy, but certainly not complete. On top of these oil changes, you should think about more regular index maintenance, checking security and backups, monitoring space and more. Those are topics for another day, but no less important than patching.
    So how often do you change your oil, or patch your database? For cars, we used to change every 3,000 miles or 3 months (I typically used the former). Over the years some cars have gone to 5,000 miles, though with more modern vehicles and better oil, many cars have gone to 7,500-10,000 mile intervals. My BMW says 15,000 miles, though I usually get nervous around 10k. No matter what your interval, it’s good to have a value and stick to it. That prevents confusion, forgetfulness, or other human errors. Choose a distance or time and maintain your vehicle regularly.
    For your SQL Server, I’d typically do the same thing. While Glenn likes to ensure he’s got the latest patches for his customers, I’m not completely sold on applying patches as soon as they’re released. There is a risk of things breaking, or even just service disruption, so I prefer to limit the issues. I prefer to let CU patches come out for a few months and see if any major issues are reported. I also likely would apply patches only 3-4 times a year, rather than the 6 times that the patches are released. In the absence of issues, I prefer stability. I do recognize that I’m taking a chance that I could run into an issue that requires patching to get support, or a security patch is released and needs to be installed quickly. In those situations, I’d need to allocate some testing time and deal with the potential issues at that time.
    Along those same lines, I don’t like updating all my instances at once. Certainly not all of a same version. If I have 20 SQL Server 2016 instances, I don’t necessarily want to patch them all today. I might patch 2-3 to ensure that things work smoothly. If that’s the case, then I’d schedule the rest next week. My experience with patching is that we usually need at least two groups, and sometimes three. I have an early and normal patch group, and sometimes a delayed one where the patch timing is problematic. However, I can’t get too far out of date as it’s easy to forget about patching older servers, and that can cause plenty of other issues in the future. In general, I prefer just two patch groups.
    As Glenn mentions, there are other benefits to regularly patching. You touch these servers and have the chance to ensure that your DR/HA plans are up to date. You think about potential issues, and in general, don’t become complacent with regards to the health of your systems. I certainly think you should apply SPs, and at least a few CUs a year to keep semi-current. And, of course, since you want to treat your instances like cattle, not pets, you also need to ensure you patch test and development systems to match their production counterparts.
    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.

  • The Migration Checklist

    One of the things I always recommend is that when you upgrade a SQL Server instance, you perform a side by side migration to a new host instead of an in-place upgrade. My main concern is risk. While the upgrade process is fairly smooth, I still have hiccups installing SQL Server at times, and for a live server, the last thing I want to do is have to uninstall SQL Server and reinstall an old version.

    Apart from the risk, I also think an upgrade is a great time to refresh hardware. If you’re paying for the latest bits, I’d spend a little more for newer hardware if I can. The cost usually isn’t much compared to SQL licenses, especially these days with hardware being very cheap and powerful. New hardware also gives me a staging place to test the migrations, without disturbing the existing system.

    Planning the migration across hosts usually isn’t too difficult, but that there can always be small issues that I need to fix after the job is done. In most cases, that’s not a problem. In some, it can cause downtime (and plenty of embarassment), not to mention a loss of confidence in the DBA team. I find that I often end up building a checklist, working through the existing instance to ensure I don’t forget any items, fixing my list as I run test migrations to new hardware and find issues.

    That makes me think I should have a good checklist that I can start from, marking this up for my specific instances. BOL doesn’t give much information. I did see a nice one in a forum post on SQLServerCentral, but I’d really like a good, solid checklist. An overall list from @spaghettidba might be a good start, and I’m hoping he’ll write an article on each of these, with the list for someone to check. However, I’m hoping to give him, and others, a jump start.

    What items do you need in a checklist for a SQL Server migration?

    This could be just moving the existing SQL Server instance to new hardware. It could be a version upgrade, consolidation, or any other reason to move. In any case, I’ll start with a general list of things to check. Let me know what I’ve missed:

    Instance Level

    • Check new hardware/software meets requirements for SQL Server
    • Verify patches levels are the same (with items needed for installs)
    • Map paths from old to new drives, verifying space
    • Ensure all logins, server roles, credentials, and permissions are migrated.
    • Migrate all sp_configure items
    • Migrate startup stored procedures
    • Migrate all linked servers
    • Migrate all XE sessions
    • Migrate Audits
    • Migrate any server level cryptographic objects
    • Migrate all jobs and agent settings (operators, alerts)
    • Migrate SSIS stuff
    • Migrate Resource Governor data
    • Migrate Database Mail settings
    • migrate replication settings at the instance.

    Database Level

    • Backup all databases
    • Backup and certificates needed for TDE
    • Restore databases with new paths
    • Verify database ownership
    • Ensure backups are running on the new instance

    As a side note, dbatools will perform much, or maybe all (still trying to determine that), of what you need. There is a Start-SqlMigration that is very impressive. While I would still want a checklist to ensure the new system works as needed, I think I’d use the PoSh tools and then add anything else I need to them.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Actionable Metrics

    One of the tenets of Devops is that we learn from the feedback we get from our system. This means that we actually need to include some instrumentation in our application as well as a way to gather, store and analyze this data. There are various frameworks to do this in application code, but we also need to include the database in here. This might mean we need to add some logging or messaging in our T-SQL code, whether to some table or file, integrating with extended events, or something else. I wish to I had a best practice for you here, but there are multiple ways to do this and the best way for different environments can vary. At the least, you need some sort of resource monitoring if nothing else.

    We don’t want to just collect all the data we can, nor do we just pick some random items. We want to think about how our system works and then choose useful data. The key to useful metrics is that they are actionable. We can change behvaior or alter the system based on the information. In other words, we can take some action, positive or negative, based on what the metric tells us about our system. Without this, it might just be useless data that we’re collecting and storing, for no particular reason.

    Of course, we might not know how useful some metrics are for awhile, so we may need to collect data that we can’t use right now. If we find that’s the case later, perhaps we cease gathering useless data after we can’t find value over some period of time, maybe months. However, we do need to perform some analysis and investigation to determine if there is a way to extract information from our metrics.

    Ultimately I tend to err on the side of gathering a bit more data than I need, even if its value is suspect. I’d rather have more data that I need to delete than find myself wanting later and wishing I’d just collected a few more metrics. The key thing is to actually take some time to look at your data and use it to make decisions. Once you find the data helping to drive the behavior of the organization, I think you’ll start to find ways to gather more and more useful information in other systems.

    Steve Jones

    The Voice of the DBA Podcast

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