Q93. Two models of using SQL server in the cloud?

IaaS - You can run SQL Server on an Azure Virtual machine.
PaaS - You can use SQL Database as a service.


In this model, SQL Server installed on a Virtual Machine is used. The working of this model is similar to using SQL Server on a local machine and you get the same functionality and capability. You also get administrative access to manage the system. You as an administrator, will be responsible for the availability of the database.

iaas-sql server on virtual mchine

Refer Figure. Our application is running on a VM and SQL Server is also on a VM. A variation of this model is the application which runs on a local machine and SQL server is installed on the VM in the Cloud. Any client application running at different locations can communicate with the database using Azure Virtual Network which will create a virtual private network connection between the database and the on-premise application.

When should I choose ‘SQL Server running in a Windows Azure VM’ option?

This approach is recommended:
  1. If you desire that your IT staff should manage and maintain SQL Server.
  2. If you require full functionality of SQL Server without any changes.
  3. If you wish to quickly move your data to the Cloud.
  4. If you want to leverage multiple options available as images in the gallery offered by Windows Azure.

PaaS - SQL Database

In this model, SQL Database is available as a service and no physical instance of SQL Server is given to the user. Instead, all users share the database server which is available as a logical instance. The database server residing in the Cloud is available as a multi-tenant service and any number of users can use and share this service. A logical instance of SQL Database can be thought of as virtual machine running SQL database for you to use.

Refer Figure. SQL Database provides a shared PaaS relational storage service and multiple clients access logical instance of SQL database server.

An application works with SQL Database just as it would work with SQL Server. Application can create tables, create and execute stored procedures and execute transactions. Applications can also use different data access technologies like Entity Framework, ADO.NET to access the SQL Database. As SQL database is Cloud based service, an administrator is not required to manage tasks such as disk usage, disk clean up and backup.

Even though SQL Database is shared by hundreds of users, isolation is assured by Windows Azure. But the performance will vary depending on the load at that point of time, as it is shared by many users. Once you get access to a logical instance, you can create multiple databases and tables and execute queries.