Доброго времени суток,
коллеги.
В данной статье
рассмотрим конфигурацию технологии NetScaler DataStream для Microsoft SQL 2014 AlwaysOn.
Технология
NetScaler DataStream, используя политики балансировки и оптимизации,
анализирует SQL транзакции в режиме реального времени, что позволяет
оптимизировать инфраструктуру баз данных путем мультиплексирования
SQL-подключений и оптимального распределения нагрузки SQL-запросов на наиболее
доступные серверы баз данных. Подробнее тут.
Другими словами, клиент
подключается к VIP Connect Switching Netscaler’а, и если поступает запрос на
запись (обновление, удаление или добавление), то политика перенаправляет запрос
на Listener MS SQL сервера, который перенаправляет запрос на первичную реплику MS
SQL, имеющую возможность записи в базу данных.
Если же поступает запрос
на чтение, то политика перенаправляет запрос на остальные Read-Only сервера MS SQL.
Также хочу упомянуть,
что технология MS SQL AlwaysOn поддерживает перенаправление read-only запросов
(READ_ONLY_ROUTING_URL), но для его настройки необходимо кастомизировать
само приложение, что не всегда представляется возможным ввиду ряда особенностей, подробнее тут.
Статья будет состоять
из следующих частей:
- Описание работы технологии NetScaler DataStream.
- Настройка MS SQL AlwaysOn.
- Настройка Netscaler.
- Проверка работы технологии.
1. Описание работы технологии NetScaler DataStream.
Процесс обработки
запроса на чтение от клиента.
- Клиент подключается к VIP Connect Switching Netscaler’а «ven-tst-lbsql» и отправляет запрос на чтение.
- CS-сервер парсит запрос и применяет соответствующую политику.
- Согласно политике, осуществляется перенаправление запроса на LB-сервер для чтения «LB_MSSQL_Read»
- Монитор «MSSQL_Read» определяет наименее загруженный Read-Only SQL-сервер. Причем сервис «service-sql02» имеет статус «DOWN» и не будет участвовать в запросах на чтение. Это сервис Primary-реплики сервера «ven-tst-sql02».
- На LB-сервере обрабатывается логика перенаправления запроса к необходимому SQL-серверу по данным монитора и политики Load Balancing’а.
- Согласно политики, LB-сервер перенаправляет запрос на соответствующий Read-Only SQL-сервер, например, на «ven-tst-sql06».
- MS SQL-сервер обрабатывает запрос и возвращает результат клиенту.
Процесс обработки запроса на запись от клиента.
- Клиент подключается к VIP Connect Switching Netscaler’а «ven-tst-lbsql» и отправляет запрос на запись.
- CS-сервер парсит запрос и применяет соответствующую политику.
- Согласно политике, осуществляется перенаправление запроса на LB-сервер для записи «LB_MSSQL_Write»
- Монитор «MSSQL_Write» определяет доступность Listener’а.
- На LB-сервере обрабатывается логика перенаправления запроса к Listener’у по данным монитора и политики Load Balancing’а.
- Согласно политики, LB-сервер перенаправляет запрос на Listener «ven-tst-sqlAO».
- Listener перенаправляет запрос на Primary-реплику «ven-tst-sql02»
- MS SQL-сервер обрабатывает запрос и возвращает результат клиенту.
2. Настройка MS SQL AlwaysOn.
Подробный процесс
установки MS SQL AlwaysOn описан тут.
Приступим к настройке уже
развернутого кластера MS SQL AlwaysOn. Откроем консоль «SQL Server Management Studio», создадим тестовую базу данных «dbuser_test»,
наполним ее данными и добавим в группу высокой доступности.
Для этого выполним на
вновь созданной базе «DB_Test» запрос:
CREATE TABLE Dzen (
number INTEGER,
test TEXT,
nickname TEXT,
ter INTEGER,
)
INSERT INTO Dzen (number,test,nickname,ter)
VALUES
(1000,'Test', 'Munsera',42),
(1001,'Test2','Yulkau',40),
(1002,'Test4','Djeka',13),
(1003,'Test4','Fritzfak',45),
(1004,'Test5','Monax',11);
--COMMIT;
Создадим пользователя «dbuser_test»
с паролем «1qaz@WSX» и добавим его на каждый сервер MS SQL с правами public. Под
данным пользователем Netscaler будет проверять доступность базы данных «dbuser_test».
Для этого щелкаем
правой кнопкой мыши по разделу «Logins», в появившемся контекстном меню
выбираем «New Login».
В появившемся окне в
поле «Login name» вводим логин пользователя «dbuser_test», в поле «Password»
вводим пароль «1qaz@WSX», нажимаем «Ok».
Пользователь успешно
создастся.
Добавим созданному
пользователю права «VIEW SERVER STATE».
Для этого на каждом сервере MS SQL в выполним соответствующий
запрос.
Щелкаем правой кнопкой мыши по SQL серверу, выбираем «New
Query».
В появившемся окне
прописываем:
USE
master;
GO
GRANT
VIEW SERVER STATE TO dbuser_test
GO
Нажимаем «Execute».
3. Настройка Netscaler.
Добавим на Netscaler поочередно
сервера MS SQL «ven-tst-sql01», «ven-tst-sql02», «ven-tst-sql06», а также Listener
«ven-tst-sqlAO».
Для этого необходимо
перейти в раздел «Configuration», раскрыть меню «Traffic Management», далее «Load
Balancing» и выбрать «Server». В появившемся окне нажать «Add».
В окне «Create Server» в
поле «Name» ввести имя MS SQL сервера, в нашем случае «ven-tst-sql01». В поле «IP
Adress» вводим IP-адрес MS SQL сервера, в нашем случае «192.168.0.170»,
нажимаем «Create».
Данную процедуру
необходимо повторить для серверов «ven-tst-sql02», «ven-tst-sql06», а также Listener
«ven-tst-sqlAO».
Сервера успешно
добавятся.
Добавить сервера можно
из командной строки. Для этого необходимо подключиться к Netscaler по проколу SSH
и выполнить:
add server ven-tst-sql01.test.ru
192.168.0.160
add server
ven-tst-sql02.test.ru 192.168.0.161
add server ven-tst-sql06.test.ru
192.168.0.167
add server ven-tst-sqlAO.test.ru
192.168.0.170
show server
Добавим пользователя, под
которым Netscaler будет проверять доступность базы данных.
Для этого в разделе «Configuration»,
раскрыть меню «System», далее «User Administration» и выбрать «Database Users».
В появившемся окне нажать «Add».
В поле «User» вводим «dbuser_test»,
в поле «Password» водим «1qaz@WSX», нажимаем «Create».
Пользователь успешно создастся.
Либо выполним в
командной строке:
add db user dbuser_test
-password 1qaz@WSX
Добавим сервисы для
каждого сервера.
Для этого в разделе «Configuration»
раскрыть меню «Traffic Management», далее «Load Balancing» и выбрать «Services».
В появившемся окне нажать «Add».
В окне «Load Balancing Service»
в поле «Service Name» ввести имя сервиса, в нашем случае «ven-tst-sql01». Далее
щелкнув по меню «Existing Server» выбираем уже заведенный сервер «ven-tst-sql01».
В поле «Protocol» выбираем MSSQL, в поле «Port» вводим «1433», нажимаем «Ok».
Данную процедуру
необходимо повторить для серверов «ven-tst-sql02», «ven-tst-sql06», а также Listener
«ven-tst-sqlAO».
Сервисы успешно
добавятся.
Либо выполним в
командной строке:
add service
service-sql01 ven-tst-sql01.test.ru MSSQL 1433
add service
service-sql02 ven-tst-sql02.test.ru MSSQL 1433
add service
service-sql06 ven-tst-sql06.test.ru MSSQL 1433
add service service-sqlAO
ven-tst-sqlAO.test.ru MSSQL 1433
Создадим два монитора и
добавим их к сервисам. Один монитор «MSSQL_Read» будет определять доступность
базы данных и выявлять ее реплику только для чтения, второй «MSSQL_Write» будет
определять доступность базы данных и выявлять ее реплику на возможность записи в
базу.
Для создания монитора «MSSQL_Read»
необходимо в разделе «Configuration» раскрыть меню «Traffic Management», далее
«Load Balancing» и выбрать «Monitors». В появившемся окне нажать «Add».
В окне «Create Monitor» в поле «Name» вводим
имя монитора «MSSQL_Read», в поле «Type» выбираем тип «MSSQL-ECV». Переходим во
вкладку «Special Parameters». В поле «User Name» вводим имя пользователя базы
данных «dbuser_test», в поле «Database» вводим имя базы данных на которой будет
выполняться запрос, в нашем случае «master». В поле запроса «Query» введем:
SELECT name FROM sys.databases a INNER JOIN sys.dm_hadr_availability_replica_states b ON a.replica_id=b.replica_id WHERE b.role = 2
В поле «Expression» вводим
выражение проверки результата запроса «MSSQL.RES.ATLEAST_ROWS_COUNT(1)"».
В поле «Protocol Version»
выбираем «2012», выделяем «Store DB» и нажимаем «Create».
Либо выполним в
командной строке:
add lb monitor
MSSQL_Read MSSQL-ECV -userName dbuser_test -mssqlProtocolVersion 2012 -sqlQuery
"SELECT name FROM sys.databases a INNER JOIN
sys.dm_hadr_availability_replica_states b ON a.replica_id=b.replica_id WHERE
b.role = 2" -evalRule "MSSQL.RES.ATLEAST_ROWS_COUNT(1)" -database
master -storedb ENABLED
Создадим монитор «MSSQL_Write».
В окне «Create Monitor», в поле «Name» вводим имя монитора «MSSQL_Write», в
поле «Type» выбираем тип «MSSQL-ECV». Переходим во вкладку «Special Parameters».
В поле «User Name» вводим имя пользователя базы данных «dbuser_test», в поле «Database»
вводим имя базы данных на которой будет выполняться запрос, в нашем случае «master».
В поле запроса «Query» введем:
SELECT name FROM sys.databases a INNER JOIN sys.dm_hadr_availability_replica_states b ON a.replica_id=b.replica_id INNER JOIN sys.availability_group_listeners
c ON b.group_id=c.group_id INNER JOIN sys.availability_group_listener_ip_addresses
d ON c.listener_id=d.listener_id WHERE b.role=1 and d.ip_address like '192.168.0.170'
Где IP-адрес '192.168.0.170'
- это адрес Listener’а.
В поле «Expression» вводим
выражение проверки результата запроса «MSSQL.RES.ATLEAST_ROWS_COUNT(1)"».
В поле «Protocol Version»
выбираем «2012», выделяем «Store DB» и нажимаем «Create».
Либо выполним в командной строке:
add lb monitor
MSSQL_Write MSSQL-ECV -userName dbuser_test -mssqlProtocolVersion 2012
-sqlQuery "SELECT name FROM sys.databases a INNER JOIN
sys.dm_hadr_availability_replica_states b ON a.replica_id=b.replica_id INNER
JOIN sys.availability_group_listeners c ON b.group_id=c.group_id INNER JOIN sys.availability_group_listener_ip_addresses
d ON c.listener_id=d.listener_id WHERE b.role=1 and d.ip_address like
'192.168.0.170'" -evalRule " MSSQL.RES.ATLEAST_ROWS_COUNT(1)"
-database master -storedb ENABLED
Мониторы успешно
создадутся.
Привяжем соответствующие
мониторы на необходимые сервисы.
Выделим сервис «service-sql01»,
нажимаем «Edit».
В появившемся окне «Load
Balancing Service» в разделе «Monitors» нажимаем «Service to Load Balancing
Monitor Bilding»
Далее нажимаем «Add
binding».
Выбираем монитор «MSSQL_Read»,
нажимаем «Bind».
Монитор успешно
добавится.
Данную процедуру
необходимо повторить для сервисов «service-sql02», «service-sql06». А также
добавить монитор «MSSQL_Write» для сервиса «service-sqlAO».
Либо выполним в командной
строке:
bind service
service-sql01 -monitorName MSSQL_Read
bind service
service-sql02 -monitorName MSSQL_Read
bind service
service-sql06 -monitorName MSSQL_Read
bind service
service-sqlAO -monitorName MSSQL_Write
По статусу сервисов
видим, что сервера «ven-tst-sql01» и «ven-tst-sql06» будут отвечать на запросы на
чтение, север «ven-tst-sql02» - на запросы на запись.
В нашем случае видно,
что сервис «aervice-sql02» имеет состояние Down, он будет отвечать только на
запросы на запись.
Создадим два Load Balancing
Virtual Server’а и добавим в них соответствующие сервисы.
Для этого в разделе «Configuration»
раскрыть меню «Traffic Management», далее «Load Balancing» и выбрать «Virtual
Servers». В появившемся окне нажать «Add».
В окне «Load Balancing
Virtual Server» в поле «Name» вводим имя LB-севера, в нашем случае «LB_MSSQL_Read».
В поле «Protocol» выбираем «MSSQL», в поле «IP Address Type» выбираем «Non
Addressable», нажимаем «Ok».
LB-сервер успешно создастся.
Далее нажимаем на «Load Balancing Virtual Server Service Binding».
Выделяем сервисы «service-sql01»,
«service-sql02», «service-sql06», нажимаем «Select».
В появившемся окне «Service
Binding» нажимаем «Bind».
В настройках LB-севера
изменим версию SQL-сервера с 2008R2 на 2012.
В настройках алгоритма
балансировки «Method», в поле «Load Balancing Method» выбираем «LEASTCONNECTION»,
нажимаем «Ok».
Аналогично проделываем
для создания и настройки LB-сервера «LB_MSSQL_Write».
Либо в выполним в
командной строке:
add lb vserver LB_MSSQL_Read
mssql 0.0.0.0 0 -lbMethod LeastConnection -dbsLb ENABLED
set lb vserver
LB_MSSQL_Read -mssqlServerVersion 2012
bind lb vserver
LB_MSSQL_Read service-sql01
bind lb vserver
LB_MSSQL_Read service-sql02
bind lb vserver LB_MSSQL_Read
service-sql06
add lb vserver
LB_MSSQL_Write mssql 0.0.0.0 0 -lbMethod LeastConnection -dbsLb ENABLED
set lb vserver
LB_MSSQL_Write -mssqlServerVersion 2012
bind lb vserver
LB_MSSQL_Write service-sqlAO
Видим, что LB-сервера успешно созданы и настроены.
Подробнее тут.
Создадим и настроим Content Switching
Virtual Server, который будет принимать запросы от клиента и, согласно политикам,
перенаправлять их на необходимый Load Balancing Virtual Server.
Для этого в разделе «Configuration» раскрыть
меню «Traffic Management», далее «Content Switching» и выбрать «Virtual Servers».
В появившемся окне нажать «Add».
В окне «Content Switching Virtual
Server» в поле «Name» вводим имя CS-севера, в нашем случае «ven-tst-lbsql». В поле
«Protocol» выбираем «MSSQL», в поле «IP Address» вводим IP-адрес CS-севера, в
нашем случае «192.168.0.171». В поле «Port» вводим «1433», нажимаем «Ok».
В настройках CS-севера изменим версию
SQL-сервера с 2008R2 на 2012.
Content Switching Virtual Server успешно
создастся.
Создадим две политики для CS-сервера.
Политика «CS_MSSQL_Read_Policy» будет перенаправлять запрос на чтение на LB-север
«LB_MSSQL_Read», политика «CS_MSSQL_Write_Policy» на LB-север «LB_MSSQL_Write».
Для этого в разделе «Configuration» раскрыть
меню «Traffic Management», далее «Content Switching» и выбрать «Policies». В появившемся
окне нажать «Add».
В появившемся окне «Create Content
Switching Policy» в поле «Name» вводим «CS_MSSQL_Read_Policy», в поле «Expression»
вводим:
MSSQL.REQ.QUERY.COMMAND.CONTAINS(\"select\")
Нажимаем «Create».
Создадим политику для операций на
запись в БД. В окне «Create Content Switching Policy» в поле «Name» вводим «CS_MSSQL_Write_Policy»,
в поле «Expression» вводим:
MSSQL.REQ.QUERY.TEXT.CONTAINS(\"insert\")
|| MSSQL.REQ.QUERY.TEXT.CONTAINS(\"update\") ||
MSSQL.REQ.QUERY.TEXT.CONTAINS(\"delete\") ||
MSSQL.REQ.QUERY.TEXT.CONTAINS(\"drop\") ||
MSSQL.REQ.QUERY.TEXT.CONTAINS(\"create\") || MSSQL.REQ.QUERY.TEXT.CONTAINS(\"alter\")
Нажимаем «Create».
Видим, что политики успешно созданы.
Входим в свойства CS-сервера и
добавляем данные политики. В окне «Policy Binding» в поле «Select Policy» выбираем
политику «CS_MSSQL_Write_Policy», в поле «Priority» вводим приоритет «100». В поле
«Goto Expression» выбираем «END», в поле «Target Load Balancing Virtual Server»
выбираем LB-север «LB_MSSQL_Write». Нажимаем «Bind».
Добавим политику «CS_MSSQL_Read_Policy».
Для этого в окне «Policy Binding» в поле «Select Policy» выбираем политику «CS_MSSQL_Read_Policy»,
в поле «Priority» вводим пониженный приоритет «110». В поле «Goto Expression» выбираем
«END», в поле «Target Load Balancing Virtual Server» выбираем LB-север «LB_MSSQL_Read».
Нажимаем «Bind».
Видим, что политики успешно привязаны
к CS-серверу.
Настроим на CS-севере LB-север,
который будет применяться по умолчанию. Для этого на странице настройки CS-сервера выбираем «Default Load Balancing Virtual Server».
В появившемся окне в поле «Default Load
Balancing Virtual Server Name» выбираем «LB_MSSQL_Read». Нажимаем
Либо выполним в командной строке:
add cs vserver ven-tst-lbsql MSSQL 192.168.0.171 1433 -mssqlServerVersion
2012
add cs policy CS_MSSQL_Write_Policy -rule
"MSSQL.REQ.QUERY.TEXT.CONTAINS(\"insert\") ||
MSSQL.REQ.QUERY.TEXT.CONTAINS(\"update\") ||
MSSQL.REQ.QUERY.TEXT.CONTAINS(\"delete\") || MSSQL.REQ.QUERY.TEXT.CONTAINS(\"drop\")
|| MSSQL.REQ.QUERY.TEXT.CONTAINS(\"create\") ||
MSSQL.REQ.QUERY.TEXT.CONTAINS(\"alter\")"
add cs policy CS_MSSQL_Read_Policy -rule
"MSSQL.REQ.QUERY.COMMAND.CONTAINS(\"select\")"
bind cs vserver ven-tst-lbsql -policyname CS_MSSQL_Read_Policy -priority
110 -gotoPriorityExpression END -targetLBVserver LB_MSSQL_Read
bind cs vserver ven-tst-lbsql -policyname CS_MSSQL_Write_Policy -priority
100 -gotoPriorityExpression END -targetLBVserver LB_MSSQL_Write
bind cs vserver ven-tst-lbsql LB_MSSQL_Write
bind cs vserver ven-tst-lbsql LB_MSSQL_Read
Подробнее тут.
Ниже описана логика работы и применения политик с приоритетами:
- Клиент, через CS-сервер подключается к LB-северу «LB_MSSQL_Read» по умолчанию, далее отрабатывают политики, и, в зависимости от запроса, CS-сервер перенаправляет подключение на необходимый LB-север.
- Если в запросе присутствуют запросы "insert", "update", "delete", "drop", "create", "alter", то отрабатывает политика «LB_MSSQL_Write» и запрос перенаправляется на LB-сервер «LB_MSSQL_Write»
- Если в запросе присутствует только «select», то политика с высоким приоритетом не применима, поэтому отрабатывает политика низкого приоритета «CS_MSSQL_Read_Policy» и запрос перенаправляется на LB-сервер «LB_MSSQL_Read».
- Если в запросах присутствуют конфликтующие политикам операторы, например, «Select» и «Update» одновременно, то запрос клиента, по умолчанию подключенного к «LB_MSSQL_Read», согласно примененной политики с высоким приоритетом «CS_MSSQL_Write_Policy» перенаправляется на LB-сервер «LB_MSSQL_Write».
4. Проверка работы технологии.
При помощи утилиты SQLCMD выполним циклический запрос к CS-серверу на
чтение от пользователя test, имеющего права на чтение\запись в базу данных DB_Test:
USE DB_Test;
GO
declare @r int
select @r = 1
while @r<500000 begin
SELECT * FROM Dzen
select @r = @r+1
end
В консоли SQLCMD запрос будет выглядеть так:
sqlcmd -S 192.168.0.171 -d DB_Test -U test -q "declare @r int select
@r = 1 while @r<500000 begin SELECT * FROM Dzen select @r = @r+1 end"
После ввода пароля начнется цикл чтения таблицы Dzen.
Проверяем на каком севере обрабатывается запрос. Для этого на каждом SQL-сервере
выполним:
SELECT
req.session_id,
req.command,
req.status,
sqltext.TEXT,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests
req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
Видим, что запрос обрабатывается на Read-Only SQL-сервере «ven-tst-sql01».
Далее выполним циклический запрос к CS-серверу на запись от пользователя test:
USE DB_Test;
GO
--Write Script--
declare @id int, @r int
select @r = 1
while @r<500000 begin
UPDATE Dzen
SET test = nickname;
SELECT * FROM Dzen
select @r = @r+1
end
В консоли SQLCMD запрос будет выглядеть так:
sqlcmd -S 192.168.0.171 -d DB_Test -U test -q "declare @id int, @r int
select @r = 1 while @r<500000 begin UPDATE Dzen SET test = nickname; SELECT * FROM Dzen select @r = @r+1 end"
После ввода пароля начнется цикл перезаписи столбца «nickname».
Видим, что запрос обрабатывается SQL-сервере «ven-tst-sql02», имеющем Primary-реплику.
Проверим работу CS-сервера, при переключении Primary-реплики на другой
сервер, например, с сервера «ven-tst-sql02» на «ven-tst-sql06».
Для этого в консоли «SQL Server Management Studio» в разделе
«AlwaysOn High Availabilitu» переходим в «Availability Groups», щелкаем правой
кнопкой мыши по «AOGroupTest». В появившемся контекстном меню выбираем «Failover».
В окне «Perform a planned failover for this availability group» нажимаем «Next».
В окне «Select the new primary replica for this availability group» выделяем
сервер «ven-tst-sql06», нажимаем «Next».
Нажимаем «Connect» и подключаемся к SQL-серверу.
После успешного подключения нажимаем «Next».
В окне «Verify the choices made in this wizard» нажимаем «Finish».
Primary-реплика успешно переключится на сервер «ven-tst-sql06».
Подключаемся к NetScaler’у. Переходим в раздел «Services» и видим, что
сервис «service-sql06» перешел в состояние DOWN. Это говорит о том, что сервер
«ven-tst-sql06» больше не будет участвовать в запросах на чтение.
Проверяем.
В консоли SQLCMD запрос на чтение:
sqlcmd -S 192.168.0.171 -d DB_Test -U test -q "declare @r int select
@r = 1 while @r<500000 begin SELECT * FROM Dzen select @r = @r+1 end"
Видим, что данный запрос обрабатывает сервер «ven-tst-sql02».
Теперь в консоли SQLCMD запрос на запись:
sqlcmd -S 192.168.0.171 -d DB_Test -U test -q "declare @id int, @r int
select @r = 1 while @r<500000 begin UPDATE Dzen SET test = nickname; SELECT * FROM Dzen select @r = @r+1 end"
Видим, что данный запрос обрабатывает сервер «ven-tst-sql06».
На этом предлагаю закончить.
Вопросы, замечания и предложения пишите в комментариях или на почту.
Удачного тестирования!
