Establishing a Linked Server to Google Cloud SQL

Need to create a linked server to a Google Cloud SQL instance? I did. Here's how I did it.

For the purposes of this post I'm using Microsoft SQL Server 2008 Express. However I have reproduced the results here using Microsoft SQL Server 2005 Enterprise Edition as well as Microsoft SQL Server 2012 Express.

What you need.

On the server you intend to establish the linked server from you need to install the following software in the order listed.

Java 6 JDK - link

Google Cloud SQL Tools - download 

OpenLink Single-Tier ODBC to JDBC Bridge (Lite Edition) - This isn't freeware. - link 

What you need to do.

Obviously you need to install the Java 6 JDK before you do anything. So do that. 

Next, extract the Google Cloud SQL tools to a permanent directory (no bullshit temp directories or anything). For the purposes of this document I am going to assume that you placed the tools in a directory called 'C:\google_cloud_sql\'.

Now you need to create/alter some system level environment variables. 

First you need to ensure that 'google_sql.jar' is present in your 'CLASSPATH' variable. On my computer/server I didn't have a 'CLASSPATH' variable so I had to create it. Here is a screen shot of how I have mine setup:

Now you need to verify that your 'PATH' environment variable is correctly setup. I had to add the following directory to my 'PATH' variable:

C:\Program Files\Java\jre6\bin\server

Having the above directory in your 'PATH' will avoid errors like 'jvm.dll cannot be found'.

Now we need to setup the Google Cloud SQL tools. If you're at all familiar with the Google Cloud SQL tools, you know that you'll need to get an OAuth token in order to be able to connect to a Cloud SQL instance. In order for this magic to work you'll need to setup the OAuth token using the account that the SQL Server service is running under. This is because the OAuth token is cached in the registry under HKEY_CURRENT_USER.

Next we setup the bridge to the Google Cloud SQL JDBC.

I'm assuming at this point that you've already installed the bridge software from OpenLink. So now we are going to setup an ODBC...

Complete the following steps:

Open the 'ODBC Data Source Administrator'.

Click on the 'System DSN' tab.

Click the 'Add' button.

Select the driver labeled 'OpenLink Lite for JDK 1.5 [6.0]' and then click 'Finish'.

Give a name to the datasource you are creating.

Specify the JDBC driver and URL string.

The JDBC driver is: com.google.cloud.sql.Driver
The URL string is: {jdbc:google:rdbms:put-in-you-cloud-sql-instance-name}

Click 'Next' on the wizard until the last step of the wizard. You should have the opportunity to test the connection. Go ahead and test to ensure that we've done everything right up until this point.

After you verify that the connection has succeeded. Reboot.

Seriously, reboot. If you don't you'll most likely encounter good ol' system error 126 when you attempt to setup the linked server.

Now we'll setup our linked server.

I'm assuming that you know how to get to the dialog that allows you to specify a new linked server. So once you're there you need to obviously name the linked server, specify the provider as 'Microsoft OLE DB Provider for ODBC Drivers', and then specify the product name and data source as whatever you called your bridged ODBC connection to your cloud SQL instance.

Assuming that my instructions aren't complete shit, and assuming that you followed them correctly, you should be able to click 'OK' and have a functioning linked server to your Google Cloud SQL instance.

Next Steps

Now that you have a handy dandy linked server, you are probably going to have a hankering to run a query against it.

Usually when you query against a linked server you use a 4 part name (linked server, database, schema owner, table name) in order to address a table. Problem is, that type of query isn't supported so we'll have to use OpenQuery. 

Never used OpenQuery? Here is a quick lesson. Let us suppose that you named your linked server 'GCS'. Let us also suppose that on your Cloud SQL instance you want to query a database called 'Example' and a table called 'Plateau'.  Your query (using OpenQuery) would look like this:

SELECT * FROM OPENQUERY(GCS, 'SELECT * FROM Example.Plateau;')

Pay close attention to the fact that the name of the linked server is not enclosed in quotes, and to the fact that the query that I specified against the 'Example' database has a semi-colon at the end. Remember, that the queries that you specify inside of OpenQuery must be syntactically identical to the queries that you would normally execute against Google Cloud SQL or MySQL.

Any questions, concerns, cusses, or discusses? Put them in the comments.

P.S. If anyone knows of a free JDBC to ODBC bridge let me know. I'd like this solution to be free...

Moving On

There comes a time in every developer's life when it's time to move on. Sometimes it's your choice, sometimes the choice is made for you, either way you're going. This post is about how I've dealt with both situations.

Your Choice

So you've decided it's time for you to move on.
There are a few questions that you need some solid answers to first.

1. Why are you leaving?
   Think hard about the answer to this question. If the only reason you're leaving is for more money you should do some research to ensure your not already making a "fair" salary. Also, I speak from experience, money can't make you happy if you simply dislike what you do.
