Category Archives: SQLServerPedia Syndication

I Am Surprised Every Time

Who me?

Yep me. I have been honored with the Microsoft Most Valuable Professional again this year in SQL Server. This is my third year to receive the award and just like the last two years I am humbled and honored to be a recipient.

Remember, What It Is.

And what it isn’t. I didn’t get my MCM before the shutdown. I didn’t earn that title and certification. I was given an honor for the service I performed to the community last year.

Others are worthy.

Maybe more so. I personally know people that deserve to be awarded. I have and will continue to nominate those people, even it it means next year I get bumped off the island so they can get on. It’s not about the award. Its about what I do to earn it.

Big Things Coming This Year.

I’ve been heads down working on a couple of projects the last few months and I can’t wait to share them with the community!

 

SQL Server, Pricing, Editions and Features

I Want Features Not Cost!

Recently Brent Ozar (blog|twitter) wrote a post SQL Server Standard Edition Sucks, And It’s All Your Fault. Needless to say, he isn’t happy with the price and what you don’t get in Standard Edition of SQL Server 2012. Brent seems to be mostly upset that since SQL Server 2008 Standard edition has been restricted to 64GB of ram. Adding additional insult, Always On is an Enterprise only feature with Database Mirroring on the green mile Microsoft will have to do something in the near future to fill this gap.

What about the rest of his list?

  • Database snapshots (a huge lifesaver when doing deployments)
  • Online reindexing, parallel index operations (wouldn’t you like to use more than one core?)
  • Transparent database encryption (because only enterprises store personally identifiable data or sell stuff online, right?)
  • Auditing (guess only enterprises need compliance)
  • Tons of BI features (because hey, your small business doesn’t have intelligence)

Yep, it sure does suck not to get any of those features and get stuck with a nice price increase.

But, Microsoft Says The Increase Isn’t That Bad(tm)

Imagine you are going from Enterprise per server to Enterprise per core on a 64 core system. At $7,000.00 a core that’s $448,000.00 bucks. If you were on SQL Server 2008 R2 Enterprise server licensing you paid just $13,969.00 and got it with 25 CALs. That is a 3100% increase in cost if you want to move to SQL Server 2012. Now that is something to bitch about. I can buy an HP DL580 with four 10-core processors and 128GB of ram for $24,259 retail, then pay $280,000.00 to license it. The days of hardware being a significant part of your IT deployments are gone. Hardware is getting cheaper and more powerful while software is getting more expensive. It’s almost an exact inverse equation.

So, What Are My Options

Well, few unfortunately. To license Oracle with the same features as SQL Server 2012 Enterprise you would be looking at 40% or more in licensing fees. On the open source front things aren’t much better. There just isn’t a single RDBMS with all the features of SQL Server or any other commercial offering for that matter.

Lets take a look at our options out of the box:

PostgreSQL

  • Can use maximum server memory – Yes
  • Database snapshots – Nope
  • Online reindexing, parallel index operations – Yes/Nope You can do an online operation but not parallel.
  • Transparent database encryption – Nope
  • Auditing – Nope
  • Tons of BI features – Nope

MySQL/MariaDB

  • Can use maximum server memory – Yes
  • Database snapshots – Nope
  • Online reindexing, parallel index operations – Nope
  • Transparent database encryption – Nope
  • Auditing – Nope
  • Tons of BI features – Nope

Now What?

Heck the only open source database that has a parallel query execution core is written in Java. On the plus side HyperSQL runs on Windows. All of these offerings also have things that SQL Server doesn’t. PostgreSQL is incredibly powerful for GIS and its unique among database engines for the large amount of indexing options available. MySQL/MariaDB has a nice feature that allows you to write your own storage engine and let MySQL act as the query processor.

If you don’t need all the feature add-on’s like Reporting Services, Integration Services or Analysis services for SQL Server then I recommend PostgreSQL every time. If you don’t mind doing some grunt work you can assemble a BI stack, reporting stack and all the other wiz-bang stuff you get out of the box with SQL Server to some degree.

If you want SQL Server’s set of tools AND its outstanding query engine you WILL have to pay for it. If you want to use SQL Sever and aren’t worried about new features, support or up to date tools you can always stick with SQL Server 2005 Standard Edition 64 bit, since it doesn’t cap memory usage and allows you to use all the memory the OS has available.

Well I’m Still Unhappy.

Yes the new licensing was a kick in the seat of the pants. Ask the Oracle guys how well they took it when Oracle did the same thing in late 2005. An article on CRN reads like it was written about SQL Server 2012 in the last month. Read it and replace “Oracle” with “SQL Server” its a hoot.

