DNS or NOT DNS, that's the question

I want to use ASP with large MSSQL database (over 200,000 records), should I use DNS or direct connection string for database? It seems Microsoft recommends DNS but there is a discussion that is slower, so which one is a better alternative?

 

I'm confused.

Do you mean DNS or DSN?

 

I think he means DSN, and I would still recommend it, even if it is a bit slower than a direct connection name/string.
You will thank me later when you have decided to change database server

or in other words: if your system is that heavily loaded that the type of connection is an issue, you should get more hardware to support the needs of your applications.

 

Sorry, sometimes typing to fast is not a very good idea, I mean DSN.

 

Go for DSN all the way. It's more secure and it's easier to switch over to a new database if you want to, and the speed reduction isn't normally that serious.

 

What people call DSN, means that you willbe using ODBC connections. What you call "direct connection" is an OLE DB connection. OLE DB is a native protocol and does it all - connection pooling, optimizations, etc. For non-MS DBMS, like MySQL, ODBC offers more mature and probably better-tested software. Going MS-to-MS, though, OLE DB is a better choice.

J.D.

 

depends on what your future plans are. Since you are asking the question, your answer is DSN. It leaves more flexibility for you in the future (although it's not impossible to make a switch if you go ole-db, it will be much easier with a DSN). The performance consideration is not a large one, and believe it or not 200,000 records is a rather small database.

 

Here's both. How's DSN help you switch easier?

Connection.Open("Provider=SQLOLEDB.1;Data Source=machine;User ID=user;Password=password;");
Connection.Open("DSN=dsn;UID=user;PWD=password")

The right approach is to create a function that connects you to the database and call this function from everywhere. For example:

<% @ language="jscript" %>
function DbmsConnect1()
{
   return Connection.Open("Provider=SQLOLEDB.1;Data Source=machine1;User ID=user1;Password=password1;");
}
function DbmsConnect2()
{
   return Connection.Open("Provider=SQLOLEDB.1;Data Source=machine2;User ID=user2;Password=password2;");
}
...
var connection = DbmsConnect1();
...

Code (markup):

This way there's only one place in the code to change if any part of the database configuration changes.

Connection type doesn't have anything to do with the size of the database. It affects how fast you connect, how fast you transfer data over the wire, how fast you convert data to your native types, how your connections are pooled, etc.

J.D.

 

That's how things should work. In an ideal situation you are only changing one file that contains your connection info. In many situations, that connection information is repeated in multiple files. In this case, the chance of missing a file increases with the number of files containing a connection string. For a beginner - it's best to use a DSN because in that case you just end up changing one odbc file.

Right. two separate thoughts there. Probably should have been different sentences or paragraphs.