SQL Server 2008 में ट्रैक परिवर्तन

मुझे लगता है कि हर DBMS डेवलपर जल्द या बाद में सामान्य रूप से डेटाबेस एक्सेस और सर्वर घटनाओं पर नज़र रखने के कार्य का सामना करता है। और एक उपकरण चुनने से पहले (या इसे स्वयं लिखना), ज़ाहिर है, आपको उन समाधानों पर ध्यान देना चाहिए जो डीबीएमएस डेवलपर्स स्वयं प्रदान करते हैं। मैं SQL Server 2008 के लिए इस समस्या को हल करने में अपना अनुभव साझा करना चाहता हूं।

SQL Server 2008 के लिए वर्तमान में 4 ऐसे समाधान हैं (SQL Server 2011 के लिए इस क्षेत्र में कोई महत्वपूर्ण परिवर्तन अपेक्षित नहीं हैं)। इनमें से कुछ उपकरण पहले के संस्करणों से आए थे, कुछ 2008 में दिखाई दिए। ये उपकरण कई तरीकों से ओवरलैप करते हैं, इसलिए कभी-कभी किसी विशिष्ट समस्या को हल करने के लिए एक (या अधिक) उपकरण चुनना आसान नहीं होता है। मैं उदाहरण के साथ प्रत्येक उपकरण का एक संक्षिप्त अवलोकन आयोजित करके इसके साथ मदद करने की कोशिश करूंगा।

1. सीटी (ट्रैकिंग बदलें)।

अक्सर सीडीसी (डेटा कैप्चर बदलें) के साथ भ्रमित। लेकिन ये उपकरण उद्देश्य और कार्यान्वयन दोनों में भिन्न हैं। सीटी को परिवर्तनों के तथ्यों को ट्रैक करने के लिए डिज़ाइन किया गया है (किन लाइनों में, कौन सा डेटा बदल गया था (सी आर यूडी)), जबकि सीडीसी परिवर्तनों के इतिहास (लाइनों के सभी संस्करण, जिनमें हटाए गए थे सहित) को संग्रहीत करता है। कार्यान्वयन के लिए, सीडीसी लेन-देन लॉग (एसिंक्रोनस) को पढ़ने पर आधारित है, जबकि सीटी तुल्यकालिक रूप से काम करता है।
प्रत्येक तालिका जिसके लिए परिवर्तन ट्रैकिंग सक्षम है, एक सिस्टम तालिका बनाई जाती है जिसमें परिवर्तित पंक्ति की आईडी संग्रहीत होती है, परिवर्तित स्तंभों की पहचान के लिए थोड़ा सा मुखौटा, ऑपरेशन का प्रकार।
CT को सक्षम करने के लिए, आपको इसे डेटाबेस स्तर पर और एक विशिष्ट तालिका के लिए सक्रिय करना होगा:

ALTER DATABASE ChangeTracking SET change_tracking = ON <br/>
( change_retention = 10 minutes, auto_cleanup = ON ) <br/>
<br/>
ALTER TABLE Orders enable change_tracking WITH ( track_columns_updated = ON )


ALTER DATABASE ChangeTracking SET change_tracking = ON <br/>
( change_retention = 10 minutes, auto_cleanup = ON ) <br/>
<br/>
ALTER TABLE Orders enable change_tracking WITH ( track_columns_updated = ON )

एक उत्कृष्ट लेख में अधिक विवरण (मापदंडों का वर्णन, उपयोग के उदाहरण और विस्तृत जानकारी)।

2. सीडीसी (डेटा कैप्चर बदलें)

ट्रैकिंग डेटा के लिए एक उपकरण। सीटी से मुख्य अंतर अतुल्यकालिक कार्यान्वयन (जैसा कि ऊपर वर्णित है) और बदले गए (सी आर यूडी) डेटा के सभी संस्करणों का भंडारण है। सीडीसी परिवर्तित डेटा को स्टोर करने के लिए सीडीसी स्कीमा में सिस्टम टेबल का उपयोग करता है। प्रत्येक तालिका जिसके लिए सीडीसी सक्रिय है, एक तालिका प्रकार cdc.dbo_Orders_CT (तालिका dbo.Orders के लिए) के नाम से बनाई गई है।

