Voice of the DBA

Author: way0utwest

  • Code I Can’t Live Without–T-SQL Tuesday #104

    tsqltuesdayBert Wagner has a good invitation this month, a T-SQL Tuesday question about code, specifically code you can’t live without. I’ve got my thoughts below.

    This is a monthly blog party, and you can participate. Write a post on this topic, and publish it on your blog. Drop a comment on Bert’s post. You can read the rules on his invitation, but you can really post anytime. It’s fun and good for your brand.

    The Critical Code

    I’ve managed lots of instances in my career. Some mission critical, some just important, some not important to me or the business, but important to someone.

    One thing I’ve found is that there are plenty of common things I do on most systems. I’ve had lots of code that I’ve written to manage all aspects of DBA work, from backups to maintenance to monitoring. I’ve had routines that handled all sorts of security or auditing.

    The code that I can’t live without, isn’t my code, but I’ve used it on probably every instance I’ve managed at some point. The code is actually from Microsoft, and it’s indispensable.

    The code is sp_helprevlogin from this support article.

    While I tend to be distrustful of keeping passwords the same for too long, and I usually don’t attempt to recover them in any way, just reset them. There are cases, however, when we’re moving an app, failing over to DR, or recovering some system and we need to keep the password the same.

    At least in the short term. When systems are down, I need them back up, and I can argue about a password reset later.

    This has been the most useful piece of code for me, and one that I think most of you could use as well.

  • Another MVP Award and a Few Thoughts

    I got the news of my MVP award last week. I’m honored that Microsoft feels I do quite a bit for the community. This was my 10th or 11th, though I’m not sure since they changed around the award periods. In any case, it’s been a long time.

    As I saw quite a few people posting their award (or re-award), I also noted a few people that weren’t renewed. I wasn’t surprised, even at some of the big names, since I haven’t seen them in the community much in the last year. Keep in mind, the award is for the most recent award period, not your past.

    I ran across this Twitter account, which I’m guessing is someone that wasn’t re-awarded as an MVP. Personally, I think this is a poor showing of one’s professionalism. I’m sure this is why the account is somewhat anonymous.

    This year we had quite a few that weren’t renewed that have been MVPs for a long time. Usually this isn’t a surprise to the individual, as they often know if they’ve been doing lots of blogging, speaking, etc. This can be surprising to others, since often we assume that person XX is always helping others. The thing I always think about is whether someone has done enough in the most recent period, not in the distant past.

    And, of course, done enough in the area Microsoft cares about. If you’re the number one expert in the world with Notification Services, writing and speaking about how to keep it alive, I’m not sure you’re getting designated as an MVP.

    I have no doubt that if I stopped blogging here and speaking at events, I wouldn’t be renewed. I do some work at SQLServerCentral, but I’m not sure it would be enough if I weren’t volunteering more of my time and knowledge in other ways.

    The award is recognition by Microsoft. It isn’t, nor should it be, any validation of your efforts to help others. You might be a great community volunteer that does a lot of writing, speaking, organizing, and more. Those are all efforts to be proud of and continue to do, if you enjoy them. They just might not be enough to beat out everyone else, again, in Microsoft’s view.

    As a friend told me, play your game. Do what works for you, and reap the rewards if they come. If they don’t, you should still be happy with what you’ve done.

  • AI Regulators

    With the GDPR being enforced in the European Union, there are plenty of companies that are getting concerned about the potential fines from regulatory authorities if they aren’t complying with the law, or at least, making an attempt. There certainly is leeway for regulators to adjust fines or give warnings if a company is making efforts to comply.

    Other companies might not worry, since there are relatively few regulatory employees and lots of companies. There are lots of complaints coming in, which could easily overwhelms the relatively small staff in each EU country. The problem will likely get worse as more consumers complain about data processing practices.

    There is one way to help amplify the capabilities of the relatively small staffs reviewing complaints. There are researchers in the EU Institute in Florence that are are working with consumer organizations to create AI programs that can help. The initial thrust is to evaluate privacy policies of companies. If there are issues, the software doesn’t assess a fine, but it does alert a human to perform additional checks.

    In one sense, this is exactly what computers can do well. They amplify the capabilities of humans by doing a piece of the work. We can build systems, whether traditional programmed ones or AI based applications, that handle a piece of the work that requires lots of human labor. Once initial evaluations are made, a human can review the work and make more refined judgments.

    The danger, to me, is that humans will be lazy. They’ll start to trust the AI systems as authorities and use less of their own judgment, mostly because it’s just easier. I could see these systems evolve over time to actually train humans involuntarily. New employees would initially trust the AI results, learning from the AI rather than teaching it and constantly evaluating its effectiveness.

    I think AI can really help improve the way that we accomplish work in many ways, but it should be audited and regularly approached with some skepticism by some sort of supervisory group. We should be sure that the goals and results from any AI system continue to be focused on what we want to achieve, and that we transparently define those. Otherwise we might end up having AIs evolve in ways that are counter to the original purpose.

    Steve Jones

     

  • Who Likes NULL?

    The title says it all: who Likes NULL values in their tables?

    I have tended to allow NULLs in quite a few places in my design, often because I view the world as messy and incomplete. I also find that applications are faulty, and might not validate data, might not run long enough without a crash to let a user insert a lot of data. The application might mangle data, or just might not have been updated to support a new column of data. I’ve found that there are times where I accept the messy real world and use NULL to represent unknown values.

    Dr. Low notes this as well in a recent post. His view is similar to mine in that he uses NULL values when we don’t know the actual data. This is preferable to some magic value that has to be coded in every application using the database. There are too many chances of mistakes, and definitely the possibility of leakage for these magic values.

    As we use more and reporting and aggregation tools, users may inadvertently see strange values exposed. Many of these tools wouldn’t be coded to translate magic values to some agreed upon value, which results in confusion and distraction for clients. The data in our systems becomes used in new and different ways as we start to connect new applications to existing databases. We may also use ETL processes to move information among systems, often to data warehouse or OLAP data stores. Often there are proof of concept prototypes built with self-service tools, such as Power BI, and the logic that was originally coded to translate magic values is lost.

    That doesn’t mean that every field should allow NULLs, but that we should consider them in places where the data is useful, but not necessarily mandated or captured in every transactions. If we have valid defaults, use them, but if not, don’t be afraid of NULL. Understand the meaning and implications of allowing NULLs and use them carefully.

    I’m curious about if you agree with me. Do you default to NULL values or do you avoid them at all costs? Do you use them judiciously? Give me the reasons why or why not, and if you have examples of where you allow NULLs, let us know.

    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.