Object Explorer: Say Hello to Azure SQL Database

In my last post, I had talked about tackling the great Azure firewall and allowing your connections through to the Azure SQL Database. Now let’s talk about what you can actually do with SQL Server Management Studio and the Azure SQL Database. SQL Server Management Studio is pretty much the tool that regular users of SQL Server are familiar with. It would make sense to be able to manage your Azure SQL Database using Management Studio!

Since there are feature limitations in the Azure SQL Database, you will not get the full range of functionality when you connect to an Azure SQL Database. Before you attempt to connect to an Azure SQL Database, ensure that you have allowed access to the server.

Connecting to the Server

imageIn the Connect to Server dialog box (Screenshot 1), you will need to provide the server name and the SQL Authenticated user name. Remember to switch to the Connection Properties tab and add your database name. If you have more than one database hosted on the server, it is imperative that you provide a database name. The USE statement is not supported on an Azure SQL Database for switching connections.

Note that an Azure SQL Database only accepts TCP connections.

What will you see

imageIn the Management Studio Object Explorer, you will get a stripped down view of the server as compared to an on-premise SQL Server instance. Screenshot 2 shows view that you will get in Object Explorer. I see the master database and the database that I connected to (megatron) … Yes I am fan of the Transformers franchise! Interestingly, you will also see an Extended Events node within the database tree.

If you have enabled Federation, then you will see the federation information for your Azure SQL Database under the Federation folder. You can launch a new query window by selecting the database to execute your queries. This part is exactly similar to how you would run queries against an on-premise database from a Management Studio Query Window.

The objects that you see in Screenshot 2 are the only ones that you are allowed to create in an Azure SQL Database. You would have noticed that a SQL Agent is missing. If you want a SQL Agent in Azure, then you could leverage a SQL Server installation on an Azure Virtual Machine or an on-premise SQL Agent which connects to an Azure SQL Database or use Azure automation.

You do have the option of performing a right-click on any of the folders like Tables, Views etc. and selecting the NEW option to get a template script for creating a table, view etc. for the Azure SQL Database.

If you are using the Premium database feature (currently in PREVIEW), then you have the option of querying the server_quotas view (currently in PREVIEW) to understand the premium database quota available on this server.

More about the Azure SQL Database in future posts!


Azure SQL Database General Guidelines and Limitations

Azure SQL Database Tools and Utilities Support

SQL Server Feature Limitations (Azure SQL Database)

Azure SQL Database–Firewall

In my last post, I talked about how to create an Azure SQL database. In this post, I am going to talk about how to connect to the same. You have multiple options to connect to the database:

1. Through the Management Portal using the link: https://<Azure SQL Database Server Name>.database.windows.net/

2. SQL Server Management tools like Management Studio, SQL Server Data Tools

3. Through programmatic means using .NET or other languages

But before you start connecting to your database, you will first need to setup the list of allowed IP Addresses. This post will talk about how to configure the firewall for your Azure SQL Database.

Continue reading

Creating the Azure SQL Database

In this post, I shall talk about the Microsoft Azure SQL Database. There are five service tiers: Basic, Standard, Premium, Web, and Business. Web and Business service tiers (editions) are being retired over the course of 12 months, effective April 24, 2014.

The maximum sizes available are:

Service Tier

Max Sizes available


100MB, 1GB and 5B


10GB, 20GB, 30GB, 40GB, 50GB, 100GB, 150GB


100MB, 500MB, 1GB, 2GB


100MB, 500MB, 1GB, 2GB, 5GB, 10GB, 20GB, 30GB, 40GB, 50GB, 100GB, 150GB, 200GB, 250GB


100MB, 500MB, 1GB, 2GB, 5GB, 10GB, 20GB, 30GB, 40GB, 50GB, 100GB, 150GB, 200GB, 250GB, 300GB, 400GB, 500GB

If you want to try the Preview service tiers, then you need to first enable the preview feature, New Service Tiers for SQL Databases, for your Azure account.

Continue reading

Setting up SQL Server on Azure for testing

I recently had the need for testing out a setup program which installs database components, integration services packages and reporting services reports. Setting up a machine like this would be really quick if you have Hyper-V installed and a VHD already pre-created with a SQL Server image. What if you do not have that handy and need to carry out your testing. This is what Microsoft Azure Virtual Machines comes to the rescue.

I used my Azure subscription to create a virtual machine for my testing. In this blog post, I will walk you through the steps for setting up a SQL Server virtual machine for testing purposes!

Continue reading