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
- An Azure Account to the Azure Portal.
- 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:
Msg 40517, Level 16, State 1, Line 13
Keyword or statement option 'DEFAULT_DATABASE' is not supported in this version of SQL Server.
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:
Msg 40516, Level 15, State 1, Line 6
Global temp objects are not supported in this version of SQL Server.
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:
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:
Msg 2812, Level 16, State 62, Line 14
Could not find stored procedure 'sp_helpsrvrole'.
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:
Msg 40517, Level 16, State 1, Line 19
Keyword or statement option 'role' is not supported in this version of SQL Server.
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:
Msg 40508, Level 16, State 1, Line 5
USE statement is not supported to switch between databases. Use a new connection to connect to a different database.
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.