A Windows Azure Virtual Machine is similar to a virtual machine run on premise except that it runs in Windows Azure, Microsoft’s public cloud. Starting with the Windows Azure 2012 Preview release, new Virtual Machine capabilities have been added to the Windows Azure. We support a number of scenarios for SQL Server running in Windows Azure in addition to moving existing SQL Server applications to Windows Azure Virtual Machines. For details on how to migrate your existing SQL Server application, see Migrating with Windows Azure Virtual Machines.
The performance of a relational database in Windows Azure Virtual Machine depends on many factors, including the VM size, the number and configuration of disks, the network throughput and latency, the configuration of the database software and the application workload. We encourage users to benchmark their application on several VM sizes and storage configurations in order to select the most appropriate configuration.
In order to help customers with the task of characterizing the performance for migrating to Windows Azure Virtual Machine, we are working on extending the Microsoft Assessment and Planning (MAP) Toolkit for SQL Server to have more built-in awareness for Windows Azure VMs.
To improve the performance of your database, you need to understand which of the server’s resources is the performance constraint. If your database performance is limited by CPU or memory, users can scale up the compute available to the SQL instance by choosing a larger VM. Follow Set-AzureVMsize for more detail on how to upgrade to a bigger VM size. Please note that this involves taking the VM offline and restarting the VM.
Be mindful that this is a cloud environment with a multi-tenant storage and networking infrastructure, so you should expect some variance in performance. In general, for production workloads we recommend running SQL Server on Medium or larger VMs. For best performance use extra-large VMs.
We recommend the default setting for the SQL configuration option “max server memory” in order to allow SQL to manage its memory needs dynamically.
The number of disks allowed for your Windows Azure VM varies based on the VM size, with each disk up to 1TB. For example, in an extra-large VM, up to 16 disks are supported. For more details, please refer to the table below (source here).
If your performance is strongly disk I/O sensitive, be mindful that a larger VM may be required in order to add enough disks to meet your I/O requirements. For details, please see the How to attach a data disk to a virtual machine topic in How to create and configure a virtual machine in Windows Azure.
Being a shared cloud service, performance of Windows Azure VM, especially the I/O capacity, also depends on concurrent load – performance will vary, for example, based on the time of day. We recommend users to validate the disk configuration to determine its I/O capacity prior to deploying SQL Server. SQLIO Disk Subsystem Benchmark Tool is available for performing such tests.
In terms of configuration, we recommend placing data and log on separate disks from the OS disk for better performance.
The default cache settings on the OS disk and the data disk are different.
For running SQL workloads, we recommend keeping the write cache OFF. In case data has to be placed on the OS disk, disable the write caching on the OS disk in the service model. Use Windows Azure Powershell command Set-AzureOSDisk to disable write caching. Please note that changing the cache settings on the OS disk while running will result in a reboot of the VM.
Apart from Windows Azure OS disk and data disk, there is also Temporary Storage available in the VM (usually exposed as drive “D:\”). This temporary storage, however, does not provide data durability as OS disks or data disks do. We recommend users refrain from keeping data that need to be persisted on this drive.
In general, users have the same database performance tuning options in the Windows Azure Virtual Machine environment that they would have in a physical server environment, this includes resources such as Windows performance counters, Xperf and SQL Server DMVs. Additionally, for logging and troubleshooting performance of the networked drives, the user can leverage Storage Analytics, a tool for performance logging and troubleshooting against Windows Azure storage.
Is this good idea that tempdb placed on D Drive ?
generally, tempdb doesn't need to be persisted and you will get better performance leave it on D drive.
Need step by step guide to setup SQL mirroring and witness server in virtual machine installation.
Always, keep tempdb in separate drive for better performance.