How to Migrate Companyweb to a SQL 2008 R2 Standard Instance on SBS 2011 - Part 2: Premium Second Server

[Today's post comes to us courtesy of Shawn Sullivan from Commercial Technical Support]

In Part One of this series we discussed how to upgrade the preinstalled instance of Sharepoint on the SBS 2011 server to SQL 2008 R2 Standard in order to migrate a larger content database. This particular post is targeted to those who are instead planning to use the second Windows 2008 R2 server they are licensed for in SBS 2011 Premium to hold their Sharepoint content database. It will deal with installing the instance and configuring network settings. For the sake of keeping these posts as short as possible, Part 3 will be an extension of this post and will give the final steps for migrating the content database to the second server.

This method is a bit more involved than the one described in the first post. The important points here are:

• We are manually installing a new named instance of SQL 2008 R2 Standard onto a separate server in the LAN. This will not be the same server that the Companyweb site is hosted on. This is also not a change in location where Sharepoint is installed. We are simply preparing to retarget only the content database to another SQL server on the network.
• Just like in Part One, we are using the SQL 2008 R2 Standard Edition for Small Business media for the Sharepoint instance. The second server you are installing onto must be joined to the domain and you must be logged in with a domain administrator account before launching setup. The SQL installation is bound to all of the same requirements listed in http://blogs.technet.com/b/sbs/archive/2009/03/23/requirements-for-installing-sql-2008-standard-edition-for-small-business.aspx
• By default, the named instance will be configured to listen on a dynamic port after setup. This is not compatible with the Sharepoint front-end web service running on the SBS 2011 server and will need to be adjusted to listen on TCP port 1433.
• If you are running Windows Firewall, or any firewall on the second server, you will have to make an exception for SQL traffic. We will walk through the steps for Windows Firewall.

Installing the Instance

1. After you have installed the second server, joined it to the SBS 2011 domain, and have logged in with the domain admin account, launch SQL setup from the Premium media. If you do not have the .NET Framework 3.0 SP1 or Windows Installer 4.5 installed, you will be prompted to install both:
   
   
   The update installation will occur the background. The SQL Server Installation Center should launch automatically when it’s complete.
2. Click on Installation > New installation or add features to an existing installation. At this point it will install the setup support files and begin the first series of environment checks.
   
   
   Note: Throughout this entire process, setup will pass through several rule check runs to ensure the installation will not fail due to outstanding issues with the environment. The most common issue we see is the failure of the OS Edition check, which is documented with troubleshooting steps in the following post:
   http://blogs.technet.com/b/sbs/archive/2010/07/21/how-to-diagnose-quot-operating-system-supported-for-edition-quot-pre-requisite-errors-while-installing-sql-2008-standard-edition-for-small-business.aspx
3. On Setup Role choose SQL Server Feature Installation.
   
   
4. Under Feature Selection choose the following:
   • Database Engine Services
   • SQL Server Replication
   • Full-Text Search
   • Management Tools –Basic
     
     
     
5. Under Instance Configuration, choose Named instance and name it SHAREPOINT, then choose the location where you intend to store the data.
   
   
6. On Server Configuration under Service Accounts, make the following changes:
   1. Change the Account Name for SQL Server Agent to NT AUTHORITY\NETWORK SERVICE and set the Startup Type to Disabled
   2. Change the Account Name for SQL Server Database Engine to SYSTEM
   3. Disable the SQL Server Browser
      
      
      
7. Accept the default collation settings.
8. On Database Engine Configuration, under Account Provisioning, leave Windows authentication mode selected and click Add Current User.
   
   
9. Before committing the install you will be presented with a summary. The .ini that holds this information is kept in “%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<ID>\ConfigurationFile.ini”.
10. Complete the installation.

SQL Server Network Configuration

Since this is a named instance, it will be configured by default to listen on a dynamic port for network connections. You will have to change this use the static port 1433 in order for the Sharepoint web application running on the SBS 2011 server to access it. To do this, follow the steps in http://msdn.microsoft.com/en-us/library/ms177440.aspx

Windows Firewall Configuration

If you are running Windows Firewall you will have to make an exception for SQL traffic. The simplest way to do this is to add a program exception for the executable file. Do to this:

1. Start > Administrative Tools > Windows Firewall with Advanced Security
2. Select Inbound Rules and select New Rule
3. Under Rule Type select Program and click Next
   
   
   
4. Select This program path and browse to “%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.SHAREPOINT\MSSQL\Binn\sqlservr.exe” and click Next.
   
   
   
5. Accept the rest of the defaults and give the rule a meaningful name, then click Finish.

At this point the instance is ready for the migrated database. We will cover this in Part Three.