Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Question Best Aproach? Centralized Database, Cluster, Replication

    Hi, We are going to start a new development for our company in the next months and we are in the first stage trying to define what's the best aproach to solve the next problem. So first let me explain our situation:

    We have some service centers geographically separated. They have a client software that stores the services, clients, sales, etc. produced in that center in some persistent place. The important point here is that all catalogs should be able to be in a centralized database because we will have a web app used by the main manager to query all the movements produced in all the service centers and at the same time we dont want duplication of clients, prices, etc, between centers.

    We can't use only a centralized database because without some catalogs we can't operate and it's a defined prerequisite that, doesn't matter our broadband access is alive or not we should be able to continue selling.

    The possible solutions we have found to the moment:

    Emulate Clustering using replication: In my experience using replication is painfull if your topology is not as simple as just duplicate data between the production database and the backup database for example. Having multislave multi master topologies is hard to configure and sometimes the data base dont even support this.

    Clustering: I have never use Clustering but for what I know you need to drop referencial integrity to use clustering and all the database must be in Main Memory, that's a heavy issue because when I talk about service centers sometimes they are not more than a terminal with one operator that uses an old old old pc.

    Centralize everithing: doesnt seem to be the solution because if we lost connectivity we cant sell.

    Have some centralized data and some local data. Replicate the local data to the central warehouse and replicate the central data to the service centers: We thing maybe this is the best aproach but this do not resolve completely the problem because if at some point we need to create non operative data like customers and theres no connectivity to central we wont be able to proceed. In the other hand the complexity of the software will increase substancially.

    We are struggling trying to find the best way to get this kind of topology work and thinking twise I'm sure I'm not the first human being that have this kind of problematic, indeed I almost sure this problem is a recurrent one.

    Could you provide me some advise?

    Sorry for the trash english.....

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    This is exactly the problem that Microsoft's Merge Replication is designed to solve. If you use GUID values as surrogate keys to allow any machine to safely add new data, and UTC time to avoid the conflicts that can arrise due to different time zones, you'll have a design that will scale gracefully from handheld devices through multi-terrabyte n-tier configurations. Your app and data can be successfully scaled from a Windows Mobile Device (such as a cell phone, but it doesn't require constant connectivity) to a laptop, a desktop, a two-tier (client/server) application, to a full n-tier or cloud configuration.

    Because this system relies on surrogate keys to allow any machine to safely enter new data and because any machine that gets a copy of data (such as customer information, etc) can modify that data, some person will have to review any conflicts that occur. This conflict resolution is a result of your specification that requires that business continue even when connectivity isn't available, it is an issue with the specification and not the solution. It is possible to code your application to prevent this from happening, but doing that will require some leeway in the business requirement that you'll have to negotiate with the business.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2010
    Posts
    5
    Hi Pat, thanks for the reponse, I use forums as a last resource because the response time can be from some hours to weeks but god you were quick!

    Returning to the topic, I have read the abstract of merge replication and is terrific, like you say this solves the exactly problem that I'm facing. But to be honest it would be difficult for me the license investment for my own company and some of my clients that would be using my software simply can't afford the price of a standard or enterprice edition.

    I understand that sometimes when you want cutting edge tech you must pay for it, but I'm in a unconfortable point where I must rely in low cost solutions or create my own ones or in the worse case limit the scope of the requirements (I wouldn't like that ).

    Other ideas using for example the microsoft sql server express edition, mysql (I have used the mysql replication and in my opinion is really limited), posgress or even using software or topology aproaches?. I don't know I'm open to any suggestion, the best here is that the development hasn't started and I still have time to choose the best option.

    Anyway thanks Pat, knowledge is power and now I know about a powerfull feature of SQL Server.
    Last edited by rsansores; 07-20-10 at 20:09.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Do a bit of comparision work before you throw the baby out with the bath water.

    Sure you'll need a hefty license for the centralized server, but that's likely to be tolerable for a business. If your business grows, you may choose to add additional servers, but that's a matter for you or your IT staff.

    If you write the app yourself (and it sounds like you plan to) and you use certain versions of Microsoft's Visual Studio, then you can distribute the SQL run time files at no charge with your applications. These runtimes are limited in terms of database size and features, but I don't foresee that any of those limitations will affect your app.

    Do a bit of research. Visit the Microsoft web sites. Ask some pertinent questions. You might find these tools are more affordable than you think.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2010
    Posts
    5
    Thanks Pat, I will present your suggestion to my project commite and lets see what is their opinion. I'm not sure if they will be completly satisfied because most of our servers are linux boxes and they are trying to migrate to opensource most of our services (is really depressing have a lot of terminals in windows 98!) and some of our customers are cheap (im not kidding!) but I think you have a serious point.

    After some time wondering and writing my ideas, my conclusion is that the money we can save in the database license can be not enough to the development of a more complex software that would manage some operations that in my opinion should be delegated to the database engine.

    My propousal maybe will be deploying a fully replicated information system to the biggest customers (delivering the cost of the database to the customer, the ones that do not feel that paying 3 thousand buks in software is a blasfemy) and offering a second cheaper choise with just a master-slave replication to the smaller customers. If you want more functionality you must pay more, maybe the bigger complain will be the absent of multiplatform but lets see what happen.

    I will be posting the results of the meeting! Hope me luck and thanks again Pat.

    Ps. In a totally unrelated topic, indeed I'm part of the software team that we will be developing the app, we are planning to use Java 1.6 + Netbeans Platform 6.9 + Hibernate for the client app and Google Web toolkit + Hibernate for the Web app, like you can see most of the tools are really far from the Microsoft world but the current software is running using C# + NHibernate and Sql 2000 Server Express and runs really smooth. Maybe we can keep the data model in Microsoft and just migrate the ORM and the programming language. Lets see what happens in the next days.

    Ps2. In my company the main bussiness is selling hardware so you can imagine what do they thing about paying a big amount of money in an intanggible software licence.

  6. #6
    Join Date
    Jul 2010
    Location
    Kansas City
    Posts
    2

    ObjectMMRS

    Hello. My suggestion is to use a multi-master approach for all customers by using a replication software suite – specifically, the database replication software ObjectMMRS. I am a representation of ObjectMMRS, and would be able to personally assist you. ObjectMMRS is able to perform multi-master replications with SQLServer Express, Standard and Enterprise. Most importantly, you will be able to open source databases such as postgresql (i.e. customers), and SQLServer Standard (i.e. Central) simultaneously. ObjectMMRS can also replicate data between different database vendors.

    The ObjectMMRS suite is inexpensive – beginning around $600 annually per database, including upgrades and support. If you are interested in this option, please visit objectmmrs.com and download our 30-day complimentary trial that includes free internet support for trial setups.

    Renato

  7. #7
    Join Date
    Jul 2010
    Posts
    5
    Hi Renato, it sounds like an interesting option. Some questions I have in mind before asking the 30 days trial.

    1. We are looking for a KISS solution, ¿How simple is the configuration of ObjectMMRS? TextFiles? XML Descriptors? Configuration tables in the same database engine?

    2. ¿How well documented is this solution? I know that buying it means getting direct support over the product, but I never understimate a good, complete and extensive technical and user documentation.

    3. Is there an API in some programming language to configure, mantain, secure and recover from failure programmatically. If not, what is used? Stored Procedures? Triggers?

    4. What are the main differences of your product, advantages or disadvantages, comparing it to other market solutions like Continuent, MySQL Cluster, Bucardo, PGCluster, etc.

    5. The price what I understand is for the main database server, just 600 bucks and I can have any number of small database terminals?

    6. ¿Companies already using this solution?

    Thanks for your time and sorry for the long long question but we are planning that this development will last at least ten year so we need to start with the right foot.

    PS: I saw in your page like a feature of your product that this do not use low level database log access, What does it means?
    Last edited by rsansores; 07-23-10 at 01:36.

  8. #8
    Join Date
    Jul 2010
    Location
    Kansas City
    Posts
    2

    ObjectMMRS

    Hi, I will try to answer all your questions below:

    1. The configuration of ObjectMMRS is simple and flexible. The suite has a web module where all the administration and monitoring tasks can be done with an intuitive web application. Text property files are minimal, only needed for startup application, like tcp/ip ports, maximum number of threads, etc.

    2. Full documentation of product exists in Portuguese at OBJECT Sistemas Wiki: ObjectMMRS - Manual do Usuário. The English version will be available at objectmmrs.com in September 2010.

    3. The ObjectMMRS engine is simple - not a black-box solution. The replication stages can be monitored and intervened at any time. For example, if an accidental large update happens at central database, the queue updates can be dropped. ObjectMMRS does not work with internal database logs - the basic mechanism is the use of trigger with basic and standard sql statements. Our triggers can be created to provide row or column update granularity.

    4. ObjectMMRS is multi-threaded and multi-processes to insure the best scalability and capability to replicate high data volume to hundreds of database servers. Most of the competitor’s database replication software does not provide the architecture to work well with just 10 or 20 database servers. Today we have customers replicating half million table rows per day for 15 database servers with half of CPU and network bandwidth than the native replication software used before (Slony).

    5. Our packages start at $600. Please contact-us with your specifications to receive an exact proposal.(sales@objectmmrs.com)

    6. ObjectMMRS is a Brazilian-based company. We just launched our business in the United Stated and have a sales team based out of Kansas City, KS.

    Brazilian companies that have been using ObjectMMRS:

    - Locaweb Internet Datacenter - one of the biggest internet datacenter in Brazil.
    - Brazilian Navy - Integrating the health system databases, 42 postgresql database servers.
    - Arcellor Mittal - Biggest stainless steel, 28 oracle databases.
    - Shibata Supermercados - Supermarket network with 10 sites.
    - Several small projects envolving less than 10 database servers, for several database vendors, like Oracle, DB2, Postgresql, MySQL, Firebird, etc.

    ObjectMMRS does not use low level database resources like database log reading, etc, it uses sql statements. This means that ObjectMMRS is always compatible, experiencing no problems on database upgrades, migrations, etc…

    Renato

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •