Voice of the DBA

Category: Blog

  • Execute the Current Statement–SQL Prompt

    I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool.  SQL Prompt will be yours as well if you give it a try.

    This is a great little feature in SQL Prompt that I wrote about at SQLServerCentral. It’s Current statement Execution, which comes about from pressing Shift+F5. Whatever statement the cursor is on, whether it’s a single line or multi-line, the statement will execute.

    It can be on the first line:

    currentstatemnt_e

    the last line

    currentstatemnt_f

    the end of the line

    currentstatemnt_g

    It doesn’t matter. When I press Shift+F5 in all of these cases, I get this:

    currentstatemnt_c

    Only the second statement executed (the SELECT), the first statement is ignored (and all other statements) and the code executed is highlighted.

    As mentioned at SQLServerCentral, there’s a caveat. This isn’t really highlighting, it’s just coloring. Read the article to understand the difference.

    I also have a video version of my tip.

    You can see a complete list of SQL Prompt tips at Redgate.

  • T-SQL Tuesday #62 – Healthy SQL

    tsqltuesdayThe invitation for T-SQL Tuesday this month comes from Robert Pearl. It’s called HealthySQL and it’s a topic I like. I’ve spent a lot of time in my career being proactive with SQL Server databases, and keeping them healthy is important.

    If you’d like to participate in the party, just write a blog post and publish it on the second Tuesday of the month.

    However, if you’d just like to participate, take the topic anytime and write your post.

    Healthy SQL Server

    I would guess most of us would prefer to be healthy over being sick. While it’s hard to be extremely strict in taking care of ourselves, many of us do make efforts to eat better, exercise, etc. to maintain our physical health. If we don’t, then we get sick and are usually miserable.

    The same thing can happen to our SQL Server instances, and if they get sick, many of us are miserable as well. Mostly because we might be working late, working overnight, or being yelled at by managers.

    I learned a long time ago that no matter how well I take care of myself, I’ll get sick at some point. And no matter what I do for my SQL Servers, at some point they’ll have issues. However if the issues could have been easily prevented, it’s embarrassing and it calls into question my capabilities as a DBA.

    I’ve got lots of stories of how I’ve prevented issues by keeping my servers healthy, but there are really a few simple things I’ve set up and worked on. I included these in my Avoiding a DBA’s Worst Days with Monitoring talk. I need to blog about these in more detail, but here they are with a few notes.

    Backups

    The core of any data system is the backup. If you have the data in a backup (and the backup is good), then you can recover from any other issues. As a result, I’ve tried to be sure that I have backups setup, monitoring on the backup jobs, and a process to make a copy of the backup somewhere else.

    Space

    Running out of disk space is one of the simplest things to avoid and one of the most embarrassing to deal with. I’ve written about placeholders, but you should be monitoring space, have placeholders available, and proactively look for more space as you get low.

    Security

    Security is important, and we read about issues all the time. However apart from hackers, loose security often means that users, developers, or anyone else can cause issues in your database. Tight security can be a pain, but it really prevents a lot of issues.

    Resources

    Resources refer to the hardware and software that allow your systems to function. If you don’t have enough resources for your workload, no one is happy and you are listening to complaints. Being proactive, looking to tune queries, reduce fragmentation, add indexes, reindex, update stats, and more are important here.

    Deployment

    Face it, we’ll always be changing our systems with new patches, code, etc. Building a decent test routine is important as we want to prevent deployments that will make our servers unhealthy. However having a plan to deal with issues (because you will have issues) means thinking about rollbacks or other methods of undoing problems.

  • It’s a Problem When a SysAdmin Can’t Log In

    I showcased a demo recently that looked at a potential issue with an application where a user used a simple update statement to gain privileged access to a system. That’s scary, and it’s a potential issue for many applications that handle their own authentication.

    The problem with this type of attack is that it can be hard to detect. A system administrator might not log in with their account for some length of time, during which the attacker has free reign in the system. However even if the administrator logs in, they might not detect the attack.

    How often have you entered your credentials for a system and it informs you that something was typed wrong. How many times have you locked out your account accidentally with too many bad password entries?

    Have you ever thought that your account was under attack?

    I know some people will have their account unlocked and retry their password, but I know plenty of administrators that would just reset their password.

    That’s bad.

    You should always check the login times for your privileged accounts. In fact, I’d like to be sure that you are using your privileged accounts regularly so that if someone were to gain access, you’d know it. Or if someone changed your password, you’d be aware.

    Treat your sysadmin account for what it is: a privilege, and a target for attack. We are seeing more and more attacks on our systems, and I expect the problems to only grow, not shrink.

  • Hardware Upgrades 2015

    I wrote awhile back that I ordered a new laptop, and I’m excited to have that coming. However, I had a few more upgrades to add to the machine, and I decided not use to use Toshiba to add them.

    These are a few items I’ll use for speaking in 2015, and I’m excited to get a better demo setup for showcasing SQL Server and Red Gate tools.

    Memory

    I can’t demo on less than 16GB of RAM. It’s disappointing as there are soooooooooooo many more choices for hardware if I could use 8GB, but I can’t. One of our demo VMs needs 6GB, and really wants 8GB. I used Grant as a testbed in a few places, and even when he built his own VM, stripping things down, it didn’t run well.

    I ordered the laptop with the base 4GB from Toshiba, and went to Crucial for a memory upgrade. For $100 less, I got a 16GB kit for the machine. There might be better choices than Crucial, but they’ve always worked well for me and I like them.

    Hard Drive

    I need disk space to keep a few demo VMs handy, and the 128GB that comes with the laptop isn’t enough. A 512GB from Toshiba was $400. I found an end of the year deal from Crucial for an mSata drive at $270. Hard to beat that, so I’ll put that in the machine once it arrives.

    External Storage

    I didn’t want to throw away the 128GB drive away, so I also grabbed a small enclosure from Amazon that will allow me to use that 128GB SSD as a backup for my main demo VM. I can connect it to any other laptop (likely Grant’s in an emergency) and work from there.

    Travel Wi-Fi

    I’ve been trying to be more economical, and looking to potentially carry two machines for demoing things. Ideally I want a small enclosure PC that I can run demos on, and connect to it over RDP. The first step here is to get a small router.

    I found a small travel router, with two LAN ports that I’ll be testing in that capacity. If that works well, I may end up trying to get a tablet of some sort as my backup machine for classes and training, some of which we are looking to start in 2015.