There are a number of major differences between Azure SQL DB and a regular SQL Server instance. Heck, in Azure SQL DB you can’t even use a USE statement. However, the biggest thing I’ve run into in my company is the need to change our processes.

We have creating a new SQL Server instance down pat. We know what questions to ask, we have forms to fill out, a new server is quickly created, a new instance installed, it’s added to our list of supported instances (depending of course), a new wiki page is added with information on billing, ownership etc, and a script is run that grants us permissions to work with it. Azure SQL DBs, on the other hand, tend to catch us by surprise. A whole new team is creating them and of course, their processes aren’t as mature yet. I mean how could they be? We keep getting surprised by requests for databases we’ve never heard of, we don’t have security for and have no information about the customer or anything else about it.

Now, I’ll guarantee this will correct itself over time, we just need to get better processes in place for the Azure DB creation. Part of that is going to be collecting information on what information we need, and that will just take time.

But here’s my thought for this post. When changing to something new, moving to the cloud, for example, it’s important to think about how your processes might need to be changed and/or expanded. Then you try out the new processes, then review them again. Then try again, review again, over and over. It’s really a never-ending process, but eventually, your processes become stable. I.e. they are good enough that the changes required are rare and/or small.

1. In the Overview blade you can see current pricing tier.
2. Under settings, just below the quick start link is the link for the pricing tier blade.
3. Select the service tier (if it’s changing).
4. Select the number of DTU using the slider bar.
5. Select the max database size using the slider bar.
6. You can now see the monthly cost
7. Hit apply.
8. Wait for a bit and the settings will change.

 
Simple enough right?

Well, there are two important things to remember. The first is somewhat obvious (at least in hindsight). If you try to decrease the capacity for a database and the database is too large for the new tier then the change will fail. This will happen even if you just increased the pricing tier minutes earlier.

Here is a concrete example:

1. You have a SQL Database on a standard tier that is currently 230gb. You expect a surge of business because of a product release. Your application is going to need more database processing power.
2. You increase it temporarily to the premium tier.
3. While the database is under premium it grows by 30gb due to the new business. It’s now a 260gb database.
4. At this point it is too big to be decreased back to a standard tier and you will either have to delete at least 10gb of data and shrink the DB or leave it at the premium tier.

Like I said, it makes sense when you think about it. Just make sure you know the size of your database before making any changes.

Here is the bit that you won’t see coming. In order to change the pricing tier the database is copied from one location to another and during the switch over the connections are disabled, causing any transactions in flight to be rolled back. This is mentioned in the documentation for SQL Database Service Tiers.

Changing the service tier and/or performance level of a database creates a replica of the original database at the new performance level, and then switches connections over to the replica. No data is lost during this process but during the brief moment when we switch over to the replica, connections to the database are disabled so some transactions in flight may be rolled back. This window varies but is on average under 4 seconds, and in more than 99% of cases is less than 30 seconds. If there are large numbers of transactions in flight at the moment connections are disabled, this window may be longer.

The important thing here is, if you plan on changing your service/pricing tier, make sure that your application can handle it.

In my opinion, this is a truly amazing feature of the cloud. The ability to scale your power needs up and down as needed could save a business vast amounts of money or keep a small business in, well, business.

Ok, so what skill was I going to sharpen? There are certainly enough of them where I need some work. Well, as sometimes happens, the choice was easy. I’d been working on sharpening my Azure skills anyway, but one of my managers asked me to go to a webinar on the subject for an upcoming project. I ended up going to Jes Borland’s (b/t) session on Azure and SQL Server on the Pass Data Architecture Virtual Chapter. Fortunately the VC sessions are recorded so you can go back and watch it yourself, or, like me watch it again.

Alright, so I’m sharpening my skills. So what did I get out of the webinar? Well, as with anything Jes does the answer is A LOT. In fact on my first pass I noted down six different things I wanted to look at in more depth. (Check out my blog ideas page.) So this is a great opportunity for the first one.

T-SQL Differences in Azure SQL Database

I used to think this was the real difference between SQL Server and SQL Database. I was wrong. Really wrong. But it’s a good place to start. Now from what I can tell everything in Azure is a moving target. There are constant changes so it’s important to know where the documentation is. In this particular case here it is: Azure SQL Database Transact-SQL differences.

It’s reasonable to assume (of course we know what making assumptions does right?) that the differences exist because SQL Databases are supposed to be as independent as possible from the windows server and to a lesser degree the instance. When you create an Azure SQL DB you create a Server (the instance) but you don’t specify a windows server. That’s because it doesn’t matter what server your instance is on. It’s independent. Well, because of that, some things just aren’t going to work.

Highlighting a few of the commands mentioned in the document:

Partially supported
These commands are supported, but only in part. Certain options aren’t available.

