po polsku
Chapter 5
Planning an Installation or Upgrade
Developing an Installation Strategy and Plan
Step 1: Determine System and User Requirements
Step 2: Select the Right Platform
Step 3: Answer Required Questions and Understand Why They
Are Important
Step 4: Install SQL Server
Developing an Upgrade Strategy and Plan
Upgrade the Existing SQL Server
Install a New Server and Migrate the Databases
Summary
Developing an Installation Strategy and Plan
Developing an Upgrade Strategy and Plan
Summary
In this chapter, you develop plans and strategies to help you correctly install
or upgrade SQL Server. Why bother with a planning stage? Why not just skip right
to the installation or upgrade? SQL Server installation is a simple process but by
planning ahead, you can make the correct decisions before the installation that affect
the performance and operation of SQL Server. In the case of an upgrade, you can never
make too many plans to limit server downtime and protect your database against problems
encountered during the upgrade. Start by examining installation strategies and plans.
Developing an Installation Strategy and Plan
Developing an installation plan starts with the assessment of the requirements
of your business or users, includes the selection and purchase of the hardware, and
finishes with making decisions for specific SQL Server options. You begin the process
by collecting user and system requirements. Then you examine possible hardware configurations
and SQL Server options. You then create a checklist to use during system installation.
Finally, you install SQL Server.
Step 1: Determine System and User Requirements
How do you determine the hardware system requirements and user requirements for
SQL Server? How else: You ask questions and do some homework. Start with the user
requirements or business requirements. Based on the requirements of the users or
business, you can determine the size and type of hardware system you need to meet
the requirements. Start with the following questions:
What is the purpose or goal of the system?
What are the database requirements?
What are the user or business requirements?
How much money will it cost?
NOTE: One decision you won't have to make is which operating system to use.
Microsoft SQL Server 6.x is supported only on Windows NT Server and Windows NT Workstation.
If you have decided to use Microsoft SQL Server, the operating system war is already
over!
The following sections expand on each of the preceding questions to help you determine
the type of system you need.
What Is the Purpose or Goal of the System? The first questions you might ask
yourself are, "What is the system for?" and "Is the system for a single
department with 10 users or for a very large database with several hundred users?"
In most cases, a system supporting more users requires more memory, disk space, and
processing power. Is the system a dedicated SQL Server system or does it perform
other activities like file and printing services? Is the system replacing another
system as a result of downsizing or right-sizing? If it is replacing an existing
system, you already have a lot information available to you (such as the current
load on the system and the current system's shortcomings). Is the system a production
system or a development/test system? You will want more fault tolerance and more
storage capability on a production server than you typically need on a development
server.
What Are the Database Requirements? What are the database requirements for
the system? Will the SQL Server primarily be used for decision support systems or
transaction systems? How heavy is the expected transaction load? If the system is
transaction driven, try to determine the number of expected transactions per day
and how the transactions are processed. For example, is the server idle for eight
hours and does it then process all the transactions during a few hours, or does it
process the transactions evenly throughout the day? What is the expected size of
the database? Are you moving databases from another system to SQL Server because
of downsizing or right-sizing? If so, you should be able to obtain information such
as the current database size, expected database size, and the transaction load of
the system from the current system.
TIP: If you have the means, dedicate a machine for SQL Server. Then you can
tune the hardware to give the best SQL Server performance.
What Are the User or Business Requirements? It is always important to understand
the requirements and expectations of the individuals who use SQL Server. What type
of query response time do the users expect? How many users will be logged on to SQL
Server at one time? What are the backup and storage requirements of the users or
business? How Much Money Will it Cost? Maybe this question should be listed first!
In the real world, the difference between the system you need and the system you
get is the amount of money you have available to spend on the system. (Enough said!)
Step 2: Select the Right Platform
After you obtain the answers and information to the questions described in "Step
1: Determine System and User Requirements," in the first part of this chapter,
you are ready to select the hardware platform for your SQL Server. For this discussion,
the hardware platform is divided into four areas:
Hardware (including the processor or processors and peripherals)
Memory
Disk drives
File system
The following sections examine each area and the type of decisions you need to
make for each area. Hardware When determining which type of hardware platform to
use, the first and last place to check is the Windows NT Hardware Compatibility List
to make sure that the brand and model of the machine you are considering is on the
list. If the brand and model you are interested in is not on the compatibility list,
download the latest list from an electronic bulletin board. If the machine is still
not listed, check with the manufacturer or Microsoft.
TIP: Save yourself a lot of problems and potential headaches: Use only the
machines approved for Microsoft Windows NT. Although you may get other machines to
work, I have seen the difficulty involved and the potential to not get the
machine up and running when using non-approved platforms and configurations.
At the time this book goes to press, Windows NT is supported on the following
microprocessors:
Digital Alpha AXP
Intel 32-bit x86 (486, Pentium, and so on)
MIPS
Power PC
NOTE: Remember to check the compatibility list; support for new systems is
an ongoing process.
So, how do you determine the correct hardware platform for your business or organization?
Start with cost and examine hardware platforms within your budget. There is no point
wasting your time researching hardware platforms you can't afford.
The next step is to use the information you gathered earlier--such as the expected
number of transactions during a given time period--and talk to the hardware manufacturers
or integrators to see whether the platform you are considering can meet those goals
and requirements. Check for SQL Server benchmarks on the particular platform and
ask to speak to other clients currently using the platform. Consider other factors
such as manufacture reliability, service, and maintenance. These three factors are
extremely important if the machine runs into a hardware problem and you are faced
with downtime. Consider expandability; for example, will you require multiple processors
in the future? If so, can the current platform be expanded to accept more processors?
Do I Need SMP (Symmetric Multiple Processors)?
Right out of the box, Windows NT 3.51 supports up to four processors; SQL Server
can take advantage of these processors without any special add-ons or configuration
changes. In theory, a perfect scaleable SMP machine would scale 100 percent, meaning
that if your SQL Server performed 20 transactions per second and you added a second
processor, you would increase the number of transactions to 40 per second. The scalability
of systems varies widely and can range from nearly 100 percent to below 60 percent.
Check with the manufacturer. What does it mean to you and SQL Server? If you are
performing heavy transaction database processing, you can expect your transaction
performance to increase with the scalability of the system. If you perform 10 transactions
per second and add a second processor on a system that provides 80 percent scalability,
you can expect roughly 18 transactions per second. SMP works very well for transaction-based
systems. What if you do primarily decision support (such as database queries)? Adding
a second processor may not be the best way to improve your system performance. In
decision support systems, the queries are I/O bound and not processor bound, so adding
additional processors does not provide the same substantial performance gain you
get with transaction-based systems.
Memory A common theme in this book is give SQL Server enough memory.
Not because SQL Server is an inefficient memory hog, but because SQL Server uses
memory very intelligently. Extra memory can provide you with some very cost-effective
performance enhancements. The minimum memory requirement for an Intel-based Windows
NT Server with SQL Server is 16M (RISC machines like the Alpha AXP and the MIPS require
slightly more memory). If you want to use replication, the minimum requirement jumps
to 32M of memory with at least 16M assigned to SQL Server.
TIP: Although a Windows NT server with 16M of memory is the minimum requirement,
I recommend starting with 32M, allocating 16M for Windows NT and 16M for SQL Server.
You can tune up from there.
The setup program allocates up to but does not exceed 8M of memory to SQL Server.
Once SQL Server is up and running, you can increase the amount of memory allocated
to SQL Server. How does SQL Server get memory? When SQL Server starts, requests are
made to the operating system to obtain the configured amount of memory. Memory is
allocated for the SQL Server executable code, static memory, data structures, and
miscellaneous overhead. SQL Server divides the remaining memory into the procedure
cache and data cache (as discussed in Chapter 19, "Which
Knobs Do I Turn?").
TIP: When SQL Server starts, it requests the amount of memory in the configuration
parameter. The operating system then allocates as much physical memory as possible
to SQL Server; if needed, it uses virtual memory to meet the memory configuration
requirement. Avoid setting the memory configuration option higher than the amount
of physical memory available to SQL Server (subtract the memory required for Windows
NT from your machine's total memory; the remainder can be allocated to SQL Server).
Using virtual memory can slow performance. Never set the memory parameter too high;
if SQL Server cannot get the required amount of memory from physical and virtual
memory, the server does not start.
In later chapters, you learn to tune your server for the correct amount of memory.
At installation time, however, how much memory should you use? Microsoft has published
the following suggestions as rough estimates for SQL Server and Windows NT memory
configurations. These figures are from SQL Server's online documentation:
Machine Memory (Megabytes)
Approximate SQL Server Memory Allocation (Megabytes)
16
4
24
8
32
16
48
28
64
40
128
100
256
216
512
464
Configuration & Tuning of Microsoft SQL Server for Windows NT on Compaq
Servers (February 1994, Database Engineering, Compaq Computer Corporation) suggests
the following formula to use as a rough estimate for memory allocation:
SQL Server Memory = 5MB for Kernel and Data Structures
+ (2% Total Data and Index Space) + (50 KB * Number of Users)
Regardless of the amount of memory you start with, once SQL Server is up and running,
you can monitor SQL Server to more accurately determine your memory requirements.
Disk Drives One of the most important system decisions you can make is the type of
disk drives and disk controllers you select. Selecting the proper disk system has
a big impact on the overall performance of the SQL Server system and the type of
data fault tolerance used to protect the databases.
CAUTION: Take special care in selecting your disk system. Disk I/O is the
typical bottleneck found in database systems.
Before you get into the specifics, you want to select fast disk drives and smart
controller cards to take advantage of Windows NT multitasking and asynchronous read-ahead
features. When buying disk drives for a database server, consider using more, smaller
physical drives rather than one large physical drive. Doing so allows you to spread
your databases and transaction logs over several different physical devices. If you
are considering buying one 2G hard drive, for example, reconsider and purchase two
1G hard drives or four 500M hard drives.
TIP: The asynchronous read-ahead technology in SQL Server 6.x is beneficial
only with multiple disk configurations and smart disk controllers that have asynchronous
capabilities.
Just as important as the speed of your hard disk system is the fault tolerance
offered in modern disk drive systems. You want the best protection for your databases
with optimum performance. One option available to you is the use of RAID (Redundant
Array of Inexpensive Disks) disk drive configurations. RAID disk configurations use
several disk drives to build a single logical striped drive. Logically, a striped
drive is a single drive; physically, the logical drive spans many different disk
drives. Striping the drives allows files and devices to span multiple physical
devices. By spreading the data over several physical drives, RAID configurations
offer excellent performance. Another benefit of RAID configurations is fault tolerance
and recovery. A RAID 5 configuration can lose a single disk drive and recover all
the data on the lost drive. When a new drive is added, the RAID configuration rebuilds
the lost drive on the new drive. A RAID 5 system offers good protection and performance
for your databases. RAID configurations can be hardware-based solutions or Windows
NT software-based solutions. Hardware-based RAID solutions are typically faster than
software-based RAID solutions. File System Should you use NTFS (New Technology File
System) or FAT (File Allocation Table)? From a performance standpoint, it does not
really matter (the performance difference between the two file systems is negligible).
In general, NTFS performs faster in read operations and FAT performs faster in write
operations. If you use the NTFS file system, you can take advantage of Windows NT
security. If you are required to have a dual boot computer, you should use a FAT
partition.
TIP: I typically recommend NTFS, which can take advantage of NT security and
auditing features.
The Right Platform What is the right platform for SQL Server? The best system
you can afford that will do the SQL Server processing you require! A good configuration
for a SQL Server system is shown in Figure 5.1: a computer configured with one or
many processors and starting with 32M of memory. Use a RAID 5 stripe set disk configuration
for the databases and the operating system and place SQL Server on a nonstriped drive.
How could this system be enhanced? Add additional stripe sets or more memory. Additional
stripe sets can give you additional logical drives so that you can place a table
on one logical drive and its index on another. For the memory requirements, monitor
your SQL Server and determine the correct amount of memory for your Server. After
all, SQL Server can run with as little as 4M of memory and as much as 2G of memory.
Figure 5.1.
A typical SQL Server hardware configuration.
Step 3: Answer Required Questions and Understand Why They
Are Important
When you begin a new SQL Server installation, you are asked several questions
such as your name, company name, and the type of license agreements for the SQL Server.
You should be able to answer these questions with no problem--but you are also asked
to answer other questions that affect SQL Server performance, maintenance, and behavior.
Examine the following installation topics in more detail to help you make the correct
choices for your SQL Server:
master device
Character set
Sort order
Network
SQL Server executive account
The master Device During installation, you are asked to give the drive,
path, and filename of the master device. The master device is the
most important database device and is described in more detail in Chapter
8, "Managing Devices." The master device contains the master
database, which houses all the SQL Server information required to manage and maintain
the server's databases, users, and devices (basically, all the information required
to maintain and run SQL Server). The master device also contains the model
and tempdb databases, as well as the optional pubs database. The
default name for the master device is master.dat and the default
path is the drive and root directory selected for the SQL Server installation in
the directory \DATA. The default/minimum size for the master
device is 25M.
NOTE: The default size for SQL Server version 4.21 is 15M. The minimum installation
size for the master device in version 6.x is 25M. You should use a size
of 35M to 40M to give you room for expansion. A larger master device size
gives you more room to create SQL Server objects and enables you to expand the temporary
database (tempdb) beyond the default of 2M (if tempdb is not placed
in RAM).
Character Set The character set is the set of valid characters in
your SQL Server database. A character set consists of 256 uppercase and lowercase
numbers, symbols, and letters; the first 128 characters in a character set are the
same for all the different character sets.
NOTE: If you plan to use SQL Server replication, you must select the same
character set for all the SQL Servers participating in replication.
Following are some common character sets you can choose from at installation time:
Code page 850 (Multilingual)
Code page 850 includes all the characters for North American, South American, and
European countries.
Code page 850 is the default character set for SQL Server 4.21 installations.
ISO 8859-1 (Latin 1 or ANSI)
This character set is compatible with the ANSI characters used by Microsoft Windows
NT and Microsoft Windows.
ISO 8859-1 is the default sort order for SQL Server version 6.x.
Code Page 437 (US English)
The common character set used in the United States. Code Page 437 also contains many
graphical characters that are typically not stored in databases.
Other available character sets are as follows:
Code Page 932
Japanese
Code Page 936
Chinese (simplified)
Code Page 949
Korean
Code Page 950
Chinese (traditional)
Code Page 1250
Central European
Code Page 1251
Cyrillic
Code Page 1253
Greek
Code Page 1254
Turkish
Code Page 1255
Hebrew
Code Page 1256
Arabic
Code Page 1257
Baltic
CAUTION: Deciding on the correct character set is important because the character
set cannot be changed easily. Changing the character set requires rebuilding and
reloading all your databases.
Sort Order The sort order you select for your SQL Server determines how
the data is presented in response to SQL queries that use the GROUP BY,
ORDER BY, and DISTINCT clauses. The sort order also determines
how certain queries are resolved, such as those involving WHERE clauses.
For example, if you choose a sort order that is case sensitive and you have a table
called MyTable, the query to select all the rows from MyTable must
have the following format:
Select * from MyTable
If the sort order selected is case insensitive, the preceding query could be written
in following manners:
Select * from MyTable
Select * from mytable
Select * from MYTABLE
SQL Server offers many different sort orders, each with its own set of rules.
The following sections briefly examine some of the possible sort order choices. Not
all sort orders are available for every character set. Dictionary Order, Case Insensitive
Dictionary order means that the characters, when sorted, appear in the order
you find them in a dictionary. Dictionary order, case insensitive, uses the following
rules to compare characters:
Uppercase and lowercase characters are treated as equivalents.
Characters with diacritical marks are treated as different characters.
NOTE: Dictionary order, case insensitive, is the default sort order for SQL
Server 6.x.
Binary Sort Order The binary sort order uses numeric values to collate
the data. Each charter is compared to its numeric representation of 0 to 255. Data
does not always come back in dictionary order; for example, UUU returns
before aaa in ascending order. Dictionary Order, Case Sensitive Dictionary
order, case sensitive, uses the following rules:
Uppercase and lowercase characters are not treated the same.
Characters with diacritical marks are treated as different characters.
TIP: If you have two servers available and you want to change the sort order
or character set, use the Transfer Manager built into the Enterprise Manager to rebuild
the database and transfer the data.
The sort order affects the speed and performance of SQL Server. Binary sort order
is the fastest of the sort orders; the other sort orders are 20 to 35 percent slower
than the binary sort order. The default sort order (dictionary order, case insensitive)
is about 20 percent slower than the binary sort order.
CAUTION: Selecting the correct sort order is important because changing the
sort order--just as changing the character set--requires rebuilding your databases
and reloading the data.
TIP: If you have several SQL Servers in your organization, you should use
the same character set and sort order for each of the servers, especially if you
want to share databases using the DUMP and LOAD commands. You can't
load a database that was dumped with a different character set and sort order.
Network Because SQL Server supports many different network options simultaneously,
clients running TCP/IP can connect to SQL Server along with clients using IPX/SPX--all
at the same time. SQL Server installs different network libraries during installation
to handle network communication with other servers and client workstations. SQL Server
always installs the named-pipes protocol. You have the option during installation
(and after) to install one or more network libraries. Keep in mind that the type
of network support you select determines the security mode you can use for SQL Server.
Before you examine the network libraries available to you, look at the three different
security modes (for detailed information, see Chap-ter 10, "Managing Users"):
Standard. An individual logging on to SQL Server supplies a user name
and a password that is validated by SQL Server against a system table. Standard security
works over all network configurations.
Integrated. Integrated security takes advantage of Windows NT user security
and account mechanisms. Integrated security can be implemented over the named-pipes
protocol or multi-protocol network protocols.
Mixed. Users using trusted connections (named-pipes or multi-protocol)
can log in using integrated security; users from trusted or nontrusted connections
can log in using standard security.
The following sections describe the network options available for SQL Server 6.x.
Named-Pipes Protocol The named-pipes protocol is the default protocol installed with
SQL Server. Named-pipes allows for interprocess communication locally or over networks
and is used in NT networks using the NetBUI protocol. Multi-Protocol Multi-protocol
is new for SQL Server version 6.x. The multi-protocol uses Windows NT Remote Procedure
Call (RPC) mechanisms for communication and requires no setup parameters. Multi-protocol
currently supports IPX/SPX and TCP/IP, enabling users of those protocols to take
advantage of SQL Server integrated security features.
NOTE: Before SQL Server version 6.0, integrated security was supported only
by named-pipes protocol.
NWLink IPX/SPX Protocol IPX/SPX is the familiar network protocol used for
Novell networks; it is the default network protocol for Windows NT 3.5x servers.
If you select NWLink IPX/SPX during installation, you are prompted for the Novell
Bindery service name to register SQL Server. TCP/IP Protocol TCP/IP is a popular
communications protocol used in many UNIX networks. If you select TCP/IP, you are
asked to provide a TCP/IP port number for SQL Server to use for client connections.
The default port number and the official Internet Assigned Number Authority socket
number for Microsoft SQL Server is 1433. Banyan Vines Banyan Vines is another popular
PC-based network system. Support for Banyan Vines is included only on Intel-based
SQL Server systems. If you install Banyan Vines, you are prompted for a valid street
talk name that must first be created using the Vines program MSERVICE. AppleTalk
ADSP Protocol AppleTalk ADSP allows Apple Macintosh clients to connect to SQL Server
using AppleTalk. If you select AppleTalk, you are prompted for the AppleTalk service
object name. Decnet Protocol Decnet is a popular network protocol found on many Digital
networks running VMS and Pathworks. If you select Decnet, you are prompted for a
Decnet object ID.
NOTE: For performance, the named-pipes protocol is the fastest of the network
protocols. Running TCP/IP and IPX/SPX is faster than using the multi-protocol but
limits your security mode options.
SQL Executive User Account The SQL Executive was first introduced in SQL
Server 6.0. The SQL Executive is the service responsible for managing SQL Server
tasks such as replication, events, alerts, and task scheduling. During system installation
and upgrade, you are required to assign an NT system user account for the SQL Executive.
You can use the local system account (in which case, you do not have to create a
new NT user account) but you will not be able to perform tasks with other servers
like replication or task scheduling. It is recommended that you set up a Windows
NT domain user account for the SQL Server Executive. Then you can access files on
other servers, such as a Novell NetWare server or Microsoft LAN Manager and perform
server-to-server replication and scheduling.
NOTE: SQL Server 6.5 has added a new Windows NT user account, installed during
installation/upgrade, called SQLExecutiveCmdExec which de-faults to a member of the
NT local users group. The new user account allows nonsystem administrators (nonSA)
to run the CmdExec task in the security context of the SQLExecutiveCmdExec user.
Step 4: Install SQL Server
The next step is to read the next chapter and begin the installation process.
Use the following worksheet to help you prepare for the installation; use the worksheet
later as a reference:
Developing an Upgrade Strategy and Plan
The plan and strategy for an upgrade is different from the plan and strategy for
a new installation. In an upgrade, you have already decided on a platform and are
currently running SQL Server. You may have many large production databases and hundreds
of users who depend on the databases, or you may have small development databases
with a few users. In many ways, upgrading an existing SQL Server is more critical
than installing a new SQL Server. The existing SQL Server contains data being used
and depended on by your organization.
Now you know why it is important to develop a plan that enables you to upgrade
to the new release; if the upgrade is not successful, your plan should allow you
to return the system to its pre-upgrade state.
Mark and Orryn's First Rule of Upgrading
Never under estimate the difficulty of an upgrade. Remember Mark and Orryn's first
rule of upgrading: expect something to go wrong; when it does, make sure that
you can get the system back and running to its previous state. Creating an upgrade
plan is essential. I was once involved with what was to be a simple upgrade for a
banking organization that gave me a six-hour window to get their high-powered multi-processor
SQL Server upgraded from SQL Server 4.21 to SQL Server 4.21a. No problem, right?
After all, the upgrade was not even a major revision number--just a revision letter.
Nothing could go wrong...NOT! Five hours later, when the SQL Server was still
not working correctly and tech support was trying to resolve the problem, we opted
to restore the system to the pre-upgrade state. Once the SQL Server was restored,
it did not work either! It appears that the problem had to do with the SQL Server
registry entries. This was not a problem because our upgrade plan called for backing
up the system registry. Once the registry was restored, the SQL Server was up and
running with no problems, and the upgrade was pushed off to another day, awaiting
information from tech support. The moral of this story is never underestimate the
potential problems that may be encountered during an upgrade, and be overly cautious.
It's better to have too many files backed up and ready to restore than not enough.
Once you perform a SQL Server 6.5 upgrade on a 6.0 or 4.2x database, there
is no turning back. The upgrade process to version 6.5 makes modifications to the
databases such as new system tables and datatypes that are not supported in SQL Server
versions 6.0 or 4.2x.
NOTE: Upgrades from SQL Server 1.x are not supported with SQL Server 6.x.
OS/2 SQL Server version 4.2x systems can be upgraded, but you must first upgrade
the operating system to Windows NT 3.51.
When upgrading an existing SQL Server to SQL Server 6.5, you have two options
available:
Upgrade the existing SQL Server
Install a new SQL Server and migrate the databases to the new server
Upgrade the Existing SQL Server
You upgrade SQL Server to the next release by running the setup program and selecting
the Upgrade SQL Server option. The upgrade option installs the new SQL Server software
and upgrades the databases with new system tables and datatypes.
Before running a SQL Server upgrade, you must make sure that you have adequate
disk space on the drive where SQL Server is located. Upgrading from SQL Server 6.0
to SQL Server 6.5 requires an additional 20M of free disk space and 2M of free space
in the master database. Upgrading SQL Server 4.2x to version 6.5 requires
the following:
65M of free disk space.
The master database must have at least 9M of free space. If the master
database does not have at least 9M free, the setup program alters the master
database and increases the size of the database.
The installation program automatically increases the size of the master
database. You must make sure that the SQL Server Open Databases configuration parameter
is equal to or greater than the number of databases on your server (including master,
pubs, model, and tempdb). If the parameter is less than
the total number of databases on your system, use the SQL Enterprise Manager or the
system stored procedure sp_configure to increase the value. SQL Server 6.5
added several new keywords and is now fully ANSI-92 compliant. If you are upgrading
from SQL Server 6.0 or 4.2x to SQL Server 6.5, run the utility program CHKUPG65 before
upgrading to SQL Server 6.5. CHKUPG65 checks to make sure that the database status
is fine, that all required comments are in the SQL Server system table syscomments,
and that there are no keyword conflicts in your databases. In SQL Server 6.0, the
CHKUPG65 utility was called CHKUPG. If you are upgrading from SQL Server 6.0 and
have not used any of the published reserved words, you will not have any problems
with keyword conflicts.
TIP: Fix them now or fix them later! Although keyword conflicts found in your
databases do not prevent the successful upgrade of SQL Server from 4.2x to 6.5, you
will have to make the corrections on SQL Server and your applications that reference
the keywords before or after the upgrade.
The syntax for the CHKUPG65 utility is as follows:
CHKUPG65 /Usa /Ppassword /Sservername /ofilename
In this syntax, password is the password for the sa user, servername
is the SQL Server being upgraded, and filename is the output file to print
the CKKUPG65 report. The filename parameter must be fully qualified with
drive, path, and filename. The following is a sample output from the CHKUPG65 utility:
===================================================================
Database: master
Status: 8
(No problem)
Missing objects in Syscomments
None
Keyword conflicts
Column name: MSscheduled_backups.DAY [SQL-92 keyword]
=====================================================================
Database: pubs
Status: 0
(No problem)
Missing objects in Syscomments
None
Keyword conflicts
Column name: sales.DATE [SQL-92 keyword]
If you have problems with syscomments entries, drop and re-create the
objects. Databases with the read only option set to TRUE must be
changed so that the read only option is set to FALSE.
TIP: A full list of the new keywords and future keywords can be found in the
SQL Server documentation. The following keywords have given me trouble during several
4.2x upgrades: CURRENT_TIME, CURRENT_USER, KEY, CURRENT_DATE,
and USER.
CAUTION: This caution does not apply to users upgrading from SQL Server
6.0 to SQL Server 6.5. If you are moving from SQL Server 4.2x to SQL Server
6.5, be prepared after the upgrade to rewrite and recompile some stored procedures.
SQL Server 6.5 is not SQL ANSI-92 compliant. You may get this error message after
upgrading when you try to execute a stored procedure: You must drop and re-create
the stored procedure <stored procedure name>. What's the problem?
Transact SQL treatment of some subqueries and SQL statements such as GROUP BY
were not ANSI-92 compliant. When you upgrade and try to execute these stored procedures
that break ANSI-92 SQL rules, you get the preceding error message. For example, the
following pubs database query works and can be compiled as a stored procedure
with SQL Server version 4.2x:
Select au_id, au_lname
From Authors
Group By (au_id)
With SQL Server 6.5, however, you get an error. To correct the SQL statement for
use with SQL Server 6.5, change the query as follows:
Select au_id, au_lname
from authors
Group By (au_id), (au_lname)
The CHKUPG65 utility does not report ANSI-92 SQL violations in stored procedures.
The Upgrade Plan Before you begin to upgrade an existing SQL Server installation,
it is important to create an upgrade plan. The following sections provide you with
an example of an upgrade plan to upgrade an existing SQL Server installation to SQL
Server 6.5:
Determine whether you have the required disk space.
Make sure that you have the required amount of disk space to upgrade your existing
SQL Server. If you are upgrading a SQL Server 6.0 installation, you need approximately
20M; if you are upgrading a SQL Server 4.2x installation, you need approximately
65M.
Run the CHKUPG65 utility
When upgrading from 4.2x or 6.0, run the CHKUPG65 utility and review the output
report. Correct any errors such as keyword conflicts reported by the utility. Repeat
step 2 until errors are no longer reported.
Estimate downtime and schedule the upgrade with users
Estimate the amount of time you expect the upgrade to take. Remember that the
larger the database, the longer the upgrade will take. Don't forget to give yourself
time to perform any necessary backups before the upgrade begins, time to test the
upgraded server, and time to handle any possible problems--including going back to
the original installation, if necessary. Once you have determined the amount of time
required to perform the upgrade, schedule a date to perform the upgrade with your
users. If you have a Microsoft Technical Support contract, notify tech support of
your upgrade plans and check for any last minute instructions or known problems.
On the day of the upgrade, follow these steps:
Perform database maintenance.
Before backing up the databases, perform the following DBCC commands on each
database: CHECKDB, NEWALLOC, and CHECKCATALOG.
Check the SQL Server Open Databases configuration parameter
Make sure that the SQL Server Open Databases configuration parameter is equal
to or greater than the number of databases on your server (including master,
pubs, model, and tempdb). If the parameter is
less than the total number of databases on your system, use the Enterprise Manager
or the system stored procedure sp_configure to increase the value.
Back up all databases
Perform SQL Server backups on the databases, including the master database.
If possible, shut down SQL Server and use the Windows NT backup facilities to back
up the SQL Server directories, including all the SQL Server devices for possible
restoration.
Back up the NT registry
Back up the NT system registry again, in case you need to restore the system
to the original SQL Server installation.
Turn off read-only on databases
For any databases that have the read only option set to TRUE,
use sp_dboption to set the read only option to FALSE.
The CHKUPG65 utility reports any databases in read-only mode.
Make sure that no SQL Server applications are executing
Before upgrading the SQL Server, ensure that no one is using SQL Server.
Upgrade the server
Run the setup program and select the Upgrade SQL Server option.
The Fall Back Plan A SQL Server upgrade is a straightforward process, but
because you are usually dealing with valuable data and systems that can be down only
for a limited amount of time, upgrades should be treated with extreme caution and
care. Just as important as a good upgrade plan is a good fall back plan in case the
upgrade does not go as smoothly as you hoped. Here are some suggestions on how to
protect yourself. Above all, make sure that you have the backups (tapes, and so on)
to return your SQL Server to its earlier state if necessary.
CAUTION: Always make sure that you have a valid backup of the Windows NT system
registry before starting any upgrade.
Suggestion 1: Complete System Backup Recovery Plan If possible, shut down
the SQL Server before the upgrade and perform a backup of the SQL Server directories
and all the data devices. You must shut down SQL Server to back up files that SQL
Server is using, such as devices. If the upgrade fails for some reason, you can restore
the SQL Server directories, devices, and the NT registry, returning your system to
its earlier setup. Suggestion 2: Complete Database Backups--Reinstall Previous Version
Perform SQL Server database backups on the databases, including the master.
Make sure that you have all the valid SQL Server configuration information such as
the server name, character set, sort order, network configuration, and device and
database layouts. If you cannot get the SQL Server 6.5 upgrade to work correctly,
having the database dumps and the required SQL Server information enables you to
reinstall your previous SQL Server system and reload your databases if necessary.
Suggestion 3: Complete System Backup and Database Backups Perform suggestions 1 and
2. You can never be too careful!
The bottom line is that the information and data completely recover your system
if the upgrade fails. Play it safe. Have a backup plan to use if the backup plan
fails! The Upgrade Checklist Use the checklist on the next page to help prepare for
a SQL Server upgrade. Check off each item on the list as it is completed. Perform
each step in order from top to bottom.
Install a New Server and Migrate the Databases
The option to install a new server does not qualify as an upgrade to an
existing system, but it is mentioned here for two special cases. The first case is
that SQL Server 6.5 can be installed alongside SQL Server 4.2x SQL Server on the
same machine.
CAUTION: You cannot install SQL Server 6.5 alongside SQL Server 6.0 on the
same machine. Because the two products share the same registry entries, installing
SQL Server 6.5 on a machine with SQL Server 6.0 results in an upgrade.
SQL Server 6.x uses a different directory structure and registry entries than
does SQL Server 4.2x. You can run the two SQL Servers simultaneously and migrate
the databases from SQL Server 4.2x to the new SQL Server 6.5 installation. This enables
you to test each database with SQL Server 6.5 and migrate all the databases without
worrying about unexpected problems because the 4.2x installation is still operating
and functional. This option is not for everyone because it requires enough disk space
and memory to support both SQL Servers and your existing databases.
The second case is mentioned for the situation in which an existing SQL Server's
machine is being upgraded to a new machine. In this scenario, you can install SQL
Server on the new machine and migrate the existing databases from the old machine.
If you decide to use either method, you should perform all the normal upgrade
steps and follow the installation procedure for a new SQL Server. Use the Enterprise
Manager interface (Transfer Manager in SQL Server 6.0 and 4.2x) to transfer the databases
and data or the DUMP and LOAD commands.
TIP: When installing version 6.5 alongside a SQL Server 4.2x installation,
remember to change the named-pipes name used for the SQL Server 6.x installation;
otherwise, the SQL Server 6.x will not run correctly. The named-pipes used by SQL
Server 6.x and SQL Server 4.2 are the same: \\.\pipe\sql\query. To change
the named-pipes name for SQL Server 6.x, use the setup program and select the Change
Network option. Make sure that the Named-Pipe checkbox is selected and click OK.
A dialog box displaying the default named-pipe appears. Change the name of the named-pipe
and click the Continue button. Although you can also use the registry editor, regedt32,
this method is not recommended.
Summary
This chapter has helped you prepare for a SQL Server upgrade or installation.
In the next chapter, you walk through the installation and upgrade process.
DISCLAIMER
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.
zanotowane.pldoc.pisz.plpdf.pisz.plpajaa1981.pev.pl