Dynamics 365 Business Central 2023 Wave 1 release (version 22) officially supports SQL Server 2022. This means that you can use the latest SQL Server version with your on-premises installations (and I recommend to do it).
SQL Server 2022 has a strong focus on cloud-oriented features, giving everyone the benefits of innovations in the cloud without being force to migrate:
SQL Server 2022 introduces lots of interesting features for Dynamics 365 Business Central:
- Introduction of ordered clustered columnstore indexes to improve performance for queries based on ordered column predicates. This is done by sorting the data prior to index compression.
- More efficient and reduced log files
- Security: SQL Server 2022 integrates with Microsoft Defender for Cloud in your VMs if you have installed the SQL Server IaaS Agent extension.
- Always On and High Availability: SQL Server 2022 allows you to connect SQL Server instances to an Azure SQL Server managed instance, allowing you to offload read-only workloads to the cloud. This feature permits you to quickly and easily create a distributed availability group between an on-premises SQL Server and an Azure SQL Managed Instance for use as a disaster recovery backup server or use as a readonly replica for reporting workloads. You can also manually failover to the managed Instance and back again to the on-premises SQL Server with just a few clicks.
- Azure Active Directory Authentication: you can connect to SQL Server using Azure AD authentication on Linux and Windows servers if they are Azure Arc-enabled servers. Azure Arc-enabled servers allow you to manage Windows and Linux physical and virtual servers hosted outside of Azure.
- Query Store: a feature that serves as the “flight data recorder” of query performance and helps identify performance issues quickly and easily for individual queries, now enabled by default on all newly created databases.
- Azure Synapse Link for SQL: this feature allows you to automatically feed changes to data occurring in SQL Server directly to Azure Synapse Analytics without the creation of a new and major ETL pipeline.
- Integration with Microsoft Purview: Microsoft Purview is a data governance solution that eases data administration across on-premises and multi-cloud environments. Now you can apply access policies, including the new “SQL Performance Monitor”, and “SQL Security Auditor” roles to instances.
- SQL Server Ledger: this new feature creates an immutable track record of data modifications over time using blockchain technologies. This protects data from tampering, which is useful for certain situations and use cases, and also offers advantages for internal and external audits.
If you’re starting a new on-premises Dynamics 365 Business Central installation today, there’s no better choice than using the latest SQL Server 2022 engine.