Below are the following two options available in Azure for hosting the relational databases.
- SQL database – Also called as Azure SQL. It’s one of the many services available in the Microsoft Cloud Platform – Azure. This offering is also known as Platform as a Service (PaaS) or Database as a service (DBaaS).
- SQL Server – Another service available in the Microsoft Azure Platform for storing the relational database with all the features available in the traditional SQL Server. This offering is also known as Infrastructure as a Service (IaaS).
Type SQL Database (PAAS) SQL Server (IAAS) Technical Features Not all features are supported. Below are few of the most important T-SQL features that are currently not supported.
1) SQL Jobs and any other feature that are dependent on SQL Server Agent are not supported.
2) You cannot use the “USE <<Database name>>” statement in the SQL Server Management Studio to connect to another database using the same SSMS window.
3) Global Temporary Variables.
4) CLR Stored Procedures – In traditional SQL Server, we can develop Stored Procedures using C# which are called as CLR Stored Procedures. Azure SQL doesn’t support CLR Stored Procedures.
5) You cannot specify the location of the DB files.
6) Any Server-Level activities are not supported in Azure SQL.
7) Database Diagram – Unfortunately, you cannot create a database diagram.
8) Windows authentication is not available – In traditional SQL Server, you can connect to SQL Server using both Windows Authentication and SQL Server Authentication. In Azure SQL, you cannot connect to the database using Windows Authentication. However, you can use Azure Active Directory authentication. Please check the official documentation for more details here.
9) You cannot use SQL Profiler
For a detailed list.All features that exists in traditional SQL Server are supported. Non-Technical stuff 1) Microsoft will take care of the Maintenance of the infrastructure.
2) Database size upto 1 TB.
3) One of the biggest advantages of going for SQL Database is Licensing. We don’t need to purchase any license for SQL License as it is a service from Azure. We just need to pay on an hourly rate based on Service tier a Performance level. All the upgrades, patches to SQL Database are taken care by Microsoft.1) You need to take care of the Infrastructure.
2) Database size can be more than 1 TB.
SQL Server (IAAS) is too costly when compared to SQL database. SQL Server (IAAS) support the following two options.
3) You bring your own license and apply the same for the SQL Server.
4) You can utilize the existing Azure provided SQL Server Image from the Azure Market Place which includes License and pay per-minute for the SQL Server and the Windows Operating System that you choose for the VM
Join the conversation! Your thoughts help the community grow.