Voice of the DBA

Author: way0utwest

Should Production Databases Be in a VCS?

There was a great discussion recently on my true version of code editorial. It was interesting to see how some people viewed the idea of a version control system as the source of code, while others truly see the production SQL Server database as the truth. No matter how you view things, I had an interesting conversation with somebody later.

This person asked if we could put the production database code into a VCS. Not as a way of tracking development tasks, but as a way of auditing production and ensuring that any changes in the VCS match with a separate VCS repository. This person noted that they would even like a process that automatically scripts objects and checks changes into a VCS. It’s an interesting idea, and one I hadn’t considered. However as I think about it, keeping a separate repo for production makes some sense.

When I speak about Version Control, I usually recommend that the production database not be linked to a repo, but that’s usually because I don’t want production changes impacting ongoing development tasks. However, having a separate, production repo means that you have an independent way for the production DBA to verify what versions of code are deployed, and previously existed. This is separate from the development repo, which has to manage it’s own branches, merges, and potentially make mistake about tracking the versions sent to other environments.

Production DBAs are often conservative about depending on others for the integrity and auditing of their own systems. After all, even when another individual or group makes a mistake, it’s the production DBA that is ultimately responsible. Keeping the object changes and deployments in a VCS, using tooling like SQL Source Control or even automated scripts, can give the DBA their own version of history, an audit trail, and a way to recover from problematic deployments without depending on developers or a full database restore.

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.

February 16, 2016
T-SQL Tuesday #75–Power BI

This month’s host is Jorge Seggara, the @sqlchicken, who works for Microsoft. A busy schedule caused a slight delay, so we’re posting the third Tuesday of this month, but that is OK. This is a great topic for T-SQL Tuesday.

Power BI Data

While Power BI is a great visualization tool, you can’t do anything without data. That means you need to find data, which is both easy and hard. Easy if you’re working within your own organization on a specific project. Slightly more complex if you want to look at data out in the world.

However I saw this in a talk last year and I was amazed. This is the type of thing I’ve written before, and it’s cumbersome and problematic. I would think that SSIS would have made things this simple years ago.

I love sports, and wanted to play with some sports statistics awhile back. Finding good data is tough, at least in a format like CSV, that you can easily import. However Power BI makes this easy. Start up the desktop and you’ll see this:

Right away Power BI wants to get data. Click on this and the Get Data dialog opens, with lots of choices.

However if you pick “Other”, you’ll see one more that I love. Web.

Click this. You get asked for a URL. Any URL.

I happen to have one handy. After the win for Denver in Super Bowl 50, I thought I’d look back at Mr. Manning’s career.

I take that URL and drop it in the dialog.

Once I click OK, this will analyze the URL for tables of data. In this case, I get quite a few.

Now, I can click each one to see what data this is. This isn’t what I want

But this is.

I now click “Edit” at the bottom to clean my data. I could just load it, but there are a few issues.

I see all the data in the designer, and I have lots of options for working with this data.

First, since I’m going to do a comparison, let me rename the table.

Next, I see the steps below the name. I’ll add more steps, but I’ll do this in the designer GUI. First, let me remove the last row, which is a career summary.

In this case, I’m only removing one row.

Now, I want to remove a couple columns. In my case, I don’t care about a few of the data items, so I’ll pull them away. I can right click a column or choose “Remove Colums” in the ribbon. Either way, I get rid of QBR and Team.

Now I’ve got a nice year by year summary of Peyton Manning’s career. When I close and apply the query, my data is loaded into a data set for use by my Dashboard. I can then repeat this, and I’ll have two sets of data.

And, here’s my PowerBI Dashboard. It’s not terribly useful, or interactive, but it’s got data from the web that I didn’t have to copy or move.

https://app.powerbi.com/view?r=eyJrIjoiMWNmYzBiYjUtMTU3Yi00NWFhLWFiZjQtNTY0NzY4NDRkZTJmIiwidCI6IjY2NjBkOGZkLTJjNmItNDg0Mi1iZmZmLTcxOTY1YzE2NTczYSIsImMiOjN9

