What I did for the week ending 2018/08/24

The highlights for this week have been a few videos from the free-to-view PASS Summit 2016 archive, available at PASS.org.

Managing SaaS Application Databases with Elastic Jobs: SQL Agent and More for Azure SQL Database, presented by Deborah Dove

• Essentially, "Elastic Jobs" is SQL Agent for Azure SQL. She also talks about "Elastic Query", which allows you to run queries on all of your databases, or a specific subset of them.

Azure Data Services: Spotlight on Azure SQL Database, presented by Debora Dove

• I think that this is a good introduction to Azure SQL and it covers Elastic Pools. I'm sure that I learned a few things. One thing that bothers ms is the low query/minute ceilings for the various offerings.

Design Patterns for SaaS Applications with Azure SQL Database, presented by Bill Gibson

• If you are going multi-tenant, sharding and employing Elastic Pools seems to be the only sane choice.
• "Most of our customers are compute-bound, not storage-bound."
• There are various clever things you can do with pools and tagging.

What I did for the week ending 2018/08/17

The summer has been busy and my Azure training schedule has suffered. Here are a few highlights from the last few weeks of study.

The following are presentations that occurred during SQLPASS Summit 2016. I can't  link to these presentations directly because they are behind a paywall (though it is free to subscribe):

Backup and Restore SQL Server Databases in Microsoft Azure, presented by Andy McDermid and Pinal Dave

This presentation is about Virtual Machines, not SQL Azure. My takeaways were:

• Segregating data, log, tempdb and backup locations is still recommended practice.
• The "backup to URL" feature can be useful because frees up a disk because it is not needed to hold backup files and writing to a URL doesn't count as bandwidth usage against your VM disks. "Backup to URL" can stripe the data just like writing "regular files" can. You need to ensure that the costs associated with the (required) Azure Storage Account make sense.
• You can stripe VM disks for performance, but remember that VM sizing has rate-limits on the disk performance. If striping provides capacity that exceeds those rate-limits, you won't get the performance benefit that you expect.

Virtual SQL Servers. Actual Performance, presented by David Klee

Klee is always worth listening to, though his client's systems are much bigger than anything I've seen. My takeaways were:

• Your virtual sockets should look like your physical sockets. Put everything inside of a NUMA node, if you can.
• Always consider: "What if my VM gets moved to a host with a different processor configuration?"
• Paraphrased: "Changed from 1x16 (socket x core) to 2x8 and the performance went up 25%"
• Paraphrased: "A client had every VM set to 64 vCPU. Changing from 64 vCPUs to 4 vCPUs got a throughput 3.5 times higher than anything the client had seen up to that point".
• Turn off "hot-add CPU" because it disables proper NUMA configuration.
• Hypervisors generally ignore hyper-thread "cores" until the server is really loaded up. That level of load should not happen often. Therefore, there is little use in turning HT off in the BIOS.
• If your storage does compression, rely on that and turn off SQL Server's data compression.
• Virtual Disk Controllers (VDCs) are still a problem. Creating additional (non-default) VDCs and spreading your IO load around is recommended.
• As always, test any change to make sure that you get what you expect.

SQL Server in Azure VM: Best Practices, Latest Features, and Roadmap, presented by Luis Vargas

and 

SQL Server High Availability & Disaster Recovery in Windows Azure, presented by Lous Vargas, Sanjay Misra, et al

Both of these were very good, with some overlap. I liked the first one so much (which Vargas presents "solo"), I watched the second one when I noticed that he was one of the presenters. My takeaways were:

• You are better off using storage pools, not MDF/NDF files, for striping and performance.
• Licensing seems to only get more complex as time goes by.