Remote DB Connection: Pros and Cons?

Have any of you tried running a PHP website using a remote database
connection?

We currently have an in-house PHP website driven by a PostgreSQL database
that is HEAVILY administered within the office with an administration sister
site (also PHP).

Problem: the office connection is having trouble keeping up with the website
traffic. Our IT guys want to outsource the whole server to a co-location
facility, but the administrators don't want the extra lag on the admin site.

Is it feasible to host the database and admin site in the office, but
outsource the website and connect to the office database remotely? Is there
any other way to do this?

Tony

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Remote DB Connection: Pros and Cons?

Tony Grimes wrote:
> Have any of you tried running a PHP website using a remote database
> connection?
>
> We currently have an in-house PHP website driven by a PostgreSQL database
> that is HEAVILY administered within the office with an administration sister
> site (also PHP).
>
> Problem: the office connection is having trouble keeping up with the website
> traffic. Our IT guys want to outsource the whole server to a co-location
> facility, but the administrators don't want the extra lag on the admin site.
>
> Is it feasible to host the database and admin site in the office, but
> outsource the website and connect to the office database remotely? Is there
> any other way to do this?

Do the website and admin area reference the same database tables?

If they don't, split the database up and keep the website db on the
website server and the other internally.

If they do, which one is the 'primary' set? ie which one gets updated?

You could use replication to keep them in sync (http://www.slony.info/
for example).

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Remote DB Connection: Pros and Cons?

Splitting the tables probably wouldn't work. Most of the tables are used by
both sites and the ones that aren't are tied to the rest by foreign keys.

Most of the updates are done from the admin site, so we're toying with the
idea of creating a read-only data warehouse for the website. The sync would
be one way from main office db to the warehouse (say, every 10 minutes).

Any updates the website does (like an event registration or profile update)
would be done remotely to the office db. In this scheme the website db would
be 10 minutes behind. We can live with that.

Doing everything by remote connection would be easier, but we're worried
about the performance.

Tony


On 10/11/07 6:01 PM, "Chris" wrote:

> Tony Grimes wrote:
>> Have any of you tried running a PHP website using a remote database
>> connection?
>>
>> We currently have an in-house PHP website driven by a PostgreSQL database
>> that is HEAVILY administered within the office with an administration sister
>> site (also PHP).
>>
>> Problem: the office connection is having trouble keeping up with the website
>> traffic. Our IT guys want to outsource the whole server to a co-location
>> facility, but the administrators don't want the extra lag on the admin site.
>>
>> Is it feasible to host the database and admin site in the office, but
>> outsource the website and connect to the office database remotely? Is there
>> any other way to do this?
>
> Do the website and admin area reference the same database tables?
>
> If they don't, split the database up and keep the website db on the
> website server and the other internally.
>
> If they do, which one is the 'primary' set? ie which one gets updated?
>
> You could use replication to keep them in sync (http://www.slony.info/
> for example).

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Remote DB Connection: Pros and Cons?

------=_Part_22671_9936128.1192148115259
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi

You would be better off with the database server and php on the same server
- both sites.

You will find there is significant lag if there is a poor
connection between the web and database server, but if you are just
accessing web pages
and updating data, even with large pages there is not much delay.

You could always try both permutations and see, but that has been my
experience.

Peter



On 12/10/2007, Chris wrote:
>
> Tony Grimes wrote:
> > Have any of you tried running a PHP website using a remote database
> > connection?
> >
> > We currently have an in-house PHP website driven by a PostgreSQL
> database
> > that is HEAVILY administered within the office with an administration
> sister
> > site (also PHP).
> >
> > Problem: the office connection is having trouble keeping up with the
> website
> > traffic. Our IT guys want to outsource the whole server to a co-location
> > facility, but the administrators don't want the extra lag on the admin
> site.
> >
> > Is it feasible to host the database and admin site in the office, but
> > outsource the website and connect to the office database remotely? Is
> there
> > any other way to do this?
>
> Do the website and admin area reference the same database tables?
>
> If they don't, split the database up and keep the website db on the
> website server and the other internally.
>
> If they do, which one is the 'primary' set? ie which one gets updated?
>
> You could use replication to keep them in sync (http://www.slony.info/
> for example).
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

------=_Part_22671_9936128.1192148115259--

Re: Remote DB Connection: Pros and Cons?

Tony Grimes wrote:
> Splitting the tables probably wouldn't work. Most of the tables are used by
> both sites and the ones that aren't are tied to the rest by foreign keys.
>
> Most of the updates are done from the admin site, so we're toying with the
> idea of creating a read-only data warehouse for the website. The sync would
> be one way from main office db to the warehouse (say, every 10 minutes).

No need to create something that has already been done.

Use slony or something to replicate the database.

> Any updates the website does (like an event registration or profile update)
> would be done remotely to the office db. In this scheme the website db would
> be 10 minutes behind. We can live with that.

When you do a registration, get it to use a remote database connection
instead of the 'local' one.

$local_connection = pg_connect('localhost....');

$remote_connection = pg_connect('remote_server_ip....');

$remote_result = pg_query($remote_connection, $query);

> Doing everything by remote connection would be easier, but we're worried
> about the performance.

The only way to find out would be to test it really. As long as you have
a decent connection in to the office and a decent connection on the
remote end and aren't pulling through a ridiculous amount of data in
queries (eg pulling through 50,000 records when you only really need
10), you should be fine.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Remote DB Connection: Pros and Cons?

--_a668f789-e86d-4ffa-af52-66611d9ad531_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


