Udostępnij za pośrednictwem

Creating a Database Programmatically with SMO and XML

  • Artykuł

I was working on a project with some colleagues about patching systems via Orchestrator.  This brought up the topic of managing state, and to do this one of the best ways is by leveraging a database to record state.  Ultimately we needed to create this database in each new environment and I decided to script it, I wanted it to be extensible no matter what I needed to do.  To do this I ended up leveraging an XML file to provide me the information I need and leveraging SQL SMO to do the hard work.  I figured I would share it with the world in case it can assist someone else.  Special thanks goes to Mitch Klann for assistance with the script.

In my next post I will show how you can also use an xml file to populate the database with values while using SMO.

The XML File

The XML file contains all the elements you need to script a database. Below is the XML file.  Within the Database element there is a section to define the SQLInstance, in this case I am just using a default instance, if you are using an named instance it should be in server\instance format.  The next Name tag is the Name of the database to create.  After that you have the Tables element and within it each table.  Each Table has a Name and then respective columns within it.  The column elements have their name, which would be the column name, an Identity Element, which would be used if you want that column to be an identity (By default it will set the Seed to 1 and increment to 1 but you could customize if you want.  Next is the DataType, this is the type of data that will be in there, you can put whatever datatypes that SQL Supports in there.  Next is Nullable, this allows you to determine whether the column can be empty or not when entering a record.  And lastly is the PK tag, this is a Boolean that allows you to set a column to the Primary Key.

 <?xml version="1.0" encoding="utf-8"?>
<DBCreate version="1.0">
<Database>
<SQLInstance>db01</SQLInstance>
<Name>ExamplePOC</Name>
<Tables>
<Table>
<Name>ServerTBL</Name>
<Columns>
<Column>
<Name>ServerID</Name>
<Identity>True</Identity>
<DataType>Int</DataType>
<Nullable>False</Nullable>
<PK>True</PK>
</Column>
<Column>
<Name>ServerFQDN</Name>
<Identity>False</Identity>
<DataType>NVarChar(100)</DataType>
<Nullable>False</Nullable>
<PK>False</PK>
</Column>
</Columns>
</Table>
<Table>
<Name>ExtraTBL</Name>
<Columns>
<Column>
<Name>ExtraID</Name>
<Identity>True</Identity>
<DataType>Int</DataType>
<Nullable>False</Nullable>
<PK>True</PK>
</Column>
<Column>
<Name>ExtraName</Name>
<Identity>False</Identity>
<DataType>NVarChar(500)</DataType>
<Nullable>False</Nullable>
<PK>False</PK>
</Column>
<Column>
<Name>ServerID</Name>
<Identity>False</Identity>
<DataType>Int</DataType>
<Nullable>False</Nullable>
<PK>False</PK>
</Column>
</Columns>
</Table>
</Tables>
</Database>
</DBCreate>

Next we have the PowerShell script that creates the Database.  Since the script uses SMO it must be ran from a server that has SMO Installed (SQL Servers have this by default).  If you want it to run remotely the easiest way would be to use a PSSession and pass the XML data into your session.

 $xml = [xml] ( get-content C:\scripts\DBCreate.xml)
cls
#Load SQL SMO Assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null

 #define preliminary variables
$sql = $xml.DBCreate.Database.SQLInstance
$dbfound = $false
$ns = 'Microsoft.SqlServer.Management.Smo'
$s = new-object ("$ns.Server") $sql
$DBName = $xml.DBCreate.Database.Name
$dbs = $s.Databases | where {-not $_.IsSystemObject}

#loop through current databases and look to see if DBCreatePOC DB already exists
foreach($db in $dbs)
{
#write-host $db.Name
if($db.Name -eq $DBName)
{
Write-Host "DB Found"
$dbfound = $true
}
}

#If DB doesnt exist create the db
if($dbfound -eq $False)
{
Write-Host "Creating DB"
$database = New-Object -TypeName ("$ns.Database")($s, $DBName)
$database.Create()

$db= New-Object ("$ns.Database")
$db= $s.Databases.Item($DBName)
#Create the table in the dbo schema
$tables = $xml.DBCreate.Database.Tables.Table
#Loop trough tables and create
foreach($table in $tables)
{
$tablename = $table.Name
 $tb = new-object ("$ns.Table")($db, $tablename)
 #Statement to create ServerTBL Table
 $columns = $table.Columns.Column
 foreach($column in $columns)
 {
 $datatype = $column.DataType
 $datatype = invoke-expression "[Microsoft.SqlServer.Management.Smo.Datatype]::$datatype"
 $columnname = $column.Name

 $tempcol = new-object ("$ns.Column")($tb, $columnname, $datatype)

 if($column.Identity -eq "True")
 {
 $tempcol.Identity = $true
  $tempcol.IdentitySeed = 1
 $tempcol.IdentityIncrement = 1
 }

  $tb.Columns.Add($tempcol)
  if($column.Nullable -eq "True")
{
  $tempcol.Nullable = $true
  }
if($column.PK -eq "True")
{
$PK = "PK_" + $columnname
 $idxpk = new-object ("$ns.Index") ($tb, $PK)
 $idxpk.IndexKeyType = "DriPrimaryKey"
 $idxpk.IsClustered = $true
 $idxpkcol = new-object ("$ns.IndexedColumn") ($idxpk, $columnname)
 $idxpk.IndexedColumns.Add($idxpkcol)
 $tb.Indexes.Add($idxpk)

}
 }
  $tb.Create()
 }
 }

Ultimately when you run the script the DB will be created, here is a screenshot from Microsoft SQL Management Studio showing the DB after creation.

image

The files shown in this blog post can be found at the TechNet Gallery