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.
  

A best explanation of REST

Recently found a blog post has a clear explanation of REST protocol “Clarifying REST”.

Mobile’s three kingdoms story…

iOS: New versions are coming out every month. I have to upgrade again…

Android: New version is out. But when can I get the upgrade…

Windows Phone: New version is finally coming. But why can’t I get the upgrade…

Protect your information online

Here is a true story about how a hacker wiped out one tech writer’s online info, his iPhone, iPad and Mac. You may have heard about it.

http://www.wired.com/gadgetlab/2012/08/apple-amazon-mat-honan-hacking/all/

Here is a useful link to protect yourself.

http://gizmodo.com/5932663/9-things-you-absolutely-must-do-to-keep-your-online-identity-secure

How to drop a database with publication

I got a database on a log shipping subscriber server which I want to drop it. But the publisher database has some active publications. SQL Server doesn't allow you to drop it directly because it thinks it has replications going on. So the way to drop it is putting the database into Offline mode first. Then you should be able to drop it. (NOTE: PLEASE ALWAYS MAKE SURE YOU HAVE A BACKUP BEFORE DROPPING ANY DATABASE!!!)

ALTER DATABASE MyDB SET OFFLINE;

Call SOAP Web Service With Basic Authorization

Recently I have to call a SOAP Web Service with basic authorization in .Net program. But there is not a simple way to configure the Service Proxy to send the authorization header in HTTP package. I did a little bit search on Internet. Found this solution. Basically, it is adding the header info when initiating the call to web service. It needs an “Authorization” header which is added to the HTTP call which contains the base64 encoded user name and password like this:

Authorization: Basic dXNlcm5hbWU6cGFzc3dvcmQ=

Following are the sample code from this post.

Firstly a method to encode the credentials:

private string EncodeBasicAuthenticationCredentials(string username, string password)
{
  //first concatenate the user name and password, separated with :
  string credentials = username + ":" + password;

  //Http uses ascii character encoding, WP7 doesn’t include
  // support for ascii encoding but it is easy enough to convert
  // since the first 128 characters of unicode are equivalent to ascii.
  // Any characters over 128 can’t be expressed in ascii so are replaced
  // by ?
  var asciiCredentials = (from c in credentials
                     select c <= 0x7f ? (byte)c : (byte)'?').ToArray();

  //finally Base64 encode the result
  return Convert.ToBase64String(asciiCredentials);
} 

Now that we have a means of encoding our credentials we just need to add them to the headers of our WCF request.  We can easily do this by wrapping the call in an OperationContextScope:

var credentials = EncodeBasicAuthenticationCredentials("username", "password");

using (OperationContextScope scope =
          new OperationContextScope(service.InnerChannel))
{
  HttpRequestMessageProperty request = new HttpRequestMessageProperty();
  request.Headers[System.Net.HttpRequestHeader.Authorization] = "Basic " + credentials;

  OperationContext.Current.OutgoingMessageProperties.Add(
                                     HttpRequestMessageProperty.Name, request);

  service.DoSomethingAsync();
}

How to make barcode font display correctly in web service

Just got an issue when using barcode font in a web service hosting in IIS7. The barcode font can't display in the document which is generated inside the web service. And the solution to make it work is setting the option "Load User Profile" of IIS 7 application pool to True.

How to check what SQL Server Trace Flags are enabled

If you use some trace flags in SQL Server, and want to find out what trace flags are enabled, just run this:

dbcc tracestatus(-1)

VS debugger 'magic names'

There is a discussion on StackOverflow about "Where to learn about VS debugger 'magic names'". It is quite useful if you are looking inside the compiler.

http://stackoverflow.com/questions/2508828/where-to-learn-about-vs-debugger-magic-names

Continue to blog...

Almost forgot I have this blog site. I decided to continue to write something again. :)

Using common table expression in SQL 2005

SQL 2005 has a new feature called Common Table Expression (CTE). You don't need to use table variable any more. It is more powerful. You can use it for recursive query, aggregation query etc.

Ex.

WITH tmp_a (col1, col2, col3)
AS
(
SELECT col1, col2, col3 FROM a WHERE a.flag = 1
)

SELECT * FROM tmp_a
WHERE tmp_a.col1 like 'aa%'

Moving to spaces.live.com

I move this blog to spaces.live.com. So see you there.

Running SSIS packages under other Users

There is one place need your very special attention when you try to deploy SSIS package, and let other users run the package. That is "Package Protection Level". By default it chooses "Encrypt sensitive data with user key". That is why you can't run it under the other users' account. Maybe you can try to use "Rely on server storage and roles for access control".