Install Clustered SQL Server 2008 R2 on Windows Server 2012 R2

Posted: 3/22/2018 11:49 AM

It turns out that installing a SQL Server 2008 R2 cluster on Windows Server 2012 R2 is a little more involved than point-and-click.

Install Windows Failover Cluster Support Feature

The SQL Server installer check throws an error that there are no shared disks available:

The cluster on this computer does not have a shared disk available. To continue, at least one shared disk must be available.

This is because the SQL Server 2008 R2 installer uses Failover Cluster features that have been deprecated from Windows Server 2012 R2, and which are not installed by default.

Open a Powershell prompt and run:

Get-WindowsFeature RSAT-Cluster*

You should see that the Failover Cluster Automation Server is not installed, but rather has an Install State of Available. To install it, you run the command:

Install-WindowsFeature -Name RSAT-Clustering-AutomationServer

Stage the Cluster Name in Active Directory

The Active Directory computer account that the Windows Cluster (NOT the SQL cluster) run as does not, by default, have access to create the SQL Cluster computer object in Active Directory. You should create the computer object before-hand and assign it appropriate permissions. If not, after installation when you attempt to start the SQL Server instance, you will receive a message that the Network Name is unable to be brought online.

  1. Create a Computer object with the name of the SQL cluster that you are about to install.
  2. Disable the computer object that you just created.
  3. Right-click on it, select Properties.
  4. Click the Security tab.
  5. Click Add.
  6. Change the Object Type to search to include Computers.
  7. Search for the name of the Windows Cluster. Click OK to confirm that you want to add it.
  8. Change the security for the Windows Cluster to have Full control.
  9. Click OK to save the Security changes.

Update Installer Files to Service Pack 1

The SQL Server installer will fail at the very end of the installation configuration process, right before it starts the installation. It complains about FILESTREAM access (regardless of whether you have set it up or not), that:

Windows 2003 hotfix KB937444 is not installed

This is because you need at least Service Pack 1 for SQL 2008 R2 to install it correctly. But how can you install Service Pack 1 if you can't even install the base SQL Server? The answer is: Slipstream Service Pack 1 into the installer

  1. Extract the contents of the regular SQL Server installer to the folder: C:\Installer_SQL2008R2_SP1\

  2. You'll need to download Service Pack 1 from Microsoft. Be sure to download all of the architecture packages.




  3. Extract each of the packages to a temporary folder:

    SQLServer2008R2SP1-KB2528583-IA64-ENU.exe /x:C:\Installer_SQL2008R2_SP1\SP

    SQLServer2008R2SP1-KB2528583-x64-ENU.exe /x:C:\Installer_SQL2008R2_SP1\SP

    SQLServer2008R2SP1-KB2528583-x86-ENU.exe /x:C:\Installer_SQL2008R2_SP1\SP

  4. Copy only the files (not the folders), except the Microsoft.SQL.Chainer.PackageData.dll, in C:\InstallerSQL2008R2SP1\SP\ to C:\InstallerSQL2008R2SP1\ to update the original files:

    robocopy C:\Installer_SQL2008R2_SP1\SP\x86 C:\Installer_SQL2008R2_SP1\x86 /XF Microsoft.SQL.Chainer.PackageData.dll

    robocopy C:\Installer_SQL2008R2_SP1\SP\x64 C:\Installer_SQL2008R2_SP1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll

    robocopy C:\Installer_SQL2008R2_SP1\SP\ia64 C:\Installer_SQL2008R2_SP1\ia64 /XF Microsoft.SQL.Chainer.PackageData.dll

  5. In each of the following locations, locate the DefaultSetup.INI file:




  6. Add the following line to the end of the file in each location:


  7. Run setup.exe in C:\SQLServer2008R2_SP1 and the installation should succeed.


SQL Server Cluster Shared Drive - Unable to Add Disk Storage

Posted: 3/22/2018 8:08 AM

I was recently building a SQL Server failover cluster and had an issue where I had two SAN-connected drives to be connected to two separate nodes, but only one drive would appear as available in the Windows Failover Cluster Manager to add to the Disk Storage.

The solution turns out to be a series of Powershell commands.

First, find the ID number of the disk that you're interested in:


Then you clear the cluster reservation for that disk number:

Clear-ClusterDiskReservation -Disk 2

Go back into the Failover Cluster Manager, and the disk should be available to add to the cluster.


Allow Execution of all Stored Procedures in a Schema

Posted: 3/16/2018 8:06 AM

Older versions of SQL Server did not have a role that allowed execution of all stored procedures. You can get around this by granting EXECUTE on an entire schema:

GRANT EXECUTE ON SCHEMA :: dbo TO [username]

