SQL Server 2014 In-memory OLTP

Now we come to the next version of SQL Server – SQL Server 2014. One of the really beauties of it is the In-memory OLTP (code name Hekaton). Can’t wait to get my hands on it once the CTP 1 is coming out. Following are the summary from this white paper about it.

In-Memory OLTP (formally known as code name “Hekaton”) is a new database engine component, fully integrated into SQL Server. It is optimized for OLTP workloads accessing memory resident data. In-Memory OLTP allows OLTP workloads to achieve remarkable improvements in performance and reduction in processing time. Tables can be declared as ‘memory optimized’ to take advantage of In-Memory OLTP’s capabilities. In-Memory OLTP tables are fully transactional and can be accessed using Transact-SQL. Transact-SQL stored procedures can be compiled into machine code for further performance improvements if all the tables referenced are In-Memory OLTP tables. The engine is designed for high concurrency and blocking is minimal.

Make the file share work on a DNS alias

A note for myself from this awesome post.

Outline

1. The Problem
2. The Solution
   • Allowing other machines to use filesharing via the DNS Alias (DisableStrictNameChecking)
   • Allowing server machine to use filesharing with itself via the DNS Alias (BackConnectionHostNames)
   • Providing browse capabilities for multiple NetBIOS names (OptionalNames)
   • Register the Kerberos service principal names (SPNs) for other Windows functions like Printing (setspn)
3. References

1. The Problem

On Windows machines, file sharing can work via the computer name, with or without full qualification, or by the IP Address. By default, however, filesharing will not work with arbitrary DNS aliases. To enable filesharing and other Windows services to work with DNS aliases, you must make registry changes as detailed below and reboot the machine.

2. The Solution

Allowing other machines to use filesharing via the DNS Alias (DisableStrictNameChecking)

This change alone will allow other machines on the network to connect to the machine using any arbitrary hostname. (However this change will not allow a machine to connect to itself via a hostname, see BackConnectionHostNames below).

• Edit the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters and add a value DisableStrictNameChecking of type DWORD set to 1.

• Edit the registry key (on 2008 R2) HKLM\SYSTEM\CurrentControlSet\Control\Print and add a value DnsOnWire of type DWORD set to 1

Allowing server machine to use filesharing with itself via the DNS Alias (BackConnectionHostNames)

This change is necessary for a DNS alias to work with filesharing from a machine to find itself. This creates the Local Security Authority host names that can be referenced in an NTLM authentication request.

To do this, follow these steps for all the nodes on the client computer:

1. To the registry subkey HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0, add new Multi-String Value BackConnectionHostNames
2. In the Value data box, type the CNAME or the DNS alias, that is used for the local shares on the computer, and then click OK.
   • Note: Type each host name on a separate line.

Providing browse capabilities for multiple NetBIOS names (OptionalNames)

Allows ability to see the network alias in the network browse list.

1. Edit the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters and add a value OptionalNames of type Multi-String
2. Add in a newline delimited list of names that should be registered under the NetBIOS browse entries
   • Names should match NetBIOS conventions (i.e. not FQDN, just hostname)

Register the Kerberos service principal names (SPNs) for other Windows functions like Printing (setspn)

NOTE: Should not need to do this for basic functions to work, documented here for completeness. We had one situation in which the DNS alias was not working because there was an old SPN record interfering, so if other steps aren't working check if there are any stray SPN records.

You must register the Kerberos service principal names (SPNs), the host name, and the fully-qualified domain name (FQDN) for all the new DNS alias (CNAME) records. If you do not do this, a Kerberos ticket request for a DNS alias (CNAME) record may fail and return the error code KDC_ERR_S_SPRINCIPAL_UNKNOWN.

To view the Kerberos SPNs for the new DNS alias records, use the Setspn command-line tool (setspn.exe). The Setspn tool is included in Windows Server 2003 Support Tools. You can install Windows Server 2003 Support Tools from the Support\Tools folder of the Windows Server 2003 startup disk.

How to use the tool to list all records for a computername:

setspn -L computername

To register the SPN for the DNS alias (CNAME) records, use the Setspn tool with the following syntax:

setspn -A host/your_ALIAS_name computername
setspn -A host/your_ALIAS_name.company.com computername

3. References

All the Microsoft references work via: http://support.microsoft.com/kb/

1. Connecting to SMB share on a Windows 2000-based computer or a Windows Server 2003-based computer may not work with an alias name
   
   
   
   • Covers the basics of making file sharing work properly with DNS alias records from other computers to the server computer.
     
   • KB281308
   
   
