database replication setup help

Hi. For the last several years, we've been running an Informix database for our accounting and production systems. We have two primary locations, which for the purpose of this email I'll call HQ and Site1. Site1 is connected to HQ over a WAN link (T1, etc), but we are investigating possible options to change this to use Informix's replication (mainly to speed up access). HQ and Site1 both work on the same data sets, so in the event the line goes down, we would prefer Site1 be read-only to avoid any data conflicts. Additionally, we like to add redundancy in the HQ location to prevent down-time in the event of server hardware failure, etc. After some research and reading, here's what we've come up with: HQ location: 1 HDR primary server 1 HDR secondary server Site1: 1 RSS server (readonly) Does this sound reasonable? When a program is run from Site1, and an update/insert/delete statement is run, how do we set it up so that the statement gets sent up to the HQ primary transparently? I read that schema change propagation only happens in Flexible Grid and not in ER, so when a schema change is made on the HDR primary, does it automatically propagate down to the secondary and RSS servers? I'm a little confused about the difference between Flexible Grid and ER and how they work together. Thanks In Advance, -Justin

OK, here are some points: - If the RSS or HDR secondary is writable, the writes are still happening on the primary (passed through by the secondary) and then propagate to the secondaries including the secondary the originated the write. There is no danger of the secondary getting out-of-sync with the primary and having local transactions that the primary doesn't know about. If the WAN goes down, you could still use the local RSS secondary at Site1 as a read-only query server, but would have to get the WAN back up in order to process updates locally again. - If you make the secondary read-only, then you apps will have to be aware of that and either update the primary using distributed queries (ie access the table's using the primary's servername: insert into mydatabase@primary:mytable ...) or they could keep two connections open (assuming the host language supports multiple connections (ESQL/C, C++, all 4GL variants, and Java do) one locally on the secondary for reading and one remoted directly to the primary for updating. The writable option is actually preferrable. - You could use ER. If the WAN goes down you could continue to process transactions locally, but when the LAN comes back up then conflict resolution rules would have to resolve any problems caused by rows updated on multiple servers. - HDR and RSS secondaries will remain in sync for schema changes because all logical log records (which include all DDL commands) are propagated to the secondaries and applied there replicating changes happening on the primary. Only index builds are slightly different. They can be configured such that the secondary just gets notice of a new index and has to build it locally or such that the actual log records generated by the index build are sent over to the secondary replicating the index page-by-page. Your choice. Indexes built the first way, the default, will remain locked on the primary until the secondaries complete their build (unless the connection is down). - Flexible Grid is just a new internal communication protocol that lets you issue commands that are processed across all servers on a named grid. Those servers can be ER participants or independent servers. Prior to v12.10 this could only be used for ER commands and DDL. In 12.10 you can also distribute queries across the grid and return data from multiple servers with a single SELECT. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 23, 2013 at 1:31 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > Hi. > > For the last several years, we've been running an Informix database for our > accounting and production systems. We have two primary locations, which for > the purpose of this email I'll call HQ and Site1. Site1 is connected to HQ > over a WAN link (T1, etc), but we are investigating possible options to > change > this to use Informix's replication (mainly to speed up access). HQ and > Site1 > both work on the same data sets, so in the event the line goes down, we > would > prefer Site1 be read-only to avoid any data conflicts. Additionally, we > like > to add redundancy in the HQ location to prevent down-time in the event of > server hardware failure, etc. > > After some research and reading, here's what we've come up with: > > HQ location: > 1 HDR primary server > 1 HDR secondary server > > Site1: > 1 RSS server (readonly) > > Does this sound reasonable? > When a program is run from Site1, and an update/insert/delete statement is > run, how do we set it up so that the statement gets sent up to the HQ > primary > transparently? > I read that schema change propagation only happens in Flexible Grid and > not in > ER, so when a schema change is made on the HDR primary, does it > automatically > propagate down to the secondary and RSS servers? I'm a little confused > about > the difference between Flexible Grid and ER and how they work together. > > Thanks In Advance, > -Justin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160a3b8e43e8c04dd6658f1

