Database mirroring is not stable.

[Former Member]

BH Hi, We are currently running SQL Anywhere 16 with High Availability option. The problem we are facing is that, when the primary server restarts, the connections to the secondary server are unstable. Some computers are able to connect (but slow connection), some computers are not even able to connect. And even when the primary server finishes booting, it does not always connect to the mirror server. I have to manually restart the service on the mirror, for the primary to find it. Also, if the arbiter is down, and only the primary and mirror are running, the connections to the database are very slow. The computers are on the same subnet mask. Any idea what could be causing that? Thanks Aron

[Breck_Carter]

Your terminology is not clear. The designations "primary" and "secondary" are not permanent. After a typical failover, for example, the partner that was previously the "secondary" is now the new "primary", and when the partner that was previously the "primary" is restarted, it becomes the new "secondary".

So... when you say things like "the primary server restarts" and "connections to the secondary server are unstable", it is unclear (to me, anyway) what you are talking about. I can guess, but my guess would be the exact opposite of the words you are using (primary versus secondary).

When you say "connections are slow" do you mean the act of establishing a connection takes a long time, or is the client server traffic slow after a connection is established?

When you are talking about connections, are you talking about the connections among the arbiter and two partners, or are you talking about client connections?

Show us ALL the configuration parameters for all the servers. Copy and paste, not re-type; edit ONLY to obfuscate private information. Chances are good that [cough] interesting choices have been made, different from defaults.

Chances are ALSO good that this forum is not the right place to debug this kind of dynamic behavior... you may need hands-on help.

[Former Member]

BH Breck, thanks for your reply. When i say the "primary server" i mean the server that acted as primary initially. Also when i say connections are slow, i meant the SQL statements executing are slow and it happens on the client computers. As far as the configuration parameters, I'm using the "-xp on" so most of the parameters are in the database. Is there a way to retrieve them? Thanks Aron

[Breck_Carter]

SELECT * FROM sa_mirror_server_status();

-- on the current primary, which is on partner1_demo...

server_name,state,last_updated,load_current,load_last_1_min,load_last_5_mins,load_last_10_mins,num_connections,num_processors,log_written,log_applied
'partner1_demo','connected','2014-04-01 15:49:37.912-04:00',0.0,0.016666666666666666,0.014583333333333334,0.010625,1,8,981950,981950
'partner2_demo','connected','2014-04-01 15:49:39.001-04:00',0.0,0.004166666666666667,0.003333333333333333,0.004583333333333333,1,8,981950,981950

-- on the current secondary, which is on partner2_demo...

server_name,state,last_updated,load_current,load_last_1_min,load_last_5_mins,load_last_10_mins,num_connections,num_processors,log_written,log_applied
'partner2_demo','connected','2014-04-01 15:49:59.096-04:00',0.0,0.0020833333333333333,0.002916666666666667,0.0043749999999999995,1,8,981950,981950

-------------------------------------
SELECT * FROM SYSMIRROROPTION;
SELECT * FROM SYSMIRRORSERVER;
SELECT * FROM SYSMIRRORSERVEROPTION;

-- on the current primary, which is on partner1_demo...

option_name,option_value
'authentication_string',abc
'synchronization_mode',synchronous
'auto_failover',on

object_id,server_name,server_type,parent,alternate_parent
3920,'partner1_demo','partner',,
3921,'partner2_demo','partner',,
3922,'primary_demo','primary',,
3923,'secondary_demo','mirror',,
3924,'arbiter_demo','arbiter',,

server_id,option_name,option_value
3920,'connection_string',SERVER=partner1_demo; host=localhost:55401
3920,'state_file',server1/partner1_demo.state
3921,'connection_string',SERVER=partner2_demo; host=localhost:55402
3921,'state_file',server2/partner2_demo.state
3922,'connection_string',SERVER=primary_demo; host=localhost:55401,localhost:55402
3923,'connection_string',SERVER=secondary_demo; host=localhost:55401,localhost:55402
3924,'connection_string',SERVER=arbiter_demo; HOST=localhost:55400

-- on the current secondary, which is on partner2_demo...

option_name,option_value
'authentication_string',abc
'synchronization_mode',synchronous
'auto_failover',on

object_id,server_name,server_type,parent,alternate_parent
3920,'partner1_demo','partner',,
3921,'partner2_demo','partner',,
3922,'primary_demo','primary',,
3923,'secondary_demo','mirror',,
3924,'arbiter_demo','arbiter',,

server_id,option_name,option_value
3920,'connection_string',SERVER=partner1_demo; host=localhost:55401
3920,'state_file',server1/partner1_demo.state
3921,'connection_string',SERVER=partner2_demo; host=localhost:55402
3921,'state_file',server2/partner2_demo.state
3922,'connection_string',SERVER=primary_demo; host=localhost:55401,localhost:55402
3923,'connection_string',SERVER=secondary_demo; host=localhost:55401,localhost:55402
3924,'connection_string',SERVER=arbiter_demo; HOST=localhost:55400

[ian_mchardy]

Instead of primary and secondary, I prefer to talk about partners (and reference them by name, say server1 and server2), and the current partner role of either primary (read-write) or mirror (read-only).

In addition to what Breck asked:

• What version of SQL Anywhere (including build number) are you using? (Note there have many significant High Availability fixes over the past year or so).
• Are both partners and the arbiter using the same SQL Anywhere version?
• Are both partners running the same OS and configuration on similar hardware?
• Is there anything else running on the partner computers that could cause a noticable amount of additional load other than the SQL Anywhere servers for the database that is running high availability?
• Are there other servers or databases running on the partner computers other than the high availability database you are asking about?
• If server1 is the primary, will it have similar load as if server2 is the primary?
• What are the client connection strings used to connect to the primary or mirror?
• I cannot imagine why stopping the arbiter would cause any performance difference of the partner servers. Can you confirm that when you see this the load on the partners is similar to when the arbiter was running, and the primary is the same both before and after the arbiter stops?
• If the answers to these questions and using an up-to-date EBF don't show anything obvious, this is likely going to require openning a tech support issue and digging through console logs and possibly other diagnostic information (possibly including server -z logs)

[Former Member]

BH Hi Breck and Ian, thanks to both of you for your replies.

It looks like the versions are the same on (server1, server2, arbiter). However, the build number is different. I'm going to update to the latest build on all the servers and see if that fixes the problem. I had trouble finding the current build number, so i went to Help > About in Sybase Central. Is there another way to see the installed version and build #? Thanks Aron

[Breck_Carter]

SELECT @@VERSION on the primary and secondary (yes, you can open dbisql on the secondary, but it will be readonly).

Also... C:\\Program Files\\SQL Anywhere\\installed.ini contains "v=16.0.0.1823"

Also... right mouse - Properties - Details tab - Product Version on C:\\Program Files\\SQL Anywhere 16\\Bin64\\dbeng16.exe