Voice of the DBA

Author: way0utwest

  • 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

  • Backups Over Time

    I’ve written about backups at various times, including responsibility, problems with systems, and home protection. This is a subject that is important to me, as I consider the responsibility for protecting data to be first and foremost. Security, performance and more all matter, but backup and restores are the most important items to pay attention to.
    As data professionals, whether looking at organizational systems or our personal data, we will find that our backup strategies change over time. We may also find that systems come and go, and it can be easy to forget about older systems. I know I’ve had to track down tapes and restore decommissioned systems years after they were reformatted or powered off.
    I ran across an interesting post from someone that went through and found their old backup media and moved it all to newer media, as well as cloud storage. While I’m not sure that I really want to go through old hard drives and keep old code or data, it’s an interesting exercise to think about.
    Do you worry about losing data from old backups? This probably applies more to home systems than organizations, but perhaps you have regulatory requirements to keep seven (or more) years of backups around. Maybe you want to be sure that your old code, projects, pictures, and more are saved elsewhere. Maybe you even want to ensure that newer formats of media are being used.
    What techniques might you use to accomplish this? I know I have a Windows Home Server that receives copies of pictures/music/video and a Crashplan account that backs up various machines in the house. That seems to work well, though I do consider taking those pictures/video and putting them on DVDs for long term storage out of the house. I’m hoping that .jpg and other formats don’t go out of popularity anytime soon.
    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.