Table of Contents

Chapter 4

Planning for Installation

Certification Objectives *

Hardware Requirements *

From the Classroom *

Testing Your Upgrade Plan *

Software Requirements *

Automated Methods for Installing SQL Server 7.0 *

Pre-Installation Needs Analysis *

Plan An Upgrade from a Previous Version of SQL Server *

Upgrade Requirements *

The upgrade process: Side-by-Side or Computer-to-Computer? *

Formulating the Upgrade Plan *

Plan the Migration of Data from Other Data Sources *

Introducing Data Transformation Services *

What is a Network Library? *

Named Pipes *

TCP/IP Sockets *

Multiprotocol *

Other Protocols *

Security Considerations for a SQL Server 7.0 Implementation *

Why is Security Important? We Trust Our Employees Here. *

Post Installation Configuration for SQL Server 7.0 *

How Users Access Data in SQL Server *

 

Certification Objectives

As with any major project, proper planning is often the cornerstone for success. This chapter will provide you with a brief overview of the planning involved in implementing a SQL Server 7.0 database platform in an enterprise environment. The chapter will present a large amount of data in overview fashion, which will enable you to formulate a plan for installation. The actual steps to carry out these plans will be discussed throughout the book, so do not be alarmed if there are terms and concepts that are not yet familiar.

We will begin with a discussion of the real-world uses of an SQL Server platform, followed by a discussion of the minimum hardware and software requirements for installation of SQL Server 7.0. Next, we will discuss the various roles an SQL Server can play (for example a Data Warehousing/Decision Support System or a Transaction Processing Server). With these basic items out of the way, we can forge forward into a discussion on the various Network Libraries available in SQL Server. We will also explain how they should be used in several different scenarios. We will then discuss the new dynamic performance tuning features in SQL Server 7.0, as well as discuss memory and device management in this new release. Finally, we will conclude this section with a brief overview of data conversion to the SQL Server 7.0 database platform.

Uses of SQL Server

SQL 7.0 is most often used for:

As our society becomes more and more dependent on data, it is rather natural to have an increased reliance on a high-performance database management system. The SQL Server platform has proven to be a powerful, reliable, and easily extensible platform for both online transaction processing (OLTP) and data warehousing/decision support system usage. Additionally, SQL Server is quickly becoming the backend database of choice for many web applications such as electronic commerce. Most of us are rather familiar with the OLTP database model, which is used for live, constantly changing data. These databases are used for many functions, which include the following: storing defect reports in the development cycle of a product, corporate human resource information, electronic commerce, and even small development server databases.

In the data warehousing/decision support system model, the database is used for storage of static (historical) data. From this data, extremely complex queries can be formulated. Finding patterns and difficult to discover relationships among the data is also possible For example, using a data warehouse of the buying patterns of a lumber yard, one could roll up data from three disparate legacy systems into SQL Server 7.0. This information could then allow the corporate buying department to make extremely informed decisions about consumer buying patterns at each of the lumber yards in the region. Microsoft has taken the usability of the data warehouse model to another level by adding the Microsoft English Query interface. This interface allows users to enter queries such as "What is the relationship between growth in nail sales and plywood sales in Miami, FL during the last three hurricanes in that region?" without entering a single line of Transactional SQL code. As well as the data warehousing features found in SQL Server 7.0, this new release also features a 100 percent compatible code base across all platforms from Windows 95 to Windows NT Server Enterprise Edition. This scalability allows SQL Server to be used for small single-user databases, development databases on laptops, and departmental databases. It will also allow SQL Server to be used with large databases such as TerraServer (http://www.terraserver.microsoft.com), which is a one terabyte database that includes geographic image data collected from US and Russian satellites. An Enterprise environment allows a programmer to develop his VB or VC++ front end on a Windows 95 laptop. Whether it sits on a laptop or an enterprise-class server, the code will be 100 percent compatible with all versions of the database.

With the popularity of Microsoft Internet Information Server webserver platform, the popularity of SQL Server as a backend database for HTML frontend databases will increase. The single sign-on capabilities and superior integration with the Windows NT operating system lends to the strength of a Microsoft-centric application server environment. Additionally, planning frontend development to incorporate the use of Microsoft Transaction Server (for guaranteed transaction completion) and SiteServer will allow electronic commerce sites to be on the Internet (or intranet) after a very short development cycle.

Installing and Configuring SQL Server in a Network Environment

In this section, we will discuss the following topics that relate to planning the installation of Microsoft SQL Server 7.0 in an enterprise environment:

Hardware Requirements

In order to perform a base installation of Microsoft SQL Server 7.0, Microsoft requires the following minimum hardware configuration for either SQL Server or SQL Client Tools installation:

These are the minimum requirements stated by Microsoft. In general, however, you will want to use a Pentium 200 or higher with 64MB of RAM or more, and allow plenty of space for the databases you will create. As with all Microsoft BackOffice products, the more system resources you can afford, the better the overall server performance. Also keep in mind that if the server will be sharing functionality with another BackOffice component (such as IIS 4.0), you will want to increase the amount of memory) to get optimal performance from the system. You might also potentially upgrade to a faster processor.

