Now that we have come this far, it is time to start creating objects using SMO and PowerShell. This level will show how to create objects including a login, a database, a filegroup, a file a database user and then we’ll end with a table and an index. Let’s get started. In the listings below, take note of the version of SMO library. If you have SQL 2008R2 installed, change the 12.0.0.0 to 10.0.0.0 and if you have SQL 2012 then change it to 11.0.0.0 and leave it for SQL 2014. Also make sure to replace “S1” by your instance name, or remove it if you are using the default instance.

Create a Login

As you may recall from the previous levels, we need a couple of elements to get started. The first is a Server object so that we have a target for creating the login. The second is a Login object in SMO so that we can set properties and ultimately create the Login in the server. Listing 9.1 shows the steps of creation of the login. Remember you need to load the assembly at the very beginning to ensure that you have access to the Login class in SMO. In addition to editing this script to replace “S1” with your instance name, feel free to replace “mylogin” and “ThisIsMyPassword1” with a login name and password of your choosing.

Add-Type –AssemblyName “Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”
Add-Type –AssemblyName “Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”
$class = “Microsoft.SqlServer.Management.Smo.”
$server = new-object -TypeName “$($class)Server” -ArgumentList "LOCALHOST\S1" # put your sql server instance in place
$login = New-Object –TypeName “$($class)Login”
# first property you need to set is Parent which is a server object
$login.Parent = $server
# next property that needs to be set before anything else
$login.Name = “mylogin”
# types of logins AsymmetricKey, Certificate, SqlLogin, 
# WindowsLogin, WindowsGroup
$login.LoginType = “SqlLogin”
$login.PasswordPolicyEnforced = $false
# This creates the login with the specified password.
$login.Create(“ThisIsMyPassword1”)
$login | Select Name, LoginType, CreateDate, DefaultDatabase | ft * -AutoSize

Listing 9.1 Create Login

You can see that there are a few steps to create a login but this could easily be encapsulated into a function, as described in level 5. The function could take parameters for Name, Server and LoginType and also a parameter specifying the password policy being enforced. (You can consider that a homework exercise for this level if you like.) For this first listing, I will show the output and verify that the login was created. The results are in Figure 9.1.

Figure 9.1 Results of Login Creation

Create a Database

Now it is time to go a little further with an advanced object and create a database. As I said in the previous example I will outline the requirements, and show you the basic code, and you can take it further and create a function to do the job. I will also leave it to you to verify that the database was created as specified. . The basic components which need to be specified in order to create a database are shown below.

Objects needed in a database:

• Server object to contain the database
• Database object to create
• Filegroup object to hold files
• File object
• Logfile object

Think of a database object that you create by right-clicking on the Databases container in Management Studio. Take a look at Figure 9.2 and notice all my red arrows. There are a lot of properties to specify, but you need to create each object and set the properties to properly create a database yourself. Management Studio does the same thing as you will do, except you do not have a dialog box to help you enter the properties. But take a look at the properties and look at the code in Listing 9.2 for how this is done.

Figure 9.2 Create Database Dialog in Management Studio

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$class = "Microsoft.SqlServer.Management.Smo"
$server = New-Object -TypeName "$class.Server" -ArgumentList "LOCALHOST\S1" # put your instance name in this place
$db = New-Object -TypeName "$class.Database" -ArgumentList $server, "MyDB"
$filegroup = New-Object -TypeName "$class.Filegroup" -ArgumentList $db, "PRIMARY"
$db.FileGroups.Add($filegroup)
$file = New-Object -TypeName "$class.DataFile" -ArgumentList $filegroup, "$($db.Name)_Data"
$file.Size = 256000
$file.GrowthType = "KB" 
$file.Growth = 256000
$file.MaxSize = 20202020
$file.FileName = "C:\SQLDATA\MyDB.mdf"
$filegroup.Files.Add($file)
$logfile = New-Object -TypeName "$class.LogFile" -ArgumentList $db, "$($db.Name)_log"
$logfile.Size = 128000
$logfile.GrowthType = "KB" 
$logfile.Growth = 128000
$logfile.MaxSize = 20202020
$logfile.FileName = "C:\SQLDATA\MyDB_log.ldf"
$db.LogFiles.Add($logfile)
$db.Create()

Listing 9.2 Create a Database with SMO

Notice all the pieces in the dialog are accounted for in Listing 9.2. If you run these statements after substituting your server/instance name and the path to your data files, you will find a new database in your databases list. Remember are that these are the minimal properties needed to create files, filegroups and finally a database.

