SQL Server 2005 RoadShow - Installing & Upgrading
to SQL2K5 (Part II in the series)
In my last article on the SQL
Server 2005 Roadshow (click link to read), I gave you an overall look at the
event itself, and a prime on the content presented there. I provided some background on the keynote, the speaker presentations, the vendor exhibits, and hopefully, a taste of what it was like to attend such a marvelous event. The main breakout session that I cover is the administrative track, which will supply us with the material in our follow-up articles. So, let’s ease on down the road and talk about what you’ll need to know before upgrading and installing SQL Server 2005 in your company.
Note: You can get more information about the SQL Server Road Shows here.
Continuing our series, the first of the administration
sessions, we set out to learn “What will it take to Upgrade to SQL Server
2005.” Indeed there’s a lot to
learn, and I will highlight some of the main differences from 2000, known issues
and workarounds, as well as discuss the methods for installing and upgrading.
Since the agenda here was very organized, I will stick to it as follows:
- SQL
Server 2005 installation enhancements and changes
- Known
issues, solutions and workarounds
- Installation
Checklist
- Upgrading
from previous versions
Installation Enhancements &
Changes
Before you go anywhere with SQL 2K5, you must be aware of
the many changes and improvements to installing the software, as well as the
types of installations used in various scenarios. There is a host of new enhancements that differ from SQL 2000, such as the installation and upgrade of individual components, improved
error logging and failure reporting, instance directory structure, a new system
configuration checker to ensure proper prerequisites, easier deployment for
remote sql servers, as well as unattended and silent installs.
For the new incarnation of our beloved SQL Server,
installation will be windows installer-based, which combines all of its
components into a single product, making each component easily selectable and
configurable, per user preference. In
addition to Analysis Services, Reporting Services, Notification Services being a
separate install from the Database Engine, DTS (now Integration Services), SQL
Profiler, and Database Tuning Advisor are optional installs as well.
Some features and components by default will be disabled or not
automatically installed. This
includes DTS, Replication, Full-Text Search, Service Broker, and SQL Mail.
New to the list of installs, (not discussed in this article) are SQL
Debugging, SQLiMail, and Database Mirroring. The user will have the ability to
add and remove all or any of these components via the Add/Remove Programs
option.
The structure of the setup program reflects its Windows
Installer-based character, and is sure to make installation more
straightforward. Although the
options for the user will be much greater, by compartmentalizing the
installation components, it will certainly streamline management and maintenance
of your SQL Server deployment.
For example, applying patches and services packs will
become easier to implement and maintain than previous versions. With
improvements to SQL Server 2005, you can incorporate hot fixes and service packs
by not only replacing older versions of files in their source, but also download
updates automatically at the time of the installation.
Now, with improved error log reporting, alerts are more
detailed, and each component install has a separate log.
You’ll be in a better position to not only understand why your sql
server installation has failed, the setup program will even make suggestions on
what corrective action to take based on the log output.
The directory naming convention has changed, where
different instance directories are created for each individual service (ie
Engine, Analysis Services, Reporting Services, etc.)
In addition, instance names in the registry will be referenced with its
corresponding directory. The new file structure also helps support high
availability upgrades, where a new instance is created in parallel with the old
instance staying online. It will
continue to run until the new instance is ready, and only then will the service
be stopped and cut over to the new one. One
contrary aspect to point out is that the ability to rename instances is not
supported in SQL2K5.
So, how many SQL2K5 instances can reside on one
server? Microsoft says a maximum of
50 instances per server are supported, but that’s not all.
It is possible to have running at the same time in addition to the 50 SQL
Instances, 50 instances of Analysis Services, 50 Full-Text, and 50 Reporting
Services! That’s a lot of
instances, but with 64-bit technology and WOW, if you’ve got the hardware,
then such an implementation could be manageable.
You can also have installed different versions (2000 and 2005), as well
as varying editions of SQL Server – (ie: Enterprise, Developer and Express can
play nicely together on the same box.
Always a bit tense when first performing a new sql
installation, hoping and crossing your fingers that all the prerequisites have
been met, and that the OS and H/W environment measures up?
Well, have no fear; a neat new utility will check all this out for you
– in advance. The System
Configuration Checker will be your guide and ensure that all the installation
prerequisites are in order, before the installation itself begins.
Taking advantage of Windows Management Instrumentation (WMI) technology,
it will check for all the necessary requirements, and if they are not all met,
will prevent the installation from taking place.
These blocking requirements include: Availability of WMI Service, OS
service pack level, minimum requirements and SQL Server compatibility with OS,
check H/W specs, pending reboots (files required for setup locked), availability
of performance counters, path permissions, and no compression.
The SCC also identifies other requirements, which do not block
installation. For example, IIS, MDAC, and other existing sql server editions.
Furthermore, missing components such as .NET Framework v.2.0, Native Client and
Setup Support files are detected and automatically installed for you.
One of the other cool things I want to mention is
support for Remote Installation. Via
the command line you can enter parameters for the destination computer, with
admin account and password. If
you’ve ever had to deal with image-based deployment of SQL Server on multiple
machines, based on your predefined installation, you most likely ran into the
headache of having to manually change the server name.
Well, now upon immediate completion of installation, SQL Server will
check if the computer name has changed, and update this automatically.
Moreover, the need to run setup to reset the computer name is eliminated
– simply change the name, restart the service, and update the sysservers table
using sp_dropserver/sp_addserver.
Known Setup Issues
What would a Microsoft release be, without some known setup
issues? Many of these you may be
aware of from previous versions, and some new ones to look out for. Let us
review all of them - just in case you memory fails you after waiting so long to
install a new version of SQL Server 😉
First some setup defaults, which being security conscious
as MS is, the following SQL Server 2005 features and components are disabled by
default:
- Analysis Services
- Database Mirroring
- SQL Debugging
- DTS Service
- Notification Services
- Replication
- Reporting Services
- Service Broker
- SQLiMail
- Sample Databases
- Xp_web
And, not too mention, which wasn’t, and I actually brought it up during the session,
- Xp_cmdshell
If you try to use xp_cmdshell, you may receive this: “Msg 15501,
Level 16, State 1, Procedure xp_cmdshell, Line 1 This module has been marked
OFF. Turn on 'xp_cmdshell' in order to be able to access the module.”
Definitely, a step forward in making our SQL Server implementations more
secure. So it appears in order to further their efforts in making their server
products more secure, Microsoft has graciously turned off xp_cmdshell by
default. Not a bad idea. If you never use it, then you’ve got a
little bit of added security by default. And, I guess if you can figure out
how to turn it on, then you know enough about Sql Server to know how to secure
it too.
Things to avoid when running setup to install SQL Server across the network,
is NOT to use mapped drives. Be
careful to make sure that if you use a UNC path, that it has not been mapped to
a drive letter, as it may cause setup failure.
As of Beta 2, changing the Windows service account under which any SQL Server
Service runs is not supported in this release. This should change by the time RTM rolls around.
Speaking of Service Accounts, don’t forget about the required
permissions for the SQL Server Agent. It must be a member of the sysadmin fixed server role, and have the usual Windows permissions:
- Log on as a service;
- Log on as a batch job;
- Replace a process level token;
- Adjust memory quotes for a process;
- Act as part of the OS;
- Bypass traverse checking.
With all the talk (see above) of how neat the System Config Checker is, it does come with a caveat. The SCC may indeed fail with a “Performance Monitor Counter Check Failed” message, and will block the Setup program from continuing with installation. This is because it cannot verify an existing registry key or run the lodctr.exe to load the counters. To continue the setup, you must manually increment the registry key. (There is a help file in \setup\help\1033\setupsql9.chm to assist)
Also, although I mentioned earlier that multiple instances of varying editions can reside on the same server, the client installs are not as accommodating. Specifically, and while occurrence of this is sporadic, the client components for SQL Server Express may prevent installation of other edition client components.
For Reporting Services, ASP.NET must be enabled on Windows 2003 before installation. In addition, it may fail to install on a server that is case-sensitive, if the wrong case for the account name is used. Another gotcha is if the CSP service (Cryptographic Services is stopped or disabled on Win2K3, a Windows Logo requirement message appears and setup fails. Finally, on this topic, no longer are the old network protocols like Banyan Vines, Multi-Protocol and AppleTalk supported. If you’re using anything other than Named Pipes and TCP/IP by now, then perhaps a new line work is in order :-). Since the presenter brought it up, I figured I might as well mention it too.
Installation Checklist
Now, on to something that every manager asks you to provide, and every DBA should have – an installation checklist. There are several important things to consider each time whether you are ready to install a new sql server engine or component, or upgrading from previous versions. Some are very typical and obvious, such as checking system requirements, reviewing security issues, and taking a backup of your current sql server instance if you are upgrading. Others, such as shutting down anti-virus programs, ensuring administrator permissions on the target machine and proper service account rights, are not always obvious. Once your setup is complete, and the latest service packs applied, you’ll want to verify your installation, by checking the summary log file for output, make sure the services are running, and launching the program. Rather than listing each bullet point here, I have posted the DBA’s
checklist in the Contact Us/Resource Section of my site (http://www.pearlknows.com) for your acquisition. You can use this as your prime for SQL2K5, and incorporate it with your company’s specific business requirements.
Upgrading from Previous Versions
Finally, in our last discussion topic, we will review all the things you need
to know to get to SQL2K5, and the proper upgrade paths.
Let’s break it down as follows, by talking about:
- Supported & Unsupported Upgrades
- Edition Upgrade
- Binaries and Data Files Upgrade
- Upgrade Methods
- Upgrading In-Place
- Component Upgrade
- Upgrade Issues and Considerations
What is a supported upgrade? To upgrade to SQL2K5, you will need to be coming from a SQL Server 2000 with SP3/SP3a or later. Existing SQL Server instances will be upgraded and migrated to new folder and registry locations. Build-to-build upgrades will allow you to upgrade from one edition to another, as well as from your Beta2 install. (This will save time, rather than having to reinstall the whole
kit and caboodle). Upgrades from SQL2K5 Beta software prior to version 2 is not
supported. (In fact, Beta 1 users are not at all supported on this release, and
there are several data access failure points from connecting with via MDAC 9.0,
the Native SQL Client, or previous MS VS 2005 versions – so don’t bother)
Any edition from SQL Server 2000, can be upgraded to SQL2K5.
These include Enterprise, Developer, Standard, Personal, and Evaluation
editions. In order of precedence,
low to high, Desktop can be upgraded to Express, Standard, Developer and
Enterprise Editions (XSDE); Personal to S,D,E; Standard to D and E, and
Developer to Enterprise edition.
A straight upgrade of the installation files (the binaries), from SQL2K to
SQL2K5 is no problem and the quickest upgrade path during setup. As in v2000,
the upgrade of the data files is supported for version 7.0, but no direct
upgrade available. This is accomplished by using upgrade methods outside of
setup, such as the traditional backup and restore, detach and attach (excluding
system db’s), and the copy database wizard.
However, if you still have legacy installs out there lurking on v.6.5,
you will not be able to directly upgrade to SQL2K5 – no surprises there.
You won’t be able use the setup to upgrade instances that reside on
clustered nodes, and no direct upgrades from MSDE.
And just in case your down with 64-bit implementations of SQL 2000,
that’s not supported either.
SQL2K5 will allow in-line upgrade wherein you can perform side-by-side
installation of the newer binaries, to reside along side with the existing older
ones. Older and newer registry keys
will also exist side-by-side. As
discussed above earlier, this will allow for very limited downtime when
upgrading, and comes online as soon as the services are restarted, now using the
new files. The user may now
uninstall or delete the old binaries and registry keys at his or her
convenience.
Another useful aspect of the in-place upgrade is the unique instance ID’s
are generated at the time of install. The
directory and registry naming convention will allow the upgrade of a single
component to future SQL versions without dependencies on other SQL Server
components. The previous directory structure gets copied into the new one, using
the instance id created. For example, you will see an instance ID like MSSQL.x
for each service component.
SQL server components such as Replication, SQL Agent, Full-Text Search, and
Analysis Service are part of the core upgrade.
For Analysis services, data migration to a new instance is required as
part of the upgrade, using the migration wizard.
In addition, some other upgrade issues and considerations to keep in mind.
Where the data integrity of index can no longer be guaranteed, or a
collation that changes during the upgrade wherein an index is no longer sorted
correctly, constraints and indexes will be automatically disabled.
“Sys” is now a reserved name in SQL2K5, thus eliminating the system
tables, and instead creating catalog views prefaced with “sys”.
Therefore, due to the new engine design, (which we will discuss in a
future follow-up article), any user-defined users with the name “sys” will
cause the upgrade to fail. Be sure to drop any user-defined login first, that has the
same name as one of the fixed server roles (sysadmin, serveradmin, setupadmin,
etc.) Also, any maintenance scripts
you may have that query system tables should be modified to reflect the new
catalog views.
Those using Full-Text Search need to know that its indexes will be rebuilt
during the upgrade, and downtime should be planned, especially if the catalog is
very large. MS does say, however,
that the rebuild times have been remarkably improved 400% faster.
For a more in-depth analysis of the setup process, click on the hyperlink for
another good article on Setup
and Deployment. To check for
updates and changes in the SQL2K5 code base before final RTM, you visit the
newsgroups at http://msdn.microsoft.com/SQL/2005/default.aspx.
Well, that’s a lot of material we covered, and that’s just installing and
upgrading. By reengineering the
setup process, installation is much more flexible and straightforward.
Do pay attention to detail, and take the time to plan your upgrade
strategy. Hopefully, this latest
article will have provided you with the foundation to begin thinking about and
planning to install and upgrade to SQL2K5.
Please stay tuned to more of my follow-up articles from the 2005 RoadShow
– that’s all for now :-).
Written by: Robert
Pearl, President
Pearl Knowledge Solutions, Inc.
Copyright © 2005 - All Rights Reserved.
Note: Not to be reprinted or published without express permission of the
author.
When
SQL is the Center of Your Universe!