These are the minimum requirements as stated by Microsoft. Some consideration should be given to the real requirements for the system to run well.

From the Classroom

Testing Your Upgrade Plan

It is very important that you plan and test the upgrade of your SQL Servers before making changes to production servers. All precautions must be made to ensure that SQL Server is available to your users when required.

If your existing SQL 6.x Servers have NT Server 3.5x you will be upgrading both the operating system and SQL Server. You should consider the benefits of re-installing the operating system and SQL Server from scratch instead of upgrading. While it is initially easier to upgrade the software than it is to perform a fresh install, computer software generally runs better and is easier to support when installed from scratch.

By David Smith, MCSE

Software Requirements

In addition to the hardware requirements above, the installation of SQL Server requires the following software configuration before starting the SQL Server installation:

Exam Watch: Microsoft exams commonly include questions regarding the minimum hardware requirements.

Automated Methods for Installing SQL Server 7.0

It is possible to install Microsoft SQL Server 7.0 using an unattended, automated method of delivery. For System Management Server (SMS) users, Microsoft has included several .PDF files to save you the step of creating these files manually. (Microsoft SMS is a server product that assists administrations by providing hardware and software inventory, software distribution and helpdesk tools such as remote control.) If you have a significant number of SQL servers to be upgraded (or installed) it may be well worth the time invested to create the files necessary for an automated installation.

Pre-Installation Needs Analysis

Prior to installing SQL Server, several decisions should be made to assure the server is being used efficiently and appropriately:

Based on the answers to these questions, a preliminary plan for building a SQL Server infrastructure can be formulated. Some important things to remember about an SQL server installation:

Plan An Upgrade from a Previous Version of SQL Server

In our pre-installation survey, we identified several issues that can change the course of the SQL Server 7.0 installation process. If the SQL Server is a brand-new installation with no data migration requirements, the database administrator’s job becomes a matter of simply creating the database and turning it over to the developers for further definition. In many cases, however, there will be a significant amount of existing data that will need to be transferred into the new server. In this section, we will discuss the various ways to get existing native SQL data into a SQL Server 7.0 installation.

Upgrade Requirements

In order to upgrade an SQL 6.x database to SQL 7.0, the conditions in Table 4-1 must be met.

  Import Computer (SQL 7.0) Export Computer (SQL 6.x)
Operating System NT Server 4.0 with SP3 or

NT Workstation with SP3

SQL Server 6.5 with SP3 or

SQL Server 6.0 with SP3

Disk Space SQL 7.0 disk space requirements, plus 1.5 @ts the size of the SQL 6.x database. No specific requirements
Network Protocols Must be using named pipes, set to listen on \\.\pipe\sql\query Must be using named pipes, set to listen on \\.\pipe\sql\query

Table 1: SQL UpGrade Conditions

Please note that in the Table 4-1, there is no mention of upgrading a SQL 4.2 server (nor its data) to SQL Server 7.0. Currently, Microsoft does not offer a direct upgrade path to SQL 7.0 for customers using SQL Server 4.2. If you wish to upgrade a SQL 4.2 database server to SQL 7.0, you must first upgrade this server to SQL Server 6.5 and then from there up to SQL 7.0.

The upgrade process: Side-by-Side or Computer-to-Computer?

Microsoft offers customers wishing to upgrade from SQL 6.x to SQL 7.0 two methods to perform the upgrade: side-by-side or computer-to-computer. When you perform a side-by-side installation, you actually maintain two versions of SQL Server on your system. These installations may be versions SQL Server 6.5 and 7.0, or 6.0 and 7.0. SQL Server 7.0 is not designed to allow simultaneous operation of SQL Server 7.0 and an older version of the product, therefore a "switch" utility is provided to switch between the two running versions. If you are upgrading your hardware along with the installation of SQL Server 7.0, you can also perform a computer-to-computer upgrade. This upgrade allows you to specify a source and a destination system for upgrading. Each upgrade method has advantages which should be evaluated for use on your environment. Examine Table 4-2 to determine the differences between each method.

  Side-by-Side Computer-to-Computer
Number of Servers Required 1 2
Method can be used on servers involved in data replication? Yes No. Only side-by-side upgrades are supported for servers involved with replication.
Advantages Allows quick and easy data migration and server upgrade. Allows SQL Server 6.5 server to remain unmodified after upgrade.
Disadvantages Requires significant amount of disk space for large databases. Stops MSSQL Service on SQL Server 6.5 database server. Both servers must be in the same domain. Requires two servers. Stops MSSQL Service on SQL Server 6.5 database server.

Table 2: Differences in Upgrade Methods

For most situations, a side-by-side upgrade will be ideal. If the plan is to migrate the data to a new hardware platform, the computer-to-computer upgrade method will provide the most efficient method of transfer. If the server to be upgraded participates in any replication strategy, you cannot perform a computer-to-computer upgrade. Additionally, if the server does participate in replication, the Distributor should be upgraded first, as SQL 7.0 was designed to allow SQL 6.5 publishers and subscribers to work with the Distributor.

Formulating the Upgrade Plan

Now that we understand the differences between a side-by-side and computer-to-computer SQL 6.x upgrade, we can formulate a plan to perform the upgrade:

    1. Identify the server to be upgraded. Verify that it meets all hardware and software requirements. If the server participates in a SQL replication strategy, you should upgrade the Distributor first using a side-by-side upgrade method.
    2. Perform a full backup of the SQL 6.x Database Server.
    3. Increase the size of the TempDB database to at least 10MB on the SQL 6.x server.
    4. Verify that local groups have the same names and members on both the SQL 6.5 and SQL 7.0 servers if a computer-to-computer upgrade was selected.
    5. Start the SQL Upgrade Procedure.
    6. Verify proper conversion of databases using SQL Tools Enterprise Manager, ISQL etc.). If there was a custom front-end written for the SQL 6.5 database, verify functionality with the front-end.

If there were any issues found after the upgrade, a contingency plan can be formulated as follows:

· Use the sp_dbcmptlevel stored procedure to attempt to set the database to behave like a previous version of SQL Server.

· Use the SQL Switch option to return to SQL Server 6.5 until the issues are resolved.

In the unlikely event that you run into significant compatibility issues, you should restore the server from the backup device.

Plan the Migration of Data from Other Data Sources

In the previous section, we saw how to transform an existing SQL 6.x database into a SQL 7.0 database. In certain situations it may be necessary to get data from non-SQL sources, such as a mainframe, an Access database, or an Oracle database server.

In previous versions of Microsoft SQL Server, Microsoft gave the user very few options for data migration. You could only migrate data from SQL 4.2, SQL 6.0, SQL 6.5, Sybase Databases, and text files into the SQL Server database. If data could be converted to a standard delimited text file, Microsoft allowed the use of a cryptic command line tool called Bulk Copy Program (BCP) to load this data into your SQL Server database. The real power behind the Bulk Copy Program was the ability to incorporate it into scripts. Many organizations depend on BCP to perform automated data input from external sources into SQL Server.

Introducing Data Transformation Services

While SQL Server 7.0 still maintains support for the Bulk Copy Program (BCP), Microsoft has made the importation of non-native SQL data extremely easy and relatively painless. Using Data Transformation Services (DTS), you can import and export data between Access, Excel, SQL, FoxPro, DBase, Paradox, Oracle, Site Server, Index Server and any ODBC data source. The beauty of this new feature is that you can not only easily transform data to load a new database, but you can also save the DTS package on the SQL Server so that a scheduled job can be performed on a regular schedule. Consider the following real-world example to better illustrate this function:

On the Job: A home health care agency uses an electronic medical record database to keep track of all patient information. The nurse brings a laptop into the field each day, entering each patient’s information into a customized Access database. At the end of the day, the nurse dials in to the corporate office and uploads the .MDB file into a common directory. The SQL Server administrator has created a DTS package that will automatically pick up the nurse’s file and roll it into a master SQL database at midnight each night. Using the export functionality of DTS, the nurse is also able to download a new Access database while they are dialed into the corporate network. This database contains the patient records for the patients they are scheduled to see the following day.

