On-Premises or Cloud - SQL Server Brain Basher of the Week #064

Here is the Brain Basher of this week.

During interviews, questions on deciding the hosting place for a planned database is very common. Sometime we ask the best place for a database by giving the certain criteria but sometime we ask that how you can decide considering general factors.

If you were asked the below question, what would be your answer?

"You have to create a database for one of the business processes related to the company. Hosting environment for the database is still undecided. We can either create it as an Azure SQL Database or can use the existing On-Premises SQL Server for hosting this database. What do you think?"

This is the Interview Question of the week.

Since there is no specific requirement, particularly on business need, most of the time, interviewee gets stuck with certain areas. However, as per my experience, it would be great if interviewee can explains the factors related for making such decisions along with pros and cons. Let's list out possible factors that influence the decision;

• Stable Connection - How this database will be used? Frequently, with heavy transactions, or infrequently with less transactions? This is very important because if the database is an OLTP database with heavy transactions and business is depend on it, you need a stable, high speed internet connection. If the company has no such facility that cannot satisfy the speed and the bandwidth required, it is better not to move into cloud. However, getting high speed internet connection is a plan that will be executed soon, hosting in cloud should be considered.
  
• Sensitive data - Some companies, specially some financial companies have policies on sensitive data. If the policy says that data should be maintained only in local servers, then hosting the database in cloud is not going to work. Not only the policies established by the company, there are some regulations enforced by the government, hence it has to be considered too. We may go for a hybrid version, that hosts part of the database in cloud and the rest in on-premises server for making sure that sensitive data is not moved out. This is possible with Microsoft SQL Server (SQL Server On-Premise Database files maintained in Azure).
  
• Accessibility - How our database is going to be accessed? What we need to make sure is, it can be accessed by the audience who need it. Users may access it via a web application, using reports, or using tools like Microsoft Excel or Power BI by connecting directly. If we hosted, can all access data as they want? Does it require special software to be installed? Does it require special permissions? Do we have to purchase more licenses? These are things we have to consider on this. If we have to spend more which is not a part of the budget or it makes the usage complex, we might have to consider on-premise. However, certain scenario make on-premises hosting complex and costly as well.
  
• Size - Is it going to be in megabytes, gigabytes or terabytes? If you expect that database will grow in terabytes within shorter time period, then it should be hosted in a local machine as 1 TB is one of the limitations with Azure SQL Database.
  
• Security - Most common factor that pushes the implementation away from cloud. Many think that having the database hosted with a local server is more secured than placing the database in cloud. However, it is not true. If you go for a good provider like Microsoft, Amazon, there have been no security breaches recorded and they guarantee the security. Securing the local environment is very difficult, have to consider security implementations electronically as well as physically and someone has to continuously watch it, patch it, upgrade it when required and monitor it. Therefore, experts recommend hosting in cloud than in-house when security is considered.
  
• High Availability and Disaster Recovery - If this is required and you have already implemented this using methods like Mirroring, Clustering or even with a backup/restore strategy, and most importantly it addresses required RPT and RPO (read more at http://dinesql.blogspot.com/2016/10/determine-backup-strategy-sql-server-interview-question.html), then you can consider local implementation. However, if you have not implemented anything, then it is always better to move into cloud as almost all providers support HA and DR.
  
• Cost - No doubt, initial cost is always very high when it comes to hosting in local server. You may spend on purchasing servers, software licenses and some extra hardware for speeding up the processes. But the initial cost is very low when hosting in cloud. You may calculate for multiple years and compare, but in most cases, hosting in cloud is always cheaper than hosting in on-premises server.
  
• Maintenance - If it is local, you need someone to do this. Installing service packs required, checking space required, performance tuning, monitoring,... many things. If it is cloud, most of these are addressed by the provider and only few need our attention. Example, Azure SQL Database suggests enhancements on indexing and statistics and we can use these recommendations for performance tuning that saves time and resources. So, if you do not have a dedicated person for performing these, go for cloud.
  
• Features and Functionalities - You may need to implement some processes that needs certain set of functionalities. For example, if you need to send an email using SQL Server Database, you need to use Database Mail feature. If you need to execute a business login on a scheduled time, you need to use SQL Server Agent. You know that these features are not available with Azure SQL Database. Not only that, it does not support all TSQL commands, standard partitioning among multiple volumes (with Azure SQL Database, partitioning is possible but volumes cannot be decided. If need, federation can be considered) is not available. Therefore, we need to think about features and functionalities that will be used with the database and then decide whether we can host in a local server or cloud.

There are more but interviewee should talk about at least mentioned factors. That makes sure that the interviewee is smart and knowledgeable on the subject, not just a person who implement anything when asked.