“Everyone is upset about it,” said Joe Vaught, COO of solution provider PCPC.

We sure are.

Oracle declined to comment for this story, beyond sending a short statement to CRN, which read in part: “As platforms change, and market opportunities arise, we continue to examine our pricing structure.”

Can Oracle sue Microsoft for stealing their statement? I don’t know but it sure does look familiar. I can also tell you that Microsoft used Oracle’s licensing changes to gobble up the lower end of the enterprise space and dominate the SMB space all together. Our problem is there isn’t another major up and coming software company gunning for the middle and not the top of the RDBMS business. PostgreSQL is making great strides and is my favorite front runner. They are also looking at the parallel query issue too! If you can hold out for another year or two you may be able to live without some of the bells and whistles and PostgreSQL or some other platform will be good enough to meet your needs.

Building Flat File Connectors Dynamically For SSIS

Building Connectors Is Crap in SSIS – Updated

What else can I say. I finally broke when I had to build a flat file connector with 258 columns that needed to be imported into a staging database. 258 columns… I almost had a stroke. Not only is it mind numbing it’s also error prone. I do dabble in c# so I build a little tool to automate this for me.

Introducing SSISConnectionBuilder

SSISConnectionBuilder is a simple command line tool to ease the burden of building flat file connectors for SSIS. It is WAY WAY alpha but I am working on cleaning up the code. You can pick it up here on codeplex.

I have only tested this on Windows 8/7 64 bit. If you get an error about cannot find DLL you need to install the client development stuff from the SQL Server installer.

You need to generate an excel spreadsheet with four columns. Column,Type,Precision,Scale.

The program loops through the sheet and kicks out an SSIS dtsx file with a single flat file connector defined.

You can choose a delimiter, package name and set the csv file name for the connector. The csv file name doesn’t have to be valid. If you know the csv file will be unicode you need to pass the -u or you will have errors with your connector with the error column being ntext instead of text.
Command line options:
-s, –schemafile =VALUE Your excel schema definition file.
-d, –delimiter=VALUE The column separator you wish to use, usually a comma or pipe.
-p, –packagename=VALUE Name of the dtsx file that will have your connection in.
-c, –csvfilename=VALUE Name of the csv file that your connection will use.
-u, –unicode csv file is Unicode.
-?, -h, –help show this message and exit

Update 7/23/2013

Removed the dependencies on the SSIS SDK the current release doesn’t require any external dll’s to run!

Moving Files with Open Source Software, cURL and winSCP

Open Source, ON WINDOWS!

I’m still stunned in this day and age that people pay obscene amounts of money on tools that are easily replaced in most cases with quality and stable open source alternatives. Part of the problem is that a lot of OSS tools don’t work well on Windows or require some fiddling which can scare off some people. The two tools I’ll be chatting about today don’t fall under that category. In one or two steps you have them installed and ready to rock. I’m constantly working with clients that require file movement in a secure manor. Ether upload or download.

cURL, World Wide Web Command Line

curl is a command line tool for transferring data with URL syntax, supporting DICT, FILE, FTP, FTPS, Gopher, HTTP, HTTPS, IMAP, IMAPS, LDAP, LDAPS, POP3, POP3S, RTMP, RTSP, SCP, SFTP, SMTP, SMTPS, Telnet and TFTP. curl supports SSL certificates, HTTP POST, HTTP PUT, FTP uploading, HTTP form based upload, proxies, cookies, user+password authentication (Basic, Digest, NTLM, Negotiate, kerberos…), file transfer resume, proxy tunneling and a busload of other useful tricks.

Holy cow, that’s a ton of stuff! I use cURL mostly for HTTP and HTTPS stuff. It comes as a command line tool and a library. To get all you can out of cURL you need to install a couple of bits on Windows. First of all our favorite redistributes in the form of vcredist_x86.exe. cURL website has a link to “The ones that work” but I ended up just using the latest from Microsoft.

Secondly, if you want the S part of HTTP you need to install the Win32 OpenSSL libraries. Once that is done you will need to go do some reading. cURL is very powerful and has very good documentation.

Here is an example of downloading a file from a secure HTTPS server.

curl https://<pass>:<user>@<mysecuresite.xxx>:443/ -o transfer.log -k -l -c CSX https://<mysecuresite.xxx>:443/<myfile.csv> -o “E:\<localfolder>\<myfile.csv>” -s -S

Ow, that’s a mouth full. Lets break it down.

curl – the command line tool

https://<pass>:<user>@<mysecuresite.xxx>:443/ – the secure website we are talking to

-o transfer.log – a log of what the server returns, in this case it just sends a “login successful” message

