For your questions;
- Unless you really know what you're doing with the query, stick with the API. The reason being is that configurations and latest versions can play havok on your queries. That's not always the case, of course. If you must connect to the db, I would suggest that you create a read-only user and store those credentials in a vault dictionary. (Don't use 'sa'!)
- If you're attached to the vault, you should be able to see the db. However, while there are a few instances where you may not be able to retrieve information via the API, it's rare.
- Yes, but it's total overkill and not required. (You could use a web service, middleware, etc.)
My take on it:
- I know the db very well, so I pretty much always pull info from the database, I just never SET anything directly to the database. The API is nicer though if you can use it because it imposes things like folder and state permissions that you are almost certainly not going to include in your queries.
- Absolutely, if the client can't talk to the SQL server it doesn't work. The SQL traffic doesn't flow through the Archive Server it goes straight to the client, you can use wireshark or procmon to see for yourself, or run a profiler trace with the hostname column enabled
- I don't think you should worry about this, all clients can talk to the SQL server
API - cleaner and less intrusive into your SQL server.
Less risky from a business management perspective and far less likely to require elevated permissions from IT depending on the size of the company you work for.
Thank you all for your responses, they are very helpful! I will prefer to use the API, provided it's simple enough to get what I'm after.