Author: way0utwest

  • Actionable Metrics

    One of the tenets of Devops is that we learn from the feedback we get from our system. This means that we actually need to include some instrumentation in our application as well as a way to gather, store and analyze this data. There are various frameworks to do this in application code, but we also need to include the database in here. This might mean we need to add some logging or messaging in our T-SQL code, whether to some table or file, integrating with extended events, or something else. I wish to I had a best practice for you here, but there are multiple ways to do this and the best way for different environments can vary. At the least, you need some sort of resource monitoring if nothing else.

    We don’t want to just collect all the data we can, nor do we just pick some random items. We want to think about how our system works and then choose useful data. The key to useful metrics is that they are actionable. We can change behvaior or alter the system based on the information. In other words, we can take some action, positive or negative, based on what the metric tells us about our system. Without this, it might just be useless data that we’re collecting and storing, for no particular reason.

    Of course, we might not know how useful some metrics are for awhile, so we may need to collect data that we can’t use right now. If we find that’s the case later, perhaps we cease gathering useless data after we can’t find value over some period of time, maybe months. However, we do need to perform some analysis and investigation to determine if there is a way to extract information from our metrics.

    Ultimately I tend to err on the side of gathering a bit more data than I need, even if its value is suspect. I’d rather have more data that I need to delete than find myself wanting later and wishing I’d just collected a few more metrics. The key thing is to actually take some time to look at your data and use it to make decisions. Once you find the data helping to drive the behavior of the organization, I think you’ll start to find ways to gather more and more useful information in other systems.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The $650 SQL Saturday

    SQL Saturday #596 – Denver BI Edition was held on Feb 25, 2017. In keeping with my idea for a slimmer SQL Saturday, we ran this event for a total of $650. It’s not quite the $500 that Andy Warren challenged me to stick to, but it’s close.

    We could have run this event for even less, but since we had funds, and not much to spend them on, we decided to spend half our budget, $325, on a speaker dinner Friday night. If we hadn’t had the funds, I would have just asked speakers to meet up at a bar for a short happy hour.

    The goal was to run an event that people would enjoy, and also experiment with a few decisions to show that we could run an event for a low cost. This wasn’t simple, but it also wasn’t too hard and this post is to help others think about how they could run similar events if their budget is limited.

    Finding Space

    Getting space for an event is almost always the most difficult part of holding a SQL Saturday. I have attended events of many sizes, held in a variety of different locations. I’ve had events in businesses, colleges, elementary schools, churches, rented event space, technical colleges, and even an amusement space with a go-cart track. All of these spaces have worked, but the best places for me have been universities.

    With this in mind, I started looking last summer (2016) for some space. Using contacts, our team got a meeting with a local college, the University of Denver. We met with the one of the Deans and  a few people on his team. We described our goals, and pitched the idea for a SQL Saturday. We came away with a few possibilities.

    Across the next few months, we had a couple more meetings with the group, finally deciding to go forth with a small event, using two conference rooms that the Dean controls. Part of our decision to do so was the lack of cost. We could use the space on a Saturday for free.

    There were other space possibilities, but with small charges. We could have used a larger building, with more rooms (6-10), space for more sponsors, and even had lunch catered, but one goal was to try a small event, low-cost, aimed at both working professionals and college students.

    Lunch

    After the space, food is usually the next most expensive part of an event. It’s not just funds, since many events charge a lunch fee to cover the cost, it’s also the time and effort required to arrange for lunch, find space to eat, distribute the food, and clean up.

    I wanted to try and reduce that cost, and burden. Since we were near one edge of a college campus, we decided to take a 90 minute break for lunch, encourage people to network with others, and go find lunch themselves. We are all big boys and girls, able to find lunch away from work most days, so I didn’t expect any issues. I’ve attended numerous training events where this was expected, so why not SQL Saturday?

    It turned out not to be any issue at all. Our attendees formed groups and went for lunch, with most of them returning for the first afternoon session. We had a drop off for the last slot of the day, but overall, this didn’t present any issues. This also had the added benefit of allowing the last morning session to go long, which one of ours did, without impacting the schedule or anyone’s lunch.

    Speakers and Sessions

    When we set up this event, our goal was to program this a bit and try to reduce some of the random nature of many SQL Saturdays. Too often I’ve seen advanced sessions take place before beginning sessions, or such a random collection of talks that it seems as though every session is dramatically different from others. That’s fine, but in partnership with the university, we wanted to ensure students that might come and want to grow throughout the day would have the chance to do so.

    We ended up with many, many more sessions than we could take. With 3 room available, and of limited size (50-60ppl, 20-30 ppl, and 15 ppl), we had to make decisions. Our first choice was not to make this an overly long event and pack as much as possible into the day. We wanted to make this fun, and informative, but relaxed. As such, we decided on four 75 minute sessions, two in the morning and two in the afternoon. The 60 minutes sessions pressure speakers to limit content, and often cause people to run out of time. With only four sessions, and long breaks after the second and fourth sessions, we could let speakers go a bit long without pressure.

    We wanted to mostly use local speakers, and we met that goal. The majority of our speakers were from Denver, which gives us a local event. We turned down quite a few local and remote speakers, but since we hope to have 1 or 2 other events this year, we’ll rotate to some of our other talented experts.

    One track was designed to grow people, with an overview of BI, then a talk on how to design and produce visualizations, a short R session, and then a basic Machine Learning talk. A student or newbie to the BI area would be able to sit in this room and get exposure to different technologies, and walk away with an idea of where they might grow their learning next. The other track was more technically focused, for people that might have more experience.

    Sponsors

    We decided early to limit our sponsorships. We could have put 3-4 tables in the hallway outside our rooms, but since sponsors can eat up resources, we decided not to seek many. With the money from PASS ($250) and Microsoft ($300), we had more than enough. In fact, I made the mistake of keeping the defaults on the SQL Saturday site and ended up with Cozy Roc booking a package. We appreciated their support, and they gave away some wonderful mugs, garnering lots of attention as our only in-room sponsor.

    Fortunately I closed the sponsorship before anyone else sent more money. It’s not that more money wouldn’t be nice, but we didn’t need more. We ended up spending more than expected just because we could. I’d like to avoid waste in the future, so I want our budget to be minimal.

    We had hoped to engage a recruiter or two that might offer some assistance to attendees in job searches, but with a busy February for both Carlos and myself, we ended up not contacting any. In the future, I think we will get a couple sponsors.

    The Event

    I think things went very smoothly. People showed up ,they got coffee and breakfast, and they learned things. At the heart of a SQL Saturday, only that last item is really required.

    We had a couple volunteers to check people in, but really, I didn’t worry about that. We printed the speed passes and had lanyards to let everyone see others’ names, but I would have been happy with stick on badges that people filled out themselves. We could have marked names off our attendee list at any point in the am or during the morning sessions. Outside of understanding the total attendance and making our end-of-day giveaway easier, the checking wasn’t a big issue.

    Plenty of people showed up 60-90 minutes early, and were happy to help set signs, carry up our minimal supplies, and move tables.

    Our cleanup was minimal, and plenty of people were happy to pitch in and help. Without a lot of sponsors, and little food, there isn’t a lot to worry about.

    Attendees seemed happy, and I’ve received a few emails from people that were glad we ran the event and would like to come to another. One surprising fact for us was that many people raised their hands in the morning, noting this was their first SQL Saturday. We had marketed very little since our wait list started getting filled early on. We moved people to the regular list that week before the event, assuming we’d have some drop-off from registered attendees. We ended up with about 80-90 people walking in, which was about what we wanted and could comfortably seat.

    The Future

    Where do we go from here? I’d like to grow the Colorado Front Range community and inspire more technical people. I want to see where SQL Saturday can go, and what we can accomplish here. 

    My personal goal is to get these events in Colorado.

    • SQL Saturday Denver
    • SQL Saturday Denver – BI Edition
    • SQL Saturday Denver – xx Edition
    • SQL Saturday Denver – yy Edition
    • SQL Saturday Colorado Springs
    • SQL Saturday Colorado Springs – zz Edition
    • SQL Saturday Boulder
    • SQL Saturday Fort Collins – ii Edition

    Every year.

    Every year. I want to touch more people, who may not be able to come to the one day I hold a particular event, but they can come to a different one. I want to see many of these events around 100 people, but have some larger events if we can make them work.

    I want to have 2-3 venues in the Denver area, and hopefully 2 in the other cities, where we can spread the events around, have a backup venue if we lose one, and try to touch more people that are interested in data.

    By the way, I know that we are only supposed to have one SQL Saturday per city per year. That’s stupid. Already there are two in some cities, and no reason not to have more. PASS, remove this rule and help us grow. Or help us help you grow. Help some SQL Saturdays grow to be mini-SQL Rally’s focused on a topic, and even producing revenue for the larger organization. There’s no reason to arbitrarily place a limit on events.

    As long as they’re sustainable. That’s the key. The budget, the stress on organizers, the speakers, they all have to be able to support the events, but if they can, then why not have 3 or 4 events a year?

    I’d also like to see more experiments. One thing I’d like to try is duplicate the schedule, with the same sessions in the morning and the afternoon, just repeated. We often force people to make choices across 4, 6, or more tracks. Why not let them make some choices in the am, knowing they can see other sessions in the afternoon?

    I’d like to try half day paid pre-cons in the morning, and free, mixed sessions in the afternoon. Or maybe do sessions alternating with panel discussions and Q&A in different topic areas.

    I’d like to see other people try different things. Andy Warren really set most of the SQL Saturday patterns, with Orlando experimenting a little each year. Few people have deviated from the model, but I’d like to see that change. Do what works for you. Make a small event, make a large one, pick few speakers, pick lots. Make it cheap, make it lavish, but make it work for your city.

    Above all, and I challenged Catherine Wilhelmsen at our event to this. While I still want 500 SQL Saturdays a year, I want to hit SQL Saturday #1000 by Dec 31, 2018.

    We can do it. You can make it happen, but growing your community in your area. Don’t bring me problems, bring me solutions to make this happen.

    Please don’t disappoint me.

  • Containing the Work

    Containment is becoming more prevalent in the computer world. We have containers coming to Windows Server 2016 (or earlier versions with WinDocks), a concept that’s been embraced in the Linux world for some time. If you haven’t looked at containers, they are a great way to isolate and stabilize your application environment. They can also be great for dev/test environments. You might do a little reading to understand how they can be used.

    While containers might be good for SQL Server (or maybe not), we’ve had other types of containment in SQL Server. We got partially contained databases in SQL Server 2012, with little change since then. That is susprising to me since I expected that all Azure SQL Databases would be contained, and we would want more functionality inside of them. However, those have opened up slightly and I’m not sure where containment is going.

    Personally I’d like to see jobs contained inside of a database. The vast majority of processes I’ve written over the years for application or system work on a database pertained only to that database. I’d like to see things like backups, maintenance, etc. contained as jobs inside a database, that travel with the database as I detach/attach it, backup/restore, and more. That might eliminate some of the issue with clusters and AGs if most jobs moved with a database.

    That got me thinking. What else would we want to contain? The thing that comes to mind is a distributor. I’d like to have a distributor somehow contained with all the information about replication processes contained inside of this database (including the jobs). The database would need a way to register with the instance so the publisher and subscribers could find it, but wouldn’t that make replication administration easier?

    How about the SSIS Catalog and ETL information. Having more information here as a contained database might make managing ETL operations easier, especially if we need to move the load to another instance.

    I’m sure there are other ways in which having information, metadata, and job functions contained inside of a database would be helpful, and easier on the administration of the system, whether by humans or automated systems.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Dots, Skills, and SQL Clone: A Contest from Redgate

    Redgate just launched SQL Clone, a new product that can provision copies of databases in seconds. By copies, I mean full sized, up to 2TB copies of your database, each separate and isolated, and built in seconds. What’s even more amazing is that each of these copies takes just a few MB of space.

    echodotAs a launch contest, Redgate is giving away 5 Amazon Echo Dots to some lucky individuals. The overall winner will also receive a copy of SQL Clone. To enter, you need to to tell us what “skill” for Alexa you would build. Perhaps you want to know if any backups failed. Maybe you want to know how many deployments you’ve made to your database. Perhaps you just want to have the Dot remind you to pull the lasagna out of the oven. Just let us know in the comments section below to enter and good luck.

    At Redgate, we had some fun with SQL Clone and an echo, building an Alexa skill that provisions a database using your voice. Not terribly practical, but that might be fun if a developer gets annoyed and wants to yell at Alexa to create a a new database clone. You can see how they built the real skill here (or watch the video).

    Provisioning a database with SQL Clone and Alexa

     

    Over the last few years I’ve been talking DevOps for databases with lots of people, and one of the obstacles many people face is using a shared database for development.

    Why? Often because the developers need a certain amount of data and they use a copy of production. They also don’t have (or want to use) the disk space to give everyone a copy of the database. With SQL Clone, you can see that my 97GB database uses only 49MB for each developer.

    2017-02-22 17_11_05-SQL Clone

    Take a walkthrough of SQL Clone and see how it works for you. Download the 14 day free trial and see how SQL Clone might help you smooth your database development process.

    Terms and conditions apply: http://www.red-gate.com/products/dba/sql-clone/entrypage/competition-terms-and-conditions