Last year, I started writing an article that was supposed to be a series for Installing, Configuring and Managing Windows Server Failover Cluster using Windows PowerShell. The first of the series came out on July 2013 (which ended up being the last article I wrote for the year 2013 on MSSQLTips.com.) Since then, I’ve been involved with more projects on SharePoint and business intelligence (BI) integration that I barely had a chance to work with the SQL Server database engine on a regular basis. But since part 1 of the series is already out there, I figured it’s worth the time and effort to finish it up. So, here it is – the complete series on Installing, Configuring and Managing Windows Server Failover Cluster using Windows PowerShell.
Part 1 – from installing the feature to creating the Windows Server Failover Cluster
Part 2 – retrieving and changing cluster object properties and adding clustered disks
Part 3 – managing permissions, changing parameter values, moving clustered resources and dependencies
This post is way overdue. Since I’ve been getting a lot of requests about this specific presentation regarding SharePoint databases, I decided to do two things. First, I recorded this presentation for all my attendees to use as a reference. Now, you might be thinking, “If you’ve already recorded your presentation, wouldn’t that affect attendance in your events?” Yes and No. Yes, because those who have seen the video will no longer attend my presentation. For me, this is a great opportunity to help those individuals to plan ahead and maximize their time while attending events. As IT professionals, we’re busy, stuck in our day-to-day work and don’t even have time to look into some of these best practices that need to be applied in our environment. Often times, we are forced into the let’s-do-things-quick-and-fix-it-later corner because of the constant demand for our time. If the drop in attendance in my presentation/events would mean helping those individuals maximize their time, then, I’m all for that. This also includes those who really wanted to attend my presentations but do not have the means to do so (those in different time zones, different countries, no budget, etc.) And the flip side? No, it wouldn’t affect attendance in my events. Everyone who has seen me deliver a presentation can tell you a thing or two about why they’ve decided to attend my presentations even though they’ve already seen me (or the same presentation) at a previous event. I really work hard to prepare my presentations – the proper use of pictures, colors, fonts, and stories all are done with intent – even if it’s the same presentation delivered at a different event. This is my way of saying, “thank you for taking time off your hectic and tight schedule to attend my presentation.” In addition to that, I wanted them to have a resource that they can use as a reference when they go back to work. I want them to become valuable and continue to grow as an IT professional. After all, that’s my primary mission statement.
And, you’ve probably seen the corresponding slide deck.
But this is just the first of the two things I mentioned. Here’s the second one. I’ve written a PowerShell script to check the SQL Server instance that you use for your SharePoint databases. This is the PowerShell script that I use when delivering my presentation on Windows PowerShell for the SharePoint Administrators. It’s also the same PowerShell script that I use when I work with customers who request for my services to review and evaluate their SharePoint databases. The script checks for best practices configuration recommended for SharePoint databases – stuff like MAXDOP =1, disabled autoupdate and autocreate statistics, etc. As SQL Server DBAs, we hate some of these configuration. However, these are all documented and supported. Which means they have to be applied to your SQL Server instances and databases used by SharePoint. In addition, I have also included checks that we SQL Server DBAs consider best practices – separation of MDF and LDF files, regular DBCC CHECKDB execution, backup compression enabled, etc. You can download the PowerShell script from here.
Keep in mind that this is not the best way to write PowerShell scripts. I didn’t apply those best practices here so that would probably be my next personal project.
Feel free to use this script as you wish. It has only been tested on default instances of SQL Server 2008 and higher (named instances have not been considered yet) running on Windows Server 2008 and higher. High availability checks like failover clustering, database mirroring and Availability Groups have not been included yet on this version. Comment on the script for bugs and fixes that you want included, keeping in mind that this is specifically for SharePoint databases. Don’t expect any indexing improvements nor identifying the TOP I/O consumers because there is no way for us to modify those queries without breaking your SharePoint support contract (and I am in no way a lawyer to even argue about the contents of the EULA.)
I’ve totally forgotten about this because we did it late last year. SQL Server MVP (VMWare vExpert Awardee, EMC Elect Awardee, etc.) Denny Cherry (blog | Twitter) was kind enough to have me on his podcast. We talked about Windows PowerShell and how it can help SQL Server DBAs maximize their productivity performing tasks that, in some cases, are challenging to do with Transact-SQL.
MrDenny, thanks for the wonderful opportunity to be a part of People Talking Tech.
When you run a server operating system in your laptop, there’s a ton of stuff that you need to consider. Today, I spent almost the entire day trying to make sure that all of the drivers work on my DELL Latitude E6520. And since I run the Hyper-V role on my Windows Server 2012 machine, the first thing I did was to import all of my virtual machines from the old laptop to the new laptop. I currently have at least 14 virtual machines in my laptop. Since I do a lot of high availability and disaster recovery presentations, having that many virtual machines is just the norm for me. I took my external hard drive and started exporting all of my virtual machines.
Get-VM | Export-VM -Path "F:\VirtualMachines"
That command exports all of the Hyper-V virtual machines from my old laptop and copy them to the external hard drive. After plugging in the external hard drive to the new laptop, I copied the entire folder to the D:\ drive. Once the copy process was completed, I imported all of the virtual machines into Hyper-V.
Pretty slick especially when you’re dealing with several Hyper-V 3.0 virtual machines that you need to copy from one machine to another. And, as always, the simplicity of a one-liner command is what I use to convince IT administrators to start playing around with Windows PowerShell. Exporting and importing my Hyper-V virtual machines only took less than 20 minutes.
Indulge in free online SQL Server training on March 21, 2012 beginning at 00:00 GMT (that’s a March 20 evening start for the Americas). As this is an online event, act fast and register as soon as you can because seats are limited. Check out the 24 Hours of PASS website for more details. I will be delivering a session on SQL Server 2012 on Windows Server Core on the 12 NN (Eastern). Watch out for more information on Twitter using the #24HOP hashtag.
Back in 2009, I wrote an article about how to generate SQL scripts using Windows PowerShell and Server Management Objects (SMO) using the Script() method. One thing that you need to consider when using the Script() method is making sure that you have taken into account all of the database objects as well as their related objects. Take, for example, the Table object (or Table class according to SMO). A Table object consists of a collection of columns. Each column consists of several properties that can be considered database objects such as primary and foreign keys, constraints, etc. This means that for every column, you need to iterate thru all the possible constraints. Same thing with triggers and indexes. And we’re just looking at one database object here with multiple dependencies – the Table.
This is something that I needed to deal with when one of my customers wanted me to fix a process that included running a backup and restoring it on another server as well as truncating the tables. They simply needed to clone their database structure for testing purposes. I highlighted the fact that the backup/restore/table truncate process is utilizing a lot of unnecessary resources – network, disk and CPU – when all they needed was the database structure. That’s where I recommended using database creation scripts. It’s lightweight and does not utilize unnecessary resources. While I’m a big fan of process automation, I emphasize the need for proper definition of the process as well as making the process as simple as I possibly can. This PowerShell script is just one of the steps included in that process.
$sourcePath="C:\temp" #Variable to contain location of SQL scripts
$s=new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"
#Assign object to the database selected
$dbs=$s.Databases
#Script database creation - adding GO statement
$dbs["$database"].Script() +"`r`nGO`r`nUSE [$database]`r`nGO`r`n" | Out-File $sourcePath\$database.sql -append
#Generate script for all tables, excluding system tables
foreach ($tablesin$dbs["$database"].Tables | where {$_.IsSystemObject -eq$false})
{
$tables.Script() +"`r`nGO`r`n" | Out-File$sourcePath\$database.sql -append
#Generate script for all indexes in the specified table
foreach($indexin$tables.Indexes)
{
$index.Script() +"`r`nGO`r`n" | Out-File$sourcePath\$database.sql -append
}
#Generate script for all triggers in the specified table
foreach($triggerin$tables.Triggers)
{
$trigger.Script() +"`r`nGO`r`n" | Out-File$sourcePath\$database.sql -append
}
#Generate Default Constraints on columns that have them
#Iterate thru all the columns in the table
foreach($columnin$tables.Columns)
{
#Iterate thru all the default constraints in the columns
foreach($defaultin$column.DefaultConstraint)
{
#If there are default constraints, script out the ADD CONSTRAINT command
if ($default-ne$null)
{
$default.Script() +"`r`nGO`r`n" | Out-File$sourcePath\$database.sql -append
}
}
}
}
You can literally dig deeper on the different properties of the Table class to generate the corresponding script for the objects that you wish to create. Happy scripting!
As I’ve been translating a lot of my TSQL script to Windows PowerShell with SMO, here’s another articleI wrote on how to check for the default SQL Server backup folder
Most of the articles I’ve written about SQL Server with Windows PowerShell have been using Windows Authentication. And while it is highly recommended to use Windows authentication to connect to SQL Server, the reality is that the IT infrastructures we have don’t run on Microsoft Windows.
Here’s an article I wrote on how to use Windows PowerShell to connect to SQL Server via mixed mode authentication
Being a lazy administrator as I am, I try to minimize the amount of mouse-clicks I need to make to retrieve information about something on a Windows platform. As I have been using Microsoft Hyper-V on a bunch of my test machines, I always check if a VM is up and running before I power down my host machine (imagine the amount of electricity consumed just by keeping your machine up and running even without using it). This is specifically the case when dealing with my Windows XP VMs. I noticed that the profiles get corrupted if I shutdown the host machine without properly shutting down the VM. So, I always made sure that the VMs are not running before powering down the host machine.
I wrote a PowerShell command to query the current state of the VMs running on Hyper-V
This will actually display a bunch of information about the VMs running on Hyper-V but what we’re really concerned about is the name of the VM and it’s currently running state. These two properties are associated with the ElementName and EnabledState attributes of the MSVM_ComputerSystem class. All we need to do with the command above is to pipe the results to a Select-Object cmdlet, specifying only these two properties, as follows
While the EnabledState property will give you a bunch of numbers, I’m only concerned with those values equal to 2, which means that the VM is running. But, then, you might not remember what the value 2 means. So might as well write an entire script that checks for the value of the EnabledState property. I’ve used the GWMI alias to call the Get-WMIObject cmdlet
On a side note, make sure you are running as Administrator when working with this script as you will only see the VMs that your currently logged in profile has permission to access. Running as Administrator will show you all of the VMs configured on your Hyper-V server
While it may seem easy to create Active Directory users using the management console, I still prefer doing it using scripts so as to make sure that they are done in a uniform, standard fashion (not to mention as fast as one can possibly do especially if you will be doing it for many users). I’ve referenced the scripts provided at the CodePlex site for ADSI and Active Directory for Windows PowerShell (full credit goes to them) to create users in Active Directory for Windows Server 2008. This also works for Windows Server 2003. While I may be a big fan of automation, it is important to highlight that processes are what makes automation really work. The reason I am saying this is that the CSV file can come from different sources, say, an intranet site where you ask employees to log in and key in their details. Having a process in place to make sure that users who would be entering their details in a standard way would eliminate the need to cleanse the data (I’m still thinking as a DBA here) in the long run. Plus, having a standard in place as an organization is starting out will make it flexible enough to scale as growth happens.
# define constants
$domainstr = ",dc=domainName,dc=local"
$domainnb = "domainName" # domain netbios name
$domain = "domainName.local"
$ADs_UF_NORMAL_ACCOUNT = 512 # Disables account and sets password required.
# Remember to enable the account before logging in
# Prompt user to enter the default passsword for the users
$defaultPassword = Read-Host "Please enter default Password:" -asSecureString
# Read the list of users from the CSV file
# Include other user properties in the CSV file as necessary
Import-csv users.txt | foreach
{
# Create user name based on FirstName and LastName column in the CSV file
$strUser = $_.firstName + " " + $_.lastName
#Form the LDAP string based on the OU column from the CSV file
$strLDAP = "LDAP://OU=" + $_.OU + ",OU=domainName Domain Users" + $domainstr
$target = [ADSI] $strLDAP
$newUser = $target.create("User", "cn=" + $strUser)
$newUser.SetInfo()
#Define a naming convention for the login based on your corporate policy
#This one uses the first letter of the firstname and the lastname
$userID = $_.firstName[0]+$_.lastName
#Define the other user attributes based on the columns defined in the CSV file
$newUser.sAMAccountName = $userID.ToString()
$newUser.givenName = $_.firstName
$newUser.sn = $_.lastName
$newUser.displayName = $_.firstName + " " + $_.lastName
$newUser.userPrincipalName = $_.firstName[0]+$_.lastName + "@" + $domain
$newUser.mail = $_.Email
$newUser.physicalDeliveryOfficeName = $_.Location
$newUser.title = $_.Designation
$newUser.description = $_.Designation
$newUser.SetInfo()
$newUser.SetPassword($defaultPassword.ToString())
#Normal user that requires password & is disabled
$newUser.userAccountControl = $ADs_UF_NORMAL_ACCOUNT
Write-Host "Created Account for: " $newUser.Displayname
}