Voice of the DBA

Category: Blog

Upgrading a SQL Azure Database to v12

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

I was looking to test some of the Azure v12 capabilities, but I needed to upgrade a database. Here’s a quick look at how.

In the old portal, you’ll see your databases.

I love that there’s no information on database version. I’m sure the Azure people love this, but I don’t. Very, very annoying.

If I pick one of these, and go to the “Quick Glance” items on the lower right, I’ll see the “update to v12” message.

However in the new portal, it’s different. At first glance it looks the same, but zoom in.

Now you can see the icons are different. I couldn’t read the 12 at first, but I did notice the color difference.

Now I can go into the properties of the database and see those. Mine said v2 here, which was strange. Anyway, pick that.

You’ll get a new blade, which will show you the upgrade benefits. Select “Upgrade this server”.

Now you get a blade to upgrade the server.

You must type the server name, not the database name, to upgrade. Note that all databases on this server will be upgraded. Be aware of that. However, it’s not the full server name.

It’s just the host, but a tool tip will prompt you if you mess that up.

Once that’s done, the upgrade will start on the blade. You can close this.

The upgrade will also appear in the main blade for the database. These are scheduled, not real time.

However, within about 3-4 minutes, mine changed to “upgrading” from the schedule notice.

This was a small database, with just one table in it. However the complete upgrade took awhile. The database is available and usable while this is happening, but it does take time to complete. I think mine took about 15 minutes before it was done, but I wasn’t watching the entire time, so I could be wrong.

Once it’s done, it has the new version

Easy enough, and if you need to upgrade your systems, here’s a simple way. If you have lots of them, I’d use PoSh.

SQLNewBlogger

This post didn’t take that long to write. I had the database ready, and I took a few screen shots then started the upgrade. I wrote most of this while waiting for the process to complete and then went on to other work and came back for the final screenshot.

Easy enough, and you should practice this and document it. You’ll have some idea of how to use Azure if someone asks you to later along with some notes for yourself.

References

None

August 31, 2015
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
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
Pick the Right Database in Azure

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

Another quick post, because this is something that’s annoyed me regularly. Hopefully writing this will help me make fewer mistakes.

Let’s suppose you have an Azure SQL Database. You go to connect from SSMS, as you do every day.

you log in, and you’re connected. But your default, by default, is master. This probably happens to you when connecting to many on-premise SQL instances. What do you do?

Most of us either click the drop down, which you can do, and even in Azure, you’ll see all your databases.

You can also enter a USE statement (which I do as I like the keyboard). However when you run that, you have an issue.

These databases, while they may be grouped on the same server for communications, do not necessarily reside on the same instance. Therefore you cannot ever assume you can change from one to the other with a USE. In fact, you need to actually change your connection.

The way to do that is to click “Options” on the connection dialog and then choose the second tab, Connection Properties.

Here you can specify the database to connect to. Once you do that, everything is great.

SQLNewBlogger

This took about 5 minutes to write. I changed my connection back to master, reshot the screens as I reconnected, and things worked.

You can do this. Show your next manager that you’re constantly working on your SQL Server skills.

References

No references here. This is something I should know well enough.

August 24, 2015