Home
%3CLINGO-SUB%20id%3D%22lingo-sub-383456%22%20slang%3D%22en-US%22%3EReal%20Time%20Data%20Integration%20with%20Service%20Broker%20and%20Other%20SQL%20Techniques%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383456%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jul%2009%2C%202008%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EThis%20article%20discusses%20how%20to%20use%20various%20SQL%20technologies%20to%20accomplish%20real%20time%20data%20integration%20between%20SQL%20Server%20instances.%20It%20provides%20a%20set%20of%20sample%20code%20to%20help%20users%20with%20their%20development.%20The%20document%20focuses%20on%20the%20usage%20of%20each%20technology%20which%20is%20incorporated%20into%20the%20data%20integration%20service.%20Please%20refer%20to%20provided%20links%20for%20detail%20information%20about%20the%20technologies.%20%3CB%3E%20%3C%2FB%3E%3C%2FP%3E%3CB%3E%20%3CBR%20%2F%3E%20%3C%2FB%3E%3CP%3EReal%20time%20data%20integration%20definition%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EReal%20time%20data%20integration%20supports%20event-driven%20data%20movement%20and%20transformation%20between%20SQL%20Server%20instances%20which%20host%20databases%20with%20different%20schemas.%20The%20data%20integration%20should%20be%20transparent%20to%20source%20systems%20without%20significantly%20impacting%20the%20systems%20when%20events%20are%20captured%20and%20delivered.%20The%20technique%20also%20supports%20an%20intermediate%20format%20which%20allows%20decoupling%20of%20schemas%20between%20source%20and%20destination%20systems.%20It%20allows%20either%20system%20to%20change%20schemas%20without%20breaking%20the%20application%20in%20the%20other%20system.%20The%20data%20integration%20provides%20fast%20and%20efficient%20data%20delivery%20to%20a%20destination%20in%20an%20event-driven%20model%2C%20without%20polling%20the%20source%20system%20for%20new%20data.%3C%2FP%3E%3CB%3E%20%3CBR%20%2F%3E%20%3C%2FB%3E%3CP%3ESales%20data%20integration%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20real%20time%20data%20integration%20demo%20shows%20the%20sales%20data%20integration%20between%20the%20databases%2C%20AdventureWorks%20(AW)%20and%20AdventureWorksDW%20(AWDW).%20The%20data%20integration%20service%20catches%20the%20sales%20data%20change%20on%20AW%2C%20and%20transforms%20the%20data%20in%20the%20schema%20supported%20in%20AW%20onto%20a%20general%20XML%20format.%20The%20service%20sends%20the%20data%20in%20XML%20onto%20AWDW%2C%20and%20transforms%20it%20to%20correspond%20to%20the%20AWDW%20schema.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20demo%20uses%20the%20sample%20databases%20on%20SQL%20Server.%20Please%20refer%20to%20the%20link%20for%20the%20detail%20information%20about%20the%20databases%20%5B%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms124659.aspx%22%20mce_href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms124659.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms124659.aspx%20%3C%2FA%3E%20%5D.%20Users%20can%20download%20and%20install%20the%20databases%20for%20SQL%20Server%202008%20from%20the%20following%20link%20%5B%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms124501(SQL.100).aspx%22%20mce_href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms124501(SQL.100).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms124501(SQL.100).aspx%20%3C%2FA%3E%20%5D.%3C%2FP%3E%3CB%3E%20%3CBR%20%2F%3E%20%3C%2FB%3E%3CP%3ETechniques%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3E%20%3C%2FB%3E%20Change%20tracking%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EChange%20tracking%20provides%20a%20mechanism%20to%20query%20for%20changes%20to%20data%20and%20to%20access%20information%20related%20to%20the%20changes.%20This%20solution%20provides%20answer%20to%20the%20following%20questions.%20What%20rows%20have%20changed%20for%20a%20user%20table%3F%20What%20are%20the%20latest%20data%20in%20the%20rows%3F%20Change%20Tracking%20requires%20small%20amount%20of%20storage%20for%20each%20changed%20row%2C%20while%20it%20only%20works%20for%20getting%20the%20latest%20data.%20Please%20refer%20to%20the%20following%20link%20for%20detail%20information%20about%20Change%20Tracking%20%5B%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fbb933874(SQL.100).aspx%22%20mce_href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fbb933874(SQL.100).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fbb933874(SQL.100).aspx%20%3C%2FA%3E%20%5D.%20If%20an%20application%20requires%20information%20about%20all%20the%20changes%20and%20the%20intermediate%20values%20of%20the%20changed%20data%20then%20it%20should%20use%20Change%20Data%20Capture%20(CDC).%20Please%20refer%20to%20the%20following%20document%20for%20the%20comparison%20of%20two%20techniques%20%5B%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fcc280519(SQL.100).aspx%22%20mce_href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fcc280519(SQL.100).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fcc280519(SQL.100).aspx%20%3C%2FA%3E%20%5D.%20We%20plan%20to%20write%20another%20document%20which%20shows%20how%20to%20use%20CDC%20as%20a%20change%20tracking%20option.%20The%20following%20code%20block%20shows%20how%20to%20enable%20Change%20Tracking%20on%20the%20database%20and%20table%20levels.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EALTER%20DATABASE%20AdventureWorks%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20CHANGE_TRACKING%20%3D%20ON%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E(CHANGE_RETENTION%20%3D%202%20DAYS%2C%20AUTO_CLEANUP%20%3D%20ON)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EALTER%20TABLE%20%5BAdventureWorks%5D.%5BSales%5D.%5BSalesOrderHeader%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EENABLE%20CHANGE_TRACKING%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWITH%20(TRACK_COLUMNS_UPDATED%20%3D%20ON)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EALTER%20TABLE%20%5BAdventureWorks%5D.%5BSales%5D.%5BSalesOrderDetail%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EENABLE%20CHANGE_TRACKING%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWITH%20(TRACK_COLUMNS_UPDATED%20%3D%20ON)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EChanged%20data%20in%20XML%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAfter%20setting%20change%20tracking%20on%20a%20database%20and%20tables%20the%20change%20tables%20are%20populated%20with%20the%20data%20change%20information%20when%20data%20is%20inserted%2C%20deleted%20or%20updated%20on%20the%20tables.%20The%20data%20integration%20service%20uses%20the%20following%20code%20block%20to%20fetch%20the%20change%20information%20and%20create%20an%20XML%20file%20with%20the%20data%20change.%20Using%20CHANGETABLE%20function%20it%20creates%20change%20tracking%20information%20for%20the%20tables%2C%20%E2%80%98SalesOrderHeader%E2%80%99%20and%20%E2%80%98SalesOrderDetail%E2%80%99.%20The%20code%20generates%20an%20XML%20document%20containing%20the%20information%20using%20the%20FOR%20XML%20mode.%20In%20the%20XML%20file%20the%20root%2C%20top-level%20element%20is%20named%20with%20%E2%80%98Sales%E2%80%99%2C%20and%20each%20sales%20order%20header%20corresponds%20to%20an%20element%20named%20with%20%E2%80%98SalesOrderHeader%E2%80%99.%20A%20%E2%80%98SalesOrderHeader%E2%80%99%20element%20contains%20one%20or%20more%20%E2%80%98SalesOrderDetail%E2%80%99%20elements%20which%20describe%20the%20data%20change%20information%20on%20the%20table%2C%20%E2%80%98SalesOrderDetail%E2%80%99.%20INNER%20JOIN%20clauses%20make%20sure%20that%20all%20the%20change%20data%20information%20is%20retrieved%20from%20the%20tables.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40changeReportXML%20%3D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20SYS_CHANGE_OPERATION%2C%20c_soh.SalesOrderID%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20SYS_CHANGE_OPERATION%2C%20c_sod.SalesOrderID%2C%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3Ec_sod.SalesOrderDetailID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20CHANGETABLE%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3E(%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3ECHANGES%20%5BAdventureWorks%5D.%5BSales%5D.%5BSalesOrderDetail%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3E%40last_sync_version%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%20AS%20c_sod%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20%5BAdventureWorks%5D.%5BSales%5D.%5BSalesOrderDetail%5D%20sod%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EON%20sod.SalesOrderDetailID%20%3D%20c_sod.SalesOrderDetailID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20c_soh.SalesOrderID%20%3D%20c_sod.SalesOrderID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFOR%20XML%20PATH%20('SalesOrderDetail')%2C%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3Etype%2C%20ELEMENTS%20XSINIL%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20CHANGETABLE%20(%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3ECHANGES%20%5BAdventureWorks%5D.%5BSales%5D.%5BSalesOrderHeader%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3E%40last_sync_version%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%20AS%20c_soh%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20%5BAdventureWorks%5D.%5BSales%5D.%5BSalesOrderHeader%5D%20soh%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EON%20soh.SalesOrderID%20%3D%20c_soh.SalesOrderID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20%40salesOrderID%20%3D%20c_soh.SalesOrderID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFOR%20XML%20PATH%20('SalesOrderHeader')%2C%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3Eroot('Sales')%2CELEMENTS%20XSINIL%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EChange%20notification%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESQL%20Server%20provides%20several%20mechanisms%20for%20notifying%20data%20change%20to%20an%20application.%20For%20example%2C%20Trigger%20%5B%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms189599.aspx%22%20mce_href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms189599.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms189599.aspx%20%3C%2FA%3E%20%5D%20and%20Query%20Notification%20(QN)%20%5B%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms130764.aspx%22%20mce_href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms130764.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms130764.aspx%20%3C%2FA%3E%20%5D.%20Trigger%20provides%20a%20simple%20way%20for%20the%20notification%2C%20while%20only%20supporting%20synchronous%20mechanism.%20QN%20supports%20asynchronous%20notification%20and%20rich%20filtering%20semantics.%20However%20QN%20cannot%20be%20configured%20in%20TSQL%20within%20SQL%20Server.%20In%20the%20Real%20Time%20Data%20integration%20demo%20we%20use%20a%20technique%20integrating%20Service%20Broker%20and%20Trigger.%20It%20provides%20a%20simple%20way%20to%20support%20event%20notification%20implementing%20asynchronous%20semantic%20in%20TSQL%20within%20SQL%20Server.%20The%20following%20code%20block%20shows%20the%20event%20notification%20on%20the%20demo.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20ConversationHandle%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E(conversationHandle%20uniqueidentifier)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--Create%20a%20dialog%20to%20send%20all%20the%20transactions%20on%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40conversationHandle%20uniqueidentifier%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--Create%20a%20new%20conversation%20on%20the%20table%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20DIALOG%20%40conversationHandle%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20SERVICE%20AsynchTriggerInitiatorService%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETO%20SERVICE%20N'AsynchTriggerTargetService'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EON%20CONTRACT%20%5BAsynchTriggerContract%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWITH%20ENCRYPTION%20%3D%20OFF%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINSERT%20ConversationHandle%20(conversationHandle)%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EVALUES%20(%40conversationHandle)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20TRIGGER%20for%20initiating%20the%20change%20tracking%20demo%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TRIGGER%20ChangeTrackingTrigger%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EON%20%5BAdventureWorks%5D.%5BSales%5D.%5BSalesOrderHeader%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAFTER%20INSERT%2C%20DELETE%2C%20UPDATE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40conversationHandle%20uniqueidentifier%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20TOP%20(1)%20%40conversationHandle%20%3D%20conversationHandle%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20ConversationHandle%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESEND%20ON%20CONVERSATION%20%40conversationHandle%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EMESSAGE%20TYPE%20%5BAsynchTriggerMessageType%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EReliable%20data%20movement%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EService%20Broker%20provides%20asynchronous%20and%20reliable%20data%20movement.%20It%20supports%20TSQL%20programming%20model%20built%20on%20SQL%20Server%20database%20engine.%20Please%20refer%20to%20the%20following%20link%20for%20the%20detail%20information%20about%20Service%20Broker%20%5B%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fsqlserver%2Fbb671396.aspx%22%20mce_href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fsqlserver%2Fbb671396.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fsqlserver%2Fbb671396.aspx%20%3C%2FA%3E%20%5D.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20data%20integration%20service%20uses%20multiple%20conversations%20for%20message%20delivery%20to%20increase%20throughput.%20Using%20multiple%20dialogs%20brings%20the%20data%20parallelism%20on%20the%20receiving%20side.%20Multiple%20threads%20can%20receive%20and%20process%20the%20messages%20in%20the%20dialogs%20independently.%20However%2C%20initiating%20the%20conversations%20brings%20load%20to%20a%20system.%20Therefore%20right%20amounts%20of%20conversations%20should%20be%20chosen%20smartly.%20In%20the%20real%20time%20data%20integration%20demo%20we%20choose%20four%20conversations%20for%20processing%20the%20messages%20with%20high%20throughput.%20In%20the%20real%20time%20data%20integration%20service%20we%20initiate%20four%20dialogs%2C%20and%20store%20them%20onto%20a%20table.%20The%20demo%20uses%20the%20dialogs%20for%20sending%20messages%20about%20changed%20data%20information.%20Please%20refer%20to%20the%20following%20code%20block%20for%20the%20dialog%20creation.%26nbsp%3B%20The%20following%20code%20blocks%20present%20the%20procedure%20for%20sending%20an%20XML%20message%20using%20Service%20Broker.%20The%20procedure%20uses%20the%20four%20conversations%20evenly%20distributed%20messages%20based%20on%20the%20sales%20order%20ID%20(%20SET%20%40dialogHandleID%20%3D%20%40salesOrderID%20%25%204)%20.%20Because%20messages%20for%20the%20same%20sales%20order%20ID%20are%20delivered%20in%20a%20single%20conversation%20it%20is%20guaranteed%20that%20the%20messages%20are%20delivered%20exactly%20once%20in%20order%20manner.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20PROCEDURE%20SendChanges%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40last_sync_version%20bigint%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40salesOrderID%20bigint%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40dialogHandleID%20INT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40dialogHandle%20uniqueidentifier%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40changeReportXML%20XML%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40next_baseline%20bigint%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40TotalDialogs%20INT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40logMsg%20VARCHAR(MAX)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20TOP%20(1)%20%40last_sync_version%20%3D%20lastVersion%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20LastVersion%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40TotalDialogs%20%3D%204%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--Create%20a%20cursor%20on%20the%20change%20table%20for%20%5BSalesOrderHeader%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20cursorChangeOrderHeader%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECURSOR%20FORWARD_only%20READ_ONLY%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFOR%20SELECT%20SalesOrderID%20FROM%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECHANGETABLE%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECHANGES%20%5BSalesOrderHeader%5D%2C%20%40last_sync_version)%20AS%20Cursor_CH%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EORDER%20BY%20SYS_CHANGE_VERSION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--Open%20the%20cursor%20on%20the%20change%20table%20for%20%5BSalesOrderHeader%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--Loop%20for%20each%20changed%20sales%20order%20id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOPEN%20cursorChangeOrderHeader%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHILE(1%3D1)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFETCH%20NEXT%20FROM%20cursorChangeOrderHeader%20INTO%20%40salesOrderID%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--If%20there%20is%20no%20more%20changed%20sales%20order%20then%20exit%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20(%40%40FETCH_STATUS%20!%3D%200)%20BREAK%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%26lt%3B%20%3CB%3E%20Fetching%20changed%20data%20and%20creating%20XML%20file.%26gt%3B%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3E%20--%3CPLEASE%20refer%3D%22%22%20to%3D%22%22%20the%3D%22%22%20code%3D%22%22%20block%3D%22%22%20on%3D%22%22%20section%3D%22%22%202.a.1.%3D%22%22%3E%20%3C%2FPLEASE%3E%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--Find%20the%20conversation%20handle%20for%20the%20sales%20order%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--from%20the%20dialog%20handle%20table%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40dialogHandleID%20%3D%20%40salesOrderID%20%25%20%40TotalDialogs%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20%40dialogHandle%20%3D%20dialogHandle%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20DialogHandles%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20ID%20%3D%20%40dialogHandleID%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--Capture%20last%20version%20info%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20%40next_baseline%20%3D%20SYS_CHANGE_VERSION%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20CHANGETABLE%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECHANGES%5BAdventureWorks%5D.%5BSales%5D.%5BSalesOrderHeader%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40last_sync_version)%20as%20c_soh%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20%40salesOrderID%20%3D%20SalesOrderID%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--Send%20the%20message%20using%20Broker%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESEND%20ON%20CONVERSATION%20%40dialogHandle%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EMESSAGE%20TYPE%20%5BRealTimeDImessagetype%5D(%40changeReportXML)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECLOSE%20cursorChangeOrderHeader%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDEALLOCATE%20cursorChangeOrderHeader%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUPDATE%20LastVersion%20SET%20lastVersion%20%3D%20%40next_baseline%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EActivation%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EActivation%20allows%20message%20processing%20logic%20to%20be%20launched%20when%20a%20message%20arrives%20on%20a%20Service%20Broker%20queue.%20When%20an%20internal%20activation%20is%20used%20for%20processing%20messages%20a%20stored%20procedure%20is%20declared%20on%20a%20Service%20Broker%20queue%2C%20and%20invoked%20on%20a%20background%20thread%20when%20a%20message%20arrives.%20A%20user%20can%20also%20specify%20an%20executable%20for%20processing%20the%20messages%20as%20an%20external%20activator.%20For%20example%2C%20SQL%20Server%20Integration%20Services%20(SSIS)%20can%20be%20used%20as%20an%20external%20activation%20procedure%20to%20process%20messages.%20Please%20refer%20to%20the%20following%20link%20for%20the%20code%20sample%20and%20document%20of%20External%20Activator%20%5B%20%3CA%20href%3D%22http%3A%2F%2Fwww.codeplex.com%2FSQLSrvSrvcBrkr%2FRelease%2FProjectReleases.aspx%3FReleaseId%3D3853%22%20mce_href%3D%22http%3A%2F%2Fwww.codeplex.com%2FSQLSrvSrvcBrkr%2FRelease%2FProjectReleases.aspx%3FReleaseId%3D3853%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fwww.codeplex.com%2FSQLSrvSrvcBrkr%2FRelease%2FProjectReleases.aspx%3FReleaseId%3D3853%20%3C%2FA%3E%20%5D.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20the%20real%20time%20data%20integration%20services%20demo%20we%20use%20internal%20activators%20to%20process%20event%20notification%20messages%20on%20the%20initiator%20service%20as%20well%20as%20changed%20data%20information%20messages%20on%20the%20target%20service.%20We%20briefly%20mention%20how%20the%20services%20process%20the%20messages%20in%20the%20activation%20procedures.%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EMessage%20processing%20in%20the%20initiator%3C%2FLI%3E%0A%20%20%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EThe%20real%20time%20data%20integration%20initiator%20handles%20messages%20from%20two%20different%20services.%20One%20of%20the%20services%20is%20an%20asynchronous%20event%20notification%20service%2C%20and%20the%20other%20is%20a%20real%20time%20data%20integration%20target%20service.%20A%20single%20service%20in%20the%20initiator%20handles%20the%20message%20from%20the%20two%20different%20sources%20based%20on%20message%20types%20and%20service%20names.%20The%20following%20pseudo-code%20block%20describes%20the%20message%20processing%20logic%20in%20the%20initiator.%3C%2FP%3E%3CB%3E%20%3CBR%20%2F%3E%20%3C%2FB%3E%3CP%3E%3CB%3EWHILE%20%3C%2FB%3E%20there%20is%20any%20message%20on%20%E2%80%98RealTime_DI_Initiator_queue%E2%80%99%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3ERECEIVE%20%3C%2FB%3E%20a%20message%20%3CB%3E%20FROM%20%3C%2FB%3E%20the%20queue%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3EIF%20%3C%2FB%3E%20message%20type%20is%20%E2%80%98EndDialog%E2%80%99%20%3CB%3E%20THEN%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3E%20END%20CONVERSATION%20%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3EELSE%20IF%20%3C%2FB%3E%20message%20type%20is%20%E2%80%98ERROR%E2%80%99%20%3CB%3E%20THEN%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3EIF%20%3C%2FSTRONG%3E%20service%20name%20is%20%E2%80%98RealTime_DI_Initiator_Service%E2%80%99%20%3CSTRONG%3E%20THEN%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERaise%20error%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECreate%20a%20new%20dialog%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EResend%20pending%20messages%20using%20the%20dialog%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EReplace%20old%20dialog%20with%20the%20new%20one%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3EEND%20CONVERSATION%20%3C%2FB%3E%20(old%20dialog)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3EIF%20%3C%2FB%3E%20service%20name%20is%20%E2%80%98Asynchronous_Trigger_Target_Service%E2%80%99%20%3CB%3E%20THEN%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3E%20Raise%20error%3B%20%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3EEND%20CONVERSATION%20%3C%2FB%3E%20%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3EELSE%20IF%20%3C%2FB%3E%20message%20type%20is%20%E2%80%98Asynchronous%20triggering%E2%80%99%20%3CB%3E%20THEN%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3E%20RECEIVE%20%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3EWHERE%20%3C%2FB%3E%20conversation_handle%20is%20identical%20with%20this%20message%E2%80%99s%20handle%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3EEXEC%20%3C%2FB%3E%20SendChanges%20%3CB%3E%20PROCEDURE%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EMessage%20processing%20in%20the%20target%3C%2FLI%3E%0A%20%20%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EThe%20message%20processing%20procedure%20in%20the%20real%20time%20data%20integration%20target%20receives%20messages%20from%20a%20target%20queue%2C%20and%20transforms%20the%20messages%20from%20the%20XML%20format%20into%20a%20supported%20schema.%20A%20simple%20and%20straightforward%20way%20to%20process%20messages%20is%20to%20receive%20a%20message%20from%20the%20queue%20and%20to%20transform%20it%20one%20by%20one%20until%20all%20the%20messages%20are%20processed%20on%20the%20queue.%20However%2C%20the%20mechanism%20may%20hurt%20the%20performance%20of%20the%20data%20integration%20target.%20Instead%20of%20receiving%20a%20single%20message%20and%20transform%20it%20the%20data%20integration%20target%20service%20uses%20a%20cursor-based%20processing%20mechanism.%20It%20receives%20all%20the%20messages%20from%20the%20target%20queue%2C%20and%20stores%20in%20a%20temporary%20table.%20A%20cursor%20iterates%20the%20table%20to%20fetch%20a%20message%20and%20process%20it%20to%20covert%20from%20an%20XML%20format%20to%20a%20desired%20schema.%20The%20following%20code%20block%20shows%20the%20activation%20procedure%20on%20the%20target.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20PROCEDURE%20ProcessMessagesDW%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40handle%20uniqueidentifier%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40messageBody%20XML%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40tableMessages%20TABLE(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Equeuing_order%20BIGINT%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Econversation_handle%20UNIQUEIDENTIFIER%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Emessage_body%20VARBINARY(MAX))%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20cursorMessages%20CURSOR%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EFORWARD_ONLY%20READ_ONLY%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFOR%20SELECT%20conversation_handle%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Emessage_body%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20%40tableMessages%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EORDER%20BY%20queuing_order%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHILE(1%3D1)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWAITFOR(RECEIVE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Equeuing_order%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Econversation_handle%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Emessage_body%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20%5BRealTimeDItargetqueue%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINTO%20%40tableMessages)%2C%20TIMEOUT%201000%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF(%40%40ROWCOUNT%20%3D%200)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBREAK%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOPEN%20cursorMessages%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHILE(1%3D1)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFETCH%20NEXT%20FROM%20cursorMessages%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINTO%20%40handle%2C%20%40messageBody%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF(%40%40FETCH_STATUS%20!%3D%200)%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EBREAK%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20%3CMESSAGE%20transformation%3D%22%22%3E%3C%2FMESSAGE%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECLOSE%20cursorMessages%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDELETE%20FROM%20%40tableMessages%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDEALLOCATE%20cursorMessages%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EData%20transformation%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAfter%20receiving%20messages%20the%20target%20service%20transforms%20the%20received%20messages%2C%20and%20populates%20tables%20with%20the%20changed%20data%20information%20from%20the%20messages.%20The%20received%20messages%20are%20in%20XML%20format.%20The%20service%20processes%20each%20of%20the%20messages%20to%20obtain%20required%20information%20from%20the%20message%20using%20TSQL%20language%20coupled%20with%20integrated%20XML%20support.%20The%20following%20code%20block%20shows%20a%20sample%20of%20the%20transformation%20using%20TSQL.%20On%20this%20example%2C%20the%20transformation%20is%20occurred%20only%20for%20the%20data%20insert%20event.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINSERT%20INTO%20%5BAdventureWorksDW%5D.%5Bdbo%5D.%5BFactInternetSales%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EN1.SOH.value('CustomerID%5B1%5D'%2C%20'int')%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EAS%20%5BCustomerKey%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%2CN2.SOD.value('SpecialOfferID%5B1%5D'%2C%20'int')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%20%5BPromotionKey%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%2CN2.SOD.value('CarrierTrackingNumber%5B1%5D'%2C'NCHAR(9)')%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EAS%20CarrierTrackingNumber%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%2CN1.SOH.value('PurchaseOrderNumber%5B1%5D'%2C'NVCHAR(25)')%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EAS%20%5BCustomerPONumber%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40messageBody.nodes('%2FSales%2FSalesOrderHeader')%20N1(SOH)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECROSS%20APPLY%20soh.nodes('SalesOrderDetail')%20N2(SOD)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EN1.SOH.value('CustomerType%5B1%5D'%2C%20'CHAR')%20%3D%20'I'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAND%20N2.SOD.value('SYS_CHANGE_OPERATION%5B1%5D'%2C'CHAR')%3D'I'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESQL%20Server%20Integration%20Services%20(SSIS)%20also%20provides%20the%20data%20transformations.%20SSIS%20supports%20various%20forms%20of%20data%20transformation%20between%20heterogeneous%20sources.%20Please%20refer%20to%20the%20following%20link%20for%20more%20detail%20information%20about%20SSIS%20%5B%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fsqlserver%2Fbb671392.aspx%22%20mce_href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fsqlserver%2Fbb671392.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fsqlserver%2Fbb671392.aspx%20%3C%2FA%3E%20%5D.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20document%20discusses%20about%20real-time%20data%20integration%20technologies%20with%20the%20coordination%20of%20a%20set%20of%20powerful%20SQL%20Server%20technologies.%20The%20service%20provides%20reliable%20and%20transparent%20data%20integration%20between%20instances.%20The%20service%20is%20composed%20with%20the%20following%20technologies.%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EData%20tracking%3A%20Change%20tracking%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EChange%20notification%3A%20Triggers%20and%20Service%20Broker%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EReliable%20data%20movement%3A%20Service%20Broker%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EActivation%3A%20Internal%2C%20Blocking%20with%20WAITFOR%20RECEIVE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETransformation%3A%20TSQL%20with%20XML%20support%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20complete%20code%20list%20for%20the%20demo%20can%20be%20found%20on%20the%20following%20link%20%5B%20%3CA%20href%3D%22http%3A%2F%2Fwww.codeplex.com%2FSQLSrvSrvcBrkr%2FRelease%2FProjectReleases.aspx%3FReleaseId%3D15139%22%20mce_href%3D%22http%3A%2F%2Fwww.codeplex.com%2FSQLSrvSrvcBrkr%2FRelease%2FProjectReleases.aspx%3FReleaseId%3D15139%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fwww.codeplex.com%2FSQLSrvSrvcBrkr%2FRelease%2FProjectReleases.aspx%3FReleaseId%3D15139%20%3C%2FA%3E%20%5D.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383456%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jul%2009%2C%202008%20This%20article%20discusses%20how%20to%20use%20various%20SQL%20technologies%20to%20accomplish%20real%20time%20data%20integration%20between%20SQL%20Server%20instances.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383456%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerServiceBroker%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft
First published on MSDN on Jul 09, 2008

