SQL Server in the Cloud Era. Time for Azure SQL Databases?
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
Microsoft in the era of the Cloud with Azure decided to give as many options as possible so that everyone is satisfied. Whether you are fans of on-premise or hybrid cloud.
With the services of SQL Server in Virtual Machine (IaaS) and managed instance (PaaS) it showed its understanding of Hybrid Cloud uses with a very short adaptation time since in these cases and SQL Server Agent we also have DB mail and Change Data Capture and many other functions that we are used to working with.
Our options per case
- DTU model, is a unit of measurement between CPU, memory and disk speed:
- Basic (up to 2GB at a cost of ~4 euros per month)
- Standard
- Premium
- vCore model, Cores and Storage are measured in units:
- General purpose: The most common model has the possibility to choose serverless compute so that you are charged only for the resources you use (even if the server stops completely).
- Business Critical: The high availability model that offers many replicas.
- Hyperscale: Has the maximum scaling of resources. Backups are taken via snapshots.
What's Beyond Microsoft SQL Server in a Virtual Machine on Azure?
Azure SQL Databases are divided into three different categories depending on our needs. Let's see them in detail...
Let's go SQL databases. Although setting up a new one is no more than 5 minutes of installation and yet it changes everything we knew…
We don't take the backups, it doesn't have a sql server agent for jobs, it doesn't play change data capture, mirroring, db mail, DTC, Linked Servers, restores and many more.
The solutions for migration in SQL database is with the Data Migration Service, Transactional Replication, BACPAC and BCP.
The good thing is that Microsoft undertakes to always have the latest version with the latest patch of SQL Server, as well as through automated tuning it can create indexes and force plans by itself.
The SQL elastic pools it's the same case with SQL databases with the difference that we don't make an entire server dedicated to the database, but we have a group of databases on the same server sharing resources.
Thus, in bases that do not have high usage, we reduce costs and make their management easier.
The SQL managed instances is an intermediate solution. We don't have access to any VM that is the instance but by logging into the instance we see that we knew most of the SQL Server functions from on-premise are working.
It has a sql server agent, we can backup/restore in the classic way, db mail and much more.
In the case of migration, the same applies to SQL Databases plus transfer and restore of the classic .bak file.
And since in the cases of Azure SQL Database / elastic pools we do not get the backup, how does it get?
He takes himself by dividing them into two categories:
Long-term backups: Full backups once a week with the possibility to keep up to 10 years.
Default Backups: Differential backups every 12 hours and transaction log backups every 5 minutes which are kept for 7-35 days.
These backups are easily restored at a point in time or can be used to create a new database.
The Elastic Jobs are the corresponding jobs that can be created for the Azure SQL Database/elastic pools of one and there is no sql server agent service. It is a bit more time consuming process to set them up so far.
Azure Data Sync
Azure data sync is Microsoft's first attempt at an active/active replication solution.
It enables us to be able to have synchronized bases even on another continent. What you write on one, write on the other bi-directional or one way depending on what we want. We can define in case of conflict which data prevails. It also works with on-premise bases (requires to download a tool).
But it comes with a few thorns:
- It is not modern
- It is at least 5 minutes late.
- It has quite a performance impact
Active geo-replication / Auto-failover groups
Similar high availability services that create a readable secondary base in the same or another geographical area. In the case that we have a SQL Database or elastic pool, we go to the geo-replication solution and in the case that we have multiple databases or managed instances in an auto-failover group at the server level.
Let's mention at the end the Azure Synapse Analytics which is the equivalent of OLAP SQL Data Warehouse.