The above scenario is available using only the DTS interface to import and export Access files to and from the SQL Server. This powerful functionality is easy to use and much more user-friendly that the BCP command-line utility. Besides using the DTS scheduling commands, automation utilities based on DTS functionality can be incorporated into custom applications using ActiveX scripts.

SQL Server Configuration Options

Now that we have defined how the server will be used, we can begin determining some of the configuration specifics for the server. Prior to installation, we should have a good idea of the following: which Network Libraries should be installed, what security model will be used, and what configuration options will be configured on the SQL Server.

What is a Network Library?

In order for two people to communicate with one another, a certain process must take place. The two must first acknowledge the fact that they wish to communicate, and then begin the process of negotiating a common language that both parties can speak and understand. When SQL needs to communicate with a client application running on the SQL Server itself, SQL will choose a Windows Interprocess Communication (IPC) component, such as local named pipes or shared memory, to communicate between client and server. But what happens when the client application is running on a client on the other side of the globe?

SQL Server has a wide variety of "languages" it can speak to non-local client applications. The protocols consist of the following: Named Pipes, TCP/IP, Multiprotocol, NWLink IPX/SPX, AppleTalk, and Banyan VINES. These libraries allow network packets to transfer data back and forth between a client and a SQL Server. When configuring a server, we want to make sure we include all the appropriate libraries so that client and server can communicate. While it may seem an obvious choice to simply install all the libraries on the server, you must remember that each library installed will consume system resources, degrade overall network performance, and in some cases open a security hole that unscrupulous individuals may wish to exploit. For this reason, you will want to streamline the libraries installed on both the client and the server. We will analyze each library individually to help narrow down which libraries work in each environment.

Named Pipes

This library is installed by default in the SQL Server installation for Windows NT. If you plan to install SQL Server on a Windows 9x machine, the Named Pipes server library will not be supported on this machine, as the server portion of the Named Pipes API is not supported in Windows 9x. The default pipe used by SQL is \\.\pipe\sql\query. While you can choose to change the name of this pipe, several SQL Server utilities, such as the SQL Server Upgrade Wizard, depend on the default pipe name. For client/server named pipes communication to take place, both machines must be running named pipes and have the same pipe name specified.

TCP/IP Sockets

The TCP/IP Sockets library is also installed by default during the SQL Server 7.0 installation process. The library communicates using Windows Sockets as the IPC method across the TCP/IP protocol. It is chosen by default for Windows 9x SQL Server installations. To use the TCP/IP Sockets library, you must select a port on which the server will listen. By default, the SQL Server installation program selects port 1433.This number is the official Internet Assigned Number Authority (IANA) () port number for SQL Server. You may change this number if you wish. As in the case of named pipes, however, both the client and server must have the same port number assigned to them. If your enterprise is running a Microsoft Proxy Server, you may specify the proxy server address when setting up the server’s TCP/IP Sockets library.

Multiprotocol

The Mutiprotocol library is installed by default on Windows NT and Windows 9x SQL Server installations. There are two significant advantages to using this protocol. If you wish to assure that all data transmission to and from the database is secure, you can enable the multiprotocol encryption option in the Server Network Utility. The second advantage to using this protocol is that it will automatically negotiate what library to use (either named pipes, TCP/IP or NWLink IPX/SPX). This advantage is particularly useful if you have several SQL servers that are each running a different Network Library and you do not wish to reconfigure each client to run a specific protocol.

To enable multiprotocol encryption, you must add a value to the client computer’s registry to allow end-to-end encryption to take place. To perform this using RegEdit, go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client located in the Windows registry. Here you must 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."

Exam Watch: Even if multiprotocol encryption is selected when installing the SQL Server, you will not have an encrypted communication unless the client registry is modified with the value above.

Other Protocols

Microsoft SQL Server also includes network library support for Novell NWLink IPX/SPX networks, AppleTalk networks, and Banyan VINES networks. If you are running in a strictly IPX/SPX network environment, you may wish to load the native NWLink IPX/SPX library. To better identify which libraries can be used on top of each network protocol, consider Table 4-3.

