Sunday, December 30, 2012

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

No comments:

Post a Comment