The Weekend Database Migration Blues: Lessons from the Trenches

It was a quiet Saturday morning, the kind you hope stays quiet during a major operation. My task? Migrating a 200GB on-premises SQL Server monolith to an Azure Managed Instance—a job that required shutting down all user and application connections before taking the final backup.

I thought I had planned for everything.

I used the KILL command to terminate active sessions, but I’ve been in the game long enough to know: there’s always that one connection lurking, waiting to reconnect, no matter how many assurances you get from the application teams. To stay ahead of the curve, I decided to take an unconventional approach: disabling all SQL Server logins except the administrator.

This gave me the confidence to proceed. I took the final full backup, restored it onto Azure, and went to bed satisfied. The migration was successful—or so I thought.

Monday morning rolled around, and I got a ping from the application team: "Why are two of our apps still connected to the old database?"

Turns out, I’d overlooked a critical detail: Windows-authenticated logins in SQL Server cannot be disabled. Two applications using Windows authentication had managed to connect to the on-premises database over the weekend. Luckily, these apps hadn't written any data—but it was a close call.

To prevent further connections, I pivoted quickly. I changed the SQL Server port number and restarted the service. This effectively blocked any application from connecting, while still allowing me to RDP into the server and connect to SQL Server via SSMS using named pipes.

It was a tense moment, but in the end, no data was lost, and I walked away with a deeper understanding—and a story to share.

---

Lessons learned:

- Disabling SQL Server logins is a great idea for stopping access, but remember that Windows-authenticated logins don’t play by the same rules.

- Changing the port number for SQL Server and restarting the service is a reliable fallback when other methods don’t work.

- Always test your assumptions—what you think is "off" may still have a backdoor.

What about you? Have you ever had to wrangle rogue connections during a critical database migration? What’s your preferred way of kicking application connections out? Let’s hear your stories—I’d love to learn from your experiences!