Installing SQL Server

In this section you learn about the different types of installations: side by side, upgrade, and new install. Details about upgrades are covered in Chapter 3. You can also create a script that installs SQL Server for you. This can be very useful when you need to complete many similar installations.

Note

Each install works with a single instance. If you wish to install a named instance of the database engine and a default instance of Analysis Services, you must go through two separate install processes.

Side by Side, Upgrade, and New Install

A new install occurs when no other SQL Server components are on the server and you have a clean slate to work with. Be careful that there are no remnants of previous SQL installs. Check the directories and registry to ensure that you have a clean system.

If SQL Server components exist on the box, you can upgrade the existing instance. In this case you install SQL Server on top of the existing instance. SQL Server also supports side-by-side install. A side-by-side install occurs when you are adding another instance of SQL Server. SQL Server 2005 supports multiple instances of the Database Engine, Reporting Services, and Analysis Services on the same box. SQL Server 2005 will also run side-by-side with previous versions of SQL Server. If the existing instance is a default instance, of course, your new install must be a named instance. The following table indicates which versions can run side by side with SQL 2005. This table was obtained from SQL Server Books Online.

Open table as spreadsheet

Side-by-side support	SQL Server 2000 (32-bit)	SQL Server 2000 (64-bit)	SQL Server 2005 (32-bit)	SQL Server 2005 (64-bit) IA64	SQL Server 2005 (64-bit) X64
SQL Server 7	Yes	No	Yes	No	No
SQL Server 2000 (32-bit)	Yes	No	Yes	No	Yes
SQL Server 2000 (64-bit)	No	Yes	No	Yes	No
SQL Server 2005 (32-bit)	Yes	No	Yes	No	Yes
SQL Server 2005 (64-bit) IA64	No	Yes	No	Yes	No
SQL Server 2005 (64-bit) X64	Yes	No	Yes	No	Yes

The biggest issue with side-by-side installs is memory contention. Make sure you set up your memory so that each instance is not trying to acquire the entire physical memory.

Some companies who need many instances of low-usage SQL Servers are placing a single instance of SQL Server in a virtual machine and placing several virtual machines on a single server. VMWare and Microsoft's Virtual Server software support this architecture. Using Microsoft's Virtual Server and Virtual PC, you can create an image using Virtual PC and quickly load as a virtual machine to Virtual Server. This is more often found in the development environment than production but is dependent on the load placed on the servers and the size of the physical box. This does allow you to move the instances around transparently and to provision new instances quickly.

Scripted Installation

You can install SQL Server via command prompt calls to Setup.exe. In our experience, this was used in the past more frequently than today. You could create a small setup script, with all of your options selected, send it to a remote site, and have someone run the script to do the installation. We include a couple of the command-line syntax examples in this section. If you wish to do a very small number of installations, you can connect to the server via a remote desktop connection and do the install as if you were local. You can also use scripting to upgrade or uninstall SQL Server.

If you are installing SQL from a remote share, you must have read and execute permissions on the share. Expect all commands to be case sensitive. Also, make sure you notice the direction of the slashes in the commands; it matters.

First, ensure that the SQL Install CD/DVD is in the disk drive. Use the following command-line prompt to install all components:

 Start /wait <CD or DVD Drive> \setup.exe /qb INSTANCENAME=<InstanceName> ADDLOCAL=All PIDKEY=<pidkey> SAPWD=<StrongPassword> 

The Database Engine, SQL Server Agent, Reporting Server, and Analysis Services are all instance aware. To install any or all of these components without installing everything, use the following syntax:

 Start /wait <CD or DVD Drive> \setup.exe /qb INSTANCENAME=<InstanceName> ADDLOCAL=SQL_Engine, SQL_Replication 

Either the /qn or /qb flag is required. Using /qn suppresses the user interface, and all setup messages and errors are placed in the setup log. Using /qb shows the user interface during install and displays messages in dialog boxes. You can think of /qn as the unattended install and /qb as an attended install.

The other components, which are not instance aware, do note require the INSTANCENAME token. If you are installing to the default instance, use INSTANCENAME=MSSQLSERVER.

The ADDLOCAL option describes the software you wish to install. You can get the complete list of option values in Books Online. The thing you need to know here is that there is a two-level hierarchy of software pieces. As an example, the SQL_Engine parent has three children: SQL_Data_Files, SQL_Replication, and SQL_FullText. If you include only the parent, only the parent, none of its children, is installed. Installing a child feature automatically installs the parent, however. Removing a parent automatically removes all of the child features. These features are all case sensitive and must be included in a comma-delimited list with no spaces.

PIDKEY is the software key that comes with your software. Do not include spaces or the - character in the key.

The SAPWD option is used to specify the SA password. Always use a strong password. A strong password contains at least eight characters and includes characters from at least three of the following groups: uppercase characters, lowercase characters, digits, other special characters. (More details regarding securing your SQL Server install are covered in Chapter 9.)

There are many other parameters you can use in the command prompt, many of which are login passwords for service accounts, virtual names, and IP addresses for cluster installs. Check out Books Online to get the complete list.

You can also take all of the options, format them in an .ini style, and place them in a text file. Then reference the ini file as follows:

 Start /wait <CD or DVD Drive> \setup.exe /settings C:\sqlinstall.ini /qn 

Note

An ini file is an "initialization" file formatted in an ini style. Microsoft provides an ini file that you can modify to meet your needs. The filename is Template.ini, and it is located in the root directory of your install CD or DVD.

