it is possible to have master-master replication (which is not the same as a MySQL database cluster!) -----Oorspronkelijk bericht----- Van: otrs-bounces@otrs.org namens Sébastien Prud'homme Verzonden: wo 22-8-2007 22:30 Aan: User questions and discussions about OTRS.org Onderwerp: Re: [otrs] Database replication I'm not sure you can do this with MySQL or PostgreSQL if you want write access to the database on every location. On a master-slave configuration the slave is read-only. On a master-master (mysql cluster), you need to be on a LAN. 2007/8/22, Adriano Amaral :

Well MySQL or Postgress do a good job on replication.

check this up

http://www.howtoforge.com/mysql_database_replication- this is a Master-Slave, but you can have Master-Master replications (on Mysql)

Good Luck

Adriano

Thorsten.Dannhausen@Bertelsmann.de a écrit:

...

Hi,

i'm working for an international operating company and we want to use otrs as our global helpdesk-system. So I need a way to replicate the databases to different location so that every location has the same view, or at least almost. Has anybody done this before and give me a little hint how to accomplish this.

Many thanks in Advance

Thorsten ------------------------------------------------------------------------

_______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? => http://www.otrs.com/

--

_______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? => http://www.otrs.com/

_______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? =http://www.otrs.com/

Well what I try to accomplish is that every location has access to the same datasource to share information about the solutions. The main problem is that not all of our location are on the same network. And I really don't want to place the OTRS-System in the DMZ because we want to use it as an internal helpdesk tool. So yes we want to have an installation on every site but with the same (replicated) Database. We need to have the database on every location as some of them have a real slow connection to our headquarter. And my colleagues around the world should still be able to work with the system if there internet connection is down. But I will try it with the master-master replication and see if this will do for us. Regards Thorsten -----Ursprüngliche Nachricht----- Von: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Im Auftrag von Peter van Beugen Gesendet: Mittwoch, 22. August 2007 22:35 An: User questions and discussions about OTRS.org Betreff: RE: [otrs] Database replication I use MySQL (5.0.x) master-slave replication and it does a great job. But I use the slave as a backup server. MySQL 5.1 even promises better replication. check: http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html But I do not understand what you want to accomplish with replicated databases as OTRS is pure browser based. IMHO: put the webserver very close to the database server and you will have the best performance. Or are you going tu use multiple distributed webservers? regards, Peter van Beugen -----Oorspronkelijk bericht----- Van: otrs-bounces@otrs.org namens Thorsten.Dannhausen@Bertelsmann.de Verzonden: wo 22-8-2007 19:06 Aan: otrs@otrs.org Onderwerp: [otrs] Database replication Hi, i'm working for an international operating company and we want to use otrs as our global helpdesk-system. So I need a way to replicate the databases to different location so that every location has the same view, or at least almost. Has anybody done this before and give me a little hint how to accomplish this. Many thanks in Advance Thorsten

