Best Practices: Changing SQL Server port

MatthewRantala1

Hello,

We are in the processing in moving our geodatabases to a newer SQL Server (2016) instance. Our IT department wants to use a different port (53094) that we're currently using (1433). 

While we could proceed that way, we realize that all of our legacy MXDs would need to have their data sources remapped. So not a good solution (enterprise wide, we probably have thousands of MXDs, although not all used).

We use an alias  (GIS_xyz) to reference our database server & what we are trying to determine is if there's a way to essentially create an alias that also specifies the port.

I've been trying to see if I could use a Windows User or System DSN as means but have not gotten that to work yet although that seems like it should partially work. The problem I see with that, however, is that  it seems the user/password are embedded there. So if I am able to create a User Data Source named "GIS_xyz" that points to the new instance/port, it will have a specific user name hard-coded in it. We use SQL Server Authentication with multiple accounts (one for data owner, data editor, data viewer, etc) and it does not seem like I can create multiple DSNs with the same name that have different account info in them. Ideally, I would want the credentials in our .sde files to pass through to the DSN.

Any tricks/tips on how to gracefully switch to a different port without having to repath all your legacy MXDs?

Thanks!

crussell_esriau

Hi Matthew

Might be a little late to the party on this one, but I have previously come across this article concerning SQL aliases - Overview of SQL Server Aliases (sqlshack.com)

I haven't needed to use this process myself, but the suggestion is that you can create a SQL alias for a named instance using the named pipes protocol.  So a CNAME GIS_xyz could resolve to dbserver\instance without having to specify the instance or port in a connection string.

This would only work if your original alias GIS_xyz was entered without the port in your database connections, which should have been the case given that 1433 is the default instance and the port doesn't need to be specified.