Getting Started with Azure SQL Database v12

I realized that I’ve been talking about the wonders and glories of the new release of SQL Database when some of you might not even be prepared to work with it. Let’s remedy that. First of all, you’ll need an Azure account. There’s a free trial available. Go there and sign up. Better still, if you have an MSDN account you can take advantage of free credit on Azure. Either way, I’m not going to show you how to set up an account. I’m going to assume you have one. Now for the tough part.

Pick a Portal

There are currently two Azure portals. There’s this one:

This is the original portal with all the various services showing their status. If you go to azure.microsoft.com and select Portal from the screen, this is the one to which you’ll be taken. But, this portal is on the way out. To be replaced by this one:

I don’t want to get into which is better or preferable, but the new one is coming, so I’m going to use that. To get to this one from the old one, you can click your account in the upper right of the old Portal. You’ll see a menu choice “Switch to New Portal” Click on that and you’ll see this open. Also, instead of going to manage.windowsazure.com, you can go directly to the new portal at portal.azure.com (“Windows” is being removed from a lot of the nomenclature).

You can use either portal to create a v12 SQL Database.

Devise a Database

As with most things, there is more than one way to do this. Frankly, one of the easiest is PowerShell, but I’ll save that for an article just on PowerShell. Once you have an Azure SQL Database server, you can also use T-SQL to create databases. I’m going to assume we’re going from scratch, so we’ll go ahead and play with the GUI for now.

In the lower left of the screen, click the New button. You’ll be presented with a list:

Click on “Data + Storage.” This will open up another popup list:

Right at the top is the SQL Database we’re interested in. Worth noting though, you can create a NoSQL database with DocumentDB, blob storage with Storage, even a MongoDB if the mood takes you. We’re going to open the SQL Database screen and then the real work starts:

I’ve supplied a name for the database, SqlMagDemo. But, the database has to go on a server, so I’m going to expand the Server first. If you have an existing server, or servers, they’ll be listed here and you can use one of them. To create a new server, you’ll choose “Create a new server.” This will open another window on your screen. Looking at might right now there are five going across:

You have to supply a server name. It has to be all lower case. Supply a login and a password, and don’t forget these. Pick the location of the data center in which you wish to store your database. Finally, make sure that “Yes” is selected on “CREATE V12 SERVER (LATEST UPDATE). If that is not enabled, it means that v12 is not yet available in the data center you selected. Pick another. Click OK. That will create your server and select it for your database.

Next, you can choose where you want to create your database from. There’s a blank database, the one I use the most, a sample database and, you can use backups from other SQL Databases to create a new one. We’ll go with blank for now.

Then, you pick your pricing tier. This is where you do need to think about what you’ll be using this database for. There are different levels of functionality, performance and behavior to take into account. Three standard ones will come up, but you can click to “View All:”

You can also set the Collation for the server. You can set the Resource Group too. This allows you to manage a set of Azure resources. Once all this is set, you can click on Create. There’s also a check box that will allow you to add this database to the Portal start screen. Creation is not instantaneous, but you can see it occurring as it gets instantiated:

Finally you’ll see a SQL Database window for the newly created database:

What you have now is a SQL Database. But, there’s nothing in it (unless you used a restore). In the old portal, with pre-v12 databases, there was an online set of tools for working with databases, but that’s been removed from the product. Your choices now are to use Visual Studio and the SQL Server Database Tools to build a database, SQL Server Management Studio, or a third party tool.

To make the connection from an external source, you’ll need to go to the Server that you created and manage access through the firewall. I wrote a blog post about how to do this on the new portal.

Transact T-SQL

If you already have an Azure SQL Database server, you can use the CREATE DATABASE command in T-SQL:

CREATE DATABASE SQLMagTest;

That’s all it takes. It will use the defaults and create a database. However, you can get more sophisticated than that. There’s a separate entry in the Books Online just for CREATE DATABASE (Azure SQL Database). If you follow the link you can see that you can set the maximum size of the database, the Edition that it will operate as and the performance level as defined by the Service Objective.

There’s even the capacity to create a database based on another:

CREATE DATABASE SQLMagCopyAS COPY OF SQLMagTest;

That’s a capacity that doesn’t exist in the earthed version of SQL Server.

From there, you can use T-SQL commands to create the tables and all the rest of the database objects.

On a side note, if you do intend to manage your SQL Databases from SSMS, make sure you have Cumulative Update 5 or better installed on the server, or you'll get errors when you attempt to work with the objects there.

Conclusion

Getting started with Azure SQL Database is very easy. The GUI provides just about everything you need to get started. You can leverage the knowledge you have with T-SQL, or, as I'll show another time, you can go for full automation through PowerShell. Now you know how to get your first v12 database set up.