SQL 2012 Installation Guide
Getting the Wheels in motion for Beginners
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.
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.
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 :
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.
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.
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:
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 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. 
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.
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 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.
This concludes the installation.
Copyright to Ayman El-Ghazali www.thesqlpro.com