Network Library Compatible Network Protocols Notes
Shared Memory Local System Only – Memory to Memory Copy Used for local server communications
Named Pipes* Local (File System), TCP/IP, NetBEUI, NWLink Defaults to \\.\pipe\sql\query
Multiprotocol** Local (File System), TCP/IP, NetBEUI, NWLink Supports encryption. Automatically chooses a compatible client/server communication library.
TCP/IP TCP/IP only Defaults to port 1433
NWLink IPX/SPX NWLink Useful for pure Netware environments
AppleTalk AppleTalk Useful for Macintosh environments
Banyan VINES Banyan VINES Useful for Banyan VINES environments

Table 3: Network Libraries and Corresponding Protocols

* Server side named pipes is not supported on SQL Server 7.0 installations on Microsoft Windows 9x machines.

** Multiprotocol encryption is enabled in the server network utility by creating a new registry key on the client workstation.

Armed with this information, an administrator in a large environment can install the libraries that are appropriate for his enterprise, and disable all others. For most modern environments, a combination of named pipes and TCP/IP will most likely satisfy most requirements. For highly secure environments, the multiprotocol library should be chosen, as it is the only library which natively supports encryption.

Security Considerations for a SQL Server 7.0 Implementation

Within the realm of security, the media typically focus on data security from the perspective of a hacker trying to penetrate an environment from the Internet. Many studies show that the greatest risks to an organization are from attacks within the corporate network. These users already have a level of access and have knowledge of business practices and procedures. Members of the Information Technology (IT) staff pose the greatest risk to corporate data, as they often have administrative privileges in the domain. These privileges allow high levels of file system access even though they may not require it. Although it is far beyond the scope of this book to discuss methodologies for properly securing the file system, it is essential that we talk about how security affects a SQL Server database.

In the last section, we talked about enabling multiprotocol encryption. In environments where the data stored in the database can be viewed as sensitive, the implementation of multiprotocol encryption can be seen as a proactive step toward securing the data contained within the database. Additionally, we discussed disabling inactive network libraries on the SQL Server. To establish a truly secure environment, you may also wish to change the port settings for TCP/IP, and change the default pipe name for the named pipe connection. These small steps to securing the enterprise will help deter the curious hacker from finding his way into your database.

In almost every enterprise, there is some aversion to changing the system administrator (SA) account from the default null password. Regardless of group membership or clever changes of the port or default named pipe settings, all will be useless if the SA password remains blank. The first step after any successful SQL Server installation should be to change the SA password.

Depending on the installation options selected in the installation routine, SQL Server will depend on several services for proper operation. These services can be installed to use the local system account, or a predetermined Windows NT account. In most cases, you will want to use a Windows NT domain account, as it will allow the server to participate in replication, as well as take advantage of remote procedure call usage.

When creating a Windows NT account to use with these services, you may wish to define which machines the account is allowed to log in to. This practice will significantly increase the security of this administrative account. Additionally, you will want to ensure that a strong password is selected to deter individuals from using password hash matching utilities (such as L0phtCrack) from obtaining a password for these administrative accounts.

If you do not plan to use replication or remote procedure calls, you can reduce administrative overhead by using the LocalSystem account. By using this account, you eliminate the hassles of frequent password changes, and can allow for better separation of duties.

It is recommended that you do not use the NT Domain Account as the SQL Service Account. Creating separate accounts will allow for separation of duties and more frequent password changes.

With these basic steps out of the way, we can now begin to analyze the implementation of the SQL Server security model. As we learned earlier, SQL Server 7.0 implements two models for security: Windows NT Only and SQL Server and Windows NT. Some third party applications may expect the SQL Standard security model to be implemented, while others may allow you to establish trusted connections to the server without any issues. When planning your SQL Server infrastructure, it is important to consider whether the model should be based solely on Windows NT security, or if it should also include standard SQL Server security.

Prior to installation, you will want to define how server and database roles will be applied to each database user. SQL Server 7.0 includes a new hierarchical security model, which allows users and groups to be assigned to security roles that permissions can be assigned to. While a security role may sound similar to a Windows NT group membership, the difference between a role and a group is that the role defines a set of permissions for a specific job function or classification. For example, you can assign a user to the server role Database Creators. This right grants users in this role the ability to create new databases, but does not have any implied rights beyond the creation of a new database.

By default, SQL Server creates the following Server Roles found in Table 4-4.

