I received an amazing email this morning from Microsoft MVP Community stating the following:
I am very humbled and honored to receive this honor and joining some amazing people in this community! To everyone that nominated me and those that have enjoyed my work, I want to say Thank You! I am looking forward to continuing my blogging, posting various scripts that I write, continuing work on my codeplex projects and help others out with various PowerShell questions. Again, Thank You!!
Continuing with my little SQL and PowerShell series, I am going to talk about how you can use PowerShell to explore SQL databases using SMO.
As shown in the previous articles, I will make my initial connection to a SQL server.
## Add Assemblies
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
## Connect to SQL Server
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server('Boe-PC\SHIRE')
Viewing all of the databases and their properties is as simple as doing this:
$sqlServer.Databases
Of course, you get EVERYTHING with this. It may be better to filter out only the properties I need.
##Databases $sqlServer.Databases | Format-Table Name,Owner,Status,LastBackupDate, LastDifferentialBackupDate,LastLogBackupDate, RecoveryModel,DataSpaceUsage -AutoSize
Much better. Here we can see some relevant information such as when backups have last taken place as well as the recovery model for each database among other things. Of course, you can choose what you feel like are the most important things to view.
Typically when working with objects and you want to only get a specific item from the collection, you would need to do a Where-Object and filter for a specific property (Name in this case). Luckily, you don’t have to do this at all. You can just run the following command to grab a lone database.
$db = $sqlServer.Databases['AdventureWorks'] $db.name
Now I have access to a single database to work with and view other interesting things on it.
Something that I think is really cool is that you can generate the TSQL code to create the database just by using the Script() method.
#Show Database TSQL Script $db.Script()
Pretty cool stuff if you wanted to create another database somewhere else.
Finding all of the users that have logins added to a specific database can be found on the Users property. Within that object is a method called EnumRoles() which will also show you what roles each user is in.
#Show Users with their roles
$db.Users | ForEach {
[pscustomobject] @{
Name = $_.Name
Login = $_.Login
LoginType = $_.LoginType
UserType = $_.UserType
HasDBAccess = $_.HasDBAcces
CreateDate = $_.CreateDate
Database = $_.Parent
Roles = $_.EnumRoles()
}
} | Format-Table -AutoSize
This provides a nice way to track who has access to what databases on your SQL Server.
In the flip side, you can also look at the available roles on the database.
#Roles
$db.Roles | ForEach {
[pscustomobject] @{
Name = $_.name
Database = $_.Parent
CreateDate = $_.CreateDate
LastModified = $_.DateLastModified
Owner = $_.Owner
Users = $_.EnumMembers()
}
} | Format-Table -AutoSize
If you want to check out the schemas on the database, you can run this.
#Look at schemas $db.Schemas | FT Name,Parent,Owner -AutoSize
Let’s take a look at the permissions on the database.
#Database Permissions $db.EnumDatabasePermissions() | Format-Table Grantee,GranteeType,PermissionType, PermissionState,Grantor -AutoSize
We can also look at any locks that are on the database using the EnumLocks() method.
#Database Locks $db.EnumLocks() | Format-Table -AutoSize
Depending on the database, you may have some database logon mappings.
$sqlServer.Databases.EnumLoginMappings()
You can check to see if any transactions are running and then view those transactions on the database.
If ($db.GetTransactionCount() -gt 0) {
$db.EnumTransactions()
}
Depending on what is happening, you may or may not get anything. In my case, I ran the following command to ‘monitor’ transactions to show what is happening when I run a simple TSQL command to inserta row.
while ($True) {$db.EnumTransactions()}
You can view all of the objects on a database using the EnumObjects() method.
$db.EnumObjects()
Yes, there will be a lot of objects returned when you run this command.
If you want to know the size of your data files (both .mdf and .ldf) as well as what their growth rate might be, then the following lines of code will be pretty useful for you.
First off is the database file/s. First we can check out the FileGroups property to see how many file groups we have associated with this database.
$db.FileGroups
Only one file group to work with here. We can dig deeper by looking at the Files property to see more information about the database file.
$db.FileGroups.Files
Here you can see the actual size of the file (Size) and the size actually being used by the database (UsedSpace). Also, you are able to see more information like its growth rate. Lets do some formatting to make this a little easier to read.
$db.FileGroups.Files | Select FileName,Name,
@{L='FileSizeMB';E={[math]::Round(($_.Size * 1024)/1MB,2)}},
@{L='DatabaseSizeMB';E={[math]::Round(($_.UsedSpace * 1024)/1MB,2)}},
MaxSize,@{L='PhysicalDiskFreeSpaceMB';E={[math]::Round(($_.VolumeFreeSpace * 1024)/1MB,2)}},
Growth,GrowthType | Format-Table -AutoSize
Here you can see where the file is located at, the size of the file (4MB) as well as how much of that size is actually being used by the database (2.88MB). The max size is –1, basically meaning that this thing is a goldfish and will grow to the size of the bowl (or drive in this case) if allowed. We can also see that this will grow by 10% each time its DatabaseSizeMB out-grows the FileSizeMB. So it may be a good idea to not only give it a maxsize but to also change the growth rate on this database to prevent a potential disaster.
Not to be left out, we can also check out the transaction log size in a similar way by looking at the LogFiless property of the database.
$db.LogFiles
Much like the database files, lets format it a little.
$db.LogFiles | Select FileName,Name,
@{L='FileSizeMB';E={[math]::Round(($_.Size * 1024)/1MB,2)}},
@{L='TLogSizeMB';E={[math]::Round(($_.UsedSpace * 1024)/1MB,2)}},
MaxSize,@{L='PhysicalDiskFreeSpaceMB';E={[math]::Round(($_.VolumeFreeSpace * 1024)/1MB,2)}},
Growth,GrowthType | Format-Table -AutoSize
You can even run TSQL using the ExecuteWithResults() method. In this case I want to see all of the tables on this database.
$db.ExecuteWithResults('SELECT * FROM INFORMATION_SCHEMA.TABLES').Tables.Rows
I will be finishing up this article by showing you how you can create a database, make some configuration changes, verify it exists and then removing the database from the SQL server.
First, lets create a database on the SQL server..
#Create a database quickly
([Microsoft.SqlServer.Management.Smo.Database]@{
Name ='ProxTest'
Parent = $sqlServer
}).Create()
Now we can make some configuration changes to this database. But first we need to show the settings prior to making the change.
$newDb | Format-Table Name,Owner,Auto*Statistics* -AutoSize
Now we can make some changes.
$newDB.SetOwner("boe-pc\Administrator")
$newDB.AutoCreateStatisticsEnabled = $True
$newDB.AutoUpdateStatisticsAsync = $True
$newDB.AutoUpdateStatisticsEnabled = $True
$newDB.Alter()
The Alter() is important because if I don’t call it, no changes will be made to the database. With that, lets check out those properties again.
$newDb | Format-Table Name,Owner,Auto*Statistics* -AutoSize
As you can see, the properties have been updated with a new owner as well as some statistics options.
Now, I really don’t need this database, so lets use the Drop() method to remove the database.
$newDB.Drop() $sqlServer.Databases.name
No more database!
That is all for this article on exploring the database using SMO. The next article will talk about backing up and restoring a database using SMO.
Continuing on from my previous article where I showed you how to make an initial connection to a SQL server using SMO, this article aims to dive deeper into the Microsoft.SqlServer.Management.Smo.Server object to view the configuration and available methods of the server.
First thing is to load the assemblies and make the initial connection to the SQL server.
## Add Assemblies
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
## Connect to SQL Server
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server('Boe-PC\SHIRE')
Just by itself, the SQL server object has a lot of properties.
Just a few of the properties available are shown below.
You can even enumerate through all of the databases via the database property. This will display the Microsoft.SqlServer.Management.Smo.Database objects. I won’t dive too deep into the database objects for this article, but will for a future article.
$sqlServer.Databases | Select Name
SQL Server roles can also be looked at as well.
$sqlServer.Roles | Select Name
Same goes for SQL logins on the server.
$sqlServer.Logins | Select Name
All of these will be looked at in greater detail in future articles. The main point of this is to show you what is available at the root of the SQL server object.
Digging deeper into the configuration using the Configuration property, we can really take a look at some useful properties that can even be configured.
$sqlServer.Configuration
As you can tell, there are quite a few things available here look at and possibly configure. Let’s take a look at the currently configured MaxServerMemory to see what it is set at compared to what we are using and how much physical memory is on this system.
MaxServerMemory
$sqlServer.Configuration.MaxServerMemory
The ConfigValue is the actual value of the configuration while the Minimum and Maximum are just that, the limits both upper and lower for this particular setting. On a default installation of SQL, this value is set to the Maximum value (not really the best thing).
Before I get into how I changed this, lets also take a look at the physical memory of the server which can be found in the SQL server object.
PhysicalMemory
$sqlServer.PhysicalMemory
This value is in MB, so this is basically 4GB of memory on this system. So this begs the question as to how much memory is being consumed by the SQL server? Well, one way to find this out is by using performance counters. What counter should we use to find the information needed? The quickest way is to use Get-Counter –ListSet with a little filtering to find the correct counter.
(get-counter -ListSet "*SQL*" -ErrorAction Stop).Counter -like "*Memory Manager*Total*"
In this case, the counter required is \MSSQL$SHIRE:Memory Manager\Total Server Memory (KB). Note that this returns a value in KB. Running Get-Counter with the counter will show just how much memory is being used.
Get-Counter -Counter '\MSSQL$SHIRE:Memory Manager\Total Server Memory (KB)'
Looks like this server is using 92200KB (or 90MB) of memory.
Keep in mind that this is the current amount of memory being used and would be subject to change. But going back to the MaxServerMemory and changing it, lets say to 1GB of memory. Doing so is fairly simple:
#Remember that this is in MB $sqlServer.Configuration.MaxServerMemory.ConfigValue = 1024 #Apply the change to the server $sqlServer.Alter() #Verify $sqlServer.Configuration.MaxServerMemory
There you have it! The MaxServerMemory is now set to 1024 MB. If I didn’t call the Alter() method after making the change, the memory config would not have been updated.
You can also check for any locks on the server.
$sqlServer.EnumLocks()
A fun method to use is EnumProcesses()
$sqlServer.EnumProcesses() | Format-Table -Auto
Now you can see the current snapshot of activity that is occurring on the SQL Server; similar to viewing the Processes on Activity Monitor.
You are also able to look at the server permissions.
$sqlServer.EnumServerPermissions() | Format-Table -AutoSize
These are just some of the things you can look at and explore in your SQL server. Next time we will look at databases using PowerShell and SMO!
The video recording from my presentation on WSUS Administration with PowerShell has been posted and is now available to be viewed. The presentation materials from this are available at the link below.
In a previous article, I showed you how to find all of the SQL servers and instances using a function that queries the registry. Moving on from this, I will now show how you can connect to a SQL server (and instance) using SQL Server Management Objects (SMO).
In case you are wondering why I am talking about SQL, it is because I enjoy working with SQL when I get the chance and while I may not be an expert at it, you can be sure that I will learn as much as I can and even more so how I can leverage PowerShell to work with it.
SMO can be used with PowerShell to make a connection to a SQL server by name (or name\instance) and allow you to utilize the objects to traverse the SQL server to look at the databases, jobs and just about anything else you need to do. Did I also mention that you can use T-SQL as well through this? If you are looking to only run T-SQL statements without anything else, then there is another method that might suit you without the need for SMO that I will try to discuss in a future article.
To use these, you need to have the SQL Server Management Studio installed (or you can find the assemblies on another installation, but I cannot promise that everything will work correctly for you OR you can download the SQL Feature Pack to get access to the assemblies). Once you have that installed, you can run the following command to load up the assemblies (SQL 2008 as an example).
#SQL 2008 add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
Using Add-Type and specifying the exact version will make sure that you are using the latest version of the assemblies if you happen to have multiple versions of SQL installed such as 2005 and 2008.
Once the assemblies have been loaded up, you can then attempt to make your first SQL server connection.
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server('DC1')
#View the connection
$sqlServer
Ok, that is a ton of information to look at! But as you can see, there are a lot of things to look at and traverse through to view what is on the SQL Server. So what happens if I connect to a server that doesn’t have SQL installed? Will it just error out?
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server('DoesNotExist')
$sqlServer
No error message, but it still has a somewhat empty shell of an object that is available. Keep this in mind when you are working with SMO and PowerShell. Just because the connection appears to have worked, it doesn’t mean it actually did work.
One last thing, if you want to check out all of the databases on this particular server, run this (make sure you re-connect to a known good server first):
$sqlServer.Databases
To make this more useful, I will only show a few properties so you can get the idea of what is available.
$sqlServer.Databases | Select Name,Owner,RecoveryModel,DataSpaceUsage
Hmm… my SUSDB is not using a Full RecoverModel. Better get that changed sometime soon, but as you can see, you can see the name, owner recoverymodel and dataspaceusage to name a few things which are available.
There is much more to explore with using SMO and PowerShell and I will do just that in some upcoming articles including showing how you can perform Transact-SQL (T-SQL) queries as well as checking the status of SQL Jobs and events.
Learn Powershell | Achieve More 