Voice of the DBA

Tag: T-SQL Tuesday

  • Code I Can’t Live Without–T-SQL Tuesday #104

    tsqltuesdayBert Wagner has a good invitation this month, a T-SQL Tuesday question about code, specifically code you can’t live without. I’ve got my thoughts below.

    This is a monthly blog party, and you can participate. Write a post on this topic, and publish it on your blog. Drop a comment on Bert’s post. You can read the rules on his invitation, but you can really post anytime. It’s fun and good for your brand.

    The Critical Code

    I’ve managed lots of instances in my career. Some mission critical, some just important, some not important to me or the business, but important to someone.

    One thing I’ve found is that there are plenty of common things I do on most systems. I’ve had lots of code that I’ve written to manage all aspects of DBA work, from backups to maintenance to monitoring. I’ve had routines that handled all sorts of security or auditing.

    The code that I can’t live without, isn’t my code, but I’ve used it on probably every instance I’ve managed at some point. The code is actually from Microsoft, and it’s indispensable.

    The code is sp_helprevlogin from this support article.

    While I tend to be distrustful of keeping passwords the same for too long, and I usually don’t attempt to recover them in any way, just reset them. There are cases, however, when we’re moving an app, failing over to DR, or recovering some system and we need to keep the password the same.

    At least in the short term. When systems are down, I need them back up, and I can argue about a password reset later.

    This has been the most useful piece of code for me, and one that I think most of you could use as well.

  • It’s Azure T-SQL Tuesday #103

    tsqltuesdayI’m slightly surprised that there haven’t been more invitations about Azure, with the main development branch of SQL Server being in the Azure cloud, but I’m glad this one came along.

    The invitation is from Björn Peters, and he asks us to write about Azure SQL Database or Managed Instances. We can really write anything that’s a part of the platform, which gives us lots of choices.

    I’ve got two quick items.

    T-SQL Tuesday

    Did you know tsqltuesday.com is hosted on Azure? It’s actually at http://tsqltuesday.azurewebsites.net. When Brent gave me to the domain, and Adam wanted a quick site, I provisioned a WordPress site on Azure in a few minutes. It actually took longer to square away the initial DNS and domain name movement than getting the site up and configured. Templates and known configuration settings help to ensure that you can build a new version of xxx, very quickly.

    The cost was low as well, about $3.60/month and initially $5/month for the hosted MySQL database.

    This changed last year, when the MySQL hosting option was discontinued. I could have upgraded to $15/month, but decided to experiment. Azure has a MySQL as a Service PaaS offering for the same $15/mo, so I moved to that. I was somewhat amazed how easy it was to provision this database on my own and get things running. While the site costs me a little money, it’s a donation to the community, and I’m happy to see so many people participating in the monthly event.

    Quick Labs

    One of the things that I needed to do last year was setup a new cluster and Availability Group for a proof of concept. I have typically done this by getting some VMs ready on my desktop, making connections, etc. However, in this case, I decided to use Azure instead. I was curious about whether this was faster or easier.

    It was.

    Across a few days, as I learned a bit about provisioning new machines and connecting them with networking, I was able to get my set up with 3 machines, a load balancer, storage, etc. I had a working set of Availability Groups, that I could access from anywhere.

    Then I tore it down and started again.

    One of the great things about Azure is that you can use the Azure Resource Manager to provision new services fairly quickly. It’s not as instantaneous as you would like, though it’s not bad. I think the fact that you request actions and then wait until you are notified of something completing in the portal makes the process seem slow.

    In my case, the few days became < 1 hour the second time around.

    Plus, I didn’t use resources on my local machine, where I might have to balance the load with other tasks. I could also shut down my system and reduce costs. With ARM, I could even get rid of the lab and rebuild it in an hour in the future.

    To me, this is the place where the cloud makes lots of sense. If you have a load that is sporadic and can be up or down on demand, then the cloud is helpful. You can remove resources you don’t need, only paying as you go.

    If I were using this every day, I’d probably stick with the local system, but for a periodic use item, it makes sense to fire up the lab when I need it, rather than keeping it alive all the time.

    I look forward to doing more with Azure in the future and learning even more about how to build up the resources I need, when I need them.

  • T-SQL Tuesday #102–Getting Started

    It’s time for another blog party, on the second Tuesday of the month. For this month’s T-SQL Tuesday, Riley Major has a couple of invites. One is on giving back, which is good, but doesn’t fit me. 

    The second option is to explain how and why we got started, so here goes.

    Giving Talks

    I started SQLServerCentral with Andy Warren, Brian Knight, and a few others. The site had unexpected success, and running this in addition to all of working full time jobs was tough. However, we built the site as a place where we could share knowledge and help others. In the first year, I’m not sure if I enjoyed writing articles or answering questions more, but both kept me busy.

    At some point, the Boulder SQL Server User Group invited me to give a talk. I’d been to the Denver group a few times, but hadn’t really thought about speaking. Bill Wunder was the person that convinced me to share some knowledge live and I went. I’m not sure I did a great job prepping and delivering information, but it must have been fine because I got invited back.

    At first I didn’t love the experience. It’s a lot of work and in this case a few people were critically listening to and questioning me on whatever topics I chose.

    A couple years later, we talked about doing a session at the PASS Summit. I hadn’t spoken there, but Brian convinced me we should do a debate on a topic and try out a new type of presentation. Looking back, this was really basic, but it was helpful and useful to do a few things.

    1. Have someone on stage with me to carry the load and divert some attention away from me.
    2. Pick a topic that I felt I knew something about
    3. Use the format of a debate, since we argued regularly, I knew we could pull this off.

    It went OK, though looking back, this was a rudimentary and unpolished session. Still, people seemed to enjoy it.

    Fast forward a few more years. Andy and I had been talking about SQL Saturday and kicked it off. At first I wasn’t sure this would work, and I missed SQL Saturday #1 in Orlando. However, with a few events in other locations, I decided to fly down for SQL Saturday #8.

    I can still remember sitting on an airplane, looking out the window as we descended to Orlando being amazed that I was traveling outside my local area to speak. I hadn’t delivered a talk at any conference at this point, so I was a little in awe of the experience.

    I had an afternoon talk, so I had the chance to watch others, but was a bit nervous. My room filled up, with all 20-30 seats taken and almost an equal number of people on the floor. Fortunately no fire marshal or administrative people were around to thin the crowd. The talk went well, and I felt confident walking away that I could share things.

    More importantly, I couldn’t answer every question, and had some “I don’t know”s or “I’ll check and get back to you” moments. In spite of those, I got great reviews and comments, so I continued on.

    From there, I submitted to more events, and different events over time. I’ve been accepted at most, rejected a few, and cancelled rarely. Life does get in the way, and I’ve learned to try and not over-extend myself. I’ve also learned I can say no. I do that more than I’d like, but I’m at peace with my decisions.

    I’ve even gotten over rejections. Plenty of large conferences have declined my submissions and even small ones do at times. SQL Saturday #735 – Finland just declined to choose me, and that’s fine. It’s their event, and they should pick the speakers they want, especially if they are local to the area.

    The one thing I’ll leave here is that I was terrified to present to groups in high school. I’d be nervous, avoid making eye contact, and try to say as little as I could. Over time I learned I needed to talk to other people in various jobs. Often 1 or 2, but sometimes a group of 5, 6, 10 or so. That’s really a presentation, albeit an off-the-cuff one. I learned that explaining a technique to another sysadmin isn’t far off from talking to an audience. It’s different, but not a lot.

    I still worry about new presentations, and I do try to practice them not only walking in circles around the office, but often with a smaller group. I would encourage you to try presenting, usually at work, because these are skills that are useful. If you’ve solved a problem, or fixed something, you can share. Give a 15 minute talk at your user group. You might not like it, and that’s fine.

    If you do, however, I’d love to see you start speaking at a SQL Saturday or other conference. I’m more than happy to give up a speaking opportunity if it gets more people to share their knowledge.

    Heck, I’ll even co-present with you if I’m at the event. Just ask and I’ll stand in front of an audience with you.

  • T-SQL Tuesday #101–Tools

    tsqltuesdayThis month’s T-SQL Tuesday is brought to you by Jens Vestergaard. The invitation for #101 is for essential SQL Server tools. Since I work for a tool vendor, you can guess that I’m going to be shaded that way.

    This is the monthly blog party on the second Tuesday of the month. You can participate by writing your blog, linking to the invite and leaving a comment there (or a pingback). This is the creation of Adam Machanic, and if you want to host, drop him a note.

    My Essential SQL Server Tools

    There are really three main tools I’d say that are needed for SQL Server work. At least, these are the ones I need.

    1. SSMS – It’s free, and it’s the main tool for connecting to SQL Server. You might like VS Code or some other editor, especially on other platforms, but really SSMS is required for most people.
    2. SQL Prompt – It’s expensive for some, but after using this for years, I think the the native intellisense hurts more than helps. Just formatting alone make this critical for me. I’m moving through quicksand, mixed with molasses, when I don’t have Prompt installed.
    3. Git – Another free one, but an important one. As we write code, SQL Server will only keep the latest version. Much of what we do means that we need to understand where we came from with code. We do have integrations at Redgate for SQL code, but no matter what, just having a record of work is important, especially with multiple people. I love git, but if you want SVN or something else, use that. Just use something.

    These are the main ones. Making these the main ones doesn’t mean that others aren’t important, but it just means these are the top of the list. After these, you can add in others. Here are the ones that I think are important for getting things done in a modern, multi-environment world.

    SQL Search – From Redgate, but useful for finding things in a large environment. Way better than browsing.

    Plan Explorer – I think SQL Monitor has a nice plan tool, but for those of you writing code and wanting to test it, this is way better than the native plan viewer.

    DLM Dashboard – Free, and let’s you see and track those changes in production. Find out when code is being released and you aren’t aware of it.

    Sublime Text – I tend to do some work in other languages, and in text, and having a quick, easy to use editor is nice. I think Sublime is great and worth the small fee.

    Password Safe – Having long, secure passwords is important. I don’t try to remember them anymore, and I do want separate ones for every site. I like this open source tool, and have ports on my mobile devices.

    Dropbox – Critical for me, since I use files on multiple machines. I keep copies of my presentations, password safe files, and more in sync across devices with Dropbox. Plus I can scan in docs from my phone if I need to send and share them.

    dbatools – This is an amazing project. I hated working with the sqlps module and really avoided PoSh for many things before dbatools. Give this module a try and you may learn to love PowerShell.

    SQLServerCentral – I’m biased, for sure. However, everyone could use a few things that we provide. A community, where you can get inspired and learn things (daily articles and editorial). A way to ask questions and get answers (our forums), and a place to keep scripts that are helpful for you (Scripts and Briefcase). These are important to rounding out your career, beyond just your current job.

    There are plenty more choices, and certainly lots of tools within SQL Server. You should know about Extended Events, or Query Store (or Open Query Store) and more. Learn to use the resources you have, practice and test with them, and if you need a paid tool, don’t be afraid of asking. If the tool provides an ROI, it’s worth it. If it doesn’t, tell the vendor why.