Table of Contents

Chapter 5

Installing SQL Server

Certification Objectives *

Limitations of the SQL Server Platform when Working with Character Data *

What is the Character Set, and How Does It Affect Me? *

How Come I Can Restore a Database that Used a Different Character Set If Both Servers Use Binary Sort Order? *

Why Does My Data Have To Be Reloaded If I Change the Character Set? *

Choosing the Unicode Collation Sequence *

What is Unicode? *

How Does the Unicode Standard Affect SQL Server 7.0? *

Choosing the Appropriate Sort Order *

Upgrade from a SQL Server 6.x Database *

Perform an Unattended Setup *

Creating an Unattended Installation Script *

Start Menu Entries *

SQL Server Services *

File and Directory Structures *

From the Classroom *

SQL Security Begins With Your SQL Service Account *

Should Your SQL Server Be A Domain Controller? *

Return Error Codes from Unattended Installation *

Certification Objectives

In this chapter, we will shift our focus from the theoretical world of planning to one in which we are able to interact with the SQL Server platform. Using the theory derived from the last chapter, we can bring our installation plan to life quickly and effectively by beginning a hands-on installation of SQL Server 7.0 on a Windows NT Server.

We will begin by discussing how SQL Server governs character data by examining the character set, the Unicode Collation Sequence, and the sort order. To better prepare you for an actual SQL Server installation (and to better prepare you for the exam) we will discuss the file and directory structures used in SQL Server 7.0. Each directory will be discussed, and each significant executable and file extension will be described. Following this discussion, we will forge ahead with the creation of the Windows NT account that will run the SQL Services.

In the exercises, you will perform an installation of SQL Server 7.0 and learn about how SQL Server can be setup using automated methods. We will also discuss the SQL Server Upgrade Wizard, which allows SQL 6.x databases to be upgraded to SQL Server 7.0. We will then look at the results of our work, examining the changes that SQL has made to the operating system.

Finally, we will shift our focus from the server platform. This will entail discussing the client utilities and network library installation procedure on a Windows NT Workstation computer.

How SQL Governs Character Data: the Character Set, Unicode Collation Sequence, and Sort Order

In this section, we will discuss three methods SQL Server uses to govern character-based data and how they affects the SQL Server running environment. We will begin with a semi-single language character set, then move to a brief discussion of the Unicode standard for multi-language computing platforms, and then discuss how SQL will attempt to sort these character sets within the database.

Limitations of the SQL Server Platform when Working with Character Data

There are a few rules you must remember about the character set, sort order and Unicode collation sequence:

What is the Character Set, and How Does It Affect Me?

The character set (sometimes referred to as a code page) is the collection of letters, numbers, and special characters that will define the values used in the char, varchar and text character-based datatypes. No matter what character set is chosen when the server is built, the first 128 of the 256 possible values will be identical from set to set. The characters in positions 129–256 (the extended set) will contain special characters that help further define the written language. Examples of these special characters are accent marks and special digits.

By default, SQL Server uses code page 1252. 1252 is a new character set that was introduced with the advent of Windows when Microsoft finally abandoned the DOS CP 437. This code page also has a few other names, which includes the following: ANSI, Latin 1, and ISO 8859–1. All characters in this code page are 100 percent compatible with the ANSI characters used throughout the Microsoft Windows 9x/NT environment. Unless there is a very specific requirement to obtain characters not shown in this code page, you should allow the SQL Server installation program to use this default character set.

If you have a requirement to store data that contains characters foreign to code page 1252, you may wish to select code page 850. 850 functions as the Multilingual character set. Besides supporting English, this code page includes special characters for most European and South American countries.

Code page 437 (US English) includes special graphics characters which would serve little use in a SQL database. This code page is a left over from the MS-DOS world, and should be left in the past unless there is a significant reason to use this character set. Considering how often you will need a line drawing font (which is the ability to draw graphical boxes and charts using the system font) in a database, there will probably be very few compelling cases to use this code page.

Also new in SQL Server 7.0 is character set support for Asian, Central European, Mediterranean and Middle Eastern languages. These character sets are listed as follows:

If you wish to get an overview of what each of these character sets look like, check out Roman Czyborra’s page at http://czyborra.com/charsets/codepages.html.

When choosing a character set, you must choose carefully. If you make an incorrect selection, the only way to change the character set is to implement the following: backup the databases, change the character set (using the rebuildm utility), then rebuild the databases and reload all existing data using DTS or BCP.

Additionally, when selecting a character set, keep in mind that you cannot load a database from a dump device unless the character set used on the server matches the character set used to create the database device. To better illustrate this idea, let’s look at the following scenario:

A large multi-national bank has SQL Servers in both London and Zurich. When the SQL Server was installed in London, the default code page of 1252 was selected. When the Zurich server was built, however, the staff built the server using character set 850 to anticipate any foreign language requirements that might arise. During the bank’s last merger, the IT management team decided that it would be best to consolidate the SQL Servers at these two facilities into one server, and to locate this server in London. It would be impossible to restore the Zurich database on the London server (using tape backup or a disk dump device) because of the difference in character sets. The only solution in this case is to use the BCP utility or Data Transformation Service to restore the data. If the bank had established a standard for which code page was to be used throughout the organization, a tape with the SQL database on it could be sent to London, restored, and been functional again in a matter of a few hours.

How Come I Can Restore a Database that Used a Different Character Set If Both Servers Use Binary Sort Order?

Binary sort order is the simplest of all the sort orders included with SQL Server. It also has the highest performance. In short, the binary sort order eliminates the process of converting code page ASCII values to their contained values. Instead, the binary sort order will perform the sort simply by referencing the ASCII value. While this performance advantage may be very appealing, the binary sort order can yield some rather odd result sets. Using the Binary Sort order, ZYXVW will sort before abcdefg because the ASCII value of "Z" is less than the ASCII value of "a" in all character sets.

As mentioned earlier, the code page consists of 256 characters. The first 128 are identical across every ISO code page, and the 129–256 positions are used for extended characters. Early in our careers as computer professionals, we learned that computing systems do not really understand the names we call them. Instead, they simply understand the underlying numeric address. No where is this more obvious than on the Internet, where www.microsoft.com translates into 207.46.130.xxx using DNS.

As we might expect, in the world of code pages the same principle holds true. When SQL stores character information, it is not storing the characters themselves, but their numerical equivalents. In code page 1252, if we store the Æ symbol, we are actually referring to ASCII code 248 in the code page. SQL Server does not care what the "address" actually stores. It simply cares that the value you specified is stored in that location.

If we examine who lives at ASCII code 248 in code page 1250 (Central European), we will find it stores a different value altogether in this address space. Instead of the expected Æ symbol, we receive the r character as the returned value. Although this seems to threaten the result sets of a database that uses binary sort order, Microsoft allows this functionality to persist. Perhaps it is with the belief that most organizations do not take advantage of the extended characters in most code pages.

Why Does My Data Have To Be Reloaded If I Change the Character Set?

In order to change the code page, Unicode collation sequence or sort order, you must rebuild the master database using the "rebuildm" utility. This utility will copy a fresh copy of the master database to your server, effectively destroying all databases on the system.

Choosing the Unicode Collation Sequence

In this section, we will discuss a topic closely related to the character set by introducing the Unicode Collation Sequence.

What is Unicode?

Unicode is a standard (ISO/IEC 10646-1:1993) that removes language boundaries by enabling a 16-bit (65,536 character) multilingual character set. Microsoft has recognized the need for multi-language computing platforms. They are able to achieve this with Unicode. If you have a desire to read more about the Unicode standard, you can visit the Unicode web site at http://www.unicode.org.

How Does the Unicode Standard Affect SQL Server 7.0?

SQL 7.0 introduces a few new datatypes for developers to use including the Unicode aware nchar, nvarchar and ntext datatypes. These datatypes use twice the system resources as their non-Unicode aware counterparts, but are able to store data for most languages. If the SQL Server will service multiple languages in the single database, administration and development can get a bit interesting. It can become this way if you need to find a single character set that can store the character information for all languages. Additionally, even if you can find a character set that will accommodate all languages that will be used on the database, you must still account for the various code pages used in each client locale. Instead of this administrative nightmare, Microsoft recommends implementing a new SQL 7.0 installation with the default code page (1252) and using the Unicode equivalent datatypes (nvarchar, nchar and ntext) in any front-end application development projects. While these Unicode-specific datatypes may be expensive in terms of system resources, this expense is quickly recovered. This recovery is due to the fact that extended characters will not need to be converted between each code page.

With changes in character sets and languages, it is possible for the sort order of the data to change. In English we are used to sorting items by numerical values and alphabetical order. But what happens when the alphabet changes? Does "ä" come before "a" in a column that was defined using a Unicode specific datatype when the items are sorted on a client machine in Germany? To answer these questions, SQL Server looks to the Unicode Collation Sequence to address these very issues.

The Unicode Collation Sequence is a sort order for Unicode data but remains separate from the SQL Sort Order. Using the Unicode Collation Sequence, we can apply a language (or regional) variant to the sort order. From there we can begin sorting data appropriately for each individual language or region. SQL Server allows a Unicode Collation Sequence to be applied for any one of the following languages or regional locations:

Like the character set, if you make a mistake or decide to change your strategy after loading your databases, you will need to make some changes. These changes consist of the following: backup the server, change the Unicode Collation Sequence, rebuild the databases on the server, and reload the data using BCP or the Data Transformation Service (DTS).

On a similar note, it is worth mentioning that if you are performing an upgrade to SQL 7.0 from SQL 6.x, you must always select the default Unicode collation. Under SQL Server 7.0, the Master database (which holds SQL configuration information) contains Unicode columns that were not Unicode in SQL Server 6.5. While this may seem a rather minute issue, any deviation from a default Unicode Collation Sequence on a 6.x to 7.0 database upgrade can cause uniqueness constraints to be violated or the upgrade process to fail.

Along with the Unicode Collation Sequence, you may also configure four additional sensitivity properties: Case-Insensitive, Accent-Insensitive, Kana-Insensitive (Asian languages only), or Width-Insensitive (Asian languages only). These properties will help you control how SQL will sort datasets that touch a column defined with a Unicode defined datatype. In a case-insensitive sort, SQL will evaluate "A" and "a" as equal. The other properties function similarly.

