PowerShell: Creating and Joining SharePoint 2010 Farms

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.


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).


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.


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)


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.


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!

Comments (7)

  1. Dr.Sharepoint says:

    awesome. I always recommend this. great to have a nice script to do it

  2. Paul Beck says:

    Hi Roger, your example variable is incorrectly used see $FarmPassphrase.  You are switching $PassPhrase with $FarmPassphrase.  

    Simply change:

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


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

  3. Paul Beck says:

    Actually, my last post was erroneous, the issue is this post doesn't show how to create the $farmphrase variable.  The download is correct.

    $FarmPassphrase = (ConvertTo-SecureString $Passphrase -AsPlainText -force)

  4. That's correct, I didn't do a complete line-by-line walkthrough of the script.  The PassPhrase you use does have to be a secure string, so we just take the string value provided and convert it to a secure string using the same line you just noted.

    Thanks for the feedback.

  5. Jacob Aierstock says:

    So i'm curious as to where you are actually creating the SQL alias…is this being stored in the hosts file on the server or another location?

    I currently just modify the hosts file on my server and point it to the IP of the SQL server with an alias, any issues with that?

  6. It's actually being stored in the registry (same as if you were to use 'cliconfg.exe')

  7. Jerry LaGrou says:

    Hi Roger,  I'm having a problem.  We have two instances of SharePoint.  Separate databases, servers, the whole thing.  Some of our automation has been putting files in the one instance, some of our automation has been putting files in the other.  Is there a way to move/merge data from the one to the second?  Thanks,  Jerry LaGrou.  jerry@lagrou.org