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)
- 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.
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.