HA for production machine

  • Hello community members!

    >> Current situation

    A customer has a visualisation which controls a production machine. The PC has a MySql database, Php frontend and Access program. Those parts are needed to control the prodcution process. As the PC has fullfilled its liftime cycle, the customer wants to migrate the solution to a newer plattform (operating system, office components, database product)

    >> Tasks

    - Migration of the current data from MySql to a local microsoft sql server (lets call it LDB) instance

    - update the PHP and Access software.

    The LDb only needs one CAL and a single processor/core as it will work only local.

    A further wish of the customer is to add high availability to the database. The recipe and charge data should be loaded from a centralized server instance (call it SDB, i assume it is an enterprise edition). The same data should be replicated to the LDb in case of a network failure or some other failure so that they can still produce.

    Some of the data from the LDB should be replicated to the SDB e.g. environmental data (temprature, humidity, ...). The environmental data must be then archived on the SDB (only on the SDB) to keep the LDB performance high. I would build the archivation process as a stored procedure which will run in regular intervals.

    >> Questions

    Is replication or mirroring a better approach?

    Can I build a mirror with a standard edition (LDB) and an enterprise edition (SDB) or do I need the same licenses for both databases?

    If the solution is built with replication - I can mark some tables in the database to be replicated from the SDB to the LDB. Other tables (like the trend data) would be marked to be replicated from the LDB to the SDB. For this task I would need one publisher and two distributor databases with the specified tables. Would this work?

    Thank you for your help.

    Alex

  • for mirroring there are certain perquisite in terms of environment, os and recovery so please go through it first.

    second replication if your tables are having primary key in terms of transaction replication you have to check.

    log shipping and replication both will provide some latency which you should be ok with respective to your environments SLA and needs.

    third just to remind in mirroring your secondary will not be available to use it can only be used as some point for DR.

  • Hello srikantmeister!

    Thank you for your answers. As the customer is not allowed to use a different version then SQL Express as the local DB, mirroring of the database has "died".

    I still have some questions about the replication but I will open another thread for this issues.

    Thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply