Set up SQL Server for a BI test environment

 

Applies to: SharePoint Server 2010

Important

This article is part of the baseline setup for the Configuring a BI infrastructure: Hands-on labs series. To complete the steps in this article, you must first have completed Set up accounts for a BI test environment.

This article describes how to install and configure Microsoft SQL Server 2008 for use with Microsoft SharePoint Server 2010. SharePoint Server 2010 requires one of the following versions of Microsoft SQL Server:

• Microsoft SQL Server 2008 R2

• Microsoft SQL Server 2008 with Service Pack 1 (SP1) and Cumulative Update 2

• Microsoft SQL Server 2005 SP3 with Cumulative Update package 3 for SQL Server 2005 Service Pack 3

This article describes installing and configuring SQL Server 2008 R2.

Video demonstration

This video shows how to set up SQL Server 2008 R2 on the Contoso-SQL virtual machine.

Running time: 11:13

Watch the video.  For an optimal viewing experience, download the video. Right-click the link, and then click Save Target As to download a copy. Clicking the link opens a .wmv file in the default video viewer for full-resolution viewing.

Configure a local administrator

We recommend that you install SQL Server 2008 R2 while logged on with the SQL Server administrator domain account (Contoso\SQLAdmin). Prior to doing this, you must add this account to the Administrators group on the Contoso-SQL virtual machine.

Log on to the Contoso-SQL virtual machine with the domain administrator account (Contoso\administrator) and perform the following procedure to add the Contoso\SQLAdmin account to the local Administrators group.

To add a local administrator

1. Log on to the virtual machine where you want to install SQL Server as a domain administrator (Contoso\Administrator).

2. Click Start, click Administrative tools, and then click Computer Management.

3. Expand Local Users and Groups, and then click Groups.

4. In the pane on the right side, right-click Administrators, and then click Properties.

5. Click Add.

6. Type the name of the SQL Server administrator account (Contoso\SQLAdmin), and then click OK.

7. Click OK.

8. Log off the virtual machine.

To perform the remaining procedures in this article, log on to the Contoso-SQL virtual machine with the Contoso\SQLAdmin account.

Install SQL Server 2008 R2

The first step is to install SQL Server 2008. SQL Server 2008 requires .NET Framework version 3.5. If .NET Framework 3.5 is currently not installed, you must install it or enable it as a feature in Windows Server 2008 before you continue.

Perform the following procedure to install SQL Server 2008 on Contoso-SQL.

To install SQL Server 2008 R2

1. Attach the SQL Server 2008 R2 DVD to the virtual machine DVD drive.

2. Log on to the Contoso-SQL virtual machine as the SQL Server administrator (Contoso\SQLAdmin).

3. On the SQL Server DVD, double-click setup.exe.

4. If a SQL Server 2008 R2 setup requires Microsoft .NET Framework dialog box is displayed, click OK.

5. In the SQL Server Installation Center, in the pane on the left side, click Installation.

6. In the pane on the right side, click New installation or add features to an existing installation.

7. On the Setup Support Rules page, click OK.

8. On the product key page, type your product key, and then click Next.

9. On the License Terms page, review the license agreement and, if you agree, select the I accept the license terms check box, and then click Next.

10. On the Setup Support Files page, click Install.

11. On the Setup Support Rules page, click Next.

12. On the Setup Role page, select the SQL Server Feature Installation option, and then click Next.

13. On the Feature Selection page, select the following check boxes:

    • Database Engine Services

    • Analysis Services

    • Reporting Services

    • Management Tools - Complete

    Note

    We also recommend that you install SQL Server Books Online.

14. Click Next.

15. On the Installation Rules page, click Next.

16. On the Instance Configuration page, leave the Default instance option selected, and then click Next.

17. On the Disk Space Requirements page, click Next.

18. On the Server Configuration page, change the Startup Type for SQL Server Agent from Manual to Automatic.

19. Click Use the same account for all SQL Server services, type Contoso\SQLSvc for the Account Name, type the password for that account, and then click OK.

20. Click Next.

21. On the Database Engine Configuration page, select the Mixed Mode option, and type and confirm a password for the built-in SQL Server administrator account.

22. Click Add Current User to add the current user account as a SQL Server administrator.

23. Click Next.

24. On the Analysis Services Configuration page, click Add Current User to add the current user account as an Analysis Services administrator.

25. Click Next.

26. On the Reporting Services Configuration page, select the Install the native mode default configuration option, and then click Next.

