Your purchase of SQL Server 2012 is very likely to be budget-driven. Microsoft has two licensing options, based either on computing power (core based), or if you are buying Standard or Business Intelligence editions, on the number of devices or uses (Client Access License or CAL). If you are buying new hardware to support SQL Server, then this may leave you with no option but to go for a cheaper version than Enterprise if the bulk of your budget has already been spent on storage and memory. Microsoft's Assessment and Planning Toolkit is a useful tool for license planning and utilization and can be found on the TechNet site at http://www.microsoft.com/sam/en/us/map.aspx.
After any budget limitations have been addressed, you will need to consider the workload that your SQL Server will undertake. Performance is important, so if you have many users hitting the server in parallel or a heavy amount of processing, then Standard Edition may not be sufficient.
Let us think about the underlying hardware. Disk speed and memory are where you want to focus your attention to achieve the best performance from your SQL Server. Always make sure you have enough disks and RAM.
Ideally you do not want to put all your disk I/O onto a single spindle, so splitting the load is always a good idea. You will want to put your database files (.mdf
) on separate drives to your log (.ldf
) files for a very good reason. In a typical Online Transactional Processing (OLTP) system, SQL Server will access your data files in a random manner as the disk is written to, and read from. For the most part the log file is written to sequentially, so the disk head moves more uniformly. Any interruption to this uniform disk head movement, such as random reads or writes to a data file, will incur latency delays.
Navigating the Installation Process
Over the years, Microsoft has altered the installation process as more features have been added to the database engine and it can be a confusing path to navigate. However, do not be disheartened if you are unsure; over time we have met many sysadmins who have been uncertain how to install and configure SQL Server correctly. Even as an experienced DBA, installing is something you may not do that frequently.
Tip
You are ready to install SQL Server 2012 with its new and exciting features. But wait a minute... before you upgrade your production environment, install it in development or test as a secondary instance and make sure any SQL Server feature changes are addressed before going live.
If you install SQL Server correctly from the beginning, this will help your long-term performance so we will focus on those areas that matter the most, in order to increase your performance.
If your SQL Server installation comes in ISO file format, you can install directly from it by using software to create a virtual CD/DVD drive which mounts the ISO file for you. This saves you burning a DVD. We like to use MagicDisk, as this is free and reliable. To download your free copy, point your browser at: http://www.magiciso.com/tutorials/miso-magicdisc-overview.htm?=mdisc_hlp106.
The setup wizard will alert you if there are additional requirements. You may need to install Windows Server 2008 SP2 or Windows Server 2008 R2 SP1 before installing SQL Server. If you are installing on your local Windows 7 machine, you will also need SP1. This takes about an hour to install.
Tip
Decide on the name of your Windows Server before you install SQL Server. If you want to change it, do this now before installing the database engine components. Changing the Windows name after installing SQL Server usually results in needless work and serious stress: linked servers, replication and mirroring may be affected and are more likely to break, as they will search for the original name of the server.
Once your Windows server is prepared, double-click on the setup.exe
file to begin installing SQL Server. On the Planning screen, you will see links to help documentation, including release notes and how to upgrade from a previous version. Read
and review as necessary, then click on the Installation link. Choose the option which best suits the installation you want. For a new installation, click on the top link: New SQL Server stand-alone installation...This will start the setup process:
Enter your license key number or choose to run it as an evaluation (or install the Express Edition). On the next screen click to accept the software license terms. When you arrive at the Setup Support Rules screen, you will need to address any reported issues before proceeding.
Once you are on the Setup Support Role screen, leave the first option of SQL Server Feature Installation checked, unless youwant to change it, and click on Next.
The Feature Selection screen is where it becomes interesting. If you later discover there is a component you have missed, you need not worry as you can install it later on. Right now, you will at least need to choose Database Engine Services. Depending on which edition you are installing, you will have different options.
We are using Enterprise Edition and want to install Analysis Services (SSAS), Reporting Services (SSRS), Integration Serices (SSIS), Data Quality Services (DQS), and Master Data Services (MDS), so that we can explore them later on in this book. You could equally choose Dveloper Edition to install these features.
Bsiness Intelligence Developer Studio (BIDS) has been replaced with SQL Server Data Tools, which is used to develop reports, create packages, and build Analysis Services objects such as cubes and dimensions.
You will probably not want to install this on your production system but in our case we have included it, again for the purpose of this book.
In order to manage SQL Server, check the Management Tools option (SSMS). Make your selections and click on Next.
Instance Configuration gives you two options: installing a default instance or a named instance. If this is the first SQL Server to be installed, it will be installed as a default instance automatically. You can choose to install it as a named instance if you wish; just click on the radio button to select this option and enter a new name.
We will choose the default and leave the files in our C:
directory.
Tip
Install SQL Server on the same drive as your Operating System (OS), usually the C: drive, and put your data and logs files each on their own drive. As an absolute minimum, mirror all your drives using RAID 1, including the OS and SQL Server executables drive. If you want to increase your performance further, give tempdb its own drive. Tempdb is used not just for user queries, but also by SQL Server's internal operations. SSDs can speed up tempdb operations significantly.
Click on Next and wait while the setup calculates whether you have enough disk space. This may take a minute. We are now good to go, so click on Next and ring up the Server Configuration screen. We are now talking about Windows Active Directory (AD) accounts, passwords, and security.
For many DBAs, the biggest stumbling point when installing SQL Server is not knowing which service accounts to use. If you wish, you can use a different account for each service you install, but it is a good idea to set up at least one account that is dedicated to SQL Server. By using a separate account for each service, you isolate any issues if a password or service is compromised. However, the downside to this is that server engineers have a potentially higher maintenance overhead.
Tip
Use a different account for your development and test servers to the one you use in production. This means you have no requirement to give the login to your developers, thereby restricting access to production systems.
The account that you use for SQL Server should have a strong password and preferably be limited to much less than local administrator rights (see the following tip). It is always a good idea to talk to your system administrator before proceeding, to make sure the account you use is set up correctly.
Tip
This is an important point to note... Never use an account which is a domain admin account; always use a domain user account. Give only the security rights that are needed. There is a lot of information on TechNet, including the article Setting Up Windows Service Accounts, which is written specifically for SQL Server: http://technet.microsoft.com/en-us/library/ms143504.aspx.
Click on the Account Name to bring up the Browse option. Click again to bring up a window to allow you to choose another account:
At this point, you may also want to change the startup options for SQL Server Agent and SQL Server Browser. If you intend to run any scheduled jobs, start SQL Agent. If you want to allow remote connections to your new database engine, SQL Browser may need to be running if you have any named instances installed. You can start or stop each service later on from the command line, using Services Management Console, SQL Server Configuration Manager, or from the
Cluster Administrator tool if you are running SQL Server in a failover clustered environment.
Set up your accounts and move on to the Database Engine Configuration screen:
At this stage, choose Windows authentication if you only connect using AD authentication. If you have third-party applications using a username and password then you need to select Mixed Mode, and enter a password for the system administrator (sa) account. Add yourself and any other DBAs on your team who must have sysadmin access.
Click on Next. We are installing Analysis Services, so the screen for SSAS options appears. Choose your server mode, either Multidimensional and Data Mining Mode (your analytical objects are stored in the OLAP engine and queried using MDX) or Tabular Mode (your analytical objects are stored in the xVelocity engine and queried using DAX). If you are unsure, choose the default and if you need to run both modes, you can later install a separate instance to run in the other mode.
Tip
See Chapter 4, Analysis Services, to discover which Analysis Services model is already installed and running on your server.
Whichever mix of services you have chosen to install, you will eventually land on the Error Reporting creen. If you would like to help Microsoft improve SQL Server, leave the option checked to send error reports.
After we have passed the test for
Installation Configuration Rules, we can now click on Next and move on to review what we are about to install. Once we are all set, we can finally click on the Instal button. This takes a while so it is a good time to grab a coffee.
Based on the selection of the services and features, our installation took about an hour to complete on a desktop PC, but this will probably be a bit quicker on your servers! Be aware that once the setup has completed, you will be prompted to restart the server.
Once you have restarted your server, go to Program Files and open up SQL Server 2012 Management Studio. If, like us, you have been using SSMS for a while, you will be familiar with its look and feel. It may be a slight shock to see that Microsoft has given the UI an overhaul and it is now very similar to the Visual Studio 2010 interface:
Note that Books Online (BOL) is no longer included with the installation media. You can download and install it separately or simply view it directly on the Microsoft website at http://msdn.microsoft.com/en-us/library/ms130214.aspx.
Now that you have successfully installed SQL Server, you need to know how to get the best out of it. We will be covering new features such as High Availability, columnstore indexes, and Distributed Replay later on in this book but right now it is important to think about security updates, patches, and service packs.
Tip
Using Twitter to Learn More
A great way to keep up with security releases and find out about how your SQL Server works is to follow a few key Twitter profiles. Microsoft's Customer Support Service Engineers for SQL Server is a wonderful source of both news and information. You can read their blog at http://blogs.msdn.com/b/psssql and follow them on Twitter @MicrosoftSQLCSS
. You can benefit from following Microsoft's main SQL Server Twitter account @SQLS
erver
, as well as @TechNet
.