February 16, 2016
Zero Downtime for 2016

I’ve never felt that zero downtime is possible for a system over any length of time. There are no shortage of companies that seek to prove me wrong, and some are doing very well. For example, when is the last time that the Google search engine was down? It happens, though I haven’t seen this in a long time. Many of the highly available applications out there are built using as distributed applications across many machines, so that even if there are failures, they don’t cascade to interrupt users.

I was thinking about this as I saw a post asking database engineers to architect for zero downtime in 2016. That’s a good goal, and certainly if you work at a high profile retailer or service company, you should look for ways to improve availability.

In fact, I would guess that anyone struggling with specific events, like Black Friday, would be working on this problem constantly. I remember years ago Michelle Ufford spoke about the challenges at GoDaddy during the Super Bowl due to the advertisements the company ran. They had log files that couldn’t catch up to the load for hours and spent an entire year working to build a better database system.

Ultimately I think the best way to handle large spikes of activity is by building an application that avoids putting large spikes of activity on your database. Use messaging and queues to buffer traffic. Use read only copies of your database for traffic that doesn’t need to write to the main database. Anywhere that you can limit the load on your system can help prevent the database becoming a bottleneck for your system.

Perhaps more importantly, if you can spread the load, you don’t need to purchase more and more hardware. If nothing else, I think this is a good argument for better database architectures for applications. However if you’re like me, most of your systems will have to deal with the hardware that is available. In that case, the best you can do is write better T-SQL and ensure you have given SQL Server enough, but not too many, indexes.

Steve Jones

The Voice of the DBA Podcast

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

February 15, 2016
Quick Tests–Function Returns
I ran across a neat piece of code recently from Gail Shaw. She answered a question on returning the base path from a path in a string. Meaning if I had this string:

c:\Users\Sjones\Documents\text.txt

I’d want to return this:

c:\Users\Sjones\Documents

Her code looked like this, which is a nice, simple, elegant way of finding the path, no matter how many backslashes.

LEFT(@FullPath, LEN(@fullpath) – CHARINDEX(‘\’, REVERSE(@fullpath)))

Of course, you can easily add the last backslash with a slight change to the math.

However I wanted to add some tests. Does this really work? What if I don’t have a backslash? I thought the best way to do this was with a few tSQLt tests, which I quickly built. The entire process was 5-10 minutes, which isn’t a lot longer than if I had been running random tests myself with a variety of strings.

The advantage of tests is that if I come up with a new case, or another potential bug, I copy the test over, change the string and I have a new test, plus all the regressions. I’m not depending on my memory to run the test cases.

I first put the code in a function, which makes it easier to test.

CREATE FUNCTION GetParentPath
( @fullpath VARCHAR(4000)
)
RETURNS varchar(4000)
AS
BEGIN
RETURN LEFT(@FullPath, LEN(@fullpath) – CHARINDEX(‘\’, REVERSE(@fullpath)))
END

Here’s my base test:

EXEC tsqlt.NewTestClass ‘StringTests’;
go
CREATE PROCEDURE [StringTests].[test simple path with one backslash]
AS
BEGIN
— Assemble
DECLARE @input VARCHAR(4000) = ‘c:\myfile.txt’
, @expected VARCHAR(4000) = ‘c:’
, @actual VARCHAR(4000)

— Assert
EXEC @actual = dbo.GetParentPath
@fullpath = @input

— Assert
EXEC tsqlt.AssertEquals
@Expected = @expected
, @Actual = @actual
, @Message = N’Incorrect Path’
END
GO

I can easily copy this and add new inputs with different paths, and matchout outputs, to test new cases. For example, my first cut produced five tests for these inputs:
- c:\myfile.txt
- c:\
- c:
- c:\Documents\myfile.txt
- c:\Users\sjones\Documents\myfile.txt
There are certainly other tests, but this 5-10 minutes of work gives me repeatable testing, and if I needed to include this function in a larger project, I already have a series of tests that can be run in my CI process.

What’s more, if I replaced this with a CLR function, such as something with SQL#, I could still use these tests.
February 15, 2016