اجرای تراکنش های توزیع شده (Distributed Transaction) در SQL SERVER

مقدمه

اجرای تراکنش به صورت فراگیر (توزیع شده) بین چندین Instance دیتابیسی یکی از موارد پر مصرف در سیستم های عملیاتی می‌باشد که روش درست انجام آن و راه حل های قابل استفاده برای انجام چنین کاری کمتر توسط Developer ها و DBA ها شناخته شده است، بنابراین در این مقاله قصد دارم به تشریح یکی از شیوه های اجرای تراکنش های توزیع شده بین چندین Instance دیتابیسی (از هر نوع دیتابیسی) بپردازم.
ابتدا بهتر است مفهوم تراکنش توزیع شده (Distributed Transaction) را کمی موشکافی کنیم. تراکنش توزیع شده (Distributed Transaction)، نوعی از تراکنش دیتابیسی (Database Transaction) است که در آن از دو Instance دیتابیسی مجزا و یا بیستر جهت اجرای کار استفاده می‌شود (به زبان ساده تر تراکنش دیتابیسی که بر روی دو یا چند سرور دیتابیسی بر روی شبکه اجرا می‌گردد)،

این مفهوم از دو کلمه اساسی و پر معنی تشکیل شده است :

1. Distributed: که به معنی توزیع شده / پخش شده می‌باشد و اشاره به توزیع کار بر روی منابع فیزیکی متعدد دارد
2. Transaction یا Database Transaction: به معنی تراکنش. واحدی از کار که می‌بایست، یا به طور کامل و بی نقص اجرا گردد و یا درصورت بروز هرگونه نقصی اثرات نیمه تمام آن کار بلا اثر شود (به عبارتی یا باید همه چیز درست اجرا شود و یا اصلا هیچ اتفاقی نیافتد). همچنین یک تراکنش می‌بایست دارای خواص ACID باشد (جهت کسب اطلاعات بیشتر در خصوص تراکنش و مدیریت تراکنش به مقالات “تراکنش در SQL Server“و”مدیریت تراکنش” مراجعه نمایید)

حال با ترکیب این دوکلمه با یکدیگر به یک نتیجه هیجان انگیز می‌رسیم، Distributed [Database] Transaction: که مشمول مزایا و خواص هر دو مفهوم می‌شود، یعنی اجرای تراکنش بر روی چندین دیتابیس که بر روی سرورهای مجزا قرار دارند و این تراکنش، خواص ACID را نیز پشتیبانی می‌کند، که در نتیجه آن یک کار، یا با موفقیت بر روی کلیه سرورها به سرانجام می‌رسد و Commit می‌شود و یا در صورت بروز هرگونه نقصانی کار بر روی کلیه سرورها Rollback شده و بلا اثر می‌شود.
بدنیست پیش از آنکه نحوه پیاده‌سازی Distributed Transaction ها را بیان کنم، چند نمونه از کاربردهای Distributed Transaction را با هم ببینیم:
مثال اول: شما در سازمانی مشغول به فعالیت هستید که دارای یک دفتر فروش مرکزی و تعداد زیادی شعب فروش است، از شما خواسته شده است که فاکتورهای فروش روزانه هر شعبه را در قالب یک فاکتور فروش تجمیعی در انتهای هر روز به دفتر مرکزی ارسال کنید

 طبیعتا یکی از راه حل ها آن است که در جدول “فاکتور فروش” در دیتابیس دفتر مرکزی، به ازای هر شعبه یک فاکتور فروش ایجاد کنید و سپس به کمک Link Server به سرور های دیتابیس هر یک از شعب وصل شده و از اقلام فاکتور فروش روزانه آن شعبه Query بگیرید و سپس  نتیجه Query را در جدول “اقلام فاکتور فروش” در دیتابیس دفتر مرکزی درج کنید. همچنین جهت مدیریت بهتر رخدادهای و جلوگیری از خواندن تکراری اقلام فاکتور فروش شعب، ترجیح می‌دهید پس از آنکه فاکتور فروش تجمیعی در دیتابیس دفتر مرکزی درج شد، رکوردهای Process شده در شعبه را Flag بزنید تا بعدا اقدام به بازخوانی مجدد آن‌ها نکنید.
