SQL Server to MySQL (in-house server to remote Linode VPS)

[Zane]

This isn't exactly an application design question, but rather a system design one.

 

I am about to install an Inventory Control System inside this store I work in.  

The store itself also owns a Linode VPS running Centos 6.4 which hosts our website.

 

This new Inventory System will come built in with a Microsoft SQL Server, and supposedly it is a SQL Anywhere database, but I'm not too sure what that means.

 

 

I need to make this database publicly accessible, but only via the Linode VPS.  Surely, setting restrictions is easy enough to address that issue.  That isn't my question.

 

My first idea is to put this server into the DMZ, easy.  But it doesn't exactly sound safe.  So my next idea was to put a middleman server in the DMZ, this way the Linode can send queries to that middleman server and it will send that data to the SQL Server and back.  This is very vaguely described I know, but I don't want to get too much into details, but rather, understand how I can create that middleman server, and what could Install onto it that would allow me to securely process queries?

 

My first thought was to install a webservice, that accepts an XML/JSON request and returns an XML/JSON response.

Then, I realized directly afterwards that I don't have any experience setting up a webservice like that.

What kind of options are there out there?
 

Ultimately, my question is, should I just put the Server in the DMZ or should I create the middleman, and if so, can someone point me in the right direction as to getting a webservice set up?

Edited July 16, 2014 by Zane

[kicken]

I'd probably just set it up to only be accessible from the internet by your Linode IP via your firewall. Whether this requires placement in the DMZ or not I dunno. Building a web service just to pass queries and results seems like unnecessary work.

 

Another alternative would be to open a SSH tunnel between the Linode and DB servers and just route the connection through there. You'd need something to monitor and re-establish the tunnel should you ever have a failure such as a temporary network outage though.

[gizmola]

Just FYI SQL Anywhere is not SQL Server. It was originally a product from Watcom that provided a small scale rdbms focused on data replication. It was designed for small shared sql databases that needed to be replicated potentially across wan's and possibly hundreds of servers. It is often licensed as an embedded database for small scale sql db needs, being that it has a lightweight engine process that has been ported to a lot of different platforms. It's not surprising that a company might have built a product with it embedded.

 

I don't know that we're clear on your requirements. What does "make it publicly available" really mean?

 

I'm guessing here that what you mean is that you are going to have to develop an extension to your website that does queries to the sql anywhere database.

 

Assuming that is true, and because sql anywhere intrinsically has this replication capability, one option is to run a sql anywhere instance on your linode box, and then all you would want or need to do is allow just enough access through your firewall for the replication traffic to run between your internal box and the linode instance. You could tighten these rules down to the specific ports and ip's with port forwarding and deny/allow rules. You would not need nor want to put the box in the DMZ.

 

I'd really want to fully understand the vendor's solution stack before I made any decisions on this, so consider it food for thought, although you can run this idea by them, again assuming that their system is built on top of sql anywhere.

[Zane]

Giz, you seemed to hit the nail on the head with your assumption.

 

I have an in-house server running Win7 Pro x64.   The program, called RFMS, includes a SQL Anywhere Server in it's installation.  At least I am pretty sure from the documentation I have read.  I won't actually get this software until sometime tomorrow and then I have to wait for the high-dollar desktop computer to come in.  I'm simply trying to plan ahead using what I know now.

 

Putting it in the DMZ is what scares me, so it seems, fortunately, I won't need to do that.  I'm not too network infrastructure savvy, but I know enough to recognize acronyms and google for which I really have a concrete question.  There is also PHPF  

 

Not only will I need to solve my network challenges, but also configuring my web server to support this http://php.net/manual/en/ref.sybase.php

 

But basically, yeah, I need the Linode and only that Linode to communicate with this yet to be bought, in-house server;  for which I will also need to setup a static IP, unless there is an easier alternative for making your site "publicly accessible"... and by publicly accessible, gizmola, I mean, that it is connectable through an external IP.  I would love to just buy a short domain name and keep a dynamic IP because it's cheaper and less of a pain in the ass, but .... I gotta learn sometime.
 

I'm guessing here that what you mean is that you are going to have to develop an extension to your website that does queries to the sql anywhere database.

 

 

