CODEWITHSUNDEEP
sqlsql-servernetwork-programming
Agent_Spock
Agent_Spock

Reputation: 1197

How does SQL communicate with database over network without sharing any kind of physical files?

Sorry in advance if the question sounds lame but i couldn't find the answer on the internet.

How does SQL communicate with database over network without sharing any kind of physical files?

Like for example if we are using access and we want to access the data over network then we have to share the DB file so it can be access in the network BUT in case of SQL Server i have never seen any SQL DB files being shared on the network.

What kind of method they use to do so?

Upvotes: 1

Views: 1858

Answers (2)

Mo Chavoshi
Mo Chavoshi

Reputation: 565

In order to understand, let's first talk briefly about the two main protocols that are in common use today:

  • TCP - Transmission Control Protocol
  • UDP - User Datagram Protocol

Both are part of the TCP/IP suite of protocols.

TCP is the main protocol by which clients communicate with SQL Server. Actually, it is more correct to say that clients and SQL Server use Tabular Data Stream (TDS), but TDS actually sits on top of TCP and when we're talking about Windows and firewalls and other networking devices, that's the protocol that rules and controls are built around. So we'll just speak in terms of TCP.

UDP, unlike TCP, is not connection oriented. A "client" can send a UDP communications to anyone it wants. There's nothing in place to negotiate a communications connection, there's nothing in the protocol itself to coordinate order of communications or anything like that. If that's needed, it's got to be handled by the application or by a protocol built on top of UDP being used by the application.

Network Communications - Talking to SQL Server

When an instance of SQL Server is set up, what TCP port it listens on depends. A default instance will be set up to listen on port 1433. A named instance will be set to a random port chosen during installation. In addition, a named instance will be configured to allow it to change that port dynamically. What this means is that when a named instance starts up, if it finds something already using the port it normally uses, it'll pick a new port. If you have a named instance, and you have connections coming across a firewall, you're going to want to use SQL Server Configuration Manager to set a static port. This will allow the networking and security folks to configure their devices for maximum protection. While you can change the network port for a default instance of SQL Server, most people don't.

Network Communications - Finding a SQL Server

When just the name is specified for a client to connect to SQL Server, for instance, MySQLServer, this is an attempt to connect to the default instance. In this case the client will automatically attempt to communicate to port 1433 on MySQLServer. If you've switched the port for the default instance, you'll need to tell the client the proper port, usually by specifying the following syntax in the connection string: ,. For instance, if you moved SQL Server to listen on 14330, you'd use MySQLServer,14330 instead of just MySQLServer.

Network Communications - Named Pipes

Named pipes is an older network library communications mechanism and it's generally not used any longer. It shouldn't be used across a firewall. However, if for some reason you need to connect to SQL Server with it, this protocol also sits on top of TCP. Named Pipes is actually used by the operating system and it has its own mechanism within the protocol to determine where to route communications. As far as network communications is concerned, it listens on TCP port 445. This is true whether we're talking about a default or named instance of SQL Server.

You can find the complete thread here: https://www.mssqltips.com/sqlservertip/2182/network-communications-mechanisms-for-sql-server/

Upvotes: 3

Ben Thul
Ben Thul

Reputation: 32737

In the same way that when you submit a search to your favorite search engine (Google, Bing, etc), they don't ship you their entire data set just so you can find what you're looking for, so it is with a database server. The data is stored on the server (likely on the network) and everyone accesses that one place.

Upvotes: 0

Related Questions