Ms sql named pipes что это

Конфигурирование Microsoft SQL Server в сети

Как мы уже говорили, SQL Server поддерживает множество сетевых библиотек: именованные каналы (named pipes), TCP/IP, multiprotocol , NWLink IPX/ SPX , AppleTalk , Banyan VINES , VIA (Giganet) и VIA (ServerNet II). Каждая сетевая библиотека соответствует своему сетевому протоколу или набору протоколов. В этом разделе мы дадим краткий обзор каждой из сетевых библиотек.

Сетевой протокол, с помощью которого вы запускаете SQL Server, будет определяться, скорее всего, исходя из стандартов, принятых в вашей фирме, или в зависимости от того, что применяется на остальных компьютерах. Все команды и функции SQL Server могут передаваться через все сетевые протоколы, однако некоторые протоколы работают быстрее других. Кроме того, некоторые протоколы поддерживают маршрутизацию и службы имен, а некоторые – не поддерживают.

Named Pipes – именованные каналы

Протокол named pipes (именованные каналы) был разработан фирмой Microsoft несколько лет тому назад. В named pipes поддерживаются два режима: локальный и удаленный (дистанционный). Протокол local named pipes применяется в случаях, когда и клиент, и сервер находятся на одном и том же компьютере, а remote named pipes применяется, когда клиент и сервер находятся на разных компьютерах. Если соединение установлено через named pipes, сетевые утилиты SQL Server распознают, как оно осуществлено – с помощью локальных именованных каналов (local named pipes) или же с помощью удаленных (дистанционных) именованных каналов (remote named pipes).

Именованные каналы – это клиентский протокол, применяемый по умолчанию, и он является протоколом, применяемым по умолчанию в операционных системах Windows NT 4 Server и Windows 2000. В операционных системах Microsoft Windows 95 и Microsoft Windows 98 именованные каналы применяться не могут. В этих системах на стороне сервера применяются протоколы TCP/IP, multiprotocol и shared memory . Хотя протокол «именованные каналы» работает эффективно, он обычно не используется в больших сетях, потому что он не поддерживает маршрутизацию и шлюзы. Также не рекомендуется его применение на медленных сетях, т.к. по сравнению с другими протоколами, например, TCP/IP, для него требуется передача значительно большего объема информации между сервером и клиентом.

TCP/IP

Протокол TCP/IP является одним из наиболее популярных сетевых протоколов, из-за очень большого количества компьютеров, на которых он работает, из-за того, что его применение принято в качестве стандарта , и из-за его высокой скорости. Он также является сетевым протоколом, применяемым в Интернете. Сетевая библиотека TCP/IP является одной из наиболее высокопроизводительных сетевых библиотек SQL Server. Благодаря высокой скорости и богатым возможностям TCP/IP станет хорошим выбором.

Multiprotocol

Сетевая библиотека multiprotocol – это новинка, появившаяся в SQL Server 7 и сохранившаяся в SQL Server 2000. Эта сетевая библиотека на самом деле является совокупностью нескольких сетевых библиотек. Поэтому она не так эффективна, как одиночные сетевые библиотеки, но зато обеспечивает большую гибкость. Сетевая библиотека multiprotocol поддерживает протоколы TCP/IP, NWLink IPX/ SPX и именованные каналы (named pipes). При использовании сетевой библиотеки multiprotocol обычно используется первый из протоколов, имеющихся на клиенте и на сервере. Так как клиент может соединяться с разными серверами при помощи разных протоколов, то multiprotocol является идеальным выбором.

NWLink IPX/SPX

Протокол NWLink IPX/SPX идеально подходит для интеграции (встраивания) систем SQL Server 2000 в сети Novell NetWare, потому что интеграция в нем реализована гладко, «без швов». Протокол IPX/ SPX известен уже долгое время и обладает высокой производительностью и надежностью.

AppleTalk

AppleTalk – это сетевой протокол, разработанный фирмой Apple Computer и применяемый в компьютерах Apple. Windows NT и Windows 2000 поддерживают AppleTalk , благодаря чему серверы и клиенты Windows NT и Windows 2000 могут гладко, «без швов», интегрироваться в окружение AppleTalk .

Banyan VINES

Сетевая библиотека Banyan VINES служит для поддержки компьютеров, работающих в сети VINES . С помощью этой сетевой библиотеки можно интегрировать клиенты и серверы Windows в окружение VINES .

VIA (Virtual Interface Architecture)

Этот протокол поставляется в двух вариантах – Giganet и ServerNet II. Он хорошо подходит для кластеризованных серверов.