Choosing the Appropriate Sort Order

For all non-Unicode data, SQL Server must have a set of rules on how it should sort data when the user performs a query on the database. The sort order is primarily tasked with determining what characters should be considered equal. For example, if SQL is asked to evaluate if the string "Apple" is equal to "apple", it will be up to the sort order to determine the equality of these statements.

The sort order is also responsible for determining the default order in which columns will be presented to the user in a query. The options for the sort order will vary depending on which character set was chosen.

Using Microsoft’s recommended character set, ISO-8859-1 (CP1252) carries the sort order options found in Table 5-1.

Sort Method Equality Test Example Sort Order Example
Dictionary order, case-insensitive (default) A = a, Ä = ä, Å = å, a ¹ à A, a, à, á, â, ä, Ä, Å, å

Because case is not specified, and there is no uppercase preference, these can sort as A, a or a, A

Binary Order Equality based on ASCII Values:

A=65 a=97

Therefore, A ¹ a

Equality based on ASCII Values:

A=65 a=97

Therefore, "A" will appear in the results set before "a"

Dictionary order, case-sensitive A ¹ a, Ä ¹ ä, Å ¹ å,
a
¹ à ¹ á ¹ â ¹ ä ¹ å,
A
¹ Ä ¹ Å
A, a, à, á, â, Ä, ä, Å, å
Dictionary order, case-insensitive, uppercase preference A = a, Ä = ä, Å = å,
a
¹ à ¹ á ¹ â ¹ ä ¹ å,
A
¹ Ä ¹ Å
A, a, à, á, â, Ä, ä, Å, å
Dictionary order, case-insensitive, accent-insensitive A = a = à = á = â = Ä =
ä = Å = å
A, a, à, á, â, Ä, ä, Å, å

Table 1: ISO-8859-1 Sort Order Behavior

In addition, the ISO-8859-1 character set includes support for the following sort orders:

It is especially important to choose the proper sort order, as it can render a front-end application useless if the incorrect order was chosen. Consider the following example:

Using Visual Basic 5.0, a member of the applications development team authored an application that uses ODBCDirect to connect to the SQL 7.0 server. SQL Server was installed using a dictionary sort, case-sensitive method. The front end expects the server to be running a case insensitive sort method, and members of the user community are complaining that the result sets are not returning expected values. This seemingly small mistake may cause every select, insert, update, and delete statement to fail. In a case-sensitive sort method, these two statements will not produce the same result set:

SELECT * FROM Customers WHERE CompanyName="Perot Systems Corp."

SELECT * FROM customers WHERE companyname="perot systems corp."

Notice that the column name and search criteria for the "where" clause is different. In a case-insensitive search, these two queries would evaluate identically. In a case-sensitive search, one (or both) of these queries will fail depending on how the column names and the data were originally capitalized.

Now that we have examined how SQL Server handles character data, we can begin examining the installation process and how the installation process will modify its installation environment.

The Installation Process

In this section, we will examine the SQL installation process in detail. We will begin by creating an NT Domain account and discussing the merits of using the domain account over the LocalSystem account or machine account types. We will then walk through the SQL Server setup application, discussing the various installation options as we progress. We will look at the SQL Upgrade Wizard to help you upgrade those SQL 6.x servers to SQL 7.0. We will then discuss two procedures Microsoft has made available to us to perform an unattended installation of SQL Server 7.0.

Once SQL Server is installed, we can explore the results of the installation process by discussing Start Menu entries, SQL Server Services, and Directory Structures. We will then perform another installation, installing the client utilities on a Windows 95 workstation. This installation will allow us to perform administration without being physically at the server. At the end of this section, we will discuss troubleshooting a failed SQL Server 6.5 upgrade, as well as client connectivity issues.

Establishing a Windows NT User Account

When using SQL Server on a Windows NT Workstation or Server, you may wish to create a Windows NT service account for use by the SQL Server Services. As we know from our experience with Windows NT Server, services can be assigned a LocalSystem account, a machine account, or a domain account. When using SQL Server, there are a few restrictions that may prevent you from choosing the LocalSystem account. If this server needs to utilize any of the following functionality, then you must create a Windows NT Domain account:

We can create a Windows NT account using the User Manager GUI tool, or by using the Windows NT command prompt. Let’s begin by examining the User Manager method for a machine account:

Launch User Manager by using its entry in the Administrative Tools start menu entry, or by typing \\server_name in the Run dialog box. Once the User Manager administrative tool comes up, select the New User menu entry. Select an appropriate name for the SQL Service account (follow the naming standard for service accounts in your organization) and select a strong password. Strong passwords should always include numbers, mixed case letters, and symbols (such as !@$%^&*). Make sure that the "User must change password at next logon" checkbox is not checked. Also make sure that the "User cannot change password" and "Password never expires" checkboxes are checked. Add this new user to the local Administrators group. In most cases, you should not have to create a home directory for the user. Press "OK" on the User Properties sheet to add the user account to the server’s local SAM.

