|
Hi everyone, I've been working on setting up Read-only scale-out system, however I am encountering an issue with it and I am not quite sure what is causing the problem. Sometimes, not always, the copy node server stops replicating and it keeps sending the following message constantly, as if the connection already exists to the DB, however that is not true. It stops working after a few days up to a month or so. It stops working completely and to make it work again, I have to copy the main database over the copy node again for it to function. I. 06/11 23:33:01. Database "DB" mirroring: Waited for incoming connection for 60 seconds before requesting log page, now dropping connections I. 06/11 23:33:01. Database "DB" mirroring: disconnected from parent "DB" I. 06/11 23:33:01. Database "DB" mirroring: parent reconnection process starting E. 06/11 23:33:01. Database "DB" mirroring: mirror connection to server "DB" failed: connection already exists I. 06/11 23:33:03. Database "DB" mirroring: connected to parent "DB" I. 06/11 23:33:03. Database "DB" mirroring: synchronizing ... Now for the details of how this is set up, I'll write here, since I believe it might not be accurately set up, however I'm unsure what I'm missing. I have 2x PCs, one with main database running and 2nd one with the copy node running. Note that copy node is being ran as a windows service. 1) Everything is created manually, like this: CREATE MIRROR SERVER "DB_primary" AS PRIMARY connection_string = 'SERVER=DB;HOST=176.24.255.20:6871'; 2) Then, I add a copy node, as the copy of primary server: CREATE MIRROR SERVER "DB_s2" AS COPY FROM SERVER PRIMARY; 3) The configuration is completely DEFAULT, except auth string: SET MIRROR OPTION authentication_string='abc'; The service that runs the DB on the 2nd computer is ran via batch file: "C:\Program Files\SQL Anywhere 17\BIN64\dbsvc.exe" -as -s auto -t network -w SybaseCopy17 "C:\Program Files\SQL Anywhere 17\Bin64\dbsrv17.exe" -x tcpip(PORT=6872) -n DB_s2 "D:\Database\DB.db" -xp on -ek abc -o "D:\Database\srvlog.txt" -on 2000000 @pause And then it is started for the first time, connects all well and everything works perfectly fine. However, after having it running for a few days, or a month or so it sometimes simply stops working and it says the error mentioned above. Then, if I run ODBC setup looks like this: [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\sqlany] "Driver"="C:\\Program Files\\SQL Anywhere 17\\BIN64\\dbodbc17.dll" "UID"="user17" "PWD"="dbpw" "DatabaseName"="DB" "DatabaseFile"="D:\\Database\\DB.db" "DatabaseSwitches"="-xp on" "ServerName"="DB" "StartLine"="dbsrv17.exe -x \"tcpip(PORT=6871)\"" "AutoStop"="NO" "Integrated"="NO" "DatabaseKey"="abc" The only thing that I was thinking of, is doing these, however I'm not sure if that's correct to do in this case with this issue: SET MIRROR OPTION promotion_time = '315360000'; SET MIRROR OPTION child_creation = 'off'; Any ideas would be greatly appreciated. If any additional information is required, please let me know and I'll get that as well. Thank you for your time. |
|
Just another idea: According to the docs sample, it seems common to define the root node via "CREATE MIRROR SERVER ... AS PARTNER" whereas the role "AS PRIMARY" is described as optional and only needed if you want to create the child node(s) automatically, whereas you seem to want to do that manually. 1
I've noticed the ones that do stop working after a while tend to create another connection string of their own looking like this: ServerName=DB_s2;CON=AutoAdd;Host=176.24.255.30:6872 That's why I thought this might help: SET MIRROR OPTION child_creation = 'off'; However, setting it AS PARTNER might be better since I don't want multiple nodes, just the one. I'll try to re-adjust the settings and see over a period of time if it helps.
(13 Jun '23, 03:46)
pcrncec1
Replies hidden
FWIW, does the contents of system table SYSMIRRORSERVER (when queried at the copy node) change before and after the copy node runs into its reconnect issues? And does it differ from the values queried at the root node?
(13 Jun '23, 05:14)
Volker Barth
Not from what I've noticed, just only adds an additional connection_string in SYSMIRRORSERVEROPTION But the definition of the copy node in SYSMIRRORSERVER is always the same. Server_type stays "copy" and it's parent is set to the PRIMARY one by object_id (no new nodes are added). However, I have never defined a connection_string myself for the copy node as I noticed it doesn't need it, since this is only supposed to be a live backup on a different PC and the only connection the copy node database should be aware of is the main server one.
(13 Jun '23, 09:15)
pcrncec1
I have tried converting the COPY node to PARTNER, however it seems like that is not possible? I always get "Syntax error -- error position too large". What I'm doing is this: DROP MIRROR SERVER DB_s2; CREATE MIRROR SERVER DB_s2 AS PARTNER FROM SERVER DB; It works if I get rid of "FROM SERVER DB" at the end, however in that case, the PARTNER database is not assigned as a child to the DB server. Unless the partner doesn't need to be linked as a child to primary server with it's ID? Nevertheless, attempt at having a PARTNER with no COPY and no connection to primary as child, database seems to be unable to run ever again: E. 06/15 10:30:00. Database "DB" mirroring: mirror connection to server "DB" failed E. 06/15 10:30:00. Database "DB" mirroring: Fatal error connecting to partner I. 06/15 10:30:00. Database "DB" transaction log current offset is 32044335 E. 06/15 10:30:00. Error: Database cannot be started -- unable to start database "D:\Database\DB.db"
(15 Jun '23, 05:28)
pcrncec1
Replies hidden
Hm, my idea according to some docs was to declare the root (and not the copy) as PARTNER not as PRIMARY.
(15 Jun '23, 05:40)
Volker Barth
I see that I've misread your initial answer, I'll give that a go. Thanks.
(15 Jun '23, 05:48)
pcrncec1
It seems like PRIMARY is mandatory. E. 06/15 11:57:59. Database "DB" mirroring: missing or incomplete definition for primary server E. 06/15 11:57:59. Error: Database cannot be started -- Missing or invalid database mirroring option
(15 Jun '23, 05:59)
pcrncec1
Hm, then I have misread the docs or the samples. The newest docs do tell:
Therefore I have assumed that a PARTNER is obligatory whereas a PRIMARY is not. But the tutorials seem to create both server roles for the same server. You may just give that a try? (And sorry for the confusion...)
(15 Jun '23, 06:39)
Volker Barth
I tried to define it again from scratch, however I do only have 2 computers and main database is on one PC, and if I have both PARTNER and PRIMARY at the same time, it requires both to have connection string, which ends up being the same for me, thus I don't really get anywhere with this: CREATE MIRROR SERVER DB_partner AS PARTNER connection_string = 'SERVER=DB;HOST=176.24.255.20:6871'; CREATE MIRROR SERVER DB_primary AS PRIMARY connection_string = 'SERVER=DB;HOST=176.24.255.20:6871'; CREATE MIRROR SERVER DB_s2 AS COPY FROM SERVER DB_partner; SET MIRROR OPTION authentication_string='abc'; If I do it without PARTNER then it works fine, however that leaves me with the settings configuration I had already from the start. I do not understand the role of PARTNER root in all of this to be honest. E. 06/15 14:19:57. Database "DB" mirroring: mirror connection to server "DB" failed E. 06/15 14:19:57. Database "DB" mirroring: Fatal error connecting to partner I. 06/15 14:19:57. Database "DB" transaction log current offset is 32044157 E. 06/15 14:19:57. Error: Database cannot be started -- unable to start database "D:\Database\DB.db" E. 06/15 14:19:57. Database cannot be started -- unable to start database "D:\Database\DB.db" I. 06/15 14:19:57. Database server shutdown requested by DBSTOP
(15 Jun '23, 08:29)
pcrncec1
|
We don't use the read-only scale-out feature, so I don't know what's possibly wrong. But the v17 readme contains some fixes on copy nodes, so as general question: What version are you using ("select @@version") on both machines?
Version returns: 17.0.10.6315