Выбор сетевой библиотеки

Выбор сетевой библиотеки должен осуществляться исходя из того, какие протоколы применяются в вашей сети. Проблемы с соединениями обычно возникают, когда сетевые библиотеки на сервере не синхронизованы с сетевыми библиотекам на клиенте. Если при соединении с сервером возникают трудности, то проверьте, как заданы сетевые библиотеки на обеих сторонах соединения. Также попробуйте соединиться с сервером при помощи другой программы, например, при помощи PING или Проводника Windows (Microsoft Windows Explorer), чтобы понять, относится ли проблема к работе SQL Server или же не работает сама сеть.

AFAIK

Named pipes enables clients applications to request a reliable and two way connection between the client application and Sql Server, across a network. A named pipe has a name which follows the UNC naming convention : \\Server\pipe\name

The default names pipe for SQL server is :
\\Server\pipe\sql\query

A named instance would have a named pipe of
\\server\pipe\MSSQL$instancename\sql\query

Early versions of SQL Server only provided Named Pipes.

Named Pipes also allows for impersonation of a clients credentials. The feature is used by SQL server when using Linked Servers. The clients logon credentials are passed across to the Linked Server for authentication.

Named Pipes can operate over TCP/IP, NETBEUI or IPX as it indirectly relies on the Common Internet Files Sytems

What are named pipes?

Both on Windows and POSIX systems, named-pipes provide a way for inter-process communication to occur among processes running on the same machine. What named pipes give you is a way to send your data without having the performance penalty of involving the network stack.

Just like you have a server listening to a IP address/port for incoming requests, a server can also set up a named pipe which can listen for requests. In either cases, the client process (or the DB access library) must know the specific address (or pipe name) to send the request. Often, a commonly used standard default exists (much like port 80 for HTTP, SQL server uses port 1433 in TCP/IP; \\.\pipe\sql\query for a named pipe).

By setting up additional named pipes, you can have multiple DB servers running, each with its own request listeners.

The advantage of named pipes is that it is usually much faster, and frees up network stack resources.

— BTW, in the Windows world, you can also have named pipes to remote machines — but in that case, the named pipe is transported over TCP/IP, so you will lose performance. Use named pipes for local machine communication.

Unix and Windows both have things called «Named pipes», but they behave differently. On Unix, a named pipe is a one-way street which typically has just one reader and one writer — the writer writes, and the reader reads, you get it?

On Windows, the thing called a «Named pipe» is an IPC object more like a TCP socket — things can flow both ways and there is some metadata (You can obtain the credentials of the thing on the other end etc).

Unix named pipes appear as a special file in the filesystem and can be accessed with normal file IO commands including the shell. Windows ones don’t, and need to be opened with a special system call (after which they behave mostly like a normal win32 handle).

Even more confusing, Unix has something called a «Unix socket» or AF_UNIX socket, which works more like (but not completely like) a win32 «named pipe», being bidirectional.

Linux Pipes
First In First Out (FIFO) interproccess communication mechanism.

Unnamed Pipes
On the command line, represented by a «|» between two commands.

Named Pipes
A FIFO special file. Once created, you can use the pipe just like a normal file(open, close, write, read, etc).

To create a named pipe, called «myPipe», from the command line (man page):

To create a named pipe from c, where «pathname» is the name you would like the pipe to have and «mode» contains the permissions you want the pipe to have (man page):

[. ] A traditional pipe is «unnamed» because it exists anonymously and persists only for as long as the process is running. A named pipe is system-persistent and exists beyond the life of the process and must be «unlinked» or deleted once it is no longer being used. Processes generally attach to the named pipe (usually appearing as a file) to perform IPC (inter-process communication).

wc will block until

This is an exeprt from Technet (so not sure why the marked answer says named pipes are faster??):

Named Pipes vs. TCP/IP Sockets

In a fast local area network (LAN) environment, Transmission Control Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are comparable with regard to performance. However, the performance difference between the TCP/IP Sockets and Named Pipes clients becomes apparent with slower networks, such as across wide area networks (WANs) or dial-up networks. This is because of the different ways the interprocess communication (IPC) mechanisms communicate between peers.

For named pipes, network communications are typically more interactive. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it starts to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients.

It is also important to clarify if you are talking about local pipes or network pipes. If the server application is running locally on the computer that is running an instance of SQL Server, the local Named Pipes protocol is an option. Local named pipes runs in kernel mode and is very fast.

