Cloud Database

[Dan Cole]

I operate a couple of different sites that using a singe MySQL database.   Until now the sites have been using separate instances of osC called from different sub directories.  Each osC instances has been tailored for the particular site using defines in the configuration files and different configuration tables.  Data is pulled/added to the single database.  I can select which manufacturers, categories and products etc appear on a particular site or across different sites.  This has worked well for several years and the single database allows me to easily manage customer interactions, email marketing, products, orders etc all in one spot without duplication.    I've been thinking about moving these sites to separate domains and setting up additional micro sites and have been wondering about hosting the database in the cloud which should allow me to continue to use a single database while deploying the sites across different domains/hosts.  I'm not 100% sure I want to do this yet but I'll likely spin off a test site to get my feet wet and see what issues arise. 

The idea has been percolating for awhile now and at this point I'm wondering if anyone is using a Cloud based MySQL database like Google Cloud SQL with osC and what they think of it.  Does it work well?  Is it efficient, fast etc? What are the pros and cons etc.

I would be interested in any experience or comments you might have in regards to operating osC on a Cloud based or remote database.

Dan

 

[♥Gyakutsuki]

Sorry @dan, but if someone has information or use, it could interesting to share this experience.

[Jack_mcs]

Dan - I think the main decision for using a different server for the database comes down to cost. I don't know what google charges but whatever it is, it will be more than what your hosting plan costs. There's no real advantage to having a database hosted on a different server other than if the main server goes down and has to be restored. Restores can take several days on a large server so having the database on an external server can allow you to get your site up more quickly. But such problems are rare nowadays so it comes back to whether having that "insurance" is worth the added cost. There is also the question of how available to the other server will be. If it is slow to load, so is your site. Google states that they have software in place to make that a non-issue so as long as that is true, cost is the only issue, that I can see.

[♥John W]

Isn't a remote database connection slower than one that is on the main server?  Or, is that a thing of the past?

[Jack_mcs]

There might be a noticeable slowdown if the site is a busy one since there is a longer distance involved with a remote server. It probably wouldn't be an issue for many small shops. It might make a difference, too, if the remote server was a local one (same host) as opposed to a completely different location.

[Dan Cole]

4 hours ago, Jack_mcs said:

Dan - I think the main decision for using a different server for the database comes down to cost. I don't know what google charges but whatever it is, it will be more than what your hosting plan costs. There's no real advantage to having a database hosted on a different server other than if the main server goes down and has to be restored. Restores can take several days on a large server so having the database on an external server can allow you to get your site up more quickly. But such problems are rare nowadays so it comes back to whether having that "insurance" is worth the added cost. There is also the question of how available to the other server will be. If it is slow to load, so is your site. Google states that they have software in place to make that a non-issue so as long as that is true, cost is the only issue, that I can see.

I looked at the pricing and it seemed ridiculously cheap but that is certainly an important criteria.  Their calculator can be found here.  See the Cloud DataStore Tab.

Speed is definitely another important criteria.  I didn't see any bench marks but I'd want it to be at least as fast as it would be if it was maintained on the same server where  our site was hosted.    To me the advantage of having one Database, and the efficiencies that arise from that, is the big plus. 

BTW, I saw on one of the Cpanels I access something referenced as a Remote Database.  I didn't look at it closely but do you happen to know if that might accomplish the same thing as Google seems to be offering? ie the ability to access it from different servers hosting different instances of osC?

Dan

 

[ArtcoInc]

@Dan Cole

 

On 8/12/2017 at 7:55 PM, Dan Cole said:

I operate a couple of different sites that using a singe MySQL database.   Until now the sites have been using separate instances of osC called from different sub directories.  Each osC instances has been tailored for the particular site using defines in the configuration files and different configuration tables.  Data is pulled/added to the single database.  I can select which manufacturers, categories and products etc appear on a particular site or across different sites.  This has worked well for several years and the single database allows me to easily manage customer interactions, email marketing, products, orders etc all in one spot without duplication. 

Dan

I have entertained the idea of doing this too. Could you further explain how you did this? A write-up on how you set up and maintain this would be most welcome. Thanks!

Malcolm

[Jack_mcs]

Dan: It may be worth giving it a try for one shop. If it works well the others could be switched. Just be sure to block your site while the database is being copied so you don't lose any data.

The remote you saw in cpanel is probably "remote access." That is an option to allow a connection to your database from some other location. So if you have two sites in different hosting accounts, you could have one load the database the other uses. You would need to put the IP of the second site into the remote access section of the main one to allow the connection.

[Dan Cole]

3 hours ago, Jack_mcs said:

