SQL Server 2016 Public Preview (CTP2) – Let’s see how it looks

The Public Preview of SQL Server 2016 is available for download on the Microsoft downloads site. The feature highlights are available in this blog post. I thought I will give blog about what’s different in the setup of the Database Engine.

SQL Server 2016 CTP2 Setup

The basic screens remain the same. The screen to select the edition is the same and the there is no change in the “License Terms”, “Global Rules”, “Product Update”, “Install Setup Files”, “Install Rules” and “Setup Role” pages.

The first major change that you will notice is in the “Feature Selection” page. This is where you will notice a change. Namely, the “PolyBase Query Service for External Data“. This installs two services:

1. SQL Server Polybase Engine Service which creates, coordinates and executes the parallel query plan against external data sources.

2. SQL Server Polybase Data Movement Service which manages communication and data transfer between SQL Server and the external data sources which will be used by the instance.

Note that these services are not instance aware and like Integration Services, there can only be one service per Windows host.

SQL Server 2016 CTP2 Setup

The next screen where you will notice a change is the Feature Rules page which checks if Oracle JRE 7 Update 51 is available or not. This is required for the PolyBase service since I had selected that in the previous screen.

SQL Server 2016 CTP2 Setup

If you don’t have the requisite Oracle JRE version running, then you will be prompted with the following message:
Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required” failed.

This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.

The forwarding link will take you to a webpage where you can download the latest JRE update. Once that is done, you will be able to continue with the installation. Another interesting part was the check for KB2919355 which is the Windows 8.1 Update which contains a cumulative set of security updates, critical updates and updates. So the setup has an OS related check as well.

The Server Configuration page allows you to choose the service accounts for the two PolyBase services.

SQL Server 2016 CTP2 Setup

The Database Engine Configuration page comes with a nifty tempdb file configuration which has a bit of learning to do but provides the opportunity to create additional tempdb files during the installation itself.

SQL Server 2016 CTP2 Setup

Once the setup is complete, you can connect to the database engine using Management Studio and you will see that your version number is 13.0.200. Also, you will notice that there are three user databases: DWConfiguration, DWDiagnostics and DWQueue which is related to the PolyBase service. More on that maybe in a future post.

So that was all about the Database Engine setup for the new SQL Server 2016 Public Preview release.

SQL Server 2016 CTP2 SSMS Disclaimer: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).


SSAS: I will add you to the existing SQL cluster

You have an existing SQL Server 2005 Failover Cluster which has Database Engine and Full-text Search as the clustered components. Now you suddenly decide that this SQL Server cluster group requires a clustered Analysis Services instance. This will then lead you down a rabbit hole trying to figure out what to do to achieve this unless and until you know where to look.

There is a note in the Books Online content stating the following:

You cannot install Analysis Services to the same cluster group as the Database Engine. You must install Analysis Services to its own group and then, after installation, you can move Analysis Services to the same group as SQL Server.

However the above information is incorrect!!

You CANNOT add a failover cluster instance of Analysis Services to an existing SQL Server failover cluster instance using the Setup GUI!

Adding an Analysis Services failover cluster instance to an existing SQL Server cluster group has probably been a point of consternation for a person attempting SQL setup if you ran into the above mentioned scenario.

Now to the interesting part… achieving the objective using setup parameters and command line setup!! Sounds like fun, eh? The setup command would need to use the following parameters:

start /wait <CD or DVD Drive>\setup.exe
/qb VS=<VSName> – Virtual Server name should be the same as the existing database engine virtual server name
INSTANCENAME=<InstanceName> – Instance name should also be the same as the instance name of the database engine. For default instances, use MSSQLSERVER.
ADDNODE=<NodeName1, NodeName2,… NodeNameN>
GROUP=<SQL Diskgroup>
IP=<IP,Networkname> – Network name here is the cluster network name. See Gotcha#2 below.
ASCLUSTERGROUP=<YourDomain \ YourDomainGroupName>

There are few gotchas here.

1. If you specify a Virtual Server name other than your existing SQL Server virtual server name for the VS parameter, then you will get a failure while trying to create a new IP resource:

Error Code: -2147019839
Windows Error Text: The cluster IP address is already in use

2. If you specify incorrect parameters for the IP parameter, you could encounter a"network name not found" error. The network name value in the IP parameter is the name of the cluster network that shows up in the Failover Cluster Manager snap-in or Cluster Administrator snap-in. The network name is NOT the name of the Windows Network Interface.

3. You need an additional shared disk. If your Database Engine is using G: drive for the existing instance, you cannot use the G: drive to install the Analysis Services instance even though you are installing into the same cluster group.


SQL Server 2005 Setup Parameters

Canonical ACLs: You said wwwhhat?

