21 Nov 2014

Sqsh / FreeTDS with SQL2012 using the instance argument


This is a corner-case advise to anyone looking for a solution as to why sqsh / tsql / freetds combination is working perfectly in one SQL2012 instance but unable to login to a newly configured SQL2012 instance, the details for which just came in.

For starters, Sqsh / FreeTDS is pretty helpful if you're dumping-off of MSSQL Server to any CSV format (to, lets say dump to a PostgreSQL instance).


Symptoms
  • Sqsh / Tsql / FreeTDS is perfectly configured
    • The setup logs in to another SQLServer perfectly well
  • All this when you are able to login to both servers via SSMS from the same box
    • So it isn't a password issue
    • Neither is it a firewall issue
  • The new SQL Server keeps giving following error messages: 


 Error Messages
"Open Client Message"Layer 0, Origin 0, Severity 78, Number 41
Unable to connect: Adaptive Server is unavailable or does not exist

OR

#  /opt/freetds/bin/tsql -S "Q4DEV2" -U 'PostgreSQL' -P 'Password@123'
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20009 (severity 9):
        Unable to connect: Adaptive Server is unavailable or does not exist
        OS error 111, "Connection refused"
There was a problem connecting to the server


Login Details (as given by MSSQL Server Admin)
OLD_SERVER: q3.thatguyfromdelhi.com (this works via sqsh / tsql)
Login:PostgreSQL
Port: 1433 (default)
Password:Password@123

NEW_SERVER: q4.thatguyfromdelhi.com\SQL2012 (this isn't working via sqsh / tsql)
Login:PostgreSQL
Port:9999 (non-default)
Password:Password@123


Solution
# cat /opt/freetds/etc/freetds.conf
[Q3DEV1]
        host = q3.thatguyfromdelhi.com
        port = 1433
        tds version = 7.0
        charset = UTF-8

[Q4DEV2]
        host = q4.thatguyfromdelhi.com
        instance = SQL2012
        tds version = 7.0
        tcp = 9999 # required only if port is not default (i.e. port other than 1433)
        charset = UTF-8

The *MAGIC* to the mix is the 'instance' keyword in the configuration. As things turn out, MSSQL Server does not just identify an Instance via a Port number (like how all sane Server configurations do) they also allow a named instance, which does not work with just a PORT number.

In fact, you may have noticed that the the Port Number is missing in the Q4DEV2 configuration, and that is because PORT is mutually exclusive with INSTANCE keyword. And yes, to confirm the obvious, value of the Instance parameter should be the text after the backslash (\) in the SQLServerName field, that the MSSQL Server Admin provided earlier.

Alternatively, if you do have the new MSSQL Server running on a non-default port (lets assume port 9999) then use the 'tcp' argument as shown above.

A few useful hours wasted at my end... hope this saves someone else's !!

5 Nov 2014

PostgreSQL Explain Plan-Nodes Grid


While reading up about the various PostgreSQL EXPLAIN Plan nodes from multiple sources, I realized a clear lack of a consolidated grid / cheat-sheet, from which I could (in-one-view) cross-check how the plan nodes perform on a comparative basis. While at it, I also tried to attribute what their (good / bad) characteristics are.

Hope this (Work-In-Progress) lookup table helps others on the same path. Any additions / updates are more than welcome.

Update:
The image below is an old-snapshot of the document. The updated web document is available here.



Find Database DNS / Endpoint via SQL

How to get Database identifier using SQL Often there is a need for client programs to find "where am I logged into?". This blog po...