It’s been a bit of a stressful ride around here at SSWUG.ORG of late. We made a significant update to our systems and in doing so, exposed a couple of surprised in compatibility between on-premises, owned SQL Server and SQL Server in the cloud, in this case, Amazon’s RDS.
Would I do it again? In a heartbeat.
I messed up on what seemed like a very small issue – one that would be simple to correct for, that ended up taking all sorts of time and work to overcome.
Quick background: SSWUG runs on WordPress for the base site, but has a number of functions and functionality bits that run on .Net, SQL Server and other platforms. No problem, we live and breath mixed environment stuff, so what could possibly be the challenge of migrating from SQL Server on a VM to SQL Server in RDS?
The answer, in our case, was linked servers. We had set up linked servers between SQL Server and the MySQL system to manage subscribers, articles and ultimately, the newsletter.
RDS doesn’t support MySQL as a linked server connection. I haven’t had much chance to work with Powershell, so I thought I’d write a quick bridge application that pulled what we needed from one system and saved it to the other. Nothing like “bath by fire” – along with so many people writing to say they’d not received their newsletter quite yet and…where is it? I was glad to hear people missed it, but stressed!
Then, we actually had (gasp) part of our system (gasp again, you’ll need it) running on classic ASP. This presented some interesting challenges for building this link and updating the newsletter generation code and all of that. 32- and 64-bit driver wars ensued. Blood in the streets. Cussing SWYNK. It was not pretty.
The result? The links have been created. The Powershell utility functions have been created. The newsletter code and processing has been re-written pretty much from the ground up. And… we have a newsletter once again, along with some other functionality behind the scenes to help us better manage the tools going forward. But boy was it painful. In addition, we have shiny new SQL Server RDS serving up the bits and that’s a great thing as well.
We’d tested and beat up on the systems before the cutover, extensively. We knew about the linked server thing – but the complexity of bridging around it and the mixing of drivers and all of that was our surprise challenge. We’ve learned a lot, from ADO in Powershell to security and firewall and encryption updates. All of it was a fantastic learning experience. If you’re interested in the Powershell lessons learned, drop me a note in the comments below and I’ll write up a post about it.
I’d like to think you won’t see any new issues, just business as normal, even perhaps better than normal. If you do see anything at all, feel free to let me know and we’ll get on it immediately.
Tomorrow, we return to the regularly scheduled “what the heck is going on with data stuff” type posts.
Happy holidays!