One of the best practices when
building out a consolidated SQL Server environment is to upgrade your
database servers to the latest release. This will buy you a longer
support cycle for the product. Versions like SQL Server 2000 are
completely out of support, and dragging them into a new consolidated
environment is not the best choice. However, there are circumstances
that will prohibit you from upgrading. These include the vendor support
for the applications that are the front end for databases not
supporting a release more current than, say, SQL Server 2000. Also,
internal business decisions to sunset the application could mean your
choice is simply to migrate the physical SQL Server 2000 server to a
virtual one and call it good enough.
Whenever you decide to upgrade the database
servers, there are two important tools to be aware of in the upgrade
process. These tools are the Microsoft SQL Server Upgrade Advisor
(Advisor) and SQL Server Upgrade Assistant (Assistant).
The Advisor tool can be run from a desktop and
points to the server instance you wish to upgrade. The tool goes
through a check of potential issues you may encounter given the server
you wish to upgrade. Examples of some of the potential issues include
the use of deprecated statements like the *= and =* join syntax or extended stored procedures that were pulled from the product.
After the tool evaluates the server, it provides useful reports like the one shown in Figure 1.
Figure 1. SQL Server Upgrade Advisor’s instance report
If you sat for a moment and thought about
situations where this tool may fail you could probably find quite a
few. For example, if we were using encrypted stored procedures the tool
could not obtain the text to parse to evaluate and so on. This tool is
not meant to be the final check in whether or not you can upgrade;
rather, it’s meant to give you a rough idea of the work involved in
upgrading to the latest version. A more thorough tool used in
application compatibility testing is the Assistant tool.
The SQL Server Upgrade Assistant is a joint
development effort by Scalability Experts and Microsoft. The Assistant
tool can be downloaded for free at www.scalabilityexperts.com/tools/downloads.html.
The Assistant tool more
thoroughly tests for compatibility than the Advisor. This tool requires
more resources, primarily time, to effectively use. The idea behind the
tool is to capture a SQL trace of a live production workload (or a
complete set of functional tests within a test environment) and replay
this trace against a separate upgraded instance of SQL Server. Since
the tool is capturing a SQL trace, it will capture every statement that
goes against the source database. This allows the tool to trap errors,
like using undocumented DBCC commands, which the Advisor tool will not
capture. When the SQL trace is replayed against the new environment, a
detailed line-by-line comparison will be presented, as shown in Figure 2.
Figure 2. SQL Server Upgrade Assistant’s Report Viewer
When developing a private cloud
environment for SQL Server, as someone in the DBA role, your tasks will
heavily center on the database inventory and consolidation piece. The
magic of a private cloud has almost everything to do with the
infrastructure—the flexibility of the operating system, the hypervisor,
and disk subsystems. One of the other major pieces of resource pooling
is taking the physical servers and virtualizing them. Products like
Microsoft System Center Virtual Machine Manager (SCVMM) have this
capability. SCVMM adds a ton more value and features on top of the
Hyper-V platform and is a major component in architecting a private
cloud infrastructure.
