locked
SQL Azure IaaS & best practice RRS feed

  • Question

  • Hello,

    hope someone with Azure SQL IaaS experience can help me. Currently I am planning the design of an Azure IaaS SQL Server. Generally we want use "SQL pay per minute" Licence and NOT BYOL.

    Therefore we must use the given SQL Image with SQL e.g. SQL 2014 SP2 Windows 2012.

    Ok, so far so good. My Problem here is: The deployed SQL Server is definetly not best practice configured as described in the following document: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance

    Just some examples:

    • Only one drive (Disk F:) for Data and Log
    • 4K Block size for Disks F: and not recommended Blocksize of 64K
    • TempDB is located on Disk C:
    • System DBs are located on disk C:

    that are just some examples. Sureley I can create and execute a Config-Script which will do all the "Best practice" settings for me, but then I would prefer to do my own custom SQL installation.

    And here are my questions:

    • What is your experience with the given SQL Images in Azure?
    • Would you prefere a custom SQL installation or configuration of given SQL Image?
    • If I use a custom SQL installation. How can I use then "pay per minute" included with SQL costs? because in this case I deploy just a windows machine. Can I really use SQL "pay per minute" for custom SQL installation?

    Thanks a lot!

    Thursday, May 4, 2017 8:38 AM

Answers

  • >Only one drive (Disk F:) for Data and Log

    Yes, but it's a Storage Space so you can add additional disks to increase the space or IOPS without reconfiguring SQL Server.  So it's not a bad design.  It's easy to add additional disks if you want to separate the logs.

    >4K Block size for Disks F: and not recommended Blocksize of 64K

    Not sure, but this is may be best for Storage Spaces

    >TempDB is located on Disk C:

    Again, a tradeoff between cost and performance.  The C: drive uses read/write caching so not all the TempDB IOPS go to storage.  You can always provision another data disk for more TempDb IOPS, or you can move it to D:\ if you are willing to do some scripting to create the folders and start SQL on machine startup.

    >System DBs are located on disk C:

    That's generally fine.

    >If I use a custom SQL installation. How can I use then "pay per minute" included with SQL costs?

    The installation media is on the C: drive of the images.  You can script whatever changes you want to make to the base install.

    David


    Microsoft Technology Center - Dallas
    My blog


    Thursday, May 4, 2017 4:53 PM