Introduction

In this new article, we will create an Azure SQL Database and then we will see the limitations compared with a SQL Server Enterprise Edition.

We divided this article in 3 sections:

• Azure SQL Database Creation (Create an Azure SQL Database using the portal).
• Connecting to Azure SQL from the SSMS (Enabling the Local IP to handle the database created in SSMS).
• Comparing Azure SQL with a local SQL Server Enterprise Edition

Requirements

1. An Azure Account to the Azure Portal.
2. SQL Server 2014 or 2016 installed with the SSMS installed.

Azure SQL Database Creation

Press the Add Icon to add a new database. In this example, we are creating a Database named "sqlcentralazure". We will create a new group, which is used to administer resources. In the Source, we will select sample to create a Database with some tables included. In the select Sample, we will select the AdventureLT database (Adventureworks Light).

We need to create a Server for the database created in previous step. The server name will be sqlservercentralserver. We will provide a database administrator login and password:

Make sure to select a pricing tier according to your needs. In this case, I am using the Basic tier (the cheapest) and press the create button:

If everything is OK, after some few minutes, press Refresh in SQL databases and the database should be available:

Connecting to the Azure SQL Database from SSMS

Click on the Database created before and copy the Server Name:

Click on all resources and look for the server created in the Azure SQL Database Creation section:

Go to the Firewall section and the IP of the local machine that will connect to the Azure SQL Database:

In the local machine, open the SSMS and connect using the Azure Server Name and the login and password specified in the Azure SQL Database Creation section, and press Connect:

Comparing Azure SQL with SQL Server Enterprise

Here you have some significant differences:

In SSMS, you can only create and alter objects using T-SQL

The most visible limitation is that there is no UI to create objects in SSMS. For example, try to create a New Login in Azure, we right click on logins and select New Login:

As you can see, there is no UI to create the login. Basically, SSMS generates T-SQL code:

SSMS is limited to create objects in Azure SQL. This may change in the future. SSDT and the Visual Studio have a friendlier interface to create tables and add data.

T-SQL Sentences are not the same in Azure SQL

There are some syntax differences and limitations in Azure SQL compated with traditional SQL Server Editions. There are several arguments that are not supported. For example, the following T-SQL Statement to create a login in SQL Server will not work in Azure SQL:

CREATE LOGIN [Peter] WITH PASSWORD=N'MyPassword!@3444', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

The following message is displayed:

This is because WITH PASSWORD is not supported. There are several differences between Azure SQL and traditional SQL Editions. Azure SQL does not support the DEFAULT_DATABASE, DEFAULT_LANGUAGE, CHECK_EXPIRATION, CHECK_POLICY.

In Azure SQL, a connection is associated with a single database. When you connect to Azure, you need to specify the connection. The DEFAULT_LANGUAGE argument is not available because the language is defined in the Azure Portal directly:

CHECK_EXPIRATION and CHECK_POLICY are arguments related to security policies in the Windows Operative System. Since Azure SQL is a service, it does not make sense to have these options.

This is the code to create a new login in Azure SQL:

CREATE LOGIN Peter 
WITH PASSWORD = 'MyPassword!@3444' 
GO

The code will create a user named Peter:

In Azure SQL, there is no AlwaysOn, Database Mirror, Log Shipping and Replication

Instead of traditional replications,Database Mirroring, Log Shipping and AlwaysOn, in Azure SQL we have Geo-Replication that allows you to have replicas in the same location or in different locations (and it is easier to configure than AlwaysOn and Replication). In Geo-Replication you could have for example a Database in USA and Replicas in Japan and Europe.

For more information, we created a nice article for you: Configuring Geo-Replication for an Azure SQL Database

There is no Azure SQL Agent

Instead of the Traditional SQL Agent, you can run on premise jobs (this is worse than the traditional way). You can create a Mobile Service and create a scheduler to do this. The other option is to run in the SQL Agent the cmd and call the sqlcmd to connect to Azure SQL.

There is no SSIS

For Integration Services Catalogs, there are no SSIS for Azure. You can connect to Azure in SSIS and copy tables and objects using Azure. You can also have a Virtual Machine in Azure with SQL Server that can have a traditional SQL Server and SSIS included.

You can also use Azure Data Factory instead of SSIS.

Where is the msdb, model and tempdb in Azure SQL?

Comparing SQL Server Enterprise with Azure SQL, we can see four visible databases in the SQL Server Enterprise. Master, model, msdb and tempdb:

In Azure SQL, we can only see the master database. Why? Because the databases are hidden:

If we use a query to show the databases in Azure, we will notice that only the master and the database created are visible:

Select * from sys.sysdatabases

What about the tempdb database? It is not visible, but can we create a temporary table? Let's try it. We are going to create a temporary table, insert some data and do a select on it to test it:

create table #temp1 (id int,productname varchar(30))
insert into #temp1 values (1,'PC'),(2,'Tablet')
select * from #temp1

OK, temporary tables work fine. What about global temporary tables? Do they work? Let's try to create a global temporary table:

create table ##temp1 (id int,productname varchar(30))

When we try to create global temporary tables, the error message is the following:

As you can see, global temporary tables are not supported. This is because of the complexity to share temporary objects in Azure SQL because of the restrictions in connections.

There is no msdb database because there is no Agent and mail services and we do not have Log Shipping either. In other words, there is no need to have access to the msdb system database in Azure SQL.

About the model, it is not visible. What happens when we create a new database in Azure SQL using the SSMS?

CREATE DATABASE DB1

When you create a database in traditional SQL Server with no other statements, it uses the model database.

In Azure, when we create a database, it is created using the Standard 0 (15 USD per month approx.). You can see the database created in the Azure Portal:

In Azure SQL you do not have the traditional server roles

In Traditional SQL Server Editions, we have a list of server roles that can be listed with the following stored procedure:

EXEC sp_helpsrvrole

The stored procedure lists the following server roles:

If we try to run that stored procedure in Azure SQL, we will get the following results:

CREATE LOGIN Peter
WITH PASSWORD = 'MyPassword!@3444'
GO
ALTER SERVER ROLE sysadmin ADD MEMBER Peter ;  

The message displayed by the T-SQL sentence is the following:

If we want to login with the Login created (Peter), we can assign the following roles to have access:

ALTER ROLE dbmanager ADD MEMBER Peter; 

The dbmanager is like a database creators. It is a role to create new databases and manage them.

There is also another special role called loginmanager, which is used to create logins.

You cannot switch to another database in Azure

There are also some restrictions when we use the USE clause in SQL Azure to switch from one database to another. The following example shows the traditional sentences to switch to the master database:

USE MASTER
GO

This is because we need to create a connection to switch to another database. When we run the sentence the following error message is displayed:

Conclusion

As you can see, the Azure SQL is a different Database than the traditional SQL Server Editions. It has a similar basic syntax in T-SQL for basic operations, but it does not have the same system procedures, the same syntax, there are sentences and keywords not supported and the roles and security are different.

In SSMS, you can view objects, but to create, alter and drop objects, you can only do it by using T-SQL. The SSMS functionallity for Azure SQL is limited.

Some of these limitations may change in the future, but, by the moment, this is the way it works now.

References

• Scheduling a job on Azure SQL 
• SSIS for Azure and Hybrid Data Movement