September 30, 2009 at 4:58 pm
I have a database servicing an outward facing web site that is in a domain that isn't trusted in our corporate domain. We want to pull data from the web database for reporting purposes. Replication and log shipping are not options because we can't map a drive for the logs across the domain boundary.
I want to code a Proof Of Concept (POC) using Service Broker that will move the data across the domain boundary using certificates on TCP Service Broker endpoints through port 22, which we already have open for our secure FTP site. My first step is to create validation schemas of each of the tables to ensure that data isn't corrupted in the ETL process. I know how to pro grammatically extract table schema as DDL SQL statements using SMO, but I want to extract the table schemas as XML schema definitions. The scripter object defined from the Microsoft.SqlServer.Management.Smo assembly doesn't seem to have an option to output the DDL as XML-XSD schema.
Has anyone perfected a method of extracting a SQL 2008 table DDL definition and converting it pro grammatically to an XML-XSD schema definition?
Thanks!
September 30, 2009 at 8:16 pm
I do not have the answer that you are looking for, however there are a few things that you should be aware of:
1) Replication does not require disk-mapping to work. I have been replication a SQL Server 2000 database back and forth across an extremely locked-down domain boundary for over four years now. All you really need is for the ports used by the replication protocols to be open between the two servers.
2) IIRC, XSD if for defining XML schemas and not relational ones. I am not an expert in XSD, but I do not recall being able to define relations or foreign keys, triggers, views, indexes, etc. In fact I do not even recall being able to define a primary key (I could be wrong though).
As such MS does not have a "Relational Metadata to XSD generator" because its probably not the right target format. A format that can only capture table names. column names and datatypes is of very limited value in the relational world. If you really think that you need this, then I would suggest checking CodePlex, you might find something there. But, if you do, there is every possibility that they will either be using their own XML language or will have custom extensions to XSD to capture more of the relational schema information.
3) Even if you generate the XSD, you still have a problem because there is no MS tool that will implement a relational schema from an XSD. Again, you might get lucky at CodePlex with this too.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2009 at 8:23 pm
And before someone says it, yes I know that the XSD hierarchical definitions can be interpreted as Relations, but they are not the same and there are a lot of common, real-world relationships that this cannot emulate.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 1, 2009 at 10:59 am
RBarry,
I'm sorry that I didn't communicate my thoughts effectively. I'm transporting data across domain boundries with Service Broker, which uses XML messages (2GB max) as it's payload. I need the table definition for the fields as an XML-XSD schema to validate these messages. There is no need for constraints, triggers, or views to be defined. All I'm doing is data validation.
I chose not to use replication because I have a personal bias against it, because I don't like the overhead associated with the Replication GUID field. I'm also constrained in these particular circumstances to not modify the database in any way to enable synchroniztion.
October 1, 2009 at 11:05 am
OK, but why use XML message types in Service Broker, if they aren't helping you (and I don't think that they do in this particular case)? Instead just create a new message type with Validation=None add it to the contract for the service and then send the scripts as NVarchar(MAX)/Varbinary(MAX).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 1, 2009 at 11:10 am
RBarryYoung (10/1/2009)
OK, but why use XML message types in Service Broker, if they aren't helping you (and I don't think that they do in this particular case)? Instead just create a new message type with Validation=None add it to the contract for the service and then send the scripts as NVarchar(MAX)/Varbinary(MAX).
Note that I am only talking about the Table definitions here, not their data.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 1, 2009 at 11:13 am
Brandon Forest (10/1/2009)
I chose not to use replication because I have a personal bias against it, because I don't like the overhead associated with the Replication GUID field.
I think that you'll find that XML encoding has a heck of a lot more overhead than the Replication GUID.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 1, 2009 at 11:21 am
Brandon Forest (9/30/2009)
..My first step is to create validation schemas of each of the tables to ensure that data isn't corrupted in the ETL process.
Hmmm, I just realized what you were saying here, and it's implication.
For what its worth, schema-validation is a poor protection against data corruption and not really what it's intended for. Schema validation is really intended to protect you against variances and changes in the schemas themselves, not in the data. If you want data-validation, then you would be much better off using BINARY_CHECKSUM instead (which coincidentally will also you protect against most of the schema changes as well).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 5, 2009 at 6:04 pm
I found the answer. It is so simple. Nothing special to do, just tell SQL to produce the schema for us. Here’s the code and results:
Brandon Forest
SQL Server DBA
/*** T-SQL ***/
Select Top 0
ApplicationId, UserId, Email
FROM dbo.Membership
FOR XML AUTO, XMLSCHEMA
/*** Results ***/
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="dbo.Membership">
<xsd:complexType>
<xsd:attribute name="ApplicationId" type="sqltypes:uniqueidentifier" use="required" />
<xsd:attribute name="UserId" type="sqltypes:uniqueidentifier" use="required" />
<xsd:attribute name="Email">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="256" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:schema>
/*** Table Def'n ***/
CREATE TABLE [dbo].[Membership](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[Email] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Membership] WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[Membership] WITH CHECK ADD CONSTRAINT [FK__aspnet_Me__UserI__0F975522] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[Membership] CHECK CONSTRAINT [FK__aspnet_Me__UserI__0F975522]
GO
October 5, 2009 at 6:36 pm
Huh, cool. I learned something new too. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply