SQL 2012 Installation Guide

Getting the Wheels in motion for Beginners

Table of Contents

Table of Contents

Introduction and Purpose of this Booklet

1. Introduction and Installation of SQL Server

Importance of a proper installation

Storage consideration

RAM Considerations

SQL Server Instances

The Actual Installation

References


Introduction and Purpose of this Booklet

Frankly put, this booklet is not for everyone.  The text to come is not for the SQL Server geniuses but rather for those looking to make an entrance to the field but find other technical books intimidating.  This booklet is for students that need practical experience using a powerful Database Management System (DBMS) such as SQL Server so that they don’t crash and burn on the first day of work. Others who are developers and would like to understand the Administration side of the software, this booklet is also written for you. If you are a CIO or an IT Manager of some sort that wants to understand how the SQL Server DBMS is managed and quickly, your call has been answered. For those that work on the infrastructure or operations side of IT and found themselves stuck in doing database management related tasks, this work is a gift to you whether you found yourself there by force or by hobby. These aren’t the only audiences limited to using this booklet, rather it is for anyone looking to start becoming a Database Administrator (DBA) that can get things done effectively.

Often I find myself remembering great conversations or training sessions that I have attended, and I realized that one of the best ways to get information across is to make the receiver comfortable with what they are receiving. For that reason, I have tried to make this booklet as easy to read as possible as not to bore the reader or overwhelm them with information that they will not remember. There are practical steps with screenshots and code snippets to allow for the reader to practice what they have learned. There is an expectation that the reader has basic computer hardware/software understanding and the basic understanding of relational databases concepts.

I sincerely hope that you like the “conversation” we are about to have about SQL Server and that you learn from it in order to start your new career or enhance your current one. Good luck to you all and I hope that you will teach others what you have learn because we all depend on one another in order to learn and we should strive to spread good knowledge.

Note: This guide is not intended for SQL Server installations that are to become part of a failover cluster service.

Please visit www.thesqlpro.com to learn more about SQL Server.


1. Introduction and Installation of SQL Server

What is SQL Server?

In a nutshell, it is Microsoft’s version of a Relational Database Management System (RDBMS) and Business Intelligence platform. The original inception for SQL Server started in 1989 and currently it has reached Version 11; better known as SQL “Denali” or SQL 2012.[1]

SQL Server is a relational database server that complies with the ACID properties that guarantees transactional database reliability. The following explains briefly the meaning of the ACID properties [2]:

The Business Intelligence (BI) platform consists of three main parts: Integration, Analysis, and Reporting services.  Integration Services acts as the platform’s ETL (Extract, Transform, Load) portion; it primarily used to load data from various relational and non-relational sources into SQL Server.  Integration Services also provides a method of exporting data from SQL Server into various relational and non-relational destinations. Analysis and Reporting services are built to provide insight to the users of the data.  Data at rest is not as valuable as data that has been given meaning which can be used to improve business decisions and strategy.  Analysis services expands on the OLTP (Online Transaction Processing) nature of relational databases into what is known as OLAP (Online Analytical Processing).  OLAP allows for multidimensional querying and reporting of data. Reporting Services provides the presentation layer for all the data that is collected and analyzed.  These three components have come a long way since their inception and have established a strong foothold for the SQL Server suite in the realm of enterprise level BI.

Requirements for SQL Server Installation (Software/Hardware)

It would be wasteful to give a detailed list of hardware and software prerequisites for installation since they exist officially on Microsoft’s site (see URL below - Reference 3). In general, SQL Server 2012 is only available to be installed on the Windows Operating System and is no longer supported on Windows 2003.

http://msdn.microsoft.com/en-us/library/ms143506.aspx

Importance of a proper installation

When someone builds a new house or office complex, the first and most important structure built is the foundation.  Similarly, the installation process for SQL Server acts as the foundation for the product and thus it is essential to get it right the first time. One may wonder “Is it not just a regular installation of a software package like any other installation?” This may be somewhat true, but there are certain configuration options that need to be installed properly during the initial setup otherwise they are very difficult to change in the future.

Please do not take for granted the importance of a proper initial installation and configuration of SQL Server. Let that be the most important lesson gained from this booklet.

