Build SharePoint Environment - Configure the SP SQL Server

As I had stated in part 1 of this series, I was reviewing Vlad Catrinescu’s series on creating a dev environment quickly and effortlessly. I think it’s a great post, but it is a single server environment and I wanted to setup a multi-server farm. That is what brought me to create this series. My first post has the architecture of the build if you are starting here and want to review. I also suggest you review the second part of the series as it contains the prep steps I outlined in the first post as well to prepare you for the steps in this post (it’s also important as we deploy your Active Directory and DNS there). In this post we will Configure the SP SQL Server.

Posts in this series:

Before I begin I want to again give credit where it is due. Some of the ideas and code I use in this series come from Vlad’s blog series I linked to above. And a huge thank you to Joanne Klein (@JoanneCKlein) for reviewing and editing the series for me.

Prepare the Server (Name, DNS and other OS options)

As with the DC you need to run the following script to ensure you don’t have any issues running any of the scripts (ps1 files). This isn’t to say you can’t run the scripts on the command line, Windows Server 2012 has the script security set so high it won’t run the ps1 files themselves. To lighten the security run the following:

Set-ExecutionPolicy unrestricted

Next we are going to configure the server name, the network card and the network settings for this server. As with the DC, we only want a private network for now. This is because the scripts rename the internal connection so you can tell them apart later on. Once the script below is run you can add your external connection and move the files you need onto the server.

#Rename server
$serverName = "srv-DrevSQL"
Rename-Computer -NewName $serverName -force

#Configure the Network Adapter
$netadapter = Get-NetAdapter -Name Ethernet
$netadapter | Set-NetIPInterface -Dhcp Disabled
$netadapter | New-NetIPAddress -IPAddress 192.168.0.10 -PrefixLength 24

#Point DNS at the DC
Set-DnsClientServerAddress -InterfaceAlias "Ethernet" -ServerAddresses "192.168.0.1"

#Rename the network adapter
Rename-NetAdapter -Name "Ehternet" -NewName "Internal Network";

#disable the firewall to make communication for SQL a little easier

#disable the firewall
netsh advfirewall set allprofiles state off

#Enable Remote Desktop
(Get-WmiObject -Class "Win32_TerminalServiceSetting" -Namespace root\cimv2\terminalservices).SetAllowTsConnections(1)
(Get-WmiObject -class "Win32_TSGeneralSetting" -Namespace root\cimv2\terminalservices -Filter "TerminalName='RDP-tcp'").SetUserAuthenticationRequired(0)

Now is the time to shutdown your server and add the external switch. This will let you map a drive to your host system and copy over that SQL server folder you prepped (you did that right?). You need that sxs folder I told you about for the next part. As with the DC, create a folder on the root of your C drive, create a new folder called SharePoint and copy in the sxs folder. You need this folder to install .NET 3.5.

#Install .NET 3.5
Import-Module ServerManager
DISM.exe /Online /Enable-Feature /FeatureName:NetFx3 /All /LimitAccess /Source:"C:\SetUpSharePoint\sxs"

The last step we want to do before installing SQL Server is to connect to the domain.

So I am going to give you two scripts to choose from here. One will prompt for the password and the other will insert the password for you. Placing a password into a script as plain text is of course a very bad thing. However, as this is your personal dev environment I will show you both ways.

Prompt Method (better way):

#This script provides the domain and username, but prompts for a password
#when joining the domain
$domainToJoin = "drevdev";
$loginID = "administrator";
$domainUser = ("{0}\{1}" -f $domainToJoin,$loginID);
$adminPWD = Read-Host -Prompt ("Please enter the password for account '{0}'" -f $domainUser) -AsSecureString;
$joinCredential = New-Object System.Management.Automation.PSCredential($domainUser,$adminPWD);

#Join to domain and reboot after.
Add-Computer -DomainName $domainToJoin -Credential $joinCredential -Restart

Password in Script Method:

#This script provides the domain and username, but prompts for a password
#when joining the domain
$domainToJoin = "drevdev";
$loginID = "administrator";
$domainUser = ("{0}\{1}" -f $domainToJoin,$loginID);
$adminPWD = ConvertTo-SecureString 'MyPasswordDontTell' –asplaintext –force 
$joinCredential = New-Object System.Management.Automation.PSCredential($domainUser,$adminPWD);