What about setting up replication between the servers? Make the the web a s=
lave of the office one and make it read only so avoid messing up the data.
=20
bastien> Date: Thu, 11 Oct 2007 18:08:29 -0600> From: thelist@tonygrimes.co=
m> To: php-db@lists.php.net> Subject: Re: [PHP-DB] Remote DB Connection: Pr=
os and Cons?> > Splitting the tables probably wouldn't work. Most of the ta=
bles are used by> both sites and the ones that aren't are tied to the rest =
by foreign keys.> > Most of the updates are done from the admin site, so we=
're toying with the> idea of creating a read-only data warehouse for the we=
bsite. The sync would> be one way from main office db to the warehouse (say=
, every 10 minutes).> > Any updates the website does (like an event registr=
ation or profile update)> would be done remotely to the office db. In this =
scheme the website db would> be 10 minutes behind. We can live with that.> =
> Doing everything by remote connection would be easier, but we're worried>=
about the performance.> > Tony> > > On 10/11/07 6:01 PM, "Chris" gmail.com> wrote:> > > Tony Grimes wrote:> >> Have any of you tried running=
a PHP website using a remote database> >> connection?> >> > >> We currentl=
y have an in-house PHP website driven by a PostgreSQL database> >> that is =
HEAVILY administered within the office with an administration sister> >> si=
te (also PHP).> >> > >> Problem: the office connection is having trouble ke=
eping up with the website> >> traffic. Our IT guys want to outsource the wh=
ole server to a co-location> >> facility, but the administrators don't want=
the extra lag on the admin site.> >> > >> Is it feasible to host the datab=
ase and admin site in the office, but> >> outsource the website and connect=
to the office database remotely? Is there> >> any other way to do this?> >=
> > Do the website and admin area reference the same database tables?> > >=
> If they don't, split the database up and keep the website db on the> > w=
ebsite server and the other internally.> > > > If they do, which one is the=
'primary' set? ie which one gets updated?> > > > You could use replication=
to keep them in sync (http://www.slony.info/> > for example).> > -- > PHP =
Database Mailing List (http://www.php.net/)> To unsubscribe, visit: http://=
www.php.net/unsub.php>=20
____________________________________________________________ _____
Are you ready for Windows Live Messenger Beta 8.5 ? Get the latest for free=
today!
http://entertainment.sympatico.msn.ca/WindowsLiveMessenger=

--_a668f789-e86d-4ffa-af52-66611d9ad531_--

RE: Remote DB Connection: Pros and Cons?

--_1fe611ce-3716-49be-b319-d49fbf1818e2_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


> Have any of you tried running a PHP website using a remote database
> connection?

Yes. If you mean the web server is on 1 box and the database server is on =
a 2nd box.

> We currently have an in-house PHP website driven by a PostgreSQL database
> that is HEAVILY administered within the office with an administration sis=
ter
> site (also PHP).=20

We have our database servers within our medium sized company and internal w=
eb servers as well as a hosting company hosting external web servers.
All access at least 1 of our internal database servers.
We wouldn't want the 3rd party host to actually have our database {although=
remote replication is (re)considered once in a while}.

> Problem: the office connection is having trouble keeping up with the webs=
ite
> traffic. Our IT guys want to outsource the whole server to a co-location
> facility, but the administrators don't want the extra lag on the admin si=
te.
>=20
> Is it feasible to host the database and admin site in the office, but
> outsource the website and connect to the office database remotely? Is the=
re
> any other way to do this?
>=20
> Tony

Missed that before I hit reply. This _IS_ what we do.

Cons:
Some stranger at the 3rd party hosting site (or someone hacked into your re=
mote web server) could see the passwords used to connect to your internal d=
atabase.
The connection between the remote web server and local database is still a =
performance/connectivity issue.
Fun with firewall rules.

____________________________________________________________ _____
Climb to the top of the charts!=A0 Play Star Shuffle:=A0 the word scramble =
challenge with star power.
http://club.live.com/star_shuffle.aspx?icid=3Dstarshuffle_wl mailtextlink_oc=
t=

--_1fe611ce-3716-49be-b319-d49fbf1818e2_--

Re: Remote DB Connection: Pros and Cons?

Thank you all for the responses. I think we're going to try replicating the
databases with Slony. Wish me luck!

Tony


On 10/11/07 6:23 PM, "Chris" wrote:

> Tony Grimes wrote:
>> Splitting the tables probably wouldn't work. Most of the tables are used by
>> both sites and the ones that aren't are tied to the rest by foreign keys.
>>
>> Most of the updates are done from the admin site, so we're toying with the
>> idea of creating a read-only data warehouse for the website. The sync would
>> be one way from main office db to the warehouse (say, every 10 minutes).
>
> No need to create something that has already been done.
>
> Use slony or something to replicate the database.
>
>> Any updates the website does (like an event registration or profile update)
>> would be done remotely to the office db. In this scheme the website db would
>> be 10 minutes behind. We can live with that.
>
> When you do a registration, get it to use a remote database connection
> instead of the 'local' one.
>
> $local_connection = pg_connect('localhost....');
>
> $remote_connection = pg_connect('remote_server_ip....');
>
> $remote_result = pg_query($remote_connection, $query);
>
>> Doing everything by remote connection would be easier, but we're worried
>> about the performance.
>
> The only way to find out would be to test it really. As long as you have
> a decent connection in to the office and a decent connection on the
> remote end and aren't pulling through a ridiculous amount of data in
> queries (eg pulling through 50,000 records when you only really need
> 10), you should be fine.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php