blog @ netnerds.net

21Apr/112

Deploying Maintenance Plans with PowerShell or ASP.NET

I feel that my job as a DBA is very dynamic and usually keeps me busy with new things... except for deploying maintenance plans. I hate making maintenance plans. Luckily, I at least have a template that I can use to keep everything uniform, but it requires me opening up the plan in Visual Studio every time I want to deploy it to a new server. I'm sure there are many different solutions for this problem, but I came up with a very elegant method that I was able to integrate into my intranet server inventory system.

The first thing I did was make a copy of my existing maintenance plan templates. If you don't have a template, you should be able to export an existing maintenance plan out of one of your existing servers. It can be imported into Business Intelligence Studio from the server as well.

Once I had my templates ready, I edited the Local Server Connection and the backup and cleanup task paths. Instead of setting a real server name or backup path, I used placeholders. I went with [SERVERNAME] for the Local server connection and [BACKUPPATH] in the backup and cleanup tasks.

What I didn't realize before was that the DTSX files that I had been editing were pure XML files in disguise. So really all my PowerShell script needs to do is to read in the file, replace text and write it back out to a temporary DTSX file. Once that was written, I could then use the required assembly and methods to read it in as a package and deploy it to my server.

If you are going to be deploying SQL 2008 packages, you need the 10.0.0.0 version of Microsoft.SqlServer.ManagedDTS assembly. Version 10 will handle both 2005 and 2008 SSIS packages. If you only have version 9.0.x.x installed, you will not be able to deploy 2008 packages.

I got the idea and information on the assembly I needed from MidnightDBA.com. Here's some of the PowerShell code:

Since I need to perform the same steps over and over, I created a function to deploy the package and then call the function for each maintenance plan I want to deploy.




#Load up the assembly

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS") | Out-Null;


#Create function that can be called

function createPackage

{param ($filename, $foldername, $sqlversion)


# Read in my maintenance plan template and replace the place holders

$packageContents = [IO.File]::ReadAllText(".\$sqlVersion\$filename")

$packageContents = $packageContents.Replace("[SERVERNAME]","$ServerName")

$packageContents = $packageContents.Replace("[BACKUPPATH]","$backupPath\$foldername")


# Write the new package.  MUST use UTF8 encoding or it will break.

Set-Content -Encoding utf8 ".\out.dtsx" $packageContents


# Load the new package

$Loadedpkg = $app.LoadPackage($pkg, $null, $null)

$Pkgname = $Loadedpkg.Name


#Save the maintenance plan to the server

$app.SaveToSQLServerAs($Loadedpkg, $null, "\\Maintenance Plans\$Pkgname", $ServerName, $null, $null)

Write-Host "$Pkgname deployed to $ServerName"

}


#Now for the main program code...

$app = New-Object Microsoft.SqlServer.Dts.Runtime.Application;

$Loadedpkg = New-Object Microsoft.SqlServer.Dts.Runtime.Package;

$DtsxPath = ".\"

$pkg = "$DtsxPath\out.dtsx"


# If you need to deploy both versions of SQL like me,

# you can have sub-folders with templates for each version if you like.

$SQLVersion = Read-Host "2005 or 2008?"


if($SQLVersion -ne "2005" -AND $SQLVersion -ne "2008")

{

Write-Host "$SQLVersion is not a valid option."

exit

}


#Get the server name to deploy to...

$ServerName = Read-Host "Enter name of SQL Server"

$ServerName = $ServerName.ToUpper()


# pass the name of the template file, the subfolder in your backup path, and the version of SQL.

# i typically have a User folder for user database backups and a System folder for System database backups.

createPackage "HistoryCleanupTemplate.dtsx" "" "$SQLVersion"

createPackage "SystemDBMaintTemplate.dtsx" "System" "$SQLVersion"

createPackage "UserDBMaintTemplate.dtsx" "User" "$SQLVersion"

createPackage "UserTLogMaintTemplate.dtsx" "User" "$SQLVersion"

Voila - your plans should be pushed out to the server.

This code can easily be converted to ASP.NET, which I decided was the better solution. This allows me to deploy maintenance plans directly from a webpage, instead of having to use PowerShell on a computer that may not have the proper assemblies installed. This does require installing Integration Services onto your web server, however.

I have simplified the code to the bare essentials. I'm not doing error checking or anything fancy. Here are the functions that would go in the page code. You are responsible for figuring out how you want the whole thing to work, but I have a basic web page created that allows me to enter the name of my SQL Server (text box), the backup path (text box or dropdown) and the version of SQL Server (dropdown) to determine which templates to use. Finally, I have a label at the bottom of the page that I can use to provide the user with information. I should also mention that your ASP.NET application pool account will need to be able to write the temporary DTSX file out to a folder location.



Protected Sub deployBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles deployBtn.Click

resultsLabel.Text = ""

Dim SQLVersion As String = versionDDL.SelectedValue

'You will retrieve this value from a textbox or dropdown if it changes from server to server.

Dim backupPath As String = "<enter your backup path here>"  


'If you wish, you can create the paths with the script to avoid having to do it manually.

IO.Directory.CreateDirectory(backupPath & "\User")

IO.Directory.CreateDirectory(backupPath & "\System")


 'Log your work to a simple label so that the user has confirmation of their actions

resultsLabel.Text &= "Backup directories created for " & ServerName & "<BR />"

resultsLabel.Text &= createPackage(ServerName, backupPath, "HistoryCleanupTemplate.dtsx", "", SQLVersion)

