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
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:
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
Under Feature Selection choose the following:
Under Instance Configuration, choose Named instance and name it SHAREPOINT, then choose the location where you intend to store the data.
On Server Configuration under Service Accounts, make the following changes:
Accept the default collation settings.
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”.
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:
- Start > Administrative Tools > Windows Firewall with Advanced Security
- Select Inbound Rules and select New Rule
- Under Rule Type select Program and click
- Select This program path and browse to “%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.SHAREPOINT\MSSQL\Binn\sqlservr.exe” and click
- 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.