Thomas LaRock starting of the KeyNote.
Azure, is the topic of this KeyNote. A lot of new nice features coming up.
In-Memory table with columnstore indexes will enable realtime-analytics of OLTP data in a new way.
Tables stretched between on-premises and cloud. Kind of like storage tiering but on table level, really cool.
First session “Building Perfect SQL Server, Every time”
Joey starts by going through settings for Max Memory, MAXDOP, cost for parallelism. Nothing new here, same recommendations as yesterday on the parallelism session.
Getting into Model DB settings. File growth settings and recovery model.
Tempdb config was nothing new, multiple files the most important.
Setup SQL Agent Alerts for critical errors. 823-825 and 016-025
Evaluate if backup compression should be used, better than using storage level reduplication which can give you extended restore times. Compression is better to do on the SQL server to avoid sending more data over the network. Or consider snapshot backup through VSS and agent backup software connected to the storage.
DAC (dedicated remote administrator) is an emergency backdoor, default only works on a RDP session directly. But if that is hogged it is good to be able to do it through SSMS from other servers. Needs to be enabled with the reconfigure advanced option.
Install Ola Hallengren Maintenance Solutions to set up backup and DBCC and index optimization, great tool.
Patch ! But wait 1-2 weeks after release.
Use standards, create a standard policy document. Include thing like, drive names, volume size, editions, settings, O/S, HA and DR options, security. Revisit every 6 months.
At lunch sat down randomly at a table where I didn’t know anyone, turns out that the guy next to me is living less than 1000 m away from where I live home in Stockholm. Small world.
For sql servers where Sharepoint shares a host use resource governor to set MAXDOP=1 for share point but let everything else on hosts run with parallelism.
Second session: “Right-sizing your SQL Server VM” with David Klee
David describes how to setup and collect Perfmon data in this article https://bit.ly/1sqSVns, perfmon data is a key component for right-sizing your VM’s. Without a baseline is nearly impossible to know when improvements have been reached.
Number of vCPU can often be to high, monitor vCPU ReadyTime to find out if lowering number of vCPU’s could improve performance.
Third session: “Performance tuning your backups”
Great session with a lot of great advice about how to increase speed of your backups and restores. Number of backup files, number of buffers and transfer size affects backup and restores times. How much ? as always: It depends….
Forth session: “Azure SQL Database Overview”
Azure now offers 99,99% SLA that is financially backed by Microsoft.
Pricing from $5 to $3000 per month ?
Backup of databases is no extra charge. Restore creates new database, you can then failover to this restored DB. Retension period from 7 to 35 days depending on tier.
Introduces DTU’s (database throughput units) to be able to compare different performance tiers of Azure SQL DB’s.
Online change of tiering when load increases, makes it very easy to scale up or down as your load changes. Is it possible to scale automatically ? Pricing is now on an hourly basis so scaling seems interesting.
Geo-replication enables async replication to between 1 and 4 secondary sites either readable or not depending on tier.
Auditing functionality is also available.
Expanded Surface Area coming soon as preview, will close the gap between SQL Server and Azure SQL DB. Adventureworks DB will then be supported for example. ChangeTracking is also available. Tool will be available to check a DB for changes needed to be able to move it to Azure SQL DB.
All traditional tools and apps on-premises are able to connect to Azure SQL DB’s.