Installing SharePoint Server 2010 on Windows Server 2008 R2 and SQL Server 2008 R2 - Part 7: SQL Server 2008 R2 installation and configuration

Update: June 05, 2013 | Publication: November 18, 2009 | by Andreas

This article describes the installation and configuration of SQL Server 2008 required for deploying SharePoint Server 2010 and is part of a series describing the complete installation of SharePoint Server 2010 on Windows Server 2008 R2 and SQL Server 2008 R2. During the installation of SQL Server we install only the minimum required features.

Please note that different features of SharePoint may require additional features to be installed. I prefer to install the least required features since the size of the image matters for me and the process will be much faster if you copy or move the virtual image. The installation of SQL Server will be done on the same server where SharePoint 2010 will be installed. This way I have everything (except the Active Directory) in one image so I can easily make a backup or restore by just making a copy of my virtual image.

Regarding the needed accounts we have to distinguish between a SharePoint related account and a SQL Server related account. SQL can be installed with a domain account which is used to run the services. This account is used to install SQL Server and in this guide not directly related to SharePoint. The SharePoint related account is needed to install SharePoint and needs certain permissions and roles in SQL Server which are described at the end of this post.

In this scenario the SharePoint account is also used as the SQL Server administrator so I can use the same account to administrate SharePoint and SQL Server.

Note: If you're done please read the comment at the end of this post by Wes Preston. He mentions a book describing how to install SQL Server 2008 R2 on the same machine where you want to install SharePoint 2010. Shortiiik explains in the comments why. Don't hesitate to post your comments too or to ask a question if you have one. Also check the overview where you can find the complete series and of course the farm topology and the deployment scenario.

Please have also a look at

Installing SharePoint Server 2010 on Windows Server 2008 R2 and SQL Server 2008 R2 - Part 1: Overview

So let's get it on...

SQL Server 2008 installation

The installation of SQL Server 2008 compared to SQL Server 2008 R2 is almost the same and described below.

If you get a compatibility warning while trying to install SQL Server 2008 on Windows Server 2008 R2 you can proceed with install.

Before running SQL Server you need to apply Service Pack 1.

SQL Server 2008 R2 installation

If you haven’t done yet install .NET Framework 3.5 SP1.

On the left side click “Installation” and on the right side click "New installation or add features to an existing installation".

Install the setup support files.

Select “SQL Server Feature Installation”.

Select the “Database Engine Services” feature and the “Management Tools - Basic” feature. Check additional features if needed.

Check additional features if needed.

Keep the default instance and default folders.

Use the same account for the services and select the sqlSvcAcc you created in Active Directory.

Keep the collation settings you can see in the screenshot.

Add the SharePoint 2010 setup administrator user as a “SQL Server Administrator”.

Click next at the “Installation Configuration” Rules screen.

Start the installation of SQL Server 2008 R2.

SQL Server 2008 R2 configuration

There are 2 configuration steps you need to do: assign permission to the SharePoint 2010 setup administrator and configure networking.

Assign permission

The SharePoint 2010 setup administrator needs to have the securityadmin and dbcreator role. The sysadmin role is assigned if you decide during SQL Server 2008 installation that your SharePoint 2010 setup administrator should be the SQL admin.

I decided to do so in my Hyper-V development environment.

Configure networking

At the end you need to configure networking if you are planning to deploy a farm with multiple SharePoint Server 2010 servers.

According to TechNet [1] you need to “ensure that SQL Server 2005 or SQL Server 2008 is updated to the required level and configured as follows”:

SQL Server 2005: Local and remote connections are enabled and are configured to use the TCP/IP protocol.
SQL Server 2008: The TCP/IP protocol is enabled for the network configuration.
RTM: TechNet still says (May 6, 2010) you need TCP/IP but you don’t need Named Pipes. From my experience you need Named Pipes.

Difference to SharePoint 2007

In SharePoint 2007 you needed to configure named pipes which isn’t necessary in SharePoint 2010.

Resources

Here are the resources used in this article:

[1] Multiple servers for a three-tier farm

Learn more about:

Next steps

Please take a look at Part 8: SharePoint Server 2010 installation

Learn more about SharePoint 2010 Installation

Comments (13) -

2/21/2010 12:39:50 PM #