Along with what Art said, you would need to set up the sqlhosts file on all the servers involved so that they know about eachother. The services file on the two secondary servers also need to be set up similar to the primary. If your secondary servers are intended to be read only, you can set up the servers as you suggest or have two HDR secondaries. One of the major differences that you will see is that the HDR secondary is guaranteed to be in sync, but may slow down transaction processing on the primary, slightly, depending upon your network. The RSS secondary could possibly be out of sync slightly since transactions on the primary are processed first and then sent to the RSS secondary. If you want the secondary servers to be exactly in sync at all times, you should have two HDR secondaries. To have your applications at site1 still point to the primary, you just need the correct sqlhosts entries and your environment variables set up correctly. If your primary server goes down and you would like to promote a secondary, you just go through the process on that secondary to promote it, and then change your environment variables to point at it instead of the original primary. I hope this helps. Larry Sorensen > To: ids@iiug.org > From: jkillen@allamericanasphalt.com > Subject: database replication setup help [30326] > Date: Thu, 23 May 2013 13:31:24 -0400 > > Hi. > > For the last several years, we've been running an Informix database for our > accounting and production systems. We have two primary locations, which for > the purpose of this email I'll call HQ and Site1. Site1 is connected to HQ > over a WAN link (T1, etc), but we are investigating possible options to change > this to use Informix's replication (mainly to speed up access). HQ and Site1 > both work on the same data sets, so in the event the line goes down, we would > prefer Site1 be read-only to avoid any data conflicts. Additionally, we like > to add redundancy in the HQ location to prevent down-time in the event of > server hardware failure, etc. > > After some research and reading, here's what we've come up with: > > HQ location: > 1 HDR primary server > 1 HDR secondary server > > Site1: > 1 RSS server (readonly) > > Does this sound reasonable? > When a program is run from Site1, and an update/insert/delete statement is > run, how do we set it up so that the statement gets sent up to the HQ primary > transparently? > I read that schema change propagation only happens in Flexible Grid and not in > ER, so when a schema change is made on the HDR primary, does it automatically > propagate down to the secondary and RSS servers? I'm a little confused about > the difference between Flexible Grid and ER and how they work together. > > Thanks In Advance, > -Justin > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

