SQL Server 2008: Change Data Capture (CDC)

 

Another one of the new features within SQL Server 2008 is the Change Data Capture (CDC).  CDC is designed to capture insert, update and delete activity on a SQL table and place the information into a separate relational table.  It uses an asynchronous capture mechanism that reads the transaction logs and populates the CDC table with the row's data which change.  The CDC table mirrors the column structure of the tracked table, together with metadata regarding the change.  In later releases, there is also going to be a audit feature allowing you to audit who accesses what information and when, based more on select statements.

 

To enable CDC, the following two statements are required. Note, SQLServerAgent must be running for this to work.

 

use testDatabase

--Activate CDC
EXEC sys.sp_cdc_enable_db_change_data_capture

--IsDatabaseEnabled?
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'testDatabase'

--Enable CDC on table
EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema = 'dbo', @source_name = 'Table_1', @role_name = 'cdc_test'

--IsTableEnabled?
SELECT is_tracked_by_cdc FROM sys.tables WHERE name = 'table_1'

 

The sp_cdc_enable_table... statement enables the tracking of the changes for a table, parameters are required for setting the source schema, the source name which is the table you want to track, and the role name, if the role does not exist it will create it for you.

 

After executing these statements you will have a number of new tables within your database.  The tables are:

 

·         cdc.captured_columns - Information about the captured columns being tracked.

·         cdc.change_tables - Tables being tracked.

·         cdc.ddl_history - Changes to the tracked table, details the command issued on the table.

·         cdc.index_columns - Guessing this is tracked indexed columns.

·         cdc.lsn_time_mapping - When a transaction starts and ends. Relates to rows in the tracked tables.

 

There is also a table for each table which is tracked, in my case I have just the one - cdc.dbo_Table_1_CT.  If we execute a change to the table, such as inserting a new record.

 

INSERT INTO Table_1 VALUES (1, 'First Test')

 

A new row is added to the table, with a record also being inserted into lsn_time_mapping.

__$start_lsn           __$end_lsn             __$seqval              __$operation __$update_mask id          TestMessage
0x0000001E0000006B001A NULL                   0x0000001E0000006B0018 2            0x03       1           First Test

 

If we update the table again using

 

UPDATE Table_1 SET TestMessage = 'Updated First Test' WHERE id = 1

 

Two records are inserted into the tracked table. One with the TestMessage 'First Test' (the original message) and the other with 'Updated First Test' both relating to a single transaction in the lsn_time_mapping table. 

 

If we do

 

DELETE FROM Table_1 WHERE id = 1

 

We have a single row inserted into dbo_Table_1_CT saying which row was deleted.

 

Within the dbo_Table_1_CT there is a column __$operation.  This gives you the ID relating to the operation executed on the row.  2 = Insert. 3 = Before Update. 4 = Post Update. 1 = Delete.

If we change the design of the table while it is being tracked, a row is inserted into cdc.ddl_history with the command executed on the table.

 

source_object_id object_id   required_column_update ddl_command                    
---------------- ----------- ---------------------- --------------------------------
1077578877       1093578934  0                      ALTER TABLE dbo.table_1 ADD Status nvarchar(50) NULL

 

However, if we execute another insert command on the table, the new column does not appear in the result set/dbo_table_1_ct.  It would be nice to keep the two insync and not have to worry about updating the table if it is being tracked. To get the tables back insync, I had to disable it (capture_instance parameter relates to the columns being captured I think) and re-enable the tracking. 

 

The commands to do this are:

EXEC sys.sp_cdc_disable_table_change_data_capture @source_schema = 'dbo', @source_name = 'Table_1', @capture_instance = 'all'
EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema = 'dbo', @source_name = 'Table_1', @role_name = 'cdc_test'

 

In case you are worried about this using up too much space, there is a sys.sp_cdc_cleanup_change_table stored procedure which can be used to remove entries up to a point in time.  You could then link this to a Job to archive the information, or simply report and remove.

