Hi,
This time I will be blogging about SQL databases. First a little about history.
SQL databases had (and still have) a significant part in the structure of a software. They contain storage for the data and configuration for nearly all Microsoft built products. The only one that I cant think of that don’t need is Exchange. Exchange has it own The Extensible Storage Engine (ESE) also known as JET database.
All the others rely on SQL, either the Express versions (ex. AAD Connect, SFB on-premises standalone etc.) or the full pledged version of SQL (CRM, ADFS with more than 5 nodes etc.)
And you always had to make sure about service accounts for agents and db itself, rights users in the DB, backups, Disaster recovery, Fail-overs, updates, tuning performance with the underlying server etc.
The following diagram present the really old-school clustering with identical hardware, firmware, updates.
And this one is AAG (AlwaysOn Availability Group) which was really nice feature compared to the old-school.
So it was a lot to do for On-premises deployments, but I think those were excellent lessons to learn, to understand the future you have to understand the past and I think it will apply also the technology.
So then to the future part, yippee.
In Azure there is a lot to choose from, but also consider the security and identity side more thank with On-premises.
You can assess you current databases with Data Migration Assistant https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15 and it will support the following.
Sources:
SQL Server 2005
SQL Server 2008
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Amazon RDS for SQL Server
Targets:
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017 on Windows and Linux
SQL Server 2019
Azure SQL Database single database
Azure SQL Managed Instance
SQL server running on an Azure Virtual Machine
With this tool you can assess your DB’s and see what can be transferred to Azure as Single Database or Managed Instance.
If you want to assess the whole server or even a group of servers, it’s better to use Azure Migrate tool https://docs.microsoft.com/en-us/azure/migrate/how-to-create-azure-sql-assessment
So now you should be having a picture what can be transferred and how it works.
Inside Azure you these options https://azure.microsoft.com/en-us/product-categories/databases/
And I will be concentrating in the marked ones as options.
What you get with SQL Server on Azure virtual machines:
So if you just want to lift you DB’s to the cloud as-is then it will be SQL Server on Azure Virtual Machines, then you have to maintain the server like before, you have to get more capacity to the virtual server underneath, so not the best option, but sometimes it has to be used example for compatibility reasons.
But you will get automated backup, Disaster recovery and many other features when you install SQL Server IaaS Agent extension https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management?tabs=azure-powershell#feature-benefits
What you get with Azure SQL Managed Instance:
You will get Azure Active Directory authentication inside the DB, how cool is that. Not anymore the manually created users inside the database. So you can allow users created in your Azure to access to the database and maintain only one identity with the security and identity protection features inside Azure Identity.
You will also get all the security benefits.
Isolated environment (VNet integration, single tenant service, dedicated compute and storage)
Transparent data encryption – TDE performs real-time I/O encryption and decryption of the data at the page level.
Azure AD server principals (logins) – https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#provision-azure-ad-admin-sql-managed-instance
SQL auditing – https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure
Advanced Threat Protection – https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure
Then you will get almost the same compatibility as with old-school SQL instance, but you don’t have to update anything. All the software and hardware under you DB engine has been taken care-off.
Overview of the service https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview
What you get with Azure SQL Database:
All the the features mentioned above, but also Elastic Pools. So the deployment options are:
- As a single database with its own set of resources managed via a logical SQL server. A single database is similar to a contained database in SQL Server. This option is optimized for modern application development of new cloud-born applications. Hyperscale and serverless options are available.
- An elastic pool, which is a collection of databases with a shared set of resources managed via a logical SQL server. Single databases can be moved into and out of an elastic pool. This option is optimized for modern application development of new cloud-born applications using the multi-tenant SaaS application pattern. Elastic pools provide a cost-effective solution for managing the performance of multiple databases that have variable usage patterns.
Elastic databases was release in 2016 and it was an really nice addition for Azure SQL services. Serverless db cluster that is expanding as your usage goes up.
With Elastic Pool you don’t have to over-allocate the service, it will find the resources inside the pool and distribute them to the DB that doesn’t have too much use.
And then use Managed Identities. With System-Assigned managed identities you can:
- Grant your VM access to Azure SQL Database
- Enable Azure AD authentication
- Create a contained user in the database that represents the VM’s system assigned identity
- Get an access token using the VM identity and use it to query Azure SQL Database
And then use Defender for SQL for securing access to your data. You can enable it thru Azure Security Center.
Or from Database itself.
Azure Defender for SQL feature detects potential threats as they occur and provides security alerts on anomalous activities. Users can explore these suspicious events using the auditing feature, and determine if the event was to access, breach, or exploit data in the database. Users are also provided a security overview that includes a vulnerability assessment and the data discovery and classification tool.
Threat intelligence enriched security alerts are triggered when there’s:
- Potential SQL injection attacks – including vulnerabilities detected when applications generate a faulty SQL statement in the database
- Anomalous database access and query patterns – for example, an abnormally high number of failed sign-in attempts with different credentials (a brute force attempt)
- Suspicious database activity – for example, a legitimate user accessing an SQL Server from a breached computer which communicated with a crypto-mining C&C server
Defender for SQL pricing.
There is always something new coming with Azure and if you want to see what is in preview, in development or released you can see them at Azure Roadmap for Databases https://azure.microsoft.com/en-us/updates/?category=databases
RSS - Posts