When Microsoft releases a major new version of a software product that is as big as SQL Server, it is an exciting time. They are on a schedule of one major release every four years and every release is increasingly more remarkable than the last.
Between us, the authors, we have covered a variety of development and administration roles spanning several decades. Over this time we have taken much delight in watching SQL Server grow stronger, gain more market share, take on new features, and even fix some critical issues too. We started working with SQL Server 6.5 way back in the 1990s and it has been a fascinating journey, arriving here in 2012 with new features that we could hardly have envisaged all those years ago.
Whether you are a database administrator or developer, business intelligence specialist, or .NET developer using SQL Server at the backend of your applications, there are lots of new features in this release to help you in your day-to-day work. There is a lot to discover in SQL Server 2012 and we have brought to you what we feel are the best and most useful of all the new features. Throughout this book there are hints and tips gained over our many years of experience, included to help you get the most out of SQL Server.
In this chapter, we will look at the new editions of SQL Server 2012. In addition to the usual suspects, we now have Cloud and Business Intelligence editions. We will also look at obtaining SQL Server and pre-installation advice as well as what a typical installation looks like. So without further ado, let's jump in.
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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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
erver, as well as
In this chapter, we looked at the different editions of SQL Server available to us, from the Evaluation and Developer Editions, right up to the big iron Enterprise Edition.
We saw how you can download Evaluation Edition from the Microsoft website, and have the option to use the Developer Edition unless you are deploying to a production system, saving added expenditure on licensing costs.
In the next chapter, things become even more exciting as we look at the new administration features in SQL Server 2012.