For some reason during my time as an application development consultant, I ended up working on a few projects where I was writing an InstallShield or Windows Installer package for the client. As a former developer working in various environments, I have made my own hypothesis that, in general, whoever gets stuck writing install scripts must feel like the little kid who got picked last for a kickball team in fifth grade. There is nothing glamorous about setup. The only time your code is being run is when the application is installed, updated, or removed. So if my hypothesis is correct and writing setup scripts is not the most exciting task for developers, then one can assume that, in general, the overall experience to the end user might not be optimal. After all, as a developer, why should you put much effort in something that is going to run only a few times in the life of the product?
The reason you should put a lot of effort as a developer into setup is that it's the first impression of your product to the customer! A flimsy install might leave the impression that the rest of the product is shoddy, which may or may not be true. Setup is also incredibly complex with multiple requirements, including providing support for multiple operating systems; multiple editions of SQL Server; and upgrading, repairing, and uninstalling. Getting all these right is a tough task.
Over the past few releases of SQL Server, setting up and installing product was an average experience at best. In general, the product would install most of the time, but there was massive room for improvement. SQL Server 2008 is part of the third generation of SQL Server products. This version contains the absolute best installation and configuration experience of any previous version of the product. The user interface is informative and intuitive, and on top of all that, there are wizards to help you perform advanced tasks such as setting up and removing cluster nodes.
In this chapter, I will discuss several aspects of planning and installing SQL Server:
SQL Server editions and resource requirements
Installation planning for SQL Server
Step-by-step install of the evaluation edition of SQL Server 2008
To get the most out of this chapter, it is best to walk through an installation of the evaluation edition yourself. This edition is a free download and functions for 180 days. You can find it at http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx.
There are two main editions of SQL Server: Enterprise and Standard. Beyond these, there are a variety of specialized editions: Developer, Workgroup, Web, Express, and Compact, as well as the evaluation edition. Before diving into the differences between each of these, it is important to first discuss 32- and 64-bit architectures because each edition may or may not support your preferred hardware configuration.
If you are relatively new to working with technology, you may have seen servers on the x86 or x64 or IA64 processor platform. If not, you will see that nomenclature once you read which platforms each SQL Server edition supports. Historically, x86 comes from the 86 part of the Intel 8086 chip. This chip design dates back the late 1970s and has been dramatically transformed over the years, increasing in performance and capabilities. Lately, the x86 in servers has been on 32-bit architecture. With 32-bit servers, one of the more significant limitations is the relatively small amount of addressable memory. Having large amounts of memory is good because it allows you to have more active connections and increases performance in memory-hungry applications like Analysis Services, SQL Server Integration Services, and the SQL Server database engine.
So, in a perfect world, the chip manufacturers would give you 64-bit to allow you to address greater amounts of memory. And they have provided 64-bit, just with some bumps in the road. Up until 64-bit, there were two main chip manufacturers supporting the Windows operating system: AMD and Intel. In terms of market share, hardware vendors appeared to prefer Intel's implementation of x86. When it was time for 64-bit, Intel veered off into a totally new direction away from its popular x86 architecture. This new chipset was called Itanium, or IA64. AMD also wanted to produce a 64-bit chip except it produced a chip that was just like the x86 except the instruction sets were 64-bit. In effect, AMD made a true 64-bit version of the popular x86 chip; it's referred to as x86-64. In the end, hardware vendors and users of these chips found it much easier and quicker to leverage AMD's implementation than Intel's Itanium. This was because with AMD the vendors did not have to rewrite major parts of their implementations in order to run on a 64-bit platform. With the minimal success of its Intanium brand of processors, Intel was forced to make an x86-64 chip as well. Together, both AMD and Intel's version of x86-64 are known to Microsoft as x64. Various editions of SQL Server support specific architectures, and chances are, if your company is buying new hardware, it's probably 64-bit. SQL Server supports the x86, x64, and IA64 platforms. However, each edition may support only a subset of these three; you'll find details on the server editions in the following section.
In previous versions of SQL Server, there was not much difference between the features in Standard edition and the ones in Enterprise edition. Enterprise edition traditionally has had features that focus on solving the needs of very large and highly available database systems. Features such as table partitioning, unlimited CPU usage, online indexing, and online restore are examples of enterprise features. If companies had unconstrained budgets, chances are they would probably just buy Enterprise edition and go on with life. In reality, most organizations are always under tight budget constraints, so companies were more likely to purchase Standard edition, which costs about one-fourth as much as Enterprise edition. With SQL Server 2008, the differences between Standard edition and Enterprise edition are quite substantial.
Most of the key features such as Resource Governor and data compression are in Enterprise edition only. SQL Server Books Online has a great article called "Features Supported by the Editions of SQL Server 2008" at http://technet.microsoft.com/en-us/library/cc645993.aspx. If you are asked to evaluate which edition to use, consider the capabilities that Enterprise edition offers, because it may be more economical to consolidate servers and applications and simply purchase a single Enterprise license.
Let's take a look at these editions in detail.
Some of the highlighted customers for SQL Server include NASDAQ (http://www.microsoft.com/casestudies/casestudy.aspx?casestudyid=49271) and the London Stock Exchange. You can imagine that their transactional loads demand a highly scalable and highly available database server. You can bet that Enterprise edition is installed for their purposes. Enterprise edition enables all the features available for all components in SQL Server. There are no stated restrictions on the number of CPUs, memory, or database size. Technically, the limitations for these (other than the database size) are imposed by the operating system. With Enterprise edition, consolidation is easy because you can also have up to 50 instances of SQL Server running on a single server.
Standard edition is designed for departmental applications. This edition contains some of the business intelligence capabilities of Enterprise edition as well as most of the management features. Specific key improvements like performance data collection and policy-based management are enabled with Standard edition. From a hardware standpoint, Standard edition is limited to four processors (unlimited cores for each).
Enterprise edition and Standard edition are the core offerings for SQL Server. If those editions were all you could buy, you probably wouldn't be too happy. There are situations where you need to have access to all the functionality of Enterprise edition but you can't afford it. A development environment is a perfect example of when you might need all the features available. For this reason and others, several specialized editions are available. Each of these editions has some restrictions that are mentioned in the following subsections.
Developer edition is really Enterprise edition with some extra development-oriented content and licensing restrictions on how this edition can be used. This edition is intended for users to build and test applications and is not allowed to be run as a production server. In the event you want to upgrade your Developer edition to Enterprise edition, this is also possible.
Workgroup edition is designed for the branch-office scenario. Since branch offices tend to replicate data from a main corporate office, Workgroup edition enables subscriber-only replication. Workgroup also provides the SQL Server Integration Services runtime engine but does not provide the designer. There are no business intelligence capabilities with Workgroup edition.
Web edition is designed for web-hosting companies. In terms of functionality, it is very similar to Workgroup edition.
Express edition is Microsoft's free version of SQL Server. Microsoft previously had a free version of a database called Microsoft Desktop Engine (MSDE). A major issue with MSDE was that there was no user interface, so it was very difficult to manage. SQL Server 2005 was the first release of Express edition. Functionally, it contains a surprising number of server features and capabilities. The one big take-away if you compare MSDE (which is based on SQL Server 2000 technology) with Express edition is that Express edition does not have the SQL Server Agent job scheduler.
There are a few more versions of Express. One is called Express with Advanced Services. The name is not really catchy, but it includes everything in Express plus a version of Reporting Services. The other is Express with Tools, which is the SQL Server database engine and SQL Server Management Studio Express version. You can also download just the SQL Server Management Studio Express version as a stand-alone install.
Compact edition is a free embedded version of SQL Server intended for occasionally connected applications for mobile devices, desktops, and web clients.
Before we get too much in depth on describing how to install and use SQL Server, it is good to introduce some key concepts about this relational database at this point in the book. In particular, you should understand the term instance as it is used with respect to SQL Server.
Prior to SQL Server 2000, you could have only one installation or instance of SQL Server installed on the server. You can think of an instance of SQL Server as a complete SQL Server installation including objects like system and user databases, logins, and SQL Server Agent jobs. Starting with SQL Server 2000, you can install multiple instances of SQL Server on the same server. These additional instances will simply listen for user connections on different ports. Since each instance is effectively a completely isolated database server, using multiple instances allows for easy application consolidation.
If you are planning on installing SQL Server on your desktop to just play with it, you probably don't create a plan, review it with other folks in your organization, and schedule a time to implement the plan. Chances are you load setup.exe and click the Next button a bunch of times until the wizard starts installing SQL Server.
Although installing SQL Server 2008 can be as simple as clicking a few buttons, it is a good idea to step back first and create an installation plan. This plan will help you answer questions that are critical to the success of the installation. Some of these questions are as follows: Do we meet the hardware requirements of SQL Server 2008? Are the systems engineering folks aware of our new installation of SQL Server? The last question is very important. In some organizations, there are measures in place to automatically block network ports if new applications appear to be running on them. This is an effort to reduce the risk and damages of viruses. Always get the necessary people involved when planning an installation of SQL Server. These people include not only your database administrators but other folks from IT such as the people who are responsible for the network and for the operating systems. The specific list depends on how your organization is structured.
Most organizations have a good idea of all the servers that they currently have. However, the larger the organization, the more likely that there are instances of SQL Server that exist that may or may not be accounted for or managed by the DBA staff. That situation may come about for a variety of reasons, even some legitimate ones. In the end, as a DBA, it is a good idea to keep an active inventory of which servers and SQL instances you manage. Not only is this a best practice, but it's a good way to prevent you and your company from getting into legal trouble. There are a variety of tools that can help you with SQL Server instance discovery. Microsoft ships a tool called the Microsoft Assessment and Planning (MAP) Toolkit. This toolkit is not designed for just SQL Server; rather, it helps users simplify the planning process for migrating to Windows Vista, Microsoft Office 2007, Windows Server 2008, Windows Server 2008 Hyper-V, Virtual Server 2005 R2, and other Microsoft products. You can find more information about this tool at http://www.microsoft.com/MAP.
With respect to SQL Server, the MAP tool will discover and inventory all the SQL Server instances in your organization. Data collected about each instance includes the version, edition, and current service pack applied. This tool also captures the hardware and platform details for computers that are running SQL Server instances, including determining whether the machines are virtual or physical. Figure 2-1 shows a page in a Word report that was produced by the tool.
The Excel workbook created by MAP is much more detailed. It lists which components are installed as well as information about the server where the instance is installed. Together, this tool and the documents it produces will help you as a DBA keep track of your environment as well as help you when you want to upgrade your environment.
Earlier, you read about the various editions available with SQL Server. Unless you know your organization is purchasing Enterprise edition, you need to determine whether the features and functionality you need are in any of the other versions. After all, there's no need to spend money unnecessarily.
SQL Server Books Online offers a plethora of great information on SQL Server, including a nice table that lists which edition contains which of the key features within the product. You can find this "Features Supported by the Editions of SQL Server 2008" article at http://msdn.microsoft.com/en-us/library/cc645993.aspx. Figure 2-2 shows some of the content of this Books Online article.
You can see from Figure 2-2 that a lot of scalability features are missing from Standard edition. That is not a misprint; don't be surprised if your favorite SQL Server 2008 feature is Enterprise-edition only.
Once your organization or team has decided on which edition to use, you need to verify the hardware and software requirements for SQL Server.
Going to college in the great state of Michigan, I had the opportunity to do an internship with an independent software vendor that was working on a project for one of the big three automobile manufacturers. I learned a lot from that experience, and it wasn't just Visual Basic and COM development. Rather, it was what was learned from the watercooler discussions. One time I overheard a few folks talking about an investigation into why brakes were wearing out much faster than they did when tested in their lab. Apparently the vehicles running those brakes that had this problem all had aftermarket rims, and these rims couldn't dissipate the heat that the brakes generated. The heat buildup caused the brakes to wear more quickly than with the stock rims. The moral of this story in relation to SQL Server is to check that the hardware you plan to install SQL Server on can support the requirements and thus provide a great end-user experience.
SQL Server Books Online does another great job going into detail on each of the editions in the article "Hardware and Software Requirements for Installing SQL Server 2008" at http://msdn.microsoft.com/en-us/library/ms143506.aspx. You can see from the article that the requirements vary between editions. Thus, it is important to consider the edition you plan to use.
In an enterprise environment, change happens. You'll frequently be faced with new requirements and user demand. In the Enterprise edition of SQL Server, it is possible to hot-add memory and CPUs, provided both the hardware and the operating system support this capability. By being able to add memory and CPU without restarting the server or the SQL Server service, you can maintain high levels of availability for your database.
In this section, we will perform the initial install of SQL Server, which simply installs the support files, including the .NET Framework and SQL Server Installation Center. If you already have SQL Server installed or choose not to install SQL Server at this time, you can skip this section. Throughout this book, the examples will use the evaluation edition of SQL Server.
Note
The Developer and Enterprise editions provide the same capabilities as the evaluation edition. If you have either the Developer or Enterprise edition available to you already, you can go ahead and use it rather than installing the evaluation edition.
You can download the evaluation edition from http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx. When you navigate to this link, you will have the option to download an IT Professionals version or Developers version. The actual SQL Server bits are the same for both versions. Selecting either one determines the content of the follow-up e-mail sent to your e-mail account by Microsoft. It has your e-mail because in order to download you will have to register. If you already have a Hotmail or Microsoft Live account, you can log in with that.
Once you choose the version, you will be prompted to register, and once you do, you will be presented with a download option. You can either download a DVD image of the product or download a single executable. Either one will work, and for the sake of simplicity, I'll assume you chose the x86 executable. Once a selection is made, the download begins, and depending on your Internet connection speed, you may want to consider grabbing a drink. The download is more than 1GB.
Once you have your own copy of the 1GB executable, you are ready to run it. Upon execution, you are asked for a folder name. Once you provide this folder path, the installation application will take about five minutes to extract all the files within the single file archive and re-create the entire SQL Server installation directory structure. When this process is complete, you will be greeted with a simple Extraction Complete dialog box.
It is at this point where you are ready to actually run the installation of Setup.
If you look at the files that are contained on the SQL Server setup media, you can see a readme.htm file. Readme files are provided with every release of the product and contain a lot of valuable information. In the SQL Server 2008 readme file, there are links to the Upgrade Advisor tool and links to various SQL Server Books Online topics regarding installing SQL Server.
At this point, you are ready to begin installing SQL Server. As with most Microsoft products, there is a setup.exe executable located in the root folder of the SQL Server media. This program will first verify that you have the .NET Framework version 3.5 Service Pack 1 installed as well as Windows Installer version 4.5. If it is determined that you are missing any of these prerequisites, setup.exe will install them for you at this time. You may be required to reboot the machine depending on which operating system and which prerequisites you have already installed.
Once the prerequisites are installed, running the setup.exe program will simply launch the SQL Server Installation Center dialog box. This small application is the graphical launch point for all kinds of setup tasks. Here you can install a stand-alone instance of SQL Server or even add a node to an existing SQL Server cluster. The installation center consists of seven tabs, each providing values to an aspect of installing SQL Server. Let's take a look at each of these tabs in more detail.
The Planning tab (Figure 2-3) is the first tab that is displayed when the SQL Server Installation Center is started.
Previously in this chapter I discussed issues to think about when you install SQL Server, such as figuring out whether your current hardware and software are compatible with SQL Server 2008. The Planning tab helps you plan your installation of SQL Server by making it easy to quickly access this kind of information. This tab also provides links to other important information such as online release notes and online installation help. In addition to hyperlinks, there are two tools within this tab, the System Configuration Checker and the Upgrade Advisor. The System Configuration Checker (Figure 2-4) will check the state of your server and determine whether Setup can proceed. Examples of these tests include determining whether your server needs to be restarted or whether you meet the minimum operation system version to install SQL Server.
There are other times within Setup where this System Configuration Checker will run for you. For example, when you are installing a stand-alone instance of SQL Server, this utility will be run at the beginning of that task.
The other tool on the Planning tab is the Upgrade Advisor. Use the Upgrade Advisor when you have an existing SQL Server installation and want to upgrade it to SQL Server 2008. The tool will check, from a syntactical standpoint, the integrity of your SQL Server installation. For example, if your application has referenced a deprecated stored procedure or function, this tool will tell you. Knowing and acting on this information before actually upgrading the server can save a lot of time and pain. This tool is discussed in more detail in the "Upgrading SQL Server" section of this chapter.
One of the last links on the Planning tab is the "How to get started with SQL Server 2008 Failover Clustering" link. Clustering is a big deal in most enterprise environments, and the way SQL Server Setup installs SQL Server on clusters has changed in SQL Server 2008. This link is an effort to help users with this new way of installing SQL Server on clustered servers.
The Installation tab (Figure 2-5) provides a series of wizards to help users install or upgrade SQL Server.
One of the many improvements to the setup experience with SQL Server has been the introduction of more wizards in key areas. On this tab you can see that four of the five links launch wizards that help users. The first option, "New SQL Server stand-alone installation or add features to an existing installation," is self-explanatory. It launches the wizard you will use later in this chapter to install your instance of SQL Server. The next option is "New SQL Server failover cluster installation. The act of setting up and configuring a clustered SQL Server has traditionally been a tedious process. This version of SQL Server relieves some of the frustration by providing easy-to-use wizards to help the process along. Even common tasks such as adding a node to a cluster can be done using the "Add node to a SQL Server failover cluster" link, which also launches a wizard. There is even a wizard that will walk you through an upgrade of SQL Server 2000 or SQL Server 2005. This would be, you probably guessed it, the "Upgrade from SQL Server 2000 and SQL Server 2005" link.
The Maintenance tab (Figure 2-6) has three more wizards for you to enjoy.
The Edition Upgrade Wizard allows you to upgrade an installation from a lower edition to a higher one. If you don't know what is meant by a "lower" edition, just think of how much each edition costs. For example, you can upgrade from Standard to Enterprise, but you cannot upgrade from Enterprise to Standard edition. This feature isn't here just to up-sell you on the bigger and faster vehicle; it does have some use in the upgrade scenario. Although it is possible to upgrade directly from SQL Server 2000 Standard to SQL Server 2008 Enterprise, the best practice would be to upgrade to SQL Server 2008 Standard first and then perform this edition upgrade once your existing applications are verified. Edition upgrades weren't easily done in previous versions of SQL Server. With SQL Server 2008, it is now possible to perform edition upgrades using a wizard.
The Repair Wizard is the second of the three wizards available on the Maintenance tab. It runs through and checks the integrity of the installation, making any necessary changes to get you back to the original installation. Use this wizard if you had a previously failed installation of SQL Server.
The last wizard on the Repair tab is related to the clustering scenario. The "Remove node from a SQL Server failover cluster" option guides you in removing a node from a SQL Server cluster. This task was once a pain to perform, but this wizard makes node removal much easier in SQL Server 2008.
The tools tab (Figure 2-7) contains three tools that will help users in their installations of SQL Server.
The System Configuration Checker tool is the same one as was described on the Planning tab. The link to the tool is included on this tab also, just for convenience's sake.
On some occasions, you may be installing SQL Server 2008 on a server that may already have components of SQL Server or other instances of SQL Server installed. The "Installed SQL Server features discovery report" option does a quick inventory of the SQL Server instances and components that are installed on the local server. The tool presents these findings in an HTML document format for easy reading. Although this tool obtains this information only for the local server, it is possible to run a different utility to obtain this kind of inventory information across your enterprise. The MAP toolkit can help you obtain this information; it was described previously in the "Accessing the Environment" section of this chapter.
If you are new to SQL Server, you may not be familiar with Data Transformation Services (DTS) packages. DTS is SQL Server's ETL tool that was introduced in SQL Server 7.0. DTS gained a lot of popularity in SQL Server 2000 and was widely deployed. However, even though DTS was successful, its design was limited and not very extensible. Microsoft decided to rewrite DTS into what is now called SQL Server Integration Services (SSIS). The two utilities do not share a single line of code, so you can imagine that the package formats are different and not compatible. There are a few options for heavy users of DTS packages. In both SQL Server 2005 and SQL Server 2008, the DTS runtime, dtsrun.exe, is provided, so you could still run these packages, or you could run a wizard from the management tools that would do a best effort to upgrade to the new format. The wizard that is available within the Tools tab, launched by clicking "Upgrade Integration Services packages," helps users upgrade from the SQL Server 2005 format to the SQL Server 2008 format. Since both formats are XML based, there isn't a large risk in upgrading between these versions.
The Resources tab (Figure 2-8) contains a plethora of links to online resources related to SQL Server 2008.
SQL Server Books Online will be your best friend. Books Online contains an immense amount of SQL Server information. From T-SQL statement definitions to how-to articles, you will find yourself frequently referring to it. SQL Server Books online can always be accessed for free from the Web at http://msdn.microsoft.com/en-us/library/ms130214.aspx.
The other link on the Resources tab that is worth mentioning is the link to the CodePlex samples web site. In previous versions of SQL Server, samples were included on the install media. SQL Server 2008 does not include any samples; rather, all samples and example databases like AdventureWorks are available from CodePlex. The CodePlex website isn't just for SQL Server; it is actually a multiproduct open source project workspace (http://www.codeplex.com).
The Advanced tab (Figure 2-9) contains links to wizards that will assist the user in special-case installations.
One of the more common techniques for installing SQL Server is to do it without a user interface (UI). This technique is called a scripted install and is very useful when you have to install multiple SQL Server instances. Imagine trying to run through the setup wizard on each one of your 20 SQL Server instances. Not only would that process take a long time, but you would be spending a lot of time sitting at the server waiting for the installation to finish. Scripted installs leverage a configuration file that specifies all the properties you would have set while walking through the wizard. The end result is you don't have to stand there and wait to click the Next button.
Creating the configuration file by hand is a bit tedious. There is a SQL Server Books Online how-to article called "How to: Install SQL Server 2008 Using a Configuration File" at http://msdn.microsoft.com/en-us/library/dd239405.aspx. This article makes a few key points about using configuration files. The most important one is that you don't have to sit and launch Notepad and write one of these configuration files from scratch. You can simply run through the setup wizard once and, instead of proceeding past the Ready to Install page, just copy the file path provided on this page and click Cancel. The file path is the location of a configuration file based on the options you selected in the wizard.
Once you have a configuration file, you can simply pass this file to Setup on the command line (setup.exe /ConfigurationFile=myCustomSQLInstall.ini) and come back when it's finished.
The Options tab (Figure 2-10) allows the user to select a processor architecture as well as the location of the installation media. This option is useful in a consolidation scenario where you want to install an x86 32-bit install on an x64 system.
Begin your installation of SQL Server by clicking the "New SQL Server stand-alone instance..." link on the Installation tab of the SQL Server Installation Center. This link will launch a wizard that you can use to install a single instance of the SQL Server database engine. When you launch the wizard, Setup will run the System Configuration Checker tool, the same one that can be launched from the Tools tab. This checker will make sure that the server is in a state that can handle the installation of SQL Server. Some of the issues that could prevent an install include situations where the server is pending a reboot or the user is trying to install SQL Server on an unsupported operating system.
After the check is performed, you will be asked for the product key. In the case of the evaluation edition, you will be asked either to enter the product key or to simply choose which free edition you want to install. To enable all the features in the product, make sure Enterprise Evaluation is selected in the "Specify a free edition" drop-down list.
Upon continuing the wizard, some additional SQL Server installation files will be installed; after that, you will be presented with the actual installation wizard. The first page of this wizard will be another system configuration check, which will check some different properties than the first configuration check. Once this completes, you are now ready to start telling the wizard exactly what you want.
The Features Selection page (Figure 2-11) allows you to specify which components of the product you want to install.
By selecting an instance feature, you are asking Setup to install a new instance of the database engine, Analysis Services engine, or Reporting Services. These options will install a new instance regardless if there is already one on the server. There are some components, though, that don't require more than one instance on the server. These components are called shared components and include things like SQL Server Books Online (would you really want two different copies of SQL Server Books Online on the same server or client machine?). If you took a good look at Figure 2-11, you may have noticed that one of the items, Redistributable Features, does not have any items under it. This is by design. I would imagine that this node will be removed in future service packs or versions of the product.
For the purposes of our walk-through, select Database Engine Services from the Instance Features node as well as all the shared features except Microsoft Sync Framework. (This framework has to do with mobile devices, and this topic is not covered in this book.)
The Instance Configuration page (Figure 2-12) allows users to select which type of instance to install—either a default instance or a named instance. Per server, there can be just one default instance. However, you can install multiple named instances. In fact, you can have up to 50 instances installed on a server. These instances could all be SQL Server 2008 or a mix of SQL Server 2000 and SQL Server 2005.
There are some restrictions on the name of a named instance. These are all documented in the "Instance Configuration" article in SQL Server Books Online. Some of the restrictions include a 16-character limit and the inability to use SQL Server reserved words, such as default.
When SQL Server creates a new instance, it creates various registry key entries and folders within the file system. Since you can install more than one instance of the SQL Server database engine on a server, SQL Server needs to uniquely name these registry keys and directories. The instance ID is the text that will be used to help create the unique name for these objects. By default, the instance ID is the same as the instance name. The instance root directory is the folder that will contain the binaries for the SQL Server database engine.
If there were existing instances of SQL Server installed on this server, the Installed Instances grid would be populated with these. This grid is useful when it's midnight and you can't remember whether or not you installed the new instance on your server.
The Disk Space Requirements page simply summarizes how much disk space will be required to install the selected features. The space usage is broken up by space required on the system drive, space required in the shared feature folder, and space required by the instance directory itself. There are no options to select on this page; it is informative only.
The Server Configuration page (Figure 2-13) is one of the more important pages within this installation wizard.
Service accounts can be built-in like the Network Service account, or they can be a Windows User account. One thing to keep in mind is whatever account is used for these services has a high privilege. If you are not using Windows Vista or Windows Server 2008, you should refrain from using the built-in system accounts and instead use a regular domain user account. The reason is that if your SQL Server instance is compromised and the attacker becomes sysadmin, they are effectively running as whatever user is listed as the SQL Server service account. If you have only a single domain user account for SQL Server and a different one for other services, then it would be much more difficult for the attacker to obtain information from the other services. Similarly, if SQL Server is running under the Local System built-in account, that account has access to everything on the server. Thus, if your server gets compromised and you are running under Local System, your whole server could be exposed to the attacker. If you are using Windows Vista or Windows Server 2008, you can use the Network Service account because it is more secure and easier to administrate than a domain account.
The Service Accounts grid on the Server Configuration tab allows the user to specify a built-in account or a user account to use for each of the services that will be installed. There is a button called "Use the same account for all SQL Server services." This saves on typing, but a best practice would be to forgo assigning all services to the same account rather use different users (or domain users) for each of the services.
The Startup Type drop-down options are Automatic, Manual, or Disabled. This setting determines the state of the service upon a server reboot. Automatic means this service will be automatically started when the operating system starts. Manual means that an administrator will have to explicitly start the service. Disabled means that the service will not be available. A best practice is to set services that you know you will not use to either Manual or Disabled. By stopping or disabling a service, you are reducing the surface area for attack.
Next to the Service Accounts tab is the Collation tab. Collation is all about language support for SQL Server. A collation setting defines how SQL Server sorts and compares characters that are stored in non-Unicode data types. Image what should happen if you issued the statement SELECT * FROM Customers where State='MA' and your state column data within the table was stored in lowercase, as in ma. Depending on which collation you were using, this query would either return all the rows as expected or return nothing. The result would depend on if the collation was case insensitive or case sensitive. For more information on using collations, check out the SQL Server Books Online topic "Working with Collations."
The Database Engine Configuration page (Figure 2-14) contains important security-related parameters that need to be addressed.
This page in the wizard has three tabs: Account Provisioning, Data Directories, and FILESTREAM. The Account Provisioning tab allows you to define which kind of authentication mode SQL Server will support. If you select just Windows Authentication mode, then only Windows users can connect to SQL Server. This situation is problematic if you can't use Active Directory or the local user store in Windows. The other option in this case would be Mixed mode. In Mixed mode, you can still have Windows-authenticated users connect, but you could also have a user credential that exists only within SQL Server. These kinds of principals are called SQL logins. SQL logins are very helpful in heterogeneous environments when you have non-Microsoft clients connecting to SQL Server. In that case, use should be limited in production because they are not as secure as using Windows-authenticated accounts. SQL-authenticated accounts are easier, though, to set up and use, and for that reason, they are used within the examples throughout this book. Thus, you need to select the Mixed Mode radio button.
When SQL Server is installed, there are a few accounts that are created before anyone connects to SQL Server. The SA account is the system administrator account and has access to everything within SQL Server. This is the highest privileged account, and its credentials should be protected. This tab allows you to enter a password for the SA account; be sure to provide a complex one.
The "specify SQL Server administrators" list box contains all the users that you want to give system administrator access. For some customers, Windows administrator users who install SQL Server are not necessarily those who will manage it. In this case, the Windows administrators would simply add whoever the DBA will be in the "Specify SQL Server accounts" list box. For the purposes of our evaluation of SQL Server, just click the Add Current User button, and your Windows User account will be added as an administrator.
The Data Directories page (Figure 2-15) within this wizard page is where you can specify the default folder paths of various databases within SQL Server.
In this example, all of the directories are on the local C drive. For demonstration purposes this is fine, but on a production system, it's best to spread different database files on different physical hard drives. This is important for user databases but is equally important for a system database called tempdb, which is SQL Server's scratch pad and is used by the server to sort data in queries and do many other data-intensive operations. Thus, it is good to put tempdb on its own physical disk if possible.
The FILESTREAM tab is used to enable the Filestream feature in SQL Server 2008. The Filestream feature allows users to exceed the 2GB storage limit by placing the data on the file system. The Filestream feature manages the integrity of the file and ensures that no one can directly affect the data on the file system. As an added bonus, if you back up the database, the files stored and managed by the Filestream feature will also be included in the backup. You do not need to enable the Filestream feature at this time. If you want to enable the Filestream feature after you install SQL Server, you can enable it using the SQL Server Configuration Manager. You can find details in the SQL Server Books Online article "How to: Enable FILESTREAM."
This page displays two options. The first option is to opt in to send error information to Microsoft. The other option is to send feature usage information anonymously collected to Microsoft. Although some may be hesitant to do this because of fear of sending sensitive information over the Internet, this issue isn't a concern since these options do not send raw data. Rather, these options will send error codes and feature usage to Microsoft anonymously. This data is extremely valuable to the SQL Server product team because it can answer questions like "How often is this feature used?" In the end, by enabling these options, you will indirectly improve the user experience of the product.
The Installation Rules page will perform another system configuration check, but this time it's yet a different set of rules. You specify parameters such as whether the WMI service is on and whether the file system is NTFS. There are no user-customizable actions on this page.
The Ready to Install page (Figure 2-16) is the page you have probably been waiting for. It gives a detailed summary of what Setup plans to do.
At this point, you could click the Install button and have the Setup application start performing the install, or you could obtain the installation script by locating the file at the bottom of the page. The "Configuration file path" setting points to a configuration file that is automatically created based on your selections on the previous wizard pages. If you wanted to perform a silent or remote install, you could now use this configuration file.
If you want to follow along in this book, you can click the Install button now. This action will start the install process. The duration of installation depends on how many options you selected to be installed and what kind of platform you are installing onto. When Setup is complete, you will be presented with the Complete page. Here you will find a link to the setup log file. If bad things happened in the setup and it failed, the setup summary log file is the first place to go to look into what might be wrong. If you ever call Microsoft product support for any installation issues, finding and sending this log will be one of the first tasks you will be asked to do. The Complete page (Figure 2-17) shows the location of the setup log files.
Chances are that if your organization has existing SQL Server implementations, you may eventually need to upgrade them. Each version of SQL Server (and almost any other software product) has a limited number of years that the product is officially supported. Once this time is up (as it was in April 2008 for SQL Server 2000), customers can purchase some extended support agreements. In reality, those SQL Server 2000 customers by 2012 will have to upgrade if they want any kind of hotfix to the product. You can find detailed information on the life cycle of Microsoft products at http://www.microsoft.com/lifecycle.
When you upgrade servers in your organization, it's probably not a spur-of-the-moment decision. There should be a lot of planning done beforehand to make sure the upgrade goes smoothly. When thinking about upgrading a SQL Server database, it is important to first consider the following questions:
Is the server hardware capable of this new version of SQL Server? If you are planning on using the same hardware you were using to run SQL Server 2000, this may not be suitable for SQL Server 2008. Complete requirements can be found in the "Hardware and Software Installation Requirements for SQL Server 2008" article in SQL Server Books Online at
http://msdn.microsoft.com/en-us/library/ms143506.aspx.Are you planning on using the right edition of SQL Server? A lot of the bright and shiny features of SQL Server 2008 are in Enterprise edition. If you are currently running Standard edition and want to upgrade because you want to leverage the new Resource Governor feature, then simply upgrading to SQL Server 2008 Standard edition will be disappointing. On the bright side, it's very easy to upgrade editions within SQL Server's Setup application. If this is your scenario and you wanted to go from SQL Server 2000 or 2005 Standard edition to SQL Server 2008 Enterprise, the best practice is to go through the setup process and upgrade the server to SQL Server 2008 Standard first. Once upgraded, test your applications against it. When satisfied, rerun Setup, and run the Edition Upgrade Wizard on the Maintenance tab of the SQL Server Installation Center. SQL Server Books online includes an article called "Features Supported by the Editions of SQL Server 2008" located at
http://msdn.microsoft.com/en-us/library/cc645993.aspx. This article has a table that lists the high-level features and which editions they're available in.Note
Other than feature availability between editions, the development interfaces are the same for all editions of SQL Server.
Are you using deprecated features? As new versions of SQL Server are released, sometimes an existing feature or functionality is no longer needed. Since users and third-party software developers may have extensively used the feature, Microsoft cannot simply remove the feature from the product upon upgrade to the newer version. This action would break the user's applications and make the incentive of upgrading much less desirable. For this reason, Microsoft has a three-release deprecation policy.
Consider the
sp_renamedbstored procedure. This stored procedure's functionality was replaced by the introduction of aMODIFY NAMEparameter in theALTER DATABASEstatement. Since having two ways of renaming a database is not desirable, Microsoft officially deprecated thesp_renamedbstored procedure starting in SQL Server 2005. This means that the next major version after SQL Server 2008 will be the last version that this stored procedure exists in the product. So, if you don't upgrade your scripts by this future version, they will not work anymore.Microsoft does a good job at warning users of these deprecated features. There is a SQL Server Books Online article that lists all the deprecated features for the release. This article is called "Deprecated Database Engine Features in SQL Server 2008" and is located at
http://msdn.microsoft.com/en-us/library/ms143729.aspx.Before you perform an upgrade, you can launch the free Upgrade Advisor from the Planning tab of the SQL Server Installation Center. This tool runs through your existing databases, SQL Server trace files, and T-SQL scripts and produces a report of issues that should be addressed before you upgrade. One of the issues the tool reports on is the use of deprecated features.
If after you upgrade you are still concerned that you may have some deprecated features being used, you can use the Windows Performance Monitor tool and monitor the SQL Server: Deprecated Features performance object counter. Figure 2-18 shows the Add Counters dialog box of the Windows Performance Monitor tool.
As you can see from the Add Counters dialog box, you can collect specific deprecated features, or you can select "All instances" and collect any occurrence of a deprecated feature.
Which upgrade strategy should you implement? When it comes time to actually perform the upgrade, you have two options to actually upgrade the bits on the server to SQL Server 2008. You can implement an in-place upgrade that's basically running Setup and replacing the old SQL Server bits on the disk with the new SQL Server 2008 ones. Alternatively, you could do a side-by-side upgrade where you do not touch the old installation at all and instead install a new instance of SQL Server 2008. With this new instance, you will copy databases and objects from the old database into the new one. There are pros and cons to either one of these kinds of upgrades. An in-place upgrade is the fastest and least resource intensive, but it incurs more downtime of the server, and if something bad happens, it will take a long time to reinstall the old server version again. The side-by-side upgrade is resource intensive and more of a manual operation, but the benefit is you don't have to move clients over until the new server is ready. If something bad happens on upgrade, there is no downtime.
As you can see, there are a number of questions to ask and issues to deal with when upgrading. Your organization may also have additional standard operating procedures for upgrading servers that may involve the use of test servers and other processes.
The setup and configuration experience of SQL Server appears to change with every version of the product. These changes are mostly for the better. Peter Saddow is one of the program managers responsible for the design of the installation and configuration of SQL Server.
Robert Walters: What is your role at Microsoft?
Peter Saddow: I have been at Microsoft for 10 years and on the SQL Server Management Services Team for the last 3 years designing the overall setup installer and patching experience. My focus areas include the UI, the logging infrastructure, and the command-line interface. My patching work includes designing the patch installer and Microsoft Update detection logic on when to offer updates to systems.
Robert Walters: What improvements have been made to the product that you feel directly benefit the experience?
Peter Saddow: The overall SQL Server 2008 setup experience has been completely rewritten. During the design phase, we worked with several customers on our initial prototypes. Users were looking for a launch pad that would provide guidance on how to plan for SQL Server installs and upgrade. This feedback turned into the Installation Center, which has links to several web resources to help with planning, links to various setup wizards, and links to tools. We also heard from customers what configuration options should be settable within the Setup UI. One of the input parameters customers repeatedly wanted was the ability to set the database engine data directories since these options are hard to change afterward.
Robert Walters: What is the most challenging part of installing SQL Server?
Peter Saddow: For the most part, setting up SQL Server 2008 is usually self-explanatory. However, the most challenging part could be troubleshooting a failed install. There are lot of rules and validation in place to help you with identifying issue before the installation process begins. If you installation does fail, the UI should display the error, and if the failure message is not very clear, it may be necessary to view the log file for more information. If this information is still not helpful, then you should seek out help by contacting Microsoft or posting your question to MSDN forums or similar.
Robert Walters: What advice do you have for users new to SQL Server?
Peter Saddow: Before you install SQL Server, you should plan for your installation. Will you be installing on a system that meets the minimum system configuration? How do you want configure the service accounts? How do you where you want to place the database files?
Robert Walters: Have there been any changes made to the way SQL Server is patched?
Peter Saddow: The patching infrastructure has been rewritten from the ground up and extends the setup infrastructure. For SQL Server 2008, the cumulative update (CU) and service pack (SP) packages can patch all editions of SQL Server 2008, and there is no need for a different package. The ability to slipstream the original media (RTM) and a service pack, or a service pack and a CU for the service pack, has been enabled. This means you can perform a single install, and when you have completed the install, you will be at the specified SP level. You can use the slipstream infrastructure for installing SQL Server 2008 or for upgrading from a prior version to SQL Server 2008. The service pack also enables the ability to uninstall. Prior to the SP uninstall feature, if you needed to remove the service pack, you would need to completely uninstall the produce and reinstall. This was a time-consuming process. On a cluster, you have more control over when a node is patched to avoid downtime, unlike the patching infrastructure in SQL Server 2005. Plenty of documentation has been created, describing how to slipstream and how to patch a clustered system.
64-bit installations of SQL Server are more often becoming the standard for SQL Server databases. Their large address space makes memory-intensive applications like SQL Server Analysis Services and SQL Server Integration Services work extremely well. Although using 64-bit hardware may be an acceptable standard for your organization, choosing which edition of SQL Server to use may be a political issue to solve. The key issue in this decision from a nontechnical standpoint is the cost differential between the Standard and Enterprise editions. This extra cost is negligible compared to the performance and productivity gains you get from Enterprise features such as Resource Governor and data compression.
When installing SQL Server, it's best to have a plan. Without a plan, you may be ill prepared if bad things happen. Having a plan is even more critical when you are upgrading an existing SQL Server database with active user databases.
The setup experience is different from previous versions of SQL Server. When setup.exe is launched, it simply displays the new SQL Server Installation Center. From this dialog box, you have the option of launching other setup wizards that help with specific tasks, such as installing a new instance of SQL Server or adding a node to an existing cluster.
If you did not install SQL Server because of lack of time or hardware resources and want to quickly test it out, you can easily launch a Microsoft virtual lab on SQL Server 2008. These virtual labs teach many aspects of SQL Server including tasks relating to both DBAs and developers. The virtual labs are run through the web browser in a virtual server environment, so you are free to do whatever you choose to do. You can find the virtual labs at http://www.microsoft.com/sqlserver/2008/en/us/virtual-labs.aspx.
Finally, I've mentioned a number of URLs in this chapter that take you to various online resources such as Books Online, the NASDAQ case study, and more. Table 2-1 lists these URLs and online resources in one place for convenient reference.
Table 2.1. SQL Server Resources
Resource | Description |
|---|---|
SQL Server evaluation edition |
|
NASDAQ case study |
|
Microsoft Assessment and Planning Toolkit |
|
"Features Supported by the Editions of SQL Server 2008" |
|
"Hardware and Software Requirements for Installing SQL Server 2008" |
|
SQL Server Books Online |
|
CodePlex samples |
|
"How to Install SQL Server 2008 Using a Configuration File" |
|
Life cycle of Microsoft products |
|
Microsoft virtual labs |
|
"Deprecated Database Engine Features in SQL Server 2008" |
|