Dan: It may be worth giving it a try for one shop. If it works well the others could be switched. Just be sure to block your site while the database is being copied so you don't lose any data.

@Jack_mcs Thanks Jack...I was coming to that conclusion too....as they say the proof is in the pudding.  It's now on my ever growing list of things to do.

Dan

 

[Dan Cole]

6 hours ago, ArtcoInc said:

I have entertained the idea of doing this too. Could you further explain how you did this? A write-up on how you set up and maintain this would be most welcome. Thanks!

@ArtcoInc Hi Malcolm.  

This concept evolved over time and is quite involved.   At this point I really don't have the time to properly write it up but if you really want to go down this path I would suggest starting up a separate thread on the topic and I'll try to give you a bullet form outline of what I did and try to help with any questions you might have.  It really isn't all that complicated but be prepared to invest a ton of time making changes and modifying core code.   

Dan 

[♥ecartz]

On 8/12/2017 at 10:55 PM, Dan Cole said:

I've been thinking about moving these sites to separate domains and setting up additional micro sites and have been wondering about hosting the database in the cloud which should allow me to continue to use a single database while deploying the sites across different domains/hosts.

You don't need the database to be on the cloud to do this.  You don't even need it to be on a separate server from the web server (although there are also times where that is helpful).  If you have a dedicated server (which can be an EC2 or similar instance), you can host multiple domains from the same server and run a single database on it.  Some CPanel style hosts may be able to allow you to share a database among multiple domains as well.  I've certainly been able to do it with subdomains (something like www.oscommerce.com and www.oscommerce.com/forums). 

I don't have experience with the Google offerings.  But with Amazon (AWS), you can run an RDS (Relational Database Service) instance and one or more EC2 (Elastic Compute Cloud) instances that access it.  The RDS instance can be configured to only allow access to the EC2 instances, so you don't have external agents trying to connect to your database.  I managed a Drupal site like this, with Amazon RDS and load-balanced web servers that could access it.  As I recall, the bill was something like a $1000 a month for a relatively busy site.  We were using the largest database ($400) and web server ($400) instances as well as the occasional second web server and other test servers and storage.  Prices may be out of date.  A smaller site could presumably run on smaller instances. 

The management tools were awesome.  We could easily track how busy the servers were and other issues.  Backing up the database was as simple as pushing a button or scheduling a regular activity. 

The way that Amazon works, you don't need to have all the web servers running at once.  So when it is slow or normal, you can run a single instance.  But when it is busy, you can add a second instance behind the same load-balanced IP.  It will split the traffic between them.  Since both go to the same database instance, the user won't be able to tell that there are multiple front ends. 

One challenge might be that for HTTPS to work, you need separate IP addresses and/or ports for each SSL domain.  If your microsites can share the same secure domain behind them, then that would certainly work.  But if not, you might need to pay extra to get more IP addresses and proxy them back to separate ports.  I remember IPs as a limitation that we never needed to fix.  We just worked around it, as we didn't need more public IPs.  There were just times they would have been convenient. 

I do not think it is a good idea to try to run a Google database with a web server somewhere other than Google.  The latency can add up.  If you're low traffic enough that it doesn't matter, you're probably also low traffic enough to run your web server on the same host as the database.  It's a little more complicated to manage (no CPanel to manage domains, etc.), but they'd essentially provide you your own virtual server.  Or as I said earlier, some CPanel style hosts will allow you to share a database across multiple domains. 

[tornrawks]

On 8/15/2017 at 1:30 PM, Dan Cole said:

I looked at the pricing and it seemed ridiculously cheap but that is certainly an important criteria.  Their calculator can be found here.  See the Cloud DataStore Tab.

Speed is definitely another important criteria.  I didn't see any bench marks but I'd want it to be at least as fast as it would be if it was maintained on the same server where  our site was hosted.    To me the advantage of having one Database, and the efficiencies that arise from that, is the big plus. 

BTW, I saw on one of the Cpanels I access something referenced as a Remote Database.  I didn't look at it closely but do you happen to know if that might accomplish the same thing as Google seems to be offering? ie the ability to access it from different servers hosting different instances of osC?

Dan

 

Dan - I have used the "MySQL Remote Access" icon in cPanel - it is just to setup your IP's / wildcard IP's to allow access to your database from off-site.  Note also - very few providers actually allow this.  This icon is there, part of cPanel - but 99 times out of 100 they have firewalled the database port(s) (typically 3306) and it is useless.

That has been my experience anyhow - the only workaround is an SSH tunnel and I find the performance terrible.  I stick with the DB on the host machine as it is best as far as responsiveness and user experience go.