A while back I had to debug the SQL Server 2008 setup code to find out why the Registry Consistency Check rule was taking an obnoxiously long time to complete on a particular server. While trying to apply SQL Server 2008 SP2, you will find that the Registry Consistency Check rule is executed. Now the setup UI didn’t show any progress for over an hour. When I checked a previous set of setup logs ,I found the following failure (from the Detail.txt file):

2011-04-17 15:06:53 Slp: d:14c36b1c9c26b2820a8\x64\FixSqlRegistryKey_x86.exe exit code: 1
2011-04-17 15:06:53 Slp: Rule ‘AclPermissionsFacet’ detection result: AreSqlAclPermissionsCanonical= False
2011-04-17 15:06:53 Slp: Evaluating rule : AclPermissionsFacet
2011-04-17 15:06:53 Slp: Rule running on machine: <machine name>
2011-04-17 15:06:53 Slp: Rule evaluation done : Failed
2011-04-17 15:06:53 Slp: Rule evaluation message: The SQL Server registry keys from a prior installation cannot be modified. To continue, see SQL Server Setup documentation about how to fix registry keys.

In another setup attempt (again from Detail.txt file), I found that the FixSqlRegistryKey_x86.exe did not log any message post the launch of the EXE:

2011-04-29 11:42:58 Slp: D:\Microsoft SQL 2008 SP2\x64\FixSqlRegistryKey_x64.exe exit code: 0
2011-04-29 11:42:58 Slp: Launching external tool: D:\Microsoft SQL 2008 SP2\x64\FixSqlRegistryKey_x86.exe
2011-04-29 11:42:58 Slp

If you capture a Process Monitor trace, you will find that the FixSqlRegistryKey_*.exe is traversing the registry keys pertaining to the SQL Server registry hive. The EXE (FixSqlRegistryKey_*.exe) looks up and fixes all non-canonical ACLs with the registry key and its child keys for the two hives below:

HKLM\Software\Microsoft\Microsoft SQL Server\

Did I just read the word Canonical?!? Yes, you did. A canonical ACL must have ACEs sorted according to the following order:

1. Access-denied on the object
2. Access-denied on a child or property
3. Access-allowed on the object
4. Access-allowed on a child or property
5. All inherited ACEs

Reference: http://blogs.msdn.com/b/patcarna/archive/2009/02/09/the-access-control-list-is-not-canonical.aspx

If SQL Setup finds ACLs in non-canonical form, then it will try and fix the ACLs for the registry key and it’s sub-keys which have the ACLs in non-canonical form. If there are permission issues encountered while traversing the registry, then this might take a longer time to error out or fail with the error message mentioned above.

You have the following option to correct the non-canonical ACLs:

1. Locate non-canonical ACLs in two hives mentioned above and fix them manually.
2. Re-run the rules
3. Check which registry key the EXE takes a long time to move forward from or encounters an access denied error using a Process Monitor trace.

Note: Always use a backing file pointed to a local disk when capturing Process Monitor traces for prolonged periods. Refer this post for more details.

Once you have fixed the non-canonical ACLs, you should be able to successfully get beyond the registry consistency rule check without having to get lunch and dinner in between!

Since I didn’t want to sit and traverse through all the registry keys manually, I wrote a Powershell script to check if the ACLs for your SQL Server registry hive and it’s sub-keys are in canonical form or not and report back the key which is not in non-canonical form.

Function fnCheckCanonical
Param ([string] $regvalue)

$reg = Get-ChildItem $regvalue -recurse
Write-Host "Checking hive : " $regvalue
foreach ($path in $reg)

$regkey = $path.ToString()
$regkey = $regkey.Replace("HKEY_LOCAL_MACHINE","HKLM:")
#Write-Host $regkey

$acl = Get-Acl -Path $regkey
if ($acl.AreAccessRulesCanonical.ToString().Equals("True"))
Write-Host "Verified key: " + $acl.Path.ToString()
Write-Host "Reg Key: " $acl.Path.ToString()
Write-Host "IsCanoncial: " $acl.AreAccessRulesCanonical.ToString()
Write-Host "Failed canonical check"



fnCheckCanonical HKLM:"Software\Microsoft\Microsoft SQL Server"
fnCheckCanonical HKLM:"Software\Microsoft\MSSQLServer"
fnCheckCanonical HKLM:"SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server"
fnCheckCanonical HKLM:"SOFTWARE\Wow6432Node\Microsoft\MSSQLServer"

ACLs don’t change into non-canonical forms just because they are having a bad day but it could happen when you use scripting tasks from .NET/VBScript/Powershell or subinacl.exe to incorrectly modify/add/remove ACLs on a registry key. Digging into the root cause of how they actually got into the state is as good as searching for a needle in all the haystacks in the world! Here is an article on how subinacl.exe could arrange ACLs incorrectly on Windows 2000: http://support.microsoft.com/kb/296865

Hope this helps you in fixing your non-canonical ACLs if you are unfortunate enough to run into this issue.

del.icio.us Tags: ,,