Administration, Azure SQL Database, Editorials, SQL Server

Bridges Between Servers

Many times as you build out environments, you’re left pulling information from different SQL Servers and different sources, so you can assemble it in a common location. This may be the case where you’re pulling information from a SQL Server, a MySQL server, Oracle and other systems, so you can create reporting and analytics runs at the information.

These bridges – in SQL Server they may be linked servers, in others, they may be code-based – are often a mix of connections. At times, you end up with an “all of the above” situation where you’re left to glue together whatever works for your specific data sources.

It’s important to make sure you’re taking a step back from these and documenting – not only the security for user name and password, but also where the source information comes from (what’s the server, and what is feeding it?). In addition, you should be sure you’re taking copious notes in any code-based solutions (PowerShell and other environments) so you can rebuild those connections if needed.

Remember, if you’re updating systems, or moving providers, or both, you’ll need to re-establish those connections to keep your systems alive and well. Think about setting up a central location that you can build out that will keep track of these programmatic connections and their requirements. There are still some solutions that use ODBC connections, for example, and there may be applications that have setup options to provide the information that becomes the connection string.

There may also be server-based scheduled processes that have to be updated. These are one of those things that run… and nobody remembers until they break. You’ll want those credentials, expected operations, and target systems to be documented before you need the information, not after.

There have been many cases where information is pulled from WordPress databases (MySQL) into a SQL Server, then accessed by a cloud provider automation platform that pulls the information from one SQL Server into another, doing operations on it as needed along the way. That operation, and the “chain of custody” of that information, is one of those things that can be difficult to recreate without the right types of information available to the folks that are putting it together in a new environment.

One tip: as you’re documenting, talk a little about the “why” of what you’re doing, explain how things got there. It could just be a sentence. But then also use another bit to provide considerations and thoughts about what may be able to be updated going forward. We’ve been able to use that information to review our connections and processing and come back and update functionality to better match the evolving environment. With that documentation, it’s very efficient to review and identify areas that are better targets than others.