SQL Server 2012 has been out for a while. Since its release Microsoft has already released a number of Community Updates (CUs). In order to help you evaluate how you are going to install SQL Server 2102, I will be showing how to install a stand-alone instance of the Database Engine. You should think of the SQL Server 2012 installation as a process and not a single activity. Therefore any installation you take on needs to happen in a number of steps. For the purpose of this article I’m going to break my installation up into two steps, a Planning Step and an Installation Step
Planning Step:
If you are going to install SQL Server you want to make sure you also consider installing the latest patch set. When you are finally ready to install SQL Server 2012, you need to visit the Microsoft site and find out what is the latest CU or Service Pack (SP) that is available. The value to having your new SQL Server 2012 instances using the most recent up to date set of patches eliminates the worry about running into the bugs that others have already found. Microsoft has included a new feature called “Product Update” that by default scans for updates using the Windows Update service on the client or through a WSUS service. You can also install updates via a network share or folder by running the installation from the command prompt. To learn more about how to use the “Product Update” feature from the command prompt read my last article.
Another thing you need to consider is what accounts you are going to use for the services that are set up when you install the SQL Server database engine. There are 2 service accounts that you will need to determine what kind of account you would like them to run under, they are: SQL Server Agent, and SQL Server Database Engine. By default these services are setup to run under the account names of “NT Service\SQLSERVERAGENT” and “NT Service\MSSQLSERVER”. Best practices suggest that you establish two different Windows Accounts for each of these services. Therefore before starting your SQL Server installation you should consider setting up a couple of Windows accounts to run the SQL Agent and Database engine services.
Lastly you need to consider where you want to place the SQL Server binaries and data files. With SQL Server 2012, you now have more options during the installation process of where to place the different SQL Server data components. As part of the installation process you will need to identify the following different data file locations:
- User database directory
- User database log directory
- Temp DB directory
- Temp DB log directory
- Backup directory
Prior to installing SQL Server you should consider how best to spread out your SQL Server database data components to optimize your database setup for your specific disk hardware configuration.
Installation Step:
Once you have appropriately planned for how to patch, what accounts you need, and the disk layout for your SQL Server 2012 environment you are ready to install SQL Server 2012. You can install SQL Server using the wizard or it can be scripted. In this article I will cover using the installation wizard to install only the database engine on a VM machine that is not connected to the Internet.
To do the installation I first logon to my VM machine with an account that has administrator access. I then make sure the SQL Server 2012 media is mounted. I then browse to the SQL Server 2012 installation folder and execute the “Setup.exe” application. Upon doing this the following window is be displayed:
SQL Server Installation Center
The first time you install SQL Server 2012, it might be worthwhile to review all of the different options. To install a stand-alone SQL Server database engine I click on the “Installation” item in the left pane of the screen above. Upon doing that the following window is displayed:
SQL Server Installation Center
To install a stand-alone instance of SQL Server I click on the “New SQL Server stand-alone installation or add features to an existing installation” item in the right pane of the above window. When I do that the following window is displayed:
Setup Support Rules
As you can see my machine passed all the support rules, so I can proceed to the next step of the installation process. If I had ended up with any “Failed” items I would not have been able to proceed without resolving them. Since I don’t have any errors I can continue on with the next step in the installation process by clicking on the “OK” button. When I do that the following window is displayed:
Product Updates
By default SQL Server 2012 installation will look for product updates using the client Windows Update service. The “Checking for Updates…” progress bar shows that my machine is searching for updates. Updates can be obtained from the live Microsoft Update Service or through a managed WSUS service. Since my VM machine is not a machine managed by WSUS and does not have Internet access to use live Microsoft Update service it will not be able to search for updates, therefore I am going to uncheck the “Include SQL Server product updates” checkbox and then click on “Next >” to proceed with the installation process, which then displays the screen below. Alternatively I could have downloaded the latest updates from Microsoft and copied them to my VM machine and then modified the SQL Server DefaultSetup .ini file so the Product Update process would find the latest patches from where I downloaded them. If you have a disconnected machine, like my VM machine, you should review my prior article, using the link I provided in the planning section, to make sure your machine installs and patches SQL Server 2012 in a single step.
Install Setup Files
Once the Install setup files are installed the process will proceed and start checking the set up support rules. Once all the rules are checked I get the following window that displays status of each checked rule:
Setup Support Rules
As you can see I got two warnings. The first warning, “Microsoft .NET Application Security”, is because my machine doesn’t have Internet access. The second one, “Windows Firewall” warning is because the Windows Firewall is enabled. If you have failures or warnings you should review them before proceeding. To move to the next step in the installation process I click on the “Next >” button, which then displays the following Product Key window:
Product Key
On the “Product Key” window I will need to enter my product key for SQL Server 2012 and then click on the “Next >” button, which brings up the following “License Term” window:
License Terms
On this window I always review the license agreement. Once I have reviewed the license agreement I can then check the “I accept the license terms.” checkbox. When I check the box the “Next >” button will be made available where I can proceed to this installation window:
Setup Role
Since I am only going to install the Database Engine I need to select the first radio button and then click on the “Next >” button, which brings up the following feature window:
Feature Selection
On this window you can see all the different features you can select. But since I am only installing the database engine I will only be checking a couple of the Database Engine items and the Management Tools complete item, which I have done on the following window:
Feature Selection
As you can see I checked only the first two Database Engine items, and not the Data Quality Services item. Data Quality Services is a new feature that comes with SQL Server 2012. Data Quality Services is a tool that can be used to cleanse, match and transform incoming data. In future articles I will be showing you how to install and use Data Quality Services. Once I have selected the Database Engine components I want to install I can click on the “Next >” button to proceed. Upon doing that the following screen is displayed:
Installation Rules
On the “Installation Rules” window review the number of failed, warning and skipped items. As you can see I had no failed, warning or skipped items. To move to the next step of the installation process all I need to do is click on the “Next >” button, which displays the following window:
Instance Configuration
On this screen I need to identify the instance name for my SQL Server install. I have two choices. I can either install the default instance, or use a named instance. There can only be one default instance on a machine, but you can have many different named instances. Since this is my first instance on my machine I’m going to install the default instance. The “Instance ID” value is used in combination with the “Instance root directory” to identify the directory location where SQL Server binaries will be installed. By default the “Instance ID” will be the same as the instance name. Because I’m going to install the default instance and want my binaries to go on the C drive I don’t need to change anything on this window. Once I click on the “Next >” button the following window will be displayed:
Disk Space Requirements
This screen displays the amount of disk space that the SQL Server installation will require. You should review the amount of space that SQL Server will use. If you want to change the location you can always use the “< Back” button to go back one window to identify a different location for where SQL Server will be installed. To proceed with the installation I click on the “Next >” button and the following window is displayed:
Server Configuration – Service Accounts
On this window you have two tabs at the top, one labeled “Service Accounts” and the other labeled “Collation”. On the “Service Accounts” tab you can specify the account names that will be used for each service. Microsoft best practices suggest that each service be run under a different windows account. Therefore I’m going to setup “SQL Server Agent” and the “SQL Server Database Engine to run under two different local windows accounts. Also I want SQL Server Agent to start up automatically every time SQL Server starts up. Therefore I will be setting the “Startup Type” on that service to “Automatic”. Once I have configured my accounts I click on the “Collation” tab to set my collation. Below is what I see when the Collation tab is displayed:
Server Configuration – Collation Tab
By default the collation is “SQL_Latin1_General_CP1_CI_AS”. If you want to have a different collation just click on the “Customize…” button. When I click on the “Customize…” button the following window pops up:
Customize the SQL Server 2012 Database Engine Collation
On this window you can select one of the two radio buttons to specify the correct collation for your installation. 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. For my instance I will be using the default collation, so I just need to click on the “Next >” button to proceed with the installation. Upon doing this the following window is displayed:
Database Engine Configuration
On this window I will be configuring the Database Engine. 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. When I click on the “Data Directory” tab the following window will be displayed:
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. Since I’m installing SQL Server on a VM machine that only has a single drive I will be taking the default locations for all the different components. But in your environment, you might want to consider how to place each data component to optimize disk I/O across your disk subsystem.
The last tab to consider on this window is FILESTEAM. The FILESTREAM tab window is shown below:
Filestream Tab
By default FILESTREAM is disabled. To enable it all I would need to do is check the checkbox labeled “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 labeled “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. Once I have 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:
Error Reporting
On the “Error Reporting” window I have the option to send Microsoft information about my installation so they can improve the SQL Server installation process. If I want to send Microsoft information all I need to do is check the checkbox. For my installation I will not be sending Microsoft information. To continue on with my installation all I need to do is click on the “Next >” button, which brings up the following “Installation Configuration Rules” window:
Installation Configuration Rules
On this window I verify that my installation has no failures, which it doesn’t. If I did have failures I would need to resolve them prior to proceeding. Since I have none I will continue my installation by clicking on the “Next >” button, which brings up the following window:
Ready to Install
On the “Ready to Install” window I can review all of my installation settings. 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. This starts the installation process and pops up the following “Installation Progress:” window:
“Installation Progress:” window
When the installation process completes the following final installation window will be displayed:
Installation Complete
On this window I can review the status of each feature I installed. I verify that each one was installed successfully. When I click on the “Close” button I will be taken back to the “SQL Server Installation Configuration” window were I can proceed with other installation options, or close the installation process altogether.
SQL Server 2012 up and Running
Getting SQL Server 2012 up and running is a little different than in the past. Microsoft has made the installation process a little more robust by developing and incorporating the “Product Update” feature. Additionally the installation process now allows you more options to identify the locations on where to place the different physical database components. This allows a better way to distribute your database file I/O across your disk subsystem, than in past versions of SQL Server. If you haven’t already installed and started using SQL Server 2012 then you should at least consider installing it with its latest Cumulative Updates in one of your testing environments.
