SQL Server is the muscle behind SharePoint. SQL Server is the relational database that SharePoint uses to store most of its content; unless BLOB (binary large object) storage is used. We’re not going to cover configuring a BLOB store in this book. The version of SQL Server that we configure will hopefully have four disks inside the VM; but don’t worry, if your hardware does not have enough SCSI interfaces to support four disks, you’ll still be able to get by with just one, as this is just a home lab, not a live-fire situation.
In this chapter, you’re going to spin up and configure SQL Server in home lab fashion. You might have already created the SQL Server machine or maybe you waited until now, either way is fine. If you already created it, you’re going to add more disks to it and then you’ll add SQL Server and configure SQL Server for SharePoint.
SQL Server communicates over port 1433 by default. We’re not going to change that in our lab. Each one of the SharePoint VMs will utilize the SQL client to set up an alias. This alias allows for portability of databases between SQL servers.
In this book, SQL Server will only use one account for the install and for its operations. In a production, you would configure much more than one account. Our SharePoint farm will be configured very closely to a production farm in regards to service accounts and least privilege.
Are you ready for some SQL?
Let’s do this thang!
Caution
This part is really fun and you could decide that you’d like to become an SQL DBA. The “Do You Want to Know More?” section has links to doing just that!
Installing SQL
Create Server and Add Drives: Preferred but Optional
Create the server just as you would for the SharePoint Member servers, except for the drives.
Create a VM and install Windows. If you want, you can add the additional disks to the server before installing Windows. If you opt to go that route, just follow steps 3–10 for each disk: L, M, and N. Then when you install Windows, remember to select Drive 0 Unallocated space during the operating system (OS) install (see Figure 9-1).
Figure 9-1. Installing Windows when the VM already has the additional disks
Give the server a static IP, set remote access, set the time zone, set updates, update the server, set the firewall and IEESC to Off, and then power it off.
Note If your host is unable to support additional drives, you can skip steps 4–25 and go straight to step 26. But, please at least try to add drives L, M, and N.
If you didn’t add the drives before the OS install, add the following drives, now:
M: 127GB, Data
L: 30GB, Logs
N: 30GB, Temp
If you’re having to skip to step 26, then power your VM back on; otherwise, add an additional hard disk to the member server by first shutting down the VM, and then by selecting the VM from the Hyper-V Manager, right-clicking the VM, and then clicking Settings…, as shown in Figure 9-2.
Figure 9-2. VM Settings
Click Add Hardware, select SCSI Controller, and click Add (see Figure 9-3).
Figure 9-3. SCSI Controller
Click Hard Disk and then click Add, as shown in Figure 9-4.
Figure 9-4. Add a hard drive
Make sure that “Virtual hard disk” is selected and click New (see Figure 9-5).
Figure 9-5. Virtual disk selected, click New
Click Next, as shown in Figure 9-6.
Figure 9-6. Click Next
Make sure that “Dynamically expanding” is selected and then click Next (see Figure 9-7).
Figure 9-7. Dynamically expanding
Give the disk a name that corresponds with the server. In Figure 9-8, I added the disk for SQL Data.
Figure 9-8. Name your disk, because all disks deserve names
You can keep the default size of 127GB or set it to a smaller number (e.g., 50GB). It’s up to you—you know your host machine. I set my second disks to 50GB, since they’re only for logging (see Figure 9-9).
Figure 9-9. Enter size or leave at default
Click Finish (see Figure 9-10).
Figure 9-10. Finish adding the disk
Repeat steps 3–12 for the L: and N: drives.
After you’ve added all three of the additional drives, open the VM and the Run bar. Type diskmgmt.msc, and then press Enter or click OK (see Figure 9-11).
Figure 9-11. Open Disk Management
Wait for Disk Management to open, and then set up your disks (see Figure 9-12).
Figure 9-12. Sometimes it opens slowly
Right-click Disk 1 and then click Online to bring it online (see Figure 9-13).
Figure 9-13. Bringing additional drives online
After the status changes from Offline to Not initialized, right-click and then click Initialize Disk (see Figure 9-14).
Figure 9-14. Getting your disk ready, initialize your disk
Select GPT and click OK (see Figure 9-15).
Figure 9-15. Choose GPT and click OK
Right-click the area under the black bar for the unallocated disk and click New Simple Volume, as shown in Figure 9-16.
Figure 9-16. New Simple Volume a drive
Click Next (see Figure 9-17).
Figure 9-17. Click Next
Keep the default setting of the entire drive for the volume size, and then click Next.
Select Drive letter M (or L or N) and then click Next.
Change the volume label to Data for drive M (change it to Logs for drive L and Temp for drive N). Perform a quick format and then click Next (see Figure 9-18).
Figure 9-18. Change the label to Data
Click Finish on the Completing the New Simple Volume Wizard screen and then repeat steps 16–23 for Drives N: and L:.
Admire your newly added drives in all their glory (see Figure 9-19).
Figure 9-19. M: Data, N: Temp files, and L: Logs
Update the server (follow the same steps as the previous exercise). Go ahead and restart it if needed.
One More GPO: CEP
This could’ve been done in Chapter 7, but it really wasn’t needed until now because we are starting to really connect the farm at this point.
Log in to your domain controller with your domain administrator account. This account is named in the following format domainadministrator, where domain equals the name of your domain (e.g., tailspintoys) and administrator equals the name of your admin account (Lucy, if you followed the rename administrator exercise without any deviation).
Open the Group Policy Management console (gpmc.msc).
Link a GPO to the domain (right-click the domain and click “Create a GPO and link….”.)
Name the GPO “Certificate Enrollment Policy” or you could call it “CEP Settings” (see Figure 9-20).
Figure 9-20. No need to overuse the word policy
Edit the GPO and navigate to Computer Configuration ➤ Policies ➤ Windows Settings ➤ Security Settings ➤ Public Key Policies and double-click Certificate Services Client - Auto-Enrollment (see Figures 9-21 and 9-22).
Figure 9-21. Edit the policy
Figure 9-22. Finding the setting
Change the Configuration Model to Enabled, click Apply, and then click OK (see Figure 9-23).
Figure 9-23. Changing the setting
Installing SQL in Your Home Lab
Does it seem like it took a long time to get to this point? Hopefully not. Let’s install SQL and then let’s move on to Chapter 10 and install SharePoint 2016.
After the server is updated, the time zone changed, the remote access set, IEESC turned off for admins, and the firewall turned off, it’s time to configure SQL—even if you couldn’t add the additional drives.
Log in to the SQL VM with the svc_sqldba account. If you are prompted that you cannot log in, log in with the domainadministrator (e.g., TailspintoysLucy).
Open the Computer Management console (compmgmt.msc) from the Run bar or by right-clicking the Windows charm icon (formerly the Start button) and then selecting Computer Management (see Figure 9-24).
Figure 9-24. Computer Management
Once compmgmt.msc opens, navigate to Local Users and Groups ä Groups and open the Administrators group. Add svc_sqldba to the local administrators.
Note If you had to add the svc_sqldba to the local administrators, you might have missed something in Chapter 7. Go back and review the exercise on creating a Restricted Groups Policy.
Connect the SQL install disc to the VM by going into the VM settings and ejecting the disc, if one is inserted, and then inserting the SQL disc. The following explains one way to accomplish this.
Open the Hyper-V Manager, right-click your SQL VM, and click Settings…. Click the DVD drive. Browse to wherever on God’s green earth you’ve stored the SQL Server 2014 ISO that you downloaded by clicking Browse…. Then once you’ve selected it, click Apply (see Figure 9-25 and my poor attempt at humor). What’s shown in Figure 9-26 is one step deeper than what’s shown in Figure 9-25.
Figure 9-25. I’m not OCD, ADD, or anything. I just like organized folders—hey, look, a squirrel!
Figure 9-26. Remember to use SQL 2014 or higher
Now that the database admin account is an administrator, you can install SQL using this account in your home lab SQL database server.
Log in to the SQL VM with the database administrator (DBA) account and briefly open Computer Management to drive into local admins, just to make sure that you’re logged in administratively.
Tip In a live-fire situation, you often times encounter issues that are not planned for, and sometimes in this book, I’ve purposefully left out a few things to allow you to encounter something like this. SQL Server needs the .NET Framework 3.5 installed. You can go ahead and install it from Server Management, add roles and features, or you can follow along and see what happens if you don’t. Either way, your SQL will be fine. I know, I know—I let the cat out of the bag. I’m not sorry, though, you shouldn’t put cats in bags. I mean what kind of a person puts a cat in a bag, anyway?! (To all animal lovers, sorry that was a poor attempt at humor.)
Open the Windows Explorer (Windows Key + E, or click the icon to open Windows Explorer). Open the DVD drive, double-click setup, and wait. It might take a few seconds to open; just be patient (see Figure 9-27).
Figure 9-27. SQL setup
Tip Patience is one of the best character traits a developer or admin can cultivate. If only I had more of it… Arghh!
When prompted “Do you want to allow the following program to make changes to this computer?”, click Yes unless you already turned off UAC, in which case you won’t get prompted (see Figure 9-28).
Figure 9-28. UAC prompts can be turned off, see the Introduction section of this book
SQL Server 2014 starts initializing the installer, as shown in Figure 9-29.
Figure 9-29. Initialization happens
After the installer initializes, you’ll be presented with the planning screen. Normally, you would run the Hardware and Software Requirements, at the very least, in a production scenario. For the purposes of the home-lab simulated environment, just click Installation (see Figure 9-30).
Figure 9-30. Installation center planning page
Click “New SQL Server stand-alone installation or add features to an existing installation” since this is a new install (see Figure 9-31).
Figure 9-31. New stand-alone install, non-cluster
The product key for SQL is always there; just click Next (see Figure 9-32).
Figure 9-32. Click Next
You can check the Turn on CEIP box if you want, or you can leave it unchecked. Accept the license terms, and then click Next (see Figure 9-33).
Figure 9-33. SQL Licensing for prod and non-prod
Next, SQL Server 2014 Setup starts installing the setup files. Wait for it to finish (see Figure 9-34).
Figure 9-34. Installing setup files
When presented with the Setup Role, choose the default SQL Server Feature Installation, and then click Next (see Figure 9-35).
Figure 9-35. SQL Server 2014 Setup Role
Select Database Engine Services, Client Tools Connectivity, Client Tools Backwards Compatibility, Management Tools - Basic and Complete, as shown in Figures 9-36 and 9-37. Leave the default instance on the C: drive; but please be aware that in a production environment, you may or may not change this location. Click Next.
Figure 9-36. Feature Selection part 1
Figure 9-37. Feature Selection part 2
Wait for the features to install. If you decided to forego installing.NET Framework 3.5 earlier, you’ll need to do that now if you are seeing the same failure as shown in Figure 9-38.
Figure 9-38. Feature Rules say you need to install .NET Features
Try to click Next. You should see the screen shown in Figure 9-39. If not, click the failure with the red icon. Click OK, and then go to step 47.
Figure 9-39. Rule Check Result; install the .NET Framework
Open the Server Manager (servermanager.exe) and click Manage ➤ Add Roles and Features (see Figure 9-40).
Figure 9-40. Manage, Add Roles and Features
Click Features in the left-hand menu. Select .NET Framework 3.5 Features and click Next (see Figure 9-41).
Figure 9-41. .NET Framework 3.5 Features install
The .NET Framework 3.5 Features installation starts and notes that it includes .NET 2.0 and 3.0 (see Figure 9-42).
Figure 9-42. .NET 2.0 and 3.0 included at no additional charge, except disk space and time
Navigate back to the same location in the SQL installer, as shown in Figure 9-39, and click Next. This should result in the Instance Configuration screen. Since we’re not creating a named instance and since we don’t already have the default instance installed, we can select the default instance. Leave the InstanceID at its default value of MSSQLSERVER, and then click Next (see Figure 9-43).
Figure 9-43. MSSQLSERVER default instance
Note Steps 20–22 are all together. Don’t click Next until you’re at step 22. Sorry to keep kicking this horse. If this were a production build, you would use separate accounts, but for our home lab, we’ll use svc_sqldba, or its equivalent.
Click the account name next to SQL Server Agent, where it reads NT ServiceSQLSERVERAGENT in Figure 9-44, change it to the domain account, (e.g., tailspintoyssvc_sqldba), and enter the password. Repeat for the database engine. Change the Startup Types to automatic, and then take look at the Collation tab (see Figure 9-46).
Figure 9-44. Change the accounts to use service accounts
Figure 9-45 shows after the accounts and passwords have been changed (compared to what is shown in Figure 9-44) to use the service account. Keep in mind that in a production environment, we’d use more than one service account on this screen.
Figure 9-45. After you have the passwords entered, check out the collation.
The default collation will work just fine; click Next (see Figure 9-46).
Figure 9-46. Default collation is OK
The database engine will run on Windows authentication mode. Click Add Current User, add your DBA account (e.g., tailspintoysLucy) not the domain admin (see Figure 9-47).
Figure 9-47. Click Add Current User
Don’t proceed until you see your DBA account, as shown in Figure 9-48.
Figure 9-48. Click Data Directories after you have added your DBA account
Note All the steps are important, but steps 22 and 23 are extra important.
Note If you were able to add the additional drives to your VM, perform step 24, shown in Figure 9-49; otherwise, just click Next.
Figure 9-49. Change directories
Click Data Directories after you add your DBA account. Modify them so that the data root is drive M, temp files are drive N, and user Logs are on drive L. Place backups on drive M. Click Next (see Figure 9-49).
Review the Ready to Install screen and then click Install to install a SQL Server instance (see Figure 9-50).
Figure 9-50. Installing SQL Server instance
After the install completes, review the green lights, and then click Close (see Figure 9-51).
Figure 9-51. Click Close after review
You just installed SQL Server!
If SQL Server were in production, the default instance might already have been used and you might have been installing a named instance. This is not a big deal to you, or to SharePoint, as long as both of you know the name of the instance. Step 50 would be different in that case.
Congrats on installing SQL, and if this is your first time installing SQL, a super big congrats!
Update SQL Server After Install
Check the server for updates using the svc_sqldba account. Log in to the server (see Figure 9-52).
Figure 9-52. Log in with the SQL DBA
Make sure the option to update automatically is not enabled, and make sure that you see a date and time in “Updates were installed”. You don’t want to see something similar to Figure 9-53.
Figure 9-53. Updates were never installed, and the server was last checked never
If you see that updates have not been installed, click “Let me choose my settings” (see Figure 9-53). Then select “Download updates but let me choose whether to install them” (see Figure 9-54). Click OK.
Figure 9-54. Choose when to install them, download them
The server will start checking Windows Update for updates, and then it will download them (see Figure 9-55).
Figure 9-55. Server starts checking for updates; make sure you get them all
After the updates are downloaded, apply them and then move on to the next exercise of a little bit of configuration of SQL Server. Select all of the updates for download and install (see Figure 9-56).
Figure 9-56. Click the optional updates; make sure you install all the updates
Configure SQL Server Memory Consumption
Open a Run bar and type SSMS. Click OK to open SQL Server Management Studio.
When the Connect to Server screen appears, make sure that Server Type is Database Engine and that Server Name is SP2016SQL (or the NetBIOS name of your SQL VM). Make sure to use Windows Authentication and then click Connect. Sometimes the SSMS is unable to connect; when this happens, you’ll need to go through steps 3–11. Proceed to step 3 and test whether you can open the SQL Server Configuration Manager (see Figure 9-57).
Figure 9-57. Log in to the SSMS
If you receive a message that the Management Studio cannot connect to SQL, then you need to open the Configuration Manager and make sure that the agent and the instance are turned on. If you receive an error when you open the Configuration Manager, go to step 6 of this exercise (see Figure 9-58).
Figure 9-58. Can’t connect to SQL, big scary message, never fear you’re here!
Open a Run bar and type SQLServerManager12.msc. Click OK to open the SQL Server Configuration Manager. Click Yes at the UAC prompt if you didn’t disable it. When the SQL Server Configuration Manager opens, start the SQL Server (MSSQLSERVER) instance and the SQL Server Agent (MSSQLSERVER) for the default instance. Figure 9-60 shows them both stopped (see Figures 9-59 and 9-60).
Figure 9-59. The Introduction section before Chapter 1 describes how to turn this off
Figure 9-60. Stopped SQL services (my son’s football number was 60; he was a defensive lineman who stopped the ball)
Just right-click the service to start it, wait for it to start, and then do same for the agent (see Figure 9-61).
Figure 9-61. SQL Services start (sorry, no memory associated with 61)
After they are started, you should see two green lights, as shown in Figure 9-62.
Figure 9-62. SQL Server is ready to accept incoming calls and process requests
Tip But what if you get a “Cannot connect to WMI provider” error message when you try to open SQL Server Configuration Manager in SQL Server? You do not have permission or the server is unreachable (see step 6 and Figure 9-63). Make sure that the file exists before you run the code in step 11. Don’t worry about this if it seems like it’s jumping around, just follow the steps in order, and then circle back after you correct the issues that naturally exist on SQL Server 2014 and Server 2012 R2.
Figure 9-63. The sqlmgmproviderxpsp2up.mof file exists
If you get a “Cannot connect to WMI provider” error message when trying to open the Configuration Manager (SQLServerManager12.msc), you do not have permission or the server is unreachable. You’ll need to open an administrative command window and enter mofcomp %programfiles(x86)%Microsoft SQL Server number Sharedsqlmgmproviderxpsp2up.mof, replacing the numbers with the correct number of the SQL version that you’re using. If you’re using SQL 2014, then use 120 for the number (see Figure 9-63).
To get an administrative command window, open cmd from the Run bar, right-click the icon and pin it to the taskbar, and close the command window (see Figure 9-64).
Figure 9-64. Admin command prompt, pin it
With the command window closed, perform a Shift-right-click on the icon and choose Properties. (To perform a Shift-right-click, hold down Shift while you right-click the icon). Once Properties opens, click Advanced (see Figure 9-65).
Figure 9-65. Command prompt properties, click Advanced
After clicking Advanced, check the “Run as administrator” box and then click OK, click Apply, and click OK (see Figure 9-66).
Figure 9-66. Run as administrator
Now, click the command window and notice that it opens with Administrator in its title. Close the command window (see Figure 9-67).
Figure 9-67. Administrator command prompt, what a beautiful thing
Open the Windows Explorer (Windows key + E). Navigate to C:Program Files (x86)Microsoft SQL Server120Shared and verify that the file named sqlmgmproviderxpsp2up.mof exists (see Figure 9-63). Open the command window administratively and run the following one-liner (also see Figure 9-68):
Figure 9-68. Now it will open for you
mofcomp "%programfiles(x86)%Microsoft SQL Server120Sharedsqlmgmproviderxpsp2up.mof"Go back to step 3 and open Management Studio to connect to the instance, and then come right back to step 13.
Right-click the SQL Server object and select Properties (see Figure 9-69).
Figure 9-69. SQL Server Properties
Select the Memory tab, adjust the max memory that SQL will use to a number more in line with your server. For example, if you gave your VM 5GB of RAM, you might want to cap SQL at 4GB, as shown in Figure 9-70.
Figure 9-70. Set the RAM that SQL can use to about 80% of what your server has installed
There are some additional steps related to the SharePoint installation that will bring you back to SQL Server. The only reason we’re not doing them here is because they really deserve to be part of the chapter on installing SharePoint 2016. This is for two reasons: (1) These settings are specific to a SharePoint install and would definitely not want to be overlooked by someone who bought this book simply for Chapter 10. (2) They are prerequisites for getting SharePoint installed, which goes hand in hand with point 1.
By nature, SQL Server is capable of processing requests in an asynchronous fashion in regards to the number of server processes used per request. In other words, SQL Server can process more than one request in a single server process. With the introduction of SharePoint 2013, you only want SQL Server to use a single server process for each request. As a result, the maximum degree of parallelism is set to 1. There are many other best practices for SQL in regards to SharePoint that we didn’t touch in this chapter.
In the next chapter, we’ll get into some of the best practices, but not all of them, in regards to SQL for SharePoint.
Do You Want to Know More?
Table 9-1 provides additional information.
Table 9-1. Here If You Want to Dive Deeper
Topic | URL |
|---|---|
Error message when you open SQL Server configuration manager | |
Best practices for SQL Server in a SharePoint Server farm | |
Microsoft SQL Server certifications | https://www.microsoft.com/en-us/learning/sql-certification.aspx |
SQL Server 2012 T-SQL Recipes by Jason Brimhall et al (Apress, 2015) | |
Beginning SQL Server 2012 Administration by Robert Walters and Grant Fritchey (Apress, 2012) | |
Pro SQL Server Administration by Peter Carter (Apress, 2015) |
Summary
In this chapter, you created an SQL instance, configured to it run on separate drives, and learned where you would use least privileging; but opted to use the single service account. You also solved the inability for SQL Configuration Manager (SQLServerManager12.msc) to open on Server 2012. You finished the chapter by limiting SQL Server to 4GB, or some number less than the amount of RAM that you gave your SQL Server when you built it.
In the next chapter, you’ll have many exercises as you install SharePoint 2016. This will be a lot of fun!