resultsLabel.Text &= createPackage(ServerName, backupPath, "SystemDBMaintTemplate.dtsx", "System", SQLVersion)

resultsLabel.Text &= createPackage(ServerName, backupPath, "UserDBMaintTemplate.dtsx", "User", SQLVersion)

resultsLabel.Text &= createPackage(ServerName, backupPath, "UserTLogMaintTemplate.dtsx", "User", SQLVersion)


IO.File.Delete(Server.MapPath(".\") &amp; "out.dtsx")

End Sub


Function createPackage(ByVal serverName As String, ByVal backupPath As String, ByVal filename As String, ByVal folderName As String, ByVal sqlVersion As String) As String

' Read in your package as text out of either a 2005 or 2008 folder containing the templates.

' I assume the folders are in the same location as your asp.net files

Dim packageContents As String = IO.File.ReadAllText(Server.MapPath(".\" & sqlVersion) & "\" & filename)


' Replace your placeholders with the Server and backup paths

packageContents = packageContents.Replace("[SERVERNAME]", serverName)

packageContents = packageContents.Replace("[BACKUPPATH]", backupPath & "\" & folderName)


' Set the path to write your temporary DTSX file.

Dim DtsxPath As String = Server.MapPath(".\")

Dim pkg As String = DtsxPath &amp; "out.dtsx"


' Write the new package to the disk with UTF8 encoding.

IO.File.WriteAllText(pkg, packageContents, System.Text.Encoding.UTF8)


' Create your SSIS objects

Dim app As New Microsoft.SqlServer.Dts.Runtime.Application

Dim Loadedpkg = New Microsoft.SqlServer.Dts.Runtime.Package

Dim pkgName As String


' Load your new temporary package from disk.

Loadedpkg = app.LoadPackage(pkg, Nothing, True)

pkgName = Loadedpkg.Name


' Deploy to SQL Server

app.SaveToSqlServerAs(Loadedpkg, Nothing, "\\Maintenance Plans\" &amp; pkgName, serverName, Nothing, Nothing)


' I choose to return the results of the function as a string with a success message

createPackage = pkgName & " deployed to " & serverName & " successfully.<BR />"


End Function

That just about covers it. As I said previously, I added this functionality into an intranet inventory system I created to manage all of my SQL Servers. When I add a new server to my inventory, I click a link to deploy maintenance plans and move on about my day. I still have to go in just to make sure my proper databases are selected, but for the most part it takes just a few minutes compared to the 15 to 20 minutes I was spending before.

Posted by: Brandon Abshire Filed under: ASP.NET, PowerShell, SQL Server 2 Comments

30Sep/101

Modifying Data in an Oracle Database Using ASP.NET and ODP.NET

In all my years of web programming, I've never used an Oracle database... until now. I was recently tasked with converting an Access database form into a web application using ASP.NET. The twist is that the data in the Access form is linked to an Oracle database. It would be an understatement to say that Oracle is picky, so I felt the need to document all of the steps I had to take to modify or insert data into the Oracle database. Let me say now that there are multiple ways to connect to databases in ASP.NET (ODBC, OLEDB, Native Clients...) and each method may result in different issues. This article deals specifically with ASP.NET using the Oracle ODP.NET Data Client.

1. You will need to install the Oracle client on both your development machine and the machine that will host the ASP.NET web application.

- When installing the client, choose the Administration option, (which is about 1GB) instead of the Instant Client. The Administration option will give you the ODP.NET functionality that ASP.NET will want to use to connect to the data source. Without it, you will need to use OLEDB or ODBC connections, which is outside the scope of this post.

- I had issues getting the web server to work after installing the Oracle Client. My web page kept telling me that Oracle Client 8.x.x or higher was required. Try rebooting the web server. I restarted IIS multiple times, but it wouldn't work for me. Then the next Monday I came back into work and it was working magically. We had patching over the weekend, so I assume the reboot is what fixed the issue.

2. Once you have the Client installed, you should be able to start building your app. Aside from all of the HTML encoding that happens on the Oracle queries, it should work pretty much the same as a SQL Server database at this point. Add a gridview and tie the datasource to your Oracle database to verify things are working.

3. Now the hard part... Updating or Inserting data into the database.

- More times than not, I find myself having to override the built-in functionality of ASP.NET and do custom scripting to get what I need. Since everyone programs differently, I'm not going to spend a lot of time explaining why or what I was trying to do in my code, but only what steps I had to take to get the data to update or insert.

- If you try to update or insert data to your tables, you are quite likely to see this error: ORA-01036: illegal variable name/number

4. Here are the things I had to change to get rid of this error:

- In your (GridView/DataList/FormView) code, change all Binds to Evals. In my case, I had template fields, so I only needed to change the code within the Edit and Insert templates. The ItemTemplates did not matter.

- In the code for your DataSource, add ConflictDetection="OverwriteChanges"

- I also had to clear all update and insert parameters before the update or insert executed against the database. When Visual Studio generated my code, it supplied Update/Insert/Select Parameters that get passed back to the database on updates and inserts. I wanted to make sure that was all cleared out so I could control what was happening myself.

Private Sub myGridView_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles myGridView.RowUpdating myDataSource.UpdateParameters.Clear() myDataSource.UpdateCommand = "" myDataSource.UpdateCommand = "my custom update command here" End Sub

- Finally, if you have tried all of the above and you are still getting errors on update or insert, try removing the "DataKeyNames" from the GridView/DetailsView/DataView/Whatever, at least for any fields you may be trying to update.

After making all of these changes, I was able to update and insert data into the Oracle database and my ASP.NET web app is coming along quite nicely!