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

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.

Magnifier in Chromebook

Off lately I've started to use the C720 (one of Acer's first Chromebooks) and am I in love with it or what.

While using the Chromebook to view an online Training that was created in a Flash environment (probably using Adobe Connect) I couldn't help myself get frustrated with the fact that I can't do simple things such as Zoom-in on the training video (which happened to be a Shell session) and had to really concentrate on a small part of the screen to read what the font wanted to say.

Most browsers that aren't from the prehistoric era, support zoom-in / zoom-out but the presentation application is 'smart' enough to ensure that that doesn't work as expected. So although the HTML does get enlarged but the presentation stays the same size (just re-centered) to the new zoomed in/out screen space.

A second option (I knew existed in Ubuntu since its early days) had a nifty feature for these situations, where you could just zoom-in and enlarge the screen (unbeknownst to the browser and all applications below the UI layer) this meant that anything rendered on the screen was magnified.

Now Chromebook was smart, pretty compact and an awesome little product, but I really thought expecting graphics related jugglery would be an overkill... and I couldn't find such a feature either.

... Until I read the 'Accessibility' feature in Chromebook (which is a simple little checkbox) and Voila!

Steps:

1. Click User-Image (bottom right)
2. Click Settings
3. Click 'Advanced Settings'
4. Under 'Accessibility' options -> Enable Screen Modifier
5. Zoom-in using .... Ctrl-Alt- button
6. Zoom-out using .... Ctrl-Alt- button

Am still to find things that I can't do on this ultra-cheap / ultra-light / long-lasting-battery / neat laptop!

A Life ... that just happens to have a name.

Zohra Sehgal (1912-2014)

Ae kafir, qadr ki qadr na ho to na sahi,
Ae nadaan, Umr ki fikr na ho to na sahi,

Gar ranjish ho is sadi se ya pichla koi,
To rukh kar, Ja ban ke dikha Zohra koi.

PgAdmin doesn't (directly) support restoring from PLAIN format backups (yet)

Was working with my team at office recently and had a request from the team to learn how to 'Restore' an existing backup using PgAdmin. I thought this was easy, since PgAdmin allows for both 'Backup' and 'Restore' but investigating further, I found that PgAdmin only allows for Restore in formats other than 'PLAIN'.

Cross-checked this on the internet and found references here, here and here saying the same thing. 

PgAdmin doesn't (directly) support Restore'ing from plain text backups. To restore a backup, backed up using PLAIN format, you'd need to pass it through a command line utility called 'psql' that is available in both PgAdmin as well as postgres server installations.

I think it's high time that PgAdmin support this feature, especially when all the building blocks are already in-place. Lets see if I could take out time and submit a patch for this.

... till later.

Christian Marriage Registration (Delhi, India)

(Please scroll down to the read the entire-post to know more)

Documents required for Christian Marriage Registration
(a.k.a. Documents required for Solemnization and Registration of Marriages under Indian Christian Marriage Act, 1872)

Workflow to get your Christian Marriage
Registered with the Delhi Government

Setup Ubuntu for PostgreSQL Development (from tarball)

This very brief article is a primer for those wanting to start developing on PostgreSQL (on Ubuntu 12.10):

Getting Ubuntu ready for (PostgreSQL related) development

• sudo apt-get install build-essential
• sudo apt-get install git
• sudo apt-get install libxml2-dev
• sudo apt-get install libxslt-dev
• sudo apt-get install autotools-dev
• sudo apt-get install automake
• sudo apt-get install libreadline-dev
• sudo apt-get install zlib1g-dev

Get Postgresql source

• Download: http://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.bz2
• tar -jxf postgresql-9.2.2.tar.bz2
• ./configure
• make
• make install

If you want to know more about hacking on PostgreSQL in general, you could always refer to the (very) detailed PostgreSQL Developer FAQ:

http://wiki.postgresql.org/wiki/Developer_FAQ

Happy Hacking :) !