بررسی اجمالی
SQL Server پس از راه‌اندازی قابلیت Always On در نسخه‌های2012 و 2014 درخواست‌های فقط خواندنی را به اولین سرور فعال هدایت می‌کند. هر زمان که سرور اول در دسترس نباشد آن را به سرور بعدی در لیست سرورهای خود هدایت می‌کنند. همچنین اگر شما چندین سرور ثانویه آماده برای خواندن داشته باشید، این امکان وجود ندارد که درخواست‌ها را بین آن‌ها تقسیم نمایید. در حالیکه شما در SQL Server 2016 می‌توانید Load-Balancing را بین مجموعه ای از سرورهای ثانویه فقط خواندنی تقسیم نمایید.
مزایا
این قابلیت مزایای زیر را به همراه دارد:
1- بکارگیری پیکربندی پیشرفته برای ‘Bundle’ Read-Only Routed Connections به سرور مشخص
2- قابلیت تقسیم کاری درخواست‌های فقط خواندنی در بین مجموعه‌ای از سرورهای ثانویه فقط خواندنی
محیط تست
برای نشان دادن پیاده‌سازی پیکربندی‌ها و تست آن، محیط عملیاتی شامل 3 سرور، SQL16N1، SQL16N2 و SQL16N3 را در نظر بگیرید که با یکدیگر کلاستر شده‌اند و تنظیمات SQL Server AlwaysOn بر روی آن‌ها فعال شده است.
1- پیکربندی یک Availibility Group برای Load Balancing Read-Only Routing
در ابتدا، شما باید هر یک از سرورها را برای دادن مجوز خواندن در حالتیکه سرور ثانویه است، پیکربندی نمایید و سپس Connection String را برای Replica تعریف نمایید.
2- پیکربندی دسترسی فقط خواندنی
با فعال‌سازی دسترسی خواندن این امکان فراهم می‌شود که سرورهایی که در حالت ثانویه هستند برای درخواست‌های خواندن در دسترس باشند. برای فعال‌سازی آن می‌توانید از دستورات زیر استفاده نمایید.
[sql]
ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N’SQL16N2′ WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO
<p style="text-align: right;">ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N’SQL16N3′ WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO
[/sql]
2- پیکربندی URL مسیردهی فقط خواندنی
برای هر یک از سرورهای ثانویه (Secondary Replica) یک آدرس برای متصل شدن مشخص نمایید.
[sql]
ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N’SQL16N2′ WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO
ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N’SQL16N3′ WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO
[/sql]
3- SQL Server 2016 لیست Load Balancing را برای مسیردهی سرورهای فقط خواندنی را معرفی کرده است.
یک لیست مسیردهی Read-Only برای SQL16N1 زمانیکه نقش Primary دارد ایجاد نمایید. به مثال زیر توجه نمایید:
[sql]
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N’SQL16N1′ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((‘SQL16N3’, ‘SQL16N2’)
, ‘SQL16N1’)));
[/sql]
این لیست مسیردهی Load Balancing Read-Only را بین سرورهای SQL16N2 و SQL16N3 انجام می دهد. در مثال بالا دو لیست مسیردهی به شرح ذیل ایجاد شده است:
List 1: ‘SQL16N3’, ‘SQL16N2’
List 2: ‘SQL16N1’
بررسی رفتار مسیردهی
1- مسیردهی و تقسیم کار به سرورهای مشخص شده در لیست اول
حالتی را در نظر بگیرید که تمامی سرورها در حال کار هستند و سرورهای SQL16N2 و SQL16N3 برای حالت فقط خواندنی در دسترس می‌باشند. اولین درخواست فقط خواندن به SQL16N3 هدایت می‌شود، دومین درخواست فقط خواندن به SQL16N2 هدایت می‌شود، سومین درخواست فقط خواندن به SQL16N3 هدایت می‌شود، چهارمین درخواست فقط خواندن به SQL16N2 هدایت می‌شود و این تقسیم کار به همین ترتیب با استفاده از الگوریتم Round-Robin بین دو سرور فقط خواندنی لیست اول ادامه پیدا خواهد کرد.
2- اگر هر یک از سرورهای ثانویه از دسترس خارج شوند، مسیردهی و تقسیم کار در بین سرورهای ثانویه باقیمانده ادامه پیدا خواهد کرد.
اگر سرور SQL16N2 یا SQL16N3 از دسترس خارج شود، سپس تمام درخواست‌های فقط خواندن به سرور ثانویه باقیمانده در لیست اول هدایت می‌شوند. برای مثال اگر سرور SQL16N3 در حالت Not Synchronized قرار گیرد و یا مقدار تنظیم ALLOW_CONNECTIONS آن برابر NO شود، تمام درخواست‌های فقط خواندن به سرور SQL16N2 هدایت می‌شود. به عبارتی تا زمانیکه یکی از سرورهای لیست اول برای درخواست‌های فقط خواندن در دسترس باشد، هیچ درخواست فقط خواندنی به سرورهای SQL16N1 هدایت نمی‌شود.
3- اگر تمامی سرورهای ثانویه لیست اول از دسترس خارج شوند، درخواست ها به سرورهای مشخص شده در لیست بعدی هدایت می شوند
اگر سرور SQL16N2 و SQL16N3 هر دو برای درخواست‌های فقط خواندن از دسترس خارج شوند، تمام درخواست‌های فقط خواندن به سرورهای لیست دوم هدایت می‌شود که در مثال ما سرور SQL16N1 است.
4- بازگشت به لیست اول اگر هر یک از سرورهای ثانویه لیست اول در دسترس قرار گیرند
از انجایی که سرورهای ثانویه لیست شده در لیست اول اولویت پاسخگویی به درخواست‌های فقط خواندن را دارند، به محض اینکه در دسترس قرار گیرند، درخواست‌های فقط خواندن بعدی به سمت آن‌ها هدایت می‌شوند.
تست مسیردهی درخواست‌های فقط خواندنی
SQLCMD یک ابزار مناسب برای تست مسیردهی درخواست‌های فقط خواندن است و می‌تواند از سرور اولیه مورد استفاده قرار گیرد. در اینجا بر اساس مثال نشان داده شده در بالا لیست مسیردهی‌ها تنظیم شده است. در این تست دو مرتبه با استفاده از SQLCMD متصل شده ام و متوجه شدم که نتیجه اولین اجرا از طریق سرور SQL16N3 و دومین اتصال از طریق سرور SQL16N3 پاسخ داده شده است، همانطور که در لیست مسیردهی ام مشخص شده است.
خطایابی مسیردهی فقط خواندنی
دستورات مفید برای رفع خرابی مسیردهی‌های فقط خواندنی
شما می توانید دستورات زیر را بر روی سرور اولیه برای مشاهده اطلاعات دسترس پذیری سرورها اجرا نمایید. این دسترس پذیری‌ها و وضعیت آن ها می‌تواند بر روی مسیردهی درخواست‌های فقط خواندن تاثیر داشته باشد.
[sql]
select ar.replica_server_name, ar.endpoint_url, ar.read_only_routing_url
, secondary_role_allow_connections_desc, ars.synchronization_health_desc
from sys.availability_replicas ar join sys.dm_hadr_availability_replica_states ars on ar.replica_id=ars.replica_id
[/sql]
مشاهده لیست اولویت بندی مسیردهی
[sql]
select ar.replica_server_name,arl.routing_priority,
ar2.replica_server_name,ar2.read_only_routing_url
from sys.availability_read_only_routing_lists arl join sys.availability_replicas ar
on (arl.replica_id = ar.replica_id) join sys.availability_replicas ar2
on (arl.read_only_replica_id = ar2.replica_id)
order by ar.replica_server_name asc, arl.routing_priority asc
[/sql]
لیست مسیردهی گروه دسترسی AR به شرح ذیل تنظیم شده است.
[sql]
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N’SQL16N1′ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SQL16N3’
, ‘SQL16N2’
, ‘SQL16N1’)));
[/sql]
دستور اولویت‌بندی سرورها را اجرا نمایید. با استفاده از آن، لیست مشخص و واضحی از اولویت‌بندی سرورها تشکیل می‌شود.
حال شما می‌توانید با استفاده از دستور زیر لیست اولویت مسیردهی را بین سرورهای SQL16N2 و SQL16N3 تنظیم نمایید:
[sql]
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N’SQL16N1′ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((‘SQL16N3’, ‘SQL16N2’), ‘SQL16N1’)));
[/sql]
بررسی چند سوال
سوال اول : برنامه ام را تنظیم کرده‌ام که به لیست سرورهای ثانویه فقط خواندنی متصل شود اما به سرور اولیه (Primary Replica) متصل می‌شود.
دلیل 1: برنامه شما باید از نام بانک اطلاعاتی که در Availibility Group می‌باشد، استفاده نماید. برای مثال می‌خواهید با استفاده از ابزار SQLCMD برای متصل شدن به دیتابیسی استفاده نمایید و تمامی پارامترهای اتصال را تنظیم کرده باشیم اما از نام بانک اطلاعاتی که در Availibility Group نیست استفاده شود، در اتصال به سرورهای فقط خواندنی دچار مشکل می‌شود و به سرور اولیه متصل می‌شود. همانطور که در تصویر زیر نشان داده شده است در SQLCMD پارامترهای مسیردهی فقط خواندنی را پاس می‌دهد اما نام بانک اطلاعاتی موجود در Availibility تنظیم نشده است. در نتیجه اتصال به سرور اولیه به جای سرور ثانویه فقط خواندنی برقرار خواهد شد.
دلیل 2: اگر شما سرور اولیه را در لیست مسیردهی فقط خواندنی تعریف کرده باشید و سرورهای ثانویه تعریف شده در لیست مسیردهی در دسترس نباشند (برای مثال وضعیت یکسان سازی آن‌ها در حالت Suspend باشد)، سپس مسیر اتصال برنامه‌های کاربردی به سرور اولیه هدایت می‌شود. برای مثال، سرور اولیه SQL16N1 است و ما لیست مسیردهی را همانند لیست زیر تنظیم می‌کنیم:
[sql]
/*Configure a routing list. If SQL16N3 is not available read-only connections connect to SQL16N2*/
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N’SQL16N1′ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SQL16N3’, ‘SQL16N2’
, ‘SQL16N1’)));
[/sql]
سوال دوم : لیست مسیردهی سرورهای ثانویه فقط خواندنی را تنظیم کرده ام اما به یک سروری که انتظارش را ندارم متصل می‌شود.
دلیل: سرور ثانویه که شما پیش بینی کرده‌اید که به آن متصل شوید در دسترس نمی‌باشد که دلایل آن می تواند به شرح ذیل باشد:
1- سرور ثانویه برای دسترسی خواندن تنظیم نشده است. شما باید سرور ثانویه را همانطور که در بالا توضیح داده شده است، برای دسترسی خواندن تنظیم نمایید.
2- وضعیت سرور ثانویه در حالت Suspended قرار گرفته است. اگر وضعیت سرور ثانویه در حالت Synchronizing یا Synchronized نباشد، شما نمی‌توانید بصورت فقط خواندنی به آن متصل شوید.
3- سرور میزبان SQL Server سرور ثانویه فعال نمی‌باشد.
4- URL مربوط به مسیردهی فقط خواندنی بدرستی تعریف نشده است.
شما می توانید پرس و جوی زیر را بر روی سرور اولیه اجرا نمایید. نتیجه آن می‌تواند به شما برای پیدا کردن دلیل مشکل، کمک کننده باشد:
[sql]
select ar.replica_server_name, ar.endpoint_url, ar.read_only_routing_url,
secondary_role_allow_connections_desc, ars.synchronization_health_desc
from sys.availability_replicas ar join sys.dm_hadr_availability_replica_states ars on ar.replica_id=ars.replica_id
[/sql]
برای مثال به تصویر زیر که نتیجه اجرای دستور بالا در محیط تست می‌باشد دقت نمایید، سرورهای SQL16N2 و SQL16N3 برای حالت Read-only پیکربندی نشده‌اند. همچنین SQL16N2 در وضعیت HEALTHY نمی‌باشد، که ممکن است Suspend شده باشد و یا اینکه سرور میزبان آن خاموش شده باشد و در آخر این نکته که URL مسیردهی فقط خواندنی سرور SQL16N1 خطا دارد.
جهت کسب اطلاعات بیشتر می توانید به آدرس زیر مراجعه نمایید:
Configure Read-Only Routing for an Availability Group (SQL Server)
جهت کسب اطلاعات بیشتر می‌توانید به دوره آموزش High Availability در ۲۰۱۷ SQL Server مراجعه کنید.
