Getting Things Done (a dumb move and what I learned)
Here is a story about something I did in the dim and mystic past to "get the job done" that I lived to regret...
What I did (and why it turned out to be sort of dumb).
Once upon a time, I was working at a company which will remain unnamed as a SQL developer. MSSQL 6.5 was the latest release and we were implementing a new CRM that required that version. The only problem was that our accounting and shipping software were using MSSQL 6.0. We needed to be able to take an order using the CRM software running on SQL 6.5, move the information to the accounting database running on the SQL 6.0 server. Then, once payment was made, print a picking ticket for the shipping department so they could fulfill the request.
At that time, MSSQL replication (I believe it was still a project called "wolf pack" or something like that.) did not exist between MSSQL 6.5 and MSSQL 6.0. So, we hired a pair of contract developers to write an application that would do this for us. And, they worked for weeks on the project. Finally, one week before we were scheduled to launch the new CRM, they were ready (or so we hoped). We launched the new application they had created for us and watched as it ate both servers alive. It sucked all the processing cycles and left nothing for our other systems to use. We also saw that somewhere there was a tremendous memory leak and after a very short time the server running MSSQL 6.5 would hang, then crash.
The contractors worked furiously all week to fix the problems, but by Friday afternoon, with only two days before Go-Live, we needed to either delay the launch or come up with a viable "Plan B". My manager came to me that afternoon and asked if there was anything I could do. His request was for some way of replicating around 400 transactions per day for a few weeks while we found a way to fix the application that was supposed to be doing the job.
I sat and thought about this all afternoon. And, by about 3 PM, I had a plan. I would use two extended stored procedures, xp_sendmail and xp_readmail to accomplish the task. The concept seemed reasonable enough. When an order was entered into the MSSQL 6.5 database, I would simply use dynamic SQL to generate the commands required to insert the relevant data into the accounting database running on the other server, add the commands as the text body of an email message, add a special subject line to the message so that the receiving server would know that this was something to pay attention to, then use xp_sendmail to connect to our Exchange server and send the message. Every couple minutes, the MSSQL 6.0 server would read any messages that came to it with the special subject and execute whatever commands were contained in the body of the message, then send a response back to the MSSQL 6.5 server to inform it that the message had been received and acted on. What could go wrong?
I spent the weekend working on this and by Monday morning, we were ready. Everything worked beautifully and I nearly broke my arm patting myself on the back... But, after a few days, I learned a valuable lesson. About noon one day, the Exchange server failed. No one could find the cause. To make matters worse, the CRM running on the MSSQL 6.5 server stopped working as well.
After much scrambling about and a significant loss of productivity for the company, we found the cause. The sent mail folder on the Exchange server was full and could not accept any more messages. Once that happened, Exchange quit responding to xp_sendmail. And, since I had embedded the call in triggers on the relevant tables in the MSSQL 6.5 database, the triggers were now creating locked tables while they waited for a response that would never come from Exchange.
But all was not lost. All I had to do was find some way to keep the sent mail folder from filling up. Unfortunately, I could find no automated way top do this, so I had to manually empty the folder, one email at a time, at least once each day. This took several hours each morning and I was not unhappy when the task was handed off to our intern after a couple days.
What I learned (and you should remember).
- Anything you do that makes it into production, no matter how embarrassing, will stay around a lot longer than expected. We were still using my email replication and we still had an intern spending a larger and larger part of each day manually removing messages from the Exchange sent mail folder a year later when I left the company. I don't know if or when they ever fixed the problem.
- Nothing remains static. 400 transactions per day quickly became 400 transactions per hour. And as the transaction count increased, the tedious task of emptying the Exchange sent mail server took longer and longer for some poor intern to deal with.
- Just because you can do something does not mean you should. Never, never, under any circumstances embed calls to any external system in a trigger on a table. If you do, someday, you will regret it. Any external dependency wrapped in a trigger is an invitation to disaster when the external thing (whatever it is) fails to respond as expected.
- But, remember... Sometimes, you just have to get stuff done. In spite of the embarrassment I felt, we were able to launch on time. No one above me in the chain of command ever once complained or held this against me. We went public shortly thereafter and one of the factors that improved our value was the new CRM we were using that allowed us to process many more orders per day than we ever expected to have.
I hope you found my story interesting. Maybe you learned something. But, even if you didn't, I hope it was entertaining. I'll try to add a few more stories from my "dim and mystic past" in the future.