Role Rights Associated
System Administrators All rights on the system
Security Administrators Can read the audit log, add/drop logins, grant the ability to Create a Database
Server Administrators Can drop extended stored procedures, run DBCC pintable, and can shutdown and reconfigure the server
Setup Administrators Can add and drop stored procedures, add users to the Setup Administrators role, and can install replication
Process Administrators Can KILL processes
Disk Administrators Can add/drop devices, and can configure mirroring
Database Creators Can alter, create, extend, and rename a database

Table 4: SQL Server Roles

In addition to applying server wide permissions, you may also place users into specific roles in each individual database. Unlike the Server Roles, you can create new roles in each database to tailor the roles to your organization. SQL Server includes the following database roles:

When planning your SQL Server 7.0 infrastructure, you will want to determine which users will be placed into which roles. This process will allow you to create a secure environment that does not restrict business needs. In too many organizations, the lack of understanding about proper security leads to an environment where users enjoy too much access on the system.

Why is Security Important? We Trust Our Employees Here.

It is not uncommon to hear this question posed in many environments. If a user has too much access to a system, not only is it a security threat, but it can also result in countless hours of troubleshooting (or restoring from tape to recover from) the problems caused by these renegade users. For users with SA privileges on a database, they can delete rows of data, entire tables, or even remove a database! In order to protect your sanity as a database administrator (even in non-secure environments), the implementation of both Server and Database roles should be performed.

Post Installation Configuration for SQL Server 7.0

In preparation for the installation of SQL Server 7.0, several optional steps performed after the initial installation will allow SQL to have an extended feature set. These options consist of higher performance, easier administration, and better control over how queries are run on the server.

SQL Server has the ability to monitor the server and watch for certain conditions to occur. When SQL raises an event to the event log, you can configure SQL Server for the following: perform a net send, send an e-mail, or page someone depending on the criticality of the error. To properly plan for the installation of a SQL Server in an enterprise environment, you will want to make sure an escalation plan is in place for any issues that may arise. Begin this plan by identifying the individuals who will be responsible for rectifying the situation. You will want to identify what events should trigger an e-mail alert and which events will trigger a pager alert in the SQL Alert Manager. SQL Server will have many of the important alerts predefined in the Alert Manager, including full database transaction logs, full database alerts and other critical errors.

One of the most useful extended features in SQL Server is the SQL Mail component. SQL Mail allows you to process queries and receive messages about the status of the server via electronic mail. This feature requires the installation of the Windows Messaging Service (or Microsoft Exchange) and an appropriate MAPI driver. You log into the server, set up the Exchange profile, and then configure SQL Server to use that profile. Once installed, you can mail queries to the SQL Server for processing, get a results set back via e-mail, and receive critical alerts and messages from the server. Using a distribution list, you can further enhance the functionality of SQL Mail by including the individuals on the escalation plan in the distribution list. Using this method, you can easily switch which individuals are notified by the SQL Mail utility.

One of the many new features found in SQL Server 7.0 is the ability to dynamically configure database sizes and the amount of memory allocated to the SQL Server. By default, SQL Server is configured to automatically allocate the most appropriate amount of memory and disk space to the server and its databases. Using the manual configuration options, you can force SQL to allocate only a specific amount of memory, and only use a subset of the server’s total processing power. This option can be very handy if you are running Internet Information Server or another application on the server. What this option will do is help balance performance across all applications running on the server. In creating an installation plan for your environment, you will want to ensure that you have analyzed any other applications that will need to run simultaneously with the SQL Server environment.

You can also configure SQL Server to allow users access to an extended stored procedure. This procedure will allow them to perform command shell functions from the SQL Server environment. The only setback is that it presents a huge security hole. This can be disastrous if it is allowed on a production database. The reason is that it allows a user with minimal access to the server to perform command line activities under the context of an administrative account. With the exception of servers that are designed for development only, the xp_cmdshell stored procedure should never be enabled for non-administrative users!

How Users Access Data in SQL Server

It would not be a particularly useful gesture to offer a brief course in Transactional SQL in order for users to make decisions based on the data in the SQL database. Instead, we must use front-end development environments such as Visual Studio, in order to provide a friendly, intuitive method for deriving result sets from the SQL database. Other environments such as Visual Basic, Visual C++, and Visual InterDev can be used as well. When planning a database implementation using SQL Server, you must also think of how the database will be accessed by the user. This key element in planning will help determine how other essential elements will be implemented on the database server. Elements such as triggers, defaults, constraints, triggers, security, network libraries, and the sort order are example of these elements.

