Biml Database Inspection

Importing tables via Biml

I've mentioned how using Biml to reverse engineer a very large database was the only option and there is plenty of great material in the community about how to do this but one thing I kept stumbling over was the using the import methods to build the above Biml always seemed to fail somewhere along the way. I assumed it was just me not understanding how it works. But, today someone else got bit with the same stumbling block so I wanted to talk through the basics of how the modeling works within Biml and subsequent posts will show the source of the issue and a work around.

Preface

Biml allows you to define the tables, views, and constraints in your database. Let's look at a minimal viable table definition for dbo.AWBuildVersion from AdventureWorks2014. Ready?

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Adventureworks" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=AdventureWorks2014;Provider=SQLNCLI11;Integrated Security=SSPI;" />
    </Connections>
    <Databases>
        <Database ConnectionName="Adventureworks" Name="AdventureWorks2014" />
    </Databases>
    <Schemas>
        <Schema Name="dbo" DatabaseName="AdventureWorks2014" />
    </Schemas>
    <Tables>
        <Table Name="AWBuildVersion" SchemaName="AdventureWorks2014.dbo">
            <Columns>
                <Column Name="SystemInformationID" DataType="Byte" IdentityIncrement="1" />
                <Column Name="Database Version" DataType="String" Length="25" />
                <Column Name="VersionDate" DataType="DateTime" />
                <Column Name="ModifiedDate" DataType="DateTime" />
            </Columns>
            <Keys>
                <PrimaryKey Name="PK_AWBuildVersion_SystemInformationID" Clustered="false">
                    <Columns>
                        <Column ColumnName="SystemInformationID" />
                    </Columns>
                </PrimaryKey>
            </Keys>
            <Indexes>
            </Indexes>
        </Table>
    </Tables>
</Biml>

Wow, that's a lot! Let's break it down.

Connections

Our Connections collection has a single entity in it, an OLE DB Connection named Adventureworks (remember, all of this is case sensitive so this Adventureworks is a different beast from AdventureWorks, ADVENTUREWOKRS, etc). This provides enough information to make a database connection. Of note, we have the server and catalog/database name defined in there. Depending on the type of connection used will determine the specific name used i.e. Initial Catalog & Data Source; Server & Database, etc. Look at ConnectionStrings.com if you are really wanting to see how rich (horrible) this becomes.

Databases

A Database (AstDatabaseNode) requires a Name and a ConnectionName. We certainly know the connection since we just defined it in the previous section and so here I'm naming the Database AdventureWorks2014. This just happens to align with the value specified in Initial Catalog but use whatever is natural. Do not name it after the environment though, please. There is nothing worse than talking about an entity named "DevSalesDb" which is referencing the production database but named after the location it was built from.

Schemas

A Schema (AstSchemaNode) requires a Name and a DatabaseName (see above). Since I'm after a table in the dbo schema, I just specify it as the name.

Tables

Finally, the Table (AstTableNode) which requires a Name and a SchemaName. Where have we seen this pattern? However, look at the value of the SchemaName. We have to qualify the schema with the database because we could have two Schema entities in dbo that point to different Database entities.

Once inside the Table entity, we can define our columns, keys, indices as our heart desires.

Wrap up

An amusing side note, if you're using Mist/BimlStudio to import the Schema and Table, the wizard renders all of this correctly, there only seems to be a defect in how I'm scripting the above entities.