#Join the domain and reboot after.
Add-Computer -DomainName $domainToJoin -Credential $joinCredential -Restart

Install and Configure SP SQL Server

Now that you have joined the domain and installed the prerequisites we can install and configure our SQL Server. One can build a PowerShell script to install SQL Server with features enabled, but again I agree with Vlad and I don’t want to do that. You may be using your SQL server for other things besides a SP dev environment and want other features installed, or you have other needs that would render my script useless. So basically what we are going to do is copy the SQL binaries to the C:\SetUpSharePoint drive, run the config wizard to get us the config file we need and then use PowerShell to install SQL Server.

Run the following PowerShell command to copy the binaries from the SQL install disk to your C:\SetUpSharePoint folder:

#Assumes 'D' is your DVD drive.  Change as required
Copy-Item D:\ C:\SetUpSharePoint\SQLBinaries\ -Recurse

Build the Config File

The first time you ever have to follow this process and install SQL Server 2012, you will need to create a config file. After that you should be able to preserve it and re-use it by changing server names if you are changing up servers. This can be done completely through PowerShell, but I don’t want to do this here as everyone’s needs are different (hardware, usage, etc). Instead I am going to save you time on determining the steps to create the file and walk you through the steps to configure the installation. After that we will load up our old friend PowerShell and install away.

Navigate to where you saved the SQL Binaries and execute the setup process
Click on the Installation tab (left frame) and click on New SQL Server stand-alone installation or add features to an existing installation
Click OK at Setup Support Rules
If prompted for a license key, enter that now. Click Next
Accept the terms of the agreement, click Next
You may be prompted to include an update. If so, ensure the check mark is there.

The installation process will download the necessary updates and install the setup files.
At Setup Roles, select SQL Server Feature Installation and click Next
If you aren’t doing anything fancy, we won’t need to select a lot of options at the Feature Selection screen. The bare minimum you should have checked is: Database Engine Services and Management Tools – Complete. That’s all you really need for now for a base SP farm installation. Click Next after you have selected the features.

Click Next until you get to Instance Configuration.
Normally you won’t need to put a named instance here, however you will see in my screen shot that I did. In my case I still have a need for SP2010, so I want to keep those instances separate. If you do too, then I suggest you add an instance. Make your change (or not) and click Next.

Keep clicking Next until you get to Server Configuration.
Enter in the SQL service accounts we created in the last post and enter their passwords. Click Next once that is done.

Change the DB Engine Config to Mixed Mode and enter in the ID and PWD you wish to use here
Also add the SQL and SP admin accounts as SQL Server Admins. Add the Domain Admins account for good measure as well.

Leave you Data Directories the same unless you actually have separate drives added to your VM to separate Data and Log drives (though this is Dev and not really necessary).
Click Next until you get to “Ready to Install” and stop there.
At the bottom of that page you will see Configuration file path. That’s what we came here for.

Copy the file location, go to that file and move a copy of it over to C:\SetUpSharePoint\SQLBinaries.
You can now cancel the wizard.

Install the Server with PowerShell

Now that you have created your config file, we actually need to make a minor change to it. In the script we will run, we make use of the “/Q” option. This interferes with the UIMode setting in the config file. Open up ConfigurationFile.ini and look for the entry “UIMODE = “Normal”. Place a semicolon (“;”) in front to comment it out.

If you don’t do this, you will receive the error: “The /UIMode setting cannot be used in conjunction with /Q or /QS”

We can now install SQL Server with a single two-line PowerShell Script. Open your PowerShell Console as admin and run the following (don’t forget to change the domain):

cd C:\SetUpSharePoint
.\SQLBinaries\Setup.exe /ConfigurationFile=.\SQLBinaries\ConfigurationFile.INI /Q /Action=Install /IAcceptSQLServerLicenseTerms /SQLSVCPASSWORD=spDev321 /AGTSVCPASSWORD=spDev321 /sapwd=spDev321

Enable SQL Server Browser

I found out the hard way when setting up my environment that SQL Server Browser does not start automatically for some reason in SQL Server 2012. To fix this, we can set it up using a couple of lines of PowerShell.