Depending on the security policy of your organization, you may wish to create additional accounts for each of the SQL Services or servers running SQL Server. If you choose to create service accounts for each server, you can restrict the machine that the account is allowed to login when creating the account. This will prevent this administrative account from being used elsewhere in the enterprise. If the server will, however, participate in replication, Microsoft recommends using the same service account for the publishers and all subscribers.

If you have a large number of service accounts in the domain to create, or wish to create a script to automate the process, you can use the "net user" command to create the user on the command line. The following command will create an account in the domain named "svcSQL" with a password of 4y@d@2Y@D@!. The account will never expire, and the user is not allowed to change the password. Additionally, the account is restricted to log on to two SQL Servers named SQLSRV001 and SQLSRV002.

NET USER svcSQL 4y@d@2Y@D@! /ADD /expires:never /times:all /passwordchg:no /fullname:"SQL Server Service Account" /comment:"Service Account for MSSQLServer Servers" /workstations:"SQLSRV001,SQLSRV002" /DOMAIN

This syntax will create the account and set most of the options to enable it to be assigned to a service. One issue is that two items are missing. An account that will be assigned to a service must have Administrative privileges. For this restriction, we can use another command line utility: "NET GROUP". If we wish to add the "svcSQL" account we just created to the "Domain Admins" group (which should be added by default to the Administrators group of each member server) we can use the following syntax:

NET GROUP "Domain Admins" svcSQL /ADD /DOMAIN

The last item to be done to create this account would be to set the account property "Password Never Expires" to true. Unfortunately, when Microsoft wrote the NET USER command, they did not include the "Password Never Expires" option. To complete this, you must actually use the GUI tool! Luckily, if you have created a number of accounts, you can use the multiple select feature in User Manager for Domains and set all of the accounts at once.

If you wish to refine the administrative privileges a bit more, a Global group can be created in the domain called SQLServerAdmins with all administrators added to this group. Following this creation, the SQLServerAdmins group should be added to the local "Administrators" group of each SQL Server. This process will help delegate the administrative privileges granted to your database administrators.

The Setup Program on Windows NT 4.0

Microsoft has taken great steps to ensure that the installation of SQL Server is relatively easy to perform on any system. On the CD (or in the network share where the CD is located) you will find an autorun.exe file. This will bring up the main installation menu for SQL Server 7.0. From this menu you perform the following tasks: install prerequisites (like SP3 for NT), install supporting software (IE 4.01), install the main SQL Server application, and install additional SQL Server components (English Query and OLAP). Additionally, you can browse the books online to answer your pre-installation questions.

Before beginning the actual install of the SQL Server core components, you will want to make sure you have IE 4.01, Windows NT Service Pack 3, and the SQL Mini-Service pack installed before continuing. If you are running the Enterprise Edition of Windows NT 4.0, you will also want to assure that the Clustering hotfix is applied. Additionally, if you plan to upgrade a Microsoft SQL Server 6.5 installation to SQL Server 7.0, Microsoft has included the required Service Pack 4 for SQL on the installation CD.

If the setup routine detects an installation of SQL Server 6.x on the system, it will display the dialog box shown in Figure 5-1.

Fig5-1.gif (4468 bytes)

Figure 1: If SQL Server 6.x is detected, the SQL 7.0 installation will automatically prompt you to convert the data using the SQL Upgrade Wizard. This screen will not be shown unless there is already a SQL Server installation on the system.

If you elect to run the SQL Upgrade Wizard, the wizard will launch immediately following the Server installation. You can always run the wizard after installation.

Next, you will be presented with choosing the installation type. In most environments, you will want to choose a Custom setup, so you can specifically tailor the installation to your enterprise. You may also configure the installation and data paths using the dialog box shown in Figure 5-2.

Fig5-2.gif (6148 bytes)

Figure 2: The installation setup type dialog box. Select the installation type and setup path

Once you have selected the program and data file locations, you can click "Next". If you chose the "Custom" installation method, the screen shown in Figure 5-3 will be displayed.

Fig5-3.gif (8204 bytes)

Figure 3: The Select Components Dialog Box

In the dialog box shown in Figure 5-3, you can choose which SQL Server components you wish to install. Table 5-2 lists the available components and their functions. If you wish to enable the Full-Text Support service or Development tools, you can install them here.

Component Sub-Component Function
Server Components SQL Server The core SQL Server executables.
Server Components Upgrade Tools Tools to help migrate from SQL Server 6.x to SQL Server 7.0.
Server Components Replication Support Installs replication functionality.
Server Components Full-Text Service Extends SQL’s character-based column searches beyond LIKE and equality operators.
Management Tools Enterprise Manager Installs the Microsoft Management Console and snap in for SQL Server administration.
Management Tools Profiler Traces and records database activity.
Management Tools Query Analyzer ISQL Utility to enter and evaluate TSQL statements.
Management Tools DTC Client Support Extends database transactions across multiple servers.
Management Tools Replication Conflict Resolution Displays and helps resolve replication conflicts.
Client Connectivity None Client Server communication components. Includes native support for ODBC, OLE-DB and SQL-DMO.
Books Online Books Online on disk Installs the Books Online reference tool on the local hard drive.
Books Online Books Online on CD Installs the Books Online reference tool to run from the CD.
Development Tools Headers and Libraries Installs headers and libraries for the C++ development environment.
Development Tools Virtual Device Interface Installs the DLLs necessary to backup and restore from a device.