Basically yeah.  I haven't decided whether to have the Linode feed from the server itself, or feed from a backup database.  I had the idea to put a Database Backup Server into the DMZ, simply because there isn't anything that needs to be secure; no credit cards and such are going to be sent.

 

My other idea was to create the DB Backup Server on the Linode, and have it constantly sync with the in-house server; say every 30 minutes or so.

 

I don't want to say that I really wish I had taken more NET+ courses, but damnit... I should have. 

 

The WebService route is something I don't want to do.  Mainly because I have next to no experience with that, XML/SOAP/cURL aren't my strong points.

Edited July 15, 2014 by Zane

[Zane]

Since I'm having the hardest time trying to figure out how to even get Sybase installed so I can actually connect to any SQL Anywhere DB whatsoever, I think that feeding from a Database Backup / MySQL DB Server would be the best option.  I'll put Centos 6 on a spare in-house computer, install php, apache, mysql, etcetera... and have that system connect to the in-house server, and populate a MySQL database for backup.

 

Though, I suppose that may present problems when I actually use a backup, perhaps I could just save a database dump on the side?

 

I can then put the backup in the DMZ and use that as the source for the Linode.

 

Does this sound like a bad idea?  

Should I go the extra step and stumble around with NAT settings and ports and much more networking jargon?

 

My linode is running php 5.5, and it seems the Sybase libraries only support up to 5.4.x

And since I don't want to run 5.4 on my Linode, I chose to put 5.4 on the in-houe db backup server.

 

Please let me know if I'm headed in the wrong direction.  I really wish this store had decided to go with cloud hosting instead, it would have been so much easier, at least I think it would.  The store's owner has trust issues with technology and definitely doesn't want her data up in "outer space".

[Zane]

Or better yet, I could install both the RFMS Server and the PHP/MySQL/Apache Server on the same machine, but then I wouldn't use the DMZ at all and would have to do some kind of witchery to get only that DB Backup / Server available to the Linode.  Luckily there's not much of a rush on this, I am limited to my own capabilities

[gizmola]

I'm pretty sure that basically you just need to install the sybase client libraries on the linode box.

 

I found this:

 

http://www.sybase.com/detail?id=1087327

 

Then they have an example page here: http://www.sybase.com/detail?id=1062960

 

So lets say you setup a sql anywhere server running on the linode box:

 

This product would handle transparent sync between the db's.

 

http://www.sybase.com/developer/mobile/sqlanywhere/mobilink

 

The other possibility is just connecting from the linode server using the same client library listed above directly to the server. You'd just have port forward traffic to that server for the tcp client port:

 

http://dcx.sybase.com/1200/en/dbadmin/serverport-network-conparm.html

[Zane]

basically you just need to install the sybase client libraries on the linode box.

I want to do that so much, I'm definitely going to try.  I have never had to change the --configure variables for PHP before.

Basically, I installed Centos on the Linode, then I installed ZPanel to take care of what I don't know about Linux, although I have been learning more and more, here and there.

 

I think it would make my life a lot easier if i did it that way too. (installing Sybase client onto the VPS).  And I'm going to attempt that method first,

I do need to learn how to do this stuff

 

I was able to use yum replace to upgrade to PHP 5.5, I should be able to figure this out, right?

--------------------

Ok, so my game plan now is to finish setting up the development Linode and somehow manage to install Sybase client onto it successfully.

Edited July 16, 2014 by Zane

[Zane]

After thinking about it, and reading about it, I don't think I want to, or should, connect to the embedded database directly from the Linode.  You mentioned that Sybase is designed to be replicated, so it seems that would be the best option, right?  Replicate it to the Linode.

Furthermore, I get the feeling that Sybase isn't that efficient for a webserver's data source.  So I will not only have to replicate it, but rather I will also have to convert it.

 

So, 

 - replicate to VPS from in-house server
 - constantly overwrite a mysql database with the converted sybase data.

 

The only way I can think to convert the Sybase to MySQL is with a PHP script that runs on cron every half hour or so.

Either that, or there exists a Linux script out there that will do this conversion for me that I don't know about.

 

