SQL Server backups to Alternate Data Streams or Colons

So in the midst of a very busy day I performed a manual backup of a database for a developer so he could make some major changes, test, and rollback if needed. I entered the backup file name with a colon on the timestamp…DUH! Of course, this worked, as NTFS supports the use of Colons in the filename. Went back later to restore the backup for the developer and the filename was truncated at the colon and was 0 bytes in size. WTF, the backup worked, there was no warning or error from SQL Server? Then I remembered my old NTFS “friend” – Alternate Data Streams. Basically ADS is a way to put data into different streams of the file. If you’ve ever wondered how Windows knows to warn you when you run an executable downloaded from the internet via IE, this is how. IE places a “zone.identifier” in the ADS to let Windows know this file might not be safe.

There’s a couple of ways to get around this and recover the backup regardless of the truncated filename and the size of 0. The quickest and easiet way is to just restore the database or log from TSQL. So if you backup a database with

BACKUP DATABASE test TO DISK='test_11:30.bak'

That will work fine. In your default backup directory, you’ll see a file “test_11” and it will be 0 bytes in size. If you then try to use the SSMS GUI to restore this, it will fail.

If you instead use TSQL…

RESTORE DATABASE test FROM DISK='test_11:30.bak'

it will work.

The colon tells the OS to create a file with aname of everything before the colon, and all the data into an ADS with an idetifier of everything after the colon… so in our example, test_11 has an ADS in it with an identity of :30.bak. The backup data is all there in that stream.

So now you’re saying “Well, what if I don’t know the stream identity”! If that’s the case there are a number of tools that can tell you all the ADS identities in a file… I use STREAMS from that Sysinternals genius, Mark Russinovich. If will spit out the ADS in the file you give it. I’ve also used notepad and a Windows port of the *nix “cat” command to pull the data out of that ADS and into a new file. That new file would then be able to be restored from with the SSMS GUI. With Notepad, just open a command prompt and type NOTEPAD test_11:30.bak and give it some time, and it will have all that data in Notepad. Save that as test.bak and you can restore anyway you want.

Denali Always On adventures

I’ve built a new AD controller VM and two SQL server VM’s with Windows Server 2008 R2 EE. Joined both SQL servers to the domain. Installed the Failover Cluster feature on each. Installed Denali RC0. Enabled AlwaysOn High Availability in the SQL Server Configuration Manager.

Then I created a FayWorks database, and a new Availability Group. I set up the primary and then a replica / read only / preferred backup. Then I set up a Availability Group listener. I connected with SSMS to the AG via the listener, started a script that inserted 5000 rows into a temp table in RBAR fashion. Initiated a failover. The insert failed at some point, but I was able to restart the insert without reconnecting or changing anything after the failover completed. Slick as a pan covered in bacon grease.

This combines the best of HA Failover Clustering with the best of Mirroring / Log Shipping / Etc. Being able to geographically set up an Availibility group, having the mirror be targeted for backups, reporting, etc, having up to 4 replicas, completing a failover of just an AG, creating a virtual instance name / IP. Oh yeah, Denali is a game changer.

Some links I have used

http://msdn.microsoft.com/en-us/library/hh213080(v=sql.110).aspx
http://msdn.microsoft.com/en-us/library/hh213417(v=sql.110).aspx

How to Set Up SQL Server 2012 Always On Availability Groups

There seem to be a lot of questions and even some misinformation popping up with regards to SQL Server 2012 licensing. Microsoft is moving away from licenses based on the number of processor in a server to figure out per/cpu licensing. They are now licensing hardware on a per/core license.

Right or wrong, agree or disagree, here’s the details as I know them. Based on a number of sources, including a meeting with my employers VAR and an internal MS licensing expert.

Per core licensing is based on “Core-Packs”. Each core-pack covers two cores, and there is a minimum purchase of two core-packs. This will be an expensive premium if one plans on building a single processor dual core machine, and you’re paying to license a minimum of four cores.

I’ve been told that the core-packs cost 50% less than current per/cpu licenses. That makes the magic number a total of 4 cores with 2012 licensing costing the same a single processor license with current license costs.

I’ve read that with 2012 licesning – in the case of virtualization if you license all the physical cores you have unlimited rights to virtual OS’s (vOS). Current day licensing with Enterprise Edition would only allow a total of 4 vOS’s per license.

Also, current customers with an EA will retain their current purchasing plan until the expiration of the EA, regardless of when that is.. if it’s Jan 1 of 2013, then all of 2012, you continue to purchase the licenses as you have. Once the EA is up, you will have to submit the number of cores in your currently licensed environment and MS will “trade” those for the equivalent number of Core-Packs. I’ve heard conflicting reports of a hard limit of 20 cores or 10 Core-Packs per server, and other reports that do not mention that limit.

