Tag: syndicated

Using tSQLt to Find Min/Max Times

I love tSQLt. It’s a good way to write tests that can determine if your code is actually working. Since I’m a fan of unit testing, I think using tests to verify your logic is great. What’s excellent with tSQLt is that I can verify a number of cases at once.

I ran across this post asking for help with a query. Given the sample data and results, I wrote this proc and test. In the test, my “Act” is calling a proc I wrote that executes the first post’s query.

CREATE OR ALTER PROCEDURE RunTimeTests
AS
BEGIN
    SELECT
        Taskid,
        MIN(StartTime),
        MAX(EndTime),
        DATEDIFF(MINUTE, MIN(StartTime), MAX(EndTime))
    FROM TimeTests
    GROUP BY Taskid;
END;
GO
EXEC tsqlt.NewTestClass @ClassName = N'tTimeTests'
GO
CREATE OR ALTER PROCEDURE [tTimeTests].[test calculation min max time from timetests]
AS
BEGIN
    -- assemble
    EXEC tsqlt.FakeTable @TableName = N'TimeTests', @SchemaName = N'dbo'

    INSERT into TimeTests
        VALUES 
        (1, '2017-02-23 09:48:47.413',NULL ),
        (1, '2017-02-23 09:50:47.413', '2017-02-23 10:59:47.413' ),
        (1, '2017-02-23 09:49:47.413',Null ),
        (2, '2017-02-23 10:40:47.413','2017-02-23 11:55:47.413' ),
        (2, '2017-02-23 10:39:47.413', NULL ),
        (2, '2017-02-23 10:11:47.413','2017-02-23 11:30:47.413')

    CREATE TABLE tTimeTests.Expected
    ( taskid INT, Mindtime DATETIME2(3), maxtime DATETIME2(3), Minutes int)

    INSERT tTimeTests.Expected
     VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71)
          , (2, '2017-02-23 10:39:47.413', '2017-02-23 11:55:47.413', 76)   

    SELECT *
     INTO tTimeTests.Actual
      FROM tTimeTests.Expected
      WHERE 1 = 0;
    -- act
    INSERT tTimeTests.Actual EXEC RunTimeTests;

    -- assert
    EXEC tsqlt.AssertEqualsTable
     @Expected = N'tTimeTests.Expected', @Actual = N'tTimeTests.Actual', @Message = N'Incorrect times'
    
END

When I run this, it easily verifies the answer that the data is incorrect from the poster.

If I change my expected results:

    INSERT tTimeTests.Expected
     VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71)
          , (2, '2017-02-23 10:11:47.413', '2017-02-23 11:55:47.413', 104)

and re-run the test, it succeeds.

Now, does this mean the developer wouldn’t make this mistake? After all, if you think you should be getting those results, you will struggle with the query.

It doesn’t help there. However, it does help if you modify this code later and start to have strange results. This also means that I can add in more rows to the data, even more cases, and determine if the procedure still works. If I’m trying to cover a dozen cases, it’s much easier to re-run a tSQLt test than manually looking through results.

Give tsqlt a try. It’s free, and if you have the SQL Toolbelt, you can get a GUI with SQL Test for executing your tests.

March 13, 2017

Create a DACPAC to Move Databases–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In my last post, I talked about what a DACPAC was. Now let’s create one. This turns out to be about as simple as it could be. First, let’s choose a database. In my case, I’ll use the PartsUnlimited database on my SQL Server 2016 instance. I’ll start by right clicking the database and selecting “Tasks”.

Down near the bottom there is an “Extract Data-tier Application” entry. Choose that. Once you do, you should get a wizard screen. We’ll click past the first screen.

Next we need to set the properties of the DACPAC we are building. The application name is usually the database. The version can be anything, but ideally you are versioning your database in some way. Most people don’t, so they can leave this as 1.0.0.0. If you are actually building your database somehow, you ought to be using a CI process and have some version number. Ideally you’d keep this somewhere in the db and rev it.

We also need to include an optional description and a path to the file. I’ll leave the defaults, but feel free to change these as needed.

The next screen is a summary. If everything looks OK, click Next. This will start the creation of the .DACPAC.

The final screen will show progress, which is fairly short and simple. I think this has always worked for me. If I click the “Finish” button at the bottom (not shown), the dialog disappears.

If I go to the file location, I’ll see my DACPAC in the filesystem. The file is recognized as an SSMS file, and in another post we’ll look at how we unpack this.

I can check this file, however, to be sure there is something inside it. This is a zip file, and if I open it in 7-Zip, I see this:

Opening the model.xml file, I see data that seems like it describes my database. I talked about this in my previous post.

There, a simple DACPAC. This is a format Microsoft uses, and while it’s not perfect, at least I now know how to build one.

SQLNewBlogger

This post really took me about 10 minutes, across a few days. I started it after building a DACPAC, and taking the screenshots, but I didn’t have time to write it. I finally spent the other 6 or so minutes putting these words down the next week.

A quick showcase of something I learned.

March 8, 2017
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.
March 7, 2017
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.

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

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

March 6, 2017