All of this talk about considerations for moving your database to a new SQL Server (be it the cloud or on-premises) brings up a number of questions about your systems, how they’re set up and running and what types of things you need from a potential update to the infrastructure.
There are many outlier type situations that, while they’re critical functionality to you, may not appear on checklists to consider when you’re moving your databases around. Some of those may not even be deal-breakers, but they certainly can be successful-move-breakers. Here are a few things we’ve run into with both our own systems and those of clients:
– external processes that span servers – in many cases, there may be processes that span servers to pull information from multiple sources (reporting, or aggregation services). These are famous for causing issues with a new server as server names change or access isn’t set up correctly for these lesser-realized interfaces to your server. Reporting is a killer app in this regard, and if you offer ad-hoc reporting across data sources, it’s a great idea to consider a test migration and visiting with the users of that system to make sure they’ll be covered in the move. In some cases, moving to a hybrid or cloud solution will take extra steps to support the security and availability of that information for their system. This might mean reviewing an on-site gateway to the cloud, or security setup options to allow access, or even firewall updates to allow access, depending on the types of things that are happening. See also “linked servers,” next.
– linked servers, if they’re used by your application infrastructure, are one of those things that can be a non-starter for cloud-based solutions. Many cloud-based SaaS solutions don’t support linked servers or have limited support for only some types of linked servers. If this is a requirement, you may need to revisit the need, or you may have to find other ways to get the information pulled together across those server boundaries. There have been many cases where a VM or on-premises linked server solution had been in place to talk with different types of servers (SQL Server to MySQL, for example) and it was necessary to code around this issue. While this is one of those outlier type things certainly isn’t “everywhere,” it is critical to specific solutions, so it had to be addressed.
– OS-level or external server processes – these can be gotchas that are less apparent – make sure you know of any server tasks or other automation that may be hitting your server, what it does and that it is supported in the new environment. In too many cases, these are sporadic operations, which means as you cut over your systems, all seems good, but then some period of time later you realize that some numbers aren’t updating or operations are incomplete, etc. These can be harder to spot, depending on whether you’re working with services, Windows tasks, or SQL jobs.
– SQL Jobs – check SQL Jobs and make sure they’re implemented on the new system (and perhaps that they’re DISABLED on the old system, to prevent duplication of work). These can be tricky because if your old server/database is still up and running, the job will seemingly still be operating correctly. There won’t be errors to trap and it will take noticing that its target operations aren’t getting done to realize something is amiss. Make sure you go through the jobs and understand them, and be meticulous about what they’re connecting to, the operations that are happening, etc.
What other types of gotchas have you seen when moving systems? This isn’t so much a cloud thing as a “we’re moving to a new system” thing – but I’d love to hear additional things you’ve run into. Tell me I’m not alone in finding odd/surprise things! 🙂