طبیعتا ما باید برای انجام چنین کاری از Transaction استفاده کنیم تا درصورت بروز خطا در هریک از مراحل ذکر شده کل کار Cancel شده و بلا اثر گردد، اما نکته مهم آن است که شما بدلیل آنکه در حال استفاده از Linked Server در Transaction خود هستید، بلافاصله پس از اجرای دستورات خود در قالب Transaction با خطا مواجه می‌شوید، دلیل آن این است که SQL Server به طور پیش فرض تراکنش‌ها را به صورت Local مدیریت می‌کند، حال آنکه تراکنش شما بدلیل استفاده از Linked Server دیگر Local نمی‌باشد.

پس باید برای رفع این مشکل یا از Distributed Transaction ها استفاده کرد که قابلیت ساپورت چنین سناریوهایی را دارا می‌باشند، یا باید اصلا استفاده از Transaction را کنار گذاشت، که در اینصورت احتمال بروز خطا در سیستم شما به شدت بالا میرود و می‌تواند دردسرهای مالی زیادی برای سازمان شما به همراه داشته باشد.
مثال دوم: فرض کنید نرم‌افزار فروش شما از دیتابیس SQL Server استفاده می‌کند و شما نرم‌افزار خود را به سازمانی فروخته‌اید که آن سازمان خود دارای یک  نرم‌افزار حسابداری است که از دیتابیس ORACLE استفاده می‌کند.

حال مشتری از شما خواسته است تا به محض آنکه فاکتور فروش در نرم‌افزار شما صادر شد، بلافاصله یک رکورد هم در دیتابیس اوراکلی سیستم حسابداری آن سازمان درج شود و با شما شرط کرده که در صورت عدم امکان درج رکورد در سیستم حسابداری آن‌ها، سیستم فروش شما هم نباید توانایی ثبت فاکتور داشته‌باشد (همان جمله معروف یا همه، یا هیچکس).
طبیعتا باز هم یکی از مطمئن ترین راه‌حل‌ها استفاده از یک Transaction واحد برای درج رکورد در دیتابیس SQL Server و ORACLE است، بدین شیوه یا رکورد در هر دو سیستم به سلامت commit می‌شود و فاکتور صادر می‌شود و یا درصورت بروز هر مشکلی در این روال هیچ رکوردی نه در سیستم فروش و نه در سیستم حسابداری درج نمی‌گردد، با توجه به آنکه در این مثال با دو RDBMS مختلف سر و کار دارید که بر روی سرورهای مجزایی نصب شده‌اند، باز هم نخواهید توانست با استفاده از Transaction‌های عادی این عمل را انجام دهید و راهکار شما باز هم استفاده از Distributed Transaction می‌باشد.
[قطعا متوجه جذابیت Distributed Transaction ها شده‌اید، شما می‌توانید با استفاده از این روش یک Transaction واحد بر روی چندین سرور فیزیکی مستقل با RDBMS‌ها متفاوت بگیرید و همزمان قابلیت ACID را بر روی Transaction خود داشته باشید. حال می‌رسیم به نحوه پیاده سازی Distributed Transaction‌ها در SQL Server.

قدم اول: تنظیم سرویس MSDTC در ویندوز

بر روی کلیه سرورهای دیتابیسی ای که قرار است در تراکنش، مورد استفاده قرار گیرند(همچنین خود سرور اجرا کننده دستور) هر چهار تنظیم زیر را انجام دهید:
۱- در قسمت Run دستور Services.msc را تایپ نموده و اجرا کنید، تا لیست سرویس‌های ویندوز به شما نمایش داده شود.
۲- سرویس Distributed Transaction Coordinator را پیدا کرده، آن را Start نمایید و Startup Type آن را هم بر روی حالت Automatic)Delayed Start) قراردهید
۳-از Control Panel به قسمت Administrative Tools رفته و Component Services را اجرا کنید
۴-درصفحه باز شده (Component Services) به آدرس مقابل بروید: Console Root > Component Services > Computers > My Computer > Distributed Transaction Coordinator و سپس بر روی Local DTC کلیک راست کرده و Properties را انتخاب کنید، حال به برگه Security رفته و آیتم های زیر را انتخاب کنید‌:
Network DTC Access, Allow Remote Clients, Allow Inbound, Allow Outbound, No Authentication Required, Enable SNA LU 6.2 Transactions سپس دکمه OK را زده و موارد را تایید نمایید.