SQL Server, Kerberos, SPN

So having a lot of fun recently here at work. First some background information.

We have two domains, an internal domain, and an external domain. We’ll call them JASONINC.com and JASONEXT.com. We have a one way trust between them that says JASONEXT.com trusts anything from JASONINC.com. I have SQL Servers running in both domains, and Kerberos has worked flawlessly to allow JASONINC.com users to connect to the JASONEXT.com SQL Server regardless of the Service Account used to run SQL Server. In JASONEXT.com I have some SQL Servers running under Local Service, some runing under a JASONEXT service account, and some even running under a JASONINC service account.

Suddenly on Monday morning, for any SQL Server in JASONEXT I was receiving an “Cannot generate SSPI context” error from Management Studio. It’s very transient, at my office location, I could not connect to any JASONEXT box via the short name, FQDN via Kerberos. If I specified the IP address in the connection, it worked, because it would fall back to NTLM authentication.

Looking at the ERRORLOG on these SQL Servers, the ones not running under the JASONEXT service credentials regestered their SPNs without issue. The ones running under Local Service or JASONEXT accounts would not register their SPNs with an error 0xd state 13.

What has seemed to be working is manually setting the SPNs via SETSPN -A mssqlsvc/hostname:port Service Account on the JASONCOMINC domain.

However, there’s still something funny happening. I have one SQL Server running under Network Service, it was restarted 2 months ago, the log indicates that the SPNs were registered properly, but I still get “Cannot generate SSPI context”.

Netmon traces are weird… I see the kerberos call from my client to the primary DC in the location where the SQL Server is located, with the SPN in the request. I see a response from the DC saying contact krbtgt/root DC. That’s the end of the Kerberos traffic…. I never see the client then call to the root DC asking for the SQL Server SPN ticket.

On ones where it is working now via a manual SETSPN, I see much more, I see the call to the primary DC where the SQL Server is located, I see the response with krgtgt/root DC. I see the client call to the root DC, I see the response from the root with krbtgt/root JASONEXT DC. I see the client call out to the root JASONEXT DC, I see a response from that… but the response is 0x1f KRB_AP_ERR_BAD_INTEGRITY but it connects. I did not check to see if it failed back to NTLM or if it was connected via Kerberos.

This morning, from one client, I am getting 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN, but connecting, and I verified it fell back to NTLM to connect. I’m lost with all the different things happening at different times from different hosts and clients. Thankfully we don’t use domain service accounts for our applications other than Sharepoint, and thankfully for Sharepoint, we don’t try to cross the domains. The problem comes when developers are trying to connect into the JASONEXT.com domain. The work around is to have them connect via a SQL Login.

Anyone have any ideas or other troubleshooting tips? I’m going to sit in our AD admin’s cube this morning and have him prove out that our trust is correct and working between the domains. I want to get MS involved with the odd error I see in the SQL Server ERRORLOG for failing to register the SPN, but most of those errors are from months ago, and up until Monday everything was working.

FUN SQL Server Publication error

Transactional Replication from single publisher to three remote subscribers. Right click on Publication at Publisher, and click properties. Try to select another page other than the General page which shows by default pops up with an error …. “The value must be greater than or equal to -1 and less than or equal to -1”. I think that means it HAS to be -1… default value is 0.

LOVE IT

Jason’s Kickin Cornbread Stuffing

This is a really tasty Cornbread based stuffing with Andouille sausage, dried cranberries, and walnuts.

2 loafs of cornbread
1 lb andouille sausage
2 cups Craisins or dried cranberries
1 cup chopped walnuts
2 cups low sodium chicken or turkey stock
1 medium onion
4 celery stalks
Tbsp chopped garlic
tsp Pepper
Pinch Salt

Cut cornbread into 1″ cubes. Dry in 200 degree oven for 1-1.5 hours (or let sit out for a day or two). Chop andouille sausage into bite size pieces. Chop onion and celery.

Preheat oven to 350
Place onion and celery in medium sauce pan with tblspoon of oil. Cook over medium high heat until tender. Reduce heat to medium and add stock until boiling. Reduce heat to simmer, add garlic, salt and pepper. Simmer 10 minutes. Add Craisins or dried cranberry. Simmer 5 minutes.

Place cornbread into 13×9″ pan. Add andouille sausage and walnuts. Pour stock & vegetables over top. Mix gently.

Bake for 1 hour. Remove, stir gently, if still soggy in the center put back in over for another 30 minutes.

