Instance TCP Port Misconfiguration On Cluster Leads To Unrecorverable Situation....

Jan 10, 2007

Hi all. I have ran into a annoying situatin on sql cluster. Config is:
w2k3 EE x64 r2 - two nodes
sql 2005 x64 standart v2153
2instances, active-active cluster.
While changing tcp port configuration of one instance (using configuration manager) I have acidentaly created that setting:
- All IP dynamic port and All IP port was set to same number while separate IPs have enabled dynamic ports and does not have static port set.
This situation leads to unability of starting the instance anymore. There is no need wide explanation, its quite clear - while starting instace, it was configured with static port and with dynamic port with the same number, so port conflict error takes place. Problem was how to escape from this situation. On non clustered sql is quite easy, because these settings are stored in registry and also can be changed thorough Configuration manager, easy solution. Nor on the cluster.. Modifying the registry even on both nodes was unefective, every try to start instance fails with port conflict error and bad config was saved to registry again. I wasnt able to find where this config resides and change it to corect values. I have revealed only that it is not stored in instance master DB (restoring it from backup). Looks like its held somehow by cluster service itself, but I didnt find where it should be and how to edit it. So the last possible option take place (time was against me) - reinstalation of whole clustered instance. Nice example of how one click can create you a 2 days of work... Iam still interested if there is a smooth and easy way out of this situatin:-) So if anyone knows..... If there is not, I realy warn everybody.

DAvid

View 1 Replies


ADVERTISEMENT

Transact SQL :: BPA On A Cluster Using Default Instance And Static Port

Aug 25, 2015

Having problems running the BPA for 2012. We setup using a clustered environment, SQL Server installed using default instance, and a static port not 1433. When I try to run the BPA using the instance name it says it doesn't exist, and won't let me add a port number. However, if I use MSSQLSERVER as the instance name it runs with no port specified and returns results. I assume that it doesn't require a port because the default instance would pull from the Browser Service and the using MSSQLSERVER is the same as running the default on a standalone server. Are those assumptions correct?

View 2 Replies View Related

SQL 2012 :: Advanced Cluster Preparation For New Instance On Existing Cluster

Apr 10, 2014

I ran the Advanced cluster preparation for a new sql instance on an existing cluster.

Slq Server 2012.

After is completed, it was successful, I realized I specified the wrong Instance Root directory.

Is it possible to remove what the preparation installed? Or is it possible to change the root directory?

View 3 Replies View Related

How Can I Get The Port Of A Named Instance Of SQL Server

Sep 14, 2004

Thanks for Microsoft's Windows XP Service Pack 2 and Windows Server 2003 that make the security much stronger. However, besides the invisible benefit so far, I have become the victim of this security policy.

I have several named instances of SQL 7/2000 installed in my machine. They are not visible out of the Microsoft new fire wall. I need to access the instances from outside the fire wall, but I don't know which port I should open for the instances.

From SQL online, the default instance of SQL server is connected through TCP/IP by default port 1433. I could successful open that port and made the default instance visible to outside. However, the port 1433 doesn't work for the named instances. SQL online said, the port for named instance is dynamically (by default) chosen the first time the instance is started. So, actually, I have no way to know the port.

Is there anyway that I can check the database or somewhere to get the port that is used by the named instance?

Thanks for any input and recommendation.

View 4 Replies View Related

Cannot Connect To SQL Instance Without Port Information

Jul 5, 2007

Hi all,I'm having some trouble with connecting to SQL server instance latelywithout adding a port number. I used to be able to always connect toinstance without having to input SQLSERVER,portnumber. However nowthis is not the case. I doing this through an ADO connection object,and creating the connection string. Does anyone know if there was arecent Microsoft update or patch that would've cause this to stopallowing me to connect to a SQL instance without having to put in theport information? Thanks.

View 2 Replies View Related

SQL 2012 :: Opening 1433 Port For Server Cluster

Jun 21, 2015

SQL Server 2012 two node single instance cluster.

For DB access from application severs, do we need to open port (1433) for NODE IPs too ?

Or is it enough if we open the port 1433 only for VIRTUAL IP ?

View 4 Replies View Related

SQL Server 2008 :: Named Instance And Specified Port Not Connecting?

Feb 14, 2013

I have opened up a port on a remote SQL instance and can see that the port is LISTENING when using the PortQry tool. I have also set the TCP port in the TCP/IP properties in the IPAll section for that instance, yet I am unable to connect and get an error of

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=14998; (.Net SqlClient Data Provider)