Table 2: Custom Setup Component Installation Options

You will notice that the SQL Server installation includes installation of the Microsoft Management Console (MMC). MMC is Microsoft’s new strategy for a universal administration tool. The MMC is included in most new Microsoft products including IIS4, SQL 7.0, Exchange 5.5, and most notably, the next version of Windows NT, to be called Windows 2000.

Once you have tailored the SQL Server installation to fit the needs of your enterprise, you can press "Next" to continue with the installation.

We will now configure the character set, sort order, and Unicode collation sequence for this server (see Figure 5-4). Please remember that these settings cannot be changed without rebuilding the Master database, which will cause you to lose all databases on the server. Therefore, based on this knowledge, you should choose carefully! In almost all cases, the default selections should work well for any North American or Western European countries.

Fig5-4.gif (6306 bytes)

Figure 4: The Character Set/Sort Order/Unicode Collation Dialog Box

Once you have made your selections for how SQL will handle character data, you can define the network libraries that SQL will use to communicate with the client application. For a view of the network libraries dialog box, see Figure 5-5.

Fig5-5.gif (6061 bytes)

Figure 5: The Network Libraries Dialog Box

If you wish to encrypt data between client and server, you will need to ensure that the Multi-Protocol encryption checkbox is selected. This setting will only enable the server to have encrypted conversations with the client. In order to actually encrypt the data, you must modify the client’s registry, as mentioned in Chapter 4.

Last, you will configure which account you will assign to each service. You can either allow all services to be run from a single account, or assign specific accounts to each service that SQL will install. These changes are made in the following dialog box shown in Figure 5-6.

Fig5-6.gif (6276 bytes)

Figure 6: The SQL Services Configuration dialog box

Notice in the dialog box shown in Figure 5-6 that you can allow each service to be started automatically by selecting the Auto Start Service checkbox.

If you did not add the service account login to the administrators group, the dialog box shown in Figure 5-7 will be displayed to warn you of the mistake.

Fig5-7.gif (1863 bytes)

Figure 7: This dialog box will be displayed if the service account does not belong to the administrators group on the server

Additionally, the error shown in Figure 5-8 will occur if the password is incorrect for any of the service accounts.

Fig5-8.gif (2047 bytes)

Figure 8: An incorrect password was entered for the service account

Once SQL Server has verified that the service accounts are functioning properly, the setup routine will present the screen shown in Figure 5-9. This screen indicates that you can go grab a soft drink and relax for a few minutes.

Fig5-9.gif (6419 bytes)

Figure 9: The SQL Setup routine is ready to begin the copy process

Once SQL Server has completed copying all components, it will verify the installation by starting the SQL Server and performing a few configuration procedures. If you receive the screen shown in Figure 5-10, you have successfully installed SQL Server 7.0.

Fig5-10.gif (5627 bytes)

Figure 10: SQL Server 7.0 has successfully been installed on your system

If you elected to upgrade a SQL 6.x database, then the setup installation will automatically launch the SQL Server Upgrade Wizard. We will discuss usage of the SQL Server Upgrade Wizard in the next section.

Exercise 5-1: Installing SQL Server

  1. Using the procedure learned in this section, perform a custom installation of SQL Server 7.0 on a Windows NT system.
  2. For consistency, leave all component selections at their default values.
  3. Make sure you select the Multi_Protocol Encryption checkbox when configuring the network libraries.

Upgrade from a SQL Server 6.x Database

Using the SQL Server Upgrade Wizard, we can transform existing SQL 6.x databases into the correct schema and format for a SQL Server 7.0 installation. This utility may be launched in two ways: from the SQL Server 7.0 setup routine, or from the Start Menu by selecting "SQL Server Upgrade Wizard" in the SQL Server – Switch menu entry.

Before beginning the SQL Server Upgrade Wizard process, please verify that the SQL 6.5 Server is running Service Pack 4 or greater. Also verify that the TempDB is at least 10MB to accommodate the temporary tables created by the wizard. Additionally, you will want to assure that the database server (or servers) has been fully backed up before continuing. If this is a production database upgrade, you will want to inform the users of the system that the system will be unavailable during the time of the upgrade.

If you must expand the TempDB, you can use the following syntax in ISQL/W:

DISK INIT name = ‘tempdb1’,physname = ‘c:\mssql\data\tempdb1.DAT’,vdevno = 100, size = 12800

GO

ALTER DATABASE TempDB ON tempdb1 = 25

This procedure will first create a new disk device using 25MB of disk space. Next, we will alter the existing TempDB to use the new device and size it to 25MB. Do not be concerned if you are unfamiliar with the syntax of the above command. You will learn all about TSQL command syntax for database manipulation as we progress throughout this book. In fact, one of the key features of the SQL Server 7.0 release is the automatic allocation of disk space to databases. This feature virtually eliminates resizing databases as they near capacity. Now that we have the prerequisites out of the way, we can begin upgrading our SQL 6.5 to the more robust 7.0 platform.

For this example, we will upgrade only the Customer database of a fictitious order entry system to SQL 7.0. Let’s begin by launching the SQL Server Upgrade Wizard from the Start Menu. The system will respond with the dialog box shown in Figure 5-11.

Fig5-11.gif (5361 bytes)

Figure 11: The SQL Server Upgrade Wizard Initialization Screen

After initialization, we can choose how the data will be transferred between the servers: either via network or using a tape device (sneaker net). Even if you choose to use the tape method, named pipes must be installed and configured to use the default pipe: \\.\pipe\sql\query. The dialog box for configuring these options can be shown in Figure 5-12.

Fig5-12.gif (7087 bytes)

Figure 12: Data and Object Transfer dialog box

We have discussed several times how the SQL Upgrade Wizard can be configured to perform a side-by-side or computer-to-computer upgrade. In the dialog shown in Figure 5-13, we can specify the source and destination system names.

Fig5-13.gif (6138 bytes)

Figure 13: The dialog box that specifies a computer-to-computer or side-by-side upgrade

To perform a side-by-side upgrade, both source and destination names must match. If the server to be upgraded participates in replication, you must perform a side-by-side upgrade. You will start the enterprise migration with the server that serves as the distributor. For a computer-to-computer upgrade to be completed successfully, both computers must be in the same domain.

Once the choice has been made to use a side-by-side or computer-to-computer upgrade method, you can continue to the next dialog box (shown in Figure 5-14), which will allow us to select the scripting code page. In almost all cases, you will select the default. Only change this selection if you know that the master database on the SQL 6.5 Server contains a different code page than the one selected.

Fig5-14.gif (4922 bytes)

Figure 14: Selecting the master database code page

As shown in Figure 5-15, you will determine which databases will be upgraded.

Fig5-15.gif (6788 bytes)

Figure 15: Determining which databases will be upgraded to SQL Server 7.0

Once you have selected the database you wish to transfer, you can specify where the data will go once it gets to the SQL 7.0 Server. By default, SQL 7.0 will automatically create a new database on the server. You can, however, exercise a bit more control over the system, and force it to use a specific database. Another option would be to use a script to create the new database (as shown in Figure 5-16).

Fig5-16.gif (6046 bytes)

Figure 16: Determining how the existing 6.x database will be created in SQL Server 7.0

As shown in Figure 5-17, you must specify which objects to transfer into the new SQL 7.0 database.

Fig5-17.gif (6119 bytes)

Figure 17: Determining which objects will be transferred, and how to handle ANSI nulls and quoted identifiers

When you have completed these steps, SQL will present you with a summary screen (shown in Figure 5-18), which will allow you to review your work and begin the process.

Fig5-18.gif (7227 bytes)

Figure 18: The SQL Server Upgrade Wizard summary screen

Once you press "Finish", you will be presented with a screen similar to the one shown in Figure 5-19.

Fig5-19.gif (5705 bytes)

Figure 19: The upgrade process begins

SQL Server will stop and restart the SQL 6.x and 7.0 several times during this period, and will report the status of the tasks it is performing as it progresses. Any mistakes in configuration or problems with the upgrade will be reported in this window. When the upgrade is complete, you will receive an "upgrade successful" status message.

Perform an Unattended Setup

SQL Server 7.0 allows the option of performing an unattended server setup. This process is much like the one that can be performed using Windows 95 and Microsoft Office. Before the implementation of SQL 7.0, a large company might have 10 to 20 separate SQL 6.5 Servers. Therefore, installing each of them manually was not a large problem. Since SQL Server 7.0 can also now be installed on your client workstations, a copy may have many hundreds of installations. These workstations would normally be laptop computers that are disconnected from the network for most of the working day. While an unattended setup script can make the installation of SQL Server on your 10 servers an easier process, it is quite a necessary feature to make the installation of SQL Server on 1,000 laptops possible.

When performing an unattended installation of SQL Server, you will not be prompted for information such as the character set and account for the SQL Service. This information will be retrieved from the installation file. SQL Server 7.0 uses a completely new installation program than SQL 6.5. So if you created ini files for automated installation of SQL 6.5, you will need to re-create them in SQL 7.0.

Creating an Unattended Installation Script

To create an Unattended Installation Script, you must start the SQL Setup program with the option k=Rc and proceed through the installation process manually as found in following list:

Running an Unattended Installation

Now that you have created your setup.iss file you are ready to install SQL Server using the Unattended Insulation script. Running the installation script is as easy as creating it and is completed as follows:

Be sure to note the following:

Now that we have installed SQL Server, let’s take a look around the system to see what SQL has changed in terms of the following: Start Menu, Services that are now running, and the file structure on our system.

Start Menu Entries

After installation of SQL Server 7.0, the entries in Table 5-3 will be visible under the Microsoft SQL Server 7.0 menu entry.

Start Menu Entry Function
Books Online SQL Server Documentation
Client Network Utility Configures the network library on the client computer. The server and client may be the same machine.
Enterprise Manager The Microsoft Management Console with a SQL 7.0 administration snap-in.
MSDTC Administrative Console Allows configuration, administration and monitoring of the Distributed Transaction Coordinator.
Performance Monitor Launches a SQL Server specific view of system performance.
Profiler Allows the administrator/developer to collect extended information about the SQL Server, including deadlocking, server connects and disconnects login attempts, and other vital information.
Query Analyzer Environment for running interactive TSQL statements, monitoring performance, index analysis, and statistics on specific queries. An indispensable tool for ad hoc queries and performance analysis.
Server Network Utility Configures the server’s network libraries.
Service Manager Used to start and stop SQL related services without navigating to the control panel. Can also run in the taskbar for quick access.
Uninstall SQL Server Removes the SQL Server installation.

