If you need to create a SQL Server database within your organisation, using your own hardware, there is generally one well-established way of doing that. You need to provision a server (either physical or virtual), install a SQL Server engine and instance on that server, and then create your database within that instance. There are a huge variety of ways that you can tailor your installation of SQL Server to meet specific needs, but the general principal of server / instance / database remains the same. Your company will need to look after all of these themselves, along with any related tasks you create to support the database.
However, if you are open to hosting your database within a cloud-based environment, there are several different set-ups that you can choose from. All of these are quite different, varying in both their capabilities, and the effort that they will take you to create and maintain.
I couldn’t find a graphic that gave a concise summary of the main features of each option for hosting a SQL database in Azure, so I made the one below. I’ve tried to align each option to their nearest equivalent in an on-premise SQL Server environment.
Click on the “full screen” icon at the top of the picture to see a larger copy, or download a PDF version using the button below the diagram. The highlighted words link to the relevant page on the Microsoft website, which provides more detailed information.
A very brief explanation of SQL databases in Azure
Unlike an on-premise SQL Server environment, in Azure you don’t need to create a server and SQL instance first, before you can create a database. With Azure SQL Database you can simply enter a few basic details of the database that you’d like to create into a form, and Azure will go away and create it using it’s own resources. Azure will also automatically take care of backing up, performance tuning, licensing, and replicating the database for high-availability. This is the lowest maintenance solution, but it does have several limitations.
For example, you can’t run cross-database queries, or really interact with other databases outside of the Azure SQL Database you’re using. Neither can you send mail from the database, or take your own adhoc manual backups. There is also little isolation from other Azure SQL Databases – you’re sharing resources with other customers. The second option – Azure SQL Managed Instance – removes many of these limitations. It allows you to group together several databases within an instance with it’s own dedicated compute and storage resources, and a private IP address. This is closer to the concept of the SQL instance in an on-premise environment.
The third option – an Azure Virtual Machine with SQL Server installed on it – most closely resembles the traditional on-premise server / instance / database arrangement. The main difference is that rather than the Infrastructure team at your organisation providing you with a server that they’ve created with company resources, Azure provides you with one using their storage and compute power instead. After you have the server, the rest of the process is very similar whether you’re using on-premise hardware or an Azure Virtual Machine. Either way, you’ll need to arrange for SQL Server to be installed, and create an instance before you can start creating databases. You’ll need to take care of SQL security patches, back-ups, optimisation, etc yourself too – although there is some automated help that you can get from Azure. This solution involves the most maintenance, but also offers the most flexibility and compatibility with SQL Server.
Of course, there is a lot more to each variety of database in Azure than what I’ve just mentioned, and this is only meant to be a very general introduction. There is a massive amount of extra documentation on the Microsoft website.
If you’d like any additional advice or help on creating databases within Azure, feel free to contact us at hello@dataminister.com