قدم دوم: تنظیم Linked Server‌های مورد استفاده در SQL Server

به برگه Security از Linked Server‌های مورد استفاده خود رفته و گزینه Enable Promotion of Distributed Transactions را فعال کنید

قدم سوم: تنظیم Advanced Options در SQL Server Instance:

sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

قدم چهارم: تنظیم Firewall

درصورت استفاده از فایروال بر روی سرور خود، تنظیمات زیر را بر روی کلیه سرورهای مورد استفاده برای Distributed Transaction اعمال کنید

۱-از قسمت Control Panel برنامه Windows Firewall را باز کنید
۲-از پنل سمت راست گزینه Allow an app or feature through Windows Firewall را انتخاب نموده و از صفحه باز شده گزینه Distributed Transaction Coordinator را پیدا کرده و آن را فعال نمایید. قدم پنجم: اجرای تراکنش به صورت توزیع شده

تا اینجای کار کلیه تنظیمات انجام شده، حال نوبت به آشنایی با نحوه نوشتن یک Distributed Transaction می‌رسد، اما پیش از هر چیز فراموش نکنید که Distributed Transaction‌ها بدلیل سرباری که بابت عملیات Synchronization بین سرورهای مختلف دارند نسبت به Local Transaction ها به طور طبیعی کند‌تر هستند.

SET XACT_ABORT ON
Begin Distributed Transaction T1
...
...
...
Commit Transaction T1
--OR, Rollback Transaction T1
SET XACT_ABORT OFFبه Syntax نوشتاری دستور Begin Transaction توجه کنید! تفاوت بین تراکنش Distributed و تراکنش Local برای T-SQL با لغط کلیدی Distributed بین Begin و Transaction مشخص می‌شود، همچنین بهتر است از دستور SET XACT_ABORT پیش از شروع تراکنش و پس از پایان تراکنش‌های توزیع شده استفاده کنید‌!

در انتها لازم به یادآوری چند نکته می‌باشد:

•  این تنها یکی از روش‌های اجرای تراکنش‌ها به صورت توزیع شده است و راه‌های دیگری نیز وجود دارد.
• تراکنش‌های توزیع شده دارای کندی خاص خود هستند که طبیعی می‌باشد و میزان کندی به عوامل متعددی بستگی دارد، پس بهتر است پیش از آنکه در سایت عملیاتی خود این روش را نهایی کنید، در ابتدا از میزان Performance آن بر روی سرورها و شبکه خود تست گرفته و اطمینان حاصل کنید.

پانوشت : ظاهرا برخی از دوستان در پیاده‌سازی این تکنیک در محیط عملیاتی (به دلیل استفاده از Firewall‌های غیر ویندوزی/ نرم افزاری) با مشکل مواجه شده بودند، زیرا تنظیمات فایروالی که در بالا توضیح داده شده بود برای استفاده تو محیط هایی بود که از Windows Firewall بهره می‌برند، نه از Third-party Firewall ها، بنابراین اون دسته از دوستانی که تنظیمات دقیق فایروال را می‌خواهند بدانند، باید به متن زیر مراجعه کنند و مطابق با اون تنظیمات رو انجام دهند:

A. Make sure your application and database servers can communicate through the firewall over port 135.
B. Make sure both servers can ping each other with NetBIOS or DNS name–STEP 2 (Make sure DTC is configured properly)
A. Check the MS DTC settings (you can get to this from the control panel) on the application server AND the database server(s).

B. The following MS DTC settings should be turned on/checked:
B.1 Allow network access
B.2 Allow remote administration (not required, but advisable for testing/debugging)
B.2.1 This setting should be shut off in Production. Safety first !!!
B.3 Allow inbound connections
B.4 Allow outbound connections

C. Make sure you can telnet from your application server to the database server (and vice versa).
C.1 use the following command: telnet [the name of your server] 135
C.2 If you see a blank screen with a blinking cursor, then telnet worked. Port 135 is open, and your servers can communicate over it
C.3 NOTE: On Windows Server 2008, telnet is not installed by default. You may have to install this on the server.
You can follow this guide to install a telnet client

