Voice of the DBA

Author: way0utwest

Should We Move to Azure?

This editorial was originally published on May 23, 2014. It is being re-published as Steve is out of the office, with a few minor editorial changes.

Update: We made a number of changes last year, upgraded the forums, and have continued to implement bug fixes. We are still considering this idea.

At SQLServerCentral we have been debating some architectural changes to the site. We’d like to improve the way a few of our features work, add new ones, and replace a few items that are buggy and hard to maintain. The site has worked well over the years, but we’ve made relatively few improvements, mainly due to the fact that like many companies, this website isn’t our business, and work has a lower priority than some other areas in the company.

Recently I had a suggestion presented to me and I wasn’t sure how I felt. There were certainly pros and cons to both taking advantage of the opportunity as well as passing it up. While we are still talking about the process, I wanted to ask the community what they might think this week.

Should we move SQLServerCentral to Azure?

I really like the idea of PaaS. There are issues, and certainly I think Microsoft could do a better job of creating competition as well as securing and even deploying their platform. However I think the idea of just consuming services as opposed to installing and managing pieces of software, is a great direction in which to move. SQLServerCentral would seem to be a perfect type of application that fits in the cloud.

We run a generic ASP.NET site, using fairly common and simple database features in SQL Server. We don’t store or manage much in the way of PII, financial, or medical data outside of email addresses, and we don’t have the need to scale to high levels, either in data storage or computational resources. The one problematic area is our email requirements. I’m not sure what the cost is to send emails from Azure, but we send millions per week, and CAN-SPAM requirements can be tricky to meet in the cloud.

However I’m just wondering what the community thinks. If we successfully moved to the cloud, and disclosed details about the process, would that give you more confidence in the cloud? Would it be interesting? Or is SQLServerCentral just a service and you don’t care how it works, as long as it does. Let us know this week.

Steve Jones

November 8, 2017
Off to DevOps East

The travel countdown is down to 2 trips this year. The second to the last is this week, as I’m off to DevOps East, a conference in Orlando, FL that tries to bring together lots of DevOps experts and advocates to share ideas and learn more. This is my first time at this event, and I’m looking forward to talking DevOps for a few days.

I’ll be in Orlando all week, and then I have a month off before one last trip for SQL in the City.

If you’re at the conference, say hi. And certainly talk databases with everyone that you see.

November 7, 2017
ROLLing UP Totals–#SQLNewBlogger

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

I was editing an article recently that talked about ROLLUP, and I wanted to play with this a bit more. I hadn’t used this much in my career, but it’s a handy operator that’s worth including in your toolkit.

ROLLUP is used to provide additional totals for your aggregates while using GROUP BY. Here’s a little example. I’ve got some data for American Football quaterbacks. In this case, I’ve extracted some stats for a few noteworthy players today. Here’s a sample:

I want to aggregate some data together. For example, let’s say that we want to look at the total touchdowns scored per year by these players in their first 5 years. That’s a simple query:

SELECT
   Season = qs.CareerYear
   , TDs = SUM(qs.Touchdowns)
FROM dbo.QBStats AS qs
WHERE qs.CareerYear <= 5
GROUP BY qs.CareerYear

This gives me data like this:

However, when I look at this, I don’t have a total across all years. In my set, there are players that have played up to 18 seasons, and I want to know the aggregate number of TDs. I can get that by adding WITH ROLLUP. This is added

after the group by. However, I can also change this to GROUP BY ROLLUP and include the columns in parenthesis.

SELECT
   Season = qs.CareerYear
   , TDs = SUM(qs.Touchdowns)
FROM dbo.QBStats AS qs
WHERE qs.CareerYear <= 5
GROUP BY ROLLUP (qs.CareerYear)

This is OK, but the NULL isn’t great. What can I do here? I can add an ISNULL or COALESCE to my query and get this:

Useful, but what about if I’m aggregating by individual players? I can add the player to the column list, but I also need to add the player to the ROLLUP (or GROUP BY) list as well. If I do that, I get this:

That’s quite a bit more data, and I now see NULL values in the Player name. What’s this?

These are the totals for that subgroup. In this case, the first NULL above, next to the 1, is the total for all players for season 1. I can use another trick to clean this up:

This now shows some data in an easier to understand fashion, with some totals. We can see for season 1 that 3 players really started their careers in a strong fashion and 3 didn’t.

At the end, however, we get a different result.

This is because we have handled the player name as a NULL for the second column, but not the season as a NULL. For this last row, out “Total” text comes from an ISNULL of the first column. Here we’d want to do this:

This is where COALESCE comes in handy, allowing us to account for multiple NULL values.

ROLLUP is a nice way to get totals for each of your GROUP BY columns. This can make some of your reporting easier, and perhaps even faster by having the totals calculated in the result set.

SQLNewBlogger

This was a fairly easy piece to write. It took longer to get data together and find a set that had two grouping elements and was interesting than write the queries. I also had to play with ROLLUP a bit to clean up the NULLs, which always made this seem like a useless operator.

I think I’d look to use this more if I had the chance for many reports, as summing totals in some client tools is a pain.

November 6, 2017
DevOps Basics – Ignoring Files in Git

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

One of the things you’ll run into at times is the need to keep some scratch files, or extra files, in your Git repository, but not track them. One common type of file for me when working with SQL is a .zip file. I may zip up code to share or copy to a friend (without giving repo access).

Ignoring files is easy in Git. We just add a .gitignore file. This is a list of files that the git repository will not track or show in status. Essentially, we see them in our file system, but git doesn’t.

Creating .gitignore

To create a .gitignore file, the easiest method for me is to just create a text file. I can do it like this:

This gives me a new file. Certainly VS Code, Sublime, etc. will make this easy as well. The format is simple, with a list of files and/or patterns to ignore. For example, I’ve got a .zip file in my repo.

I don’t want to see this, but I do right now:

If I want to ignore this file, I’ll enter this in my .gitignore file:

GitTests.zip

If I want to ignore all zips, I’ll do this:

This is a part of my repo, so I need to commit it.

Now my status is clean.

Generated .gitignore

Some applications will generate a .gitignore. For example, my C# project gets this file from Visual Studio.

That’s a subset of files that are often in a VS project, but we don’t want to track in a VCS. Images, archives, executables, etc.

You can customize this as you need, and it’s easy to just edit the text file and commit the changes.

Hopefully this helps you understand how to best work with git and keep your repo clean. This also means your git add –all is easy to use without adding unnecessary files.

October 30, 2017