2. Error message when you try to access a server locally by using its FQDN or its CNAME alias after you install Windows Server 2003 Service Pack 1: "Access denied" or "No network provider accepted the given network path"
   
   
   
   • Covers how to make the DNS alias work with file sharing from the file server itself.
     
   • KB926642
   
   
3. How to consolidate print servers by using DNS alias (CNAME) records in Windows Server 2003 and in Windows 2000 Server
   
   
   
   • Covers more complex scenarios in which records in Active Directory may need to be updated for certain services to work properly and for browsing for such services to work properly, how to register the Kerberos service principal names (SPNs).
     
   • KB870911
   
   
4. Distributed File System update to support consolidation roots in Windows Server 2003
   
   
   
   • Covers even more complex scenarios with DFS (discusses OptionalNames).
     
   • KB829885

Minimum permissions for MS SQL Server Service Account

Just a note for myself from this post. Minimum permissions for a MS SQL Server service account are:

• Log on as a service (SeServiceLogonRight)
• Replace a process-level token (SeAssignPrimaryTokenPrivilege)
• Bypass traverse checking (SeChangeNotifyPrivilege)
• Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
• Permission to start SQL Server Active Directory Helper
• Permission to start SQL Writer
• Permission to read the Event Log service
• Permission to read the Remote Procedure Call service

JavaScript code for get a string MM/dd/yyyy from a Date

In JavaScript, there is no easy way to get a string as “MM/dd/yyyy” from a Date type. Here is little helper function for it:

   1: function getDateString(dateInput) {

   2:     var dDate = new Date(dateInput);

   3:     var sDay = dDate.getDate();

   4:     var sMonth = dDate.getMonth()+1;

   5:     var sYear = dDate.getYear();

   6:     var sDate = sMonth+'/'+sDay+'/'+sYear;

   7:     return sDate;

   8:  

   9: }

Use Powershell Script to Check Windows Service Services’ States

I have a bunch of windows servers running SQL servers. Want to make sure all windows services which are set to “Auto Start” are not stopped. Here is the powershell script to check them assuming we have all the server names in file DBServers.txt.

   1: $dbservers = get-content ".\DBServers.txt"

   2:  

   3: foreach ($Hostname in $dbservers) 

   4: {

   5:     Write-Host "Checking on server: $Hostname"

   6:  

   7:     $Services=get-wmiobject -class win32_service -computername $Hostname `

   8:             | where {$_.StartMode -eq "Auto" -and $_.Started -eq $false } `

   9:             | select-object Name,state,status,Started,StartMode,Startname,DisplayName

  10:  

  11:     foreach ( $service in $Services)

  12:     {

  13:         $message="[$Hostname] " +$Service.Name + " (" + $service.DisplayName + ") " +$Service.state +" " `

  14:                 +$Service.status +" " +$Service.Started +" " +$Service.Startname 

  15:         write-host $message -background "RED" -foreground "BLACK"     

  16:     }

  17: }

  18:  

  19: Write-Host "All checkings are done."

Goodbye, Google Reader

One of my favorite Google tools - Google Reader will be closed soon. Google Note was gone. Now comes powering down Google Reader. I have to find another tool to keep track of wonderful IT news...

Token-based server access validation failed with an infrastructure error.

Recently I went in a newly built MS SQL Server 2008 R2 instance on Windows Server 2008 R2 server, I got login failure when trying to connect to SQL Server instance through SQL Server Management Studio (SSMS) using windows authentication. And I have added my account as SA in the SQL instance. I checked the error message detail. It was:

Error: 18456, Severity: 14, State: 11.

Login failed for user 'Domain\myuser'. Reason: Token-based server access validation failed with an infrastructure error.

This is the first time I got this error since I didn’t run any SQL server on Windows Server 2008 R2 before. After a little bit research, it was caused by UAC (User Access Control). I ran SSMS with option “Run as Administrator”. And I was able to login to SQL Server successfully. Here is a blog post explaining very clear about it.

How to enable new resolutions on Lenovo S10-3t

The best resolution for S10-3t is 1024x600. But actually it can support better resolution than that. Here is the trick to edit the registry to get new screen resolutions:

• Run "regedit.exe"
• Go to 
• HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\
  Control\Class\{4D36E968-E325-11CE-BFC1-08002BE10318}\0000
  
• Find "Display1_DownScalingSupported" and open it.
• Change the value to 1
• Close the registry and restart your system.