Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Scott Klein atscottkl@microsoft.com or Thomas LaRock at thomas.larock@solarwinds.com
Q: What is a VHD?
A: VHD is a Virtual Hard Drive (or Disk). Here's a link about uploading a VHD to Azure: azure.microsoft.com
Q: What is IOPS?
A: IOPS is I/O per second, and is used to describe the maximum number of reads and writes to a disk or storage area.
Q: Why does A7 & A8 go up to 56 and not enough to max out standard edition?
A: I believe this is a limitation for the O/S itself, in order to help maintain a level of performance for all guests on a host. In other words, the 56 isn’t something SQL specific, it is O/S and guest specific.
Q: Why do they not give A7 & A8 enough memory to maximize standard edition's memory (64)?
A: [This amount of memory] is independent of the SQL edition. We need to balance out the core/memory to maximize the number of VMs we can accommodate.
Q: Is there a link for more information about A8 and A9?
A: See details here: azure.microsoft.com
Q: We have 1 TB limitation for Drive. Why?
A: Tom LaRock: I don’t know the technical reason for the limitation, but you can attach 16 drives at 1TB each, which seems like that would be enough for most users. Scott Klein: No technical limitation. People have asked for larger sizes but they are focusing on provisioned IO rather than increasing the size of the page blob.
Q: Is temp drive = tempdb? What is the temp drive used for and what should go on it?
A: No, the term “temp drive” is the temporary disk. You can read more details about this at: msdn.microsoft.com What should go on the temp drive is your swap file and anything else you don’t mind possibly losing upon reboot, or if you don’t care about read/write performance levels.
Q: Any guidance on getting some kind of benchmarks for testing deployments w/ eye on costs?
A: Scott Klein: Not at this time, but information will be available in the upcoming months.
Q: What is an affinity group?
A: An Affinity Group guarantees that all virtual machines (VMs) will be located in the same region, and groups to optimize for performance. Refer to this link for more information: msdn.microsoft.com
Q: Is (local domain) windows integration an option? For provisioning ...
A: Thomas LaRock: That depends on what you are looking for (Scott please correct me). You cannot use a local disk for an Azure VM. But you can upload a local disk to Azure blob storage and use that for your Azure VM. Scott Klein: Yes, Tom is correct. However, if the question is more about whether you can domain join an Azure VM to a local domain, you can in fact do this.
Q: Does an [Azure VM] customer have a choice to choose a VM host and location?
A: You cannot choose your host, but you do get to choose the location of your data center.
Q: Is there any alternate option [to] replicate to [an]other Geo location?
A: Any alternate method would be a manual process for you to build yourself. Scott Guthrie has a wonderful post that details the geo-replication service: blogs.msdn.com
Q: What is the default allocation unit size?
A: 4k
Q: Why not use Azure disk caching? Shouldn't that increase read performance?
A: The issue here (as I understand it) is that caching, while it might be good for performance, is actually an issue with regards to SQL Server maintaining transactional consistency (ACID) as outlined here by CSS: blogs.msdn.com
Q: Is it recommended to have system databases (master, model, msdb) in its own data drive?
A: Yes, not necessarily for performance, but definitely for DR purposes.
Q: Should we create multiple disks in storage areas and/or stripe them?
A: Yes and yes.
Q: Why is the performance for temp disk not predictable?
A: Because it is a shared resource amongst all the guests on the host, and the most volatile to something like a “noisy neighbor” syndrome.
Q: Is a secondary DC needed for [disaster recovery] DR purpose?
A: If you disable geo-replication, then yes, you would want to use a second data center for your DR needs. However, there are three copies of your data inside the primary data center, so chances of your server disappearing completely are very small.
Q: What is column count and how does is reflect in WAVM
A: The column count is something specific for Storage Spaces and you can read more details about that here: technet.microsoft.com
Q: Are we going to talk about how much it would cost to spin SQL Azure VM?
A: That wasn’t in the scope of this webinar] but here is the Azure Pricing Calculator: azure.microsoft.com/
Q: Would you be able to touch on the benefits of using Azure IaaS (Infrastructure as a Service) vs Azure PaaS (Platform as a Service) for a SQL Server service? Would you recommend PaaS for production deployments? It sounded like PaaS had lower levels of functionality, but at a lower price point.
A: Azure IaaS offers you more control as well as the ability to run a fully function edition of SQL Server. The PaaS (aka SQL Database) does not have all the same features as an on-premises version of SQL Server. Use of PaaS for production depends upon your requirements, which are different for everyone. And yes, PaaS is typically a lower price point.
Q: Is Microsoft using SSD for disk storage?
A: Not at the moment, no.
Q: Is there a standard algorithm to warm up the disksm, or a utility, perhaps?
A: Refer to this link for a sample script: download.microsoft.com
Q: What if I use 128-kb allocation when formatting VHD? Will it be more performance than 64-kb allocation?
A: Great question! At first I wanted to say “No,” but it turns out that you might want 256-kb stripes as outlined here: hmsdn.microsoft.com
Q: Please publish the url for the whitepaper referred to. (“Performance Best Practices for SQL Server in Azure Virtual Machines”)
A: “Performance Best Practices for SQL Server in Azure Virtual Machines”: msdn.microsoft.com
Q: Do we have the option to choose third-party monitioring tools …?
A: Yes, you can use SolarWinds Database Performance Monitor (formerly Confio Ignite) to monitor database performance on VMs:confio.com
Q: Do you have recommended value for all these counters?
A: They are no different than the recommended thresholds for an on-premises server.
Q: Does it work with SQL 2008 Enterprise?
A: You can install SQL 2008 on an Azure VM, yes.
Q: Why not use autogrow? Do not use autogrow at all or is it still recommended to preallocate plus some autogrow?
A: Thomas LaRock: Ideally you would be able to size your data files upon creation of the database and never need to allow for them to grow automatically. However, the reality is that autogrow is a nice feature to leave enabled. Frequent autogrowth can lead to additional fragmentation over time however. Scott Klein: Autogrow is considered to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow. If autogrow is used, pre-grow the file using the Size switch.
Q: Is TDE in the works?
A: Not sure what is meant here. Since this is SQL Server in a VM, full SQL is supported, including TDE. You might be confusing SQL Server in an Azure VM with Azure SQL Database.
Q: What about configuring SQL memory?
A: You should configure SQL Server memory in the same manner as you are doing now. Set the max server memory option to a value less than the O/S memory. That value depends upon your needs, but for a 56GB Azure VM I would probably set my max to be about 50GB to start and adjust as needed from there.
Q: What is the azurecat website?
A: They don’t have a specific website, but a good starting point for recommendations can be found here: microsoft.com. In the left hand column you will find many articles written by members of the team.
Q: Would performance be affected if I use SQL AlwaysOn and geo-replicate the second (standby) DB server?
A: Well, performance would likely be affected, but I can’t say by how much. One thing that will be affected is your billing, as you would pay for the egress of all your data.
Q: Any recommendation for the pagefile?
A: The same recommendation for your current servers, I think 4GB is about the right size (unless I need a full memory dump). But if the Azure O/S has 56GB of RAM, and I have a 300GB temp disk, I suppose I could just set it to be 56GB as well. You shouldn’t need to set the swap file to be more than the O/S memory.
Q: If using the gallery SQL VM, how much preconfigured is it?
A: Great question! The template comes with all SQL Server services installed. You can see all the details here: msdn.microsoft.com
