This post will be the first post in a series of posts to setup a SharePoint 2013 Business Intelligence development environment. This first post will cover the base setup with virtual servers and virtual SharePoint 2013 development environment using Virtualbox .
Later there will follow posts cover a complete SharePoint 2013 environment configured with all Business Intelligence services (Excel Services, Reporting Services, Power View and Performance Point Services) and kerberos authentication.
Kerberos authentication is usually needed when you are running SharePoint 2013’s Business Intelligence services in an environment where the data (Data warehouse(s)/Cubes) are stored on a different machine than the SharePoint server where reports and analyses are viewed.
SharePoint 2013 BI Development Environment Tutorial
- >SharePoint 2013 development environment using Virtualbox<
- Install SharePoint 2013 SP1 using AutoSPInstaller
- Configure SharePoint 2013 Business Intelligence Services
- SharePoint 2013 Business Intelligence kerberos configuration
Contents
Please note that this post will only cover the installation and setup of the virtual machines and not the actual SharePoint 2013 installation. The SharePoint 2013 installation will follow later in the post Install SharePoint 2013 SP1 using AutoSPInstaller.
The setup will look as the diagram below:
Note: The resources assigned to the machines in this setup are at the bare minimum and only recommended for testing. For any serious development and heavier use you will most likely need to increase the amount of RAM and vCPU’s. I would recommend about twice the resources for all the machines if possible. If you are not using a fast SSD disk go get one – you will need it! If you wonder where the lame server names are from I have taken them from Disney s’ story of The Gummibears.
Prerequisites
Below is required software installation medium/ISO files. Please note that you should use the R2 version of Windows 2012 and SP1 of both SharePoint 2013 and SQL Server. If you use lower versions some of the Business Intelligence features that are to be installed at a later stage might not work!
For this post:
- Microsoft Windows Server 2012 R2
- Microsoft SQL Server 2012 SP1
(Standard edition or better) - Microsoft SQL Server 2012 CU 8 (Cumulative Update 8)
Download from: http://support.microsoft.com/kb/2917531
For the upcoming series of SharePoint 2013 Development Environment posts
- Microsoft SharePoint Enterprise Server 2013 SP1
(Note that enterprise is required for Business Intelligence features)
Install Oracle Virtualbox
Oracle Virtualbox is a software that allows you virtual machines (multiple virtual operating system installations) on your PC. There are several other alternatives around like Microsoft Hyper-V and VMWare vSphere however I choose to write this post on Oracle Virtualbox since it is cross-platform and open source. To get started grab the installer package for your system from:
https://www.virtualbox.org/wiki/Downloads
(This post is written and tested using Virtualbox 4.3.10 r93012 with Windows 8.1 as host system)
- Install Virtualbox from downloaded installation file
- Start virtualbox
- Click File –> Prefrences –> Verify or change the default machine folder.
This is where Virtualbox will but Virtual Machines and their corresponding virtual harddisk files as default. Preferably use a hard drive with enough disk space – it should be able to host Windows Server 2012 R2, SharePoint 2013 Enterprise Server SP1, SQL Server 2012 SP1 and then additional contents and software that you plan to use. e.g. Visual Studio, sample data etc. For this guide you will probably need at least 50gb of free space. (Since we use an immutable disc image as base we will only need to reserve space for one Windows installation)
Prepare Virtualbox immutable base image
A base image in Virtualbox is called immutable image. An immutable image is a virtual hard disk file that cannot be changed. When you create a immutable image in virtualbox that file can be used as base file for several other virtual machines hence saving time and disk space. In this setup we will create an immutable hard disk file with a base installation of Windows Server 2012 R2. We will then use this file as base for the virtual machines that we will create. Then the other virtual machines create will only hold the file differences from the immutable image. This will save both disk space and time when we create new virtual machines.
To create the immutable hard disk file we will first need to create a new virtual machine that creates and uses that file. When we are ready we can remove the virtual machine but save its’ hard disk file (VDI file) and use it as a base image to other virtual machines.
- Create new virtual machine for use as base imageClick Machine in the menubar–> New –>
Name: Windows Server 2012 R2 Immutable.vdi (or whatever you want), Type: Windows, Version: Windows 2012 (64-bit) –> Next –>
Memory size: 2048 –> Next –>
Create virtual harddrive now –> Select VDI –> Next –>
Select Dynamically allocated –> Choose location, Drive size: 500GB (Since it is dynamic it will not reserve this amount of space on your drive so there is no reason to be sparse on disk size) –> Click Create - Mount Windows 2012 R2 ISO fileSelect your newly created machine in the list and click settings –>
Storage –> Select empty CD/DVD icon –> Click the small CD icon to the upper right:
Browse to and select your Windows 2012 R2 ISO file. Click OK. - Click Start to run your virtual Machine (VM)
- Install Windows
- Install Virtualbox Guest Additions CD Image.Click Devices –> Insert Guest Additions CD Image
Open windows explorer –> CD/DVD device –> Run through the installation
(The installation will provide windows with devices drivers for Virtualbox) - Install Windows Updates and other software that you would like to have in all of your virtual machines
Run C:\Windows\System32\Sysprep\sysprep.exe –> Click generalize –> Shutdown:
- When sysprep is complete next time when the virtual machine is started it will start with a clean windows installation. E.g. you have to fill in administrator password and initial setup will start. (Just as when you buy a new computer and start it for the first time)
Make virtual disk immutable
- In Virtualbox main window: Click File –> Virtual media Manager –> Select disk –> modify –> Check immutable:
- Select your created VM and right click –> Remove . Then make sure to click Remove ONLY. This will remove your VM but not its’ virtual hard drive file which will make an immutable VDI file for use as our base image.
- We now have a immutable disk file for use as base imge when we create the rest of our virtual machines.
Configure Virtualbox virtual network
This section will create a virtual network where our virtual machines can communicate with each other. The network will have access to your computer network through NAT (Network Address Translation) which also will provide internet access for your virtual machines.
- In Virtualbox main window: Click File –> Preferences –> Network tab –> Click the small “cirtucuitboard icon” to the upper right to add a new NAT Network –> Enter details as below:
Setup AD/DNS virtual machine
- Create new virtual machine
Instead of creating a new disk file during the wizard select use existing disk file and choose the file that we made an immutable disk image in previous step:
- In Virtualbox main window: Select the newly created machine –> Settings:
– General tab –> Advanced tab: Configure Shared Clipboard and Drag’n Drop if you prefer to be able to use it
– System tab: Adjust RAM and CPU to fit your needs. (I run with 1vCPU, 1GB RAM)
– Network tab: Select the nat network that we created earlier:
– Click OK. - Start the VM
- In menubar: Select Machine –> Take Snapshot –> OK
(Important! The snapshot will create a new VDI file that holds the VMs file difference towards the base image file so without it all changes will be lost upon reboot) - Enter user information and password
- Change computer name (Zummi)
- In Control Panel –> Network and Sharing Center –> Change adapter settings –>
Right click ethernet adapter –> Properties –> Select Internet Protocol Version 4 TCP/IPv4:
Change network to static and use the ip-addresses that matches our virtual network created earlier:
- In Server Manager: click Add Roles and features and add Active Directory (AD) and DNS roles to server:
- In Server Manager: Click the “flag” and Promote this server to a domain controller:
– Add new forest
– Root domain name: gummis.com
– Type in domain password
– Click next addiotnal steps to the prerequisites check:
– Install - In Active Directory Computers And Users create SQL service account user:
SQL2012_Services Used for SQL server services (sql server, ssas, tabular, power opivot, agent etc.) - Done
Setup database virtual machine
- Create new virtual machine (using the immutable disk file as before)
- Take Snapshot
- Enter user information and password
- Change TCP/IP network settings:
IP: 10.0.2.231
Netmask: 255.255.255.0
Gateway: 10.0.2.1
DNS: 10.0.2.230 - Change computer name (Tummi)
- Join gummis.com domain
Note, if you get the following problem:
– Run C:\Windows\System32\Sysprep\sysprep.exe
– Select enter system audit mode
– Reboot
- In menubar: Select devicse –> CD/DVD –> Chose from virtual CD/disk file –> Select your Microsoft SQL Server 2012 SP1 ISO file
- Install default SQL and Analysis Services instance (MSSQLSERVER). This instance will be used for Business Intelligence content in this scenario and not for SharePoint databases. Open the installation from the CD/DVD drive in windows explorer. Run setup.exe and go through the installation wizard with the following configuration shown in the images below. For more details around SQL Server installation please see:
http://msdn.microsoft.com/en-us/library/ms143219.aspxSelect features:
- Configure service accounts.
Use SQL2012_Services account for SQL services:
Do not forget to add yourself/domain admin account as administrator account:
- In the SSAS configuration chose to install Analysis Services in multidimensional mode
- Finish the next steps of the SQL installation wizard.
- Install another SQL Service instance named SHAREPOINT for use with SharePoint content, administration and service application databases. Feature selection:
Change name of instance:
Configure service accounts:
Do not forget to add yourself/domain admin account as administrator account:
Finish the rest of the steps in the SQL Server Feature installation wizard. - Install SQL Server CU 8
– Download from http://support.microsoft.com/kb/2917531
– Run SQLServer2012-KB2917531-x64.exe to apply the update. Apply the update for all SQL/SSAS instances. - Open and enable service ports.
Note: If you disable the firewall you will not need to add firewall rules and configure dynamic ports and can skip those settings.
Open windows firewall advanced settings and add the following inbound rules:
- Open SQL Server Configuration Manager and enable TCP/IP for all instances:
Named instances have dynamic ports by default which we need to change to static ports if the firewall rules above should work. For more information on how to change a SQL Server instance to use static ports please see: http://technet.microsoft.com/en-us/library/ms177440.aspx.
Configure static ports for our SharePoint named instance as below. Do not forget to scroll down and change all of the bindings to be static.
- Create SQL Alias in SQL Configuration Manager (good practive if you would move/migrate server in the future)
- Done
- Create new virtual machine (using the immutable disk file as before)
- Do not forget to add more resources to this mahine. In virtualbox main window: select VM –> Settings:
– System Tab:
– Increase CPU (I choose 2 vCPU)
– Increase RAM (I choose 8 GB)
(As mentioned before these are absolute minimum settings and only recommended for testing – at least double it for heavier use) - Take Snapshot
- Enter user information and password
- Change TCP/IP network settings:
IP: 10.0.2.232
Netmask: 255.255.255.0
Gateway: 10.0.2.1
DNS: 10.0.2.230 - Change computer name (GRAMMI)
- Join gummis.com domainNote, if you get the issue with duplicate SID run sysprep as mentioned in step 6 of Setup database virtual machine
- If you are going to access your SharePoint server from the network as well do not forget to login to add firewall inbound rules for TCP/IP ports 80 and 443.
- Done (Actual SharePoint 2013 SP1 installation will be done using AutoSPinstaller in upcoming posts)
Hi,
I’m wondering if you could elaborate a bit further on the “Please note that you should use the R2 version of Windows 2012 and SP1 of both SharePoint 2013 and SQL Server.” what specific might be affected by this?
Many thanks!
Hi Graham,
SharePoint 2013 WITHOUT SP1 is not offically supported by Microsot to run on Windows Server 2012 R2. Also I have had problems to get all BI features with SQL Server versions lower than 2012 with SP1 to work. It did not even work properly when adding the SQL 2012 SP1 update after installation but I had to uninstall the old SQL Server 2012 and install a fresh SQL Server 2012 with SP1 to get it working. Among others I rembered problems with Reporting Services Add-In did not register properly. There where also other issues I cannot remember now however I wasted way to much time before I figured out I needed to have SQL Server 2012 with SP1. Also if you read the overview/planning docs from Microsoft they recommend SQL Server 2012 with SP1 as lowest version when using BI features.
http://technet.microsoft.com/en-us/library/ff945791%28v=office.15%29.aspx#BI
“Note that to use the business intelligence (BI) tools in SharePoint Server 2013 you must install SQL Server 2012 with Service Pack 1 (SP1), 64-bit version.”
Hope it helps!
Kind regards,
Jonas
Great tutorial!
I was getting an error when I tried to make the disk immutable but was able to fix things by clicking Release first then setting the immutable option.
Rob
Thanks for the great tutorial.
The DNS for the SQL server and SP seems to be wrong.
DNS: 10.0.2.231 I believe should be DNS: 10.0.2.230
And the IP for the SP server IP: 10.0.2.231 I believe should be IP: 10.0.2.232
Hi Blake,
You are correct, thanks for letting me know. I have updated the tutorial with correct IP’s now 🙂
//Jonas
Hi Jonas,
Could you please share the result (a virtual machine file that BI service configured, and the essential usernames, passwords)?
I will develop PerformancePoint on that.
Idea from:
I see you said that “I want to simplify for others to spend less the same time and effort on things that I think I have spend to much time on doing myself”
Just a question, why don’t you upload your latest result? so that the others save their time more.
I think that there will be many people like me.
You helped me much.
Thank you!
Hi Ten,
I’m not able to share a virtual machine. That would imply licensing violations on both Microsoft Windows, Microsoft SQL Server and Micrsoft SharePoint. Also I would need to cater for both bandwith and diskspace of virtual machines which I rather not. //Jonas
Thank you so much for this tutorial!
Is it normal that there is no internet access with this NAT Network? When I add second adapter with NAT to have internet access during SQL Server installation (for downloding updates), setup complains about missing domain trust when I want to configure the Service Accounts.
What am I doing wrong?
Great tutorial, thank you so much!