Remove Private Key Password From PKCS12 File

Posted: 1/26/2018 7:49 PM

Per this question on Serverfault:

If you have a PFX file that contains a certificate and private key with password, and would like to separate the certificate and private key and strip out the password, you can use these steps in OpenSSL:

  • PASSWORD is your current password
  • YourPKCSFile.PFX is the file you want to convert
  • NewPKCSWithoutPassphraseFile is the target file for the PKCS12 without passphrase

Follow the steps below:

  1. Extract the certificate: openssl pkcs12 -clcerts -nokeys -in "YourPKCSFile.PFX" -out certificate.crt -password pass:PASSWORD -passin pass:PASSWORD

  2. Extract the certificate authority key: openssl pkcs12 -cacerts -nokeys -in "YourPKCSFile.PFX" -out -password pass:PASSWORD -passin pass:PASSWORD

  3. Extract the private key: openssl pkcs12 -nocerts -in "YourPKCSFile.PFX" -out private.key -password pass:PASSWORD -passin pass:PASSWORD -passout pass:TemporaryPassword

  4. Remove the passphrase: openssl rsa -in private.key -out "NewKeyFile.key" -passin pass:TemporaryPassword

You now have three separate files without a password.


Bypass FileVault 2 Login Screen

Posted: 1/5/2018 7:41 PM

By default, the FileVault 2 Login Screen comes up before the OS boots, preventing you from accessing it remotely via SSL or Screen Sharing.

Apple provides a utility that, when executed, will save your username and password in the system memory and use it one time (then clear it) to proceed past the FileVault 2 Login Screen.

Open Terminal and execute the following command. Be careful though, as it will cause an immediate reboot of the system (as though you used the shutdown command):

sudo fdesetup authrestart

After the reboot, your hard drive will be unlocked and you will be presented with the standard MacOS Login Screen (as though you do not have FileVault 2 enabled).

Not every Mac supports this command though. To find out if yours does, you can execute this command:

fdesetup supportsauthrestart

If the response is "true", then you're ready to reboot.


Encrypt an HFS+ Partition From the Terminal

Posted: 1/2/2018 7:21 PM

Use the following diskutil syntax to encrypt a partition on MacOS from the Terminal:

diskutil cs convert /Volumes/MyDrive -passphrase

You do not enter a passphrase on the command line, but rather you will be prompted for one.


Unsubscribe from Navigation Events

Posted: 12/13/2017 8:39 AM

I recently ran into a problem where an Angular Component was still responding to Navigation Events even after having navigated away from that route.

When subscribing to an Observable, be sure to store the subscription in a global variable and call the .unsubscribe() function when the component is being destroyed.

export class MyComponent implements OnInit, OnDestroy {

    constructor (private router: Router) { }

    // stores the subscription object
    navSubscription: Subscription = null;

