Voice of the DBA

Tag: administration

  • Assembling Your Own Toolbox

    Last month Brent Ozar posted that September was community tools month. He had a number of posts that highlighted tools and a number of other people (Francesco, Erik, VladDBA ) did as well. I didn’t do any blogging because, well, it was a busy month for me. I was gone from home for 21 days, so a bit harried in life.

    As a part of this effort, I saw Ken Fisher write about being a Script Magpie, which is an interesting analogy. A magpie is a bird that collects various shiny objects. Ken collects scripts in the same way. Things that catch his eye are added to his repo of scripts that he keeps handy.

    I used to do something similar. I didn’t have a repo back then, though I should have. However, all VCSes in the past lacked some of the ubiquity of Git. Instead I had a folder that I kept synched on various flash drives that I carried from job to job. I’ve also usually had a share inside companies where a team of developers or DBAs could share SQL scripts.

    I’d hope that most technology professionals would share scripts, especially when they can be tokenized or added as SQL Prompt snippets. Reusing work is a big part of working with computers and why many people have embraced tools and utilities that they didn’t write themselves. I certainly think having a toolbox is important, especially tools that integrate well with an existing environment.

    I don’t think it’s worth rewriting many tools inside a company. I work for a software vendor, and I’ve seen the effort put into building tools. Both free and paid tools can save you a lot of time and effort inside your job, not the least of which is maintaining the tools over time as versions and editions change.

    What are the contents of your toolbox? Which things are those that you’d bring to a new position if you moved organizations or groups? Let us know today.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Checking Database Compatability = #SQLNewBlogger

    Recently I needed to check the compatibility level of a database and SSMS didn’t work. This is what I did in T-SQL.https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBRloggers. Here are some hints to get started.

    Querying the System

    I would have assumed I could use the DatabaseProperyEX() function to get this, but as I look through the list of properties, the compat level isn’t in there.

    When I look through the Docs for the compat level, I find that the page for database compatibility, the page mentions that querying a DMV is the way to check this. Seems strange, but I guess that’s what you do.

    Here’s the query:

    SELECT name, compatibility_level FROM sys.databases;

    Y0u can filter this by database name if you need it.

    I needed this as I was testing SQL Server 2022, but my SSMS version (18.10), didn’t recognize level 160. I assumed that was what should have been there, but I needed this query to verify.

    Now, hopefully I’ll remember that I just need to query the DMV.

    SQL New Blogger

    This was a quick post, really just over 5 minutes to write. It’s not super technical, but it does show that I can research something and solve a problem. And I have an alternative when my main tool doesn’t work.

    Good skills to showcase on a blog.

  • The Case for Patching

    Recently I was testing a feature in SQL Server on 2017 and 2019. There was supposed to be an improvement across versions, but I didn’t see it. Then I realized that I was on SQL Server 2019 CU 2 on my laptop, and the current CU is 17. I took a few minutes to download that and install it.

    I have often been a lagging patcher in production environments, often looking to stay a CU or two behind, depending on my workload. SQL Server has been a very security -table platform, so that’s often worked well, though there are security updates at times. For those, I usually prioritize a patch getting applied.

    Windows (Linux, MacOS, etc.) tends to get patched more often than other software, especially by administrators. At least on desktops. Servers sometimes lag a bit, which can be a problem. I saw this week that a lot of attacks in 2022 Q2 were for a vulnerability Microsoft patched in Sep 2021 but hadn’t gotten all their customers to apply the patch. That situation was a problem early in my career with many vulnerabilities, and it’s still apparently an issue now.

    If you’re wondering how big a deal patching can be, remember the Equifax hack? This occurred because administrators hadn’t patched a system. Whether it’s a host OS, a database platform, or some other software system, it’s important that you keep somewhat current with patches. We never know when vulnerabilities will appear, and honestly, for most of us, we can’t spend the time tracking every piece of software and the various vendor disclosures.

    We can, however, patch relatively quickly. While I don’t expect that most, or even many, people will patch within a month, I do think that delaying six months is probably a bit long. That being said, I need to check a few of my servers and make sure my admins are keeping them up to date.

    Steve Jones

  • The Useful Cloud Tags

    One of the things I’ve seen in working with cloud based resources is that you get a lot of them in your account quickly. A database might end up with a server, an IP address, a network, security groups, and more. A few clicks of the mouse in the Azure Portal can create a new Resource Group that doesn’t just have the one thing you need, but 3, 4, or more other items.

    In addition to the quantity, there are also the problems of namespaces in cloud resources. Some of the items you provision are publicly named in a domain, such as database.windows.net. In these cases, the resources need to be unique among all cloud customers. Just like domain names, this means that you might have collisions with your favorite name. While I might like jones.com, there are a few other people that would likewise prefer this. The rest of us might have to choose jones2.com, stevejonesincolorado.com, or some other variation. In large organizations, you might end up with LA34532345454.database.windows.net for an Azure SQL Database.

    That means that the names of the systems don’t make sense to anyone, and many of the people that need to use or manage them will not even know which resource belongs to which system. This has been true for servers in many organizations for a long time, even with their own domains. Often there is some document, perhaps stored on the root of the machine or in an online share, that provides more information for people accessing the system.

    The cloud makes keeping track of systems harder, and there is a greater need to classify, categorize, and tag resources. Most of the cloud providers have built extensive tagging systems that help users add metadata to various resources and search/filter by these tags.

    Today I’m wondering which tags are useful in the cloud? How do you decide which tags, or type of tags, to apply? You might choose to apply an application name, a business department or owner, or some other type of information that helps you keep track of which resources are needed and how to deal with them. Is there some guideline on the type of key-value pairs you use for tags?

    I also would you want to easily have tags for on-premises SQL Server instances? We do have extended properties, but those are cumbersome. Would you want some sort of easy system query that retrieved all tags. Perhaps something like @@InstanceTags or @@DatabaseTags that retrieved all the data you’d stored in extended properties.

    We are only seeing more and more resources that need to be managed, patched, and deployed. Tagging is one of the ways that helps our organizations keep track of resources, especially if we provision them and we move on to our dream job somewhere else. Let me know how you handle things today.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.