Voice of the DBA

Category: Uncategorized

  • We Have a Date

    For nearly half of my life, I’ve looked forward to new versions of SQL Server. At first I was just hoping to see SQL Server run on a stable OS, as the OS/2 1v.3 was my original installation. That system couldn’t run 12 hours without someone restarting it, and the upgrade to SQL 4.2b and OS/2 2.1 was welcome. That change reduced my schedule below the 100hr/week level. It wasn’t long after that we upgraded to Windows 3.1 Advanced Server, and never looked back. Since then, I’ve upgraded through all the versions since. From 6.0 to 6.5 to 7.0, and then all the 200x versions.

    This week, Microsoft announced the general availability of SQL Server 2017 will come on Oct 2, 2017. This will also be the version that debuts on Linux and in Docker. I’ve been running SQL Server on Linux, and overall, I enjoy it. I’ve experimented with Docker, and since I often have multiple versions running, I think containerized SQL Server is likely the way to go for development systems. Perhaps even further downstream at some point. We’ll see, as I’m not completely sure that containers are the best choice for database systems, but I am considering them as a possible replacement for VMs.

    I haven’t always thought every upgrade of SQL Server was worth installing for many data professionals. I think a few versions were really point releases, incorporating limited changes that affected few people. For my own systems, I’ve often looked to skip a version, though that depends on my needs. I think SQL Server 2005, 2008, 2012, and 2016 were worthwhile upgrades with lots of new features. However, some of those features are immature, so perhaps 2008 R2, 2014, and 2017 were better choices for moving your production systems.

    There aren’t a lot of changes in SQL Server 2017, but the addition of Linux and the query plan improvements will be helpful to many of us. If you are looking to install a new system, take a look at this version. It’s a more solid SQL Server 2016, even if you don’t need the new features. I am happy that Microsoft is releasing more often, giving me the option of adopting new features quicker. I don’t know if I’d want something like graph database capabilities, but I’m glad I get the choice in 2017 rather than having to wait until 2019 or 2020.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Database Mirroring Needs FQDNs

    A quick basic post, and one that I’ve forgotten. Since blogging is a good way to remind myself of things, here goes.

    I was testing Database Mirroring (DBM) recently for an upgrade situation. I’ve set up it up in the past, but since it’s deprecated, I’ve moved on to working with Availability Groups (AG) for the most part. However, mirroring might still be in use for you, or you’re looking to perform a simple rolling upgrade, and DBM works well.

    I restored a database on a new instance, opened the firewalls for 5022, and then went through the mirroring wizard. Once I was done, I enabled mirroring on the secondary database (the one restoring) and that worked fine.

    Then I ran this on the primary:

    ALTER DATABASE Baseball SET PARTNER = 'TCP://192.168.1.201:5022'

    After a few minutes, I got an error:

    Msg 1418, Level 16, State 1, Line 10
    The server network address “TCP://192.168.1.201:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    I tried all sorts of things, including shutting off firewalls, and disabling the rebuilding mirroring. My endpoints were fine, the domain accounts running the instances had access, but it wouldn’t work. I tore down mirroring and added it back, verifying each machine could see the other by name. I tried again.

    ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02:5022'

    I knew I had a problem when this took more than 5sec to respond. Again, an errror.

    Msg 1418, Level 16, State 1, Line 10
    The server network address “TCP://SQL02:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    Finally I tore things down again, deleted endpoints, but this time I connected to the instance with an FQDN and configured things. I made I connected to the mirror with an FQDN as well. Finally things worked:

    ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02.HOME.XXXXX.COM:5022'

    I must have read the documentation numerous times, each time reading the FQDN, but somehow thinking that couldn’t be the issue in a small network.

    Hopefully this blog will help me remember.

  • A Bad First Day

    I’ve started quite a few jobs throughout my career. In college, I worked in a variety of restaurants, changing employers at the start of every summer and again at the start of the school year. At some of those positions, I had a hectic beginning, with the staff shorthanded days and no one to train me, but they needed me to jump in. More than a few bartender jobs started like that, with me jumping in, making drinks, and teaching myself where things were stored. Luckily there weren’t a lot of mistakes I could make that might severely damage the business.

    Fortunately the frequency of new jobs has declined over time, though if I changed positions at this point, I’d go through much of the confusion that many new starters have. Where are things, how do I get work done, what credentials and procedures do I use, and more would be the questions on my mind for a few days. Perhaps a DevOps shop might smooth the process out and prevent issues, but I could see how this type of mistake could be made: a new intern used a document to setup a database, and ended up crashing the production database.

    There are lots of comments on that post, but essentially a setup document contained production values (url, user, pwd) and a junior developer used those values instead of the output from their local laptop. I common mistake. A document should have no ambiguity, and certainly should never have production passwords inside of it. Individual credentials should always be used to connect to sensitive systems, with some auditing of who connected (and hopefully what they did).

    I’ve had developers crash databases before and delete data. If they’re databases I manage, there’s a backup. I’ve never been in the situation where a database I was managing didn’t have some backup. However, I’ve had clients and coworkers managing databases with no backups. Perhaps the situation I felt lots of empathy for was a client that had developers making backups before they deployed updates to a client database.  One day an update didn’t work correctly, and it asn’t until a day later they realized. At that point, they couldn’t restore the backup. They called me, only to have me inform them that they were actually making striped backups. Unfortunately, they had deleted one of the stripes, and we couldn’t find it. Data loss, that fortunately didn’t kill the company, but it did cost quite a bit.

    There are any number of ways that a person can make a mistake on their first day. To prevent this, a good process should limit the damage that an individual can cause because of ignorance. This might be especially important for anyone that might have access to production data.

    Steve Jones

    The Voice of the DBA Podcast

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

  • How Can I Get Accepted to Speak at the PASS Summit?

    There has been some discussion and concern over the changes to the speaker selection process for the PASS Summit. I’ve written some thoughts, as have others (Joey, Andy), but I was struck a bit by Eugene Meidiner’s post. In it, he asks, how can I speak? What do I need to do beyond presenting at user groups or SQL Saturdays? How do you get to the Summit?

    I’ve got a few thoughts, but keep in mind this is a complex topic. I’d like to see some people write about their experiences, preferably those that have spoken only once or twice.

    The Path

    First, there is no one path. Unlike martial arts, or some other program, there isn’t a series of things you need to do in order to get selected. What works for one person might not work for another.

    It’s like getting recognized by Microsoft as an MVP. There isn’t a set of things to do, but some guidance that may help.

    Compete

    The next thing to understand is that this is a competition. Imagine that you want to run in the Olympics as a sprinter. You can do everything possible to increase your speed. You might be the best sprinter in your college, or region. You might run the 100m (mens) race in 9.90 in college. When you get to the trials, you run a 10.0.

    That’s not good enough if the top three go to the Olympics that year. The best three times at the trials were 9.8, 9.84, 9.98.

    Does that mean you’re not an amazing sprinter? No. You just weren’t one of the those selected that day.

    The PASS Summit speaker selection is way more complex. We divvy up the abstracts into various piles, mostly based on the topic area. If you look at last year’s schedule, there were five main areas. Your session is in one of those areas, competing with many others. If we pick (roughly) 60 sessions in an area, you have to be top 60 to make it. Doesn’t mean you’re not a great speaker, or don’t have a great session. It means you didn’t compete well enough at this time, for this event.

    Get Help and Get Better

    The way into the Summit is to get better at your craft. That’s better at your speaking craft, better at your abstract writing, certainly better branding yourself as a SQL Server expert. It’s not that you need to be smarter, better at the Microsoft Data Platform, but people have to think you are worth going to see.

    Adam Machanic has a great post on session abstracts. Louis Davidson has thoughts, and Brent Ozar has hints. There’s no magic formula. You have to build something that catches the eye of your peers, who are those reviewing the abstracts, and you have to impress them with the talk.

    Most of these people are volunteers that have attended lots of talks at events. They’ve seen lots of speakers. Certainly there can be bias towards those people whose sessions they’ve seen, or who they think has submitted an abstract, but mostly they’re looking for great content. Something they’d consider attending, or think others would.

    That’s subjective and unlikely to get much better. At some point someone has to make decisions, and so you need to ensure you impress people.

    Use the advice that’s out there. Have friends, or even more experienced speakers review your abstract. Try to find a good place to submit. If you want to talk T-SQL tricks, you’re going to compete with Mr. Ben-Gan, Mr. Betrand, Mr. Kline, and plenty of experienced speakers. Be careful of that. Find a place where you solve a problem, do it well, and it’s interesting to others. You won’t know if that’s true in a vacuum. Get feedback and advice from others.

    Gain Experience

    Part of the process is getting experience. This means presenting often, and learning to do it well. You need to ensure the audience likes watching your presentations.

    • Are you invited back?
    • Do multiple people tell you afterwards you had a great talk?
    • Do multiple places accept your abstract?

    You should be answering yes to all of these.

    My suggestions:

    • Present multiple talks for one group –  Learn to express yourself, communicate well, and handle different demos. You should get positive feedback from your local group, your virtual chapter, something.
    • Present the same talk to multiple groups – One of the things that helps me get better is to deliver the same talk more than once. I learn how to build better flows, how to correct previous issues, and explain myself better.
    • Enjoy Yourself – If you don’t really enjoy teaching people something, or impressing them with your knowledge, why do you want to speak at the Summit? Is it resume bullet? A bucket list item? Those are fine, but passion and enjoyment shine through.

    Cross Your Fingers

    This is a hard competition. Lots of people want to speak, and if you have submitted 4 times and never been picked, that doesn’t mean PASS is out to get you. Or that you suck. It means you didn’t make it over the bar those four times. Whether you did the same thing or four different things, the results could be the same.

    This year, I’d suggest that you try a multi-pronged approach. Refine something you submitted last year, with feedback from others. Add something new, but make sure you get feedback on this as well.

    Don’t give up, but don’t get too frustrated. At least this year if you’re a first time speaker, things are slightly easier. Invites mean that you aren’t competing against someone like Itzik Ben-Gan or Brent Ozar, who likely get invites. Therefore your abstract isn’t weighed against theirs. A focus on getting some level of new speakers mean you should get a little weight added to your submission as well.

    Enjoy the challenge, do your best, meaning put effort in, and let the chips fall.

    At least you won’t compete against me this year. I won’t submit, so there’s one or two slots that will be open.