为什么使用服务代理

　　在开始探讨代码之前，我要讲一下为什么我选择使用SQL 服务代理替代SQL复制的一点幕后原因。这样可能对阐述有好处。

　　(选择使用SQL服务代理代替SQL复制的)主要原因是我们想在数据从生产环境OLTP数据库迁移到报表数据库时，能对数据做ETL(数据提取转换加载)。我们还需要很容易地就能从一个报表数据库扩展为多个数据库的能力，以满足报表服务器在不同地点的情况。

　　表

　　下面你会发现一些SQL代码，我们将用这些代码创建一些示例表。在我们的OLTP数据库中，我们创建了两个表，它们是表“LoanApplication”和表“Customer”，而在我们的报表数据库中我们只有一个表“LoanApplication”。当数据插入或者更新到表“LoanApplication ”和表“Customer”时，数据会打包成XML文档并发送到报表数据库。在我这里的示例代码中，所有东西都在一台服务器上，但是数据库要挪到分离的服务器上也是非常容易的。

SQL:

1. IF EXISTS (SELECT * FROM sys.DATABASES WHERE name = ‘Sample_OLTP’)
2. DROP DATABASE Sample_OLTP
3. GO
4. IF EXISTS (SELECT * FROM sys.DATABASES WHERE name = ‘Sample_Reporting’)
5. DROP DATABASE Sample_Reporting
6. GO
7.  
8. CREATE DATABASE Sample_OLTP
9. CREATE DATABASE Sample_Reporting
10. GO
11.  
12. ALTER DATABASE Sample_OLTP SET NEW_BROKER
13. ALTER DATABASE Sample_Reporting SET NEW_BROKER
14. GO
15. ALTER DATABASE Sample_OLTP SET TRUSTWORTHY ON
16. ALTER DATABASE Sample_Reporting SET TRUSTWORTHY ON
17. GO
18.  
19. USE Sample_OLTP
20. GO
21. CREATE TABLE LoanApplication
22. (ApplicationId INT IDENTITY(1,1),
23. CreateTimestamp DATETIME,
24. LoanAmount MONEY,
25. SubmittedOn DATETIME,
26. ApprovedOn DATETIME,
27. LoanStatusId INT,
28. PrimaryCustomerId INT,
29. CoSignerCustomerId INT)
30. GO
31. CREATE TABLE Customer
32. (CustomerId INT IDENTITY(1,1).
33. FirstName VARCHAR(50),
34. LastName VARCHAR(50),
35. EmailAddress VARCHAR(255))
36. GO
37. USE Sample_Reporting
38. GO
39. CREATE TABLE LoanReporting
40. (ApplicationId INT,
41. CreateTimestamp DATETIME,
42. LoanAmount MONEY,
43. SubmittedOn DATETIME,
44. ApprovedOn DATETIME,
45. LoanStatusId INT,
46. PrimaryCustomerId INT,
47. PrimaryFirstName VARCHAR(50),
48. PrimaryLastName VARCHAR(50),
49. PrimaryEmailAddress VARCHAR(255),
50. CoSignerCustomerId INT,
51. CoSignerFirstName VARCHAR(50),
52. CoSignerLastName VARCHAR(50),
53. CoSignerEmailAddress VARCHAR(255))
54. GO

　　服务代理对象

　　在这个系统中，我只利用一对服务代理队列来处理所有数据传输。这样可以在数据流入时维持相互一致性。这些SQL服务代理对象应该被在示例OLTP数据库和示例报表数据库中创建。

SQL:

1. CREATE MESSAGE TYPE ReplData_MT
2. GO
3. CREATE CONTRACT ReplData_Ct
4. (ReplData_MT SENT BY ANY)
5. GO
6. CREATE QUEUE ReplData_Source_Queue
7. GO
8. CREATE QUEUE ReplData_Destination_Queue
9. GO
10. CREATE SERVICE ReplData_Source_Service
11. ON QUEUE ReplData_Source_Queue
12. (ReplData_Ct)
13. GO
14. CREATE SERVICE ReplData_Destination_Service
15. ON QUEUE ReplData_Destination_Queue
16. (ReplData_Ct)
17. GO