-k -This allows us to accept self-signed certificates I use this if we are dealing with site to site transfers inside the company

-l – This tells cURL to follow 3xx http redirects

-c CSX – This tells cURL to setup a cookie store some sites need them some don’t.

https://<mysecuresite.xxx>:443/<myfile.csv> – The path and name of the file we want to grab

-o “E:\<localfolder>\<myfile.csv>” – If you don’t tell cURL to save it to a file it will just echo it at the command prompt.

-s – This tells cURL to be silent. You can omit this and add the -v for verbose when you are troubleshooting issues.

-S – This tells cURL to NOT be silent on errors. If you are automating things trapping the error is important.

BAM!

Now I can grab the files I need secured via SSL over HTTP. Script them via batch file and call them from an SSIS package or SQL Server Agent job step.

WinSCP, Softer Than PuTTY

WinSCP is another tool for transferring files built specifically for Windows. If you wanted to do SFTP, SCP or FTPS you would usually fall back to using PuTTY’s psftp.exe command line tool. It does allow for robust automation but I’ve always had a headache dealing with importing SSH keys. Since the scripts will be executed under the service account that SQL Server is running under I would always have to log into the server as the service account, execute the script and accept the key. Not a huge deal just a PITA since the key is stored in the registry of he locally logged in profile. WinSCP allows you to add the SHA key in the script bypassing all that hassle.

Lets look at an example batch operation.

I’m calling this from a batch file

winscp.com /script=e:\<batchdir>\<wsftpcommands.txt> -resumesupport /parameter “%1”

winscp.com – This is the command line executable

/script – This is the command script we want to execute

-resumesupport – This bit of awesomeness will allow restarting of failed downloads so, if you have a large file you can resume instead of starting over if it fails

/parameter “%1” -This passes a single parameter to the command script. So, I’d call winscp.bat “<filename>” and it will pass the file name into the command script.

wsftpcommands.txt internals.

# Automatically abort script on errors
option batch abort
# Disable overwrite confirmations that conflict with the previous
option confirm off
open sftp://<user>:<pass>@<ftp.mysecureftpserver.xxx> -hostkey=”ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx”
# Change remote directory
cd /<mydir>/<mysubdir>
# Force binary mode transfer
option transfer binary
get “%1%” “<mydir>\<mysubdir>\%1%”
# Disconnect
close
# Exit WinSCP
exit

Poof!

I’ve now got a bulletproof way to download files securely AND have the ability to restart failed downloads. Again, I can’t state how huge this is. I’ve got a backup file coming from France nightly and it is 1 terabyte. If it fails say at 999 gigabytes and I had to restart from the beginning it would be a real bummer, oh and miss our restore window completely.

Don’t Be Afraid To OSS

With these two tools I made these download processes automated and reliable for the client replacing a tool that costs thousands of dollars a year, and wasn’t as reliable as two command line tools and a batch file.

Sharing

Do you use OSS tools like these? I’d love to hear about them and if you can provide an example on how you use them and I’ll add them to this post!

Finding SQL Server Installs with Powershell, Again

But the SQL Listener isn’t started!

Yep, I heard you loud and clear. There is more than one way to find SQL Server on your network. This time we will dig deep into the TDS protocol docs again along with a bit of guess work.

Old Dog New Protocol Plus PowerShell

Last time we used PowerShell and the .net framework to assemble a UDP packet and send it to port 1434. This works out just fine if you have the SQL Browser running. If you are running a single standalone instance on the default port the browser service isn’t needed and may not even be enabled. This time we will be using TCP instead of UDP. This gets us around the browser service issue but also limits the information we can get back. As we dig through the documentation we can send what is known as a PRELOGIN packet. The server will respond with a few bits of information including the version of SQL Server that you are trying to log into. My problem is a simple one. I’m really not sure I’m decoding the return packet correctly. The only thing the docs say about it is it is a valid PRELOGIN response. It isn’t a complete LOGINACK packet, I’m not sure what it is. As soon as I know I’ll update this post. UPDATE: It is a PRELOGIN packet with a 0x04 header type and should conform to the PRELOGIN packet specification. What it does do though is prove if SQL Server is listening on a specific TCP port. As far as I can tell the version I return should be accurate for SQL Server 2005 and above. UPDATE: I have tested this on SQL Server 7.0 through SQL Server 2012 and it works for SQL Server 2000 and above. The version number is correct if the version number minor is above 255. I’m still working to decode the minor version number correctly. This module allows you to pass in a server name and any TCP port to check for SQL Server listening so even if someone has moved from the default of 1433 you can still scan other ports at your leisure  I would warn you that scanning every port on a machine will get you a visit from your network security folks. If you plan on doing a security sweep you should get everyone involved up front. Trust me on this one, they don’t think it is funny when you scan every IP and port on the network looking for SQL Server installs!

UPDATE: Thanks to Kendal Van Dyke(blog|twitter) a real PoSH guru AND SQL Server guy I’ve renamed the function following the PoSH standards of verb-noun.

<#
.SYNOPSIS
    Sends PRELOGIN to TCP port to get SQL Server Version.
.DESCRIPTION
    Does what it says.
#>
function Get-SQLServerVersionTCP{
    [cmdletbinding(
        DefaultParameterSetName = '',
        ConfirmImpact = "low"
    )]
    Param(
        [Parameter(
            Mandatory = $True,
            Position = 0,
            ParameterSetName = '',
            ValueFromPipeline = $True)]
        [string]$Computer,
        [Parameter(
            Mandatory = $False,
            Position = 1,
            ParameterSetName = '',
            ValueFromPipeline = $True)]
        [int]$Port,
        [Parameter(
            Mandatory = $False,
            Position = 2,
            ParameterSetName = '',
            ValueFromPipeline = $True)]
        [int]$ConnectionTimeout
    )

    begin {
        $ErrorActionPreference = "SilentlyContinue"
    }
    process {
        #get the ipaddress of the server
        $IPAddress = [System.Net.Dns]::GetHostEntry($Computer).AddressList[0].IPAddressToString
        #build and connect to TCPSocket
	    $TCPSocket = new-object System.Net.Sockets.TcpClient
        $TCPSocket.Connect($IPAddress, $Port);

        #attach socket to network stream
        $TCPStream = $TCPSocket.GetStream();

        # Build the prelogin packet see notes below
        $PreloginPacket =   0x12,0x01,0x00,0x34,0x00,0x00,
                            0x00,0x00,0x00,0x00,0x15,0x00,
                            0x06,0x01,0x00,0x1b,0x00,0x01,
                            0x02,0x00,0x1c,0x00,0x0c,0x03,
                            0x00,0x28,0x00,0x04,0xff,0x08,
                            0x00,0x01,0x55,0x00,0x00,0x00,
                            0x4d,0x53,0x53,0x51,0x4c,0x53,
                            0x65,0x72,0x76,0x65,0x72,0x00,
                            0x01,0x00,0x00,0x00;

        #send the prelogin packet
        $TCPStream.Write($PreloginPacket, 0, $PreloginPacket.Length);

        #wait a bit for the stream to build up
        Start-Sleep -m 1000

        # Buffer to store the response $ReturnData
        $ReturnData = new-object System.Byte[] 255

        # Read the TcpServer response
        $BytesReturned = $TCPStream.Read($ReturnData, 0, $ReturnData.Length)

        [string]$VersionNumber = $ReturnData[29].ToString()+"."+$ReturnData[30].ToString()+"."+(($ReturnData[31]*256)+$ReturnData[32]).ToString()
        return $VersionNumber
        $TCPStream.Close();
        }
}
Export-ModuleMember -Function Get-SQLServerVersionTCP

And for those who are REALLY curious my notes on assembling the PRELOGIN packet. This should work with SQL Server 2005 and above.

Packet Header
0x12 packet type 12 is prelogin
0x01 status 01 is end of message meaning this is the only packet
0x00,0x34 packet length
0x00,0x00 SPID the client spid for debugging from sql server to client
0x00 packetId unsigned char packetid is incramented by 1 modulo 256 this is currently ignored??
0x00 window currently not used just set it to 00
End Packet Header
Option Stream
0x00, This is the version number packet
0x00,0x15, 21st position
0x00,0x06, 6 bytes long
0x01, This is the encryption option
0x00,0x1b, 27th position
0x00,0x01, 1 byte long
0x02, INSTOPT the instance name
0x00,0x1c, 28th position
0x00,0x0c, 12 bytes long. This will vary currently hard set to MSSQLServer
0x01, ThreadId, currently hard set to 1
0x00,0x28, 40th position
0x00,0x04, 4 bytes long
0xff, Packet Terminator
End Option Stream
Begin Tokenless Stream
0x08,0x00,0x01,0x55, Major build number 0x00,0x00, Minor build number
0x00, No encryption
0x4d,0x53,0x53,0x51,0x4c,0x53,0x65,0x72,0x76,0x65,0x72,0x00, Instance name if known should be set if not default to MSSQLServer
0x01,0x00,0x00,0x00; ThreadId
End Tokenless Stream

The End?

Not quite. I’m working on another method that doesn’t rely on TCP/IP at all. I’ll let you guess what it might be.

ALL HAIL POWERSHELL!