Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Multiple Instances with Different IP Addresses and Same Port Number in SQL Server Expand / Collapse
Author
Message
Posted Saturday, May 16, 2009 7:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 25, 2010 9:38 PM
Points: 107, Visits: 264
intekhab.alam (5/15/2009)
Thanks for response.

Here are the details abt my env...

I am migrating db sql2k -->sql2k5 where sql2k5 is running on 2 node cluster. and to balance the node separating the db across two instance (active\active mode) running on same cluster nodes..

Old sql2k instance were using 1433 node & to avoid code changes at app side to SQL to listen same port for both instances running on cluster.
Cluster servername - serverN1\serverN2
1st instance name - sqlvirtualA\instance1 - Port 1433,1434
2st instance name - sqlvirtualB\instance2 - Port 1433,1435


intekhab.alam (5/15/2009)

I got the error message for SPM/kerberos authentication as you mentioned in your reply too.
sql error log say ----
"The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies."

This isn't necessarily a problem. Because you are running your services under an AD account, it won't have the required privileges to create SPNs by default. It is better to create your SPNs manually anyway. To enable Kerberos in a cluster, you will need to enable "Register in DNS" and "Enable Kerberos" in your Network Name resource for each cluster group. This will require an cluster group outage to take effect. When it does, a new AD Computer account will be created in your CN=Computers folder. Move this to the same organisational unit as your serverN1 and serverN2 AD Computer accounts.
Then create your SPN as required, eg
setspn -A MSSQLSvc/sqlvirtualA:1433 [service account]
setspn -A MSSQLSvc/sqlvirtualA.[your ad domain]:1433 [service account]

Do that for both instances. You will also need to enable Delegation on the Kerberos tab of the that is running the SQL Database Engine. Setting it to Allow delegation for any Kerberos service is the easiest, but you can lock it down further if you wish.

intekhab.alam (5/15/2009)

based on this error message NTLM authentication scheme is used once kerberos failed. I queried below statement and result is "NTLM"
"select auth_scheme from sys.dm_exec_connections where session_id=111"


Expanding on that a little more nicely is
select c.auth_scheme, s.login_name
from sys.dm_exec_connections as c, sys.dm_exec_sessions as s
where c.session_id = s.session_id


intekhab.alam (5/15/2009)

-- SQL instance is coming up properly & and I can make connection both the instances remotely using similar port using query analyser. also connection from app server\clinet have no issue using below connection string.
sqlvirtualA,1433
sqlvirtualA,1434
sqlvirtualB,1433
sqlvirtualB,1435

- app uses dns alias to connect to SQL instances and have no issues as well.
dns alias - sql-db1.fqdn mapped to sqlvirtualA
dns alias - sql-db2.fqdn mapped to sqlvirtualB


My biggest concern\question..
how this will behave going forward?
what idoes MS say for such cinfiguration? and des it a sound proof or suggested seting which can be implemnedted on critical Prod env?
Are you\anone aware about such setting already implement on any env and how its working?

again.. you reponse will be much appreciated. Awaiting your response.
Thanks...


If you are already using dns aliases to support your application (which is a good thing), then why do you want to try and make life a whole heap more complicated by trying to do what you're describing here? It is much simpler, and supported, to simply set each instance to a unique port. That they are in separate cluster groups gives them separate IP addresses. From memory, I don't think the SQL Network Configuration Utility provides an IP address option for your virtual (cluster group) IPs.



--
Andrew Hatfield
Post #718581
Posted Sunday, May 17, 2009 8:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,285, Visits: 4,225
Andrew Hatfield has described very well what needs to be done when running multiple SQL Server instances on a cluster.
.. you are already using dns aliases to support your application (which is a good thing)

Excellent - this is the way to go.
... why do you want to try and make life a whole heap more complicated by trying to do what you're describing here? It is much simpler, and supported, to simply set each instance to a unique port. That they are in separate cluster groups gives them separate IP addresses.

