Thanks to all 180+ of you who joined the Data Architecture Virtual User Group for a hour all about SQL Server 2019! We reviewed everyone's favorite new features of SQL Server 2019. There were lots of questions, we got off onto tangents and use cases for SSRS, licensing, Hybrid Buffer Manager, Accelerated Database Recovery, and memory-optimized TempDB metadata. I answered some more questions below.
Kudos to Kenny Neal for organizing!
Slidedeck here:
Q&A Followup:
1. The Database Experimentation Assistant uses Distributed Replay to do AB testing for the same workload on different hardware/platforms. Yes, as long as you can connect the application to the SQL Server instance using Windows Auth, you can do AB testing between on-prem or Azure VM instances of SQL Server and Azure SQL Database or Azure SQL Managed Instance, and only between SQL Server on Windows and SQL Server on Linux. More info: https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-overview?view=sql-server-ver15
2. More info on the announcement that you can get SQL Server for free with Software Assurance if it's an Availability Group replica in Azure VM's: https://cloudblogs.microsoft.com/sqlserver/2019/10/30/new-high-availability-and-disaster-recovery-benefits-for-sql-server/
3. Yep, you can put the SSISDB in an Availability Group. You have been able to since 2012, it has gotten easier/better recently. https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017#always-on-for-ssis-catalog-ssisdb
4. Would "OPTIMIZE_FOR_SEQUENTIAL_KEY" help with BULK INSERTS? Parallel bulk inserts maybe. The sequential key hotspot PAGELATCH_EX issue is not I/O related (because it's PAGELATCH_EX, not PAGEIOLATCH_EX), it's memory page related, and it's related to multiple parallel inserts, not just a lot of inserts. More info here: https://support.microsoft.com/en-us/help/4460004/how-to-resolve-last-page-insert-pagelatch-ex-contention-in-sql-server.
5. Parallel Data Warehouse (now inside of the Analytics Platform System appliance) is in a different lifecycle than SQL Server... I'm afraid I don't follow it and I don't know which of these features is getting over into the PDW.
6. There was a question I think about about Resource Governor and Hybrid Buffer Pool or PMEM (Persistent Memory such as Intel Optane DC). I wasn't sure there was a connection at the time. Still pretty sure there isn't. The memory governed by the Resource Governor is not buffer pool memory but query execution grant memory.
7. Accelerated Database Recovery is a SQL Server 2019 feature - it doesn't depend on database compatibility level! Though you do store the ADR version store in the user database (in a filegroup of your choosing), it works with any database compatibility level. More about ADR here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-accelerated-database-recovery and also in our book, out soon!
8. Are there any drawbacks to enabling the memory-optimized tempdb metadata option? There are some fringe limitations in memory-optimized TempDB metadata that might be an issue for some, but probably not for most. Damir Matešic joined my webinar today and followed up with some more info on the limitations here: https://blog.matesic.info/post/Memory-Optimized-TempDB-Metadata.
If you have any additional questions, feel free to leave a comment here on this blog post or hit me up on twitter @william_A_dba.