Добрый день, уважаемые читатели блога. Не столь давно, а именно в конце весны текущего года, автор данных строк и ваш покорный слуга читал очередной цикл лекций по SQL Server. Как всегда после доклада была масса вопросов, как непосредственно по прочитанной теме, так и вообще, «около SQL-ных». Часть этих вопросов в который уже раз привлекла внимание автора к явному недопониманию SQL-администраторами (в том числе имеющим значительный опыт работы с данной системой) назначения и принципов функционирования такого незаметного, но важного (как минимум, его положено считать таковым) сервиса как SQL Server Browser (или короче — SQL Browser). Зачем он нам вообще? Следует ли держать его включенным или выключенным? Есть ли ему альтернативы? Влияет ли на его поведение конкретная строка подключения, с которой клиент обращается к серверу? И так далее, и тому подобное... Изрядная часть упомянутых администраторов решает все указанные вопросы очень просто: «а как инсталлятор SQL сервера настроил, так оно и работает». Но это явно путь не истинных «SQL-гуру» вообще и не путь читателей блога sqlCMD.ru в частности. Нам, разумеется, нужно четкое и ясное представление о данном компоненте, о «физике» его работы и о критериях по которым мы можем принять взвешенное решение — запускать ли данный сервис автоматически, запускать его же лишь иногда вручную, либо попросту перевести его в состояние disable.
Все это привело автора к желанию написать относительно короткую заметку (по крайней мере в масштабах того блога на котором вы имеете удовольствие находится прямо сейчас такие заметки считаются умеренными по объему 
), в которой бы излагались практические правила работы с обсуждаемым сервисом. Статей теоретизирующих его применение и поясняющих его назначение «вообще» существует немало, но автору хотелось дать своим читателям максимально простой и ясный инструмент позволяющий в секунды проверить — «что будет, если я вот в такой конфигурации отключу SQL Browser»? Вот эта самая заметка и этот самый инструмент — перед вами.
Конфигурация тестового стенда.
Традиционно всякие «опасные» опыты и эксперименты с IT-системами принято проводить на выделенных компьютерах обобщенно называемых «тестовыми стендами». У автора в качестве такового выступает виртуальная машина под управлением VMware® Workstation. Почему автор выбрал в качестве системы виртуализации именно эту систему (а не более другую, если вы меня понимаете 
), а так же краткое руководство по созданию в ней виртуальной машины можно узнать из нескольких первых частей довольно объемного цикла SQL кластер на VMware Workstation. Для статьи текущей, факт виртуальности тестового стенда не играет абсолютно никакой роли. Мы можем считать, что у автора есть совершенно отдельный компьютер имеющий такую конфигурацию:
- имя компьютера — WINR2;
- экземпляры SQL Server на нем — три. По умолчанию и два именованных экземпляра, MSSQL2 и MSSQL3;
- сетевой адрес компьютера — статический IP-адрес 192.168.81.3.
Вот такие три настройки релевантны теме нашей сегодняшней беседы. Все дальнейшие описания и обсуждения, в том числе касающиеся обещанного выше инструмента, ведутся именно для них. Если настройки вашего тестового стенда/производственной среды отличаются (а, надо думать, это практически гарантированный случай) вносите соответствующие корректировки в читаемый вами текст/код.
Немного теории.
Хотя статья заявлена как «чисто практическая» уж совсем без теории нам не обойтись — нужна же нам некая «отправная точка», верно? Так вот, давайте максимально сжато и кратко опишем проблематику вопроса.
Как известно, в мире вообще (и в рамках той организации где мы имеем удовольствие работать в частности) есть куча серверов, и не обязательно SQL. SQL Server, как всем хорошо известно, это лишь один из возможных (но совсем не обязательных) сервисов (Windows service) только могущих быть размещенных на этих серверах. Мы, как клиенты-потребители этих многочисленных сервисов, выбираем с каким физическим сервером нам хочется поработать указывая его IP-адрес в сети. Итак, клиент (то есть некое софт-приложение) выбирает конкретный сервер адресуясь к нему по соответствующему IP-адресу. Но — на физическом сервере может быть куча сервисов. Как клиенту обозначить себя как клиента именно SQL Server сервиса? А вот для этого тот самый клиент должен указать не только верный IP-адрес, но и номер порта на этом адресе. Причем сервис еще может выбирать какой порт ему «слушать», а вот клиент выбирать к какому ему порту подключаться — нет (разумеется мы предполагаем что клиенту нужны услуги строго предопределенного сервиса, а не любого какой первый подвернется). В этом смысле первый полностью предопределяет поведение второго. Скажем, в случае тестового стенда автора клиент должен указать IP=192.168.81.3 (см. сетевые настройки тестового стенда чуть выше) и порт=1433 (а об этом поговорим через предложение). Тогда всем и сразу ясно: клиент желает работать с сервером WINR2, с сервисом SQL Server, и причем именно с его экземпляром по умолчанию. Это все потому, что порт 1433 официально закреплен именно за программным продуктом SQL Server, причем уже внутри этого продукта порт закреплен за именно экземпляром по умолчанию. Произвела такое закрепление организация уполномоченная производить подобные операции, IANA (Internet Assigned Numbers Authority). Убедиться, что указанный порт действительно делегирован ею исключительно для нужд SQL Server можно в довольно-таки официальном документе Service Name and Transport Protocol Port Number Registry на сайте самой организации. Из этого документа ясно видно, что под нужды нашего главного героя, то бишь под SQL Server, зарезервированы два порта: 1433 и 1434. И причем каждый порт зарезервирован для двух транспортных протоколов — и для TCP, и для UDP. На текущий момент вам должно быть понятно назначение одной из четырех комбинаций: TCP/1433. Именно по этому протоколу и именно по этому порту каждый клиент обращается к сервису SQL Server и причем именно к экземпляру по умолчанию. Назначение прочих комбинаций протокол/порт станет ясно по ходу изложения материала.
Так вот, вплоть до версии SQL Server 2000 проблем с описанной архитектурой было ровно ноль. Поскольку экземпляров на одном физическом компьютере в те далекие года могло быть ровно 0 или 1, то этот самый единственный экземпляр (сейчас бы мы назвали его «дефолтовым», но тогда он ни в каких обозначениях не нуждался) прямо при инсталляции «садился» на порт 1433 и начинал «слушать» запросы клиентов. Последим же оставалось лишь указать правильный IP-адрес того физического компьютера где данный эксклюзивный экземпляр сервера обретался и — вуа-ля, работай себе на здоровье. С версии 2000 появилось само понятие «множественный экземпляры SQL Server». Да и сам термин «экземпляр» появился в те же дни, ведь до того, как вы уже поняли, термины «экземпляр» и «SQL Server» были эквивалентны и в особом обозначении первого нужды не было. Эти качественные (прежде всего) и количественные (во вторую очередь) изменения существенно подорвали стройную картину мира с единственным 1433-м портом. Ведь каждый экземпляр, как всем хорошо известно, это, на физическом уровне, свой отдельный Windows service. И клиент должен явно указать, желает ли он работать с условным SQL Server 1 (условный же Windows service для него обозначим как sqlsrv1), или же с не менее условным SQL Server 2 (сервис sqlsrv2). А как происходит дифференциация sqlsrv1 от sqlsrv2 при условии что они оба запущены на одном физическом компьютере? Правильно, уже ответили в первом абзаце текущего раздела: эти сервисы должны были «слушать» разные порты, а клиенты, соответственно, адресовать запросы именно на эти прослушиваемые порты.
Когда очертилась новая архитектура решили так: экземпляр по умолчанию — не трогаем вообще. Как он «садился» на 1433, так пусть и дальше поступает. И для клиентов, разумеется, ничего не изменилось. Но вот для любых именованных экземпляров встал вопрос такого сорта: какой порт назначить им? Зарезервировать для каждого такого экземпляра свой порт? Можно было б, но вот последние версии SQL Server позволяют ставить до 50-ти (!) экземпляров на одну машину. Если б каждый производитель софта резервировал бы порты «на всякий» и в таких объемах они б все (порты, а не софт-производители) были бы распределены еще в прошлом веке. И писать свои собственные сетевые сервисы/клиентов стало б решительно невозможно — где свободные порты брать? Поэтому поступили по-джентельменски: оставили официально «забитыми» лишь все те же два порта, 1433 и 1434. Для именованных же экземпляров порешили так:
- или при запуске сервис этого самого именованного же экземпляра «садится» на порт X, самостоятельно (и более-менее случайно) выбирая этот самый X среди не занятых портов данной конкретной машины. При перезапуске сервиса X может как сохранить свое предыдущее значение, так и изменить его;
- или, если того пожелает DBA, он может самостоятельно «делегировать» конкретный порт X (разумеется, X обязан быть свободным и ни одно приложение того же компьютера не должно на него претендовать) тому же именованному экземпляру. Тогда при каждом (пере-)запуске сервиса экземпляр будет стабильно «садиться» именно на X.
В общем так или иначе, но если именованный экземпляр запустился — он определенно «оседлал» некий порт X. И вот тут нас ждет финальный вопрос ради которого и весь сыр-бор с SQL Browser и разыгрался: как быть клиентам желающим обратиться именно к именованному экземпляру? Ладно если DBA выбрал второй из двух возможных подходов, тогда такой статический порт можно сообщить клиенту, а именно и скорее всего программисту «ваяющему» этот клиент на одном из высокоуровневых языков программирования. Большой вопрос стоит ли вообще сообщать столь чувствительную информацию как номер порта, открытого в корпоративном файрволе (и как мы увидим далее на этот счет существуют разные точки зрения). Но это хоть возможно чисто технически. Ну а если выбран первый путь (который выбирается установщиком по умолчанию, между прочим)? Да тогда обычно и сам DBA не знает конкретного значения X! Он может, конечно, выяснить эту цифру, но что делать клиенту? Перекомпилироваться каждый раз при перезапуске сервиса (порт-то возможно сменился!)? Или, в лучшем случае, править клиентский ini-файл содержащий строку подключения? Это на пятистах-то рабочих станций? Не смешно...
Хорошо, посмотрим на проблему с такой стороны: если номер порта есть сущность «плавающая», то что бы избежать многократных перекомпиляций/редактирований ini-файла у клиента, вполне очевидно, должна быть более другая и стабильная сущность. Которая сохраняется (и гарантированно сохраняется!) сколько б сервис именованного экземпляра не перезапускали. И такая сущность есть! Зовется она именем экземпляра (instance name) и назначается, обыкновенно, один раз в жизни — в момент инсталляции этого самого экземпляра. Разумеется, и это имя не стоит размещать на билбордах города, однако имя экземпляра, несомненно, менее «хак-чувствительная» информация по сравнению с конкретным номером открытого порта. И вот ее уж точно можно (и даже, без вариантов, нужно) свободно сообщить программисту клиентского приложения. Однако и это еще не конец истории. Даже если клиент знает имя экземпляра это, само по себе, не поможет ему подключиться к требуемому сервису ни на йоту. Потому что «генеральное» правило пользования сервиса осталось все тем же: указать правильный IP-адрес для подключения к нужному компьютеру, плюс указать порт (а не какое-то там имя) для пользования услугами нужного сервиса в рамках этого компьютера. Ну IP-адрес (а точнее и скорее всего просто доменное имя) сервера с SQL Server наш программист, конечно, знает. А вот порта он как не знал, так и продолжает, даже если DBA сообщает ему корректное имя экземпляра.
В общем, вы уже догадались: нам нужен некто/нечто способное провести сопоставление (по нашему, по «IT-шному» — маппирование) «имя экземпляра» → «номер порта, этим экземпляром прослушиваемого». Так вот если сказать совсем по простому, то 99% функционала изучаемого нами сервиса SQL Browser предназначен для проведения именно такого маппирования. То есть, с совсем небольшой натяжкой можно сказать, что SQL Browser решает эксклюзивно единственную задачу: принять от клиента имя экземпляра и сообщить ему взамен номер порта, на котором тот экземпляр «сидит». Еще 1% функционала сводится к выдаче тем же сервисом списка всех экземпляров SQL Server на данном физическом компьютере, но, хотя мы и коснемся и этого функционала в дальнейшем материале, он совершенно точно является вторичным на фоне основной «мета-задачи» маппирования.
Инструмент тестирования.
Итак, как вы уже поняли из вводной части статьи, отключение сервиса SQL Browser может привести к одной единственной «маленькой» проблемке: ни один клиент не сможет подключиться к нашему SQL серверу. Может показаться, что проблемка в принципе касается лишь именованных экземпляров, и, в большой степени, это замечание справедливо. Однако, как будет показано далее, возможны проблемы и с экземпляром по умолчанию, правда лишь в весьма специфических условиях и особой конфигурации.
Под инструментом тестирования автор данных строк понимает некоторую программу позволяющую за несколько секунд убедиться — будет ли тот или иной клиент работать (или, более точно, как минимум подключаться) с указанным экземпляром в случае запущенного/остановленного сервиса SQL Browser. Сразу запомним себе правило:
Итак, вся суть тестирования упирается не более чем в «подсовывании» клиенту различных строк подключения и наблюдения за тем, что он нам скажет в ответ на попытку соединиться c SQL Server. Вполне понятно, что технически реализовать эту совсем простую задачу можно десятками (если не сотней) способов. Можно было взять банальный SQL Server Management Studio и использовать его как клиента подключающегося с различными connection strings. Не менее блестяще с той же задачей справилась бы утилита командной строки, чье имя дало название данному блогу. Однако, по ряду причин (среди которых и образовательная), автор решил создать супер-элементарное консольное приложение (Visual Studio у всех есть?), где требуемая строка подключения формируется редактированием трех констант — двух строковых и одной целочисленной. Разумеется, по вкусу, вы вольны переделать данный код таким образом, что бы значения брались из командной строки, или даже из внешнего файла конфигурации, однако автор стремился к абсолютному минимализму и вот что у него получилось:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | using System; using System.Text; using System.Data.SqlClient; namespace TestSQLbrowser { class Program { const string instName=null; const string ipNum="192.168.81.3"; const int portNum=0; static string ds=ipNum+ (string.IsNullOrEmpty(instName)?string.Empty:@"\"+instName)+ (portNum==0?string.Empty:@","+portNum.ToString()); static string conn="Network Library=dbmssocn;Data Source="+ds+ ";Initial Catalog=master;Integrated Security=sspi;Connect Timeout=3"; static void Main() { Console.WriteLine("Try connect to: "+ds); using(SqlConnection connection=new SqlConnection(conn)) { try { connection.Open(); Console.WriteLine("Connection is established!"); } catch(Exception ex) { Console.WriteLine("Error detected: "+ex.Message.ToString()); } } Console.ReadKey(true); } } } |
Код настолько незатейлив, что будет понятен даже если «магическое» словосочетание «си-шарп» не говорит вам ровным счетом ничего. Наш главный предмет исследования, строка подключения, формируется в переменной conn и состоит из параметров этой строки разделенных точкой с запятой. Четыре параметра у нас имеют фиксированное значение, пробежимся сначала по ним:
- Network Library=dbmssocn — для той задачи что мы перед собой ставим у тестового стенда автора есть небольшая проблемка. Ведь его тестовый стенд представляет собой обособленный компьютер не предполагающий внешних подключений. То есть и клиент, и все сервисы SQL Server размещаются у автора на одной физической (хоть и виртуальной ) машине. А нам нужно тестировать именно подключение клиента, причем не абы как (через Shared Memory, допустим, нам подключение и даром не нужно), а исключительно по протоколу TCP/IP, то бишь через сеть. Параметр Network Library со значением dbmssocn форсирует выбор именно TCP/IP библиотеки и мы можем не сомневаться — если подключение нашим клиентом установлено, оно установлено только через сеть. Не обязательно даже отключать протоколы Shared Memory у тестируемых экземпляров. Кстати, автору вспомнилось, что на тех же лекциях с которых начался рассказ, один из слушателей увидев обсуждаемый параметр спросил: «а разве не правильней форсировать выбор клиентом протокола через префикс другого параметра, Data Source»? Во-первых, о спрошенном параметре речь у нас впереди, а во-вторых, что касается непосредственно вопроса, то ответ будет — без разницы. То бишь, строка начинающаяся с Network Library=dbmssocn;Data Source=WINR2;... функционально эквивалентна строке начинающейся с Data Source=tcp:WINR2;..., ибо второе попросту разворачивается в первое. То есть имеет место дублирование синтаксиса. В целом, IT-отрасль вообще и ее языки программирования в частности стоят и основываются на избыточном дублировании самых различных компонентов, начиная от «железа» и заканчивая DLL-библиотеками. Но то, что получилось в конкретно синтаксисе строки подключения для клиентов создаваемых под платформу .NET выходит за всякие рамки приличий. Одно и тоже можно сказать в ней десятком (и автор не гиперболизирует, это буквально!) способов. Что, конечно, не есть хорошо. Впрочем мы отвлеклись, а с этим неприличным дублированием имен параметров и их значениями в строке подключения .NET поделать все-равно ничего нельзя. Замечу лишь, что не следует заниматься «дублированием дублей» и писать что-то вроде Network Library=dbmssocn;Data Source=tcp:WINR2;..., это попросту неработоспособно. Так что форсируйте протокол (если подобный «форсаж» вообще нужен вашему клиенту) или через параметр Network Library, или через префикс, но не то и другое сразу;
- Initial Catalog=master — в контекст какой базы данных переключаться сразу за успешным соединением с сервером. Нам совершенно без разницы, ну пусть master будет;
- Integrated Security=sspi — SQL Server будет аутентифицировать нас через проверку подлинности Windows, то бишь никакие логины/пароли не требуются;
- Connect Timeout=3 — поскольку у нас соединение клиента будет хоть и сетевое, но все же до известной степени локальное, нам нет нужны выжидать 15 секунд (значение данного параметра по умолчанию), что бы понять неуспешность данной попытки. За 3 секунды наш клиент либо будет подключен к серверу, либо сообщит нам причину неуспеха такой попытки.
К рассмотренным только что четырем статическим параметрам «приклеивается» параметр пятый, тот самый Data Source. Это, с большим опережением, самый важный параметр любой строки подключения, именно здесь задается целевой сервер (через IP, либо имя сервера) и, возможно, целевой порт. Значение данного параметра формируется в момент запуска нашего клиента из значений трех констант, а именно:
- имя сервера — указывается обязательно в константе ipNum, про разные форматы этой компоненты параметра Data Source мы немного поговорим далее;
- имя экземпляра — указывается только если константа instName имеет значение отличное от null, причем значения предыдущей константы и этой автоматически разделяются слешем соответствующего вида. Если же instName имеет значение по умолчанию (null), то эта компонента просто игнорируется;
- номер порта — указывается только если константа portNum имеет значение отличное от 0, причем значения предыдущих двух констант и этой автоматически разделяются запятой. Если же portNum имеет значение по умолчанию (0), то и эта компонента просто игнорируется.
Ну а логика самого приложения более чем элементарна: вывести сформированное значение параметра Data Source на консоль, попробовать подключиться согласно этому значению, сообщить туда же, на консоль, итог такой попытки. Ну и дать нам возможность прочитать все это через ожидание нажатия любой клавиши.
Для примера, комбинация значений трех разобранных констант
1 2 3 | const string instName="MSSQL2"; const string ipNum="192.168.81.3"; const int portNum=48229; |
определенно выведет на консоль строку
и затем результат подключения к такому серверу и такому порту. Если результат положителен, то вы, фактически, имеете готовую валидную строку подключения (или, как минимум, центральную часть этой строки) для, практически, любого клиента. Вы, допустим, можете подключить к тому же самому сервису студию (не ту, что Visual, а ту что Management):
Впрочем, если вы надумаете добавить новый «коннекшен» в окно Server Explorer именно Visual студии — просто пишите в соответствующем окне диалога ровно тоже самое:
Ну и конечно же, небезызвестная утилита командной строки, как же ее и не подключить:
То есть, часть Data Source строки подключения определенно унифицирована среди всех программных клиентов, как минимум авторства Microsoft, а, по хорошему, это должен быть де-факто стандарт для абсолютно любых клиентов. В том числе и наших, «самописных». Варьироваться могут лишь флаги командной строки/названия опции конфигурационного файла/названия полей окон диалогов, но вот формат значений тех самых флагов/опций/полей должен быть совершенно стандартизован. Три приведенных примера это ясно подчеркивают.
Итого: мы поняли исходную проблему (необходимость маппирования одного значения в другое), мы знаем механизм решения этой проблемы (сервис SQL Browser) и у нас на руках есть неплохой тест-клиент для апробирования того самого механизма в различных условиях конфигураций как сервера, так и клиента. Последнее замечание, кажущееся автору совершенно самоочевидным, но, на всякий случай: сервис SQL Browser должен быть запущен не на клиенте, как думают некоторые (с чего это людям в голову приходит подобная дичь? 
), а, конечно же, на том же физическом компьютере на котором развернут как минимум один (а как максимум — 50) экземпляр целевого сервиса для подключения клиента, то есть SQL Server. И да, имейте в виду, что даже если компьютер имеет инсталированными все 50 экземпляров (читай — 50 копий сервиса SQL Server), то SQL Browser будет все равно ровно один. Если вы еще раз проанализируете главную «мета-задачу» последнего сервиса, то без труда сообразите, что больше одного и не требуется. Ведь любой клиент вне зависимости от своих дальнейших намерений может «спросить» у этого единственного сервиса номер порта необходимого ему экземпляра и получить ответ.
Ну вот, теперь мы совершенно готовы к проведению практических опытов и экспериментов.