Movin Madness

This month our client migrated their servers to another environment. When the actual date for the migration was upon us, it felt a lot like the Moving Van had arrived at the client's home and he was in his bathrobe frantically trying to wave it off for a few more days. For the most part, the server migration went fairly well, with some issues (big and small). I've outlined a few of them -- some of which drove me crazy. MSXML 4 - Access Denied We've got a neat little flash microsite that pulls an xml feed from an external site using classic asp. Interestingly enough, the simple ServerXMLHTTP method .Send() for a simple URL was returning an Access Denied error. Turns out, this is a feature of security hardening in MSXML4 SP2. I had to change the Local Security Policy, add the URL to the Trusted Sites internet zone in Internet explorer and REBOOT the server. Quite a bit of hassle just for a xml feed. Cannot resolve conflict in Collation Restored databases from the old production envrionment onto the new environment, and found that some applications weren't behaving as expected. When poking into the error, I found that i was receiving an error based on the current Collation (the language and sort order of the database) between databases were different. This was probably because the regional settings between the machines were different, and the databases that were created on the server defaulted to an incompatible. To resolve I had to: 1) Create a new version of the database with a different name 2) Use an ALTER DATABASE statement to set it to the desired collation. 3) Script the original database into a single block of SQL DDL statements 4) Remove all collation specific references on fields as the script would try and create varchar fields with specific collations. 5) Use a DTS task to copy the data from one database to the other, specifying in the task to Use Collation so it would adopt the collation of the target machine. 6) Drop the original database and rename the new version to reflect the original name. Cannot enroll in new Transaction Brilliant. Originally I asked the new hosting provider two months ago if they had any best practices on how to configure an environment with a firewall between the database and web servers. The only answer I received from their tech team was to use port 1433 -- which in lamens terms, is like saying cars need gas -- SQL always uses port 1433. The problem I knew we were going to have is when you actually try and use distributed transactions from the web server: there is a lot of communication between the web server and database -- way more than just port 1433. When I found out that they weren't aware of this concern, that should have been my first guess. I gave up that the hosting provider was going to make this easy for me, so I provided them very clear instructions on how I was going to configure DCOM to restrict the web and database servers to use specific ports. I clearly told them that once this was done, I would need two way (inbound/outbound) communcation on these ports. I outlined very specifically which ports i needed TWO WAY communication. When I recieved an email confirmation that they had opened the ports for TWO WAY communication, I politely thanked them and went back to the process of configuring my applications. When I received the unable to enlist in new transaction error, I was a bit suprised, but as I didn't have a whole lot of time to fully test the application in the new environment, I wasn't that surprised. I thought I might be having problems with incorrect registry settings, or name resolution, etc. It was about fourty minutes later, after double-checking my settings and reading knowledge base articles on this problem, that I discovered that the ports had been opened for the web server, but not the database. The email I sent the hosting provider, to which I attached my previous email with the clearly outlined instructions, retrospectively, wasn't that polite. I only wrote SOME of the email in ALL CAPS. (Incidentally, why is it THAT ALL CAPS LOOKS LIKE YOU'RE SHOUTING?????) Unable to convert varchar to datetime When I realized that the default regional setting of the server didn't really help, I went digging into the code. We had a form that collected the data in a very specific format: Please provide your date of birth (yyyy/mm/dd): At the code level, some very ancient asp classic code that was building the sql statement inside the script (terrible!!!) and opening the recordset with the target SQL. strSQL = "SELECT count(*) from myTable where DateCreated = ' " & Request.Form("TimeStamp") & " ' " oRs.Open strSQL, oConn Brutal. Here we're basically asking the SQL server to try and resolve the text into a datetime using the text format the user supplied -- if your SQL box is configured with a different time format, you're pretty much screwed. While writing inline sql inside your presentation code is considered extremely bad form, I can appreciate the developer's complaint that it's too much work to write a custom COM object just to write a silly database call. But if you have to use inline sql, you should at least attempt to use a stored procedure. If you so lazy that you can't write a stored procedure and you have to write inline sql because you're a toe head, then heaven forbid you write a few extra lines of code and use a parameterized sql statement, as so: strSQL = "SELECT count(*) from myTable where DateCreated = ?" Dim oCmd set oCmd = Server.CreateObject("ADODB.Command") oCmd.Parameters.Append oCmd.CreateParameter("TimeStamp", adDBTime, adParamInput, 8, Request.Form("TimeStamp") ) oRs.Open oCmd, oConn, 1, 3 Although it's a couple extra lines, I sleep better knowing that ADO will take care of the datetime format conversion.