Follow up to the SQL Server Fireside Chats after the 24 Hours of PASS Sessions

October 27, 2011

Product
SQL Server 2012

Content type
Thought Leadership

The SQL Server Team held Fireside Chats at the end of the 24 Hours of PASS on September 7-8, 2011 to give attendees the opportunity to ask questions directly to members of the SQL Server engineering team. The engineers answered most of the questions during the chat, however there were a few questions that we promised to follow up on at a later date. That date has come!

Please find the answers to all the outstanding questions listed below. We encourage you to listen to the audio recordings (below) if you weren’t able to join us during the 24 Hours of PASS, a great way to learn more about SQL Server 2012 directly from the engineers.

Audio Recordings of the Fireside Chats with SQL Server Engineers

Fireside Chat #1 on Wednesday, September 7, 2011
Fireside Chat #2 on Thursday, September 8, 2011

Outstanding Questions and Answers

Has FullTextSearch been improved or enhanced?

We have implemented several significant changes to Full-Text Search in SQL Server 2012: http://msdn.microsoft.com/en-us/library/cc645577(v=SQL.110).aspx Property Search, customizable proximity searches and new word breakers and stemmers are a few. In addition to these Full-Text Search enhancements, we are introducing Semantic Search: http://msdn.microsoft.com/en-us/library/gg492075(v=SQL.110).aspx. Semantic search builds upon the existing full-text search feature in SQL Server, but enables new scenarios that extend beyond keyword searches. While full-text search lets you query the words in a document, semantic search lets you query the meaning of the document.

Can contained databases work with Windows logins or only with SQL Server logins?

Contained databases support both Windows Authentication and SQL Server Authentication. Additional details are available on the SQL Server Security Blog: http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/08/contained-database-authentication-in-depth.aspx

Anything changing with Pivot/UnPivot?

There are no changes to the PIVOT/UNPIVOT clause in SQL Server 2012.

Extended events new interface has a wizard to build sessions with an option to choose templates. As of yet only one template “Standard” is listed. Are any further templates planned?

Yes, we are introducing a number of templates in the RTM product. The templates will cover scenarios such as identifying lock intensive queries, tracking query execution in various ways, collecting wait statistics and tracking connections to the server. We will remove the Standard template in CTP3 and it will be replaced by one of the new templates. You can also create your own templates and add them to the template list in the Wizard if you find a particular session useful.

Can you manage Contained Databases using Policy-Based Management? i.e., can PBM be used to mitigate some of the security risks of privilege escalation?

PBM works with Contained Databases the same way it works with regular databases, but since PBM is a server level feature the policies will not be contained. Refer to the specific documented threats against Contained Databases, and you can build appropriate policies to mitigate the risks.

Is there a way to manually re-run a Reporting Services report subscription? Currently in SQL 2008 – you must determine which one of the SQL Agent Jobs is associated with that subscription and manually execute that job.

Unfortunately, you will still need to map the SQL Server Agent Job to the subscription to manually execute a subscription.

With regard to Ring-Buffers, we have an issue with the 2MB buffer XML” which is a problem we have to work around. Is this planned to be resolved so we can fully take advantage of Extended Events to RingBuffer target?

The issue is not actually with the ring_buffer itself, but with the amount of information that we can return from the DMV dm_xe_session_targets. This was limited to 4MB in SQL Server 2008 and 2008 R2, and the ring_buffer can actually hold much more than that. The ratio of expansion from the binary data stored in the ring_buffer to the XML output by the DMV produced the recommendation that you limit the size of the ring_buffer to 2 MB so as not to miss data. In SQL Server 2012 we have increased the size that we can output via the DMV to 8 MB. We are restricted to having a fixed size because of the way DMVs work. We expect that this size should cover the desired usage scenarios for the ring_buffer, which by definition is designed to be used for temporarily retaining data using a cyclical “first in, first out” pattern. If you are trying to keep data for archival purposes, or need to keep more than 4 MB of data (which is a huge number of events) then the event_file is the recommended target.

Are there any improvements to Change Data Capture (CDC) in terms of ease of querying changes and performance overhead?