Creating and Joining SharePoint 2010 Farms using PowerShell.  This is something anybody working with SharePoint quickly learns to do.  Some of us do it a few times a year, others do it a few times a day.  This can be a pretty time consuming process, and sometimes it’s nice to be able to just kick off the process and walk away for a few minutes.

Background

Every SharePoint farm (Post-STSv1/SPS 2001) requires a configuration database, and that configuration database has to reside within a SQL instance.  A quick search on your favorite search engine will quickly reveal that it’s really not difficult to create or join a a SharePoint farm.  Both processes are explained very well in this TechNet article titled Multiple servers for a three-tier farm (SharePoint Server 2010).  That being said, there are also some general practices for hardening your SQL environments that you use for your SharePoint environments.  Those are discussed in this great article titled Harden SQL Server for SharePoint environments (SharePoint Server 2010).

Approach

What I typically do, at a minimum, during farm creation or when I’m joining a new farm is create a SQL alias prior to joining the farm.  This provides me with two main benefits.  The first one is the security aspect.  If your SharePoint Central Administration site does happen to get compromised, the name of the SQL server will not be exposed.  The second key benefit, which has been very useful for many of my customers, is the ability to change SQL servers seamlessly.  Yes, there are alternative methods to achieve this, clustering is a great example.  If for instance a SQL server was being migrated from a 32-bit deployment of SQL 2005 to a 64-bit deployment of SQL 2008 R2, you can simply stop all SharePoint services (W3SVC, SPTimerv4, SPAdmin, etc.), move all databases to the new instance, and re-configure the existing alias to point to the new SQL instance before restoring all SharePoint services.  SharePoint still understands this to be the same instance and no connection strings need to be re-configured.

Solution

There are effectively two parts to each of the scripts.  The first part of the script is exactly identical, because it’s creating the SQL alias on all SharePoint servers, whether you’re creating or joining a farm.  In order to accomplish this, we need to assign two variables, like this:

$SQLServer = "SQLServerName"
$SQLAlias = "AliasForSharePointSQL"

$SQLServer could also effectively be a named instance (Server\Instance) and work just the same, and the script could be adapted to accept ports other than the default (1433) for SQL communication.  This is also suggested in the articles above, though not always a requirement for every deployment.  To create the actual alias, we would use the following few lines:

 

$x86 = "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo"
$x64 = "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo" 


#Check to See if The ConnectTo Key Already Exists, Create it if it Doesn't.
if ((test-path -path $x86) -ne $True) {
     write-host "$x86 doesn't exist"   
    New-Item $x86
}

if ((test-path -path $x64) -ne $True) {
     write-host "$x64 doesn't exist"   
    New-Item $x64
}  

#Record the Alias Type
$TCPAlias = "DBMSSOCN," + $SQLServer


#Create The TCP/IP Aliases

New-ItemProperty -Path $x86 -Name $SQLAlias -PropertyType String -Value $TCPAlias
New-ItemProperty -Path $x64 -Name $SQLAlias -PropertyType String -Value $TCPAlias

The second part of the script does vary, depending on whether we’re creating or joining a farm.  Not much, but it does vary slightly due to the different activities required in each process.  The important thing to remember is that you only need to create the farm once, after that, it’s just a matter of joining the remaining servers to the farm using the exact same script on all other servers.  To create a farm, the following parameters will be required:

$FarmAccountUserName = "Domain\User"
$FarmAccountPassword = "AccountPassword"
$PassPhrase = "FarmPassphrase"
$FarmConfigDatabase = "SPFarm_ConfigDatabase"
$AdminContentDatabase = "SPFarm_Admin_ContentDB"
$CentralAdminPort = 8080

 

I include the password for my account, and the passphrase directly in my script.  Reason being, I need to run certain tasks (like creating or connecting to a configuration database) under those accounts.  This is usually a run-time process for me, using a copy of my scripts.  Following my deployment, the scripts are permanently purged, and only sanitized copies are kept for reference.  That’s my preference, however you could also simply prompt for credentials using Get-Credential.  For joining servers to the farm, the $AdminContentDatabase and $CentralAdminPort variables are not needed, and are not included in the sample scripts I’m providing.

 

To create a new farm, I’m simply using the following line:

New-SPConfigurationDatabase -DatabaseServer $SQLAlias -DatabaseName $FarmConfigDatabase -AdministrationContentDatabaseName $AdminContentDatabase -Passphrase $FarmPassphrase -FarmCredentials $FarmCredentials

 

To join a server to an existing farm, I’m simply using the following line:

Connect-SPConfigurationDatabase -DatabaseServer $SQLAlias -DatabaseName $FarmConfigDatabase -Passphrase $FarmPassphrase

 

Each of the Scripts performs the additional required tasks, such as installing the help collection to the central administration web application, securing resources, installing the required services and features, etc.

 

You can download the Farm Creation Script from this location: Download CreateFarmWithSQLAlias.ps1 (zipped)

You can download the Join Farm Script from this location: Download JoinFarmWithSQLAlias.ps1 (zipped)

Usage

All variables are defined in the top of the script.  Everything else is generated dynamically.  Once you reach the beginning of script execution in the script, no further manual edits are required.  Each script should require a maximum of 5 minutes to configure for your environment, and save you at least that much time going through the SharePoint wizards.

Feedback

As always, if you have any questions or feedback, let me know. If you have any ideas to optimize the script, I'd like to hear that too. Thanks for reading!