Voice of the DBA

Author: way0utwest

tSQLt in Azure SQL Database

I was excited to hear about the v12 Azure databases supporting CLR assemblies. Mainly because I’ve been doing testing work with tSQLt and wanted to run tests in an Azure database.

I upgraded a SQL Azure database to v12 and connected in SSMS. My first step was to open the tSQLt file.

I had the file open, connected to Azure. This was the easy part. Now I need to actually send the batch across and compile the code:

My next step was to execute it. However that didn’t work as well as I expected.

There are multiple errors here, but it’s possible that one error causes others. I pinged Sebastian Meine, the creator of tSQLt about the External_Access and he noted there was only one method that needs it.

So I decided to make a change. First, a search.

That got me the assembly installation.

I decided to try and change this to something that’s contained inside the database. Since Azure is a bit of a black box, I thought safe was the way to go.

With that change made, I compiled the entire file again. This was the only change I made.

That’s cool, but does it work? I connected in Object Explorer and then opened SQL Test. I saw my database, and I could create a test.

However I got an error on the connection and creation of a procedure.

Even from Object Explorer, I refreshed the procedures, but got this:

This appears to be a SQL Server 2014 RTM error. Actually I was on a CU, but not SP1. It is supposed to be corrected in SP1.

However the procedure was created, and I could alter it.

More importantly, I can execute it.

If I change the code.

Now it fails.

It appears that tSQLt can now work in v12 databases in Azure, so start adding those unit tests to your database projects.

August 27, 2015
A Clock With Benefits

One of the difficulties I think that developers face is that so few people understand what actually goes into producing software. The end result that a user sees can mask the effort and complexity of the underlying code. A person might see one application that required months of effort and not understand how it’s different than a similar (in their view) application that was prototyped and completed in a week.

I’ve seen a few efforts to try and explain how code is written, and I think the huge piece by Business Week was a really good explanation for the layman. It tries to simplify concepts, and does so in a humorous way. I suspect that this piece might actually help our clients and customers gain some appreciation for the difficulty of producing a basic application.

However I think we need some additional articles like this to help explain our database world. While there are some good, basic pieces on what a database is, we don’t necessarily help anyone understand the complexity of assembling disparate data, especially at scale.

I don’t think end users would care much about why database performance can be an issue, but I do think that some well written, easy to read, enjoyable pieces on the issues of writing poor database code might give developers some appreciation for why indexes matter, what RBAR does to performance, and what solutions might help their code shine.

No one wants software written poorly, and no one wants deadlines missed. However the world of software development is complex and the more that we can help our clients understand this, the less time we waste on explanations.

Steve Jones

The Voice of the DBA Podcast

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

August 26, 2015
The Log Chain

I ran across a question recently from someone that received a similar message to the one showed in this image:

In this case, the log chain is broken. I’ve received this message, usually from a script that is automatically restoring all the logs in a folder. I’ve also had clients call me with this message, saying their log shipping system is broken.

What has happened is that you (or someone) is restoring the logs out of order. Each log backup forms part of a sequence that we call the log chain. The restore process requires that each log backup be restored in the same sequence in which is was created.

In the error message above, SQL Server is letting you know that the Log Sequence Number (LSN) contained in the file from which a restore was attempted doesn’t match the last LSN in the restored database.

Fixing This

The fix is easy; restore the correct files in the correct order. In this case, I knew I’d taken 3 log backups, and I was restoring in the incorrect order. I’d restored log backup 1, and was now trying to restore log backup 3.

If I restored log backup 2 (using with norecovery), the restore would work. I could then restore log backup 3 and eventually bring the database online.

If you can’t find the correct log backup, then your database is still usable. Just run RESTORE DATABASE WITH RECOVERY, and you can access your data. However the data will only be correct to the point in time of the last successful restore.

That could be a serious problem if you are missing a log backup early in your restore sequence. It could be a career limiting problem.

Always be sure you keep all log backups since the earliest full backup you keep handy for restores and protect those files. Losing one could be a major problem for your organization.

August 25, 2015
Surfing

How many of you have gotten up before dawn, driven to the coast, gotten on a long piece of fiberglass and paddled into the ocean before turning around to ride back to shore? It’s a great way to start your day, and I’ve been lucky enough to do this many times in my life. I look forward to getting back out there someday in the future.

Surfing is an interesting sport, because you don’t really get to pick your path. You catch a wave, and begin riding it in, altering your course based on the speed of the water, the height and structure of the wave, and even the way the current is flowing. While you move in the direction you want, your choices are limited.

This is unlike many other moving sports. While snowboarding you can stop or slow down. In many wheeled activities you can go in almost any direction. Much of the time you have more control over your motion, and can even take a break.

However waves are unique. They’re few and far between. If you want to slow down, you end up really abandoning that journey, one you’ll never get again. If you paddle back, you’re hoping you’ll get the chance for another fun ride that day. That isn’t always the case.

I saw someone post a note that working in technology is like surfing waves. As we learn and grow, we often feel that we can’t slow down, or change directions very much. If we’re a SQL Server DBA, we’re riding that wave. Trying to slow our career, or move to another technology means (often) abandoning some of what has worked well for us. We really follow the wave we’ve chosen as often as possible, usually resistant to change.

You can change, however, and if you want to do so, make some plans and look for a new wave. It will take time; it will require some investment, but it’s possible. Always remember life is short, and we want to work in order to enjoy our lives with friends and family. We don’t want to get stuck working in ways that prevent us from enjoying our lives.

Steve Jones

The Voice of the DBA Podcast

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

August 25, 2015