Table 3: Start Menu Entries for a full SQL Server installation.

SQL Server Services

The SQL Server Installation installs three services on your Windows NT system:

Using the Control Panel, you can do the following: configure these services to run automatically at startup, modify which account they are associated with, and start and stop these services. For starting, stopping, and pausing the services only, you can use the Service Manager utility on the start menu or the task bar.

File and Directory Structures

In this section, we will examine the directory structure and files that are installed by the SQL Server 7.0 Setup routine. The Enterprise Manager allows almost all functionality to be performed in a GUI environment. On occasion, there may be times where you will want to script a process or access functionality that is not available using the management console.

The Microsoft setup application will install the following directories under the \MSSQL7 directory (or the directory of your choice):

Table 5-4 lists a few of the SQL executables that have well-documented syntax in SQL Server Books Online.

   
bcp.exe  
console.exe  
dtsrun.exe  
dtswiz.exe  
isql.exe  
isqlw.exe  
makepipe.exe  
nsqlprep.exe Precompiler that creates an embedded SQL
odbcping.exe Tests ODBC connection for database
osql.exe TSQL  
readpipe.exe Used in conjunction with makepipe to test
rebuildm.exe  
regrebld.exe Backup and restore utility for SQL Server
replmerg.exe  
snapshot.exe  
sqlagent.exe  
sqlalrtr.exe  
sqldiag.exe  
sqlftwiz.exe  
sqlmaint.exe  
sqlmangr.exe  
sqlservr.exe  
sqltrace.exe  
svrnetcn.exe  
textcopy.exe  
vswitch.exe  
wiztrace.exe  
Books  
Data  
DevTools  
FTData  
HTML  
Install SQL scripts and result sets performed during installation
Jobs  
Log  
ReplData  
Upgrade  

Table 4: SQL Executables Documented in SQL Server Books Online

Using the executables in the Binn directory, many things can be accomplished. Powerful command-line scripts can be created to perform maintenance, administration, replication or data transformation using scheduled tasks (such as the "AT" command in NT).

Installing Client Utilities and Configuring the Client Network

Last up in the installation process, we will install the client utilities on a Windows NT Workstation computer. You will want to install the client utilities on any computer that will connect to the SQL Server. These computers will need to include the following: a version of the SQL Server ODBC drivers, network library support for all libraries, and optionally, the administrative console for SQL Server 7.0. Let’s have a look at what needs to be performed to create a new administrative console:

Begin by running the autorun.exe from the CD or network share. You will still need to comply with the hardware and software requirements for SQL Server 7.0, including all Service Packs and hotfixes. Once your system is compliant, you can begin the client installation process. Start with a custom desktop installation (see Figure 5-20).

Fig5-20.gif (6148 bytes)

Figure 20: Choose a custom installation for all client installations

Once the installation path and setup type have been chosen, pare down the server installation to just the Client Connectivity for general users. You can also pare down to just the Client Connectivity, Books Online and Management Tools for developers and administrative users as well (see Figure 5-21).

Fig5-21.gif (8171 bytes)

Figure 21: Configuring the SQL Server installation for Administrative tools and Network Library Support

Once you have completed the component selection process, the system will display the dialog shown in Figure 5-22.

Fig5-22.gif (5627 bytes)

Figure 22: A successful client installation

Exercise 5-2: Using the Client Network Utility and Enabling Multiprotocol Encryption

In this exercise, we will use the Client Network Utility to configure the client to connect to a SQL Server running its SQL Server on TCP/IP port 1433. We will then add the Multiprotocol library and enable Mutliprotocol encryption.

  1. Begin by going to the Control Panel and selecting the ODBC applet. The screen shown in Figure 5-23 will appear.
  2. Fig5-23.gif (8603 bytes)

    Figure 23: The ODBC Data Source Administrator Dialog

  3. Next add a new datasource of type SQL Server as shown in Figure 5-24.
  4. Fig5-24.gif (12226 bytes)

    Figure 24: Adding a new SQL Server datasource

  5. Select a name, description, and the NetBIOS name of the running SQL 7.0 Server as shown in Figure 5-25.
  6. Fig5-25.gif (10834 bytes)

    Figure 25: Configuring a new SQL datasource

  7. In the dialog box shown in Figure 5-26, configure the ODBC datasource to use standard SQL security. Use the "SA" account.
  8. Click the Client Configuration button (see Figure 5-26).

Fig5-26.gif (11376 bytes)