    // called when the component is initialized
    ngOnInit() {
        this.navSubscription = any) => {
            // test if the event is an end navigation
            if (event instanceof NavigationEnd) {
                // do something here

    // called when the component is destroyed
    ngOnDestroy() {

Angular / Third Party Cookies

Posted: 11/8/2017 8:48 AM

When performing a GET or POST using Angular's HTTP service against a third-party server, returned cookies are, by default, not accepted by the browser.

To enable this, when calling your this.http.get() or, you need to add the option withCredentials and set it to true. This will allow your browser to accept any third-party cookies


Change Windows Network Connection to Private/Public

Posted: 8/20/2017 10:04 AM

Sometimes Windows will not correctly identify a network as public or private. To manually change the setting for a network connection, open a PowerShell prompt and execute:

PSH> Get-NetConnectionProfile

Name             : Network  2
InterfaceAlias   : Internet
InterfaceIndex   : 3
NetworkCategory  : Public
IPv4Connectivity : Internet
IPv6Connectivity : NoTraffic

Name             : Network
InterfaceAlias   : Local
InterfaceIndex   : 9
NetworkCategory  : Private
IPv4Connectivity : Internet
IPv6Connectivity : LocalNetwork

PSH> Set-NetConnectionProfile  -InterfaceIndex 3 -NetworkCategory Private

PSH> Set-NetConnectionProfile  -InterfaceIndex 3 -NetworkCategory Public

Join Angular Observables

Posted: 8/17/2017 4:11 PM

If you have multiple observables that you want to execute simultaneously, and be notified when they've completed, you use the Observable.forkJoin() function, passing it an array of Observables:

const observable1 = this.http.get('url1.html');
const observable2 = this.http.get('url2.html');

Observable.forkJoin([observable1, observable2]).subscribe(results => {
  const results1 = results[0]; // observable1 results
  const results2 = results[1]; // observable2 results


Add JAR to Local Maven Repository

Posted: 5/18/2017 6:48 PM

If you've obtained a JAR file that is not available in an existing Maven repository, you can add it to your local Maven repository with the following command. Make sure that all of your options are surrounded by double-quotes:

mvn install:install-file "-DgroupId=org.mozilla" "-DartifactId=jss" "-Dversion=4.2.5" "-Dpackaging=jar" "-Dfile=jss-4.2.5.jar"

Enable Hibernate SQL Logging in Grails

Posted: 5/18/2017 6:08 PM

It's often useful when debugging a Grails application to be able to see the exact SQL that is being executed in the database.

If you're using GORM, you can modify your /grails-app/conf/application.yml file with the following settings. Each Hibernate statement that is executed will be outputted to the standard console.

    logSql: true
    formatSql: true

Hibernate Error: @P0 Syntax Error

Posted: 5/18/2017 6:02 PM

Grails domain classes using GORM offer the dynamic finders Class.findByXXX() and Class.findAllByXXX(max: 123), which will retrieve 1 record and 123 records, respectively.

On SQL Server 2012 or higher, attempting to call one of these functions will throw a Hibernate error "Invalid syntax @p0". This is because you need to add an additional configuration option to your /grails-app/conf/application.yml file dataSource section:

dialect: org.hibernate.dialect.SQLServer2012Dialect

Use Environment Variables in application.yml

Posted: 5/14/2017 9:29 AM

A convenient method of configuring parameters in a Grails app application.yml file is to use system environment variables instead of hard-coding values.

The syntax for an environment variable is:


Custom-Named Grails WAR Files

Posted: 5/6/2017 2:22 PM

Gradle provides the ability to customize the name of the WAR file that is generated from the Grails war command.

Grails uses the war gradle plugin, which specifies the following pattern to create the archive name:


To customize the name of a generated WAR file, add a war {} closure to the end of the build.gradle file. To remove a property from the name (such as the version), set it to an empty string. You can also include the name of the environment that the WAR file is being generated for by retrieving the Java system property grails.env. The name of the current project is and is the default value for baseName.

String env = System.getProperty("grails.env") ?: "production"

war {
    version ""
    appendix env

Change Grails Internal Tomcat Port

Posted: 4/30/2017 11:12 AM

By default, the grails run-app command, as well as running a Grails WAR from the terminal or as a Windows service, creates an instance of Tomcat on port 8080. To change this, edit the /grails-app/conf/application.yml file and create a new server section with the port number that you want:

    port: 9001

Configure Tomcat Windows Service JVM Launch Options

Posted: 4/16/2017 12:32 PM

Launch regedit and navigate to the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Apache Software Foundation\Procrun 2.0\{TomcatInstance}\Parameters\Java\Options where {TomcatInstance} is the name of the Tomcat instance you want to configure.

Separate each JVM option on a different line:


Configure JVM Garbage Collection Logging

Posted: 4/10/2017 2:26 PM

When your application gets to be a certain size, you'll have to enter the world of Java Garbage Collection, and analyzing memory usage to better determine needs and improve performance.

Part of that analysis is enabling Garbage Collection logging, so that you can then run the log through an analysis tool like to get an idea of what's going on.

Add the following JVM options to the end of your launch string. In the example below, the file that's generated will be called gclog.txt.


Intelli-J Error: "No Groovy SDK Defined"

Posted: 3/27/2017 2:35 PM
  1. Right-click on the root of your Grails project and select "Add Framework Support".
  2. Select "Groovy".
  3. The dropdown menu next to "Use library" will allow you to choose a Grails library.
  4. If the "Use library" dropdown menu is empty, click "Create" and choose your Grails library directory. If you have installed Grails through SDKMAN, it will be located at: /Users/{username}/.sdkman/candidates/grails/current/ where {username} is your username.
  5. To remove an old version of Grails: Select File -> Project Structure -> Global Libraries. Delete the missing Grails version.

Prevent Windows 10 Remote Desktop Session Auto-Logoff

Posted: 2/14/2017 1:05 PM

By default, Windows 10 will automatically log off idle Remote Desktop sessions.

To prevent this:

  1. Run gpedit.msc.
  2. Expand Computer Configuration -> Administrative Templates -> Windows Components -> Remote Desktop Services-> Remove Desktop Session Host -> Session Time Limits.
  3. Disable all of the listed policies, regardless of their current state.
  4. Expand User Configuration -> Administrative Templates -> Windows Components -> Remote Desktop Services-> Remove Desktop Session Host -> Session Time Limits.
  5. Disable all of the listed policies, regardless of their current state.
  6. Reboot the computer.