Azure SQL Database is a database as a service (DaaS) offered by Azure. It enables you to implement a SQL database without worrying about software or hardware installations.
Single Database —a single database, deployed to an Azure VM and managed with a SQL Database server. This is the most basic deployment model.
Provisioned —your database uses Azure resources dedicated to the Azure SQL service.
Select Azure SQL from the left-hand menu in Azure portal. Select + Add and choose the Select SQL deployment option page. Click Create.
Managed Instance —a fully-managed database instance. It is designed to enable easy migration of on-premises SQL databases.
Hyperscale —an expansion of the Business Critical tier. It is designed for massive OLTP implementations and enables auto-scaling of compute and storage.
To prevent third-party access, ensure that any users or applications with plaintext access to keys or data run outside your cloud. This reduces Internet accessibility to this data and contains your risk.
SQL uses all CPUs available from the operating system. It creates schedulers on all the CPUs to make best use of the resources for any given workload. When multitasking the operating system or other apps on the SQL server can switch process threads from one processor to another. SQL is a resource intensive app and so performance can be impacted when this occurs. To minimize we can configure the processors in a way that all the SQL load will be directed to a pre-selected group of processors. This is achieved using CPU Affinity Mask.
Giving SQL this permissions means that, when requesting space for data files, SQL tells Windows to mark the space as used and immediately hand it back to SQL, which results in faster data file growth.
When a query is run, SQL tries to allocate the optimum amount of memory for it to run efficiently. By default, the min memory per query setting allocates >=1024 KB for each query to run. Best practice is to leave this setting at the default value of 0, to allow SQL to dynamically manage the amount of memory allocated for index creation operations. If however SQL server has more RAM than it needs to run efficiently, the performance of some queries could be boosted if you increase this setting. So long as there is memory available on the server, which is not being used by SQL, any other apps, or the operating system, then boosting this setting can help overall SQL server performance. But if there is no free memory available, increasing this setting would likely hurt overall performance rather than help it.
The min server memory option sets the minimum amount of memory that the SQL instance has at its disposal. Since SQL is a memory hog which chews up whatever RAM throw at it you are unlikely to ever encounter this unless the underlying operating system were to request too much memory from SQL server.
Setting a memory limit for each SQL instance so that the different SQL instances are not duking it out over RAM will guarantee best performance.
If an app has a large number of schemas / stored procedures then this could potentially impact other apps which share the same SQL instance. Instance resources could potentially become divided / locked, which would in turn cause performance issues for any other apps with databases hosted on the shared SQL instance.
So long as there is memory available on the server, which is not being used by SQL, any other apps, or the operating system, then boosting this setting can help overall SQL server performance. But if there is no free memory available, increasing this setting would likely hurt overall performance rather than help it.
While multiple cloud solutions exist, the most common for running SQL Server tend to be Amazon AWS EC2, Azure Virtual Machines, and Google Cloud Platform (GCP) Compute Engine. While each of these platforms also offers fully managed SQL Server solutions, I’m specifically talking about self-managed virtual machines running on the cloud platform. Each of the cloud providers offers template-based deployment of virtual machines of different sizes of vCPU, memory, and with different I/O throughput limitations. I/O configurations in the cloud are significantly more complex than on-premises since multiple factors have to be considered to maximize the performance of the I/O subsystem. The instance type will determine the maximum amount of I/O the instance itself can perform.
Using the PVSCSI virtual storage controller, Windows Server is not aware of the increased I/O capabilities supported. The queue depth can be adjusted for PVSCSI in Windows Server to 254 for maximum performance . This is achieved by adding the following key in the Windows Server registry “HKLMSYSTEMCurrentControlSet servicespvscsiParametersDevice /v DriverParameter /t REG_SZ /d “RequestRingPa ges=32,MaxQueueDepth=254”
Many of the cloud VM template s also offer non-persistent storage configurations known as ephemeral storage, which provides high throughput/low latency storage temporary to the virtual machine. This can be great for things like SQL Server tempdb, buffer pool extension files, scratch file storage, etc., but it also has the additional requirement of being reconfigured anytime the OS is shut down and then restarted. The drive letter assignments for the ephemeral storage, as well as any file system folder structures or permissions, won’t exist when the system first boots. This generally requires running a script on Windows startup to partition, format, and assign a drive letter to the device, and then creating any folders and permissions required for SQL Server before the SQL Server service is started or it will fail. There are plenty of free example scripts to handle this task and then start the service available online for the different cloud platforms, but this is an additional configuration consideration.
They’re not eternal, so stop using best practices from SQL Server 2000 and 2005 timeframes for hardware systems today, except where they’re tested to be providing benefit.
I’ve seen this countless times where a SAN vendors “best practice” configuration for SQL Server is ignored to follow the old I/O Best Practices for SQL Server guidance from Microsoft, resulting in an over-complicated configuration that doesn’t perform as well as the SAN vendor’s tried-and-proven recommendations.
While separation of data files and log files physically in storage today would still be recommended, the reality is performance impacts on SSD, NVMe, and even most SAN-based storage configurations today, from mixing random and sequential I/O’s together, don’t exist. I’m a huge advocate of separating log files to RAID 1 or 10 storage for higher redundancy when possible, and I also advocate having completely different storage for backups and maintaining a secondary copy of the backup files (e.g., in the cloud, on a storage appliance, another server) to ensure you can always access the backups in an emergency. In 2021 alone, SQLskills has received countless requests for consulting from businesses impacted by ransomware attacks that encrypted not only their database files but also the only copies of their backups as a result of them only being stored on the same system.
When I started working with SQL Server, virtualization was in its infancy, and few businesses would consider virtualizing mission-critical workloads like SQL Server due to the limitations and performance requirements. However, today, virtualization is the norm for most operations, and dedicated physical hardware implementations are becoming more of the exception rather than the rule for SQL Servers. While virtualized SQL Servers allow better utilization of hardware resources and much more dynamic scaling, it’s also not without its own challenges, especially where storage is considered. While SQL Server behaves the same whether it is virtualized on-premises or in the cloud, there are very distinct differences and considerations around storage configuration between the two that will greatly affect the performance. Here are some best practices for virtualizing SQL Server.