Voice of the DBA

Tag: Azure

Crossing Azure Borders

The speed at which Azure is evolving actually amazes me at times. I know that the Azure platform is a focus for most, if not all, groups at Microsoft. The breadth of changes occurring every quarter continue to enhance capabilities to the point where Azure is a viable platform for more and more applications all the time. It’s not for everyone, but it does work well for more and more organizations all the time.

I’ve been watching the Azure SQL database evolve, and been doing a little work with it for a few years. It has grown from a fairly limited RDBMS to a full-featured database, albeit with limitations. Some of those issues have meant that tools or techniques I’ve been familiar with haven’t worked well, if at all.

One of those items was the ability to query across databases. Previously I’ve had to open two connections and pull data to a client to combine it. However just recently that with the elastic database query in Azure now supports queries across databases. This is cool, as we can now build applications that easily query across shards, or even just across different databases without requiring additional client work.

The list of features in an on-premise SQL Server instance that aren’t in the Azure SQL Database continues to shrink. While there’s more work to be done, I think we can start to experiment with systems and see where the Azure platform might support our needs. Certainly our business people like the ongoing rather than capital expense nature of cloud platforms.

We should be sure we understand where, and where not, we could use an Azure SQL Database. I’m curious if any of you have had success with Azure and what types of applications you use the platform to host.

Steve Jones

The Voice of the DBA Podcast

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

November 5, 2015
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
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