Monday, May 25, 2009

SQL database deployment automation

Previous part is HERE.

Before deploying Web Portal, we have to set up the database: Which web portal haven't a database... ? :)

So, that's the first job of our Solution Accelerator. In the previous post, you have seen the graphical user interface: let's use it !

So, just click on the first menu to deploy database !

A new dedicated screen is displayed:

It is a very simple GUI!
SQL server name and instance name are the only require fields and all we need is to click on the OK button...

However, there is one thing we can do using this GUI: configuring the user account which will be use to deploy the database. This can be done on the top of the window: we can choose if you want to use windows authentication (current logged user) or an other account.

At the bottom of the window, there is an empty space... So, we won't just keep this space empty! In fact, this area is dedicated to log: all information, warnings, errors will be display here.

Each step, each traitment of the script is described and displayed in the log area. That's the Display-Info and Display-Error functions job:

### <summary>
### Adds a log.
### </summary>
### <param name="str">Message to add</param>
### <param name="strType">Message type</param>
function Log-Message($str, $strType)
{
    $strDate = [DateTime]::Now.ToString('G')
    $strType = $strType.ToUpper()
    $strLine = "[$strDate] $strType: $str"
    Write-Verbose $strLine
    $script:strLog += $strLine + "`r`n"
}

### <summary>
### Displays error message.
### </summary>
### <param name="str">Message to display</param>
function Display-Error($str)
{
    $LblInfo.Text = ''
    $LblError.Text = $str
    $LblError.Refresh()
    Log-Message $str 'Error'
}

### <summary>
### Display information message.
### </summary>
### <param name="str">Message to display</param>
function Display-Info($str)
{
    $LblInfo.Text = $str
    $LblInfo.Refresh()
    Log-Message $str 'Info'
}

All theses displayed messages are logged thanks to Log-Message function which adds a date to the message.
Well, logging is very helpful to debug !

Since we saw the GUI and we have all prerequires we need: SQL scripts which will be executed, let's see the most interesting part: How to deploy the database using PowerShell!

First, we have to check that we can access to the SQL scripts.
We declare some variables in the script scope:

### <summary>
### Path to current script.
### </summary>
$script:strPath = $MyInvocation.MyCommand.Path

### <summary>
### SQL scripts to execute during installation step.
### Order is essential.
### </summary>
$script:arrSQLGenerateDBScripts = 'CreateDatabase.sql',
                                  'ProvisioningDatabase.sql'
### <summary>
### Installation logs.
### </summary>
$script:strLog = ''

We used the $MyInvocation PowerShell builtin variable to get our last executed command line: usefull to get the full path where the script is located!

arrSQLGenerateDBScripts variable contains an ordered list of SQL scripts to execute.

Then, strLog variable is a simple string which is our log.

The first thing to do is to check the SQL scripts access:

### <summary>
### Checks SQL files.
### </summary>
### <return>True on success, false otherwise</return>
function Check-SQLFiles()
{
    trap {
        $blnRes = $false
        continue
    }
    $tab = $script:strPath.Split('\')
    $strPath = ''
    for ($i = 0$i -lt $tab.length - 2$i++) {
        $strPath += $tab[$i+ '\'
    }
    $blnRes = $true
    $bln = $true
    Display-Info "Checking files in $strPath..."
    if ($script:arrSQLGenerateDBScripts.Length -gt 0) {
        foreach ($strFile in $script:arrSQLGenerateDBScripts) {
            $bln = Test-Path "$strPath\Database\$strFile"
            if ($bln -eq $false) { break }
        }
        $blnRes = $blnRes -and $bln
    }
    if ($blnRes) {
        Display-Info "Installation files successfully checked"
    } else {
        Display-Error "Installation files missing."
    }
    
    return $blnRes
}

This function checks that scripts' names stored in the script variable exist and are located in the ../Database folder.

Now, it's time to execute the SQL scripts!
We have to connect to the SQL database using a connection string:

### <summary>
### Generate the SQL connection string.
### </summary>
### <param name="strDB">Database name</param>
### <return>SQL connection string</return>
function Generate-StringConnection($strDB)
{
    $strInstance = $TxtDBServer.Text
    if ($TxtDBInstance.Text.Length -gt 0) {
        $strInstance += '\' + $TxtDBInstance.Text
    }
    $strInstance = $strInstance.ToUpper()
    $objBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
    $objBuilder['Application Name'= 'Web Portal'
    $objBuilder['Data Source'= $strInstance
    $objBuilder['Initial Catalog'= $strDB

    if ($ChkWindowsAuth.Checked -eq $false) {
        $objBuilder['User Id'= $TxtLogin.Text
        $objBuilder['Password'= $TxtPassword.Text
    } else {
        $objBuilder['Integrated Security'= $true
    }

    return $objBuilder.ConnectionString
}

This function needs a database's name which will be used by the "Initial Catalog" parameter of the connection string.
If no SQL instance's name is provided, the server's name is used (default instance).

Then, we can write the function to execute the SQL scripts:

### <summary>
### Executes a SQL script.
### </summary>
### <param name="strScript">Script full name</param>
### <param name="objSQL">SQL connection</param>
### <return>True on success, false otherwise</return>
function Execute-SQLScript($objSQL, $strScript)
{
    trap {
        Display-Error $_.Exception.Message
        $bln = $false
        continue
    }
    $bln = Test-Path $strScript
    if ($bln) {
        Display-Info "Executing script $strScript..."
        $objSQLCmd = New-Object System.Data.SqlClient.SqlCommand
        $objSQLCmd.Connection = $objSQL
        $strContent = ''
        foreach ($strLine in Get-Content $strScript) {
            if ($bln -eq $false) { return $bln }
            if ($strLine.ToUpper() -match "^\s*GO\s*$") {
                $objSQLCmd.CommandText = $strContent
                $objSQLCmd.ExecuteNonQuery() | Out-Null
                $strContent = ''
            } else {
                $strContent += $strLine + "`r`n"
            }
        }
    } else {
        $LblError.Text = "File $strScript not found"
    }
    
    return $bln
}

There are more than one method to execute a SQL script. Here, we read the SQL script file line per line and execute the SQL request if we find the "GO" instruction.

Oh ! That's a chance to explain some regular expression basis !

"^\s*GO\s*$" string means that we search a string containing a GO surrounded by spaces (or not)!

Because it is relevant to know if the function has successfully executed the script, it returns a boolean :)

Well, at this point we have all elements to deploy the database:

### <summary>
### Installs the SQL database.
### </summary>
function Install-Database()
{
    trap {
        Display-Error $_.Exception.Message
        if (($objSQL -ne $null-and
            ($objSQL.State -eq [Data.ConnectionState]::Open)) {
            $objSQL.Close()
        }
        $bln = $false
        continue
    }
    $LblError.Text = ''
    $objSQL = New-Object System.Data.SqlClient.SqlConnection
    $objSQL.ConnectionString = Generate-StringConnection 'master'
    Display-Info "Opening database connection..."
    $objSQL.Open()
    if ($objSQL.State -eq [Data.ConnectionState]::Open) {
        $strPath = Get-SQLScriptPath
        foreach ($strScript in $script:arrSQLGenerateDBScripts) {
            $bln = Execute-SQLScript $objSQL "$strPath\$strScript"
            if ($bln -eq $false) { break }
        }
        $objSQL.Close()
        Display-Info "Database connection closed"
        if ($bln) {
            Display-Info "Database successfully installed"
        } else {
            Display-Error "Error executing script $strPath\$strScript."
            Display-Info "Database installation aborted"
        }
    }
}

This function uses the previously detailed functions and deploy the database in few steps:
  • Connection string creation
  • Database connection opening
  • SQL script execution
  • Database connection closing
All theses steps are described in the log area of the window.

Next step is to deploy our Web Portal: IIS Automation !
See you soon.

No comments:

Post a Comment