Remote Installation

Remote installations are not supported; you need to use Remote Desktop Connections instead. In previous editions of SQL Server, you could sit at one machine and do an install directly to another machine. This is called a remote install. This is beneficial if you are in one city and need to install SQL Server on a machine in another city. However, since the operating system now allows Remote Desktop Connections, there is no need for the SQL product to include this capability. You can make a Remote Desktop Connection by selecting Start All Programs Accessories Communications Remote Desktop Connection. You will be prompted for a server name to connect to and login credentials. Once logged in, you install SQL as if you were sitting at the Server Console.

We are not going to go through each of the dialog boxes for the install, but I will take you through the more critical ones.

Our test installs were from the MSDN Universal DVD. Microsoft Developer Network (MSDN) subscriptions allow developers to purchase much of Microsoft's Operating System, Servers, Office Products, and more at a very attractive price and use them for development purposes. My installations were done from this software source.

To get started, put in the CD or DVD. You may get an automatically started dialog box and have to navigate to the correct version of SQL you wish to install. If you have to navigate on your own, you will eventually see 'Setup.exe' in the Servers Directory of the version that you need. Run it.

You must agree to the license terms and the process may install some prerequisites. Then it will run a system configuration check, as shown in Figure 2-1. Do not ignore anything that does not pass. Even warnings should be addressed now, not after you have completed what may be a partially successful install. IIS is only required for Reporting Services installations, even though the warning occurs.

Figure 2-1

Next, choose the components you wish to install, as shown in Figure 2-2.

Figure 2-2

Your license for SQL Server will allow you to install all of the components on a single box. However, if you decide you wish to install the SQL Server engine on one box, SQL Server Reporting Services on another box, and Analysis Services on a third box, that is three licenses, not one. Licensing for Microsoft products changes over time and can become very complicated. You should contact your vendor or Microsoft representative to ensure you are in compliance.

SQL Server includes two demonstration databases with SQL 2005. AdventureWorks is a transactional sample, and AdventureWorksDW is a data warehouse sample. These demo databases are not installed by default. If you want to install them now, and you probably do, click the Advanced button on the Components to Install dialog box. You will be taken to the Feature Selection dialog box, shown in Figure 2-3.

Figure 2-3

In this dialog box you can customize which features you wish to install. Open up Documentation, Samples, and Sample Databases. You will see that they are not selected for install. Open up Sample Databases and select the "Entire feature will be installed on the local hard drive" option. This will install the sample databases. You can install them later, but doing so now will save you a little time. Click Next.

Five dialog boxes later, you will eventually get to the Collation Settings dialog box, as shown in Figure 2-4. Make your collation-setting choice here. You should have a good reason for your choice; think about it and make a good decision.

Figure 2-4

Continue through the install. You will get a Setup Progress dialog box, as shown in Figure 2-5.

Figure 2-5

This gives you status information about the install. If you click any row, it will open up the log file for that item. This log file contains a huge amount of detail regarding the specifics of the install. If you encounter any problems during your install, you should go here to get those details. This information is stored in the <SQL Install Directory>\90\Setup Bootstrap\LOG\Files directory. If you get into trouble and have to call Product Support Services (PSS), you will need to have these files ready.

Once your install is complete, make sure you check out your new server. Make sure you can log in, and do some work.

Where is AdventureWorks?

Installing the sample databases is a little strange. On some systems, there was no additional effort required to install, other than the installation steps outlined in the previous section. On other systems, more work was required.

Nothing we did in the initial install allowed us to get the sample databases installed. You must go to Control Panel Add or Remove Programs to get this done.

Select Microsoft SQL Server 2005 and choose Change, and you will be presented with the Component Selection dialog box, as shown in Figure 2-6. Choose Workstation Components.

Figure 2-6

Click Next until you get to the Change or Remove Instance dialog box, and choose Change Installed Components. You will then see the Feature Selection Dialog Box, as you did during the initial install, as shown in Figure 2-7. Select "Entire Feature will be installed on the local hard drive" for the sample databases. You might want to do this for the sample applications as well.

Figure 2-7

You'll then see the Sample Databases Setup dialog box shown in Figure 2-8. Choose "Install and Attach Sample Databases," choosing the instance where you wish them to be installed. Then click through, and you should now be able to use the sample databases.

Figure 2-8

When you install AdventureWorks via attach, it will use the SQL_Latin1_General_CP1_CI_AS collation, not the default server collation. If you wish to use a different collation, you must drop the database and recreate it from the installation script. The script is named C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP\instawdb.sql. You can also use this script to revert AdventureWorks to its original state, but first you need to change the script as follows. Search for the following string:

 SET @data_path = @sql_path + 'AWDB\'; 

Directly below this line you will find the following instructions for reverting the database.

 SET @data_path = @sql_path + 'AWDB\'; -- -- To reinstall AdventureWorks from this script change the SELECT statement above to the following. -- -- SET @data_path = 'C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP\'; -- 

Where Are Pubs/Northwind?

The familiar and comfortable Pubs and Northwind databases are not included on the SQL Server CD. Although AdventureWorks is a much more complete sample, you may wish to continue using the Pubs and Northwind databases on SQL Server 2005. You can download them from Microsoft's Web site. The file name is SQL2000SampleDB.msi. Download and extract this file. By default it will go into the C:\SQL Server 2000 Sample Databases directory. There will be instructions that explain the install process, which is very simple.