|
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:
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? |
|
Quick answer: try running This may not work (you may get a "permission denied" error) depending on whether you are running Much longer answer: Let me try to clarify the different names and parameters:
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:
You would then use the following connection string to connect to the live database:
If you are on a different computer, you would add ";HOST=serverHostName". If you wanted to connect to the test database, you would change 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 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 This is a very long-winded answer but hopefully it gives you enough information to solve your problem. 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.
(20 May '18, 15:11)
Lighthorse
Replies hidden
1
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:
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.
(21 May '18, 09:39)
Graeme Perrow
So your requirement is that the test database
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
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.
(22 May '18, 03:09)
Volker Barth
> 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
(22 May '18, 08:45)
Breck Carter
Well, at least in our network I'm way more familiar with machine names than IP addresses:)
(22 May '18, 09:18)
Volker Barth
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)
(23 May '18, 07:49)
Breck Carter
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.
(28 May '18, 10:37)
Lighthorse
You wrote in your first comment:
And now:
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...)
(29 May '18, 03:05)
Volker Barth
Yes I did. Thanks - I've updated my comment.
(30 May '18, 09:16)
Graeme Perrow
|
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.
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.