I have done this on other instances, although they were default instances, and it has always worked fine.

View 9 Replies View Related

Setup And Upgrade :: Changing TCP Port Of A Default Instance

Jul 31, 2014

I installed a default SQL Server 2012 instance on Windows Server 2012.I enabled the SQL Browser and changed the TCP port of the instance to 1435. I changed port 1433 to 1435 I was able to connect to the instance using SSMS on another server. Now I have to enter Servername,1435 to make a connection.Why does the SQL Browser not resolve the servername to the right IP address and port 1435? Why am I not able to make a connection when I enter the servername or <servername>MSSQLSERVER? Is a SQL Browser only resolving named instances?This works fine for a named instance. Is there something special about default instances or the SQL Browser?

View 15 Replies View Related

DB Engine :: How To Get Port Number For A Named Server Instance

Jun 21, 2015

I have TWO named SQL Server instances (on the same machine) and I need to know the port of each of them, how can I do that? Is it write to check the following:

Which one to take: "TCP Dynamic Ports" or "TCP Port"? and what is the difference between them anyways? 

Can the two instances (or more) on the same machine use the same port?!

View 12 Replies View Related

SQL 2012 :: Named Instance With Specific IP / Port And Connect Using Browser

Jul 31, 2014

I'm trying to configure a named instances of SQL Server 2012 SP2 to have specific IP addresses and listen to that IP on port 1433. Alongside this, I have the default instance running on a different IP and listening to that on port 1433.I've managed to get them configured so that they are reachable using the IP, and looking at the log for each they are listening correctly on that IP on port 1433; in addition, the default instance connects fine using the SERVERNAME.However, when I try and connect to the named instance using the SERVERNAME/INSTANCENAME syntax, I receive the following:-

"Cannot connect to SERVERINSTANCE.
Instance failure. (System.Data)"

Configuration for the TCP/IP protocol for that instance is as follows:-

Protocol Tab

Enabled= Yes
Keep Alive = 30000
Listen All = No

IP addresses Tab

IP1
Active = Yes
Enabled = Yes
IP Address = 10.1.1.1
TCP Dynamic Ports = 0
TCP Port = 1433

[code]...

The SQL Browser service is running and prior to switching off Listen To All, I could connect to the instance remotely using the instance name.

View 5 Replies View Related

SQL Server Admin 2014 :: Static Port Of Named Instance

Nov 3, 2015

I changed the Port of my named instance to use static port but still error log is giving two values ,why??

spid15sServer is listening on [ 'any' <ipv6> 50152].
spid15sServer is listening on [ 'any' <ipv4> 50152].
ServerServer is listening on [ ::1 <ipv6> 57518].
ServerServer is listening on [ 127.0.0.1 <ipv4> 57518].

View 5 Replies View Related

Setup And Upgrade :: Determine What Port Does Named Instance Are Listening?

Sep 22, 2015

I have a named sql instance on SQL 2012. The issue is that the clients is having problem connecting to it when the firewall is on, this is not consistent. The connection is fine when I disabled the firewall. When I turned the Windows firewall back on, it will work for awhile and it fails. I checked the SQL configuration manager and saw that a dynamic port is assigned to the named instance. I did put that in the firewall rule and unblock it. I also put TCP 1433 and 1434 as well as the browser service port, TCPUDP 2382 and it still didn't work.What is the best way to determine what port does the named instance are listening?

View 11 Replies View Related

Can't Change TCP Port Using Configuration Manager For X64 SQL 2005 SP1 Virtual Instance

Feb 27, 2007

Hi, I hope someone can help me!   I changed the TCP port that the default instance of SQL 2005 x64 SP1 is listening on and now SQL server won't start as the port is being used - no problem I say to myself, I'll just change it again, however I can't!  

My problem is that I can not change the TCP Port back again using Configuration Manager - no matter what values I put in, after a restart of the instance it comes up with this in the IP Addresses tab for TCP/IP Properties and SQL Server won't start:

IP1
Active   Yes
Enabled   No
IP Address  aaa.bbb.ccc.111
TCP Dynamic Ports
TCP Port  0

IP2
Active   Yes
Enabled   No
IP Address  aaa.bbb.ccc.222
TCP Dynamic Ports
TCP Port  0

IP3
Active   Yes
Enabled   No
IP Address  aaa.bbb.ccc.333
TCP Dynamic Ports
TCP Port  0

IP4
Active   Yes
Enabled   No
IP Address  10.1.1.1
TCP Dynamic Ports
TCP Port  0

IP5
Active   Yes
Enabled   No
IP Address  127.0.0.1
TCP Dynamic Ports
TCP Port  0

IPALL
TCP Dynamic Ports 2020
TCP Port  2020

Interestingly, the ip address that the server is listening on aaa.bbb.ccc.444 is not reported in the list of IP addresses in the configuration manager for this instance, yet it is when I view the available IP addresses for the other two instances - is this significant?

I have tried failing to the other node, changing it there, changing it at the console and also while logged onto the the virtual machine remote desktop, rebooting both nodes, etc, etc.   There are two other instances on this cluster that are still working fine but I hvae not attempted to change the TCP ports they listen on.

I used the same method to change this default instance as I did for a named instance on a similarly configured cluster and it worked fine.   How to fix this?

Regards, Mike

2007-02-27 12:03:17.69 Server      Server is listening on [ aaa.bbb.ccc.444<ipv4> 2020].
2007-02-27 12:03:17.71 spid9s      Starting up database 'tempdb'.
2007-02-27 12:03:17.72 Server      Error: 26023, Severity: 16, State: 1.
2007-02-27 12:03:17.72 Server      Server TCP provider failed to listen on [ aaa.bbb.ccc.444<ipv4> 2020]. Tcp port is already in use.
2007-02-27 12:03:17.75 Server      Error: 17182, Severity: 16, State: 1.
2007-02-27 12:03:17.75 Server      TDSSNIClient initialization failed with error 0x2740, status code 0xa.
2007-02-27 12:03:17.75 Server      Error: 17182, Severity: 16, State: 1.
2007-02-27 12:03:17.75 Server      TDSSNIClient initialization failed with error 0x2740, status code 0x1.
2007-02-27 12:03:17.75 Server      Error: 17826, Severity: 18, State: 3.
2007-02-27 12:03:17.75 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-02-27 12:03:17.77 Server      Error: 17120, Severity: 16, State: 1.
2007-02-27 12:03:17.77 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

View 11 Replies View Related

DB Engine :: Connection To Server Instance From SSMS Fails Without Specifying Port

Oct 27, 2015

connecting to a SQL Server Instance. I have a SQL server DB on a server having an Instance not running on the default port. Let's say ServerAInstanceA has the services exposed on Port 12345. When I'm trying to connect to ServerAInstanceA without specifying the port number, while I can connect from one client machine (ClientA), I am unable to connect from a different client machine (ClientB).On a side note, when I provide the port number, I can connect both from clients A and B. My question is, when I do not provide the port number, why is one client machine able to connect while the other isn't?

View 4 Replies View Related

Named Instance Connectivity Error - Only Works When Port Number Is Specified??

Sep 27, 2007

I have a new named instance (SQL Server 2005 x64 SP2 (Build 3159) Developer edition) setup with dynamic port selection. A default instance was also running on the server prior to the named instance setup.

SQL Server Browser service is running. Remote connections are configured on the named instance. But still remote clients can only connect when the port number is specified after the instance name (servernameinstancename,9999). Without the port number, "error 26 - Error locating Server/instance specified" is thrown.

Doesn't the SQL Server Browser service eliminate the need for clients to specify the port number? What am I missing?

Thanks.

View 1 Replies View Related

SQL Server Admin 2014 :: Picking Static Port Number For Named Instance

Apr 3, 2015

Basically the question is, which number should I pick?

View 4 Replies View Related

SPN In Cluster Instance

Jun 13, 2015

I have installed a SQL Server cluster recently.Noticed below errors logged in the error log. I understand that SPN is not getting registered, hence this error.What permissions SQL Server service account? Service account is admin in local nodes anyway.

"SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service.

This is an informational message. No user action is required.SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon atte

2015-06-12 18:11:06.970 Logon Error: 18452, Severity: 14, State: 1.
2015-06-12 18:11:06.970 Logon Login failed. The login is from an untrusted domain and cannot be used with Windows authentication "

View 3 Replies View Related

Cluster: Instance Naming

Sep 3, 2007

Hi all

SQL Server 2005 cluster. Active-Active. We want to create two instances. The two nodes are named:

mike-940-01
mike-940-02