Storage consideration

As with any enterprise level software, getting the right hardware is an important decision. I would strongly recommend that time be spent studying RAID (Redundant Array of Individual Disks) and Storage Area Network (SAN) storage technologies. A general understanding of the different types of storage media types such as Solid State Disks (SSD) or Hard Disk Drives (HDD - Magnetic Disk ) would be helpful to a good DBA. Finally, knowledge of the interfaces associated with storage access and data transfer is extremely useful. These interfaces include the modern SATA, SCSI, SAS and Fibre channel connections for internal drives.

The one point I would like to discuss in this section is the type of RAID array that Microsoft recommends for SQL Server database and log files. In general, transaction log (T-Log) files need to be on the fastest type of RAID array that provides fault tolerance. For that reason, Microsoft recommends using RAID 1+0. RAID 1+0 is very expensive however due to the fact that disks are both striped and mirrored. Simply put, four must have a minimum of four disks for a RAID 1+0 and every time you wish to expand the array you must add at least two more disks.  If the requirement is to have 1 Terabyte of space for log files, then you will need to purchase 2 Terrabytes worth of space for a RAID 1+0 in the form of four 500GB drives.[4,5]

RAID 5 is a cheaper, but less effective, setup that can work well with T-Log files. RAID 5 provides fast read/write speeds with fault tolerance. Keep in mind however that read throughput is severely affected when one of the disks in this RAID formation fails. [4,5]

With regards to Data files, generally they can be placed on RAIDs that are slower. For data that is rarely written to, RAID 1 is a good option because it has very high throughput for read operations and provides fault tolerance. This seems a viable option for archived or read-only data files. RAID 5 is another good option for data files since it can be cheaper, depending on configuration, and provides both fault tolerance and the best throughput for read/write operations after RAID 1+0. [4,6]

In general, with regards to where files should go, you should have the following setup as a minimum:

  1. Log Files (.LDF Files) on one Drive or Storage Array
  2. Data Files (.MDF and .NDF files) on another Drive or Storage Array
  3. Backup Files (.BAK files or .TRN files) on another Drive or Storage Array
  4. TempDB Files on another Drive or Storage Array

Optionally, you can also separate read-only filegroups on to separate drives or even have partitioned tables spread across separate drives.  The configurations are endless. Also with regards to disk allocation unit size, Microsoft recommends using 64KB clusters when formatting disks for SQL Server.  [4,10]

Some more detailed information about RAID arrays can be found at (Reference 6): http://msdn.microsoft.com/en-us/library/ms190764.aspx

RAM Considerations

RAM is one of the most important components of SQL Server. A database file is a collection of data pages that are 8KB in size. SQL Server organizes them into logical units called Extents which are a set of eight pages; it can be deduced that Extends are 64KB in size. This is the reason why storage should be formatted to 64KB so that they are aligned better with these data pages that are organized in Extents. [10]

Those data pages that make up our data files sometimes “live” in RAM. The basic way that this works is that when a query is run, SQL Server pulls the data from disk to RAM and stores it in its buffer pool to be used to satisfy query requests. SQL Server using something called the Least Recently Used (LRU) Algorithm that determines which pages can be dropped from memory if more space is needed to satisfy other query demands. A process called Lazy Writer takes care of cleaning out those pages from RAM.

Now that you understand the basic usage of RAM for SQL Server, think about the fact that most of what you do in SQL Server is read/write data which uses data pages.  The more RAM you have, in general, the more data pages you can store in memory and the less disk IO is needed to pull data from your server’s disk storage. Therefore we can deduce that RAM can significantly improve your SQL Server performance since the bigger and faster RAM your server has, the more data it can process and at faster speeds; physical disk speed is much slower than RAM. So for your server you may opt for buying more RAM than purchasing faster disks such as Solid State Drives due to the cost factor; timeframe is a factor of course since disk technology is always getting better, faster, and cheaper.

Once SQL Server is installed it will use all memory available to it on the Operating System. Allowing SQL Server to use all the system’s RAM is not recommended by many SQL Server professionals.  There are many schools of thought in this and my intention is not to start a debate about which practice is the best.  There are many recommendations I have come across in my career, but the one that is always consistent is that you should always set a maximum for the amount of RAM used so that the Operating System has it’s own memory pool to operate in. This setting is configured after the installation and is beyond the scope of this booklet, but definitely something very important to understand.

SQL Server Instances

An instance in SQL Server is basically a collection of services that are running under a virtual name. There are two types of instances that can be installed, the Default Instance and Named Instance(s).  You can only install one Default Instance of SQL Server which uses the name of the server to identify itself.  A Named Instance can be given an alternate name for which it is called by and you can have multiple Named Instances; you don’t even need to have a Default Instance installed to install a Named Instance.

Let me give an example to clarify both options and so that you can understand what an instance is composed of. Let us start with the Default Instance on a server named SQLDatabase1.  If you were to install only the Database Engine on that server, then your clients and applications would connect to that server using the name of the sever.  Your instance would only have the Database Engine, SQL Server Agent, and SQL Browser services available to be used.

Additionally, if you wanted to install another Instance of SQL Server on that same server you could install a Named Instance. Let us call that named instance SQLNamed1. Your clients and applications would use the name SQLDatabase1\SQLNamed1 to connect to that collection of services that is installed under that instance.  If you installed the Database Engine, then that Instance will have its own set of System and User Databases and its own security.  That Instance is a separate “environment” than the Default instance. This is a good way to separate work loads and even access.  Each Instance can have its own resources allocated to it (RAM, Disk, etc) and have its own jobs as well since SQL Server Agent is installed per Instance.

Finally, you can actually install different types of services on each instance.  For example, you can setup an Instance to run the Database Engine, another one to run Analysis Services, and a third one to run Reporting Services.  If you were limited to using one Development Server and you had multiple Production Database Servers, you could install multiple SQL Server Instances each with its own Database Engineer service to mimic each of the Production Database Servers. Each Instance has its own set of services that can be managed under the Windows OS or using SQL Server Configuration Manager.  They can be setup to use different startup security or startup types (Automatic vs. Manual). The possibilities are limitless but of course there is a maintenance overhead since each Instance has its own set of securables.

I would recommend researching the use of SQL Server Browser with Named Instances as a follow up to this section if you chose to use Named Instances.

The Actual Installation

The installational media for the SQL Server Enterprise Edition 180-day evaluation license can be downloaded through Microsoft’s SQL Server site at: http://www.microsoft.com/sqlserver

Microsoft also provides a developer edition at a low cost for those who want to use and learn SQL Server beyond the 180-day limit. A free, limited version known as SQL Express can be downloaded at: www.microsoft.com/express/sql/

The following installation guide is based on the RTM SQL Server ISO image downloaded directly from Microsoft in April, 2012. There may be slight changes to some of the installation screens based on when you download the product.

[Figure 1-1]

  1. Initial Setup Screen [Figure 1-1]. Click on “New SQL Server stand-alone installation...” to start the setup program.

[Figure 1-2]

  1. Setup Support Rules [Figure 1-2]. On this screen a set of rules are run to determine if SQL Server is prepared to continue with installation.  Press OK to continue.

[Figure 1-3]

  1. A new screen should pop-up with the first dialog being for the Product Key [Figure 1-3]. You may chose the Evaluation or Express Edition. You may also choose to enter a product key if you have one.
  2. License Terms is the next screen. Click the check box to accept the license agreement and then Press Next
  3. The Product Updates screen may or may not appear; this is most likely a feature that will be available when updates are released for SQL Server.
  4. Install Setup Files is the last section in this dialog. Accept the selection and Press Next to proceed.

[Figure 1-4]

  1. A new screen should now appear with Setup Support Rules [Figure 1-4]. Press the next button to continue if you can.  If there are failed statuses, you may need to make a few fixes before proceeding with the installation.  Clicking on the blue status messages will provide more information.
  2. Setup Role is the next screen. Select SQL Server Feature Installation if it is not already selected and click next. We will not be installing SQL Server PowerPivot for SharePoint.
  3. At the Feature Selection screen please install the features that you wish to use. Keep in mind that for security and performance considerations you should not install services that you do not plan to utilize.
  4. Choose your installation path at the bottom of the screen and click Next when satisfied.
  5. For the next section, Installation Rules, click Next to continue.