For TCP/IP Sockets, data transmissions are more streamlined and have less overhead. Data transmissions can also take advantage of TCP/IP Sockets performance enhancement mechanisms such as windowing, delayed acknowledgements, and so on. This can be very helpful in a slow network. Depending on the type of applications, such performance differences can be significant.

TCP/IP Sockets also support a backlog queue. This can provide a limited smoothing effect compared to named pipes that could lead to pipe-busy errors when you are trying to connect to SQL Server.

Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.

SQL Server Architecture (Explained)

MS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a request. The SQL Server accepts, processes and replies to the request with processed data. Let’s discuss in detail the entire architecture shown below:

As the below Diagram depicts there are three major components in SQL Server Architecture:

1. Protocol Layer
2. Relational Engine
3. Storage Engine

Let’s discuss in detail about all the three above major modules. In this tutorial, you will learn.

Protocol Layer – SNI

MS SQL SERVER PROTOCOL LAYER supports 3 Type of Client Server Architecture. We will start with “Three Type of Client Server Architecture” which MS SQL Server supports.

Shared Memory

Let’s reconsider an early morning Conversation scenario.

MOM and TOM – Here Tom and his Mom, were at the same logical place, i.e. at their home. Tom was able to ask for Coffee and Mom was able it serve it hot.

MS SQL SERVER – Here MS SQL server provides SHARED MEMORY PROTOCOL. Here CLIENT and MS SQL server run on the same machine. Both can communicate via Shared Memory protocol.

Analogy: Lets map entities in the above two scenarios. We can easily map Tom to Client, Mom to SQL server, Home to Machine, and Verbal Communication to Shared Memory Protocol.

From the desk of configuration and installation:

For Connection to Local DB – In SQL Management Studio, “Server Name” Option could be

TCP/IP

Now consider in the evening, Tom is in the party mood. He wants a Coffee ordered from a well-known Coffee Shop. The Coffee shop is located 10 km away from his home.

Here Tom and Starbuck are in different physical location. Tom at home and Starbucks at the busy marketplace. They’re communicating via Cellular network. Similarly, MS SQL SERVER provides the capability to interact via TCP/IP protocol, where CLIENT and MS SQL Server are remote to each other and installed on a separate machine.

Analogy: Lets map entities in the above two scenarios. We can easily map Tom to Client, Starbuck to SQL server, the Home/Market place to Remote location and finally Cellular network to TCP/IP protocol.

Notes from the desk of Configuration/installation:

• In SQL Management Studio – For Connection via TCP\IP, “Server Name” Option has to be “Machine\Instance of the server.”
• SQL server uses port 1433 in TCP/IP.

Named Pipes

Now finally at night, Tom wanted to have a light green tea which her neighbor, Sierra prepare very well.

Here Tom and his Neighbor, Sierra, are in same physical location, being each other’s neighbor. They’re communicating via Intra network. Similarly, MS SQL SERVER provides the capability to interact via the Named Pipe protocol. Here the CLIENT and MS SQL SERVER are in connection via LAN.

Analogy: Lets map entities in the above two scenarios. We can easily map Tom to Client, Sierra to SQL server, Neighbor to LAN and finally Intra network to Named Pipe Protocol.

Notes from the desk of Configuration/installation:

• For Connection via Named Pipe. This option is disabled by default and needs to be enabled by the SQL Configuration Manager.

What is TDS?

Now that we know that there are three types of Client-Server Architecture, lets us have a glance at TDS:

• TDS stands for Tabular Data Stream.
• All 3 protocols use TDS packets. TDS is encapsulated in Network packets. This enables data transfer from the client machine to the server machine.
• TDS was first developed by Sybase and is now Owned by Microsoft

Relational Engine

The Relational Engine is also known as the Query Processor. It has the SQL Server components that determine what exactly a query needs to do and how it can be done best. It is responsible for the execution of user queries by requesting data from the storage engine and processing the results that are returned.

As depicted in the Architectural Diagram there are 3 major components of the Relational Engine. Let’s study the components in detail:

CMD Parser

Data once received from Protocol Layer is then passed to Relational Engine. “CMD Parser” is the first component of Relational Engine to receive the Query data. The principal job of CMD Parser is to check the query for Syntactic and Semantic error. Finally, it generates a Query Tree. Let’s discuss in detail.

Syntactic check:

• Like every other Programming language, MS SQL also has the predefined set of Keywords. Also, SQL Server has its own grammar which SQL server understands.
• SELECT, INSERT, UPDATE, and many others belong to MS SQL predefined Keyword lists.
• CMD Parser does syntactic check. If users’ input does not follow these language syntax or grammar rules, it returns an error.

