Installing and Configuring SQL Server 2012
The plan is to install and configure SQL Server 2012. This blog post is a part 2 to my installing SharePoint 2013 so let’s go..
Just to recap, I’m installing SQL server 2013 Standard on Windows Server 2012 OS. I’m creating my server using Windows Hyper V service on a guest OS as Windows Server 2008 R2 with 3GB and 80GB on a 64 bit processor as hardware specifics. Things to note include:
- Do not try to install SQL Server 2012 on a compressed, encrypted or read-only drive, because setup will block the installation.
- Do not install SQL Server on a Domain Controller.
- Verify Windows Management Instrumentation service (Control Panel -> Administrative Tools -> Services) is running.
- The user account that is going to be configured to run SQL Server Setup must have administrative privileges on the computer.
- Enable .NET Framework 3.5 (http://www.sqlcoffee.com/Troubleshooting101.htm).
- Make sure your server has internet access to download all the latest updates. I had a problem with internet connection and connection to my domain. Enabling DHCP (required for internet access) ‘detaches’ the server from the domain as it changes the internet settings for that server (IP v4 settings for the network connection adapter) to automatically assign IP addresses and DNS (Dynamic settings). To add the server to the domain you simply change the IP, Subnet Mask, DNS server address in the adapter settings to static addresses, which means they don’t change. I haven’t resolved this yet but research points to creating DNS forwarders to resolve, but I don’t want internet access on my servers once it is set up so this doesn’t bother me for now. I will resolve it one day when I get around to it and will blog about it.
- Identify drives to which the databases and/or logs will be backed up, ensuring that there is enough disk space to accommodate the backups for the retention period that you choose.
- Create dedicated service account for each of the service to be installed. Ensure that these accounts are not member of Local Administrator because it will give unnecessary rights to these accounts. Note: for the purpose of this tutorial I’m using local system accounts as a service start-up accounts. Assign Deny logon locally right to these service accounts.
- Choose the port number for you SQL Server installation because common TCP\IP ports 1433/1434 ports are well known and are common target for hackers. Therefore it is recommended to change default ports associated with the SQL Server installation.
Ok so we are ready, let’s do this!
Run the setup from the installation media. I used Hyper V, so I attached the installation image to the DVD Drive so it appears as the dvd drive on the server.
Under Installation, choose a New SQL Server stand-alone installation.
You will be presented with a Setup is now preparing to launch Setup Support Rules window:
Now Setup Support Rules will run to identify problems that may occur during the Setup Support Files installation. If I had ended up with any “Failed” items I would not have been able to proceed without resolving them. If you don’t have any errors continue on with the next step in the installation process by clicking on the “OK” button, otherwise click the failed links to resolve the issues.
Next choose between an evaluation version or enter your product key to install your required version:
In the License Terms window, tick the box to accept the license terms and then click Next to continue and install the set up files:
Click Next to install set up files:
By default the SQL Server 2012 installation will look for product updates using the client Windows Update service. The “Checking for Updates…” progress bar shows when it is searching for updates. If you don’t want to search for updates uncheck the “Include SQL Server product updates” checkbox and then click on “Next >” to proceed with the installation process, which installs the updates.
Access to the Internet is required, since SQL Server setup will try to get updates for SQL Server during installation. If the computer does not have access to the Internet and Windows Update service, then you may get the above error message:
“SQL Server Setup could not search for updates through the Windows Update service. You can either check again or click Next to continue. To troubleshoot issues with your Windows Update service, view the link below ensure you have Internet or network access, and ensure your Windows Update service can find updates interactively through the Windows Update control panel
Error 0x80240042: Exception from HRESULT: 0x80240042.”
These files are necessary to perform the actual installation. Following the installation of the setup support files, you will be presented with another compatibility check. Following dialog appears once you successfully pass these checks.
The warning message above refers to a firewall warning. You can go ahead with the installation of SQL Server, but when it finishes configure your Windows Firewall to allow SQL Server access or disable it. See here to set that up: http://msdn.microsoft.com/en-us/library/cc646023.aspx
Now we need to assign this server a role. Select SQL Server Feature Installation option and then Next to continue to Feature Selection page.
I selected all features to install the Database Engine Services, Analysis Services, Reporting Services, and a number of shared features including SQL Server Books Online. You can also specify the shared feature directory where share features components will be installed. I left all other defaults in there. Click Next to continue to the Installation Rules page, Setup verifies the system state of your computer before Setup continues:
Click Next to continue to the Instance Configuration page. Each server machine can host one default instance of SQL Server, which resolves to the server name, and multiple named instances, which resolves to the pattern ServerName\InstanceName. Then you need to specify if you are going for a named instance or the default.
On this occasion I used a named instance, but the default is fine too. If there are already an instances on the machine, they will be named here too. Then click Next to proceed to the Disk Requirements Page:
Click Next to go to Server Configuration page:
Here you specify service startup and authentication credentials. Microsoft recommends that each service account have separate user accounts as a security best practice as shown in the following figure:
The SQL Server 2012 Books Online makes the following security recommendations:
Run separate SQL Server services under separate Windows accounts.
Run SQL Server services with the lowest possible privileges.
Associate SQL Server services with Windows accounts.
Require Windows Authentication for connections to the SQL Server.
I specified my SQL Service Account to run the SQL Server Agent and the SQL Database Engine and kept all the default collation settings. I would suggest that if you plan to install a number of SQL Server instances you consider installing all of them with the same collation. This will minimize the collation issues you will have when comparing data across databases and instances. Clicking next takes you to the Database Engine Configuration:
Note that there are three different tabs on this window: Server Configuration, Data Directory, and FILESTREAM. On the first tab, “Server Configuration”, I need to identify the “Authentication Mode” that SQL Server will use for my installation. There are two choices for authentication, “Windows” and “Mix Mode”. If I select Windows only, then all logins that need to connect with my SQL Server instance will need to be Windows accounts. If I select “Mixed Mode” then I can have both Windows and SQL Server authenticated logins. I typically select “Mixed Mode” so I can have the flexibility of using both account types, but I typically require all applications to connect using Windows Authentication if possible. I do this because I have found a number of third party applications that still do not support Windows authentication. If I specify “Mixed Mode” I am also required to set a SA password. Additionally on this tab I can add the Windows logins that I want to be placed in the sysadmin role by using the “Add Current User” or “Add…” buttons at the bottom of this window.
I also added the current Admin and Set Up admin accounts as administrators.
Use the “Data Directories” tab on the “Database Engine Configuration” page to change the default location for data directories. On this window I can specify the location for each of the different database components. With SQL Server 2012, Microsoft has given me more options on where to place the different data components. I now have options for where I want the user database, temp databases and backup components to reside. This allows me to separate my components across different disk drives to help optimize the disk layout of my SQL Server components across my I/O subsystem. I left the defaults in there.
By default FILESTREAM is disabled. FILESTREAM feature is quiet useful when binaries or other data that does not fit neatly in a table structure. To enable it all I would need to do is check the checkbox labelled “Enable FILESTREAM for Transact-SQL access”. If I check this box the “Enable FILESTREAM for file I/O access” checkbox becomes available. If I decided to check this box Windows will be able to access the files store in the windows share. I can use the “Windows share name” to identify the Windows file share name that will be used access the file stream data. If I also want to allow remote clients to be able to access the FILESTREAM datasets then I would also want to check the checkbox labelled “Allow remote clients access to FILESTEAM data”. For my installation I will not be enabling FILESTREAM, so I will not be checking any of these boxes. If I find I need FILESTREAM later I can always enable it. If you plan to use RBS you need to enable FILESTREAM for Transact-SQL access and FILESTREAM for file I/O.
Once I had configured all three tabs on the “Database Engine Configuration” window I can then click on the “Next >” button, which will bring up the following window:
Select “Install Analysis Services in Multidimensional and Data Mining Mode” to install Analysis Services with support for online analytical processing databases and data mining models. Or select “Tabular Mode” to install Analysis Services with support for tabular models. Each mode is exclusive of the other. Pick an administrator. I created an AD account for Analysis services to run and used this account as the Admin. Analysis services I required for Business Intelligence features to work in SharePoint 2013.
In the Data directories Tab, I left the defaults as is. Clicking next brings up the Reporting Services Configuration window:
I chose the above settings, for more info on there see here: http://msdn.microsoft.com/en-us/library/ms144292.aspx
If you selected to install the Distributed Replay Controller feature, then the above window presents you with options to configure it. I created an AD account to administer this and inserted it here. Distributed Replay Client Configuration page appears next:
Here I entered the Domain Controller name, the machine I was working on, and left the defaults for directories.
On 32-bit computers, the default installation path is C:\Program Files\Microsoft SQL Server\
On 64-bit computers, the default installation path is C:\Program Files (x86)\Microsoft SQL Server\
Click Next to advance to Error Reporting page, tick the check box if you want to send Windows and SQL Server error reports to Microsoft:
Click Next, then the System Configuration Checker will run some more rules that will validate your computer configuration with the SQL Server features you have specified. This window will verify that the installation has no failures. If I did have failures I would need to resolve them prior to proceeding.
Click Next, and on the “Ready to Install” window I can review all of my installation settings. This page shows a tree view of installation options that were specified during Setup. On this page, Setup indicates whether the Product Update feature is enabled or disabled and the final update version. I can use the slider bar on the right to move up and down so I can review all the options. If I need to change any options I can use the “< Back” button to go back and change installation settings. After I review all my installation settings are correct, I click the “Install” button to install SQL Server 2012. The Setup will first install the required prerequisites for the selected features followed by the feature installation. The installation process pops up the following “Installation Progress:” window:
If installation succeeds the next window will display:
Then you’re done. I would then fire up Configuration Manager and start all my required services. Also enable all 3 protocols for SQL Server Network Configuration, also in configuration manager (Restart of services required). Don’t forget to enable SQL Server Browser service if you installed a named instance of SQL Server.
Log into SQL Server Manager and add and assign your SharePoint Set up account (Spadmin) permission roles in the database: DBCreator, SecurityAdmin, and SysAdmin
If you have any problems during installation or error messages you can’t figure out, I used this site to help decipher them:
Now you should be ready to install SharePoint 2013 in the next part…