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].
Basically the question is, which number should I pick?
View 4 Replies View RelatedI've two instances(Default, Named[dynamicsFINANCE]) running on SQL server 2014. However, when I try to connect to named instance say (dynamicsFINANCE) using SQL authentication from local SSMS, I get below error message:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
I assigned a static port number to the named instance [dynamicsFINANCE] 1450. I also setup the firewall rule to allow access to Port 1450.
Plan to setup AG on in both default and named instance of two nodes. Can we do it ?
View 9 Replies View RelatedI have an existing 2012 default SQL Express. It's set up on a VPS managed by a third party. I have an administrator account on this 2012 Windows server. I'm not much of a sysadmin or a DBA but I get around. ;)
I've installed a new NAMED instance on this VPS and can not connect to it with client tools (SSMS). If I remote in, I can connect this way.
What steps might a seasoned DBA expect to make when getting a new named instance ready for the world.
Assign a port? Check the port?
Open the firewall for the port?
Will this new named instance listen on a different port than the previously installed SQL Express instance?
I need to create a named instance on a sql cluster.
How do I perform this? Is it as simple as creating a named instance on a standalone machine? Is there anything I need to consider?
I try to connect from a pc to a SQL Server on another pc. Both pc’s are in a workgroup. I want to connect from a Windows Forms application to a named instance on the other computer. By now I have been able to connect from one pc to SQL Server on the other with tcp:smurfin, 52782.
I want to be able to use servernameinstancename (instead of portnumber) to make a connection in a Windows Forms application.
I’ve checked / tried te following:
•In the properties of the instance, tab Connections, the option Allow Remote Connections is enabled
•In Configuration Manager: TCP is enabled
•The service SQL Server Browser is started
•On the tab IPAddresses, in the section IPAll, there is NO portnumber for TCP Port. And TCP Dynamic Ports has the nummer 52782
•I have created un inbound rule for port 52782 and also for 1434 (SQL Server Browser). And to be on the save side: a rule for 1433 as well.
•Restarted the service
If I run the following code in SQL Server, that same port number (52782) is returned:
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO
SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
[Code] ....
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.
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.
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?!
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 RelatedWe have a bought in application written in Visual Basic 6 using ADO 2.7 to connect to SQL Server. The application has two connections strings and uses internal logic to determine which of the two connection strings connects to a read-write database. It does something as simple as trying an update that makes no changes to a very small table somewhere.
Previously this application connected to a Mirrored Database hosted on two Windows Server Failover Clusters and SQL Server 2005.For the future the App will be connecting this application to a SQL Server 2014 Availability Group.The Availability Group is made up of four servers, in a cluster, two at one site on Subnet A and the other two at a second site on Subnet B. The Availability Group has been given a Listener with a DNS name of AGLISTEST on port 1433 with two Static IP addresses, one for each subnet.
Our Contractor has configured the Application’s connection strings to connect to the two Static IP addresses assigned to the AG. He is of the opinion that although ADO has zero understanding of an Availability Group, at least one of the Static IP Address will translate to the Availability Group primary server because it is a Cluster Resource and so act like a Cluster IP Address on Windows Server Failover Cluster; i.e. sort of mimicking the previous configuration. This does work and he has demonstrated it, however I can’t find any article that proves to my satisfaction this is intended behavior not something that accidentally works. If it is accidental behavior then that would be something that might stop working at an unexpected point in the future.
All the research I have found relates to connecting to the AG Listener name, and all the multi-subnet issues and Active Directory issues that go with connecting using what is a legacy client.So; the question is, is this translation / redirection of static IP Addresses assigned to an Availability Group listener documented and safe?
We have transaction replication configured across multiple SQL instances and could find one of the replication (from publisher to subscriber) net transport sessions is happening via Named pipes rather than TCP. where to troubleshoot this issue and what action to be taken to make it happen via TCP.
View 0 Replies View RelatedI've been reading about the protocols and i've been trying to connect to my SQL SERVER using each one of the protocols, but i think I'm missing something, i dont have any problems using Shared Memory and TCP protocols, but I'm not pretty sure how to connect to my remote SQL SERVER using NAMED PIPES protocols. The books On Line of Microsoft refer that you can connect using this protocol using the next syntax: Connecting to a default instance by specifying a named pipe name:
APPHOSTpipeunitapp
Connecting to a named instance by specifying a named pipe name:
APPHOSTpipeMSSQL$SQLEXPRESSSQLquery
Connecting to default instance by name, forcing a named pipes connection:
np:APPHOST
Connecting to named instance by name, forcing a named pipes connection:
np:APPHOSTSQLEXPRESS
But I can't connect to my remote SQL SERVER, if i try the same with my local SQL SERVER i don't have any problems. I've been reading the articles so many times searching something about Named Pipes is only for local connections, but Microsoft say that it's posible to connect to a remote SQL SERVER, but i don't know how to do that. The firewall is disabled, I changed the APPHOST in the above syntax for the IP address of my SQL SERVER but it didn't work..
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.
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 RelatedI 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.
How to set up a specific aliased instance name. I have installed two identical named instances on a server called LONPOCSQL. The instance names are FINPROD and FINREP, so when connection to them remotely I connect to LONPOCSQLFINPROD or LONPOCSQLFINREP. My question is how can I configure it so that I can connect remotely to the instances in by referencing them as FINPRODFINPROD and FINREPFINREP?
The reason for this is that I need to replace a very old system where the applications connect to the sql servers by referencing them as FINPRODFINPROD and FINREPFINREP respectively. I'm trying to set up a 2-node SQL alwayson AG cluster for HA+DR with one server in the prod DC and the other in DR. I need both instances to live one the same box.
1. As far as I'm aware SQL Aliases only work when the connection is local, remote connections fail
2. I can't create a DNS alias called "FINPRODFINPROD" and point it at a listener name as slashes "" are not allowed in DNS CNAMEs
3. My old boss once built a SQL 2008 R2 failover cluster with two instances on it called "lonsql40lonsql40" but there is no documentation on how he did it and I can't find anything on the web
I have built a SQL 2014 Always on Cluster. I need to create three Availability groups and listeners in the same instance. Do I need 3 separate IP address for each listener or one IP address can be shared for all 3 Listeners ?
View 3 Replies View RelatedSay I have 2014 AlwaysOn setup and working:
SQLNode1 ->Instance01
SQLNode2 ->Instance02
When I setup my listener: ListenerA...Do I need to use the instance name in it?
ListenerAInstance01 or ListenerAInstance02 depending on which SQLNode is the "active" availability group?
Am I better off to use the same instance name for both nodes, since my goal is to have all databases on both instances in the same availability group and sync'd? When SQLNode1 migrates over to SQLNode2 I will need to update the instance name in my connection string on the listener from ListenerAInstance01 to Instance02? When I connect with SSMS do I just use: ListenerAInstance01 (or 02)?
How you are handling the replication of the many instance-level objects/items (logins, linked servers, server roles, database mail, operators, on and on) to the replicas in an AlwaysOn topology.
I'm especially curious about DBAs managing larger SQL Server environments. In my current environment, we have approximately 80 production SQL instances containing about 650 databases that require high availability and disaster recovery.
We use mirroring today and have a solid, home-grown solution for replicating the instance-level items from production to disaster recovery. AlwaysOn changes things a bit since we'll have multiple replicas and of course the database could be active on any one of those at any time. So my concern is about instance-level items being created in one instance but never deployed to the other instances participating in the AG group.
Looking for query that lists all databases, in an instance, that are not accessed before a given date (e.g., not accessed before December 31, 2014)?
View 6 Replies View RelatedWhy do I leave my DB in Restoring State after attaching to the instance? Could not this fire a redo activity on Transaction Log?
Or, How does sql server undo all uncommitted transactions on active vlf during recovery?
How you would calculate the average read/write latency experienced by a SQL Server instance during a specific time window in order to monitor this for multiple instances. From this MSDN blog, I know that you have to take multiple samples and do some calculations to get the correct latency.
[URL] ...
However, the SQLServer:Resource Pool Stats object tracks these numbers per resource pool and we want to get one number for the whole server. Since there can be a different base value for each resource pool, you can't simply sum the numerator values together. Here's some sample data from a server that illustrates the problem.
object_name counter_name instance_name cntr_value cntr_type
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) default 307318919 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base default 25546724 1073939712
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) internal 2045730 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base internal 208270 1073939712
I'm thinking I would need to do some sort of weighted average, but I'm not sure if that will result in the correct value. Here's the formula I am thinking about using currently before doing the calculation over time
((default * default[base]) + (internal * internal[base]))/(default[base] + internal[base])
Then to do the calculation over time, I'd use the changes in the calculated numerator and denominator to get the average.
Does this sound like to correct way to get this value? Is there a good way to verify?
I have access to the default instance of a SQL Server ..... I have access to the QA only .. by reading the registry through the QA I get to knw that there are additional 4 more instances on the Server ..... is is possible to get the port on which they are listening using the QA ....
I knw that we can go to the servers network utility & see it there in the TCP /IP ... but i dont have access to that , neither do i have access to the error logs that tell me , on which port is SQL Server listening to
Can someone help
The box I am trying to connect to is running two instances of SQL Server.
There is a SQL Server 2005 instance which is the default. There is a SQL Server 2000 instance which is named 'SQLSERVER'.
I can connect to the SQL Server 2000 instance no problem:<add key="ConnectionString" value="server=MYPCSQLSERVER;database=mydatabase;user id=****;password=****" />
However, I am having trouble connecting to the Default SQL Server 2005 instance. I have tried:
<add key="ConnectionString" value="server=MYPC;database=mydatabase;user id=****;password=****" />
but it doesn't work. I have tried explicitly setting SQL Server 2005 to use port 1434 (as SQL Server 2000 is running on port 1433), and then used:
<add key="ConnectionString" value="server=MYPC,1434;database=mydatabase;user id=****;password=****" />
but this doesn't work either.
Am I mssing something here? Any help much appreciated
Thanks...
I have a 3 node cluster on which I have installed SSAS as it's own insntance. I have created this as a named instance and can connect to it by serverinstance if I'm on the server itself. However from my desktop I get the error saying instance was not found on server name.
I have defined an alternate port and setup firewall rules and can connect via server:port but not serverinstance. Prior to making this change SSAS was running on default port of 2383 and I could connect just by servername.
I have read many articles for previous versions saying that clustered SSAS will always use 2383 and that you must connect just using servername. However and this is were it gets strange. I have a 2 node UAT cluster with SSAS setup exactly the same way I've described above and I can connect from my desktop as serverinstance.
Should I be able to connect as serverinstances for a named clustered instance in 2012 ?
I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
Hi All,
I am trying to republish a database but when the static snapshot runs it fails with the error msg below.
I am using windows 2003 and sql server 2005 standard edition.
I have a database named MDS that has a publication named "LoanProducts".
I have another database named MDSNZUAT on the same server. This database subscribes to the LoanProducts Publication.
Replication succeeds from MDS->MDSNZUAT.
Once replication is complete I would like to create two publications (LoanProducts and UserData) from MDSNZUAT.
I can successfully republish MDSNZUAT LoanProducts, but when i try to generate the static snapshot for UserData i receive the error below.
2007-03-02 05:36:28.64 [0%] Preparing table '[dbo].[LoanFeeType]' for merge replication
2007-03-02 05:36:28.65 [0%] The replication agent had encountered an exception.
2007-03-02 05:36:28.65 Source: Replication
2007-03-02 05:36:28.65 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-03-02 05:36:28.65 Exception Message: There is already an object named 'MSmerge_default_constraint_for_rowguidcol_of_1282103608' in the database.
2007-03-02 05:36:28.65 Could not create constraint. See previous errors.
2007-03-02 05:36:28.65 Message Code: 2714
If i do not replicate MDSNZUAT to MDS first (i.e. MDSNZUAT is a completely stand alone database with no prior subscriptions),
I can successfully create static snapshots for both publications (LoanProducts and Userdata)
Has anybody had any similar experiences with SQL 2005? This has worked previously with SQL 2000.
Any input is appreciated.
Thanks,
Tim
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 RelatedHow to connect to the named instance of SQL Server using Java?
I had the following code working fine(but with the default instance only)
SET JAVA_HOME=D:Program FilesJavaSoftJRE1.3.1_03
"D:Program FilesJavaSoftJRE1.3.1_03injava" -classpath Launcher.jar;Sprinta2000.jar;log4j.jar;activation. jar;mail.jar Launcher -Ddatabase -Uuser -Ppassword -Sserver:1433 -hlocalhost -p9900 -TTrue -VFalse -QK -QS
But the same code is failing with the named instance:
SET JAVA_HOME=D:Program FilesJavaSoftJRE1.3.1_03
"D:Program FilesJavaSoftJRE1.3.1_03injava" -classpath Launcher.jar;Sprinta2000.jar;log4j.jar;activation. jar;mail.jar Launcher -Ddatabase -Uuser -Ppassword -Sserverinstance:1434 -hlocalhost -p9900 -TTrue -VFalse -QK -QS
I have a java compiled jar file, which accept the connection string as follows: -Uusername -Ppassword -Sservername:port
This string works fine with the default instance of SQL Server, I mean as long as you supply the name of the SQL Server as 'SQLServer', but if I want to run it against the named instance of SQL Server like 'SQLSERVERNamedInstance' then the connection is failing, giving me the unknown host error. Any idea why?
We have installed SQL 2000 SP4 on Windows 2000 Server. We created named instances on the SQL server.
When trying to connect to named instances from a remote sql client which is on windows 2000 and windows 2003, its throwing an error message saying that remote connections are disabled.
But, remote connections are enabled on the SQL server.
After changing the component services settings on windows 2003 where sql client is installed, we are able to connect to named instances.
But, after applying the same settings on another windows 2003 machine, we are unable to connect to the same instances.
We have another machine where we installed SQL 2005 on windows 2003 SP1. When trying to connect to named instances from any remote client, its throwing an error message saying that remote connections are disabled. But, remote connections are enabled in SQL server settings. We tried connecting to the instances by changing the component services settings but still we are facing the same issues.
Can you please help me on above issues?