Voice of the DBA

Category: Blog

  • 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.

  • Building Connection Strings

    I ran across a great article on Connection Strings from my good friend, Allen White (b, t, c), recently. Quite a few of the items he mentioned in there, I knew, but it was a good review to stop and think about how to actually connect to systems. Especially as many of us will be moving to more complex, distributed environments that might include cloud (public or private) resources.

    The Address

    The address part of the connection is simple for many people, but often misunderstood if you’ve only worked in single domain with default instances. Many people are used to just putting in the name of the server and connecting from SSMS or some other dialog based application.

    However I do think it’s important to understand that connections to SQL Server are just like browsing the web. For example, I can do this:

    tcp:xxyyee.database.windows.net, 55345;

    This is a connection of the format:

    protocol:name of server, port;

    We don’t often worry about protocols, but in case you might want to ensure it’s a TCP connection, specify it. This is like:

    http://www.amazon.com

    In this case, I’d specified http. However you should try ftp:, https:, or other protocols in your browser. You might be surprised how often these work.

    The server address is a fully qualified domain name (FQDN). In a single domain, local network, you often just put the name of the host, but you should be aware that any address works.

    The port is 1433 by default, but you should be aware that this can be changed by the SQL Server administrator in the Configuration Manager, in Services, or even with firewall rules. If you’re ever in doubt, specify it.

    Security

    I hadn’t thought about security being more than trusted or not, but Allen pointed out a couple of settings I need to use. He talks about using SSPI to ensure Kerberos is used and also to set a value to encrypt the connection string. Both are good ideas.

    Five Minutes of Learning

    Read Allen’s article and learn a bit more about connection strings, or review your knowledge. Little pieces like this can help keep your skills sharp and allow you to pick up tidbits of knowledge that might really help you some day.