Agree with Andrew.
From memory, I don't think the SQL Network Configuration Utility provides an IP address option for your virtual (cluster group) IPs.

Andrew is correct that IP cannot be specified but the port can be specified.

You need to take only a few more steps to have the SQL Server instances have fixed ports rather than dynamically assigned ports and to map the dns aliases to the actual SQL Server instance names. This will insure that the clients can connect using only the DNS Alias and do not need to specify IP or port.

First, decided what ports are going to be used for SQL Server. Registered Ports are those from 1024 through 49151 and Dynamic and/or Private Ports are those from 49152 through 65535. These ports number ranges should not be used for named SQL Server instances as a future conflict is possible. Reference http://www.iana.org/assignments/port-numbers

Second, so that no other program requesting a dynamic port will be assigned your desired SQL server port, reserve the desired port numbers. "How to reserve a range of ephemeral ports on a computer that is running Windows Server 2003 or Windows 2000 Server" http://support.microsoft.com/kb/812873/

Third, "Configuring SQL Server Named instances to use a fixed port"
http://technet.microsoft.com/en-us/library/ms345327(SQL.90).aspx. This will require a SQL Server restart.

Fourth, map the DNS aliases to the SQL Server using the SQL Server Configuration Manager.

Finally test by connecting using the DNS Alias without a port number from a different server.

Good Luck







SQL = Scarcely Qualifies as a Language
Post #718669
Posted Tuesday, July 14, 2009 11:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 21, 2010 7:44 PM
Points: 2, Visits: 59
how did you use the instance name ,port number in the DNS alias.

i am trying to move instances from one server to another,rename them and change the default port. so i wanted to use DNS aliases to redirect clients to the new server,instance and port without having to reconfigure my applications or clients.
Post #753170
Posted Wednesday, February 3, 2010 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:46 PM
Points: 53, Visits: 229
Andrew, not sure I understand why SPNs and kerboros will not work? Can you clarify?
Post #858765
Posted Saturday, February 6, 2010 6:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 25, 2010 9:38 PM
Points: 107, Visits: 264
This thread is a little old, so please bear with me.


SPNs and Kerberos work, but for a 2003 cluster, they are not created automatically. When using SQL in a cluster, you must enable "Register in DNS" and "Use Kerberos" and then create your SPNs.


Hope this answers your question



--
Andrew Hatfield
Post #861231
Posted Monday, February 8, 2010 7:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:46 PM
Points: 53, Visits: 229
Are they created automatically for 2008 cluster?
Post #861643
Posted Wednesday, May 12, 2010 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 2, 2010 9:24 AM
Points: 2, Visits: 12
I am having the same issue. I've tried/set all the options as oulined, but still am not able to connect to the server directly (eg:dbservername), only by using servername\dbserversname, or by setting up a local alias (dbservername=servername\dbservername), or a third way (using dbartisan for example, servername:port number).

Is there anything else that could be an issue?
Post #920599
Posted Tuesday, July 9, 2013 2:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:50 PM
Points: 2,691, Visits: 3,374
I know this is an old discussion, but it came up when I was googling the interwebs for info on this.

1. You need to understand DNS.
2. You need to configure your server's network config correctly; i.e. do not register automatically with DNS Server
3. Delete all A records from DNS and re-enter as static
4. Disable listen all in your nework protocols
5. Set ip's accordingly
6. Set dynamic ports to nothing; i.e. delete anything there. Otherwise it will use it. 0 is not blank, it is "find something"

Should all work.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1471852
Posted Thursday, August 1, 2013 8:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 14, 2014 8:39 AM
Points: 33, Visits: 299
We have a server with 3 instances of SQL Server running, each running on it's own IP and port 1433.

I've created a blog post outlining how it was done.

http://kevine323.blogspot.com/2013/08/setting-up-aliases-on-sql-server-with.html
Post #1479979
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse