This section describes how to build a SQL Server to manage the SharePoint databases. Part A details how to create a SQL server Virtual Machine in the Microsoft Azure Virtual Network created in section III - B. Part B details how to install SQL Server 2012 BI and configure it for use in a SharePoint 2013 farm. Most of these details are the same whether you are building a SQL Server on your own hardware or in Azure.
Below are links to all the sections of this Guide.
|
XV. Turn Windows Firewalls Back On for All Servers
|
A. Build the SQL Server VM for SharePoint Databases
This section describes how to build a SQL Server 2012 SP1 Virtual Machine in the Microsoft Azure Virtual Network you created in step III – B.1. Create New Azure Virtual Machine for MBP-SQL
a. Create a New VM Named MBP-SQLThis procedure provisions a Windows Server 2012 VM in Azure then installs SQL Server 2012 with Service Pack 1 on the server rather than provisioning a SQL Server 2012 VM from Azure. This choice is more economical if you already have an MSDN subscription or otherwise have the SQL Server 2012 install ISO because the Windows Server VM in Azure is less costly than and the SQL Server VM. As an alternative you may choose to provision a SQL Server from Azure: this will save you time but will cost more.
Note: take care to follow the instructions around steps 10 - 20 below in order to be sure to join this VM to the mbp.com domain, attach the VM to the Virtual Network mbp-net and assign the Static Private IP Address 10.0.0.5 to this VM. In short, stick to the plan we defined in the detailed physical architecture in section II – D – 4.
1. From the Microsoft Azure Portal (portal.azure.com), click the + NEW button in the upper left.
Azure displays the “Create” blade.
2. From the “Create” blade, select Compute.
Azure displays the “Compute” blade.
3. From the “Compute” blade, select Windows Server 2012 R2 Datacenter.
Azure displays the “Create VM” blade.
4. From the “Create VM” blade, enter the Host Name, User Name and Password from the plan
Field
|
Value
|
Description
|
Host
Name
|
MBP-SQL
|
New
machine will be MBP-SQ.mbp.com
|
User
Name
|
mbpadmin
|
The
local machine admin account
|
Password
|
$hareP0int
|
Use
a sufficiently strong password of your choice
|
5. From the “Create VM” blade, click PRICING TIER
Azure displays the “Choose your pricing tier” blade.
6. From the “Choose your pricing tier” blade, click View All
Azure re-displays the “Choose your pricing tier” blade showing all the many options.
7. Select the VM size matching the plan created in section II – D – 2. For my MBP TEST farm, I selected A5 Standard to get 2 Cores and 14 GB RAM. Then, at the bottom of the “Choose your pricing tier” blade click Select.
The A5 Standard option is an economical choice for a TEST farm. For a production SharePoint farm I would recommend you consider DS3 Standard which has faster disk IOPS and a local SSD.
8. From the “Create VM” blade, click OPTIONAL CONFIGURATION
Auzre displays the “Optional config” blade.
9. From the “Optional Config” blade, click OS SETTINGS
Azure displays the “OS Settings” blade.
10. From the “OS Settings” blade, click DOMAIN JOIN
Azure displays the “Domain join” blade.
11. On the “Domain join” blade, specify the domain established when you created the AD-DC VM in section III – 3 – B. In this example,
Domain
|
mbp.com
|
User Name
|
mbp\mbpadmin
|
Password
|
$hareP0int
|
12. At the bottom of the “Domain join” blade, click OK
Azure closes the “Domain join” blade
13. At the bottom of the “OS Settings” blade, click OK
Azure closes the “OS Settings” blade.
14. From the “Optional config” blade, click NETWORK
Azure displays the “Network” blade. Notice that Azure has defaulted to a Virtual Network with the same name as the Host Name. We must change this default so the SQL Server is on our mbp-net virtual network and had the Private Static IP address we planned.
15. From the “Network” blade, click VIRTUAL NETWORK
Azure opens two blades: the “Virtual Network” blade and the “Create a virtual network” blade.
16. From the “Virtual Network” blade, under “Use an existing virtual network”, select mbp-net, the network we created in step III – B – 1.
Azure closes the “Virtual Network” blade and we see the choice of Virtual Network displayed in the “Network” blade.
17. From the “Network” blade, select IP ADDRESS
Azure displays the “IP address” blade
18. From the “IP address” blade, under the “Private IP address” label, change the “IP address assignment” from DHCP to Static.
19. In the “IP Address” field, enter the Static IPV4 address we planned for the SQL server back in section II – D – 4 (10.0.0.5 in this case).
20. At the bottom of the “IP address” blade, click OK
21. At the bottom of the “Network” blade, click OK
22. From the “Optional config” blade, click STORAGE ACCOUNT
Select to use existing storage account rather than creating a new one (optional).
23. At the bottom of the “Optional config” blade, click OK
24. From the “Create VM” blade, click RESOURCE GROUP
Azure displays the “Resource group” blade.
25. From the “Resource group” blade, under “Use an existing group”, select the resource group we created in section II, MBPI in this case.
26. At the bottom of the “Create VM” blade, click Create
Azure displays the portal home page with the “Creating Windows Server 2012 R2 Datacenter” graphic while it provisions the new machine.
27. When the Azure Management Portal displays the new VM in status "Running" you may proceed to the next step
b. Attach additional disk to the VM
For database systems such as SQL Server, best practice for Azure environments is to attach an additional disk with Host Cache Preference = NONE for the data volume. You will use this data disk when you install SQL.
1. From the Microsoft Azure Portal (portal.azure.com), click the tile for MBP-SQL, the new VM you created in the preceding section.
Azure displays the “MBP-SQL Virtual machine” blade
2. From the “MBP-SQL Virtual machine” blade, click All settings
Azure displays the “Settings” blade
3. From the “Settings” blade, click Disks
Azure displays the “Disks” blade.
4. From the “Disks” blade, click the Attach New… button
Azure displays the “Attach a new disk” blade
5. From the “Attach a new disk” blade, enter the size of hard disk (in GB) you planned -- in this case 512 -- into the Size (GB) field.
Make sure to keep the Host Caching Preference = NONE.
6. At the bottom of the “Attach a new disk” blade, click OK
a
Now connect to the new VM and configure the new disk as drive F:
7. Click Connect from the button bar at the top of the “MBP-SQL Virtual machine” blade.
8. Your browser prompts you to open or save the MBP-SQL.rdp file. Save the RDP file with a name like MBP-SQL.rdp to your desktop.
9. Open the RDP file to open a remote desktop session. You must use the MBP-SQL\mbpadmin credentials to login.
10. If Remote Desktop Connection displays a certificate prompts re: The identity of the remote computer cannot be verified…, click Yes.
Windows displays the Server Manager Dashboard
11. Follow steps a thru h below to initialize the disk you attached to the VM and create a new volume to store SQL data.
a. From the Server Manage, click File and Storage Services --> Disks
b. On the DISKS list, right-click on the new disk Number 2 (will show Partition = Unknown) and click Initialize,
When prompted to Initialize Disk, click Yes to confirm the operation.
c. After initialization is complete, right-click the disk, click New Volume…
d. From the New Volume wizard, click Next to accept the default values in the first three screens of the New Volume Wizard
e. From the “Assign to a drive letter or folder” screen of the wizard, in the Assign to: option, pick drive letter F: to assign the new volume to. I use F: as the data drive because on most VMs the D: drive is already used for temporary storage and E: is used for a mounted ISO file. Click Next.
f. From the “Select file system settings” screen of the wizard, set the Volume label to something like data. We will use this volume to store the SQL databases when we install SQL. Click Next.
g. On the Confirmation page, click the Create button to create the disk.
h. When the Results screen displays "You have successfully completed the New Volume Wizard" click Close.
c. Verify that MBP-SQL is joined to the mbp.com domain
1. While logged in to the SQL Server MBP-SQL, Open Server Manager and select Local Server
2. In the PROPERTIES window, verify that the Domain property is mbp.com.
If you followed the steps in Section IV - A – 1 – a, then this VM should be a member of the mbp.com domain. In this case skip to section 2 (Prepare to Install SQL Server 2012) below. If not, follow steps a - f to add the VM to the domain:
a. next to the Workgroup, click where it says WORKGROUP
b. Windows displays the System Properties page
c. From the System Properties dialog
d. Set Computer Description = SQL Server (optional)
e. Under "To rename this computer…" click Change…
f. In the Computer Name/Domain Changes dialog, select Member of Domain and enter mbp.com
a
g. Click OK
Windows prompts for credentials of a user with authority to add MBP-SQL to the domain.
h. Enter the domain administrator credentials (MBP\mbpadmin in this case) and click OK.
If successful, windows displays "Welcome to the mbp.com domain"
8. Click OK
9. Windows prompts. "You must restart your computer to apply these changes". Click OK.
Subsequently in Active Directory, you will see MBP-SQL as one of the Computers in the domain.
When the server (MBP-SQL) is running again, you may proceed to the next section.
2. Prepare to Install SQL Server 2012 on MBP-SQL
Note that the steps in this section are independent of our choice to use Azure Virtual Machines for our infrastructure. These are steps you must take to prepare to install a SQL Server irrespective of whether the server is physical, virtual, or hosted in the cloud.
a. Download SQL Server Install Image to MBP-SQL
1. Open a remote desktop (RDP) session on the SQL server MBP-SQL and login as MBP\mbpadmin.
2. Turn off IE Enhanced Security for Administrators as follows:
a. From Server Manager, click Local Server.
b. In the PROPERTIES area, on the right, after “IE Enhanced Security Configuration” click On.
c. In the IE Security Configuration dialog, specify IE ESC is Off for Administrators. Click the OK button.
3. Create a folder named f:\downloads on the data drive to receive the SQL ISO download.
4. Launch IE and open msdn.microsoft.com. Sign in to MSDN using your Microsoft account that has an MSDN subscription so that you can download the SQL server install image.
5. Navigate to Subscriber Downloads and download the ISO file for SQL Server 2012 Business intelligence with Service Pack 2 (x64) - DVD (English).
b. Add Sql_Admin to Local Admin Group on MBP-SQL
Add the SQL Administrator Account – sql_admin – to the local administers group on SQL Server MBP-SQL. Subsequently we’ll use this account to install SQL server.
1. From Server Manager select Tools --> Computer Management from the upper right corner.
2. From Computer Management, under System Tools, expand Local Users and Groups.
3. Select the Groups folder.
4. Double-click on Administrators to open the Administrators properties box.
5. Under Members click Add…
6. Add MBP\sql_admin – the SQL Administrator.
7. Click OK.
8. Close Computer Management
9. Disconnect (Logout) as mbpadmin. Subsequent work should be be done signed in as sql_admin.
B. Configure SQL Server for SharePoint Databases
1. Install SQL Server 2012 BI with SP2 on MBP-SPDB
Obtain the ISO file for SQL Server 2012 Business intelligence with Service Pack 2 (x64) - DVD (English), Then proceed with SQL Server install. Note that Enterprise Edition with SP1 or SP2 will work fine and includes all components required for SharePoint.In this first install of SQL Server, we will only install one instance -- named SPDB -- for use by SharePoint; later we will install a POWERPIVOT instance (SSAS) on a different server:
1. Remote Desktop to MBP-SQL and login as MBP\sql_admin.
2. Mount the ISO file for SQL Server 2012 Business intelligence with Service Pack 2 (x64) - DVD (English)
3. Launch setup.exe
SQL Server displays the SQL Server Installation Center
5. From the left side panel, click Installation
7. Click OK
SQL Server displays the Product Key page. The product key is auto filled because this is an MSDN subscription download.
8. Click Next
SQL Server displays the License Terms.
9. Accept the terms and click Next
SQL Server may display the Product Updates page. If so, click Next
10. SQL Server displays the Install Setup Files page
11. Wait for SQL Server to install the setup files on the system.
SQL Server displays the Setup Support Rules page
12. Click Next
SQL Server displays the Setup Role page
SQL Server displays the Feature Selection page
15. Click Next
SQL Server displays the Installation Rules page.
16. Click Next
SQL Server displays the Instance Configuration page.
a. Select Named instance rather than Default Instance.
b. Enter the Named Instance name SPDB as planned
c. The Instance ID will automatically change to “SPDB” to match your Instance name
d. Change the Instance root directory to use a folder on your DATA drive F:
e. Click Next
SQL Server displays the Disk Space Requirements
18. Click Next
SQL Server displays the Server Configuration page.
a. Set SQL Server Agent to MBP\sql_service
b. Set SQL Server Database Engine to MBP\sql_service
c. Set SQL Server Integration Services 11.0 to MBP\ssis_service
20. Click Next
SQL Server displays the Database Engine Configuration page
22. Click the Data Directories tab
SQL Server displays the Database Engine Configuration page
SQL Displays the FILESTREAM tab.
a. Select Enable FILESTREAM for Transact-SQL access
b. Select Enable FILESTREAM for file I/O access
25. Click Next
SQL Server displays the Error Reporting page
26. Click Next
SQL Server displays the Installation Configuration Rules page
27. Click Next
SQL Server displays the Ready to Install page
SQL Server displays the Installation Progress page
29. Wait
SQL Server displays the Complete page
31. Close the SQL Server Installation Center
2. Add Setup User Account to dbcreator and securityadmin roles on SQL Server
Perform these steps to grant the necessary access to the SQL server for the Setup account (MBP\sp_admin)
1. Open a remote desktop on the SQL Server (MBP-SQL) and login using the SQL Server Administrator Account (MBP\sql_admin).
2. Launch the SQL Server Management Studio.
In the Connect to Server prompt, the Server name should be MBP-SQL\SPDB, the new Named instance of SQL Server you created in the previous step .
3. Click Connect
4. From SQL Server Management Studio, in Object Explorer on the left, expand the Security folder
5. Expand the Logins folder
6. Verify the server principal MBP\sp_admin already exists within the Logins folder. The preceding step added MBP\sp_admin with the sysadmin role. In this case, skip to step 8.
7. If MBP\sp_admin does not already exists within the Logins folder, follow these steps to add the MBP\sp_admin login:
a. right-mouse click on the Logins folder
b. Select New Login…
SQL Server Management studio displays the Login - New dialog
c. Click Search… and select Location = mbp.com . Enter the Setup User Account (AKA Install account: sp_admin in this case).
d. Click Check Names. Then click OK.
SQL Server Management studio displays the Login - New dialog.
8. Ensure the Server Roles for sp_admin include at least dbcreator and securityadmin. For least privileges you may remove sp_admin from sysadmin role. If sp_admin was previously created, double click to open the Login Properties dialog. Else if you just created sp_admin, the Login-New dialog is still open. In either case, from the Select a page area on the left side of the dialog, click Server Roles
SQL Server Management studio displays the Server Roles page.
10. Click OK
11. Exit SQL Management Studio
Note that the SharePoint Farm account, MBP\sp_farm in our case, will also be added with dbcreator and securityadmin roles to all the SharePoint databases. However, we don’t need to perform that configuration step now because SharePoint setup will add the sp_farm account to the appropriate server roles automatically. We run the SharePoint setup using the Setup User account. Note that it is a best practice to run all SharePoint install and farm creation scripts signed in as the Setup User account, not the Farm account.
3. Turn Off Windows Firewall for Domain Network
By default on Windows Server 2012 R2, Windows Firewall is turned on and blocks incoming connections. Turn off Windows Firewall for the Domain network so that other hosts such as MBP-CA can connect to the SQL Server instances on MBP-SQL.
1. Logged in to the SQL server as administrator, from the Server Manager, select Local Server
2. Locate the Windows Firewall setting and click Domain: On
3. Click the link on the left marked Turn Windows Firewall on or off
Windows displays the Customize Settings page
4. Under Domain network settings select Turn off Windows Firewall
For the isolated MBP domain, we can leave both Private and Public network settings with Firewall on.
6. Click OK
7. Close the windows firewall dialog
4. Configure SQL Server Maximum Server Memory
1. Launch SQL Server Management Studio and connect to the named instance (SPDB in this case)2. Right click the SQL Server instance
3. Select Properties.
4. From the Sever Properties dialog, select Memory
Following guidance I received from Marcus Crast, we'll set the Maximum server memory (in MB) to 60% of the total (14 GB) available memory on the server, leaving the remaining 40% for Windows and the other services. So on a 14 Gb server, 14 x .6 = 8.4 GB.
5. Set Maximum server memory = 8400 MB (if your server has 14 GB).
6. Click OK
5. Set SQL MAXDOP
Ensure that on the SQL Server instance, MAXDOP (Max degree of parallelism) is set to 1. (The default is 0.)
For explanation why this is required, see http://technet.microsoft.com/en-US/library/ms189094.aspx
1. In SQL Server Management Studio, in the Object Explorer, right-mouse click on the SQL instance you will be using for the SharePoint databases (SPDB in this case).
2. Select Properties
3. In the Server Properties - <ServerName> dialog, select the Advanced page.
4. Under Parallelism, change the Max Degree of Parallelism property to 1
5. Click OK.
6. Close SQL Server Management Studio
This concludes Section IV of the MBP SharePoint 2013 BI Farm Setup Guide. In this section we added a Virtual Machine for SQL Server, installed SQL Server 2012 R2, and configured the SQL named instance that will be used by SharePoint. In Section V we will add the Virtual Machines that will become our SharePoint servers.
Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking.
ReplyDeleteAzure Online Course Hyderabad