Example: Let’s say a Russian went to a Japanese restaurant. He orders fast food in the Russian language. Unfortunately, the waiter only understands Japanese. What would be the most obvious result?

The Answer is – the waiter is unable to process the order further.

There should not be any deviation in Grammar or language which SQL server accepts. If there are, SQL server cannot process it and hence will return an error message.

We will learn about MS SQL query more in upcoming tutorials. Yet, consider below most basic Query Syntax as

Now, to get the perception of what syntactic does, say if the user runs the basic query as below:

Note that instead of ‘SELECT’ user typed “SELECR.”

Result: THE CMD Parser will parse this statement and will throw the error message. As “SELECR” does not follow the predefined keyword name and grammar. Here CMD Parser was expecting “SELECT.”

Semantic check:

• This is performed by Normalizer.
• In its simplest form, it checks whether Column name, Table name being queried exist in Schema. And if it exists, bind it to Query. This is also known as Binding.
• Complexity increases when user queries contain VIEW. Normalizer performs the replacement with the internally stored view definition and much more.

Let’s understand this with help of below example –

Result: THE CMD Parser will parse this statement for Semantic check. The parser will throw an error message as Normalizer will not find the requested table (USER_ID) as it does not exist.

Create Query Tree:

• This step generates different execution tree in which query can be run.
• Note that, all the different trees have the same desired output.

Optimizer

The work of the optimizer is to create an execution plan for the user’s query. This is the plan that will determine how the user query will be executed.

Note that not all queries are optimized. Optimization is done for DML (Data Modification Language) commands like SELECT, INSERT, DELETE, and UPDATE. Such queries are first marked then send to the optimizer. DDL commands like CREATE and ALTER are not optimized, but they are instead compiled into an internal form. The query cost is calculated based on factors like CPU usage, Memory usage, and Input/ Output needs.

Optimizer’s role is to find the cheapest, not the best, cost-effective execution plan.

Before we Jump into more technical detail of Optimizer consider below real-life example:

Example:

Let’s say, you want to open an online Bank account. You already know about one Bank which takes a maximum of 2 Days to open an account. But, you also have a list of 20 other banks, which may or may not take less than 2 days. You can start engaging with these banks to determine which banks take less than 2 days. Now, you may not find a bank which takes less than 2 Days, and there is additional time lost due to the search activity itself. It would have been better to open an account with the first bank itself.

Conclusion: It’s is more important to select wisely. To be precise, choose which option is best, not the cheapest.

Similarly, MS SQL Optimizer works on inbuilt exhaustive/heuristic algorithms. The goal is to minimize query run time. All the Optimizer algorithms are propriety of Microsoft and a secret. Although, below are the high-level steps performed by MS SQL Optimizer. Searches of Optimization follows three phases as shown in the below diagram:

Phase 0: Search for Trivial Plan:

• This is also known as Pre-optimization stage.
• For some cases, there could be only one practical, workable plan, known as a trivial plan. There is no need for creating an optimized plan. The reason is, searching more would result in finding the same run time execution plan. That too with the extra cost of Searching for optimized Plan which was not required at all.
• If no Trivial plan found, then 1 st Phase starts.

Phase 1: Search for Transaction processing plans

• This includes the search for Simple and Complex Plan.
• Simple Plan Search: Past Data of column and Index involved in Query, will be used for Statistical Analysis. This usually consists but not restricted to one Index Per table.
• Still, if the simple plan is not found, then more complex Plan is searched. It involves Multiple Index per table.

Phase 2: Parallel Processing and Optimization.

• If none of the above strategies work, Optimizer searches for Parallel Processing possibilities. This depends on the Machine’s processing capabilities and configuration.
• If that is still not possible, then the final optimization phase starts. Now, the final optimization aim is finding all other possible options for executing the query in the best way. Final optimization phase Algorithms are Microsoft Propriety.

Query Executor

Query executer calls Access Method. It provides an execution plan for data fetching logic required for execution. Once data is received from Storage Engine, the result gets published to the Protocol layer. Finally, data is sent to the end user.

Storage Engine

The work of the Storage Engine is to store data in a storage system like Disk or SAN and retrieve the data when needed. Before we deep dive into Storage engine, let’s have a look at how data is stored in Database and type of files available.

Data File and Extent:

Data File, physically stores data in the form of data pages, with each data page having a size of 8KB, forming the smallest storage unit in SQL Server. These data pages are logically grouped to form extents. No object is assigned a page in SQL Server.