Intermission

Before we complete the level I want to recap the topics in this level. You need to add SMO to your PowerShell environment, then create objects with New-Object. You then start setting properties and finally use the CREATE method to make the database appear. The final two objects we will create are going to be a user in the database for the login we created earlier, a table object in the database and an index on the table. There are many more objects that can be created with SMO but this will be something that you can work out on your own after reading this level.

Create a Database User

The script for creating a database user object needs to supply a database and a login object. In Listing 9.3 you will see the code that will create a database user for the login. A database user created through SMO needs a database and a login specified, just as when creating a user through SQL Server Management Studio

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$class = "Microsoft.SqlServer.Management.Smo"
$server = New-Object -TypeName "$class.Server" -ArgumentList "LOCALHOST\S1"  #put your own server name in
$db = $server.Databases["MyDB"]
$name = "mylogin"
$login = "mylogin"
$user = New-Object -TypeName "$class.User" -ArgumentList $db, $name
$user.Login = $login
$user.DefaultSchema = 'dbo'
$user.Create()

Listing 9.3 Create Database User for ‘mylogin’

When you execute this script, and maybe later turn it into a function, you will get a user name ‘mylogin’ and it will be mapped to a login name ‘mylogin’ with a default schema of ‘dbo’. The user will be created in the database MyDB. It may seem like there is an enormous amount of things you have to do to create objects, but if you take a look, there are just a few properties that need to be set, and by using functions in a module, you can load it and then call the functions when needed. The work gets simpler by passing parameters and working smarter, not harder. Clicking through Management Studio works too, but when your number of servers expands, you end up with an enormous number of clicks. I prefer to take the simpler way out.

Create a Table

To continue on with the object creation, you will build a table with SMO now. This table called Table1 will have two columns called Table1Id and Name. We use Table1Id as the primary key in the next section. The code is in listing 9.4. The objects needed are the database you just created and a table object. We will add a column object to this table and then use the Create method to create the table.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$class = "Microsoft.SqlServer.Management.Smo"
$server = New-Object -TypeName "$class.Server" -ArgumentList "LOCALHOST\S1"  #put your own server name in
$db = $server.Databases["MyDB"]
$Table1 = New-Object -TypeName "$class.Table" -ArgumentList $db, "Table1"
$Table1Id = New-Object -TypeName "$class.Column" -ArgumentList "Table1, $Table1Id"
$DataTypeInt = New-Object -TypeName "$class.DataType" -Argumentlist "Int"
$Table1Id.Nullable = $false
$Table1Id.DataType = $DataTypeInt
$Table1.Columns.Add($Table1Id)
$Name = New-object -TypeName "$class.Column" -ArgumentList $Table1, "Name"
$DataTypeVarChar = New-Object -TypeName "$class.datatype" -ArgumentList "VarChar", 50
$Name.Nullable = $true
$Name.DataType = $DataTypeVarChar
$Table1.Columns.Add($Name)
$Table1.Create()

Listing 9.4 Create Table with SMO.Table

Create an Index

To finish off this level, we’ll create an index on an existing table. In the creation of this object, you need a table which you just created above. It is in the database that we created in this level. Let’s take a look at how the Index object is created and attached to a table. In Listing 9.5 you will see the code necessary to create an Index with SMO.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$class = "Microsoft.SqlServer.Management.Smo"
$server = New-Object -TypeName "$class.Server" -ArgumentList "LOCALHOST\S1"
$db = $server.Databases["MyDB"]
$table = $db.Tables["Table1"]
$index = New-Object -TypeName "$class.Index" -ArgumentList $table, "NewIndex"
$col1 = New-Object -TypeName "$class.IndexedColumn" -ArgumentList $index, "Table1Id", $true
$index.IndexedColumns.Add($col1)
$index.IndexKeyType = [Microsoft.SqlServer.Management.Smo.IndexKeyType]::DriPrimaryKey
$index.IsClustered = $true
$index.Create()

Listing 9.5 Create Index

Summary

To recap what we have seen in this level, we can create objects as well as interrogate them with SMO and PowerShell. We have created a login, a database, a database user, a table and an index. all using the SMO library and PowerShell.. As you have gone through this level, I hope you have thought of more tasks that you can perform with SMO to make your life much simpler. Take on the challenge to turn these scripts into functions or your own little module. Be sure you take advantage of the SQLPSX module at http://sqlpsx.codeplex.com before you go making your own module, because there might already be code that does what you want. This module has a lot of great functions to help you out in your database administration duties. Have a great day!