Voice of the DBA

Tag: T-SQL Tuesday

  • Another Brick in the Wall–T-SQL Tuesday #105

    tsqltuesdayIt’s that time again, and this month we have a good topic from Wayne Sheffield. We’re asked about getting stuck, about being blocked, about encountering a brick wall.

    I have to say that I don’t think I encounter these very often, usually because I try to be effective, even if things get ugly in code. The important thing for me is to deliver value. That doesn’t mean I build slapdash applications and leave them. I do my best, and try to continue to learn to build things better. However, in the heat of the moment, I might need to do something silly that I end up either rewriting later or having someone else rebuild the solution.

    With that…

    The Large Aggregate

    I once worked for a financial services company. This was decades ago, back in the age of spinning disks, SQL Server 6.5, and flip phones. We had a number of clients on our main application, but one large client that dominated our business.

    We needed to provide quarterly summaries of activity and portfolio performance for all clients, but for this main customer, they also wanted us to add monthly reports. They were actually their own management firm, but we provided them with back end services. Within a month, they would have millions of transactions that we had to group by some organization and then calculate performance.

    This wasn’t a big deal with other clients, though most of them had relatively simple calculations. For this client, however, they had separate rules for certain types of activity and the result was a calculation that we couldn’t easily get into CASE statements or other types of query workflows.

    In trying to handle all their requirements, I (and my junior team), got stuck. We couldn’t come up with a set of queries that would work. We didn’t want to go down the road of writing custom queries for each organization as this would be an ongoing nightmare as new requirements were introduced.

    We had delay after delay in trying to implement their monthly reporting and pressure kept mounting. Eventually we turned to using temp tables and making different passes through them to handle specific requirements. We had ugly flags in our temp tables to let us know which set of rules we’d applied and which we hadn’t.

    The code worked, but it was slow. Too slow, in fact, and my boss wasn’t pleased. After a particular unpleasant dressing down, I was wondering if this was the career for me. I’d talked with friends, posted questions, but in this pre-SQLServerCentral era, there were limited places to get assistance. I had hit brick wall.

    I never got to find a solution to the problems. My boss at the time was one of my worst, and he became petty. I started to get assigned various inane tasks, which were slightly annoying. However, on a night off, while on a date with my wife at a concert, he paged me with an emergency. I had to leave the show to answer and call in. When I did, he told me this was just a test to see if I would respond, then berated me for taking more than 5 minutes to do so.

    I quit the next day.

    Perhaps not the best reaction, but between the brick wall and poor treatment, I decided to move on. Hopefully someone else managed to solve the the performance issues and help the client.

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