Exercise 4-1: Creating an Installation Plan for Your Work Environment

The following worksheet will help you plan a secure and efficient SQL Server implementation. The best method to use this worksheet is to answer each question in the left-hand columns:

All SQL Server Installations      
Verify Hardware Compliance for
new SQL Server 7.0 Platform
     
Alpha AXP or Intel Pentium 133 or higher Compliant Non-Compliant Upgrade processor architecture
32MB RAM Compliant Non-Compliant Upgrade RAM
Free Disk Space Available on Server Compliant Non-Compliant Add additional disk space
Verify software compliance for new SQL Server 7.0 Platform      
Windows NT 4.0 with Service Pack 3 or higher Compliant Non-Compliant Install Service Pack 3
Windows 95 or Windows 98 Compliant Non-Compliant Install Windows 9x
Additional network support for VINES or AppleTalk required? No Yes Install additional software
Does this server require a special character set? No (use default) Yes Select the appropriate character set during installation
Does this server require a special Sort Order? No (use default) Yes Select the appropriate sort order during installation
Does this server require a special Unicode Collation Sequence? No (use default) Yes Select the appropriate Unicode Collation Sequence during installation
What Network Protocols are used in your enterprise?      
TCP/IP Named Pipes, Mulitprotocol, TCP/IP    
NWLink Named Pipes, Multiprotocol, NWLink, IPX/SPX    
NetBEUI Named Pipes, Multiprotocol    
AppleTalk AppleTalk    
VINES Banyan VINES    
Will you require encrypted data to and from the SQL Server? No Yes Use the Multiprotocol Network Library
Do you have MAPI drivers for your e-mail infrastructure? Yes, install SQL Mail No Obtain MAPI drivers if you wish to implement SQL Mail Services on the SQL Server
Identify users and administrative roles: Complete    
Define escalation plan for SQL Server (if necessary) Complete    
Identify which users will receive e-mail or pages from SQL Alert Interface Complete    
Choose a strong password to be used for the SA account

Complete

   
How will SQL Services log on to the server? LocalSystem Account
NT Domain Account Create the appropriate domain accounts
Will this server be a standard production server or will it be used for development? Production Development Add the developer options in the Complete/Custom installation
Does the front-end application require a specific SQL Security model to be implemented? No (use NT only) Yes Choose the appropriate SQL Server security model
Large Scale SQL Server 7.0 Roll Outs:      
Method for automated installation SMS Unattended Install Create answer file
Upgrading Existing SQL Server Installations:      
What SQL Platform does the data currently exist in? SQL 6.x SQL 4.2 Upgrade database server to SQL 6.5 before attempting to upgrade data
Will the upgrade process be performed during installation or after? During After Run the SQL Upgrade Wizard after configuration of the server
Do any of the servers to be upgraded participate in replication? No Yes You must perform a side-by-side upgrade, starting with the distributor replication server
Obtaining data from other sources:      
Will this server obtain data from an external source? No Yes  
Is the data source supported by SQL Server? Yes No Convert the existing data a delimited text file
Post SQL Server Installation Configuration:      
Do you wish for non-administrators to access the shell using an extended stored procedure? No Yes Check the xp_cmdshell option in the SQL Server properties dialog
Will this SQL Server also be used for another BackOffice component or custom application? No Yes Verify memory and processor usage after installation

Certification Summary

In this chapter, we discussed the features and configurable options that are available during and after installation of the SQL Server 7.0 database server platform. We learned about the following components: how SQL Server can be implemented in an enterprise, the hardware/software requirements for both SQL Server Client and Server installations, and how SQL Server can be rolled-out using SMS or an answer file. Next, we examined the migration of data from previous versions of SQL Server, as well as the migration of data from external sources using the Data Transformation Service.

To help determine how SQL Server fits into an Enterprise environment, we then discussed the network libraries, including when each library should be implemented. A brief summary of the database and server roles and the SQL Security model was presented. We then looked at how a custom a SQL front-end could affect our installation plan. At the end of the chapter, you were presented with a worksheet that will help you plan the installation, configuration, security, and maintenance of a SQL Server 7.0 database.

In the next chapter, we will take an in-depth look at character sets, sort orders, and Unicode Collation Sequences. We will also create an NT account for the service accounts, as well as walk through an installation of SQL Server from start to finish.

Two-Minute Drill