A database is the heartbeat of most modern web and mobile applications and services. It’s the thing that keeps your application ticking. The server that hosts your database on the other hand, will help determine its reliability, availability, and performance.
You can see why it’s important that you choose the right one.
But how?
A question we often get asked from our clients is: which server hosting option is right for me?
The good news: you have options. Lots of them, in fact. The not-as-good news: because there are so many options, it can seem a little overwhelming when you’re not armed with the right information to help you make that decision.
Not to worry!
We’re here to put your mind at ease and give you a breakdown of the best hosting options that will work for you, beginning with SQL.
What is SQL?
Generally speaking, if you want your website to be able to store and retrieve data from a database, your web server should have access to a database system that uses the SQL language.
SQL, or Structured Query Language, is the standard language for dealing with relational databases. SQL can be used to insert, search, update, and delete database records. It can also support a lot of other operations, including the optimization and maintenance of databases.
SQL can be used for a great number of important things, including:
- Accessing the data in the RDBMS system
- Describing the data
- Defining the data in a database and manipulating that specific data
- Creating and dropping databases and tables
- Using the function in a database, creating a view, and the storing procedure
- Setting permissions on tables, procedures, and views
Now that you know a little bit more about SQL and the advantages it brings, let’s get down to the nitty-gritty and talk about which SQL hosting options are right for you.
SQL Hosting Options
Principal Architect (and overall software engineer badass) at Inventive, Simon Timms, recently sat down and laid out a handy guide for choosing the right SQL hosting platform.
“There are a bunch of different ways to host SQL Server workloads on Azure,” Simon says. “Answering some questions about how you use SQL server can help guide us in picking the right option for you.”
Simon listed three options (in order of his preference) for SQL hosting that we typically consider.
They are as follows:
In Simon's experience, more managed solutions are usually preferential to less managed ones.
There is a huge shortage of SQL server skills out there so if you can take a more managed approach, then you’re less likely to run into problems that require you finding an SQL expert. Simon frequently says to companies that they’re not in the business of managing SQL servers but in the business of building whatever widgets they build.
Unless there is a real need, he recommends that you don’t waste company resources building custom solutions when you can buy 90% of the solution off the shelf.
When Simon talks with companies about migrating their existing workloads to the cloud from on-premise SQL servers, he begins the conversation with a ten-point survey to make sure we've already navigated what's ahead of us before any software is installed:
- Does your solution use cross-database joins?
- Does your solution make use of the SQL Agent to run jobs?
- Does your solution use FILESTREAM to access files on disk?
- Does your solution require fine-tuning of availability groups?
- Does your solution require SQL logins from CERTIFICATE, ASYMMETRIC KEY or SID?
- Do you need to make use of a compatibility level below 100?
- Do you need to make use of database mirroring?
- Does your solution need to start and stop job scheduling?
- Are you making use of SQL Server Reporting Services (SSRS)?
- Are you using xp_cmdshell anywhere in your application?
If the answer to any of the first three questions is yes, then you can’t easily use SQL Azure* and should set the baseline to a managed instance.
If the answer to any of the rest of the questions is yes, then you should set the baseline to a VM running a full-on version of SQL Server.
The only time SQL Azure is the best option is when the answer to all of these questions is no.
*Cross-database joins and SQL Agent can be replaced by Elastic Query and Elastic Jobs but neither one is an easy drop-in replacement. I typically don’t bother directing people to them for time-constrained migrations.
Get even more nuggets of SQL wisdom from Simon here!
Final Notes
There are a number of SQL hosting options that are available for you to use, each with its own advantages. It’s important that you are deliberate in choosing the one that best suits your needs and will be the platform that continues to keep your applications ticking. Still not sure about which SQL hosting option is right for you? Let’s chat! Simon and the rest of our team of dedicated software engineers are here to put you on the path to SQL hosting success!