If you want the applications to be able to automatically perform updati= ng activity from Site1, then you need to allow the RSS server to be updata= ble, because what you described (automatically perform updates on the primar= y) is exactly what updatable secondarys do. From: "Justin Killen" <jkillen@allamericanasphalt.com> To: ids@iiug.org, Date: 05/23/2013 12:37 PM Subject: database replication setup help [30326] Sent by: ids-bounces@iiug.org Hi. For the last several years, we've been running an Informix database for= our accounting and production systems. We have two primary locations, which= for the purpose of this email I'll call HQ and Site1. Site1 is connected to= HQ over a WAN link (T1, etc), but we are investigating possible options to= change this to use Informix's replication (mainly to speed up access). HQ and Site1 both work on the same data sets, so in the event the line goes down, we= would prefer Site1 be read-only to avoid any data conflicts. Additionally, we= like to add redundancy in the HQ location to prevent down-time in the event = of server hardware failure, etc. After some research and reading, here's what we've come up with: HQ location: 1 HDR primary server 1 HDR secondary server Site1: 1 RSS server (readonly) Does this sound reasonable? When a program is run from Site1, and an update/insert/delete statement= is run, how do we set it up so that the statement gets sent up to the HQ primary transparently? I read that schema change propagation only happens in Flexible Grid and= not in ER, so when a schema change is made on the HDR primary, does it automatically propagate down to the secondary and RSS servers? I'm a little confused about the difference between Flexible Grid and ER and how they work together.= Thanks In Advance, -Justin ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Larry: You can ONLY have a single HDR secondary, all other secondary servers have to be RSS. Also, for simplicity in failover, if the primary server goes down, rather than the WAN, you would want the INFORMIXSERVER pointing to a group that includes all three servers in the order you want to failover to them or you want to be using Connection Manager to manage the server and application failover. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 23, 2013 at 2:05 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > Along with what Art said, you would need to set up the sqlhosts file on all > the servers involved so that they know about eachother. The services file > on > the two secondary servers also need to be set up similar to the primary. > > If your secondary servers are intended to be read only, you can set up the > servers as you suggest or have two HDR secondaries. One of the major > differences that you will see is that the HDR secondary is guaranteed to > be in > sync, but may slow down transaction processing on the primary, slightly, > depending upon your network. The RSS secondary could possibly be out of > sync > slightly since transactions on the primary are processed first and then > sent > to the RSS secondary. If you want the secondary servers to be exactly in > sync > at all times, you should have two HDR secondaries. > > To have your applications at site1 still point to the primary, you just > need > the correct sqlhosts entries and your environment variables set up > correctly. > If your primary server goes down and you would like to promote a secondary, > you just go through the process on that secondary to promote it, and then > change your environment variables to point at it instead of the original > primary. > > I hope this helps. > > Larry Sorensen > > > To: ids@iiug.org > > From: jkillen@allamericanasphalt.com > > Subject: database replication setup help [30326] > > Date: Thu, 23 May 2013 13:31:24 -0400 > > > > Hi. > > > > For the last several years, we've been running an Informix database for > our > > accounting and production systems. We have two primary locations, which > for > > the purpose of this email I'll call HQ and Site1. Site1 is connected to > HQ > > over a WAN link (T1, etc), but we are investigating possible options to > change > > this to use Informix's replication (mainly to speed up access). HQ and > Site1 > > both work on the same data sets, so in the event the line goes down, we > would > > prefer Site1 be read-only to avoid any data conflicts. Additionally, we > like > > to add redundancy in the HQ location to prevent down-time in the event of > > server hardware failure, etc. > > > > After some research and reading, here's what we've come up with: > > > > HQ location: > > 1 HDR primary server > > 1 HDR secondary server > > > > Site1: > > 1 RSS server (readonly) > > > > Does this sound reasonable? > > When a program is run from Site1, and an update/insert/delete statement > is > > run, how do we set it up so that the statement gets sent up to the HQ > primary > > transparently? > > I read that schema change propagation only happens in Flexible Grid and > not > in > > ER, so when a schema change is made on the HDR primary, does it > automatically > > propagate down to the secondary and RSS servers? I'm a little confused > about > > the difference between Flexible Grid and ER and how they work together. > > > > Thanks In Advance, > > -Justin > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158c738be0a8604dd66ea82