I used spicy andouille sausage and the kick from the sausage was a great balance for the cornbread and craisins.

Powershell scripts to document SQL Servers

Starting at a new company as their first really dedicated SQL Server DBA, I inherited over 20 production SQL Servers. These systems had varying levels of documentation from little to none, so I’ve had a painful time of it just getting up to speed on the current environment. I’ve read a number of articles from the masters, including Brent Ozar. I’ve used his SQL Blitz scripts, and his new sp_blitz procedure to gain knowledge of existing systems. Building on that, since I’m as lazy as any other good DBA, I’ve created – and by created, I really mean that I modified – some existing powershell scripts to gather information about servers or SQL Server and dump that information into Excel. I plan on running these scripts on a monthly basis to keep them current. I’ll add to them, modify them, and create new ones as I see fit. My real thoughts are that I can use this information in the event of a disaster, and rebuild a server from backups without having to memorize information such as logins, data file locations, sql server configuration options, etc.

This is the master post, and here are the links to each of the posts that I have created so far.

The original script which started all my work. Database properties
SQL Logins
SQL Server configuration settings
Database file settings
!!!Updated OS / Hardware level settings!!!
OS / hardware level settings

Powershell to capture OS / hardware level settings

This is part 4 of a multipart posting on some PowerShell scripts that I’ve found and made some modifications to. These scripts I have used to document my environment without having to manually type information in to the spreadsheets. The master which links to all the scripts is here.

— Update, I have updated this powershell script with a much easier to read and spits out a CSV that can be opened right in Excel without any manipulation. —

The new version of this script is now here

This script gathers information about the server that SQL Server is running on. This one is unique from the rest, as it connects not to SQL Server at all, but rather to WMI to run queries to capture things like the number of CPUs and Cores (think SQL Server 2012 licensing!), memory configuration, OS, IP’s and other network info. This script also expects a text file with a list of servers, I have a file called PhysicalServers.txt that has a list of hostnames. I redirect the output of this script to a csv file, and then import that data into a excel spreadsheet. This one has only a single worksheet, one row for each server in the text file.

The base of this script came from Jesse Hamrick and his awesome site of Powershell Knowledge. Update 5/20/2016 – it seems the site I originally referenced is compromised now with malware. I have removed the link to the website.

 #* Connect to computer
 


 $serverList = "PhysicalServers.txt"
 foreach ($server in (Get-Content $serverList)) 
 {   
    $strComputer = $server
 
    #  "SysInfo Information"
    $myOutput = $strComputer + ","
    $colItems = Get-WmiObject Win32_ComputerSystem -Namespace "root\CIMV2" `
     -ComputerName $strComputer
     

     
    foreach($objItem in $colItems) {
      $myOutput += [string]$objItem.Manufacturer + ","
      $myOutput += [string]$objItem.Model + ","
      $myOutput += [string]$objItem.TotalPhysicalMemory + " bytes,"
     }
     
     
    #  "System BIOS Information"
    $colItems = Get-WmiObject Win32_BIOS -Namespace "root\CIMV2" -computername $strComputer
     foreach($objItem in $colItems) {
      $myOutput += [string]$objItem.Description + ","
      $myOutput += [string]$objItem.SMBIOSBIOSVersion + "." + $objItem.SMBIOSMajorVersion + "." + $objItem.SMBIOSMinorVersion + ","
      $myOutput += [string]$objItem.SerialNumber + ","
     }
     
    #  "Operating System Information"
    $colItems = Get-WmiObject Win32_OperatingSystem -Namespace "root\CIMV2"`
     -Computername $strComputer
     
    foreach($objItem in $colItems) {
        $subOSName = $objItem.Name.IndexOf("|") -1
      $myOutput += $objItem.Name.Substring(0,$subOSName) + ","
      $myOutput += [string]$objItem.OSArchitecture + ","
     }
     
     # "Processor Information"
    $colItems = Get-WmiObject Win32_Processor -Namespace "root\CIMV2" `
     -Computername $strComputer
     
     $count=0
     $temp=""
     
    foreach($objItem in $colItems) {
      $count++
      $temp = $objItem.Name + "," + $count + "," + $objItem.NumberOfCores
     }
     
     $myOutput += [string]$temp + ","
    
    # "Memory Information"
    $colItems = Get-WmiObject Win32_PhysicalMemory -Namespace "root\CIMV2" `
     -ComputerName $strComputer 
     $iter=1
    foreach($objItem in $colItems) {
      $iter++
      $myOutput += [string]$objItem.Capacity + ","
      $myOutput += [string]$objItem.BankLabel + ","
     }
     
     # Fill out the remaining memory slots with blanks (in my environment, the biggest server I have has 12 slots, so that's the max)
     while($iter -lt 13)
     {
        $myOutput += [string]",,"
        $iter++
     }
         
    #  "Network Information"
    $colItems = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" `
     -ComputerName $strComputer | where{$_.IPEnabled -eq "True"}
     
    foreach($objItem in $colItems) {
      $myOutput += [string]$objItem.DHCPEnabled + ","
      $myOutput += [string]$objItem.IPAddress + ","
      $myOutput += [string]$objItem.IPSubnet + ","
      $myOutput += [string]$objItem.DefaultIPGateway + ","
      $myOutput += [string]$objItem.MACAddress
     }
         
$myOutput    
    
}