I read somewhere that there can only be ONE default instance in a 2005 cluster. So I interpret that as meaning that I can only have one instance named after a virtual server (example: mike-940-03) and the other instance has to be a slashed instance (example: mike-940-03/instance1). But what if I create another virtual server (example: mike-940-04). Can I install a default instance in that server? Can someone help me clarify this?

Also, if I'm using all slashed instance names, then what does it choose as the base name before the slash? In other words, what decides which virtual server name is used? Is it the management node? Is it one of the nodes?

My objective is to keep consistency in the naming on the cluster. If I have to use slashed instances, then I want all sql server instances to have a slash.

Sorry about the confusing explanation, but that's probably a symptom of MY confusion.

Thanks!

View 4 Replies View Related

Multiple Instance Cluster

Mar 6, 2008

Hi There

If i have a 2 node cluster running a single instance (default) and i want to add another instance to the cluster , can i install the second instance as a defualt instance ?

Since the cluster instances are referred to their virtual names/ip's and if i choose to intall the binaries of the second instance to a different location is this possible?

Since in theory you never have 2 default instances running on a single server but each within a virtual server?

Normally i install a named instance but do i have to ?

Thanx

View 1 Replies View Related

SQL 2012 :: Adding Instance To Cluster?

Oct 8, 2014

We have an active/active two-node QA cluster with four instances of sql. I need to add a fifth. In the past I always moved existing instances to the other node while installing a new instance.

Other than impacts to users from install reboots, is that a hard requirement?

QA is reluctant to have me put the four existing instances on one node for most of a day, fearing big performance slowdowns. Each node has 250GB of memory and each instance is allowed 50GB. Netapp disk backend.

View 3 Replies View Related

New Instance Of A Active/Passive Cluster

Jun 20, 2008

Hi all,
Newbi to sql clustering.

I have a sql server 2005 standard edition with Active/Passive Cluster in production.

I have a new requirement where i need to create a new instance on this active/passive cluster that can be used by our sharepoint servers. Can somebody please let me know what are the steps that i need to follow like

1.What i need to ask my network admin?
2.How i need to proceed installing?
I am planing to do this over the weekend where i have the liberty of some down time.
Any help greatly appreciated
Thanks

View 1 Replies View Related

Install Additional Instance In A Cluster

Nov 27, 2006

I am looking for a best practice or any suugestion on the best way to install an additional Sql 2005 instance in a cluster environment. We have a production environment that is active/active/passive and need to install an additional instance with a little impact to the current production instances as possible. Ant suggestions or articles I can review?

View 1 Replies View Related

Move Instance From Cluster To Stand Alone?

Aug 14, 2015

I have a SQL 2008 R2 2 node cluster.  For DR testing, I need to restore an instance on a new server (completely isolated).  The existing instance runs on what I would call an alias.  The actual cluster host name is SQLPROD (cluster nodes are SQL01 and SQL02).  The instance runs on server SQLPROD-APPLE.  The instance is called APPLE.  In production, I use SQL Management Studio and I connect to SQLPROD-APPLEAPPLE.

I've setup a new VM to run SQL.  Not a cluster.  Host name is SQLREC.  The new Vm has all the IP addresses that were on the cluster and the DNS for all the aliases are correct.  I've installed instance APPLE.  I created an alias called SQLPROD-APPLE.  When I try to open SQL Management Studio and connect, I can't connect to SQLPROD-APPLEAPPLE.  I can connect to localhostAPPLE, SQLRECAPPLE, 192.168.168.50APPLE.

When I attempt to connect to the alias I get error 18452.  Pinging the alias resolves correctly.  I haven't gotten to the database restore yet, so that's not a concern.  I'm just trying to validate the connection first with the correct server and instance names.

I'm using Windows Authentication.

View 9 Replies View Related

SQL 2005 Multi-Instance Cluster Question

Mar 19, 2008



I understand most of the concepts for the Active/active setup. I am setting up a 2 node cluster. My question is can I use the default instance name (one default instance name per group) when setting up each instance or do I have to use named instances . I am assuming that since each group is a virtual machine that I can get away with using the default instance, but I cant find any documentation to confirm my frequently wrong perceptions.

I want to set it up like this

Instance1 Group

Group resources
Default SQL Instance Name Installation
Preferred Owner Node1

Instance2 Group

Group resources
Default SQL Instance Name Installation
Preferred Owner Node2

Thanks

View 4 Replies View Related

Cannot Remotely Connect To SQL2005 Instance On A Cluster

