SQL Saturday 572–Extended Events and Always On

Posted on October 19, 2016 by Amit Banerjee

This is the second year that I will be speaking at SQL Saturday at Portland. Last year I had talked about troubleshooting slow queries. This year I will talk about debugging SQL Server issues without having to use the debugger! As always, this will be an awesome prelude to the PASS Summit!

The event sees a great turnout with a number of data professionals talking about various topics. It’s a great opportunity to meet the SQL Server community and network with the folks that help you on the forums and social media. Also, you have a large number of experts under the same roof. So bring your questions.

Session Title: SQL Server Debugging Made Easy using Extended Events
Track: Enterprise Database Administration & Deployment
Level: Intermediate
Time: 1.30PM – 2:45PM
Abstract
This is a demo filled session that will show you how to debug several common issues in SQL server database engine and identify hotspots as well failures using only extended events. We will showcase how extended events make scenario based troubleshooting easier without having to collect disparate sets of diagnostic data, gather memory dumps and comprise on performance. There have been a plethora of Extended Events which have been added to SQL Server recently based on customer feedback which deprecates the need to run profiler in many commonly encountered situations in production environments. This session covers the new enhancements and capabilities available for Extended Events.

Additional Notes
Understand the diagnostics enhancements in SQL Server database engine Use the diagnostics to troubleshoot and mitigate issues quickly in mission-critical environments Setup session templates proactively to reduce mitigation time during reactive situations.

I will also be delivering a lightning talk on Enhancements in SQL Server 2016 which will keep your SQL Server environment Always On.

A big thank you to the organizers for giving me the opportunity to speak at the event again and talk about the enhancements that the Tiger team is shipping!

Upcoming PASS Summit 2016

Posted on October 13, 2016 by Amit Banerjee

It’s that time of the year again when data professionals across the world converge at Seattle for one of the largest gatherings of data professionals around the world. This is one week of deep technical sessions delivered by community experts, MVPs and Microsoft engineers. With such a high concentration of data professionals around the world, you cannot discount the phenomenal networking opportunities at the event.

PASS Summit is the must-attend conference for all data professionals. There is a wealth of learning, connections, and immediate solutions gained in 3+ jam-packed and inspiring days. And possibly one of the largest congregation of data professional in the world with over 4000 attendees. Our Corporate Vice President, Joseph Sirosh, will talk about Cloud Intelligence Innovations during his keynote session on Day 1. David DeWitt, a very familiar face at PASS summit and in the community, will talk about Data Warehousing in the Cloud during his keynote session on Day 2.

The SQL Server Tiger Team (@mssqltiger) will be delivering sessions, will be at the clinic and is eager to hear from SQL Server customers. I published a blog post which is a summary of what the Tiger team will be upto at the PASS Summit.

Parikshit and I will be delivering a session on Always On enhancements and how it would help monitor, support and troubleshoot your environments better and even make your critical environment more reliable and available!

My session at PASS Summit

Session: SQL Server Enhancements that Keep Your Environment Always On
Speaker: Parikshit Savjani, Amit Banerjee
Session Code: DBA-313-M
Session Date: October 27th
Session Time: 1:30PM – 2:15PM PST
Level: 300
Duration: 75 minutes
Room: TCC Tahoma 5

What you can expect: SQL Server Always On Availability Groups provides a number of out-of-the-box enhancements in SQL Server 2012, 2014 and 2016 which helps analyze common issues with relative ease. Attend this session to find out more about how the new enhancements can be leveraged to improve reliability, reduce manual effort and increase uptime. This session will also do a deep dive using demos of the new visualizations available for common scenarios leveraging the new diagnostic and supportability improvements that were added. As a bonus, we will show you new visualizations and data analysis techniques for common issues that you face in your Always On environments.

Attaching a CDC enabled database on SQL Server 2016

Posted on October 10, 2016 by Amit Banerjee

When you detach a database with Change Data Capture enabled on SQL Server 2014 and below and attach it to a SQL Server 2016 instance, you could run into the error mentioned below while execute Change Data Capture (CDC) related procedures.

Error:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 639 [Batch Start Line 0]

Could not update the metadata that indicates table [<schema name>].[<object name>] is enabled for Change Data Capture. The failure occurred when executing the command ‘insert into [cdc].[captured_columns]’. The error returned was 213: ‘Column name or number of supplied values does not match table definition.’. Use the action and error to determine the cause of the failure and resubmit the request.

This is due to the fact that there is a metadata change in SQL Server 2016 associated with Change Data Capture which does not happen till you manually execute sys.sp_cdc_vupgrade against the newly attached database on the SQL Server 2016 instance.

The “Attach a Database” online documentation has also been updated to reflect this information.