SQL Anywhere v. 11.0.1. 2713 - replication/HA

[Former Member]

Hi,

One of our customers is asking us to look into the possibility to improve the availability of the database , replication.

What we are looking for is more information on how to set the replication scheme up and running. Customer has 1 main server (where current database service/file is running)  and 2 backup servers which should be used as backup-servers in case the main server 1 goes down, backup server 1 has to take -over , in case backup server 1 goes down, backup server 2 has to take over....

How do we change from a traditional service on 1 server , to a replicated solution where the database is available on 3 servers ?

Any information / tips / ideas are more than welcome.

TIA

John

[Former Member]

For 24/7 availability of backend database, High Availability a.k.a Database Mirroring would be a best option based on your description.

In SQL Anywhere 11, HA architecture typically comprise of Arbiter, Primary, and Mirror Server. Arbiter server will play a role in determining which server should act as Primary/Mirror server.

Database that runs in Primary server will be an "Active" backend database, where all the changes(Update,Insert, Delete) and reading of the content in database will be available. Mirror Server will host database in read-only mode, where only SELECT statement can be performed.

For example, client application can connect to Mirror Database Server to view the data, but when changes of data (i.e. updating the record of existing contact information from Application) are made from the application, then the application will speak to Primary Database to update the changes. After changes have been made on Primary Database server, comitted transaction will be synchronized over to the Mirror Database Server. So, when Primary Server goes down for an unexpected reason (i.e power shutdown from Primary Server), Mirror Server will negotiate will Arbiter server to act as a new Primary Server, which will contain exact copy of Primary Database(as long as latest transaction was fully comitted to Mirror before Primary Server went down).

Be considerate about which database mirroring mode (i.e. Asynchronous vs Synchronous) you want to use. Failover procedure is very different with these two modes.

[Former Member]

I think high availability would serve you better than replication because the service interruption would be a lot less. Replication would be a warm standby solution at best because you'd have to apply any transactions that had been committed but not replicated.

SQL Anywhere has a high availability offering that provides varying degrees of availability depending on requirements. Here's the link:

High Availability

You can also look at Veritas or Microsoft Clustering solutions as alternatives

Thanks

Mark