... sounds simple enough...   

 

Please tell me if I am heading down the wrong track.

Edited July 16, 2014 by Zane

[gizmola]

Assuming these are simple scripts, sure that is another option. You could write your own custom replicator that pushes updates to a mysql server. Obviously you want to run this on the same machine that has the sql anywhere database, and you have the issue of what language you're going to use, but it's a good idea.

 

You basically would need to install the mysql client library on that machine, and you need some scripting language that will make things easy enough. Since you know php well, probably a custom php compile on that machine with inclusion for both client libraries would do the trick.

 

Of course you then have to figure out how you would know when a row has changed and requires update in the Linode MySQL db, but hopefully there is a timestamp of some sort that will make that obvious.

[Zane]

Assuming these are simple scripts, sure that is another option. You could write your own custom replicator that pushes updates to a mysql server. Obviously you want to run this on the same machine that has the sql anywhere database, and you have the issue of what language you're going to use, but it's a good idea.

 

 

I get the feeling that you misunderstood my idea, but I like the idea of replicating a MySQL database to the VPS from the in-house server instead like you've seemed to understand it.

 

.  

 

The idea I originally understood from you was outlined like so

 

 

I rather like the idea of a custom PHP script  for Sybase --> MySQL and then replication to the VPS's MySQL.

 

Anyway, I seem to be getting closer and closer to decent plan.

Now I just have to figure out how to implement it.

 

I read this tutorial over; I'm sure there's a wealth of better information out there, but the mysqldump method seems the easiest to me.

http://www.howtoforge.com/mysql_database_replication

 

Ccreating a mysqldump from the converted Sybase DB and sending it to the VPS.

From the VPS though, I would need a way to listen... a way for mywebstore.com to be notified that it's time to overwrite the database with the sent mysqldump.

 

That tutorial mentions a MASTER / SLAVE method.  I would definitely need more documentation to learn that method, I'll have to search for more information, but if you have a better idea, please let me know.

 

Another alternative would be to open a SSH tunnel between the Linode and DB servers and just route the connection through there. You'd need something to monitor and re-establish the tunnel should you ever have a failure such as a temporary network outage though.

 

As far as sending the mysqldump, I like this approach the best, but I'm not sure if I need to go full SSH, I would imagine that I could just send it through FTP.  It wouldn't need to be secure since it's only inventory information.  I am trying to avoid having the in-house server, or the in-house network rather, serving anything outbound.  I would rather leave it inclusive to the store. I would rather just send everything to the VPS and receive nothing back, other than the ftp status messages.

 

I feel like I'm in way over my head, but at the same time challenged to complete the project.

I will definitely learn plenty of new things in the process.

Edited July 17, 2014 by Zane

[kicken]

As far as sending the mysqldump, I like this approach the best, but I'm not sure if I need to go full SSH, I would imagine that I could just send it through FTP.  It wouldn't need to be secure since it's only inventory information.  I am trying to avoid having the in-house server, or the in-house network rather, serving anything outbound.  I would rather leave it inclusive to the store. I would rather just send everything to the VPS and receive nothing back, other than the ftp status messages.

Unless you already have FTP setup, you can just use SFTP/SCP to transfer your dump file using your existing SSH setup. Saves having to setup another service on the linode and adds encryption to the data which is nice even if not strictly necessary.

 

As far as your replication setup there are two options:

• Setup proper replication with a master and slave server. This would still require either opening the firewall up on the necessary IP's and ports or opening up a SSH tunnel so the servers can keep in contact.
• Setup "fake replication" by just periodically mysqldump'ing your master DB, transferring it to the linode and re-loading the slave DB.

The first option will keep your data the most up to date and the updates will be more transparent and prevent possible down time. I would recommend this you try and get this option working to have the smoothest syncing and closer to real-time data on the linode. I've never had the need to setup replication so I have not tried, but it doesn't sound too hard to get a basic replication setup working. The official docs on the subject are here. Have a look through those as well as whatever tutorials you find.

 

