Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Installing and Upgrading to SQL Server 2005

By Robert Pearl, (first published: 2005/06/29)

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.

    rsp05@pearlknows.com
    http://www.pearlknows.com

    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!

Total article views: 199067 | Views in the last 30 days: 139
 
Related Articles
FORUM

SQL Server 2008R2 Setup Support files installation fails in Win Server 2003 and 2008

SQL Server 2008R2 Setup Support files installation fails in Win Server 2003 and 2008

FORUM

SQL Server services goes down during HyperTerminal setup

SQL Server services goes down during HyperTerminal setup

FORUM

SQL Server 2005 Upgrade - Service pack 2

SQL 2005 upgrade to Service Pack 2

FORUM

Automating SQL Server Service Pack installation

Need to automate Service Pack Installation on servers without any Night watching

FORUM

SQL Server 2008 Service Pack Installation Issue

SQL Server 2008 Service Pack Installation Issue

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones