TPCIP port binding error & other errors for complex SQL Anywhere Startup scenario

[Former Member]

I'm working on an IT project in which I need to access existing SQL Anywhere data via ODBC. In this case its Windows 2012 R2 environment.

There's only one server and naturally we want to use test data before messing with LIVE actual data. Rather than require duplication of the entire physical server (physically or virtual) the notion was use a different (test) DSN to access a test database that intentionally has the same definition as the live data.

The test database offered for me to work with is file copy of "mydatabase.db" file and its log, with old data contained in it, in its own folder such as "D:\\duplicate\\mydatabase.db" along with the userid and pwd for database owner.

Conceptually "all" I need to do is make a second DSN that runs an instance of SQL Anywhere against the test database file and I should be able to experiment without risk to the live db. That's where the fun begins.

I've dumped hours into this with errors and so far no success. Here's what I've tried to sort out:

• For ODBC DSN configuration, on Login Tab I use the autostart (start database on this local server) feature.
• I figure I should not become default server for sure because I emphatically don't want live data coming to me, so probably I want to specify -xd on command line for startup.
• There's database file as ODBC DSN parameter but I could also specify it on the startup command line. I don't know which and have tried both.
• There's DSN "server name" parameter but also on command line I could specify "-n server" and I'm not sure whether to use one, the other, or both (or maybe these are unrelated). Do these parameters refer to server in terms of networking (e.g. "localhost") or is there a logical database server name as implied by the -n server switch?
• There's the database name. I don't want to accidentally end up receiving live data or using live data. Maybe I should use the database name parameter of the DSN to try setting a different/distinct database name e.g. "mydatabase-test". But I have the file which is a physical file copy from an old snapshot of the real live .db. Maybe I should rename the physical file e.g. "mydatabase-TEST.db". Maybe the .db file internally contains the database name so that renaming the physical file etc is not adequate.
• There's IP address. It seems I definitely don't want to run on default IP address and would be best to run ONLY on my own dynamic IP address (i.e. no shared memory connection). I figured using -xTCPIP(ServerPort=49xxx) on the startup command line. But if I use this for startup command line, where else do I specify that the ODBC DSN should use the port? Do I specify this again in the server name parameter (such as "localhost:49152") or maybe it must be specified somewhere else?

I've gotten almost entirely "connection error" results when trying to configure this properly, except for one time when I managed to connect to the LIVE data quite unintentionally.

After failing repeatedly I made another attempt to resolve this: I decided on manually launching a "personal database" instance from a command prompt. I figured I could even setup the DSN to use an "already running" server if I could just launch from the command line for a while during the time I need to work with the test db.

So I renamed the physical db file, I specified a path to the renamed db file "d:\\duplicate\\mydatabase-test.db", I used -n mydatabase-test, I used -xd, and I used something like -xTCPIP(ServerPort=49158). I noticed that it was trying to start using shared memory (I thought it would not do so when I specified the TCPIP comms), and the launch of the dbeng16.exe failed because it "could not bind to 127.0.0.1:49158". I've tried several different ports in the dynamic port range and none of them could be bound.

