MS SQL to MySQL

I’m finally just about to go live with the project I’ve been working on for the past few months.  On the development site, the dev SQL Server database is pushing data to the dev MySQL database.  I setup the live SQL Server database to push data to a test MySQL database, just so I can make sure there isn’t anything funky on the live SQL Server database that I’m not aware of.  Once I get the go ahead, the live SQL Server database will be pushing live data to the live MySQL database.  When that happens, we gonnna parrttaaayy!!  Just kidding.

Everything I’ve learned about MySQL and MS SQL during this project has been tremendous and will definitely help me on my way to becoming a real DBA.

MySQL four-part name

All day I’ve been trying to use this four-part name to run queries on my linked server. All day…… Everyone seemed to suggest doing the four-part name instead of OpenQuery.
Every time I tried the four-part name, I would get this error:
“A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.”

Of course when I found someone else with the same problem as me, they hadn’t found a solution.  But…I finally figured it out.  MySQL doesn’t support schemas or owners.  So saying, LinkedServerName.DBName.Owner/Schema.TableName wasn’t working because it wasn’t supported.  I’m too lazy to try to remember my search query that brought me to those forums of people with the same problem as me.  Now, there won’t be an answer to their problem.  Oh well.

Anyways, what ended up working for me was:  LinkedServerName…TableName

MS SQL to MySQL

I successfully connected an MS SQL Server database to a MySQL database.  The SQL Server db is for internal access and I set up the MySQL database for external access.  So, as the internal db is updated, that same live data is pushed to the external database via triggers and SQL jobs.

Whenever a certain table is updated or a row is inserted, a trigger fires, and that new or updated row is inserted into a temporary table.  I created a SQL job that runs every two minutes that will copy new rows (rows where date < getDate() and has not been copied to the external db) to the external db.  During the wee hours of the night/morning, the rows, from that temporary SQL table, that have been written to the external db are dropped.  That way, we don’t have a bloated temporary table on our hands.

Created a linked server to MySQL

Just a little background.  My company is currently running a MSSQL server for internal and external content.  We recently decided to keep the MSSQL server internal and create a MySQL server for external access.  I looked into many ways to push real-time data from the internal SQL server to the external MySQL server.  I finally settled on creating a linked server to MySQL through SQL Server.

So for the past two days, I have been banging my head against my keyboard trying to get this linked server connected.  I’ve finally banged my head at the perfect velocity and angle to create a linked server.  To start off, I searched “mysql linked servers” and followed pretty much every “How To:” I could find.  Everyone seemed to be able to create linked servers like it was child’s play.  The best “How To” I found was this one http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/.

I downloaded the MySQL Connector/ODBC 5.1 download.  Note:  I am a little green when it comes to configuring database connections and pretty much anything outside of writing a SQL query.  First, I installed it on my local machine that I use to remote into the server (the MSSQL box).  I quickly realized that wouldn’t work and didn’t make sense.  Next, I installed it on the SQL Server box, ran through creating a new “System DSN”, and of couse could not connect to the MySQL server.  My next thought was to install the Connector/ODBC on the actual server MySQL was on.  I tested the connection and it worked.  So I continued through the “How To”.  I continued through all the steps of creating a new linked server.  At the end of every “How To” was “… and click OK.  And now you’ve created a linked server.”  PPPfffftttt!!!  How about I kept getting the same error:

“Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “mysqlLinked”.
OLE DB provider “MSDASQL” for linked server “mysql-linkedserver” returned message “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”. (Microsoft SQL Server, Error: 7303)”

Of course, I searched for a solution to this error.  A ton of people ran into this issue to.  Some people found a solution because it was an ISP issue or they banged their heads against a keyboard and the linked server miraculously connected.  I finally came to my senses and installed the Connector/ODBC on the same server the SQL Server client was on.  Again I got that error.  I eventually narrowed it down to a permissions problem.  Every time I tried to create the linked server, I noticed it said ‘root@blahblah-internal-server.com’ instead of the server address I was supplying.  I went to PHPMyAdmin to add a new user.  But this time, I set the host to “Any Host or %”.  So now, MySQL should accept ‘root@blahblah-internal-server.com’.  With this new user addition, I created a new DSN with the new user credentials, and waddayaknow a connection test was successful.

One hurdle down, now it’s back to creating a linked server.  I followed through the rest of the “How To” I was using and, voilà, I created a linked server!  It was simple as pie to create a linked server.  With the creation of my first linked server in my pocket, I queried the test database I set up on the MySQL server, and it worked fine.

Current happenings

Right now for work, I am working on making a MySQL database that will lie externally and handle the company’s website database traffic.  Currently, the website is hitting our internal MS SQL Server database.

I’ve set up the MySQL database.  I’m trying to create a linked server through SQL Server.  I ran into quite a few road blocks today.  We’ll see how everything goes tomorrow.  I have a few ideas on how to make it work.

Outside of work, I’m still working with Joomla.  I’ve been doing a lot of data input.  So, I’m getting more familiar with the basic components of Joomla.  There are a few modules that were added on.  I’m more familiar with those as well.  Once, I’m done with the project I’ll have more details.