SQL Databases breaking under NT hosting

[byron]

Many users are reporting that their MySQL/MSSQL-driven databases on NT sites are starting to fail, primarily stating the server can't be found.

All Windows users hosting database sites need to take the time and make sure that the hostname being used to connect to the database server is the full, correctly formatted hostname. This would be the name of the server, followed by your service domain (or hsphere.cc which should also work in all cases.)

An Example:

Joe Customer's website database is hosted on MSSQL4.
Then you should use "MSSQL4.hsphere.cc" (or substitute hsphere.cc with your service domain instead) in your site's connection strings and/or DSN settings in the Control Panel, and not just use "MSSQL4" as a hostname by itself. (Using short hostnames may save time, but it is a bad habit to keep.)

Taking the time to verify these settings will help to keep your database sites online, and may eliminate some annoying downtime down the road.

[Brangwyn]

I'm speculating, but a couple of recent comments seem to imply that there are some IP changes going on behind the scenes that we've not been told about ... true, false ?

[admin]

This did not come the IP changes but from the vlans that are bing setup to better secure the network. But you are dead on for the IP changes, there is a few NT systems that were not setup 100% right to make the setting up the vlans "easy" or given the right blocks of IPs.

Once we get everything mapped out we will give full details, this all has to do with making things more secure, faster and bring rocketcolo over to our network from theres.

I am not doing the IPs but can get info or answer any questions if you have them.

[newmem]

I was the first one to complain about this and also started a thread about MSSQL 3 not connecting through the site.

I was using "MSSQL3" directly in the DSN ODBC setup. I changed passwords for a whole day and tried various combinations. Nothing seemed to work.

Suddenly it worked. Then I changed the server name back to mssql3 from mssql3.servicedomain.com because I feel it is faster for the server to look for a local address then an internet address for which a DNS lookup would be required.

The site is still working without any problem even though I have "mssql3" only as the server name. I think the problem was something else. I would advise everyone using MSSQL servers to try connecting to your website if the same error is coming or not. You will be able to connect via EM, but not through the website, whether using a DSN, DSN-less or OLEDB connection string.

The problem seemed to be on NT28 only as I tested on another NT server and it was working fine there.

[admin]

Not all the NT servers are in the vlan yet, once they are the mssql3 thing is not going to work. BTW mssql3.server.com is no slower it only needs to look it up the first time, if you really want to you could use the IP that skips DNS and everything, but if it changed you would have to update your code again. The safest way is always FULL DNS, I never even though of any one using just the "name".. LoL

[antic]

You might also want to start using an OLEDB driver to connect, instead of ODBC which has been an outdated technnology for years now. I don't even enable ODBC in my plans.

[admin]

Very good point antic.

[newmem]

Originally Posted by admin

Not all the NT servers are in the vlan yet, once they are the mssql3 thing is not going to work. BTW mssql3.server.com is no slower it only needs to look it up the first time, if you really want to you could use the IP that skips DNS and everything, but if it changed you would have to update your code again. The safest way is always FULL DNS, I never even though of any one using just the "name".. LoL

see? you never know what we can do as opposed to the "normal" way
that's why in all websites that I get designed, I think of all wrong things that the users can fill into text boxes and filter out the garbage.

The ODBC form did not reject "mssql3" along and gave a green signal, and it even worked, so I did it.

If I use MSSQL3.hsphere.cc instead of MSSQL4.servicedomain.com, then will the DNS lookup be required or it will know that it is a local address? Why search for something over the internet (mssql3.servicedomain.com) when you know that it is sitting next to you in your local network (mssql3)? It will take longer.

I am planning on converting all connection strings to OLEDB, but after I find the time. Till then, ODBC will work fine for me.

[ashian]

I have the same problem on NT38 and Mssql8 , I use full address , but some times the site can not find, I useing a local copy of my site and use the mssql8.DOMAIN.COM it worked and at the same time the copy of my app in Nt38 dont work, I see this problem several times in this week some time from 1 minute to 10 )

[newmem]

if that is the case, then this is a more serious problem than I thought. I noticed this only when a few people complained about not being able to access the website. What if it happens every day a few times and those who visiting at that time do not bother to complain?

Is there any way that I can get a notification when any ASP script is not able to connect to the db?

[hari]

I have the same problem on NT10 and Mssql6, I use full address, its not working. I see this problem from last week, before updating the server it was working fine but now its not working. i have 5-6 sites based on MS-Sql server (MSSQL6).
i tried using MSSQL6.Bongotechnologies.com, MSSQL6.hsphere.cc still its not working.

Kindly anyone help me.

[antic]

It's easy to get your code to send you an email when the connection fails or indeed if there are ANY script errors on your site. I do it and it is a great feeling to check emails every morning and see nothing! That's means everything is working fine.

All you need to do is use (in ASP) an Application variable to store the time of the last error (so you don't get swamped with emails - this is very important!)

So in global.asa, on App_Start, add the line:

Application("LastErrorTime") = Now

Then, in your code where you make the connection to your database (this should be in an include file of course so there's only 1 place in your code where a db connection is made):

Dim iErr, sErr, objCDO

On Error Resume Next
conn.Open "your connection string"  ' <-- put your db connection line here, whatever it is.
iErr = Err.Number
sErr = Err.Message
On Error Goto 0

If iErr <> 0 Then
  If DateDiff("n", Application("LastErrorTime"), Now) > 60  ' limit to 1 email per hour.
    Set objCDO = Server.CreateObject("CDO.Message")
    objCDO.To = "youraddress@yourdomain"
    objCDO.From = "postmaster@yourdomain"
    objCDO.Subject "Db Connection Error"
    objCDO.TextBody = _
      "Time: " & Now & vbCrLf & _
      "Domain: " & Request.ServerVariables("HOST_NAME") & vbCrLf & _
      "Error: " & iErr & ", " & sErr
    objCDO.Send
    Set objCDO = Nothing
  End If
End If

That was all written by hand just then, so I don't guarantee there's no bugs, but that's the general idea. In fact I guarantee there is at least one bug.

Hope that helps. It will also help you send some detailed info to support if you need to. Add whatever detail you want to the email body.