Well thank you Ron for this information as I'm not really familiar whith replacating databases. But the single hosted solution is something my bosses don't want at all. Our internet connection in our headquarters is reliable and redundant so that wouldn't be the problem. But what pretty often occurs is that one of our other locations are loosing there internet connectivity. As you might know it is hard to get redundancy at a payable price in some places of the world. But the location should still be able to use OTRS when there line is down as they would need it for the daily work. So any other ideas out there besides hosting a single system. -----Ursprüngliche Nachricht----- Von: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Im Auftrag von Ron DeFulio Gesendet: Donnerstag, 23. August 2007 16:27 An: 'User questions and discussions about OTRS.org' Betreff: RE: [otrs] Database replication Thorsten, I think you need to reconsider your argument for master-master replication. First, if you've got a "real slow connection" between the sites, your replication is going to be hopelessly backlogged. Viewing HTML of a single ticket is -far less data- than replicating every change made into every table from every site. Further, if the internet connection is down -forget about replication- because when mysql becomes desynched it basically does a full resync which takes HOURS on a full speed line. I have experience with mysql one-way replication and this is tedious on its own. We experimented with master-master replication on dedicated t1 lines and we had so many problems that we changed our model to a single-master and used the replicated nodes as read-only nodes (queueing the updates back to the master with a special api). If your headquarters does not have a reliable internet connection, you should consider hosting the application with an ISP that guarantees uptime. If your branch locations are without internet, they are going to be down anyway. With no connectivity to the other master databases you're going to have unresolvable synch issues where two people change the same ticket -- whose data is correct? You are expecting the OTRS application to "just work" in a master-master situation across slow or disconnected lines, let me assure you, if the OTRS programmers havent specifically built the software to work master-master in this situation, you're going to find that it screws up constantly. Take the issue of creating a new record number. Typically this process, which is what links all related tables and records together, needs to have a guaranteed unique number across the board. Without a connection, a disconnected master doesn't know what numbers have been used up. If two "record 1000's" are created in a master-master situation, you've got an unresolvable conflict. You would have to uniquely identify every site with a unique prefix, and then only allocate numbers with those prefixes. This logic would need to apply to every table where records can be added.. Users/groups/tickets/followups/customers... Practically every table. This is not "typical" design requirement. Finally, as far as intranet/extranet this is a non-issue. You secure the site at the ISP with a private cert and install a client cert onto every branch-office computer that allows them to access the site. It secures the connection and prevents access outside the company without being issued a client cert. Master-master replication is not a magic bullet to fix problems. It's a very complex environment that is useful in specific cases where the need for master-master outweighs the downsides of its implementations. Your requirements and your situation screams out for a single hosted application at a secure location shared by your branch locations. In the case of an outage, cellular wifi internet will cut it for the simple html pages served up by OTRS. I have experience doing master-master replication back in the dos days before mysql. We wrote our own software to replicate changes across a 56k dedicated line between New York and California. There is a lot behind making master-master work with any level of reliability. Its imperitive to never lose a customer's data because of replication overwrites or conflicts. This is difficult stuff to do and requires a level of granularity not typical for every-day applications. Microsoft Active Directory, for example, manages master-master replication because every field in every record is timestamped and clocks are guaranteed to be synched across the domain. Microsoft uses a special technique of generating a GUID that is unique to a particular object so there are no conflicts. These are concepts just outside the scope of OTRS and how it was intended to be used. Again you are assuming these issues will just magically resolve themselves by turning on master-master replication in MYSQL or PostgreSQL and I think you'll find this is just not the case. Good luck with your endeavor, Ron -----Original Message----- From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Thorsten.Dannhausen@Bertelsmann.de Sent: Thursday, August 23, 2007 9:29 AM To: otrs@otrs.org Subject: AW: [otrs] Database replication Well what I try to accomplish is that every location has access to the same datasource to share information about the solutions. The main problem is that not all of our location are on the same network. And I really don't want to place the OTRS-System in the DMZ because we want to use it as an internal helpdesk tool. So yes we want to have an installation on every site but with the same (replicated) Database. We need to have the database on every location as some of them have a real slow connection to our headquarter. And my colleagues around the world should still be able to work with the system if there internet connection is down. But I will try it with the master-master replication and see if this will do for us. Regards Thorsten -----Ursprüngliche Nachricht----- Von: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Im Auftrag von Peter van Beugen Gesendet: Mittwoch, 22. August 2007 22:35 An: User questions and discussions about OTRS.org Betreff: RE: [otrs] Database replication I use MySQL (5.0.x) master-slave replication and it does a great job. But I use the slave as a backup server. MySQL 5.1 even promises better replication. check: http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html But I do not understand what you want to accomplish with replicated databases as OTRS is pure browser based. IMHO: put the webserver very close to the database server and you will have the best performance. Or are you going tu use multiple distributed webservers? regards, Peter van Beugen -----Oorspronkelijk bericht----- Van: otrs-bounces@otrs.org namens Thorsten.Dannhausen@Bertelsmann.de Verzonden: wo 22-8-2007 19:06 Aan: otrs@otrs.org Onderwerp: [otrs] Database replication Hi, i'm working for an international operating company and we want to use otrs as our global helpdesk-system. So I need a way to replicate the databases to different location so that every location has the same view, or at least almost. Has anybody done this before and give me a little hint how to accomplish this. Many thanks in Advance Thorsten _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? =http://www.otrs.com/ _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? =http://www.otrs.com/