The second option is somewhat easy but will lag the DB by however long you wait between updates and unless your DB is small and loads quickly will cause downtime whenever you are syncing. The second option is essentially a "backup and restore" rather than actual replication so each sync will be dumping and reloading the entire database rather than just what's changed since the last sync.

 

Just note also that both of these methods are one-way read-only. You won't be able to update your inventory status from the linode web app, such as if someone buys something from your web store. To enable that you either have to go back to connecting to the master DB directly or re-visit the idea of a web-service middle-man. It sounds like you don't expect to be doing any updates so it may not be an issue but wanted to point it out any way.

[Zane]

 

 

The [MASTER/SLAVE] option will keep your data the most up to date and the updates will be more transparent and prevent possible down time.

That's exactly what I'm going for, transparent updates.  So apparently the mysqldump method is out of the question. I must use the MASTER/SLAVE option.

--- but I would still need to forward ports and such to open an SSH tunnel?   

 

I was thinking about opening a SSH tunnel from the in-house server, would I still need to open ports for that?

Or would I have to create a tunnel from mywebstore.com.

 

I still have more reading to do, and not to mention.... the practice.

[kicken]

You can either open the necessary ports to allow the replication, or use a SSH tunnel, you don't necessarily need to do both (unless you'd need to open ports to allow SSH).

 

Using an SSH tunnel will let you have encrypted communications and only require an out going SSH connection from the in-house server to the linode. As mentioned you'd want to setup something to monitor the tunnel and re-establish it if necessary however.

[Zane]

Amazingly good news.  Apparently, this RFMS software does not come with a Sybase SQL Anywhere database like I was told, like I read from the documentation.

 

Instead, I had to install Microsoft SQL Server Express 2012.  I'm not sure if the Express version has a different approach as far as connecting, but I am assuming I do not need to install a Sybase PHP module now.

[kicken]

If you want to connect from the Linux VPS directly to SQL Server you'll need to setup ODBC. If you plan to do some kind of SQL Server -> MySQL setup then replicate to a remote Mysql on the VPS, you'll want to use the SQLSRV driver for PHP. There's no official build for 5.5 yet, but there are some third-party ones you can find. You might also be able to do an export from sql server to mysql using the SQL server management tools.

 

A quick google search lead me to SymmetricDS which might be useful for you as well if you want to do SQL Server -> MySQL.

Edited July 21, 2014 by kicken

[Zane]

SymetricDS seems like a great solution.  according to it's documentation, I can run it as a service on my Windows machine (the in-house root node), and have it push data.

 

What I have yet to find in the documentation, is how to set it up to replicate to the client... runhavning MySQL.  It has to be possible, I would need to actually try it out before I have any decent concrete questions.  One of my greatest goals here is to avoid getting a static IP.  If I can get this SymetricDS to do nothing but send data to the VPS, then this will work perfectly; I just need to tinker with it a bit, probably more than that.

 

So, the Centos VPS "client" node should only receive, while the in-house Windows machine "root" needs only to send.

 

Great find.  Thanks kicken!

[Zane]

This SymmetricDS is driving me up the wall, I really want to use it, but I can't get the sufficient information to do what I want.  I've posted two questions on their forums and it doesn't seem to be a very active place; but they have a billion and one different user guides and tutorials with hardly any examples.

 

I downloaded a trial version of SQL Examiner, which is GUI-based, and it seems to do a somewhat decent job, I mean it actually makes the full database comparison and updates the MySQL database successfully on the remote server just fine, but I have to initialize the replication manually.

 

I experimented with scp and like it very much, but again, replication would have to be manual unless I come up with a script to automate it, and not only that, I would need to create a listener script on the remote server.  And it's not like I need to synchronize the entire database, but rather, only a few tables, from some of which I would only need a few columns.

 

SQL Server comes with something called Linked Accounts where I can actually query my remote mysal database as if it were local, and eventually I ended up researching triggers for all updates, inserts, and deletes..., but again, I find myself realizing that I suck as a database administrator.

 

My last resort to this problem would be to schedule an sql dump to run on the in-house server periodically throughout the day, send it to the VPS overwriting an SQL import file, then create a crontab on that VPS that will import that dump periodically...  

 

Why does something that seems so damned simple have to be so damned complicated?