This article discusses how to use various SQL technologies to accomplish real time data integration between SQL Server instances. It provides a set of sample code to help users with their development. The document focuses on the usage of each technology which is incorporated into the data integration service. Please refer to provided links for detail information about the technologies.


Real time data integration definition


Real time data integration supports event-driven data movement and transformation between SQL Server instances which host databases with different schemas. The data integration should be transparent to source systems without significantly impacting the systems when events are captured and delivered. The technique also supports an intermediate format which allows decoupling of schemas between source and destination systems. It allows either system to change schemas without breaking the application in the other system. The data integration provides fast and efficient data delivery to a destination in an event-driven model, without polling the source system for new data.


Sales data integration


The real time data integration demo shows the sales data integration between the databases, AdventureWorks (AW) and AdventureWorksDW (AWDW). The data integration service catches the sales data change on AW, and transforms the data in the schema supported in AW onto a general XML format. The service sends the data in XML onto AWDW, and transforms it to correspond to the AWDW schema.


The demo uses the sample databases on SQL Server. Please refer to the link for the detail information about the databases [ http://msdn.microsoft.com/en-us/library/ms124659.aspx ]. Users can download and install the databases for SQL Server 2008 from the following link [ http://technet.microsoft.com/en-us/library/ms124501(SQL.100).aspx ].


Techniques


Change tracking


Change tracking provides a mechanism to query for changes to data and to access information related to the changes. This solution provides answer to the following questions. What rows have changed for a user table? What are the latest data in the rows? Change Tracking requires small amount of storage for each changed row, while it only works for getting the latest data. Please refer to the following link for detail information about Change Tracking [ http://msdn.microsoft.com/en-us/library/bb933874(SQL.100).aspx ]. If an application requires information about all the changes and the intermediate values of the changed data then it should use Change Data Capture (CDC). Please refer to the following document for the comparison of two techniques [ http://msdn.microsoft.com/en-us/library/cc280519(SQL.100).aspx ]. We plan to write another document which shows how to use CDC as a change tracking option. The following code block shows how to enable Change Tracking on the database and table levels.


ALTER DATABASE AdventureWorks


SET CHANGE_TRACKING = ON


(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)


ALTER TABLE [AdventureWorks].[Sales].[SalesOrderHeader]


ENABLE CHANGE_TRACKING


WITH (TRACK_COLUMNS_UPDATED = ON)


ALTER TABLE [AdventureWorks].[Sales].[SalesOrderDetail]


ENABLE CHANGE_TRACKING


WITH (TRACK_COLUMNS_UPDATED = ON)


Changed data in XML


After setting change tracking on a database and tables the change tables are populated with the data change information when data is inserted, deleted or updated on the tables. The data integration service uses the following code block to fetch the change information and create an XML file with the data change. Using CHANGETABLE function it creates change tracking information for the tables, ‘SalesOrderHeader’ and ‘SalesOrderDetail’. The code generates an XML document containing the information using the FOR XML mode. In the XML file the root, top-level element is named with ‘Sales’, and each sales order header corresponds to an element named with ‘SalesOrderHeader’. A ‘SalesOrderHeader’ element contains one or more ‘SalesOrderDetail’ elements which describe the data change information on the table, ‘SalesOrderDetail’. INNER JOIN clauses make sure that all the change data information is retrieved from the tables.


SET @changeReportXML =


(


SELECT SYS_CHANGE_OPERATION, c_soh.SalesOrderID,


(


SELECT SYS_CHANGE_OPERATION, c_sod.SalesOrderID,




c_sod.SalesOrderDetailID


FROM CHANGETABLE



(



CHANGES [AdventureWorks].[Sales].[SalesOrderDetail],






@last_sync_version


) AS c_sod


INNER JOIN [AdventureWorks].[Sales].[SalesOrderDetail] sod


ON sod.SalesOrderDetailID = c_sod.SalesOrderDetailID


WHERE c_soh.SalesOrderID = c_sod.SalesOrderID


FOR XML PATH ('SalesOrderDetail'),




type, ELEMENTS XSINIL


)


FROM CHANGETABLE (



CHANGES [AdventureWorks].[Sales].[SalesOrderHeader],





@last_sync_version


) AS c_soh


INNER JOIN [AdventureWorks].[Sales].[SalesOrderHeader] soh


ON soh.SalesOrderID = c_soh.SalesOrderID


WHERE @salesOrderID = c_soh.SalesOrderID


FOR XML PATH ('SalesOrderHeader'),



root('Sales'),ELEMENTS XSINIL


);


Change notification


SQL Server provides several mechanisms for notifying data change to an application. For example, Trigger [ http://msdn.microsoft.com/en-us/library/ms189599.aspx ] and Query Notification (QN) [ http://msdn.microsoft.com/en-us/library/ms130764.aspx ]. Trigger provides a simple way for the notification, while only supporting synchronous mechanism. QN supports asynchronous notification and rich filtering semantics. However QN cannot be configured in TSQL within SQL Server. In the Real Time Data integration demo we use a technique integrating Service Broker and Trigger. It provides a simple way to support event notification implementing asynchronous semantic in TSQL within SQL Server. The following code block shows the event notification on the demo.


CREATE TABLE ConversationHandle


(conversationHandle uniqueidentifier);


--Create a dialog to send all the transactions on


BEGIN TRANSACTION


DECLARE @conversationHandle uniqueidentifier


--Create a new conversation on the table


BEGIN DIALOG @conversationHandle


FROM SERVICE AsynchTriggerInitiatorService


TO SERVICE N'AsynchTriggerTargetService'


ON CONTRACT [AsynchTriggerContract]


WITH ENCRYPTION = OFF;


INSERT ConversationHandle (conversationHandle)




VALUES (@conversationHandle)


COMMIT;


-- TRIGGER for initiating the change tracking demo


CREATE TRIGGER ChangeTrackingTrigger


ON [AdventureWorks].[Sales].[SalesOrderHeader]


AFTER INSERT, DELETE, UPDATE


AS


BEGIN TRANSACTION;


DECLARE @conversationHandle uniqueidentifier;


SELECT TOP (1) @conversationHandle = conversationHandle


FROM ConversationHandle;


SEND ON CONVERSATION @conversationHandle




MESSAGE TYPE [AsynchTriggerMessageType]


COMMIT;


Reliable data movement


Service Broker provides asynchronous and reliable data movement. It supports TSQL programming model built on SQL Server database engine. Please refer to the following link for the detail information about Service Broker [ http://technet.microsoft.com/en-us/sqlserver/bb671396.aspx ].


The data integration service uses multiple conversations for message delivery to increase throughput. Using multiple dialogs brings the data parallelism on the receiving side. Multiple threads can receive and process the messages in the dialogs independently. However, initiating the conversations brings load to a system. Therefore right amounts of conversations should be chosen smartly. In the real time data integration demo we choose four conversations for processing the messages with high throughput. In the real time data integration service we initiate four dialogs, and store them onto a table. The demo uses the dialogs for sending messages about changed data information. Please refer to the following code block for the dialog creation.  The following code blocks present the procedure for sending an XML message using Service Broker. The procedure uses the four conversations evenly distributed messages based on the sales order ID ( SET @dialogHandleID = @salesOrderID % 4) . Because messages for the same sales order ID are delivered in a single conversation it is guaranteed that the messages are delivered exactly once in order manner.


CREATE PROCEDURE SendChanges


AS


BEGIN


DECLARE @last_sync_version bigint;


DECLARE @salesOrderID bigint;


DECLARE @dialogHandleID INT;


DECLARE @dialogHandle uniqueidentifier;


DECLARE @changeReportXML XML;


DECLARE @next_baseline bigint;


DECLARE @TotalDialogs INT;


DECLARE @logMsg VARCHAR(MAX);


BEGIN TRANSACTION;


SELECT TOP (1) @last_sync_version = lastVersion


FROM LastVersion;


SET @TotalDialogs = 4;


--Create a cursor on the change table for [SalesOrderHeader]


DECLARE cursorChangeOrderHeader


CURSOR FORWARD_only READ_ONLY


FOR SELECT SalesOrderID FROM


CHANGETABLE (


CHANGES [SalesOrderHeader], @last_sync_version) AS Cursor_CH


ORDER BY SYS_CHANGE_VERSION;


--Open the cursor on the change table for [SalesOrderHeader]


--Loop for each changed sales order id


OPEN cursorChangeOrderHeader


WHILE(1=1)


BEGIN


FETCH NEXT FROM cursorChangeOrderHeader INTO @salesOrderID;


--If there is no more changed sales order then exit


IF (@@FETCH_STATUS != 0) BREAK;


--< Fetching changed data and creating XML file.>


--<Please refer to the code block on section 2.a.1.>


--Find the conversation handle for the sales order


--from the dialog handle table


SET @dialogHandleID = @salesOrderID % @TotalDialogs;


SELECT @dialogHandle = dialogHandle


FROM DialogHandles


WHERE ID = @dialogHandleID;


--Capture last version info


SELECT @next_baseline = SYS_CHANGE_VERSION


FROM CHANGETABLE (


CHANGES[AdventureWorks].[Sales].[SalesOrderHeader],


@last_sync_version) as c_soh


WHERE @salesOrderID = SalesOrderID;


--Send the message using Broker


SEND ON CONVERSATION @dialogHandle


MESSAGE TYPE [RealTimeDImessagetype](@changeReportXML);


END


CLOSE cursorChangeOrderHeader;


DEALLOCATE cursorChangeOrderHeader;


UPDATE LastVersion SET lastVersion = @next_baseline;


COMMIT;


END;


Activation


Activation allows message processing logic to be launched when a message arrives on a Service Broker queue. When an internal activation is used for processing messages a stored procedure is declared on a Service Broker queue, and invoked on a background thread when a message arrives. A user can also specify an executable for processing the messages as an external activator. For example, SQL Server Integration Services (SSIS) can be used as an external activation procedure to process messages. Please refer to the following link for the code sample and document of External Activator [ http://www.codeplex.com/SQLSrvSrvcBrkr/Release/ProjectReleases.aspx?ReleaseId=3853 ].


In the real time data integration services demo we use internal activators to process event notification messages on the initiator service as well as changed data information messages on the target service. We briefly mention how the services process the messages in the activation procedures.



  • Message processing in the initiator

The real time data integration initiator handles messages from two different services. One of the services is an asynchronous event notification service, and the other is a real time data integration target service. A single service in the initiator handles the message from the two different sources based on message types and service names. The following pseudo-code block describes the message processing logic in the initiator.


WHILE there is any message on ‘RealTime_DI_Initiator_queue’


RECEIVE a message FROM the queue


IF message type is ‘EndDialog’ THEN


END CONVERSATION


ELSE IF message type is ‘ERROR’ THEN


IF service name is ‘RealTime_DI_Initiator_Service’ THEN


Raise error;


Create a new dialog;


Resend pending messages using the dialog;


Replace old dialog with the new one;


END CONVERSATION (old dialog);


IF service name is ‘Asynchronous_Trigger_Target_Service’ THEN


Raise error;


END CONVERSATION ;


ELSE IF message type is ‘Asynchronous triggering’ THEN


RECEIVE


WHERE conversation_handle is identical with this message’s handle


EXEC SendChanges PROCEDURE



  • Message processing in the target

The message processing procedure in the real time data integration target receives messages from a target queue, and transforms the messages from the XML format into a supported schema. A simple and straightforward way to process messages is to receive a message from the queue and to transform it one by one until all the messages are processed on the queue. However, the mechanism may hurt the performance of the data integration target. Instead of receiving a single message and transform it the data integration target service uses a cursor-based processing mechanism. It receives all the messages from the target queue, and stores in a temporary table. A cursor iterates the table to fetch a message and process it to covert from an XML format to a desired schema. The following code block shows the activation procedure on the target.


CREATE PROCEDURE ProcessMessagesDW


AS


BEGIN


DECLARE @handle uniqueidentifier;


DECLARE @messageBody XML;


DECLARE @tableMessages TABLE(


queuing_order BIGINT,


conversation_handle UNIQUEIDENTIFIER,


message_body VARBINARY(MAX));


DECLARE cursorMessages CURSOR



FORWARD_ONLY READ_ONLY


FOR SELECT conversation_handle,


message_body


FROM @tableMessages


ORDER BY queuing_order;


WHILE(1=1)


BEGIN


BEGIN TRANSACTION;


WAITFOR(RECEIVE


queuing_order,


conversation_handle,


message_body


FROM [RealTimeDItargetqueue]


INTO @tableMessages), TIMEOUT 1000;



IF(@@ROWCOUNT = 0)


BEGIN


COMMIT;


BREAK;


END


OPEN cursorMessages;


WHILE(1=1)


BEGIN


FETCH NEXT FROM cursorMessages


INTO @handle, @messageBody;


IF(@@FETCH_STATUS != 0)





BREAK;


-- <Message transformation>


END


CLOSE cursorMessages;


DELETE FROM @tableMessages;


COMMIT;


END


DEALLOCATE cursorMessages;


END


Data transformation


After receiving messages the target service transforms the received messages, and populates tables with the changed data information from the messages. The received messages are in XML format. The service processes each of the messages to obtain required information from the message using TSQL language coupled with integrated XML support. The following code block shows a sample of the transformation using TSQL. On this example, the transformation is occurred only for the data insert event.


INSERT INTO [AdventureWorksDW].[dbo].[FactInternetSales]


SELECT


N1.SOH.value('CustomerID[1]', 'int')



AS [CustomerKey]


,N2.SOD.value('SpecialOfferID[1]', 'int')


AS [PromotionKey]


,N2.SOD.value('CarrierTrackingNumber[1]','NCHAR(9)')



AS CarrierTrackingNumber


,N1.SOH.value('PurchaseOrderNumber[1]','NVCHAR(25)')



AS [CustomerPONumber]


FROM


@messageBody.nodes('/Sales/SalesOrderHeader') N1(SOH)


CROSS APPLY soh.nodes('SalesOrderDetail') N2(SOD)


WHERE


N1.SOH.value('CustomerType[1]', 'CHAR') = 'I'


AND N2.SOD.value('SYS_CHANGE_OPERATION[1]','CHAR')='I'


SQL Server Integration Services (SSIS) also provides the data transformations. SSIS supports various forms of data transformation between heterogeneous sources. Please refer to the following link for more detail information about SSIS [ http://technet.microsoft.com/en-us/sqlserver/bb671392.aspx ].


This document discusses about real-time data integration technologies with the coordination of a set of powerful SQL Server technologies. The service provides reliable and transparent data integration between instances. The service is composed with the following technologies.




Data tracking: Change tracking


Change notification: Triggers and Service Broker


Reliable data movement: Service Broker


Activation: Internal, Blocking with WAITFOR RECEIVE


Transformation: TSQL with XML support


The complete code list for the demo can be found on the following link [ http://www.codeplex.com/SQLSrvSrvcBrkr/Release/ProjectReleases.aspx?ReleaseId=15139 ].