1. What are the features and benefits of Quick Test Pro(QTP)?

• Key word driven testing
• Suitable for both client server and web based application
• VB script as the script language
• Better error handling mechanism
• Excellent data driven testing features
  

2. How to handle the exceptions using recovery scenario manager in QTP?
You can instruct QTP to recover unexpected events or errors that occurred in your testing environment during test run. Recovery scenario manager provides a wizard that guides you through the defining recovery scenario. Recovery scenario has three steps1. Triggered Events2. Recovery steps3. Post Recovery Test-Run

3. What is the use of Text output value in QTP?
Output values enable to view the values that the application talks during run time. When parameterized, the values change for each iteration. Thus by creating output values, we can capture the values that the application takes for each run and output them to the data table.

4. How to use the Object spy in QTP 8.0 version?
There are two ways to Spy the objects in QTP1) Thru file toolbar: In the File ToolBar click on the last toolbar button (an icon showing a person with hat).2) Thru Object repository Dialog: In Objectrepository dialog click on the button “object spy…” In the Object spy Dialog click on the button showing hand symbol. The pointer now changes in to a hand symbol and we have to point out the object to spy the state of the object. If at all the object is not visible or window is minimized then hold the Ctrl button and activate the required window to and release the Ctrl button.

5. What is the file extension of the code file and object repository file in QTP?
File extension ofPer test object rep: filename.mtrShared Object rep: filename.tsrCode file extension id: script.mts

6. Explain the concept of object repository and how QTP recognizes objects?
Object Repository: displays a tree of all objects in the current component or in the current action or entire test( depending on the object repository mode you selected).we can view or modify the test object description of any test object in the repository or to add new objects to the repository.Quicktest learns the default property values and determines in which test object class it fits. If it is not enough it adds assistive properties, one by one to the description until it has compiled the unique description. If no assistive properties are available, then it adds a special Ordianl identifier such as objects location on the page or in the source code.

7. What are the properties you would use for identifying a browser and page when using descriptive programming?
“name” would be another property apart from “title” that we can use. ORWe can also use the property “micClass”.ex: Browser(”micClass:=browser”).page(”micClass:=page”)

8. What are the different scripting languages you could use when working with QTP?
You can write scripts using following languages:Visual Basic (VB), XML, JavaScript, Java, HTML

9. Tell some commonly used Excel VBA functions.
Common functions are:Coloring the cell, Auto fit cell, setting navigation from link in one cell to other saving

10. Explain the keyword createobject with an example.
Creates and returns a reference to an Automation objectsyntax: CreateObject(servername.typename [, location])Argumentsservername:Required. The name of the application providing the object.typename : Required. The type or class of the object to create.location : Optional. The name of the network server where the object is to be created.

11. Explain in brief about the QTP Automation Object Model.
Essentially all configuration and run functionality provided via the QuickTest interface is in some way represented in the QuickTest automation object model via objects, methods, and properties. Although a one-on-one comparison cannot always be made, most dialog boxes in QuickTest have a corresponding automation object, most options in dialog boxes can be set and/or retrieved using the corresponding object property, and most menu commands and other operations have corresponding automation methods. You can use the objects, methods, and properties exposed by the QuickTest automation object model, along with standard programming elements such as loops and conditional statements to design your program.

12. How to handle dynamic objects in QTP?
QTP has a unique feature called Smart Object Identification/recognition. QTP generally identifies an object by matching its test object and run time object properties. QTP may fail to recognize the dynamic objects whose properties change during run time. Hence it has an option of enabling Smart Identification, wherein it can identify the objects even if their properties changes during run time.Check out this: If QuickTest is unable to find any object that matches the recorded object description, or if it finds more than one object that fits the description, then QuickTest ignores the recorded description, and uses the Smart Identification mechanism to try to identify the object.While the Smart Identification mechanism is more complex, it is more flexible, and thus, if configured logically, a Smart Identification definition can probably help QuickTest identify an object, if it is present, even when the recorded description fails.The Smart Identification mechanism uses two types of properties: Base filter properties - The most fundamental properties of a particular test object class; those whose values cannot be changed without changing the essence of the original object. For example, if a Web link’s tag was changed from to any other value, you could no longer call it the same object. Optional filter properties - Other properties that can help identify objects of a particular class as they are unlikely to change on a regular basis, but which can be ignored if they are no longer applicable.