CREATE DATABASE – It says this is partially supported but to me it’s really a completely new command. (Compare to SQL Server CREATE DATABASE.) Options for Edition (basic, standard or premium), Pricing Tier (basic, S0, S1, P1, P2 etc) and the Elastic Pool. No options for things like location (of course), default language (surprising), trustworthy (not surprising) etc. The only things that I’m seeing from both are name, collation and size.

KILL – The main difference here is the inability to kill by UOW. The Unit of work id used with distributed transactions. Since we aren’t using distributed transactions this isn’t a surprise. The inability to see the status of a rollback caused by a KILL (WITH STATUSONLY option) is a bit surprising to me and I hope the option will be added soon.

CREATE VIEW – While this is listed under partially supported there doesn’t appear to be any difference for SQL Database. There is a bit of a difference when using SQL Data Warehoue and Parallel Data Warehouse. It’s just a stripped down version. No SCHEMABINDING, no ENCRYPTION etc.

Not supported at all
These are the commands that for one reason or another won’t work at all.

No cross database ownership chaining or TRUSTWORTHY – We saw that in the CREATE DATABASE command.

EXECUTE AS Logins – Since we are supposed to be limiting our server level access this makes sense. EXECUTE AS Users still appears to work normally.

Database Mail – I can see this one being technically difficult but I’m really hoping they get it working in the future.

You can connect using any number of tools. We can actually see several options by going back to our Azure Portal. Select the SQL Databases list, a specific SQL Database, and then the Quick Start option. (It looks like a broken cloud to me.)

Personally my tool of choice is SSMS. You can either follow the link show in the quick start menu or follow this one. I should point out that SSMS 2016 is free to download and use can can connect back to SQL 2008 (well actually 2000 but no guarantees that everything will work correctly).

Now install SSMS .. I’ll wait.

So now that SSMS is installed the next thing we need is the server name to connect to.

One of the properties of the SQL Database is the Server Name.

Unfortunately with a long name it’s abbreviated (it’s what you called it + “.database.windows.net” if that helps) and I haven’t figured out a way to copy it directly. What I did do is take a look at the connection strings and copy one of them.

Driver={SQL Server Native Client 11.0};
Server=tcp:myfirstserverinthecloud.database.windows.net,1433;
Database=MyFirstDatabaseInTheCloud;
Uid=sqlstudent144@myfirstserverinthecloud;
Pwd={your_password_here};Encrypt=yes;
TrustServerCertificate=no;Connection Timeout=30;

Enter that server name into a normal Object Explorer or Query Window connection window. Use the user name and password that you set up as your administrator. (There is of course a way to create other logins and I’m sure I’ll go into that at a later date.)

However the first time you hit Connect you will get an error.

I tried signing in and using the form above to create my new firewall rule but kept getting an authentication error. That being the case I went a different route.

First I go to the properties of my server, then to the firewall settings, type in the IP(range) and save.

Note: You can remove an IP(range) by hitting the ellipsis (…) and selecting delete. You do need to make sure to hit SAVE afterwards though.

Now that my IP is in place I can connect with no problems!

I’d been thinking I needed to work on Azure and SQL Database for a while now. Even better I had recently read about free credits for Azure. All you have to do is join Visual Studio Dev Essentials and go to your benefits page. A few highlights of my favorite benefits.

• A 6 month Pluralsight subscription.
• Free SQL Server Developer Edition
• And most importantly (for this post) a free $25 Azure credit each month.

 
So once we have activated our Azure account we can go to the Azure Portal. From there the first thing I wanted to do was check out my subscriptions. You can see I have the Developer Program Benefit and when I clicked on the subscription name it brought up some basic details and usage information.

Not good enough. I want to make sure I have my $25 and that I have a limit of $25. If you haven’t noticed I can be a bit cheap. So I click on Manage and that brings up the info I really wanted to see. My credit balance and my limit.

Now it’s time to make my first SQL Database. I hit add and put in my name and resource group. I then decided to create a sample database. AdventureworksLT [V12]. I ended up making it a pricing tier S0 which is the default and is $15 a month. Given my limited usage for testing I could probably be using the pricing tier Basic which is only $5 a month. Combining them I’ll have 3 servers free using my free $25 a month. I may go ahead and drop this database and switch to a Basic for the sample (assuming it will fit) but for now I’ll leave things the way they are.

Next it was time to set the server (a database has to have a server right?) Well I don’t have one yet so I add a new one. Put in all of the details there (name, admin id & password), create it and we are good to go. I now tell the console to pin this database to the dashboard and create it. It did take a fair amount of time for it to deploy my new database but after a few minutes I was ready to go!

Now it’s time for me to explore the rest of the portal, work on connecting to the database and start playing. Hopefully we can do some exploring together.