Thread: Replication / Synchronisation
06-22-06, 03:49 #1Registered User
- Join Date
- Mar 2002
- Shropshire, UK.
Unanswered: Replication / Synchronisation
Hi Guys, my first post here for quite a while.
I have a question regarding replication and or database synchronisation:
I am doing some work on a SCADA control system in .Net - (SCADA=[S]upervision, [C]ontrol And [D]ata [A]quisition)
Obviously, these are mission critical systems that must have uptimes of greater 99%.
Tradiotionally, we have used custom written database in assembler or C, but these are getting rather long in the tooth, and, in my opinion, represent a massive man-time investment developing something that already exists 'off the shelf' (for example, our custom written database, the first version of which roled out nearly 20 years ago doesn't support SQL)
So, I'm looking to see if there is an off the shelf DB that can do what we need it to do. I lean towards Microsoft because of it's integration with the .Net platform.
However, probably the most important criteria for us is that of database synchronisation - as data from the plant arrives it is stored in a database (obviously) - however, at the SAME TIME, it is sent to a back up (standby) database which is identical in every respect, except it does not 'answer' queries, it only listens and keeps it's database up to date. Then, should the 'primary' database server fall over, the standby will become active, and it is up-to-date ready to run.
Does SQL server offer such a facility that changes to a database can be immediately and automatically 'echoed' to a standby database? Automatic promotion from standby to main is not an issue, as this decision would be taken by the application software 'in front' of the database - if it cannot contact the main DB server, it will elect to start talking to the standby, thus, philisophically (sp?) the standby is now the main server.
Hope I haven't confused anyone here with my rambling?
The problem is my hunch (not being a DB guru) is that replication is NOT what I am looking for - I *think* replication does sync data and schema (correct me if i'm wrong) but not immediately - it is scheduled? Yes?
I am looking more for synchronisation - but it has to be 'on the fly' - not every 15 minutes or 8 hours etc...
If SQL server cannot do it, then it's not a major problem, since the DB access layers of the applications themselves can simply write their data to two DB's, and queue writes in a buffer if one of the partners is offline, such that they will be written to the partner when it comes online - however, I'm all for an easy life, and if the boys at Microsoft have already settled this issue, then i'm all for that!
Your advice would be appreciated.
Mark WillsMark Wills.
06-22-06, 10:21 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Assuming you can make a few minor schema changes (like establishing Primary Keys for replicated tables), then SQL Merge replication will do what you've described. Depending on hardware, network connection, etc you can get latency down to about a minute. Then you can have two or two thousand SQL servers, with little or no difference to the application.
If you need instant or selective replication, I'd suggest letting the application do the replication, but in all other cases I'd use SQL replication.
06-22-06, 11:15 #3Registered User
- Join Date
- Dec 2002
If you are looking at SQL 2000, then I might consider log shipping. Log shipping runs on a schedule, yes, and that might not meet your requirements precisely, but I believe that there are ways to catch up the "standby" server in the event of disaster. Log shipping does not automate the failover process, but the commands to make it work are not overly complex.
If you are willing to look at SQL 2005, it has much better provisions for this. I cannot now remember what the name of this feature is called (on their website, it looks like it is called "Database Mirroring").
We are going to start taking our first hard loot at SQL 2005 in about 2 weeks; database mirroring is one capability we are seriously interested in. It will extend our off-site DR capabilities greatly.
hmscottHave you hugged your backup today?