Voice of the DBA

Author: way0utwest

  • Specialist or Generalist

    What would you rather be: a expert specialist in some narrow area of SQL Server or a generalist that’s competent in many areas? The same question could apply to any area of technology, like C# or Windows, or even any subject in which you work. What’s your preference?

    Most of us don’t have to make a strict choice. Often in pursuit of deep knowledge in one area, say indexing, the effort will lead to lots of good general knowledge in other parts of the platform. I think it’s important that gain both types of skill, as you work through your career and grow your abilities. I find that there is always more to learn, especially in technology. Even in areas that I think I have deep knowledge about, I continually add to, and refine, my skills.

    Brent Ozar had an interesting post on this topic, looking at the maturity of a particular subject area. He noted the more mature some area, the more you might learn towards specialization. I think that’s good advice for your career, though I do think that if you find a deep passion, don’t be afraid to specialize in some area. While you might not get a monetary compensation directly back for the effort put in, I think the reward of learning something you love, and achieving expertise is extremely valuable and important to you as an individual.

    As we move through our careers in computing, we find there are always new technologies, techniques, and architectures that we will need to understand. We need to grow our knowledge. In many cases we see older ideas re-packaged and updated for the modern times, but that doesn’t mean skills we can rely on prior knowledge. Instead, you will need to update your skills.

    I also think more and more we will be driven to give guidance to others as much as use specific expertise to solve problems. With that in mind, I think whether you choose to be a specialist of generalist, you’ll need to continue to learn more about both implementing technology, as well as communicating your recommendations.

    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.

  • Explicitly using tempdb with ##tables

    I had someone ask me last night if this statement would create a permanent table in tempdb with a strange name or a global temp table:

    CREATE TABLE tempdb..##mytable
    ( id int
    );

    My suspicion was that this would always be a temp table, but since I usually don’t include tempdb, I decided to test things. I quickly opened up SSMS and tried it. I got this message:

    2016-04-21 13_55_14-Microsoft Solitaire Collection

    That seems to imply what I suspected. The ## override everything and determine the table type. When I look in the list of tables, I see my table there as a temporary one.

    2016-04-21 13_56_21-Start

    This is the behavior I’d expect, and it acts the same in SQL 2012, 2014, and 2016 (RC2).

    I don’t think there’s an advantage to using tempdb..##mytable, or even creating a permanent table (until restart) using tempdb.dbo.mytable, but if there is, please note something in the comments.

  • The Pen Test

    We need something like the Air Force project seeking network vulnerabilities for our database systems. While there are tools that can help from the outside, but we need SQL Server specific tools. Why don’t we have a Best Practices Analyzer for SQL Server 2014 (or 2016)? It seems as though this is one of those tools that we should have available for checking against instances and determining if there are obvious vulnerabilities. Being able to add custom extensions for particular needs would also be nice.

    I guess if this is important to your organization, you’ll pay for a tool (or a pen test), but the problem is far, far too many organizations just don’t take security seriously. It seems that despite the huge costs incurred for not properly securing computer systems, the penalties aren’t nearly enough to get every company to patch their systems and write secure code. Most just hope they’re never attacked, or if they are, no one finds out.

    I’d really like to see some tools, or maybe just a best practice process that would allow system administrators to test their own systems. I found this post from PenTestLab, and I think it’s great. The article is a good start for what to look for and what issues might exist. However even better than this might be a series of automated items we can run that showcase to management, developers, and DBAs when they’ve made a mistake that potentially opens a system to attack. Maybe best of all would be getting software vendors to actually run these types of tools against their applications and provide some proof they’ve built secure code.

    I don’t hold out hope that security will improve anytime soon, but I do hope that more system administrators learn about what issues potentially exist and check their systems for potential vulnerabilities before someone else discovers the issues exist. Lastly, I hope they all share what they learn.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Changing the sa Password with SQLCMD

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

    I wrote about using SSMS, but that’s not always convenient. If you need to change this remotely, perhaps in a hurry, SQLCMD is a quick way to do this.

    SQLCMD is a command line tool, so open a command prompt.

    2016-04-06 12_47_33-Photos

    Run SQLCMD and connect to your instance as a sysadmin. If you have any doubt, you can enter the query from my previous post to check your connection.

    Once you’ve connected, you can issue this code:

    ALTER LOGIN [sa] with PASSWORD = N‘Sup#rAmaz!ngP@$$w0rd’

    This is the code that will change the password for the login specified, even if I’ve logged in with a different account.

    Once I’ve done this, test the sa login from a new session and verify it works.

    SQLNewBlogger

    Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

    References

    SQLCMD – https://msdn.microsoft.com/en-us/library/ms162773.aspx

    ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx