Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

HA for production machine Expand / Collapse
Author
Message
Posted Thursday, August 28, 2014 2:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:26 AM
Points: 275, Visits: 109
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
Post #1608189
Posted Thursday, September 4, 2014 6:18 AM This worked for the OP Answer marked as solution
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:46 AM
Points: 124, Visits: 188
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.
Post #1610461
Posted Monday, September 15, 2014 2:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:26 AM
Points: 275, Visits: 109
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.
Post #1613569
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse