dsn vs. dsnless connection 1

[cm80]

Hi,

I was just wondering if someone would be able to explain the difference between a dsn and dsnless connection using ADO and which one is better to use,

thanks,
Carol

 

[ayladilara]

Acording to Microsoft and many others the best method is to use OLE DB one

 

[Ovatvvon]

DSN-less will provide faster service 99% of the time (unless you happen to have specific situations which would take too long to explain here right now for this purpose).

DSN general is more user friendly, it'll keep track of the database if you move it, you can use the same dsn name from multiple sites to relate to one database...however it takes just a bit longer to make connections to the database which can really add up on commercial servers with a lot of site hits.

To read more on the types of connections, read the following FAQ: faq333-178

Hope this helps. -Ovatvvon :-Q

 

[link9]

I would like to add a bit of personal experience to this one. We had to move our site from one machine to the other a year ago, and my connections were dsn-less. Ouch.

Even storing them in include files took me a very long time to migrate the site.

After that (and due to many other factors), I changed all of my connections to DSNs. I saw no performance hit. Although I didn't really "benchmark" it, per say, all of my reporting sites ran just as fast and as good as they did before... if not better due to our new hardware, which had nothing to do with the connections, themselves.

Then, about a month ago, we set up a clustered environment between our two servers (one web, one data). In a clustered environment, if one server goes down, the other picks up services. So, I had to make sure that all data connections worked on server2 that were currently working on server1.

Well, we went into the registry, created a .reg file from the existing dsns (approx. 30 seconds of work), moved that file to server2, and double clicked it (another 10 seconds). Presto -- it worked perfectly.

So, in a grand total of 40 seconds, we had all data connections working on server2. Portability. Gotta love it.

Many people will tell you to use dsn-less connections for performance reasons. Those people have valid points, and I won't contradict them on those points. However, when it comes to flat out ease of use and portability of a site, there's simply no contest between the two. DSNs will win out every time.


paul

 

[rtshort]

I think we are getting off of the mark here. The difference between a "dsn" and "dsn-less" connection is

DSN uses a connection object

DSN-LESS creates the connection object with code

Someone please correct me if I'm wrong. I'm just getting into ASP myself. Rob
Just my $.02.

 

[Ovatvvon]

Paul,

That's pretty good to hear as I would rather use DSN's, but don't because I had always been told to stick with DSN-Less.

A number of months ago, I remember you describing your environment. You had mentioned somthing to the fact that using a DSN-Less connection would have been slower because the server had to go over a 10MBs line a ways away, vs. useing the DSN connection which went over a 100MBs line 2 feet away...If that's the case...would that have somthing to do with the DSN performing around the same speed as the DSN-Less connection...or is that irrelevent due to different time periods? Also, how busy are your servers (just out of curiousity)? Just wondering the performance hit when they are more busy on different connection levels. I'd be willing to bet it wouldn't make a big difference at all with which connection you use on a not-too-busy server. So, just trying to get a better understanding of the scenario.

It would help me anyway...if in fact they are busy servers, and the DSN didn't have any noticeable performance difference, then I would probably end up using the DSN connections as well.

Thanks for your time!

-Ovatvvon :-Q

 

[durug]

Besides the performance we forgot a very important point of view here:
If you need to deploy the application to the client use dsn-less connection(stored in registry for example). None of the client wants do to extra work besides paying you

If you are the person who is administrating the application, and you don't care about performance (small application) you could use DSN.

Regards,
Durug

 

[link9]

Durug,

A DSNless connection stored in the registry? Well... the performance difference between a dsn and a dsnless connection is the registry lookup for a dsn connection... so... I guess I miss your point. What would be the benefit, and to be honest, I've never heard of storing a dsn-less connection in the registry anyway. Any more info on that?

And as far as application deployment goes, DSNs will make your life easier in that respect... not dsn-less.


Ovatvvon,

Yes, I was referring to that experience several months back. Basically, if I used dsn-less, then the server would have to go out and do a DNS (not DSN) lookup to find the server that was right above him. That lookup took place over a 10mbps line, and then communication between the two machines would also take place over that same line... On the other hand, DSN would let them communicate on the same subnet over 100Mbps (which has since been upgraded to fibre, so it's at the speed of light now ;-) -- not really that fast, but I just like saying that because it sounds cool). So I realized a noticeable difference in my application performance when I changed the connections to DSN.

In retrospect, I suppose I could have used the subnet IP address to accomplish the same thing. I didn't think of that at the time, but as it turns out, because of the portability issue I described above, I am very happy with my decision.

Now, as far as high traffic (and I define that as at least hundreds of concurrent connections) goes, I couldn't tell you honestly. We do many different things w/ our servers... all having to do with research. So we do reporting, surveying, ordering, etc...

On our high traffic days, I havent taken the time to go in and see what's taking the resources. The surveys use a separate process for each user... third party solution. While all reporting and such are custom written db applications, which use connections to our SQL Server. We have good days (response wise), and mediocre days... depending on what we have in the field, and like I said, I just haven't bothered to measure exactly what's eating the resources on the bad days. My suspicion is the survey software (which has nothing to do with db conns), but that's just a suspicion (and a likely one at that, seeing as I write the db stuff ;-))

You know what I might do to try it out? The next time you write an application that you expect high traffic on, I'd code it such that the conn is stored in and asp include, and first make it dsn-less. Then, wait for traffic to go up... jump on there and get out your stopwatch and run some routines. Time em.

Then, (very quietly) sneak in there and change the connection to use a DSN, and then get your stopwatch back out and go run the same routines. I would really be surprised if you noticed any difference, but I might be completely wrong on that one.

If you do that, though, please let us know what you find. I might even run a little internal test this week since now you have my curiosity peaked. If I do, I'll also let you know what I find.


paul

 

[durug]

Paul,

from my experience usually the connection string is stored in registry where you access it from DLL when you create recordsets, or running stored procedure. What is the other way to create a dsn-less connection? Stored in ASP? So that every hacker can see it? Or stored in DLL so that everytime you change the server you have to compile the dll again?

 

[Ovatvvon]

Durug,

My thinking is, if you store the conn code in ASP, if there were any hackers good enough to get into your system to download the file including the server-side code, then they more than likely have full access to your system anyway, and would be able to move about freely on the server and come across the db(s) anyway. Perhaps I am not on the same page as you though, I'm not sure. Could you explain how that would be a security risk a little more in detail?

Paul,

Unfortunately, My sites are mostly personal (with exception of those I write for the Marine Corps), so the traffic isn't that high (Military is very picky about stuff and don't like people (even the creators) to mess with somthing if it is not broken). I mostly want to know about the differences for any upgrades I make to the software for the Marine Corps...and additionally, for future knowledge after I get out.

However, I am really interested to here of any results that you find if you end up doing it. Please do let me know.
-Ovatvvon :-Q

 

[link9]

Durug, I'd also be interested to see an example of storing a dsn-less connection in the registry. What would it look like, and how would I go about accessing it to use in my application?

 

[durug]

1. Function to open the recordset:

Public Function OpenRecordset(ByVal SQLQuery As String) As ADODB.Recordset
Dim adoRecordset As ADODB.Recordset

On Error GoTo ErrorHandler
Set adoRecordset = New ADODB.Recordset
adoRecordset.ActiveConnection = GetConnection()
adoRecordset.CursorLocation = adUseClient
adoRecordset.CursorType = adOpenStatic
adoRecordset.LockType = adLockBatchOptimistic
adoRecordset.Open SQLQuery
Set adoRecordset.ActiveConnection = Nothing
Set OpenRecordset = adoRecordset

CleanupObjects:
On Error Resume Next
Set adoRecordset = Nothing
On Error GoTo 0
HandleError True, False
Exit Function

ErrorHandler:
SaveError ModuleName, "OpenRecordset"
Resume CleanupObjects
End Function

2. Function to get the connection from registry:
Private Function GetConnection() As String
Dim objShell As IWshRuntimeLibrary.IWshShell_Class

On Error GoTo ErrorHandler
Set objShell = New IWshRuntimeLibrary.IWshShell_Class
GetConnection = objShell.RegRead("HKLM\Software\Invotronics\B2B\DBConnection&quot

CleanupObjects:
On Error Resume Next
Set objShell = Nothing
On Error GoTo 0
HandleError True, False
Exit Function

ErrorHandler:
SaveError ModuleName, "GetConnection"
Resume CleanupObjects
End Function

Registry.reg file which is adding the values in registry (run it before you start developing your application)

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\Invotronics\B2B]
"DBConnection"="Provider=SQLOLEDB;Server=Servername;Database=B2B;UID=sa;PWD=gotcha"

Hope it helps.
And I think is a big difference hacking your asp page or reading a value from registry. But of course everythink can be hacked, ...

 

[cfk]

One important factor to note is that alot of hosting
companies try to charge (~$25/dsn) for each dsn.
With a dsn-less connection you can bypass this problem.

When it comes to migration of dsn-less conections from
one server to another theres not really a problem.
With sql-server the only thing that needs changing is
the name of the new server.
With Acces if the directory structure is the same the
there is nothing to change.

Try this for access migration.
Store your access db in the root directory of your site.
Its not totaly generic but I use it in all the access
based sites I've built.

cnConn = Server.CreateObject("adodb.connection&quot
cnConn.Open getStrConn(mydb,"DatabaseLocationDir&quot

Function getStrConn(database,rootdirectory)
Dim strConn
Dim strConnStop
strConn = "Driver=Microsoft Access Driver (*.mdb); DBQ="
strConn = strConn & Server.MapPath(Request.ServerVariables("PATH_INFO&quot)
strConnStop = InStr(lcase(strConn),lcase(rootdirectory) & "\&quot
strConn = Left(strConn,strConnStop + len(rootdirectory))
strConn = strConn & database & ".mdb;"
strConn = strConn & " UID=Admins; PWD="
getStrConn = strConn
End Function

 

[link9]

Durug,

That's a very interesting approach. Thanks for the information.


paul