D. Install and run DTCPing on the application and database servers.
D.1 Use DTCPing to test communication
D.2 If memory serves, there’s a warning about DTCPing not being supported on/by Windows Server 2008
D.3 While troubleshooting this issue, we used DTCPing on at least one Windows 2008 Server and it appeared to work properly.
Your mileage may vary–STEP 3 (Limit the port range DTC can use when communicating via RPC.) DTC is picking a port between 1024 and 65535,
which is blocked by the firewall. Follow the steps below to limit the port range the DTC has to choose from.
WARNING: The steps below involve changes to the server’s registry and restarting the entire machine,
so make sure these steps are conducted during your maintenance window. Work with your SA team and network admin
team to identify an acceptable port range for RPC to use. A.Microsoft recommends: A.1 Opening ports from 5000 and up

A.2 Opening a minimum of 15 – ۲۰ ports
B.Configure the DCOM Port Range restriction on the application server :
B.1 specify the port range (control RPC dynamic port allocation) with
[Update the registry (HKEY_LOCAL_MACHINE\Software\Microsoft\Rpc) to use the ports identified] :
Follow these steps to control RPC dynamic port allocation. You will have to do this on both computers.

Note also that the firewall must be open in both directions for the specified ports:
۱- To start Registry Editor, click Start, click Run, type regedt32, and then click OK.
You must use Regedt32.exe, rather than Regedit.exe, because Regedit.exe does not support the REG_MULTI_SZ data type that is required for the Ports value.

۲- In Registry Editor, click HKEY_LOCAL_MACHINE in the Local Machine window.

۳- Expand the tree by double-clicking the folders named in the following path: HKEY_LOCAL_MACHINE\Software\Microsoft\Rpc

۴- Click the RPC folder, and then click Add Key on the Edit menu.

۵- In the Add Key dialog box, in the Key Name box, type Internet, and then click OK.

۶- Click the Internet folder, and then click Add Value on the Edit menu.

۷- In the Add Value dialog box, in the Value Name box, type Ports.

۸- In the Data Type box, select REG_MULTI_SZ, and then click OK.

۹- In the Multi-String Editor dialog box, in the Data box, specify the port or ports you want RPC to use for dynamic port

allocation, and then click OK. Each string value you type specifies either a single port or an inclusive range of ports.
For example, to open port 5000, specify “۵۰۰۰” without the quotation marks.
To open ports 5000 to 5020 inclusive, specify “۵۰۰۰-۵۰۲۰″ without the quotation marks.
You can specify multiple ports or ports ranges by specifying one port or port range per line.
All ports must be in the range of 1024 to 65535.

If any port is outside this range or if any string is invalid, RPC will treat the entire configuration as invalid.
Microsoft recommends that you open up ports from 5000 and up, and that you open a minimum of 15 to 20 ports.

۱۰- Follow steps 6 through 9 to add another key for Internet, by using the following values: Value:
PortsInternetAvailable Data Type: REG_SZ Data: Y This signifies that the ports listed under the Ports value are to be made Internet-available.

۱۱- Follow steps 6 through 9 to add another key for Internet, by using the following values: Value: UseInternetPorts Data Type: REG_SZ Data: Y
This signifies that RPC should dynamically assign ports from the list of Internet ports.

۱۲- Configure your firewall to allow incoming access to the specified dynamic ports and to port 135 (the RPC Endpoint Mapper port).

۱۳- Restart the computer. When RPC restarts, it will assign incoming ports dynamically, based on the registry values that you have specified.

For example, to open ports 5000 through 5020 inclusive, create the following named
values: Ports : REG_MULTI-SZ : 5000-5020 PortsInternetAvailable : REG_SZ : Y UseInternetPorts : REG_SZ : Y DTC also
requires that you are able to resolve computer names by way of NetBIOS or DNS.
You can test whether or not NetBIOS can resolve the names by using ping and the server name.
The client computer must be able to resolve the name of the server, and the server must be be able to resolve
the name of the client. If NetBIOS cannot resolve the names, you can add entries to the LMHOSTS files on the computers.

C.Configure the DCOM Port Range restriction on the database server
C.1 Update the registry (HKEY_LOCAL_MACHINE\Software\Microsoft\Rpc) to use the ports identified (Kije Step B.1).
D.Restart the application server
E.Restart database server(s)
F.Open up the same range of ports (i.e. the range identified in Step 3 Section A) on the firewall bi-directionally