• * _lsn - (लॉग अनुक्रम संख्या) - लॉग में एक प्रकार का लेनदेन पहचानकर्ता।
  $ ऑपरेशन - ऑपरेशन का प्रकार (1 - डिलीट, 2 - इंसर्ट, 3 - अपडेट (अपडेट करने से पहले लाइन का संस्करण), 4 - अपडेट (अपडेट करने के बाद लाइन का संस्करण)।
• $ update_mask एक बिटमास्क है जो पंक्ति में परिवर्तित कोशिकाओं को इंगित करता है।
• आईडी और दाईं ओर सभी अन्य क्रमशः कोशिकाएं हैं। तालिका की संरचना जिसके परिवर्तन ट्रैक किए गए हैं।

सीडीसी को सक्रिय करने के लिए, आपको एक विशिष्ट तालिका के लिए डेटाबेस स्तर पर इसे सक्रिय करने की आवश्यकता है:

EXEC sys. sp_cdc_enable_db <br/>
<br/>
EXEC sys. sp_cdc_enable_table <br/>
@source_schema = N 'dbo' ,<br/>
@source_name = N 'Orders' ,<br/>
@role_name = N 'cdc' ,<br/>
@capture_instance = N 'dbo_Orders' ,<br/>
@supports_net_changes = 1 ,<br/>
@index_name = 'id_idx' ,<br/>
@captured_column_list = null,<br/>
@ FILEGROUP_NAME = null;


EXEC sys. sp_cdc_enable_db <br/>
<br/>
EXEC sys. sp_cdc_enable_table <br/>
@source_schema = N 'dbo' ,<br/>
@source_name = N 'Orders' ,<br/>
@role_name = N 'cdc' ,<br/>
@capture_instance = N 'dbo_Orders' ,<br/>
@supports_net_changes = 1 ,<br/>
@index_name = 'id_idx' ,<br/>
@captured_column_list = null,<br/>
@ FILEGROUP_NAME = null;


• @source_schema - वह योजना जिसके लिए तालिका संबंधित है, जिसके लिए हम सीडीसी को सक्रिय करते हैं
• @ source_name - तालिका का नाम जिसके लिए हम सीडीसी को सक्रिय करते हैं
• @role_name - भूमिका का नाम जिसमें परिवर्तन देखने का अधिकार होगा (यदि नहीं, तो यह स्वचालित रूप से बनाया गया है)
• @capture_instance - उस नाम के भाग से मेल खाता है जिसे संबंधित सिस्टम टेबल में प्रदर्शित किया जाएगा
• @supports_net_changes - परिवर्तनों के परिणाम सेट को प्रदर्शित करने की क्षमता के लिए समर्थन (केवल डेटा के नवीनतम संस्करण)। इसके लिए एक यूनिक इंडेक्स की जरूरत होती है
• @index_name - वास्तव में, अद्वितीय सूचकांक का नाम
• @captured_column_list - उन फ़ील्ड्स की एक सूची, जिनके लिए परिवर्तन ट्रैकिंग सक्रिय की जाएगी। डिफ़ॉल्ट रूप से, यह बात है।
• @filegroup_name - फ़ाइल समूह जिसमें सिस्टम टेबल रखा जाएगा

विशुद्ध रूप से व्यावहारिक दृष्टिकोण से, सीडीसी का महत्वपूर्ण ऋण यह है कि परिवर्तनों के लेखक को ठीक करना असंभव है। बेशक, कोई भी डिफ़ॉल्ट मान suser_sname () (मेरे काम में, यह काम करता है) के साथ cdc.dbo_Orders_CT सिस्टम तालिका में एक स्तंभ जोड़ने के लिए परेशान नहीं करता है, लेकिन एक गलती-सहिष्णु प्रणाली बनाने के लिए सिस्टम तालिकाओं के साथ इस तरह का हेरफेर सबसे अच्छा तरीका नहीं है।

संग्रहीत डेटा के लिए प्रश्नों के उदाहरण और लेख में एक विस्तृत विवरण।

3. SQL सर्वर ऑडिट

सर्वर के लिए सभी घटनाओं और अनुरोधों (चयन सहित) को ट्रैक करने के लिए डिज़ाइन किया गया एक शक्तिशाली उपकरण। इस उपकरण का दायरा काफी विस्तृत है - प्रोफाइलिंग से लेकर सुरक्षा से संबंधित मुद्दों और डेटाबेस के गैर-इच्छित भाग में उपयोगकर्ता गतिविधि की पहचान करना।
SQL सर्वर ऑडिट आपको मॉनिटर की गई घटनाओं के लिए लचीले ढंग से फ़िल्टर कॉन्फ़िगर करने की अनुमति देता है।
ऑडिट का उपयोग करने के लिए, आपको इसे सर्वर स्तर पर सक्रिय करने की आवश्यकता है:

CREATE server audit ServerAudit<br/>
TO FILE ( filepath = `D:\Audit\`, maxsize = 1GB ) <br/>
WITH ( on_failture = CONTINUE ) <br/>
<br/>
ALTER server audit ServerAudit WITH ( STATE = ON )


CREATE server audit ServerAudit<br/>
TO FILE ( filepath = `D:\Audit\`, maxsize = 1GB ) <br/>
WITH ( on_failture = CONTINUE ) <br/>
<br/>
ALTER server audit ServerAudit WITH ( STATE = ON )

सर्वर स्तर पर ऑडिट विनिर्देश (ट्रेस) बनाने का एक उदाहरण:

CREATE server audit specification ServerAudit_Permissions<br/>
FOR server audit ServerAudit<br/>
ADD ( server_principal_change_group ) ,<br/>
ADD ( server_permission_change_group ) ,<br/>
ADD ( server_role_member_change_group ) ;<br/>
<br/>
ALTER server audit specification ServerAudit_Permissions<br/>
WITH ( STATE = ON ) ;


CREATE server audit specification ServerAudit_Permissions<br/>
FOR server audit ServerAudit<br/>
ADD ( server_principal_change_group ) ,<br/>
ADD ( server_permission_change_group ) ,<br/>
ADD ( server_role_member_change_group ) ;<br/>
<br/>
ALTER server audit specification ServerAudit_Permissions<br/>
WITH ( STATE = ON ) ;

डेटाबेस स्तर पर एक ऑडिट विनिर्देश बनाने का एक उदाहरण:

USE MyDb<br/>
CREATE DATABASE audit specification SA_MyDb_Orders <br/>
FOR server audit ServerAudit<br/>
ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. Orders BY PUBLIC ) ,<br/>
ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. OrderDetails BY PUBLIC )


USE MyDb<br/>
CREATE DATABASE audit specification SA_MyDb_Orders <br/>
FOR server audit ServerAudit<br/>
ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. Orders BY PUBLIC ) ,<br/>
ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. OrderDetails BY PUBLIC )

ऑडिट को ट्यून करने के लिए, SQL Server मैनेजमेंट स्टूडियो में एक सुविधाजनक विज़ुअल इंटरफ़ेस है।

इसके अलावा, यह ध्यान दिया जाना चाहिए कि विनिर्देश सी 2 (यूएस राज्य मानक के मानकीकृत ऑडिट के साधन हैं, एमएसडीएन के अनुसार, मुझे मानक का लिंक नहीं मिला), जिसके सक्रियण के लिए आपको यह करना चाहिए:

SP_CONFIGURE 'show advanced options' , 1 ;<br/>
RECONFIGURE ;<br/>
<br/>
SP_CONFIGURE 'c2 audit mode' , 1 ;<br/>
RECONFIGURE ;


SP_CONFIGURE 'show advanced options' , 1 ;<br/>
RECONFIGURE ;<br/>
<br/>
SP_CONFIGURE 'c2 audit mode' , 1 ;<br/>
RECONFIGURE ;


4. SQL सर्वर प्रोफाइलर

उपयोगिता लंबे समय से सभी के लिए परिचित है, इसलिए अब मैं उस पर ध्यान नहीं दूंगा।

आपका धन्यवाद
प्रत्येक साधन के बारे में विस्तार से लिखने की योजना है, जब तक कि विषय रुचि का न हो।