　　路由(route)

　　在该OLTP数据库中，你创建了这样一个route(根据你的实际服务器修改BROKER_INSTANCE)。

SQL:

1. CREATE ROUTE ReplData_Route
2. WITH SERVICE_NAME=‘ReplData_Destination_Service’,
3. BROKER_INSTANCE=‘566C7F7A-9373-460A-8BCC-5C1FD4BF49C9′,
4. ADDRESS=‘LOCAL’

　　在该报表数据库中，你创建了这样一个route(根据你的实际服务器修改BROKER_INSTANCE)。

SQL:

1. CREATE ROUTE ReplData_Route
2. WITH SERVICE_NAME=‘ReplData_Source_Service’,
3. BROKER_INSTANCE=‘A4EC5E44-60AF-4CD3-AAAD-C3D467AC682E’,
4. ADDRESS=‘LOCAL’

　　OLTP数据库中的存储过程

　　在该OLTP数据库中我们只需要一个存储过程。这个存储过程将处理信息的发送，这样我们就不必在每个表中写相同的代码。

SQL:

1. CREATE PROCEDURE SendTriggerData
2. @XMLData XML
3. AS
4. BEGIN
5. DECLARE @handle UNIQUEIDENTIFIER
6.  
7. BEGIN DIALOG CONVERSATION @handle
8. FROM SERVICE ReplData_Source_Service
9. TO SERVICE ‘ReplData_Destination_Queue’
10. ON CONTRACT ReplData_Ct
11. WITH ENCRYPTION=OFF;
12.  
13. SEND ON CONVERSATION @handle
14. MESSAGE TYPE ReplData_MT
15. (@XMLData)
16. END
17. GO

　　OLTP数据库触发器

　　该OLTP数据库中每个表上的触发器被保持尽可能小，以便我们给该OLTP服务器增加尽可能少的负载负担。很明显我们会给该OLTP数据库带来额外负载，但是我们希望把它保持在最小。

SQL:

1. CREATE TRIGGER t_LoanApplication ON LoanApplication
2. FOR INSERT, UPDATE
3. AS
4. BEGIN
5. DECLARE @xml XML
6.  
7. SET @xml = (SELECT *
8. FROM inserted AS LoanApplication
9. FOR XML AUTO, ROOT(‘root’))
10.  
11. EXEC SendTriggerData @xml
12. END
13. GO
14.  
15. CREATE TRIGGER t_Customer ON Customer
16. FOR INSERT, UPDATE
17. AS
18. BEGIN
19. DECLARE @xml XML
20.  
21. SET @xml = (SELECT *
22. FROM inserted AS Customer
23. FOR XML AUTO, ROOT(‘root’))
24.  
25. EXEC SendTriggerData @xml
26. END
27. GO

　　报表数据库中的存储过程

　　该报表数据库是实际工作执行的地方。在这里我们获取XML文档，识别数据从哪个表来，然后把XML文档传递给子存储过程，然后处理数据并更新表。

SQL:

1. CREATE PROCEDURE ProcessOLTPData_LoanApplication
2. @xml XML
3. AS
4. DECLARE  @hDoc INT
5. EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml
6.  
7. UPDATE LoanReporting
8. SET ApplicationId=a.ApplicationId,
9. CreateTimestamp = a.CreateTimestamp,
10. LoanAmount=a.LoanAmount,
11. SubmittedOn=a.SubmittedOn,
12. ApprovedOn=a.ApprovedOn,
13. LoanStatusId=a.LoanStatusId
14. FROM OPENXML (@hDoc, ‘/root/LoanApplication’)
15. WITH (ApplicationId INT ‘@ApplicationId’,
16. CreateTimestamp DATETIME ‘@CreateTimestamp’,
17. LoanAmount MONEY ‘@LoanAmount’,
18. SubmittedOn DATETIME ‘@SubmittedOn’,
19. ApprovedOn DATETIME ‘@ApprovedOn’,
20. LoanStatusId INT ‘@LoanStatusId’,
21. PrimaryCustomerId INT ‘@PrimaryCustomerId’,
22. CoSignerCustomerId INT ‘@CoSignerCustomerId’) a
23. WHERE a.ApplicationId = LoanReporting.ApplicationId
24.  
25. INSERT INTO LoanReporting
26. (ApplicationId, CreateTimestamp, LoanAmount, SubmittedOn, ApprovedOn, LoanStatusId, PrimaryCustomerId, CoSignerCustomerId)
27. SELECT ApplicationId, CreateTimestamp, LoanAmount, SubmittedOn, ApprovedOn, LoanStatusId, PrimaryCustomerId, CoSignerCustomerId
28. FROM OPENXML (@hDoc, ‘/root/LoanApplication’)
29. WITH (ApplicationId INT ‘@ApplicationId’,
30. CreateTimestamp DATETIME ‘@CreateTimestamp’,
31. LoanAmount MONEY ‘@LoanAmount’,
32. SubmittedOn DATETIME ‘@SubmittedOn’,
33. ApprovedOn DATETIME ‘@ApprovedOn’,
34. LoanStatusId INT ‘@LoanStatusId’,
35. PrimaryCustomerId INT ‘@PrimaryCustomerId’,
36. CoSignerCustomerId INT ‘@CoSignerCustomerId’) a
37. WHERE NOT EXISTS (SELECT * FROM LoanReporting WHERE a.ApplicationId = LoanReporting.ApplicationId)
38.  
39. EXEC sp_xml_removedocument @hDoc
40. GO
41. CREATE PROCEDURE PRocessOLTPData_Customer
42. @xml XML
43. AS
44. DECLARE  @hDoc INT
45. EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml
46.  
47. UPDATE LoanReporting
48. SET PrimaryEmailAddress = EmailAddress,
49. PrimaryFirstName = FirstName,
50. PrimaryLastName = LastName
51. FROM OPENXML(@hDoc, ‘/root/Customer’)
52. WITH (CustomerId INT ‘@CustomerId’,
53. FirstName VARCHAR(50) ‘@FirstName’,
54. LastName VARCHAR(50) ‘@LastName’,
55. EmailAddress VARCHAR(255) ‘@EmailAddress’) a
56. WHERE PrimaryCustomerId = a.CustomerId
57.  
58. UPDATE LoanReporting
59. SET CoSignerEmailAddress = EmailAddress,
60. CoSignerFirstName = FirstName,
61. CoSignerLastName = LastName
62. FROM OPENXML(@hDoc, ‘/root/Customer’)
63. WITH (CustomerId INT ‘@CustomerId’,
64. FirstName VARCHAR(50) ‘@FirstName’,
65. LastName VARCHAR(50) ‘@LastName’,
66. EmailAddress VARCHAR(255) ‘@EmailAddress’) a
67. WHERE CoSignerCustomerId = a.CustomerId
68.  
69. EXEC sp_xml_removedocument @hDoc
70. GO
71.  
72. CREATE PROCEDURE ProcessOLTPData
73. AS
74. DECLARE @xml XML
75. DECLARE @handle UNIQUEIDENTIFIER, @hDoc INT
76.  
77. WHILE 1=1
78. BEGIN
79. SELECT @xml = NULL, @handle = NULL
80.  
81. WAITFOR (RECEIVE TOP (1) @handle=conversation_handle,
82. @xml=CAST(message_body AS XML)
83. FROM ReplData_Destination_Queue), TIMEOUT 1000
84.  
85. IF @handle IS NULL
86. BREAK
87.  
88. EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml
89.  
90. IF EXISTS (SELECT *
91. FROM OPENXML(@hDoc, ‘/root/LoanApplication’)
92. )
93. BEGIN
94. EXEC ProcessOLTPData_LoanApplication @xml
95. END
96.  
97. IF EXISTS (SELECT *
98. FROM OPENXML (@hDoc, ‘/root/Customer’)
99. )
100. BEGIN
101. EXEC PRocessOLTPData_Customer @xml
102. END
103.  
104. EXEC sp_xml_removedocument @hDoc
105.  
106. END CONVERSATION @handle
107. END
108. GO

　　你现在可以设置该队列作为激活的存储过程运行存储过程“ProcessOLTPData ”，它会在数据到达时处理数据。