Voice of the DBA

Author: way0utwest

  • The Command Shell

    Security holes are all around. Are there any in xp_cmdshell?
    Security holes are all around. Are there any in xp_cmdshell?

    Recently I heard a few people arguing over the use of xp_cmdshell in a particular situation. One person was adamant that there was a security risk in using this feature. Many of you probably feel the same way, and even the SQL Server platform has recognized there could be dangers with this feature and has it disabled by default, as part of the secure by default installation.

    However the security around this procedure has been improved over the years. Non system administrators cannot execute xp_cmdshell by default. Administrators can open up access using a proxy account, but this requires specific configuration changes by administrators. This means that a lot of the danger of using xp_cmdshell for administrative tasks has been removed.

    Or has it? This Friday I wanted to poll you and find out what you think. Many of you are creative in how you use SQL Server and will think of possibilities that many of us would not consider.

    Is there a security risk in allowing xp_cmdshell to be used by members of the sysadmin role?

    I’m not looking for potential issues if a proxy account exists. Instead I’m asking if there are real dangers in allowing administrators to use this tool? I assume you trust your administrators and they will not maliciously use this tool to cause issues in your SQL Server. Let us know how you feel this week.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • The Great Laptop Search of 2013

    It’s time for a new laptop. I don’t think it’s time, as I love the Macbook Air I bought a couple years ago. Rather some requirements for work, mainly Hyper-V, have come up and I can’t meet them with the Macbook Air. Even a 2013 summer refresh of this platform likely won’t get me what I need and I’m back to Wintel. I have a sad face, but since my daughter will likely inherit the Air, she’s smiling ear to ear.

    However that means I have work to do. Grant Fritchey went with a Lenovo W530, just getting a powerful machine. I had that form factor with a W510, and I had issues with the suspend/hibernate. However I also didn’t love the size of the machine. It was heavy and cumbersome and as much as I travel, I’d prefer something lighter.

    Ultrabooks are tempting, though I seem to only find them with 8GB of ram. The bare minimum I need. The IT group as Red Gate told me that I needed:

    • Windows 8 or Win 2012 for the Hyper-V images
    • Core i7
    • 8GB of RAM at a minimum, being prepared to give 6GB to our complex VM system.
    • 100GB of disk space
    • SSD preferred.

    That doesn’t’ give me a lot of choices in the sub 4lb range, 13” display range.

    If I was going to get a new laptop, a touch screen with Windows 8 is something that came to mind first. With that in mind, I started looking at the Lenovo X series, since I’ve had a few friends that liked those models. The X1 Carbon Touch got my first look. It’s a nice looking machine, but I’ve seen a number of complaints from people about order fulfillment for this model. That concerns me, since I would guess some of these issues are problems with the hardware. A few people I know have gotten the W530 in the same timeframes and there haven’t been issues. Besides, this machine tops out at 8GB, which is the base minimum I need.

    There were a few other machines I looked at, all in the touch screen, convertible form factor, which were interesting, but unfortunately these topped out at 8GB. That’s plenty for an ultra book or convertible that runs one operating system, even a couple virtual machines. However I don’t have control over my demo image, which others have noted really needs 6GB to run smoothly. The list I considered and rejected was:

    I suspect 8GB could work, but I don’t want to be limited if it doesn’t. We have a complex set of VMs to work with, and I think I need more RAM to be safe. After my post on the Surface Pro last week, I had a few other recommendations.

    I decided to examine each of those over the weekend and make a decision.

    I first looked at the ASUS. I’ve seen a few developers that liked this model, and it looks interesting. I can upgrade it to 10GB, which gives me a little breathing room. It’s small and thin, lightweight, and looks good. However I decided to skip this because the 10GB makes me only slightly less nervous than 8GB. I decided to check other options.

    The Toshiba is a nice machine. I used to have a 15" Qosmio, and I liked the machine. I ditched it when Toshiba wasn’t very helpful with Windows 7 drivers for my model. My son had it for about 6 months before the motherboard died, but during the 3 years it was my main machine, it worked well. The R930 looks nice, and has most of what I need. Worth considering further.

    The Sony was interesting. I’ve never had a Sony, but I’ve heard good things from people about the laptops. The Duo would have been a choice for me if it held more than 8GB. The S Series is interesting, and it goes up to 12GB of RAM. That’s likely enough for me. The display is 1600×900 and it’s under 4 lbs. It seems to meet all the criteria. A few reviews didn’t love the keyboard, and they mention going to 12GB of RAM means single channel access. I’m not sure how big a deal this is, but it’s one item.

    The Lenovo T-430 meets my criteria as well. A nice 1600×900 screen, 16GB of RAM, and it has the eraser head mouse movement. I have liked that format since IBM introduced it in the 90s. This one is a touch heaver, but it gets good reviews, and it’s a format I’m familiar with. It also has the advantage of the switching out the optical drive for a second SSD. That is what I did in my W510, and it’s what I’d likely do here as well. The T431 looks better, but since it’s not yet out, I can’t consider it.

    In weighing all the options I finally decided to get the Lenovo T431. The Toshiba was close, but I know the Lenovo product and the ultra bay that would let me stick in two drives was appealing. The Sony worries me slightly with 12GB with mismatched memory. Not a lot, but all things being equal, the T431 seems like a good fit for me.

    It’s ordered, and on the way. As soon as it comes, I suspect I’ll be looking for a new laptop bag as well, but we’ll see how this one fits in my existing bag.

    Now to learn a bit more about Hyper-V.

  • The DBA Team #1–Code and Slides

    Our first DBA Team event, in Richmond, VA just before SQL Saturday #187 went well. Overall I think our experiment was a success and we’re already talking about where and when we might do this again.

    In the meantime, we didn’t make a separate site for this series of events, being an experiment and all. I’m adding this post as a placeholder for the various slide decks and code.

    Core Monitoring for SQL Server (Steve Jones)

    Good DBAs ensure that they are always aware of the state of their instances. All systems should have monitoring in place, not just so you know when things go wrong, but so you understand what a normal workload looks like and can plan for the future. This session will cover the basics of monitoring a SQL Server system and the various metrics you should be tracking.

    Getting Started with SQL Server Backup (Grant Fritchey)

    Backups are fundamental to protecting the investment your business has in its data and they’re the foundation of disaster recovery planning. We’ll go over best practices for database backups, to ensure you’re establishing that foundation correctly within your systems. This introductory level session covers full, log, and differential backups, as well as restores and restores to a point in time. Come along to be sure you’ve got the right protection in place for your systems.

    Understanding Database Corruption (Grant Fritchey)

    A DBA’s primary purpose is to ensure that the information in their charge is accessible by the correct people within their organization. Despite everything you do to make sure you’ve got your servers configured, monitored, and tuned, with well-tested backups in place, you can still lose data through corruption. But what is corruption in a database? This session lays out exactly where database corruption can come from, how to find out exactly where the corruption is within a database, and  the methods you have available to recover from database corruption.

    Indexing for SQL Server (Steve Jones)

    Indexes are important for improving the performance of your queries, but they add overhead to your server and require maintenance. This session examines how indexes work and the basic maintenance that you should perform to ensure your system is running at its peak level.

  • What Do You Want to Know About I/O?

    Hope to see you at the conference.
    Hope to see you at the conference.

    There’s lots I want to know. What affect does an SSD have if I use it for a log file? One of my filegroups with indexes? What about tempdb? How do you dig in and prove latency to the storage groups from within SQL Server? Will the query optimizer take advantage of information about drive performance? There are lots of questions I have, and I’m hoping to get answers. I’m hoping to attend the “Ask Anything I/O” panel at SQL Intersection in a few weeks. I’m leaving the event Wed night, so unless there’s someone I’m trying to catch in a last minute meeting, I’ll be sitting in the audience.

    I am guessing that Brent Ozar, Kevin Farlee, and Mat Young will have lots of answers for me and others. I’m also guessing most of the questions will be met with references that are already out there, and I hope that the presenters will publish a list of questions and answers for attendees. That’s fine, as there are lots of times when I’m looking for information that has been published, but I can’t find in the ocean of Google results. Attending a session given by experts can be a way to shortcut some research and searching to find where the answers are located.

    That’s one of the reasons to attend a conference. Getting quick answers from experts, or learning how to better target your research. All the speakers at SQL Intersection are well known, longtime, expert SQL Server users who are friendly. They’re more than willing to answer questions, or help guide you to solve a problem or learn something new.

    The other great thing about conferences? You get to hear some great questions from other attendees. We all attack problems differently, and work in very diverse environments. There are many times I’ve heard how another person has implemented SQL Server and it’s intrigued, or even inspired, me to try something similar with my instances.

    If you can find the funding, SQL Intersection is a great event to attend, with an impressive list of speakers, each hand picked by Kimberly Tripp and Paul Randal of SQLskills. They provide amazing training in their Immersion events, and are trying to bring even more options to you with SQL Intersection. You can even walk away with something tangible in addition to all the knowledge you’ll gain: a Surface Tablet.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.