{"id":1787,"date":"2018-08-16T07:04:37","date_gmt":"2018-08-16T07:04:37","guid":{"rendered":"http:\/\/dbtut.com\/?p=1787"},"modified":"2018-11-11T20:17:30","modified_gmt":"2018-11-11T20:17:30","slug":"1787","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/","title":{"rendered":"Service Broker In SQL Server"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p><strong><span style=\"text-decoration: underline;\">Introduction<\/span><\/strong><br \/>\nA service broker is a high availibity technique in SQL Server which was introduced from SQL Server 2005.<br \/>\nThis is primarily to send the big files into smaller segments and assigning them a queue.<br \/>\nNormally it&#8217;s a good way of dealing with disaster recovery when their is point in time failure.<\/p>\n<p>A service broker is most often used when the limitation is associated where we use a connection which is inferior from the rest of the world<\/p>\n<p>The place where service broker is configured sends the data in queues, which can be treated as part of backup file and then is transmitted over a dedicated\/leased<br \/>\nline to the server, and then then restored on the main database, which is fully functional.<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>Benefits<\/strong><\/span><\/p>\n<p>The benefit of using service broker is that it allow multiple queues from different locations to insert into a Single Server(which is<\/p>\n<p>In case any of XML file from one of the server location gets corrupted which is part of queue, then it gets send to the last where the DBA can manually check it and try to get it sorted out,<br \/>\nor permanently remove it from the queue or get a fresh copy of the XML file.<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>Configuration<\/strong><\/span><\/p>\n<pre class=\"lang:default decode:true \">CREATE DATABASE ServiceBrokerTest\r\nGO\r\nUSE ServiceBrokerTest\r\nGO\r\n-- Enable Service Broker\r\nALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER\r\nGO\r\n-- Create Message Type\r\nCREATE MESSAGE TYPE SBMessage\r\nVALIDATION = NONE\r\nGO\r\n-- Create Contract\r\nCREATE CONTRACT SBContract\r\n(SBMessage SENT BY INITIATOR)\r\nGO\r\n-- Create Send Queue\r\nCREATE QUEUE SBSendQueue\r\nGO\r\n-- Create Receive Queue\r\nCREATE QUEUE SBReceiveQueue\r\nGO\r\n-- Create Send Service on Send Queue\r\nCREATE SERVICE SBSendService\r\nON QUEUE SBSendQueue (SBContract)\r\nGO\r\n-- Create Receive Service on Recieve Queue\r\nCREATE SERVICE SBReceiveService\r\nON QUEUE SBReceiveQueue (SBContract)\r\nGO\r\n-- Begin Dialog using service on contract\r\nDECLARE @SBDialog uniqueidentifier\r\nDECLARE @Message NVARCHAR(128)\r\nBEGIN DIALOG CONVERSATION @SBDialog\r\nFROM SERVICE SBSendService\r\nTO SERVICE 'SBReceiveService'\r\nON CONTRACT SBContract\r\nWITH ENCRYPTION = OFF\r\n-- Send messages on Dialog\r\nSET @Message = N'Very First Message';\r\nSEND ON CONVERSATION @SBDialog\r\nMESSAGE TYPE SBMessage (@Message)\r\nSET @Message = N'Second Message';\r\nSEND ON CONVERSATION @SBDialog\r\nMESSAGE TYPE SBMessage (@Message)\r\nSET @Message = N'Third Message';\r\nSEND ON CONVERSATION @SBDialog\r\nMESSAGE TYPE SBMessage (@Message)\r\nGO\r\n-- View messages from Receive Queue\r\nSELECT CONVERT(NVARCHAR(MAX), message_body) AS Message\r\nFROM SBReceiveQueue\r\nGO\r\n-- Receive messages from Receive Queue\r\nRECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS Message\r\nFROM SBReceiveQueue\r\nGO\r\n-- Receive messages from Receive Queue\r\nRECEIVE CONVERT(NVARCHAR(MAX), message_body) AS Message\r\nFROM SBReceiveQueue\r\nGO\r\n-- Clean Up\r\nUSE master\r\nGO\r\nDROP DATABASE ServiceBrokerTest\r\nGO\r\n\r\n<\/pre>\n\n<p>&nbsp;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_1787\" class=\"pvc_stats all  \" data-element-id=\"1787\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Introduction A service broker is a high availibity technique in SQL Server which was introduced from SQL Server 2005. This is primarily to send the big files into smaller segments and assigning them a queue. Normally it&#8217;s a good way of dealing with disaster recovery when their is point in time failure. A service &hellip;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_1787\" class=\"pvc_stats all  \" data-element-id=\"1787\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":109,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1787","post","type-post","status-publish","format-standard","","category-mssql"],"aioseo_notices":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Service Broker In SQL Server - Database Tutorials<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Service Broker In SQL Server - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"&nbsp; Introduction A service broker is a high availibity technique in SQL Server which was introduced from SQL Server 2005. This is primarily to send the big files into smaller segments and assigning them a queue. Normally it&#8217;s a good way of dealing with disaster recovery when their is point in time failure. A service &hellip;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-16T07:04:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-11-11T20:17:30+00:00\" \/>\n<meta name=\"author\" content=\"Vaibhav Krishna\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vaibhav Krishna\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/\"},\"author\":{\"name\":\"Vaibhav Krishna\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/6c10f95f66ae2b4026552b02018b18b7\"},\"headline\":\"Service Broker In SQL Server\",\"datePublished\":\"2018-08-16T07:04:37+00:00\",\"dateModified\":\"2018-11-11T20:17:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/\"},\"wordCount\":213,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/\",\"name\":\"Service Broker In SQL Server - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2018-08-16T07:04:37+00:00\",\"dateModified\":\"2018-11-11T20:17:30+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Service Broker In SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/6c10f95f66ae2b4026552b02018b18b7\",\"name\":\"Vaibhav Krishna\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/482ae666004473e37e849efeedbf7111152f716f6e9fc6852074d49536796697?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/482ae666004473e37e849efeedbf7111152f716f6e9fc6852074d49536796697?s=96&d=mm&r=g\",\"caption\":\"Vaibhav Krishna\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/vaibhavkrishna\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Service Broker In SQL Server - Database Tutorials","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/","og_locale":"en_US","og_type":"article","og_title":"Service Broker In SQL Server - Database Tutorials","og_description":"&nbsp; Introduction A service broker is a high availibity technique in SQL Server which was introduced from SQL Server 2005. This is primarily to send the big files into smaller segments and assigning them a queue. Normally it&#8217;s a good way of dealing with disaster recovery when their is point in time failure. A service &hellip;","og_url":"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/","og_site_name":"Database Tutorials","article_published_time":"2018-08-16T07:04:37+00:00","article_modified_time":"2018-11-11T20:17:30+00:00","author":"Vaibhav Krishna","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Vaibhav Krishna","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/"},"author":{"name":"Vaibhav Krishna","@id":"https:\/\/dbtut.com\/#\/schema\/person\/6c10f95f66ae2b4026552b02018b18b7"},"headline":"Service Broker In SQL Server","datePublished":"2018-08-16T07:04:37+00:00","dateModified":"2018-11-11T20:17:30+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/"},"wordCount":213,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/","url":"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/","name":"Service Broker In SQL Server - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2018-08-16T07:04:37+00:00","dateModified":"2018-11-11T20:17:30+00:00","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/16\/1787\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Service Broker In SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/6c10f95f66ae2b4026552b02018b18b7","name":"Vaibhav Krishna","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/482ae666004473e37e849efeedbf7111152f716f6e9fc6852074d49536796697?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/482ae666004473e37e849efeedbf7111152f716f6e9fc6852074d49536796697?s=96&d=mm&r=g","caption":"Vaibhav Krishna"},"url":"https:\/\/dbtut.com\/index.php\/author\/vaibhavkrishna\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/1787","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/109"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=1787"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/1787\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=1787"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=1787"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=1787"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}