27. On the Error and Usage Reporting page, leave the default values and then click Next.

28. On the Installation Rules page, click Next.

29. On the Ready to Install page, click Install.

30. When the setup process is complete, click Close.

Configure logins

When SQL Server 2008 R2 has been installed, the next step is to create a logon for the Farm Administrator account (Contoso\FarmAdmin) to provide the necessary permissions for SharePoint Server 2010 installation and configuration.

Perform the following procedure to create a logon for the Farm Administrator account.

To create a logon for the Farm Admin

1. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

2. On the Connect to Server dialog box, type Contoso-SQL in the Server name text box, and connect to the Database Engine.

3. In SQL Server Management Studio, expand Security, right-click Logins, and then click New Login.

4. In the Login name box, type Contoso\FarmAdmin.

5. In the pane on the left side, click Server Roles.

6. Select the sysadmin and dbcreator server roles.

7. Click OK.

Configure SQL Server network protocols

SharePoint Server 2010 requires that SQL Server 2008 allow connections over TCP/IP. Depending on your SQL Server configuration, this may be enabled by default. Use the following procedure to confirm that TCP/IP access is enabled.

To enable connections over TCP/IP

1. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, click Configuration Tools, and then click SQL Server Configuration Manager.

2. Expand the SQL Server Network Configuration node.

3. Click Protocols for MSSQLSERVER.

4. In the Protocol Name list, confirm that TCP/IP is Enabled. If it is not, right-click TCP/IP, and then click Enable.

5. If TCP/IP was disabled, restart the SQL Server service for the new setting to take effect.

   Note

   To restart the service, in Management Studio, right click the Contoso-SQL node at the top of Object Explorer, and then click Restart.

After SQL Server configuration is complete, the next step is to install SharePoint Server 2010 on the Contoso-AppSrv virtual machine. See the next article, Install and configure SharePoint Server 2010 for a BI test environment.

Install the Contoso sample database

Several of the labs in this series use sample data from the Microsoft Contoso BI Demo Dataset for Retail Industry. To install this dataset, you first have to download the files from the Microsoft Download Center (https://go.microsoft.com/fwlink/p/?LinkId=219170). Download ContosoBIdemoBAK.exe.

You must install this file on the Contoso-SQL virtual machine. You can put the file on the virtual machine by using one of the following methods:

• Create an ISO file that contains the downloaded file and attach it to the Virtual DVD drive on Contoso-SQL.

• Burn the file onto a physical DVD and place the DVD in the DVD drive of the host computer that is running Hyper-V. Then, connect the Contoso-SQL virtual machine to the physical DVD drive.

• Create a network file share that contains the file, and then connect to that file share from Contoso-SQL.

  Note

  You may have to add an additional virtual network adapter to Contoso-SQL to gain access to a file share that is outside the Contoso domain.

Which method that you choose depends largely on your environment.

Once you have downloaded the file and copied it to the Contoso-SQL virtual machine, use the following procedures to install the sample data.

First, create a folder for the backup files.

To create a backup folder

1. Click Start, click All Programs, click Accessories, and then click Windows Explorer.

2. In the pane on the left side, click Computer.

3. In the pane on the right side, double-click Local Disk (C:).

4. Right-click the pane on the right side, click New, and then click Folder.

5. Name the folder BackupFiles.

Once the folder has been created, you can unzip the contents of the ContosoBIdemoBAK.exe file to that folder. Use the following procedure to unzip the ContosoBIdemoBAK.exe file and restore the database backups to SQL Server.

To install the ContosoRetailDW database

1. On the Contoso-SQL virtual machine, locate the ContosoBIdemoBAK.exe file.

2. Double-click ContosoBIdemoBAK.exe.

3. On the WinZip Self-Extractor dialog box, click Browse.

4. On the Browse for folder dialog box, browse to the C:\BackupFiles folder, and then click OK.

5. On the WinZip Self-Extractor dialog box, click Unzip.

6. On the success dialog box, click OK.

7. Click Close.

8. Open SQL Server Management Studio and connect to the Database Engine.

9. Right-click Databases, and then click Restore database.

10. In the To database text box, type ContosoRetailDW.

11. Select the From device option, and then click the browse button.

12. On the Specify Backup dialog box, click Add.

13. Browse to the C:\BackupFiles folder, click ContosoRetailDW.bak, and then click OK.

14. On the Specify Backup dialog box, click OK.

15. Select the check box for ContosoRetailDW-Full Database Backup, and then click OK.

16. On the success dialog box, click OK.