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 Microsoft.sqlserver.management.smo.login $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!


About jrich

I am the Solutions Architect for Apex Learning in Seattle WA. I've been working with computers since I was 13. Started programming when I was 14. Had my first IT job as tech support at an ISP at the age of 15 and became a network admin at the age of 17. Since then I've worked at a variety of small to mid size companies supporting, maintaining and developing all aspects of IT. Mostly working with Windows based networks but have recently been working with Solaris system as well. I created this blog mostly as a place for me to take my own notes, but also share things that I had a hard time finding the info for.

Posted on May 21, 2012, in WMF (Powershell/WinRM) and tagged , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: