Book Image

What's New in SQL Server 2012

Book Image

What's New in SQL Server 2012

Overview of this book

Microsoft SQL Server has been part of the enterprise database landscape since SQL Server 7 arrived in 1998 and has evolved into the relational and BI platform of choice by businesses around the globe. The performance and full feature set of SQL Server has been widely recognized by the business community and it is viewed as a powerful weapon in their database and business intelligence arsenal. SQL Server brings numerous benefits to all businesses, central to which is a thorough understanding of the technology, both current and new.This is the book both DBAs and developers always wanted to buy but could never find in the bookstore. This is a SQL Server book that contains only the new features introduced in SQL Server 2012.This book will give you a competitive advantage by helping you to quickly learn and understand the new features of SQL Server 2012. Most readers will already have an established knowledge of SQL Server and will want to update their 2008/2008R2 knowledge swiftly with least pain.This book takes you through all of the new features of SQL Server 2012, from installing core database services and features, to the new administration and updated Transact-SQL functions. You will discover the new Analysis Services features, introduce data alerts and reporting features and explore the new enhancements to Integration Services. In addition you will learn how to automate, cleanse and transform critical business data with DQS and world-class enterprise level availability features.Finally, you will venture into simulating real-world database loads using Distributed Replay and complete your journey with a look at the new SQL Server cloud services and the new Hadoop big data platform.
Table of Contents (19 chapters)
What's New in SQL Server 2012
Credits
About the Authors
Acknowledgment
Acknowledgment
About the Reviewers
www.PacktPub.com
Preface
Free Chapter
1
Installing SQL Server 2012
Index

SQL Server 2012 Editions


Microsoft has changed the available editions with the launch of SQL Server 2012. A new Business Intelligence Edition now joins Standard and Enterprise; however if you are looking for Datacenter, Workgroup, or Web Editions, you will not find them as Microsoft has dropped them. Developer and Express Editions are still very much alive.

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.

Note

Take a look at the requirements on the Microsoft site for the most up-to-date information about supported hardware: http://www.microsoft.com/sqlserver.

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.

Tip

If you are installing Virtual Machines (VMs) then the same rules apply. If your logical drives map onto a single image then all your I/O will go through the same drive. Be careful to split these off too.

What about solid state? If you are lucky enough to be using solid state isks (SSDs), then it is the same principle. Install your data files on your SSDs and your log files on the usual non solid state disks. If you are writing sequentially to a log file then there is less performance gain to be made by using an SSD, so save these for your tempdb and data file operations. However if you have multiple log files on the same drive, then advantages may be gained by using SSDs. You are advised to read about how best to provide resilience to your SSDs as this differs from vendor to vendor. Suffice to say it is best NOT to assume that traditional RAID1/RAID5 arrays are the way to go because of the very different operational characteristics of SSD devices. If you are interested in finding out more about SQL Server and SSDs, have a look at the performance tests on SQL Server MVP Tony Rogerson's blog at http://sqlblogcasts.com/blogs/tonyrogerson.

So let's take a look at the new line-up:

Enterprise Edition

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

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.

Business Intelligence Edition

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.

Licensing in the Cloud

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.

Developer Edition

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.

Tip

Whichever editions you purchase for your production environment, always go with the Developer Edition in your development and test environments. This will save you lots of money without compromising on features.

Express Edition

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.

Obtaining SQL Server 2012

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.

Evaluation

We always recommend that you try before you buy. Head to Microsoft's SQL Server 2012 web page at http://www.microsoft.com/sqlserver and you will find links to download the latest release. You require a Microsoft Live account to obtain your copy, but this is simply a matter of registering with an e-mail address. Once you have done this, you can download an ISO image for either the 32- or 64-bit system.

Retail

Depending on where in the world you live, it may be that Microsoft do not sell their software directly to you as a business. If you have an MSDN subscription then you will receive copies as soon as they are released. However, if you are lucky enough to be in charge of purchasing, then you could just buy your licenses from Amazon. We recommend that you search the web to find the best prices.

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.