search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.29k stars 21.47k forks source link

Disk configuration recommendation for SQL VM #7003

Closed Ayanmullick closed 6 years ago

Ayanmullick commented 6 years ago

Is it Microsoft-recommended to deploy a SQL VM with just one disk? How do I specify so that SQL is installed on a separate disk than the OS disk like one can thru the GUI upon selecting the 'SQL2014SP2-WS2012R2-BYOL' image?

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

mimckitt commented 6 years ago

@Ayanmullick You would have to deploy a 2012R2 machine then install SQL on it after that fact. We don't have an option to install it on another disk when provisioning if you are using a SQL image.

As per the disks, it is suggest you use attached data disks for all date. You can refer to the SQL performance guide on our best practices.

Ayanmullick commented 6 years ago

The Portal has the option to install it on another disk when provisioning if you are using a SQL image. Ref: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-server-provision#storage-configuration

The same should be doable thru PowerShell

mimckitt commented 6 years ago

This is just showing you that you are adding a data disk to be used with SQL. Maybe I am not understanding your question...

image

When you select the SQL image it spins up a VM with SQL installed. It will be installed on the OS disk (C Drive) and will include a data disk in the build. It will add more data disks based on the amount of IOPS you are looking to have with your instance.

There is nothing that states install OS on C drive and SQL on E drive. If I am not getting this can you elaborate for me?

Ayanmullick commented 6 years ago

I assumed that the GUI provisions a Microsoft-recommended SQL configuration. What is the usecase for the 'SQL2014SP2-WS2012R2-BYOL' image if it provisions a non-optimal configuration?Only for testing?If so it should say that here.

mimckitt commented 6 years ago

@Ayanmullick can you share a screenshot of what you are seeing? I am having issues getting the GUI to load for that image

Ayanmullick commented 6 years ago

The SQL images are probably not exposed to MSDN subscriptions. When Provisioned thru the GUI, SQL points to the additional data drive for database and logs location.

image

mimckitt commented 6 years ago

Are you referring to this area?

image
Ayanmullick commented 6 years ago

When provisioned in the above way thru GUI, SQL points to that disk for Logs and Databases. But that isn't what happens when deployed thru this Powershell Script.

I just wish that the Azure MarketPlace SQL images provisioned a production-optimal Microsoft-recommended configuration.

rothja commented 6 years ago

@Ayanmullick @MicahMcKittrick-MSFT Thanks for the feedback on the portal experience. We're trying to tighten up the recommendations for SQL Server VMs, because it is confusing that we recommend two disks in some cases and yet provision everything to one disk in the portal. With storage pools and striping, it is important that everything is on one volume in some scenarios (FCI is one). Regardless, I apologize that this is confusing, and we've heard this feedback from other customers too. I'm working directly with the lead for SQL VMs, and she is investigating whether we need to update the portal images, our docs, or both. Thanks for your patience!

mimckitt commented 6 years ago

@rothja thanks for clarifying! Obviously @Ayanmullick and I both were having issues with it so good to hear we have plans in place :)

@Ayanmullick I will close this out for now but if possible @rothja please let us know when some updates are made :)

hpatel01 commented 6 years ago

Is there a update on this, its so cumbersome to deploy image in availability set and then implement best practices like moving system DBs, errorlogs on another disk, image needs to have atleast 2 disks, one for Windows/OS and 2nd for system DBs.

Ayanmullick commented 6 years ago

@hpatel01 , This is the script I'm using to move each SQL component after deployment https://dba.stackexchange.com/questions/206201/how-can-the-system-databases-in-sql-server-2016-be-moved-using-powershell/206536#206536