Jan 18, 2008

Hi,

I am trying to remotely connect to a SQL 2005 Instance on a cluster. I see the following error:

No retry on exception System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have enabled TCP/IP , Named Pipes on the node. I have turned off IPSec on the clients as well. I am using the following connection string : "Integrated Security=SSPI;server=servernameinstancename;Pooling=false;database=TrialDB"

Is there something I am missing?

Any pointers will be appreiciated.

thanks.

View 2 Replies View Related

Putting Current SQL 2000 Instance/dabase To Cluster

Jul 30, 2002

I have a system running a sql 2000 instance w/ multiple databases located in a SAN. I would like to cluster my SQL 2000 environment on the same system. Can I do this without reinstalling another database instance? Can I put my current default instance and put it to my virtual server instance? Help is really appreciated. Any tips and links can be helpful to me.

View 3 Replies View Related

Cannot Connect By Naming Instance In Mssql 2005 Cluster

May 9, 2007

Hello,



I install Cluster naming instance of MSSQL 2005 Enterprise edition with SP2 and latest patch.

When I try connecting this instance from outside of the server by "server nameinstance name" I got connection error.

But when I connecting by "server name,port number" it's connecting with no problem.

Can someone help me resolve connection outside of the server with "server nameinstance name"?



Thanks

View 7 Replies View Related

Install Analysis Services On An Instance On A Cluster With SQL Server

Dec 3, 2007



Hey,

I have a 3 node cluster running 6 instances of SQL Server, I want to install Analysis Services along side of one of the SQL 2005 instances. Thus one of the cluster instances would host SQL Server DB, Analysis Services and SQL Agent. Is this possible or does Analysis Services need it's own Cluster Instance.

Thanks!

View 3 Replies View Related

DB Engine :: Failover Cluster Instance With Mount Point

Jun 25, 2014

I've a fresh installation of SQL Server 2014 Enterprise on Windows Server 2012R2. I've setup de Windows Server Failover Cluster and the validation test has been successfully passed. I use mountpoint so I've disk M: that is the host drive for mounted volume m:Isql2014A that is the base folder for mounted volume

On that folder I've 5 mountpoint:

1) m:isql2014a empdbdata
2) m:isql2014a empdblog
3) m:isql2014auserdata
4) m:isql2014auserlog
5) m:isql2014auserlog

into these 5 mountpoint I've placed the SQL Server files.

After the installation of the first FCI I've re-run the cluster validation wizard and again it has passed successfully. When I try to failover the instance from one node to the other I have 2 problem:

1) every time I do failover, some disks fail to come online for a while and then succeed :

Cluster resource 'CLD - IstA - TempDB Data' of type 'Physical Disk' in clustered role 'SQL Server (ISQL2014A)' failed. The error code was '0xaa' ('The requested resource is in use.').

2) sometimes the SQL Server service fails to start, in the error log I see that the master database is not accessible. The master database in into the m:isql2014abinbackup.

View 7 Replies View Related

How To Uninstall A Specific Instance Of SQL Server 2000 From A Cluster

May 8, 2007

I have a SQL 2000 clustered environment with 4 SQL instances and i need to remove a specific instance from the cluster the resources(physical drive,virtual IP,instance name) being used by it

View 5 Replies View Related

Can Multiple Instance In A Cluster Share Disk Resources?

Dec 8, 2006

Is there a way for multiple SQL 2005 instances in a cluster to share drives for user databases.

Once we install our instances in the cluster using seperate disk resources. Can we then have multiple instances share the same physical disk for user databases only. This is for a test environment.



Thanks

View 1 Replies View Related

DB Engine :: Server 2012 Failover Cluster New Instance

Aug 6, 2015

I am new to SQL Server 2012 clustering.I added a new instance to one of the two nodes.when I try to move it to the other node it fails.Do I need to install it on both?If so, what options do you install on the second node?

View 7 Replies View Related

SQL Server 2012 :: Remove Named Instance From Failover Cluster

Oct 13, 2014

In QA we have a two-node cluster with four instances of sql. In trying to add a fifth, I was given an IP address already in use so the install hung.

I removed it from the cluster but it is still there in the registry etc on the node I was working on.

I read about using the maintenance tab of the sql server install to "remove a node" but the terminology is confusing. To me a node is a physical server and an instance is an instance of sql server -- not the same at all but they are often referred to as the same thing.

I definitely don't want to remove one of the servers from the cluster.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved