Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

We have an installation of 100 Remotes replicating to one concolidate (alle DBs running SQL Anywhere 17).

Now we are going to replace the currently used 'file message type' with a new 'HTTP message type'.

As we can't make a test with 100 Databases, we want to go direct to production.

My question is, does the DBSRV17 accept 100 simultaneous connections from DBRemote? Has anyone already experienced such a situation?

asked 08 Sep '23, 07:11

Baron's gravatar image

Baron
2.1k136149177
accept rate: 48%

we want to go direct to production.

Does it mean you won't test with 100 remotes with HTTP message transport, or not test with HTTP message transport at all? We haven't used SQL Remote with the builtin HTTP transport ourselves (because we had used HTTP for file transport long before HTTP message transport was introduced in some v12 EBF), so I can't tell about the limits.

But years of SQL Remote usage have told us that thoroughly testing is absolutely necessary if you want to prevent th re-create remotes...

That being said, do you expect all remotes to replicate simultaneously? Does this appear with your current FILE message transport, too? (In my experience, that would be highly unlikely unless the replication is triggered by a regular schedule with similar starting times for all remotes...)


If you are worried whether the database server running the consolidated database might get exhausted by the additional HTTP load, you can later switch to a separate message server. If the new message server is available to the remotes on the same URL as the consolidated database server has been so far, in my understanding that would be doable without having to change anyything on the remotes's setup - it should be a server-side configuration change only. Please note that this is just my understanding, I have not tested this.

(11 Sep '23, 02:50) Volker Barth
Replies hidden

@Volker Barth, thanks for the reply. The HTTPS as Message Type have we already tested (with 2 remotes). What we can't test is the limitation (100 remotes).

The job starting DBRemote on each remote will be triggered from a central point, so they will connect almost simultaneously to the Server, this is also the case in our current FILE message type.

Do you mean that I should set up an extra (empty) Database which will serve only as HTTP File Server? Is this possible in general? This could be a good idea to free up the Cons. DB, but this will also not mean that we increase the count of concurrent connections.

(11 Sep '23, 04:15) Baron

I can't tell whether you should use a separate message server - but it's a supported option (as is a web server farm), see the according tutorials in the SQL Remote documentation.

(11 Sep '23, 05:44) Volker Barth

For your actual question, the web server's MAXCONN protocol option and the number of licensed connections decide how many conccurent HTTP(s) connections can be handled.

Of course you have to find out yourself whether the actual load can be handled by your machine...

If it does not (or not well), I would suggest to prevent all remotes to replicate concurrently, as replication is basically a "time-lag" based approach by design, there usually should be no problem when different remotes replicate to different times...

permanent link

answered 12 Sep '23, 04:05

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

Thanks again for the reply.

According to the documentation I can set the "size" of "maxconn" to 0 in order to have an unlimited number of concurrent connections, on the other hand, it is specified that the maximum number of HTTP connections which can be accepted by the server is 7!

What about the "number of licensed connections"? Is this that what I find in sysoptions?

select * from sysoptions where "option" = 'max_connections'

(12 Sep '23, 11:20) Baron
1

MAX_CONNECTIONS is intended to limit the number of connections to a single database on a server hosting multiple databases (assuming it is not a login policy setting then it is max connections for the user).

The personal server has a limit of 10 connections and it reserves 3 for non-HTTP use. This limit will not impact your case generally as the remote will be acting as a HTTP client. It is the consolidated that will be the HTTP server and that will generally be a network server (and since you are needing 100 concurrent connections, must be a network server). For HTTP, a network server allows up the maximum number of connections plus queues up to 1000 additional connections if the server has reached that maximum. see https://help.sap.com/docs/SAP_SQL_Anywhere/61ecb3d4d8be4baaa07cc4db0ddb5d0a/5c38fec231d64260941cedd6379ef5e8.html

Connections are not going to be an issue for your use case (as long as you have a server licensed appropriately), it is really workload. As someone often stated, it is absolutely possible to construct a statement that can bring a database to its knees with a single connection.

(12 Sep '23, 13:04) Chris Keating
Comment Text Removed

One more question here: Is there a way for the server to determine the number of clients being currently replicating?

In other words, if I am on the server (the same machine on which DB is running as cons. and as message server), and if I trigger the start of DBREMOTE for a specific client from this server machine, how can I tell whether the DBREMOTE against this specific client is finished.

I tried something like this:

select * from sa_conn_info() where Userid = 'remote_client_user';

This worked but not always, since (as the experience showed), the connection from client to message server is not for the whole cycle of DBREMOTE active.

(08 Nov '23, 05:28) Baron
Replies hidden

Is there a way for the server to determine the number of clients being currently replicating?

What do you mean by that? In my understanding, even with HTTP as transport, SQL Remote replication is still a disconnected process, i.e. remote and consolidated databases never have a direct connection with each other but simply pass each other messages.

So, when using HTTP transport, the only information the consolidated database could possibly try to find out is whether a remote might currently send or receive messages via the message server as part of their replication process. Is that your question? (If so, I don't know...)

(08 Nov '23, 08:53) Volker Barth

Yes, this is what I mean. Whether a remote is sending or receiving messages via message server.

In the cons. database (since it is also the message server), I use my above select statement, but one start of DBREMOTE on the client does not mean one connection to message server, rather several connections.

(08 Nov '23, 09:03) Baron

I don't use SQL Remote with HTTP (and had just once long ago for testing), but there might be some usable logic in the according stored procs (those whose name starts with "sr_") and/or tables you could try to check.

(08 Nov '23, 10:29) Volker Barth
showing 3 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×103
×78
×33

question asked: 08 Sep '23, 07:11

question was seen: 383 times

last updated: 08 Nov '23, 10:29