Is there a sane way / best practices way to swap out a DB from MySQL to SQL Server in a multiple large projects? Some of the projects are ASP projects, some of them are windows services, some are standalone windows apps. I don't think any ORM is used. I think its just SQL statements all over the place.
Nope, no sane way. It will require an insane amount of work, testing, unexpected bugs and missed deadlines. I would recommend:
- Start polishing those tests now. Or start writing them.
- Look for a commercial product that will help with the data transfer.
- Set up your new area asap and start doing the mapping, just to get an idea of what's involved.
- talk (or post here and Stack Overflow) to folks who know these issues. Post specific questions about primary keys, indexes, triggers, etc. to start getting a handle on it.
- Make it a project that gets enough resources (several people and several months) to do it. Make sure you communicate how much work it is - once you yourself a got a better idea (right now you don't).