Powershell to document Database file settings

This is part 3 of a multipart posting on some PowerShell scripts that I’ve found and made some modifications to. These scripts I have used to document my environment without having to manually type information in to the spreadsheets. The master which links to all the scripts is here.

This script gathers the data and log file settings for each database. It also expects a flat file named Servers.txt with a list of SQL Servers to connect to. It will connect via SSPI, so make sure you run it with a Windows account with the appropriate permissions on each SQL Server.

#change these settings to your environment
$Filename='DatabaseFiles'
#these are actually User ODBC DSNs DSNs. Better for Excel.

$serverList = "Servers.txt"

# constants.
$xlCenter=-4108
$xlTop=-4160
$xlOpenXMLWorkbook=[int]51
# and we put the queries in here
$SQL=@"
SELECT DB_NAME(mf.[database_id])AS [Database Name], 
        suser_sname(d.owner_sid) AS [Owner], 
		mf.STATE_DESC AS [Status],
       mf.[file_id] AS [File ID], 
       mf.name AS [File Name], 
       mf.physical_name AS [Physical Location], 
       mf.type_desc AS [File Type],
       CONVERT( bigint, mf.size/128.0) AS [Total Size in MB]
FROM sys.master_files mf
inner join sys.databases d
on d.database_id = mf.database_id
ORDER BY DB_NAME(mf.[database_id]);
"@

# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
#if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
#  {
#  New-Item "$DirectoryToSaveTo" -type directory | out-null
#  }
$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in (Get-Content $serverList)) 
	{ #only create the worksheet if necessary
	if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
	else  {$ws = $wb.Worksheets.Add()} #add if it doesn't exist
	$currentWorksheet += 1 #keep a tally
	#if  ($server.version -eq 2000) {$SQL=$SQL2000} #get the right SQL Script
	#if  ($server.version -eq 2005) {$SQL=$SQL2005} 
	#if  ($server.version -eq 2008) {$SQL=$SQL2008}
	$currentName=$server.Replace("\","_") # and name the worksheet
	$ws.name=$currentName # so it appears in the tab 
	# note we create the query so that the user can run it to refresh it
    
    $myconnectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=$server"  
    $adOpenStatic = 3 
    $adLockOptimistic = 3  
    
    $objConnection = New-Object -comobject ADODB.Connection 
    $objRecordset = New-Object -comobject ADODB.Recordset  
    
    $objRecordset.CursorLocation = 3
    
    $objConnection.Open($myconnectionstring) 
    $objRecordset.Open($SQL,$objConnection,$adOpenStatic,$adLockOptimistic)  
    
    $objRecordset.MoveFirst()
    
    $qt = $ws.QueryTables.Add($objRecordset,$ws.Range("A1"))
	# and execute it
	if ($qt.Refresh())#if the routine works OK
		{
	              $ws.Activate()
		$ws.Select()
		$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
		$excel.Rows.Item(1).VerticalAlignment = $xlTop
		$excel.Rows.Item(1).Orientation = -90
		#$excel.Columns.Item("F:AH").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
		#$excel.Columns.Item("F:AH").Font.Name = "Wingdings" 
		#$excel.Columns.Item("F:AH").Font.Size = 12
		$excel.Rows.Item("1:1").Font.Name = "Calibri"
		$excel.Rows.Item("1:1").Font.Size = 9
		$excel.Rows.Item("1:1").Font.Bold = $true
		$Excel.Columns.Item(1).Font.Bold = $true
        $Excel.Columns.Item("A:H").Font.Size = 9
        [void] $Excel.Columns.AutoFit()
		}

	}
$filename=$filename -replace  '[\\\/\:\.]',' ' #remove characters that can cause problems
$filename = "$filename.xlsx" #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$objRecordset.Close() 
$objConnection.Close() 
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev's Excel trick 'Pro Windows Powershell' p380
[GC]::Collect()