People frequently ask me how to set up the data source for a database. Most users want to know how System, User, and File Data Source Names (DSNs) differ from one another.
File DSNs keep their connection information in a file you can store anywhere. Thus, you can store File DSNs on the server so that your developers can access them across the network. The Visual InterDev 6.0 data connection feature automatically reads the File DSN information and stores it in the data connection.
User and System DSNs reside on the machines you create them on. A User DSN is for one user only; its settings follow the same storage route for that user as persistent settings for Microsoft Word or any other Windows application. Visual InterDev stores System DSNs on the system and by system, not by user. Thus, any user on the system can reuse any System DSN.
System DSNs have another advantage. For example, let's say you have a development server, a local system you work on, and a production server. Furthermore, the development server and the production server each have a SQL Server database that the Web application you're building uses. The development server is running a test copy of the database while the production server runs the live copy.
How do you set up your system so you can develop and test your application on your local system and development system, then deploy it to the production server without changing any code for the data connection? The secret to this juggling act is to create a System DSN on each system with the same name. Point the System DSNs on both the development server and the local workstation to the development server database. Point the System DSN on the production server to the production database. All three DSNs must have the same name. In your Active Server Pages (ASP) application, point the application's data connection to the System DSN name. Now you can move the application to the local workstation, the development server, or the production server without changing any connection information. You can use test data to work on the application and use production data to deploy the application.
Another advantage of System and User DSNs is their speed. Both are faster than a File DSN pointing to the same database.