(I've written entire full-scale applications that successfully use dynamic port numbers on a variety of Windows Server versions...)

Does someone have ideas about the dbeng16.exe port binding error that happens regardless of what port is specified?

Or --far better-- does anyone know a recipe I could follow to setup the DSN quickly and correctly for the intended purpose?

[Breck_Carter]

Will your production server be supporting multiple users on a network?

If so, you do NOT want to use dbeng16.exe, NOR do you want to start the database via the ODBC AUTOSTART parameter.

You probably want to start dbsrv16.exe via command line, or as a Windows service. ODBC DSNs can be used for client connections, but they are very simple when the server is already running... it is also easy to use DSN-less connections via ODBC.

[Former Member]

Hi Breck,

Thanks for writing. The production server is already running and it is currently supporting multiple users in a LAN environment. I believe the production server is launched with dbsrv.exe.

The challenge is to add a second database server that is used exclusively for test and it must not collide with the production one. I did not think it would be much of a challenge until I began tripping over how to correctly interpret the parameters, and especially understanding how to work with the DSN config parameters as compared to the command line switches.

Naturally I'd like the second server to otherwise be as similar to production as possible so that my effort can cut over to production after high confidence has been reached working against the test db.

[graeme_perrow]

Quick answer: try running dbdsn -cm -g dsnname, which will display the dbdsn command used to create that DSN. Modify that command to create a second DSN by changing the connection string in the -c switch of that command to include DBF=full-path-to-test-database-file. Remove any DBN connection parameter. Change the name of the DSN (-wu switch) and execute the command to create a second DSN that can be used to connect to the test database.

This may not work (you may get a "permission denied" error) depending on whether you are running dbeng16 or dbsrv16 and the value of the -gd switch. In that case, you can either start the test database in a second server or modify the live server to run both the live and test databases. See below.

Much longer answer:

Let me try to clarify the different names and parameters:

• Server name: is a symbolic name you give to the server when you start it (using the -n switch before any database filenames). You then specify this in the connection string using the ENG parameter.
• Host name: is the name of the computer. You can use this (or the machine's IP address) in the connection string using the HOST parameter. If the client application is running on the same computer as the database server, you don't need this.
• Database name: is the symbolic name of the database. One database server can run multiple databases, so if your server is running more than one, you need to specify which one you want to connect to. Even if you're only running one database, it's a good idea anyway. You specify this when starting the database server using the -n switch after the database filename, and by using the DBN parameter in the connection string.

When starting the database server, you first give it the server name using -n, then specify a database filename and then (optionally) a database name, again using -n. For example:

dbsrv16 -n MyServerName path-to-live-database-file -n LiveDB path-to-test-database-file -n TestDB

You would then use the following connection string to connect to the live database:

UID=user;PWD=password;ENG=MyServerName;DBN=LiveDB

If you are on a different computer, you would add ";HOST=serverHostName". If you wanted to connect to the test database, you would change DBN=LiveDB to DBN=TestDB in your connection string.

If you prefer, you can start a second server for the test database. A single server running two databases can make more efficient use of system resources than two separate servers but either will work. In that case, you'd need a different server name both on the server command line and the connection string.

That's the naming stuff. Now onto the networking stuff.

When you start dbsrv16, the server attempts to listen for TCP connections on port 2638. You can change that port number if you want, but if you're only running one server on the machine, there's no need to. If you are trying to connect from a different machine, you need to add the HOST= parameter, as described above. Just add ;HOST=ServerHostName to the connection string.

If you are running two servers on the same machine, they cannot both listen on the same port. If you want to specify a port to listen on, add the -x tcpip(port=n) switch to the server command line (where n is the port number). Then you need to change the HOST connection parameter to ;HOST=ServerHostName:n. If you do not specify a port number on the second server, it will pick a random available port starting at 49152 and display the chosen port in the server console. You will need to check for the port number and specify that in your connection string. This is more difficult because if you shut the server down and start it again, it may pick a different port.

This is a very long-winded answer but hopefully it gives you enough information to solve your problem.

[Former Member]

Hi Graeme,

Thanks for your reply. I'll try to digest and apply this information as best as I'm able.

My scenario already has a database server running and responsive on port 2638, which is the production server I wish to avoid colliding with.

The folks who are responsible for the production server don't want me altering how it's currently set up, and I think this indicates that adding a test.db to the same server is pretty well off limits. This suggests to me I need to have another instance running for test at least until my effort can be switched over to the production server. (They actually use more than one for production purposes but I'm not situated to attempt changing any of that.)

I take it from your reply that there's nothing inherently built into the .db file itself that would "force" a particular database name, i.e. in a way that would override naming either by command line or DSN-supplied switches. SO if I understand correctly, working with an old snapshot of the .db file as a source of test data (& schema) would not inherently cause a collision with the production database.

It appears one of the major areas of confusion I've had is understanding how the Windows DSN configuration dialog parameters [which I presume go to the ODBC driver?] vs. command line switches placed on the "startup" line in the DSN configuration dialog [which presumably go to the dbeng or dbsrv executable?] are intended or not intended to interact with one another!

I haven't even reached the point of using a connection string (one that presumably specifies the DSN) because the "Test Connection" button on the configuration dialog has reported communication failure in every case except for the one time I inadvertently connected to the live server/db.

I'll continue to study your reply but hope these remarks would help clarify a little where I've been getting into trouble so far.

[graeme_perrow]

Sorry, I did not address your question about renaming the database file. The answer is yes and no. If you are making a copy of the live database file, you also need to make a copy of the live transaction log file and point the database at that file. You need to do this:

copy live.db test.db
copy live.log test.log
dblog -t test.log test.db

Once you've done that, you can start a new server with test.db and it will not conflict with live.db.

All connection parameters whether in a connection string or a DSN go to the ODBC driver. If no server can be found using those parameters, then we may attempt to "autostart" one, depending on which parameters were supplied. For example, if you give a HOST or LINKS parameter, we assume you're attempting to connect to a server on a remote host, so we don't autostart. We start with the START= parameter if it exists, otherwise we use a default of "dbeng16". We then add server switches based on the connection parameters:

• ENG -> -n (server name)
• DBF -> database file name
• DBN -> -n (database alias)

You should use the connection parameters rather than including these switches in the START parameter.

You may want to look at this question for more information on connecting and connection parameters, though there's no information there on autostarting.

[VolkerBarth]

So your requirement is that the test database

1. does not run on the same machine as the production database and
2. all other configuration aspects should be as similar as possible?

Then I would suggest to just copy the production database to the test machine (i.e. without renaming the database file and transaction log file) and configure a Windows SQL Anywhere service to run there. The only necessary differences between both databases are

• the database server name
• the host name (i.e. the machine name)

So you could use, say, connections strings like

HOST=ProdServerMachine;ENG=ProdEngine;DBN=YourDatabase;UID=...

to connect to the production database and likely

HOST=TestServerMachine;ENG=TestEngine;DBN=YourDatabase;UID=...

to connect to the test database. (I'm assuming each database server is the only one on the according machine so they use the default port 2638.) Of course, you could use the IP address of the machines instead of their host name.

[reimer_pods]

I suppose you meant to write

dblog -t test.log test.db

[Breck_Carter]

> you could use the IP address of the machine

That is highly recommended for testing...

HOST=192.168.1.101 - for a computer on the local network

HOST=localhost - for a SQL Anywhere server on the same computer

[VolkerBarth]

Well, at least in our network I'm way more familiar with machine names than IP addresses:)

[Breck_Carter]

The world is a big place, and it contains many strange things, like fake host names in the client computer's C:\\Windows\\System32\\drivers\\etc\\hosts file.

In other words, IP addresses are the real thing.

...on the OTHER hand, you can change the hosts file to redirect production host names to a development IP address, which I sometimes do when testing client deliverables 🙂

(like HA setups mimicing multiple computers on one laptop)

[Former Member]

Hi Volker, Actually the test database must run on the same host as production. This is a reason I feel that a lot of caution/precaution is needed.

[VolkerBarth]

You wrote in your first comment:

The folks who are responsible for the production server don't want me altering how it's currently set up, and I think this indicates that adding a test.db to the same server is pretty well off limits.

And now:

Actually the test database must run on the same host as production.

Hm, now I don't really understand. If you are not allowed to add a test database to the running database server on the host but need to run it on the host itself, then apparently you need to run a second database server instance on the same host. I don't understand why that is "less dangerous" from "the folks's" view because a second server instance might also compete for the host's resources...

If you need to run a second server instance, then regard the second last paragraph of Graeme's answer, i.e. you need to specify a particular port then both when starting the database server and when connecting from clients. (Aside: It isn't the easiest thing to configure but I think it's far from "very complicated" - and by chance no unusual setup at all...)

[graeme_perrow]

Yes I did. Thanks - I've updated my comment.