13. What is a Run-Time Data Table? Where can I find and view this table?
In QTP, there is data table used, which is used at runtime.-In QTP, select the option View->Data table.-This is basically an excel file, which is stored in the folder of the test created, its name is Default.xls by default.

14. How does Parameterization and Data-Driving relate to each other in QTP?
To data driven we have to parameterize. i.e. we have to make the constant value as parameter, so that in each interaction(cycle) it takes a value that is supplied in run-time data table. Through parameterization only we can drive a transaction (action) with different sets of data. You know running the script with the same set of data several times is not suggested, and it’s also of no use.

15. What is the difference between Call to Action and Copy Action.?
Call to Action: The changes made in Call to Action, will be reflected in the original action (from where the script is called). But where as in Copy Action , the changes made in the script ,will not effect the original script(Action)

16. Explain the concept of how QTP identifies object.
During recording qtp looks at the object and stores it as test object. For each test object QT learns a set of default properties called mandatory properties, and look at the rest of the objects to check whether this properties are enough to uniquely identify the object. During test run, QTP searches for the run time objects that matches with the test object it learned while recording.

17. Differentiate the two Object Repository Types of QTP.
Object repository is used to store all the objects in the application being tested.Types of object repository: Per action and shared repository.In shared repository only one centralized repository for all the tests. where as in per action for each test a separate per action repository is created.

18. What the differences are and best practical application of Object Repository?
Per Action: For Each Action, one Object Repository is created.Shared: One Object Repository is used by entire application

19. Explain what the difference between Shared Repository and Per Action Repository?
Shared Repository: Entire application uses one Object Repository , that similar to Global GUI Map file in WinRunner.

Per Action: For each Action, one Object Repository is created, like GUI map file per test in WinRunner

20. Have you ever written a compiled module? If yes tell me about some of the functions that you wrote.
Sample answer (You can tell about modules you worked on. If your answer is Yes then You should expect more questions and should be able to explain those modules in later questions): I Used the functions for Capturing the dynamic data during runtime. Function used for Capturing Desktop, browser and pages.

21. Can you do more than just capture and playback?
Sample answer (Say Yes only if you worked on): I have done Dynamically capturing the objects during runtime in which no recording, no playback and no use of repository is done AT ALL.-It was done by the windows scripting using the DOM(Document Object Model) of the windows.

22. How to do the scripting. Are there any inbuilt functions in QTP? What is the difference between them? How to handle script issues?
Yes, there’s an in-built functionality called “Step Generator” in Insert->Step->Step Generator -F7, which will generate the scripts as you enter the appropriate steps.

23. What is the difference between check point and output value?
An output value is a value captured during the test run and entered in the run-time but to a specified location.EX:-Location in Data Table[Global sheet / local sheet]

24. How many types of Actions are there in QTP?
There are three kinds of actions:Non-reusable action - An action that can be called only in the test with which it is stored, and can be called only once.Reusable action - An action that can be called multiple times by the test with which it is stored (the local test) as well as by other tests.External action - A reusable action stored with another test. External actions are read-only in the calling test, but you can choose to use a local, editable copy of the Data Table information for the external action.

25. I want to open a Notepad window without recording a test and I do not want to use System utility Run command as well. How do I do this?
You can still make the notepad open without using the record or System utility script, just by mentioning the path of the notepad “( i.e. where the notepad.exe is stored in the system) in the “Windows Applications Tab” of the “Record and Run Settings window.