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.

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

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."

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 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;

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)

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%'

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".

SQL 2005 64bit with Linked Server SQL 2000 Query Issues

We just upgrade our main DB from SQL 2k to SQL 2k5 64bit. But we still need to use linked server to link to another SQL 2k for running some queries. But I get the following error when trying to execute some query:

OLE DB provider "SQLNCLI" for linked server "SQL 2k Server" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0TCP Provider: An existing connection was forcibly closed by the remote host.Msg 18456, Level 14, State 1, Line 0

The solution is: When running a query, SQL 2k5 64bit is trying to invoke the sp_tables_info_rowset_64 store procedure. But this proc is not in SQL 2k server. So one wrapper is needed to put in SQL 2k server to make the query work.

Create Procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int
set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

Running SSIS package programmatically

Here I found a nice blog post for Running SSIS package programmatically. Sql Server Integration Service is still a little bit confusing. Not just like you can easily run some SSIS packages on remote DB server which most likely every DBA dreaming for that. You need to use SQL Agent or do some programming of Web Service to run it.

SQL Server 2008 Feb CTP

A few features I like in the SQL 2008:

• Database encryption
• Database backup compression
• FileStream support for big files
  

I also like the IntelliSense in the new SQL Management Studio.

Execute SSIS Package in C# Apps

Add referrence to Micosoft.SQLServer.ManagedDTS.dll. It is in your SQL Server Program Files Folder\SDK\Assemblies.

using Microsoft.SqlServer.Dts.Runtime;

namespace ExecuteSSIS
{
class Program
{
static void Main(string[] args)
{
Application app = new Application();
//
// Load package from file system
//
Package package = app.LoadPackage(@"c:\myPackage.dtsx", null);
package.ImportConfigurationFile(@"c:\myPackage.dtsConfig");
//Pass parameters
Variables vars = package.Variables;
vars["variable"].Value = "values";
DTSExecResult result = package.Execute();
Console.WriteLine("Package Execution results: {0}",result.ToString());

//
// Load package from Sql Server
//
Package package2 = app.LoadFromSqlServer(
"myPackage","server_name", "sa", "password", null);
package2.ImportConfigurationFile(@"c:\myPackage.dtsConfig");
//Pass parameters
Variables vars2 = package2.Variables;
vars2["variable"].Value = "value";
DTSExecResult result2 = package2.Execute();
Console.WriteLine("Package Execution results: {0}",
result2.ToString());
}
}
}

How to examine SQL Server's I/O Statistics

I just happened to find this old article. But it is still good.

http://www.databasejournal.com/features/mssql/article.php/10894_2244381_1

Create Data Dictionary for SQL Server Database

Recently, I need to create a data dictionary for our 12GB database. And I did a research on creating data dictionary, and found this good tool - Data Dictionary Creator. It stores all the information in Extended Properties. Here are some blog link for this cool tool:

Data Dictionary Creator - Rapid database documentation

Generate Sql Server data dictionary table