To customize this column to your needs, we want to invite you to submit your ideas about topics that interest you and issues that you want to see addressed in future Knowledge Base articles and Support Voice columns. You can submit your ideas and feedback using the Ask For It
(http://support.microsoft.com/common/survey.aspx?scid=sw;en;1301)
form. There's also a link to the form at the bottom of this column.
Welcome! I am Sandy Yakob with the Microsoft SQL Server
Content team. I will be your hostess for what I hope are many future SQL Server
Support Voice columns. A quick note about me: I have been with Microsoft for 14
years, and for the past three years I have been working in the SQL Server
Content team.
We hope to provide a new column every other month. For
this month's column, Vishal and Suresh will cover the topic "Getting ready for
SQL Server 2005." I hope that you enjoy the column and find it helpful. The
release date for SQL Server 2005 is fast approaching. In this edition of the
Support Voice column, we will discuss the various ways that you can prepare to
deploy SQL Server 2005.
The first thing you want to do is to familiarize yourself with the
new architecture of SQL Server 2005, the new features, and the changes and
enhancements that have been incorporated into SQL Server 2005. There are
several resources available to help you with this effort. The following
resources and content are available:
SQL Server Books Online contains a lot of information that
will help you with planning and organizing information to help you get ready
for SQL Server 2005. You can download the complete SQL Server Books Online from
the following Microsoft Web site:
You will have to download the installer files and perform the
installation to obtain SQL Server Books Online for an individual installation
on your computer. When you browse through the contents of SQL Server Books
Online, you will find a topic titled "What’s New in SQL Server 2005." This
topic has several categories under which the different enhancements are
discussed in detail. This topic is a good place to start your journey into the
exciting world of SQL Server 2005.
There are several Webcasts that are delivered by members
of the SQL Server development team and the SQL Server escalation team. These
Webcasts discuss the enhancements that are available in SQL Server 2005. Many
of these Webcasts are targeted toward new tools and techniques available for
troubleshooting different issues and problems. These are available for
on-demand viewing on the following Microsoft Web site:
Several more Webcasts are planned for delivery in the near
future.
Several members of the SQL Server development team have
spent significant time generating white papers that contain good quality
information about the different features and how they may apply to your
specific situation. These white papers are detailed and contain most of the
relevant information. You can read these white papers on the following
Microsoft Web sites:
Several new features are introduced in SQL Server 2005.
Some of the existing features from SQL Server 2000 have been enhanced to
provide more scalability, robustness, and availability. Based on the targeted
environments, some of these features are available in various editions of SQL
Server 2005. You will want to carefully review the availability of the features
that correspond to certain editions. You can find this information in the
"Features supported by the editions of SQL Server 2005" topic in SQL Server
Books Online. This information is also available on the following Microsoft Web
site:
The next important aspect of getting ready for SQL Server 2005 is
planning how to set up a new instance of SQL Server 2005 or upgrade from SQL
Server 7.0 or SQL Server 2000 to SQL Server 2005. There are several resources
available to help you with this effort:
Every product will have a specific set of software and
hardware requirements to function correctly. Similarly, SQL Server 2005 also
has a comprehensive set of software and hardware requirements. These
requirements differ based on various factors, including the edition, the
operating system, the features, and many other combinations. You can find these
requirements and analyze them in the "Hardware and software requirements for
installing SQL Server 2005" topic in SQL Server Books Online.
There may be situations where the person who is planning or
performing the installation of SQL Server 2005 may overlook a specific
requirement. Generally, this oversight would cause a setup failure or an
incorrect installation of SQL Server 2005. SQL Server 2005 introduces the
System Configuration Checker tool. The System Configuration Checker tool is
launched as part of the initial setup process. This tool can detect problems
with the requirements for performing a successful installation of SQL Server
2005 and will notify the person performing the installation. Based on this
report, you can take corrective actions, and then perform the installation or
the setup. This tool helps with appropriate identification of potential
problems with setup and minimizes the time that you will spend setting up SQL
Server 2005. You can find a list of parameters that are evaluated by the System
Configuration Checker tool and possible remedies in the "Check parameters for
the System Configuration Checker" topic in SQL Server Books Online.
There is a new tool that can help with issues that may
occur when you upgrade an existing instance of SQL Server 7.0 or SQL Server
2000 to SQL Server 2005. The Upgrade Advisor tool works in a way that is
similar to the way that the Setup program provides an early warning of
potential problems. We strongly recommend that you use this tool to identify
any sort of incompatibility or possible incompatibility issues that may occur
after or during the upgrade of the instance and the databases. The Upgrade
Advisor tool performs an analysis of various components of the existing
installation, and then generates a report. You can install the Upgrade Advisor
tool from the SQL Server 2005 CD-ROM, or you can download the tool from the
following Microsoft Web site:
After you make sure that you have all the required resources and
you have evaluated any upgrade issues, you should make sure that your
application will behave the same way in SQL Server 2005 as it did in earlier
versions of SQL Server. You should be very careful when you do this. Because of
the many enhancements and changes that were made in SQL Server 2005, it is
possible that some of the application components behave the same way while some
of them do not, including error messages and conditions, application
performance and responsiveness, as well general application behavior and the
flow of logic. For some applications, you may be able to predict these possible
changes, but in a majority of applications, you will have to perform some level
of testing to understand the behavior of the application against SQL Server
2005. Yes, this is a point we want you to consider seriously. You will be
thankful and relieved later when you end up with a very successful installation
or upgrade if you devote the proper amount of time and resources to test your
applications against SQL Server 2005. You will have your own testing methods
that your organization follows. You can also use the following three tools to
help you with this process:
SQL Profiler replay: In most environments, it will be impossible to set up the actual
application to perform the tests (for example, in a production environment).
Most of the robust test tools exist for that reason. If you would like to
simulate the same load that SQL Server encounters in a production environment,
you should evaluate the use of the replay mechanism that is available with the
SQL Profiler tool. This mechanism lets you capture a SQL Server trace file that
contains information about the activity against SQL Server, and then replay it
against the same server or a different server. You will have to capture a
specific set of events, and make sure that the basic inputs are configured
properly for the replay to work. For more information about prerequisites,
setup, and performing the replay, see the "Replaying traces" topic in SQL
Server Books Online. By using this method, you can isolate problems that could
prevent you from a successful application deployment in a SQL Server 2005
environment.
Make sure that you save the Profiler replay trace of SQL
Server as soon as you have finished the testing. This trace can be very helpful
both for you and Microsoft Customer Support Services (CSS) to understand the
difference between SQL Server 2000 and SQL Server 2005 if you experience any
performance issues with SQL Server 2005.
Database Engine Tuning Advisor: You might have designed and created the database schema to
support your application, and it might work very well in the existing version
of SQL Server. Before you upgrade or deploy your application against SQL Server
2005, you might want to make sure that your current database design is suitable
for the new feature set that you will use. You might also want to consider how
an enhanced feature will respond to application requests. You may have to make
some modifications to the underlying index specifications for your application
to work efficiently with SQL Server 2005. To help with these efforts, you can
use the Database Engine Tuning Advisor. You can definitely use workloads that
you created for replay with the tuning task. The Database Engine Tuning Advisor
can make suggestions based on the workload that you supplied, and you can use
the new features to make improvements. For more information, see the "Database
Engine Tuning Advisor reference" topic in SQL Server Books Online.
Dynamic management views and functions: Dynamic management views and functions reveal server state
information that may be used to monitor the health of a server instance,
diagnose problems, and tune performance. You can use these dynamic management
views to understand the capacity and resource utilization of SQL Server 2005
when you are replaying the traces or performing stress tests to understand the
behavior. These views reveal a lot of information that could help you to
identify any bottlenecks that the system experiences when you perform these
early stress tests. For more information, see the "Dynamic management objects"
topic in SQL Server Books Online.
To successfully upgrade from SQL Server 2000 to SQL Server 2005,
it is very important to examine the health of your databases and the server as
a whole. The following checklist is a sample used by Customer Support to
guarantee a smooth transition while working on proactive upgrade engagements.
You might want to design something that could help you get a step closer to a
successful upgrade. This checklist is not complete, and you may want to add or
subtract steps based on your requirements:
Run the Upgrade Advisor tool to identify potential blocking
issues and backward compatibility problems.
Make sure that the computer on which you are installing
SQL Server 2005 does not have any hardware errors.
Run the DBCC Checkdb command on all the user databases and the system database to make
sure that the databases are clean.
Make sure that the backups of the SQL Server 2000 user and
system databases are current and in a safe location.
Stop all scheduled SQL Server Agent jobs, and make sure
that no conflicts exist.
Stop all third-party software that is running on the
server.
Verify the rights of the logged-in user who is installing
SQL Server 2005 or upgrading SQL Server 2000 to SQL Server 2005.
Make sure that no databases are set to Read Only.
Review the Setup Consistency Checker output, and then
address any error messages that you received.
Finish the installation.
Back up the new installation databases.
Place the previous backups and the new backups in a safe
location.
Because there are so many changes in any new product release, some
of the commands, interfaces, features, and behaviors may be changed,
discontinued, or deprecated. You will definitely have to analyze the changes
because some breaking changes might stop you from deploying your application
against SQL Server 2005. It is better for you to understand these changes and
limitations very well in advance for a smooth transition to SQL Server 2005. A
lot of work has been started to reveal all this information to users and
administrators. In SQL Server Books Online, there is a complete list of
discontinued functionalities, deprecation announcements, breaking changes, and
behavior changes that are present in SQL Server 2005. For more information, see
the "Backward compatibility" topic in SQL Server Books Online. This topic has
all the details listed according to the components that you may be interested
in. Additionally, SQL Profiler has two new event classes that reveal this
information when SQL Server encounters any one of these situations. The new
event classes are the Deprecation Announcement event class and the Deprecation Final Support event class.
When you run the Upgrade Advisor tool, it
will generate a report of your current SQL Server 2000 instance, and point out
any known backward compatibility issues between SQL Server 2000 and SQL Server
2005.
It is always useful to know some of the issues that you will
experience when you are working with a new product. This information will save
you valuable time and effort. You can view the articles that discuss known
issues and problems that were uncovered during the Beta timeframe and their
possible resolutions. To do this, perform an advanced search for "SQL Server
2005 Beta" content on the following Microsoft Web site:
I’d like to thank Suresh Kandoth and Vishal Anand for their
hard work on this article. Suresh Kandoth is an escalation engineer with the
SQL Server Support team. He has been with Microsoft for the past four years
working on SQL Server technologies. He has in-depth experience on several
Microsoft Windows server products and enjoys supporting customers and debugging
problems. Vishal Anand is a technical lead with the SQL Server Support team and
has been focused on SQL Server 2005 for more than one year. He has been with
Microsoft for the past four years and has also worked as a database
administrator. Vishal enjoys working with customers and solving problems. Hope
you enjoyed this month’s topic! Thanks for reading! As always, feel free to
submit ideas on topics you want addressed in future columns or in the Knowledge
Base using the
Ask For
It
(http://support.microsoft.com/common/survey.aspx?scid=sw;en;1301)
form.
Back to the top
