Monthly Archives: May 2012

PowerShell – It’s All About Automation: Creating a DB and documenting it on SharePoint

I’m sure most of you know that I wear many hats. Sys admin, DBA, Storage admin etc etc. One of the things we’ve recently done here is to setup a central SQL Server because we noticed a lot of the servers had SQL or SQL Express that were not being backed up correctly or just not known about so to manage this we’ve moved them all to one DB cluster.

Because of this I now have to create DB’s on a more regular basis which involved a new DB, a SQL account and documenting it. You ask, why SQL accounts? It’s a slow process to get domain service accounts here, too much of a pain!

So there are 3 steps in this task.

  1. Create a DB
  2. Create the SQL Account (with random password)
  3. Insert data in to company WIKI (This resulted in my modifing a sharepoint wiki post)

I’m not a huge fan of using modules in scripts just to prevent there being any dependencies on them so we’ll need to load up a couple of .NET assemblies for this.

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") [void][Reflection.Assembly]::LoadWithPartialName("System.Web")

SQL and the Web reference.

The System.Web is used to create the password. Sherif Talaat has a neat post on this.

Lets connect in and create that DB. The code assumes you’ve got a db name/instance (just a server name or server\instance name) stored in $dbinstance and it that $dbname has the name of the db you’d like to create.

$dbServer = new-object Microsoft.SqlServer.Management.Smo.Server ($dbInstance)

$db = new-object Microsoft.SqlServer.Management.Smo.Database $dbserver, $dbname $db.create();

$login = new-object $dbserver, $dbname $login.LoginType = 'SqlLogin' $login.DefaultDatabase = $dbname

$pass = [System.Web.Security.Membership]::GeneratePassword(10,0) $login.Create($pass) $db.SetOwner($dbname)

This makes a connection to the SQL server and then creates a new DB object and runs the Create method which injects it in to the SQL Server.

We then go about making a new SQL login and setting its default DB and login type. You’ll notice we use the DBName as the login name, this just makes it easier for tracking what belongs to what. It would be easy enough to change this.

Then we use the static GeneratePassword method in the web.secuirty.membership class to create a password for us and assign that to the login and set the login’s ownership of that DB.

Now we just take the username/db name and password and send it over to our wiki.

I wont repeat the code, since i’ve already documented it but you’ll want to do some string manipulation to make sure it ends up where you want it to. I use a simple template entry I search for in my wiki and then replace it with the template entry and new entry (ie, append)

The point here is that its really easy to make your life easier if you start thinking about some of the small tasks that need to be done and how easily (sometimes) they can be automated!

I’d love to hear about some of the tasks you’ve automated so drop a comment!