Choosing Where to Host SQL Server Database

SQLServerF1

Cloud solutions has been gaining increased support and many customers moving their data on to cloud technologies or planning to move in future. When it comes to running SQL Server or hosting a SQL Server database, there are several options for DBAs or management which include On-premise physical server, On-premise Virtual server, Infrastructure as service(IaaS) or SQL Azure database as service(PaaS). There are various factors which can can consider about choosing which applications are better run on which of the above mentioned environment and which features impact on where the SQL Server instance and databases are better hosted in one of the above environments. Below are list of items to consider for choosing where to host SQL Server database from On-premise physical server, On-premise Virtual server, Infrastructure as service(IaaS) or SQL Azure database as service(PaaS).

SQL Server Version Support – If you are looking for flexibility and control over which versions of SQL Server instance and which patches to be applied then any one from On-premise physical server, On-premise Virtual server, Infrastructure as service(IaaS) can be your choice as all these support any version of SQL Server and patches to be applied based on DBA/Developer team recommendations. Where as SQL Azure database as service(PaaS) does not offer this flexibility and allows us to only choose from one the existing versions available and any new patches may be forced at times.
Security – For very critical applications, On-premise physical server, On-premise Virtual server are preferred as these are maintained at our own data centers and brings more control over security. Infrastructure as service(IaaS) can be used for sensitive applications which have high security requirements, but not too very highly critical.

Storage – In On-premise physical server, On-premise Virtual server, the storage support, configuration and its performance is purely determined by the storage teams as per the requirement from the DBAs, Developers and Management. For Infrastructure as service(IaaS) or SQL Azure database as service(PaaS), the storage and its speeds are to be chosen from available options and has different cost for different storage sizes and performance, so based on our requirement we can choose the require storage.

Backups – For On-premise physical server, On-premise Virtual server backups are to be taken care by DBA or backup team and can choose to use native maintenance plans, custom scripts or third party backup tools and can perform backups locally or to backup share or tape. In Infrastructure as service(IaaS) backups can be configured to store locally which is Azure storage. In SQL Azure database as service(PaaS) backups are taken care by Microsoft and we only have to choose how many days the backups are to be stored which decides RTO or RPO. Depending on the settings chosen the cost would be impacted.

Cost – On-premise physical server involves high cost for maintaining hardware, OS, Network, Storage, SQL Server, etc. For On-premise Virtual server the cost of hardware less compare to On-premise physical server as we can use one server host to host multiple guest systems, this reduces the hardware maintenance cost, but this will bring additional cost of administering virtualization, rest all costs remain same for Storage, SQL Server, etc. For Infrastructure as service(IaaS) the cost is further lower as this reduces the hardware maintenance cost as it is taken care by Microsoft, but other costs of Storage, SQL Server, etc will remain same. SQL Azure database as service(PaaS) is the lowest price option available as hardware, and many SQL Server operational costs are reduced as these are taken care by Microsoft.

Suited Applications – On-premise physical server, On-premise Virtual server are better suited for applications which are hosted on our own data center which avoids network latency between applications and databases. Mission critical, data sensitive and high performance required applications are better to be run on On-premise physical server, On-premise Virtual server where we want more control. Infrastructure as service(IaaS) is best for Dev, Test type servers or servers which are not mission critical, but still are important and expected high performance with varied performance at different times. SQL Azure database as service(PaaS) is best suited for new applications developer keeping in mind cloud technologies to take advantage of cloud features.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Leave a Reply

Your email address will not be published. Required fields are marked *