What's New in SQL Server 2012

The Enterprise Edition continues to be the major version, delivering all services and all features. If your production systems require top-end mission critical features such as asynchronous mirroring or automatic page repair, then it is unlikely you will consider any other version. There are a couple of major new features in SQL Server 2012 which we are really excitd about.

First is the addition of AlwaysOn Availability Groups. If you are currently using database mirroring you will want to explore availability groups. An availability group allows you to group related databases together, so they failover as a group. Unlike mirroring, you do not have to choose between either synchronous or asynchronous replicas, you can have a mix of both. See Chapter 8, AlwaysOn, for more details.

Second up is the introduction of columnstore indexes. Are you developing a data warehouse? Then you will be interested in these new indexes for sure. A columnstore index lives up to its name, in that all data from one column, rather than one row, is stored on the same data page or pages. Star schema queries using columnstore indexes execute faster than normal indexes as most values are retrieved from a single page, rather than from rows spanning multiple pages.

The Enterprise Edition boasts advanced security features, such as database-level audit and Transparent Data Encryption. It also includes previous Enterprise Edition only features such as online index rebuilds, data compression, and table partitioning, none of which are available in Standard or BI Editions.

Standard Edition supports up to 16 cores and along with OLTP capabilities, it offers some basic BI features. It doesn't have all of the new features introduced in Enterprise Edition, so it would be wise to check the feature comparison chart on Microsoft's website before making your purchasing decision at http://www.microsoft.com/sqlserver.

There is less emphasis on BI and advanced security in Standard Edition but, with a lower price tag, it may be appropriate for some of your less-critical operations. You will not benefit from AlwaysOn, or from Power View or PowerPivot, or the data management services Data Quality Services (DQS) and Master Data Services (MDS), which are all more data warehouse and BI-specific, but it will very reliably run yourOLTP databases.

If you do not want the whole feature set that Enterprise Edition offers, but need to do some business intelligence gathering and processing, then this new edition may do the job. The database engine supports up to 16 cores, while SSRS and SSAS can use the maximum thatyour OS supports.

We were both surprised by the omission of columnstore indexes in this version as these new indexes will bring performance gains to any data warehouse. However, if you decide on Business Intelligence Edition, you will benefit from most of the other BI features available in Enterprise Edition including Power View, PowerPivot for SharePoint Server, Data Quality Services (DQS) and Master Data Services (MDS).

We will take a quick look at the new features in MDS in Chapter 2, SQL Server Administration, and an in-depth look at DQS in Chapter 7, Data Quality Services. Power View and PowerPivot are beyond the scope of this book.

If you are running cloud-based SQL Servers then you will be pleased to hear that Microsoft has included cloud and VM servers in their 2012 licensing model. This should give you enough flexibility to pay for only what you need and a number of options are included. Please see the Microsoft licensing datasheet at http://www.microsoft.com/sqlserver/en/us/get-sql-server/licensing.aspx for more information.

This is the version you will install in your development and test environments. Identical to Enterprise Edition, it has everything you need to replicate your production environment. The only limitation is that it is not used as a production server. Check out the prices on Amazon; at the time of writing, SQL Server 2012 Developer Edition retails at around $60.

This free and lightweight edition, though not heavy-duty enough to be installed in your server room, is useful for small or mobile applications and worthy of a mention. There are add-on tools to manage the Express Edition, which are perfect for developers needing the bare mnimum of requirements.

Consider your needs carefully and check the full list of features on the Microsoft comparison matrix before you make your purchase, at http://www.microsoft.com/sqlserver.

If you do not think you need the extra features in a higher edition, go with what you need, then upgrade when you have the requirement. Visit the licensing model at http://www.microsoft.com/sqlserver/en/us/get-sql-server/licensing.aspx for the latest information.

Next we will look at how you acquire a copy of SQL Server, as it is not quite as straightforward you might expect. We hope you will evaluate it before you purchase, so first we will look at how to download the Evaluation Edition.

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.

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.

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.

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.

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.

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.

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.