I think what you need to define is what data really need be shared between the sites. If you limit the scope of your replication to specific data, for example, the FAQ section that is not updated all the time, you could implement separate installations and only replicate the FAQ data between the sites. If your business requirements are that ticket tracking has be shared between locations, that makes things more difficult. You could also implement a failover type of scenario where you have a single master and replicate the data to your external sites when the lines are operational. When the lines are down, the local OTRS copy becomes a read only source so you have access to the shared queues and ticket data but updates cannot be made while the line is down. In other words -- the master site is available at http://master.otrs.site and while the internet lines are working, all users share the site at that location. If the internet line is down, then the disconnected user brings up http://otrs.local which is simply the last-known-copy of otrs replicated down the line from the master. This would be a read-only copy. When the line goes back up, replication will resume, and the agents can enter their updates into the master database. Finally, you might be able to do master-master replication in your situation but you will probably need to change the otrs source code to include some of the modifications I've mentioned. Having a globally unique record identifier for items is the critical one. Out of the box I don’t think OTRS is going to solve this for you but with a little programming you can probably get it to work. Step one is to define the scope of what will need to be replicated and step two is to investigate what it will take to meet that requirement. You'll need to look at the OTRS table diagram to see what ties the tables together, and enforce per-site uniqueness in between those tables. For example, if any branch #2 can append a note to a ticket created by branch #1 while branch #2 is offline, you'll need to prefix the note table with a code that uniquely identifies that the note was originated at branch #2. For example, the UniqueID could be '[ticket #]+[branch #]+[timestamp]'. Internally if OTRS uses a long-integer for these uniqueid's that would require a pretty large code change. I'm just thinking off the top of my head of some of the challenges you might face. You'll probably need a per-site table which is not replicated and contains unique-prefix information (for starters). It’s a big project, but if you've got the resources to do it, anything is possible. :) -----Original Message----- From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Thorsten.Dannhausen@Bertelsmann.de Sent: Thursday, August 23, 2007 10:46 AM To: otrs@otrs.org Subject: AW: [otrs] Database replication Well thank you Ron for this information as I'm not really familiar whith replacating databases. But the single hosted solution is something my bosses don't want at all. Our internet connection in our headquarters is reliable and redundant so that wouldn't be the problem. But what pretty often occurs is that one of our other locations are loosing there internet connectivity. As you might know it is hard to get redundancy at a payable price in some places of the world. But the location should still be able to use OTRS when there line is down as they would need it for the daily work. So any other ideas out there besides hosting a single system. -----Ursprüngliche Nachricht----- Von: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Im Auftrag von Ron DeFulio Gesendet: Donnerstag, 23. August 2007 16:27 An: 'User questions and discussions about OTRS.org' Betreff: RE: [otrs] Database replication Thorsten, I think you need to reconsider your argument for master-master replication. First, if you've got a "real slow connection" between the sites, your replication is going to be hopelessly backlogged. Viewing HTML of a single ticket is -far less data- than replicating every change made into every table from every site. Further, if the internet connection is down -forget about replication- because when mysql becomes desynched it basically does a full resync which takes HOURS on a full speed line. I have experience with mysql one-way replication and this is tedious on its own. We experimented with master-master replication on dedicated t1 lines and we had so many problems that we changed our model to a single-master and used the replicated nodes as read-only nodes (queueing the updates back to the master with a special api). If your headquarters does not have a reliable internet connection, you should consider hosting the application with an ISP that guarantees uptime. If your branch locations are without internet, they are going to be down anyway. With no connectivity to the other master databases you're going to have unresolvable synch issues where two people change the same ticket -- whose data is correct? You are expecting the OTRS application to "just work" in a master-master situation across slow or disconnected lines, let me assure you, if the OTRS programmers havent specifically built the software to work master-master in this situation, you're going to find that it screws up constantly. Take the issue of creating a new record number. Typically this process, which is what links all related tables and records together, needs to have a guaranteed unique number across the board. Without a connection, a disconnected master doesn't know what numbers have been used up. If two "record 1000's" are created in a master-master situation, you've got an unresolvable conflict. You would have to uniquely identify every site with a unique prefix, and then only allocate numbers with those prefixes. This logic would need to apply to every table where records can be added.. Users/groups/tickets/followups/customers... Practically every table. This is not "typical" design requirement. Finally, as far as intranet/extranet this is a non-issue. You secure the site at the ISP with a private cert and install a client cert onto every branch-office computer that allows them to access the site. It secures the connection and prevents access outside the company without being issued a client cert. Master-master replication is not a magic bullet to fix problems. It's a very complex environment that is useful in specific cases where the need for master-master outweighs the downsides of its implementations. Your requirements and your situation screams out for a single hosted application at a secure location shared by your branch locations. In the case of an outage, cellular wifi internet will cut it for the simple html pages served up by OTRS. I have experience doing master-master replication back in the dos days before mysql. We wrote our own software to replicate changes across a 56k dedicated line between New York and California. There is a lot behind making master-master work with any level of reliability. Its imperitive to never lose a customer's data because of replication overwrites or conflicts. This is difficult stuff to do and requires a level of granularity not typical for every-day applications. Microsoft Active Directory, for example, manages master-master replication because every field in every record is timestamped and clocks are guaranteed to be synched across the domain. Microsoft uses a special technique of generating a GUID that is unique to a particular object so there are no conflicts. These are concepts just outside the scope of OTRS and how it was intended to be used. Again you are assuming these issues will just magically resolve themselves by turning on master-master replication in MYSQL or PostgreSQL and I think you'll find this is just not the case. Good luck with your endeavor, Ron -----Original Message----- From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Thorsten.Dannhausen@Bertelsmann.de Sent: Thursday, August 23, 2007 9:29 AM To: otrs@otrs.org Subject: AW: [otrs] Database replication Well what I try to accomplish is that every location has access to the same datasource to share information about the solutions. The main problem is that not all of our location are on the same network. And I really don't want to place the OTRS-System in the DMZ because we want to use it as an internal helpdesk tool. So yes we want to have an installation on every site but with the same (replicated) Database. We need to have the database on every location as some of them have a real slow connection to our headquarter. And my colleagues around the world should still be able to work with the system if there internet connection is down. But I will try it with the master-master replication and see if this will do for us. Regards Thorsten -----Ursprüngliche Nachricht----- Von: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Im Auftrag von Peter van Beugen Gesendet: Mittwoch, 22. August 2007 22:35 An: User questions and discussions about OTRS.org Betreff: RE: [otrs] Database replication I use MySQL (5.0.x) master-slave replication and it does a great job. But I use the slave as a backup server. MySQL 5.1 even promises better replication. check: http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html But I do not understand what you want to accomplish with replicated databases as OTRS is pure browser based. IMHO: put the webserver very close to the database server and you will have the best performance. Or are you going tu use multiple distributed webservers? regards, Peter van Beugen -----Oorspronkelijk bericht----- Van: otrs-bounces@otrs.org namens Thorsten.Dannhausen@Bertelsmann.de Verzonden: wo 22-8-2007 19:06 Aan: otrs@otrs.org Onderwerp: [otrs] Database replication Hi, i'm working for an international operating company and we want to use otrs as our global helpdesk-system. So I need a way to replicate the databases to different location so that every location has the same view, or at least almost. Has anybody done this before and give me a little hint how to accomplish this. Many thanks in Advance Thorsten _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? =http://www.otrs.com/ _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? =http://www.otrs.com/ _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? =http://www.otrs.com/