The maintenance of the object is done via extents. The page has a section called the Page Header with a size of 96 bytes, carrying the metadata information about the page like the Page Type, Page Number, Size of Used Space, Size of Free Space, and Pointer to the next page and previous page, etc.

File types

1. Primary file

• Every database contains one Primary file.
• This store all important data related to tables, views, Triggers, etc.
• Extension is .mdf usually but can be of any extension.

1. Secondary file

• Database may or may not contains multiple Secondary files.
• This is optional and contain user-specific data.
• Extension is .ndf usually but can be of any extension.

1. Log file

• Also known as Write ahead logs.
• Extension is .ldf
• Used for Transaction Management.
• This is used to recover from any unwanted instances. Perform important task of Rollback to uncommitted transactions.

Storage Engine has 3 components; let’s look into them in detail.

Access Method

It acts as an interface between query executor and Buffer Manager/Transaction Logs.

Access Method itself does not do any execution.

The first action is to determine whether the query is:

1. Select Statement (DDL)
2. Non- Select Statement (DDL & DML)

Depending upon the result, the Access Method takes the following steps:

1. If the query is DDL, SELECT statement, the query is pass to the Buffer Manager for further processing.
2. And if query if DDL, NON-SELECT statement, the query is pass to Transaction Manager. This mostly includes the UPDATE statement.

Buffer Manager

Buffer manager manages core functions for modules below:

• Plan Cache
• Data Parsing: Buffer cache & Data storage
• Dirty Page

We will learn Plan, Buffer and Data cache in this section. We will cover Dirty pages in the Transaction section.

Plan Cache

• Existing Query plan: The buffer manager checks if the execution plan is there in the stored Plan Cache. If Yes, then query plan cache and its associated data cache is used.
• First time Cache plan: Where does existing Plan cache come from?If the first-time query execution plan is being run and is complex, it makes sense to store it in in the Plane cache. This will ensure faster availability when the next time SQL server gets the same query. So, it’s nothing else but the query itself which Plan execution is being stored if it is being run for the first time.

Data Parsing: Buffer cache & Data Storage

Buffer manager provides access to the data required. Below two approaches are possible depending upon whether data exist in the data cache or not:

Buffer Cache – Soft Parsing:

Buffer Manager looks for Data in Buffer in Data cache. If present, then this Data is used by Query Executor. This improves the performance as the number of I/O operation is reduced when fetching data from the cache as compared to fetching data from Data storage.

Data Storage – Hard Parsing:

If data is not present in Buffer Manager than required Data is searched in Data Storage. If also stores data in the data cache for future use.

Dirty Page

It is stored as a processing logic of Transaction Manager. We will learn in detail in Transaction Manager section.

Transaction Manager

Transaction Manager is invoked when access method determines that Query is a Non-Select statement.

Log Manager

• Log Manager keeps a track of all updates done in the system via logs in Transaction Logs.
• Logs have Logs Sequence Number with the Transaction ID and Data Modification Record.
• This is used for keeping track of Transaction Committed and Transaction Rollback.

Lock Manager

• During Transaction, the associated data in Data Storage is in the Lock state. This process is handled by Lock Manager.
• This process ensures data consistency and isolation. Also known as ACID properties.

Execution Process

• Log Manager start logging and Lock Manager locks the associated data.
• Data’s copy is maintained in the Buffer cache.
• Copy of data supposed to be updated is maintained in Log buffer and all the events updates data in Data buffer.
• Pages which store the data is also known as Dirty Pages.
• Checkpoint and Write-Ahead Logging: This process run and mark all the page from Dirty Pages to Disk, but the page remains in the cache. Frequency is approximately 1 run per minute.But the page is first pushed to Data page of the log file from Buffer log. This is known as Write Ahead Logging.
• Lazy Writer: The Dirty page can remain in memory. When SQL server observes a huge load and Buffer memory is needed for a new transaction, it frees up Dirty Pages from the cache. It operates on LRU – Least recently used Algorithm for cleaning page from buffer pool to disk.

Summary:

• Three Type of Client Server Architecture exist: 1) Shared Memory 2) TCP/IP 3)Named Pipes
• TDS, developed by Sybase and now owned by Microsoft, is a packet which is encapsulated in Network packets for data transfer from the client machine to the server machine.
• Relational Engine contains three major components:CMD Parser: This is responsible for Syntactic and Semantic error & finally generate a Query Tree.

Optimizer: Optimizer role is to find the cheapest, not the best, cost-effective execution plan.

Buffer Manager: Buffer manager manages core functions for Plan Cache, Data Parsing & Dirty Page.