Hi, there!
I'm following this walkthrough, but since I don't have additional computer for Domain Controller, I am trying to install the whole thing on one machine. In this step, the installation of Sql Server 2008 R2, I was warned, being a domain controller might cause problems. However, I continued and bumpued into a problem, where I had to point the installation the Account for the Sql Server Agent Service - it showed me wrong credentials, when I pointed the newly created(from Powershell) Managed Service Accounts! So, I googled alot, and found this:
blogs.technet.com/.../...ged-service-accounts.aspx
I decided to go with the standard NT Authority\System account and then change the running account of the service through the Services Console.
I'll write back if I succeed/fail with this.
Thanks for your posts, Andreas!

Kocko Reply

7/21/2010 3:37:23 PM #

Hi,
did you got any luck with the service account issue .i am still stuck so use the same why you suggested.

Sameer Reply

5/19/2010 12:20:37 AM #

Another tidbit that might belong here or the previous page/step: There is a sQL Server 2008 R2 ebook that mentions you need to install the SharePoint 2010 bits - WITHOUT running the config - BEFORE installing SQL Server 2008 R2 (assuming they're talking about on the same machine). There weren't a lot of details, so I'm not sure of which scenarios this applies to yet - we're still checking.

blogs.msdn.com/.../...soft-sql-server-2008-r2.aspx

Wes Preston Reply

5/20/2010 9:19:12 AM #

Hi Wes,

that's interesting... it would be really great if you can share your findings if you figure it out

Thanks in advance
Andreas

Andreas Glaser Reply

5/24/2010 8:20:51 AM #

2Wes Preston: the only information mentioned in the ebook about installing sharepoint BEFORE running SQL Server 2008 R2 installation I found was because of the "analyses integration with sharepoint" option be displayed in the installation wizard. See page 10: "In order to use this new installation feature option, SharePoint 2010 must be installed but not configured prior to installing SQL Server 2008 R2."

So there is no need to install sharepoint before the sql server unless you want to install analyses services that integrated with sharepoint

Shortiiik Reply

9/15/2010 9:27:46 PM #

when adding spAdmin as administrator, add the current account as well, otherwise you will get login error.

Ladan Reply

9/16/2010 7:54:16 AM #

Thank you for the input ;)

Andreas Glaser Reply

10/31/2010 2:42:03 AM #

is the AD intended to be on the same box as sharepoint in this example? if not, how are u able to have two VMs at a time? also how do u use Hyper V features in Windows 2008 R2?
may be dumb questions.. ihave run into issues trying to have AD on the same box as SQL and SPS... I also noticed a local users and groups screen shot on a <Computer Management> scree in the procedure which is not possible if you have AD.. just keen to know what the setup was really for this installation...thx a ton..

laks Reply

2/18/2011 6:42:11 PM #

Did you have documentation to determine collation? Is Enable FileStream for Transact-SQL disabled (default) on install?

Thank you very much!

Mary E Reply

7/11/2011 12:01:49 PM #

Dear Mr. Glaser,

I'm new with installing servers. I followed your documentation step by step. The thing is I installed the sql server on the same on MS server 2008 R2. Now when I want to connect to the sql server with the service account sqlsvcacc I get the following error message:

Cannot connect to MSSQLSERVER
------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

I was googeling, and found some configuration to be done:

Enabling the following services under the sql server Configuration Manager:

1. Shared Memory
2. Named Pipes
3. TCP/IP

I also disabled the windows firefall.

Can you suggest me a solution? That would be very nice.

PIZZONIA Fabiano Reply

7/11/2011 10:44:38 PM #

Hi,

I had the same issue... there can be more than 1 reason for this. Please have a look at: sharepoint-tutorial.net/.../...2008-r2-issues.aspx

I think solution 1 doesn't apply to your problem since you did it right but maybe solution 2 or 3 might work.

Regards
Andreas

Andreas Glaser Reply

9/12/2011 8:44:23 AM #

thanks

mustafa s. arica Reply

10/18/2011 5:11:41 PM #

Hello Andreas,

I am ambati,am very new to this field right now am doing my thesis on this field.
i have few questions regarding instalation of sharepoint server 2010.
I am using windows server 2008 R2 Operating system, sql server 2008, team foundation server 2010 package, for interaction between share point products and team foundation server i need to download share point server 2010´but during installation i am facing some problems.
can please suggest me.

ambati Reply