Month / July 2013

by Arvind Shyamsundar

Data Quality Services (DQS) and Failover Cluster instances

One of my customers made the observation that it is not possible to uncheck the Data Quality Services component in the Feature Selection screen in SQL Server 2012 setup. The interesting thing was that she clearly recalled that earlier it was possible to select it (the default was unchecked), and we wondered what changed that it now mandated the selection of the DQS installation:

image

FYI, she was now running SQL Server 2012 with the Product Update enabled using the command line switches /Action=InstallFailoverCluster /UpdateSource=<path to SP1 EXE>. This means the setup support files are those from SP1.

Analysis

It then came to our attention that in CU1 there was a fix released to ensure DQS compatibility with failover cluster. The issue is documented in KB article 2674817.

Now as per the KB, there is no way to apply the fix (other than a re-install of the FCI Sad smile) to an already-installed FCI which had the DQS components previously selected (but which were otherwise not working due to the issue described in the KB.) So to prevent users from running into the same problem if they were ever to use DQS after the FCI installation, the product team decided that post CU1 the selection of DQS is mandatory.

The other angle on this is, what if you are never planning to use DQS, is this selection going to add services, databases or files which are unused? The good news is that the impact is not much; the selection of DQS in the above feature list just drops a DQSInstaller.exe into the SQL BINN folder, which then has to be (optionally) executed to actually complete the DQS server installation. So, if you never run the DQSInstaller.exe, it will not create the DQS databases on the FCI.

Conclusion

To summarize, though the feature cannot be unchecked, in reality it does not add too much to the footprint of the installed FCI. Hope this helps!

Advertisement
by Arvind Shyamsundar

Error message “Invalid column name ‘uses_native_compilation’” when scripting objects in SQL 2014 CTP1

Due to the In-Memory OLTP enhancements in SQL 2014, there is metadata support added in the form of a new column: uses_native_compilation in the sys.all_sql_objects catalog view. When you use SMO to retrieve a database object (typically when you script it using the SSMS Generate Scripts wizard, for example,) it retrieves properties from such catalog views to populate the corresponding SMO properties such as IsSystemObject and IsNativelyCompiled.

Unfortunately the code in the CTP1 (after all, it is non-production ready status for a good reason Smile) has an issue because it does not consider that IsNativelyCompiled property is absent from the catalog views for older releases of SQL Server. You may see this today as an error when scripting objects from Management Studio 2014, or if you directly use SMO to script objects as well. Note that this issue is only when connected to SQL 2012 or SQL 2008; scripting objects for SQL 2014 instances will work just fine.

FYI the relevant portion of the call stack might look like this:

Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. 
System.Data.SqlClient.SqlException: Invalid column name ‘uses_native_compilation’.

… at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveSprocDependencies(List`1 schemaboundList)

So if you are getting this issue, please vote on the Connect Bug I filed for this issue, so that the concerned team in the product group can prioritize this and fix it. Here is the link again in plain text format:

https://connect.microsoft.com/SQLServer/feedback/details/792637/system-data-sqlclient-sqlexception-invalid-column-name-uses-native-compilation-when-scripting-older

by Arvind Shyamsundar

SQL 2014 In-Memory OLTP ‘Hekaton’: training videos and white papers

Personally, the feature I am most excited about in SQL Server 2014 is the In-Memory OLTP (codenamed ‘Hekaton’) feature. Since SQL Server CTP1 was released, a lot of my interactions with colleagues and customers have revolved around this new feature and the one question everyone has is, how do I get started? Well, here’s a readiness roadmap I have put together to help you!

Videos

Sunil Agarwal, Principal Program Manager from the SQL Server product team, has been one of the key people steering the work towards its eventual public unveiling in CTP1. I’ve been fortunate enough to interact with Sunil on a few occasions and I marvel at his passion and energy. He recently presented three sessions at TechEd 2013 North America, which are now available online, complete with slides and video!

A related set of deep sessions were delivered by Jos de Bruijn (also a key member of the Hekaton PM team) at TechEd Europe 2013. Jos is a PhD and his technical depth and passion to share information is clearly evident – take a look for yourself!

 

In-Memory OLTP Code Samples

Here are some useful samples from MSDN.

 

General Reading

 

Research Papers

All the inside scoops from the team which made it happen!

If you find more resources which you would like to share, please post a comment or contact me through this blog to let me know, I will gladly link them up in this roadmap!