Figure 26: Specifying security and network library settings for the ODBC connection

  1. To demonstrate a failing connection, we will set the TCP/IP port setting to 9999 (see Figure 5-27). If you used port 9999 when installing your SQL Server, choose a different port number for this exercise.
  2. Fig5-27.gif (5501 bytes)

    Figure 27: Entering a non-matching TCP/IP port number

  3. SQL Server will return the error message shown in Figure 5-28 after you try to select "Next".
  4. Fig5-28.gif (3810 bytes)

    Figure 28: ODBC error message due to non-matching TCP/IP port numbers

  5. Using the Client Configuration button, reconfigure the TCP/IP settings to match your SQL Server’s TCP/IP settings. The default is port 1433. Once this change is made, you should see the screen shown in Figure 5-29.
  6. Fig5-29.gif (12292 bytes)

    Figure 29: You can continue configuring the ODBC connection once connectivity is established between client and server

  7. Using the ODBC driver, you can modify the result sets returned by SQL Server to a more appropriate format for your locale. This is done in the dialog box shown in Figure 5-30.
  8. Fig5-30.gif (11298 bytes)

    Figure 30: You can change the format of result sets to match your locale using the ODBC Administrator

  9. Finally, we can test our connection to assure we have good communication with the SQL server. Click the Test Data Source button in the dialog box shown in Figure 5-31 to perform this test.
  10. Fig5-31.gif (6548 bytes)

    Figure 31: Testing the ODBC data source

  11. If all goes well, you should receive the confirmation shown in Figure 5-32.
  12. Fig5-32.gif (5028 bytes)

    Figure 32: Confirmation that the ODBC driver has connectivity to the SQL Server

  13. On your own, try reconfiguring your client to use multiprotocol, and test the data source.

If you successfully performed the conversion of the TCP/IP connection to multiprotocol, you can then modify the registry to include multiprotocol encryption:

  1. In the run dialog box, type REGEDT32. This will bring up the registry editor.
  2. Navigate to the following location:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client

  1. Add a new key called RPCNetLib. Navigate to the new key, and add a new REG_SZ (String) datatype. This new value should be named "Security", and have its value set to "Encrypt". This will enable end-to-end multiprotocol encryption.

From the Classroom

SQL Security Begins With Your SQL Service Account

SQL Server Security begins with the account that you use to run the SQL Server, SQL Agent and MSDTC Services. If these accounts are compromised all of the data on the server can be viewed and manipulated. Here are some tips for securing these accounts.

Should Your SQL Server Be A Domain Controller?

If you have a small business or many small sites with only one or two NT servers then it is best to have your SQL Servers installed as Domain Controllers.

If you have a larger business and already have two or more Domain Controller centrally and one in each region I generally recommend setting up SQL Server as a Member Server. The primary reason is that to be a SQL Server Administrator (not a dbo or sa) you need to be a machine administrator. The only way to be a machine administrator on a Domain Controller is to be a Domain Administrator.

To assign a Domain Account Administrator privilege on a member server you start User Manager for Domains by typing usrmgr \\servername, then simply add your SQL Service Account to the Administrators Group.

—David Smith, MCSE + Internet

Troubleshooting

If your installation fails but SQL Server does not display a message such as "Not Enough Disk Space" you will need to dig a little further into the problem. Here is a list of places to look for assistance with solving such an installation problem with SQL.

The next step in troubleshooting any installation problems is to connect to Microsoft’s support web site at http://support.microsoft.com/support/ or use Microsoft TechNet. Both of these tools are updated frequently and contain information on trouble shooting, new Service Packs (for Windows, Windows NT and SQL Server) and even trouble shooting wizards that work with you step-by-step to solve your problem.

Return Error Codes from Unattended Installation

When you run the unattended installation, an error code may be returned. Table 5-4 is a list of possible error code numbers and their explanations.

Error Code Description
0 Success
-1 General error
-2 Invalid mode
-3 Required data not shown in the Setup.iss file
-4 Not enough memory available
-5 File does not exist
-6 Cannot write to the response file
-7 Unable to write to the log file
-8 Invalid path to the InstallShield Silent response file
-9 Not a valid list type (string or number)
-10 Data type is invalid
-11 Unknown error during setup
-12 Dialogs are out of order
-51 Cannot create the specified folder
-52 Cannot access the specified file or folder
-53 Invalid option selected

Table 5: Error codes from an unattended installation

Certification Summary

In this chapter we discussed SQL Server’s character set, Unicode collation Sequence, and the Appropriate Sort Order. It is very important in SQL that these all be set correctly. Not only for your server, but for your entire organization. If different servers have different settings, then transferring data between servers may be much more difficult than it needs to be.

The chapter also discussed in detail the installation process. This included the NT User account used to control security and access to your data. Exercise 5-1 guided you through the installation of SQL Server on your system. It is very important that you take the time to go through this exercise so you can work with SQL Server before writing your exam.

Continuing with the installation process, this chapter covered the issues regarding moving from SQL 6.5 to 7.0. In this section, the issue of the importance of ensuring your system met the requirements of SQL 7.0, including memory, disk space, and appropriate service packs.

The section on the installation of SQL Server concluded with a section on Unattended Setup. Unlike previous versions of SQL Server, creating installation scripts for SQL 7.0 is very simple, and can be completed by recording the options that you select.

Troubleshooting a failed installation that isn't accompanied by a helpful error message can require digging further into the cause, and seeking assistance from outside resources. This chapter ends with a list of resources, as well as a description of error code numbers that may be returned from an unattended installation.

Two-Minute Drill