$sqlService = Get-WMIObject `
              -ComputerName . `
              -namespace root\Microsoft\SQLServer\ComputerManagement11 `
              -class SqlService `
              -filter "ServiceName='SQLBrowser'"

#set the service to automatic
$sqlService.SetStartMode(2);

#start the service
$sqlService.StartService();

If you receive an error message “Exception calling “StartService” : “” ” then the service has already been started for you.

That’s it. You now have a second server ready to go in your farm.

We can take the scripts we ran today and group them into three single scripts for next time. It would have been two, except we needed an extra shut down to add on the external switch. I created the following for myself:

1_PrepareSQLServer:

#Rename server
$serverName = "srv-DrevSQL"
Rename-Computer -NewName $serverName -force

#Configure the Network Adapter
$netadapter = Get-NetAdapter -Name Ethernet
$netadapter | Set-NetIPInterface -Dhcp Disabled
$netadapter | New-NetIPAddress -IPAddress 192.168.0.10 -PrefixLength 24

#Point DNS at the DC
Set-DnsClientServerAddress -InterfaceAlias "Ethernet" -ServerAddresses "192.168.0.1"

#Rename the network adapter
Rename-NetAdapter -Name "Ehternet" -NewName "Internal Network";

#disable the firewall to make communication for SQL a little easier

#disable the firewall
netsh advfirewall set allprofiles state off

#Enable Remote Desktop
(Get-WmiObject -Class "Win32_TerminalServiceSetting" -Namespace root\cimv2\terminalservices).SetAllowTsConnections(1)
(Get-WmiObject -class "Win32_TSGeneralSetting" -Namespace root\cimv2\terminalservices -Filter "TerminalName='RDP-tcp'").SetUserAuthenticationRequired(0)

2_SQL_NETandDomain

#Install .NET 3.5
Import-Module ServerManager
DISM.exe /Online /Enable-Feature /FeatureName:NetFx3 /All /LimitAccess /Source:"C:\SetUpSharePoint\sxs"


#This script provides the domain and username, but prompts for a password
#when joining the domain
$domainToJoin = "drevdev";
$loginID = "administrator";
$domainUser = ("{0}\{1}" -f $domainToJoin,$loginID);
$adminPWD = Read-Host -Prompt ("Please enter the password for account '{0}'" -f $domainUser) -AsSecureString;
$joinCredential = New-Object System.Management.Automation.PSCredential($domainUser,$adminPWD);

#Join to domain and reboot after.
Add-Computer -DomainName $domainToJoin -Credential $joinCredential -Restart

3_InstallSQLServer

cd C:\SetUpSharePoint
.\SQLBinaries\Setup.exe /ConfigurationFile=.\SQLBinaries\ConfigurationFile.INI /Q /Action=Install /IAcceptSQLServerLicenseTerms /SQLSVCPASSWORD=spDev321 /AGTSVCPASSWORD=spDev321 /sapwd=spDev321

$sqlService = Get-WMIObject `
              -ComputerName . `
              -namespace root\Microsoft\SQLServer\ComputerManagement11 `
              -class SqlService `
              -filter "ServiceName='SQLBrowser'"

#set the service to automatic
$sqlService.SetStartMode(2);

#start the service
$sqlService.StartService();

That’s it! You now have an Domain Controller and a SQL Server. You’re now ready to install SharePoint 2013 and configure your APP server. We will be doing that in the next post.

Click here for the next post in this series: Preparing SharePoint Servers (Coming Soon!)

Thanks for reading.

Quickly Build a Multi-Server SharePoint 2013 Developer Environment (Part 3) – Configure SP SQL Server

Prepare the Server (Name, DNS and other OS options)

Install and Configure SP SQL Server

Build the Config File

Install the Server with PowerShell

Enable SQL Server Browser

Like this:

Comments

Leave a Reply Cancel reply

Quickly Build a Multi-Server SharePoint 2013 Developer Environment (Part 3) – Configure SP SQL Server

Prepare the Server (Name, DNS and other OS options)

Install and Configure SP SQL Server

Build the Config File

Install the Server with PowerShell

Enable SQL Server Browser

Share this:

Like this:

Comments

Leave a Reply Cancel reply