2. What are you looking for?
   Think big here! What is missing from your current environment that you'd like to see in your next? Bigger team? Larger databases? More time off? Whatever it is, make note, because the answer to this question will help you make the best move. Sounds obvious I know, but sometimes the salary offer alone is enough to make you want to say yes. Keep the answer to this question as a benchmark to objectively judge how good any potential job offer is.

Not Your Choice

The choice has been made for you, it's time for you to go.
Whether you've been laid off or fired one thing is almost certain, you'll think they are fools. But guess what, it doesn't matter, you're still not employed and the company will survive without you. The quicker you accept this the better off you'll be.
Some people might tell you that you should start your job search immediately after being handed your walking papers. I would suggest (if possible) taking a day or two to decompress and try to shake any negative feelings about your situation out of your system, they are not helpful when trying to find a new gig.
Your spouse may take your new situation harder than you are. But that's OK. It's your job to support them no matter what. I found that by reassuring my wife that everything was going to be OK actually helped me realize that everything was going to be OK. DO NOT resent your spouse for not coddling your bruised ego, it's possible they are scared, so just relax. Keep cool and when this is all over you'll find that you have a stronger relationship.
Keep moving. Before and during your job search you have to keep moving. Do something! Write some code, write a blog post, go pick up litter, just don't sit idle. I found that messing around with the Project Euler questions was a great way to keep me occupied between calls with recruiters and job interviews.
Sleeping. I should warn you, your quality of sleep will go down the metaphorical shitter. Just accept it. No extra sleeping pills or booze for you. The last thing you need is a drug or alcohol problem. Don't worry though, once you have an offer in hand this to shall pass.

Resume

You've been keeping this thing updated right? I didn't think so.
One thing I've found after having A LOT of people look at my resume is that everyone has their own idea of what makes a great resume. I took the "action->impact" approach to writing my resume. Basically, what did you do and what impact did it have. Here's a sample from my resume:

Designed and implemented a corporate directory XML application for our Cisco Call Manager deployment. The directory was populated by integrating with Active Directory for employee and department information as well as the Call Manager's internal LDAP server for phone number and extension information. The end result was that a user was able to browse through the corporate directory by department on their phone.

Wordy? Sure is, but most people that reviewed my resume didn't seem to mind. Just don't go overboard, no one is going to read a twenty page resume. Listen to your gut here, if you worked with a one hour photo mini-lab ten years ago a prospective employer looking for a developer probably won't care.

Job Leads

In todays job market place you'll probably have to deal with a recruiter. This isn't a bad thing you just have to be smart about it and remember one thing, you don't work for them. You are how they make money, so if they aren't treating you right, walk away.
But if you are lucky enough to have a personal network (no I'm not talking about LinkedIn, but hell it couldn't hurt), it's time to engage it. Time and again I've found that it's more about who you know than what you know. On my most recent job hunt, I had acquaintances or friends at two of the three companies. People are a little less likely to bullshit you if you have a history.

Interviewing

Depending on who you ask, this can be the most nerve wracking part of the entire process.
But it shouldn't be.
A job interview should feel like a conversation about developing software, yourself and the company you are interviewing with. You should obviously avoid using foul language, but if you can't get a good conversational flow going then you probably wouldn't fit in anyway.
In my latest round of interviews I was lucky enough to avoid bizarre interview questions. I understand the motivation behind asking someone "if they were were a breakfast cereal which one would they be", but give me a break, just because you read a book, blog post or pamphlet about this stuff it doesn't make you a PhD in behavior psychology. I digress. If you find that your interview is a series of these questions you should treat that as a warning sign.
Another warning sign. Punctuality. Remember it's not just them interviewing you. If you are scheduled for a two o'clock interview and they put you in a non-descript conference room and make you wait twenty minutes until finally the interviewer comes in with a smug look and no apology, huge warning sign.
But always be courteous. No matter how much you feel an interviewer has slighted you, it does you no favors to tell him/him what to do and where to do it.

Job Offer Consideration

So you've got at least one offer. Consider it carefully, because if everything goes the way it should you'll be there a while.
I recently had three job offers, here are some of the things that I took into consideration when making my decision.

• Money. Obviously not the most important aspect of the job offer but still important. Think about your last company. How many merit increases in pay did you receive. It's probably the same way at the next company. All I'm trying to say is, you may not get a raise for a couple of years, so make sure you are comfortable with the number.
• Flexibility. Does the shop you are considering require that you work 50 hours a week? Can you work from home? Can you work 7am to 4pm? These are important questions to ask, because they go towards your long term happiness.
• Commute. The shorter the better. Need I say more?
• Benefits. How much is health coverage going to cost you? What about short and long term disability? Paid time off? Holidays? All of these things are super important. Make sure you don't let the salary number blind you.