Art, You say "If the RSS or HDR secondary is writable, the writes are still happening on the primary" and then later "If the WAN goes down you could continue to process transactions locally". I'm confused by this - if all writes are happening on the primary, then how could I continue to process transaction locally? I guess my big question here is if I should be using an RSS at Site1 or an HDR secondary. I was under the impression that if I was using an HDR secondary and the WAN link went down, then the HDR primary at HQ would not be able to process transactions (insert/update/delete) because it could not sync with the offline HDR secondary at Site1 - am I wrong on this? -Justin -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, May 23, 2013 10:53 AM To: ids@iiug.org Subject: Re: database replication setup help [30329] OK, here are some points: - If the RSS or HDR secondary is writable, the writes are still happening on the primary (passed through by the secondary) and then propagate to the secondaries including the secondary the originated the write. There is no danger of the secondary getting out-of-sync with the primary and having local transactions that the primary doesn't know about. If the WAN goes down, you could still use the local RSS secondary at Site1 as a read-only query server, but would have to get the WAN back up in order to process updates locally again. - If you make the secondary read-only, then you apps will have to be aware of that and either update the primary using distributed queries (ie access the table's using the primary's servername: insert into mydatabase@primary:mytable ...) or they could keep two connections open (assuming the host language supports multiple connections (ESQL/C, C++, all 4GL variants, and Java do) one locally on the secondary for reading and one remoted directly to the primary for updating. The writable option is actually preferrable. - You could use ER. If the WAN goes down you could continue to process transactions locally, but when the LAN comes back up then conflict resolution rules would have to resolve any problems caused by rows updated on multiple servers. - HDR and RSS secondaries will remain in sync for schema changes because all logical log records (which include all DDL commands) are propagated to the secondaries and applied there replicating changes happening on the primary. Only index builds are slightly different. They can be configured such that the secondary just gets notice of a new index and has to build it locally or such that the actual log records generated by the index build are sent over to the secondary replicating the index page-by-page. Your choice. Indexes built the first way, the default, will remain locked on the primary until the secondaries complete their build (unless the connection is down). - Flexible Grid is just a new internal communication protocol that lets you issue commands that are processed across all servers on a named grid. Those servers can be ER participants or independent servers. Prior to v12.10 this could only be used for ER commands and DDL. In 12.10 you can also distribute queries across the grid and return data from multiple servers with a single SELECT. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 23, 2013 at 1:31 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > Hi. > > For the last several years, we've been running an Informix database for our > accounting and production systems. We have two primary locations, which for > the purpose of this email I'll call HQ and Site1. Site1 is connected to HQ > over a WAN link (T1, etc), but we are investigating possible options to > change > this to use Informix's replication (mainly to speed up access). HQ and > Site1 > both work on the same data sets, so in the event the line goes down, we > would > prefer Site1 be read-only to avoid any data conflicts. Additionally, we > like > to add redundancy in the HQ location to prevent down-time in the event of > server hardware failure, etc. > > After some research and reading, here's what we've come up with: > > HQ location: > 1 HDR primary server > 1 HDR secondary server > > Site1: > 1 RSS server (readonly) > > Does this sound reasonable? > When a program is run from Site1, and an update/insert/delete statement is > run, how do we set it up so that the statement gets sent up to the HQ > primary > transparently? > I read that schema change propagation only happens in Flexible Grid and > not in > ER, so when a schema change is made on the HDR primary, does it > automatically > propagate down to the secondary and RSS servers? I'm a little confused > about > the difference between Flexible Grid and ER and how they work together. > > Thanks In Advance, > -Justin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160a3b8e43e8c04dd6658f1 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

First, you are misreading and combining two of my points. As relates to RSS secondaries (same applies to HDR secondary BTW), I said: "If the WAN goes down, you could still use the local RSS secondary at Site1 as a read-only query server, but would have to get the WAN back up in order to process updates (read that as transactions) locally again." I only said that you could "continue to process transactions locally" with reference to ER nodes where you might run into the conflict issues that you alluded to in your post. Now, I mentioned this when Larry said you could have two HDR secondaries, but let me repeat it: YOU CANNOT HAVE MORE THAN ONE HDR SECONDARY CONFIGURED AT THE SAME TIME! After the one, single, lone, solitary (did I emphasize that enough?) HDR secondary all other secondary servers have to be either SDS secondaries attached to a primary server's disk drives or RSS secondaries which is what you want to configure at Site1. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 23, 2013 at 2:43 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > Art, > > You say "If the RSS or HDR secondary is writable, the writes are still > happening on the primary" and then later "If the WAN goes down you could > continue to process transactions locally". I'm confused by this - if all > writes are happening on the primary, then how could I continue to process > transaction locally? > > I guess my big question here is if I should be using an RSS at Site1 or an > HDR > secondary. I was under the impression that if I was using an HDR secondary > and > the WAN link went down, then the HDR primary at HQ would not be able to > process transactions (insert/update/delete) because it could not sync with > the > offline HDR secondary at Site1 - am I wrong on this? > > -Justin > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Thursday, May 23, 2013 10:53 AM > To: ids@iiug.org > Subject: Re: database replication setup help [30329] > > OK, here are some points: > > - If the RSS or HDR secondary is writable, the writes are still > > happening on the primary (passed through by the secondary) and then > > propagate to the secondaries including the secondary the originated the > > write. There is no danger of the secondary getting out-of-sync with the > > primary and having local transactions that the primary doesn't know about. > > If the WAN goes down, you could still use the local RSS secondary at Site1 > > as a read-only query server, but would have to get the WAN back up in order > > to process updates locally again. > > - If you make the secondary read-only, then you apps will have to be > > aware of that and either update the primary using distributed queries (ie > > access the table's using the primary's servername: insert into > > mydatabase@primary:mytable ...) or they could keep two connections open > > (assuming the host language supports multiple connections (ESQL/C, C++, all > > 4GL variants, and Java do) one locally on the secondary for reading and one > > remoted directly to the primary for updating. The writable option is > > actually preferrable. > > - You could use ER. If the WAN goes down you could continue to process > > transactions locally, but when the LAN comes back up then conflict > > resolution rules would have to resolve any problems caused by rows updated > > on multiple servers. > > - HDR and RSS secondaries will remain in sync for schema changes because > > all logical log records (which include all DDL commands) are propagated to > > the secondaries and applied there replicating changes happening on the > > primary. Only index builds are slightly different. They can be configured > > such that the secondary just gets notice of a new index and has to build it > > locally or such that the actual log records generated by the index build > > are sent over to the secondary replicating the index page-by-page. Your > > choice. Indexes built the first way, the default, will remain locked on > > the primary until the secondaries complete their build (unless the > > connection is down). > > - Flexible Grid is just a new internal communication protocol that lets > > you issue commands that are processed across all servers on a named grid. > > Those servers can be ER participants or independent servers. Prior to > > v12.10 this could only be used for ER commands and DDL. In 12.10 you can > > also distribute queries across the grid and return data from multiple > > servers with a single SELECT. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Thu, May 23, 2013 at 1:31 PM, Justin Killen < > jkillen@allamericanasphalt.com> wrote: > > > Hi. > > > > For the last several years, we've been running an Informix database for > our > > accounting and production systems. We have two primary locations, which > for > > the purpose of this email I'll call HQ and Site1. Site1 is connected to > HQ > > over a WAN link (T1, etc), but we are investigating possible options to > > change > > this to use Informix's replication (mainly to speed up access). HQ and > > Site1 > > both work on the same data sets, so in the event the line goes down, we > > would > > prefer Site1 be read-only to avoid any data conflicts. Additionally, we > > like > > to add redundancy in the HQ location to prevent down-time in the event of > > server hardware failure, etc. > > > > After some research and reading, here's what we've come up with: > > > > HQ location: > > 1 HDR primary server > > 1 HDR secondary server > > > > Site1: > > 1 RSS server (readonly) > > > > Does this sound reasonable? > > When a program is run from Site1, and an update/insert/delete statement > is > > run, how do we set it up so that the statement gets sent up to the HQ > > primary > > transparently? > > I read that schema change propagation only happens in Flexible Grid and > > not in > > ER, so when a schema change is made on the HDR primary, does it

If you run HDR to "Site1" and your WAN drops, your HDR primary should just continue to run. What it will do is hold the logs until the WAN is back and the secondary asks for them. If the logs turn over while the secondary is down, you have to either replay logs from tape on the secondary or do a physical restore of the database on the secondary to restart replication. --EEM -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Justin Killen Sent: Thursday, May 23, 2013 1:43 PM To: ids@iiug.org Subject: RE: database replication setup help [30334] Art, You say "If the RSS or HDR secondary is writable, the writes are still happening on the primary" and then later "If the WAN goes down you could continue to process transactions locally". I'm confused by this - if all writes are happening on the primary, then how could I continue to process transaction locally? I guess my big question here is if I should be using an RSS at Site1 or an HDR secondary. I was under the impression that if I was using an HDR secondary and the WAN link went down, then the HDR primary at HQ would not be able to process transactions (insert/update/delete) because it could not sync with the offline HDR secondary at Site1 - am I wrong on this? -Justin -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, May 23, 2013 10:53 AM To: ids@iiug.org Subject: Re: database replication setup help [30329] OK, here are some points: - If the RSS or HDR secondary is writable, the writes are still happening on the primary (passed through by the secondary) and then propagate to the secondaries including the secondary the originated the write. There is no danger of the secondary getting out-of-sync with the primary and having local transactions that the primary doesn't know about. If the WAN goes down, you could still use the local RSS secondary at Site1 as a read-only query server, but would have to get the WAN back up in order to process updates locally again. - If you make the secondary read-only, then you apps will have to be aware of that and either update the primary using distributed queries (ie access the table's using the primary's servername: insert into mydatabase@primary:mytable ...) or they could keep two connections open (assuming the host language supports multiple connections (ESQL/C, C++, all 4GL variants, and Java do) one locally on the secondary for reading and one remoted directly to the primary for updating. The writable option is actually preferrable. - You could use ER. If the WAN goes down you could continue to process transactions locally, but when the LAN comes back up then conflict resolution rules would have to resolve any problems caused by rows updated on multiple servers. - HDR and RSS secondaries will remain in sync for schema changes because all logical log records (which include all DDL commands) are propagated to the secondaries and applied there replicating changes happening on the primary. Only index builds are slightly different. They can be configured such that the secondary just gets notice of a new index and has to build it locally or such that the actual log records generated by the index build are sent over to the secondary replicating the index page-by-page. Your choice. Indexes built the first way, the default, will remain locked on the primary until the secondaries complete their build (unless the connection is down). - Flexible Grid is just a new internal communication protocol that lets you issue commands that are processed across all servers on a named grid. Those servers can be ER participants or independent servers. Prior to v12.10 this could only be used for ER commands and DDL. In 12.10 you can also distribute queries across the grid and return data from multiple servers with a single SELECT. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 23, 2013 at 1:31 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > Hi. > > For the last several years, we've been running an Informix database > for our accounting and production systems. We have two primary > locations, which for the purpose of this email I'll call HQ and Site1. > Site1 is connected to HQ over a WAN link (T1, etc), but we are > investigating possible options to change this to use Informix's > replication (mainly to speed up access). HQ and > Site1 > both work on the same data sets, so in the event the line goes down, > we would prefer Site1 be read-only to avoid any data conflicts. > Additionally, we like to add redundancy in the HQ location to prevent > down-time in the event of server hardware failure, etc. > > After some research and reading, here's what we've come up with: > > HQ location: > 1 HDR primary server > 1 HDR secondary server > > Site1: > 1 RSS server (readonly) > > Does this sound reasonable? > When a program is run from Site1, and an update/insert/delete > statement is run, how do we set it up so that the statement gets sent > up to the HQ primary transparently? > I read that schema change propagation only happens in Flexible Grid > and not in ER, so when a schema change is made on the HDR primary, > does it automatically propagate down to the secondary and RSS servers? > I'm a little confused about the difference between Flexible Grid and > ER and how they work together. > > Thanks In Advance, > -Justin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160a3b8e43e8c04dd6658f1 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Art, I'm sorry if I offended, that was not my intention. I see your contributions to this community and I respect your advice and opinions. I am quite certain that I am just simply misunderstanding some core concept (in my defense, IBM has done a pretty good job of providing an abundance of acronyms and options as to make everything complicated IMHO). So then, when you say I can continue to process transactions, you mean that the primary HDR at HQ continues to run, even in the event that the connection to an HDR secondary/RSS is severed, and that any nodes connected to the now disconnected HDR secondary/RSS will work for read requests but get an error on write requests. The question here then is what's the difference between an HDR secondary and an RSS? I thought the difference was that when applying a transaction, the changes were guaranteed on a secondary because it happens at the same time as the primary update, but for RSS it is not guaranteed because the change is delayed? -Justin -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, May 23, 2013 11:55 AM To: ids@iiug.org Subject: Re: database replication setup help [30335] First, you are misreading and combining two of my points. As relates to RSS secondaries (same applies to HDR secondary BTW), I said: "If the WAN goes down, you could still use the local RSS secondary at Site1 as a read-only query server, but would have to get the WAN back up in order to process updates (read that as transactions) locally again." I only said that you could "continue to process transactions locally" with reference to ER nodes where you might run into the conflict issues that you alluded to in your post. Now, I mentioned this when Larry said you could have two HDR secondaries, but let me repeat it: YOU CANNOT HAVE MORE THAN ONE HDR SECONDARY CONFIGURED AT THE SAME TIME! After the one, single, lone, solitary (did I emphasize that enough?) HDR secondary all other secondary servers have to be either SDS secondaries attached to a primary server's disk drives or RSS secondaries which is what you want to configure at Site1. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 23, 2013 at 2:43 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > Art, > > You say "If the RSS or HDR secondary is writable, the writes are still > happening on the primary" and then later "If the WAN goes down you could > continue to process transactions locally". I'm confused by this - if all > writes are happening on the primary, then how could I continue to process > transaction locally? > > I guess my big question here is if I should be using an RSS at Site1 or an > HDR > secondary. I was under the impression that if I was using an HDR secondary > and > the WAN link went down, then the HDR primary at HQ would not be able to > process transactions (insert/update/delete) because it could not sync with > the > offline HDR secondary at Site1 - am I wrong on this? > > -Justin > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Thursday, May 23, 2013 10:53 AM > To: ids@iiug.org > Subject: Re: database replication setup help [30329] > > OK, here are some points: > > - If the RSS or HDR secondary is writable, the writes are still > > happening on the primary (passed through by the secondary) and then > > propagate to the secondaries including the secondary the originated the > > write. There is no danger of the secondary getting out-of-sync with the > > primary and having local transactions that the primary doesn't know about. > > If the WAN goes down, you could still use the local RSS secondary at Site1 > > as a read-only query server, but would have to get the WAN back up in order > > to process updates locally again. > > - If you make the secondary read-only, then you apps will have to be > > aware of that and either update the primary using distributed queries (ie > > access the table's using the primary's servername: insert into > > mydatabase@primary:mytable ...) or they could keep two connections open > > (assuming the host language supports multiple connections (ESQL/C, C++, all > > 4GL variants, and Java do) one locally on the secondary for reading and one > > remoted directly to the primary for updating. The writable option is > > actually preferrable. > > - You could use ER. If the WAN goes down you could continue to process > > transactions locally, but when the LAN comes back up then conflict > > resolution rules would have to resolve any problems caused by rows updated > > on multiple servers. > > - HDR and RSS secondaries will remain in sync for schema changes because > > all logical log records (which include all DDL commands) are propagated to > > the secondaries and applied there replicating changes happening on the > > primary. Only index builds are slightly different. They can be configured > > such that the secondary just gets notice of a new index and has to build it > > locally or such that the actual log records generated by the index build > > are sent over to the secondary replicating the index page-by-page. Your > > choice. Indexes built the first way, the default, will remain locked on > > the primary until the secondaries complete their build (unless the > > connection is down). > > - Flexible Grid is just a new internal communication protocol that lets > > you issue commands that are processed across all servers on a named grid. > > Those servers can be ER participants or independent servers. Prior to > > v12.10 this could only be used for ER commands and DDL. In 12.10 you can > > also distribute queries across the grid and return data from multiple > > servers with a single SELECT. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Thu, May 23, 2013 at 1:31 PM, Justin Killen < > jkillen@allamericanasphalt.com> wrote: > > > Hi. > > > > For the last several years, we've been running an Informix database for > our@@NL

No offense taken, I guess I'm just in a cranky mood. Not your fault dude. Let's take this offline. Will contact you directly. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 23, 2013 at 4:03 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > Art, > > I'm sorry if I offended, that was not my intention. I see your > contributions > to this community and I respect your advice and opinions. I am quite > certain > that I am just simply misunderstanding some core concept (in my defense, > IBM > has done a pretty good job of providing an abundance of acronyms and > options > as to make everything complicated IMHO). > > So then, when you say I can continue to process transactions, you mean that > the primary HDR at HQ continues to run, even in the event that the > connection > to an HDR secondary/RSS is severed, and that any nodes connected to the now > disconnected HDR secondary/RSS will work for read requests but get an > error on > write requests. The question here then is what's the difference between an > HDR > secondary and an RSS? I thought the difference was that when applying a > transaction, the changes were guaranteed on a secondary because it happens > at > the same time as the primary update, but for RSS it is not guaranteed > because > the change is delayed? > > -Justin > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Thursday, May 23, 2013 11:55 AM > To: ids@iiug.org > Subject: Re: database replication setup help [30335] > > First, you are misreading and combining two of my points. As relates to > RSS secondaries (same applies to HDR secondary BTW), I said: "If the WAN > goes down, you could still use the local RSS secondary at Site1 as a > read-only query server, but would have to get the WAN back up in order to > process updates (read that as transactions) locally again." I only said > that you could "continue to process transactions locally" with reference to > ER nodes where you might run into the conflict issues that you alluded to > in your post. > > Now, I mentioned this when Larry said you could have two HDR secondaries, > but let me repeat it: > > YOU CANNOT HAVE MORE THAN ONE HDR SECONDARY CONFIGURED AT THE SAME TIME! > > After the one, single, lone, solitary (did I emphasize that enough?) HDR > secondary all other secondary servers have to be either SDS secondaries > attached to a primary server's disk drives or RSS secondaries which is what > you want to configure at Site1. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Thu, May 23, 2013 at 2:43 PM, Justin Killen < > jkillen@allamericanasphalt.com> wrote: > > > Art, > > > > You say "If the RSS or HDR secondary is writable, the writes are still > > happening on the primary" and then later "If the WAN goes down you could > > continue to process transactions locally". I'm confused by this - if all > > writes are happening on the primary, then how could I continue to process > > transaction locally? > > > > I guess my big question here is if I should be using an RSS at Site1 or > an > > HDR > > secondary. I was under the impression that if I was using an HDR > secondary > > and > > the WAN link went down, then the HDR primary at HQ would not be able to > > process transactions (insert/update/delete) because it could not sync > with > > the > > offline HDR secondary at Site1 - am I wrong on this? > > > > -Justin > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art > > Kagel > > Sent: Thursday, May 23, 2013 10:53 AM > > To: ids@iiug.org > > Subject: Re: database replication setup help [30329] > > > > OK, here are some points: > > > > - If the RSS or HDR secondary is writable, the writes are still > > > > happening on the primary (passed through by the secondary) and then > > > > propagate to the secondaries including the secondary the originated the > > > > write. There is no danger of the secondary getting out-of-sync with the > > > > primary and having local transactions that the primary doesn't know > about. > > > > If the WAN goes down, you could still use the local RSS secondary at > Site1 > > > > as a read-only query server, but would have to get the WAN back up in > order > > > > to process updates locally again. > > > > - If you make the secondary read-only, then you apps will have to be > > > > aware of that and either update the primary using distributed queries (ie > > > > access the table's using the primary's servername: insert into > > > > mydatabase@primary:mytable ...) or they could keep two connections open > > > > (assuming the host language supports multiple connections (ESQL/C, C++, > all > > > > 4GL variants, and Java do) one locally on the secondary for reading and > one > > > > remoted directly to the primary for updating. The writable option is > > > > actually preferrable. > > > > - You could use ER. If the WAN goes down you could continue to process > > > > transactions locally, but when the LAN comes back up then conflict > > > > resolution rules would have to resolve any problems caused by rows > updated > > > > on multiple servers. > > > > - HDR and RSS secondaries will remain in sync for schema changes because > > > > all logical log records (which include all DDL commands) are propagated > to > > > > the secondaries and applied there replicating changes happening on the > > > > primary. Only index builds are slightly different. They can be configured > > > > such that the secondary just gets notice of a new index and has to build > it > > > > locally or such that the actual log records generated by the index build > > > > are sent over to the secondary replicating the index page-by-page. Your > > > > choice. Indexes built the first way, the default, will remain locked on > > > > the primary until the secondaries complete their build (unless the > > > > connection is down). > > > > - Flexible Grid is just a ne