Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I currently have an SQL Server hosting two production instances which have numerous web apps accessing them. As far as I know, none of these apps are using a specific port in their connection strings, nor am I aware if this even matters.

My questions is, what are the implications of assigning theses instances to static ports? Will this break my apps? Are there other adjustments I need to make or consider before doing this?

Reason for asking? My infrastructure team has requested that I assign static ports for the purpose of assigning SPNs for Constrained Delegation rights. Thanks. ;)

share|improve this question
I presume that your IF team is registering SPNs manually using SETSPN. It's my understanding, though I've never verified it in a multi-instance environment, that if you grant the Active Directory service login the "register an SPN" permission, all instances (default or non-default) will register the proper SPN upon startup. I presume that this would get around any hard-coded port problems. I worked at a place (no multi-instances) where we did a lot of fiddly work with SETSPN and all that effort went away when we started letting the DB server register SPNs by themselves. – darin strait Nov 12 '12 at 14:22
@darin Unfortunately, our infrastructure team doesn't want to allow SPNs to be set dynamically, which is why they want me to assign our SQL Server instances to static ports. – Chiramisu Nov 12 '12 at 18:09

2 Answers

up vote 4 down vote accepted

The SQL Server Browser service will help the client connect to each instance even with static port numbers. As long as that service is running on your SQL server, then your web applications should not have any issue connecting.

You can read more about how the Browser service works here.

share|improve this answer
Thank you. This was the most helpful answer as I was not aware that there was a service that facilitated the connections to the various instances; though it makes perfect sense. :) – Chiramisu Nov 12 '12 at 18:11
up vote 0 down vote

Well, if you never changed the port configuration on the server when the instances were installed, you are not likely to find any connection strings using a specific port.

Yes, it does matter how your applications' connection strings are set up. Changing the port will likely break your applications. Since the static port has to be unique, you would only be able to set one instance to the default port used by SQL Server, 1433. Applications pointed to that instance may keep working for that instance. The second instance, however, would require your applications to update their connection strings and specify the port you configured it to.

share|improve this answer
Indeed? I find this odd since by default additional instances use dynamic ports and they are not specified by connection strings. It would seem odd to me if it indeed worked this way. If this is really the case, then how can I expect to make the transition from dynamic to static ports without breaking my apps during the conversion? Thanks. – Chiramisu Nov 10 '12 at 9:50
I would verify if you connection strings actually specify a port for their applications. Then as Patrick suggested have the Browser service running and let it manage the incoming connection request for your applications. – Shawn Melton Nov 11 '12 at 4:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.