topic Re: Best Practices: Changing SQL Server port in Geodatabase Questions

Best Practices: Changing SQL Server port

MatthewRantala1 — Tue, 12 Jan 2021 23:38:07 GMT

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_XXX) 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_XXX" 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!

Re: Best Practices: Changing SQL Server port

MatthewRantala1 — Thu, 14 Jan 2021 22:27:06 GMT

Answer from Tech Support is that if the port changes, you need to update your MXDs. We have a script for doing that but were hoping to save our casual GIS users the trouble. Tech Support did not have a suggestion on how to avoid that other than keep your port the same.

Re: Best Practices: Changing SQL Server port

CraigRussell — Fri, 12 Feb 2021 01:31:47 GMT

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_XXX 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_XXX 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.

Re: Best Practices: Changing SQL Server port

MatthewRantala1 — Fri, 12 Feb 2021 14:41:05 GMT

Thanks, Craig.

That looks like it would do exactly what I originally was looking to accomplish. Is kind of moot at this point because our IT team decided to move us to an instance on the default port. But good to know that it could have been done.

Thanks!