[Figure 1-5]

  1. Instance Configuration [Figure 1-5]. Instance ID for default should be MSSQLServer. If there is another default instance installed, then you will have create a named instance instead. Select Named Instance and pick an appropriate name.  If you have other instances installed on the computer then they will show up in the window at the bottom of [Figure 1-5]. Select Instance Root Directory and then click next when done.
  2. Review Disk Space Requirements and then click Next.

[Figure 1-6]

  1. Server Configuration. This screen allows for the setup and configuration of security related to SQL Server and its various services. Microsoft recommends using a separate Windows domain account for each service that is running. This reduces security risks. To change the configuration of the account used, click on the “Account Name” cell for the corresponding services you would like to change. You may wish to leave the defaults, as shown above, if you are running this installation on a test machine that is not part of a domain. Also, you may consider using Managed Service accounts if your organization supports them. The selection on the right of the screen labeled “Startup Type” allows for configuring when services start up. All of these can be changed after installation if you change your mind using SQL Server Configuration Manager. Please click next to proceed when you have completed your configurations.  [7,8,9]
  2. The Collation Tab is used to change the rules that SQL Server uses to determine the proper use and sorting of characters in a language. This selection is made for the entire instance.  Databases can have different collations than the instance. Leave the default value and click next to proceed.

[Figure 1-7]

  1. Database Engine Configuration.
  1. Server configuration is the place to configure initial SQL Server instance level security [Figure 1-7]. You can choose between using Windows only or Mixed Mode Authentication.  Mixed Mode allows for SQL Server logins to be created. If you choose Mixed Mode you must enter a password for the System Administrator (sa) account. You may also wish to add the current user to the SQL Server Administrators or a group of users.

[Figure 1-8]

  1. The Data Directories tab allows for configuration of default directories for Database files and logs as well as backup files.  This does not mean that databases, log files, and backups cannot be placed elsewhere, rather this is the default path that will be used unless manually specified otherwise. As you can see from this example, Database files and log files are on separate drives to improve performance. The same applies with the TempDB files.

[Figure 1-9]

  1. Filestream is a feature that allows for Binary Large Objects (BLOBS) to be stored  on the filesystem but can be query and manipulate the object through SQL Server. In general, when there is a need to store unstructured data files larger than 1mb on the filesystem but need to access them within SQL Server.  These files include video, images, audio and more. Do not configure this feature since we will not be covering it in this booklet. If you are interested in Filestream and would like more information you can visit this webpage: http://technet.microsoft.com/en-us/library/bb933993.aspx
  1. Reporting Services Configuration. Take default which is to Install and configure and then click next.
  2. Error Reporting, select the option which suits your needs best. Click Next.
  3. The Installation Configuration Rules screen will come up to verify that everything is ready to go. Click Next to proceed.
  4. Ready to Install is the last screen of this installation. It will show a summary of all options and configurations that were selected on the previous screens.  Click Install to continue and then wait until finished; this may take some time depending on your system.

This concludes the installation.


References

  1. Microsoft SQL Server WIKI- http://en.wikipedia.org/wiki/Microsoft_SQL_Server
  1. ACID Model WIKI - http://en.wikipedia.org/wiki/ACID
  1. Hardware and Software Requirements for Installing SQL 2012 - http://msdn.microsoft.com/en-us/library/ms143506.aspx
  1. Storage Top 10 Best Practices - http://technet.microsoft.com/en-us/library/cc966534.aspx
  1. SQL University: The Basics of Storage - http://www.brentozar.com/archive/2009/12/sql-university-the-basics-of-storage/
  1. RAID Levels and SQL Server - http://msdn.microsoft.com/en-us/library/ms190764.aspx
  1. Server Configuration - Service Accounts - http://msdn.microsoft.com/en-us/library/cc281953.aspx
  1. Security Considerations for a SQL Server Installation - http://msdn.microsoft.com/library/ms144228(SQL.110).aspx
  1. Managed Service Accounts - http://technet.microsoft.com/en-us/library/dd560633(v=ws.10).aspx
  1. Disk Partition Alignment Best Practices for SQL Server - http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx


Copyright to Ayman El-Ghazali www.thesqlpro.com