The most important component of any upgrade, consolidation, or migration is having adequate technical information about each system. Without this, you cannot proceed successfully with any of your efforts. If you do not know if you can combine a workload, it is impossible to make any decisions.

Each system that is being considered must be profiled, and not for just one day or, sometimes, even just one week. The profile is the result of monitoring over time to gather trends. These trends might be daily or weekly jobs, as well as daily, weekly, and even monthly trends in workload (for example, end-of-month sales reports). Profiling includes much more than just the system name: every important performance aspect of the server must be captured. When figuring out if workloads and systems can be combined, you need to determine if incompatibilities will result from, say, a configuration setting or an application requirement.

Besides capturing general items such as operating system version, service pack levels, IP addresses, and disk configurations through the worksheets, the specific settings for each application also need to be captured. SQL Server provides the following methods to assist in the documentation of your systems:

You should also document all of the objects associated with each database. This includes stored procedures, jobs, logins, and system-level logins as well as their corresponding database users, maintenance plans, and so on, even if they reside outside of the database. You will use this information later to determine conflicts, such as duplicate stored procedure names and logins or differences in naming standardizations. If you want, run the same DBCC commands on each system. Document anything and everything, even if you think it might not be relevant. Include linked servers, modified system databases, and so on.

Gathering performance metrics from your server is more than a one-time event. To truly understand what is going on with your system, performance metrics must be done over a period of time and then analyzed. At a minimum, you should gather statistics about your systems for one business week. During that time, record numbers when there is little to no load, medium load, and heavy load. Gathering numbers only at times that show one extreme or the other does not paint an accurate picture of system use.

Before you can consider any other technical aspect, you must decide if you will be using one instance with many databases or multiple instances, each with its own set of databases. Instances were first introduced in SQL Server 2000. One instance of SQL Server 2000 equates to one installation of SQL Server on your machine. Prior to SQL Server 2000, you could only have one installation of SQL Server per physical server. There are two types of instances: default and named. A default instance is analogous to the current functionality of previous versions of SQL Server. A named instance of SQL Server is exactly like it sounds: you name your SQL installation with a unique name. SQL Server 2000 supports up to 16 instances on one physical server or in a single Windows server cluster, regardless of the number of physical nodes comprising the cluster. In other words, you can have 16 named instances or one default and 15 named instances. Consult SQL Server Books Online for any changes in the number of instances supported.

Each instance gets its own set of core binaries, but they share underlying components such as MDAC, Microsoft Distributed Transaction Coordinator (MS DTC), and one Microsoft Search service. If there is more than one instance of SQL Server, each has the ability to stay at a certain service pack level, with the exception of any shared components. This is a benefit for a consolidation effort, because you can have instances at the specific levels at which you need them; however, for example, if you need to install a SQL Server 2000 service pack and it for some reason requires a physical reboot, you will affect all other services running on that server. In general, once you have upgraded one instance to that service pack level you will not have to reboot to upgrade the other instances because these shared files have already been replaced. Regardless, your availability and SLAs must be taken into account when designing consolidated SQL Servers.

From a performance perspective, each instance will get its own memory, including cache for stored procedures. Is it better for your endeavor to put hundreds of databases under one instance or to spread them out over more than one? The "Memory and Processor" section later in this chapter delves into this topic in more depth.

In most cases, a single instance means less administrative overhead: with only one instance to manage, the complexity of what goes on in SQL Server can be easily managed and localized. It also ensures that "automatic" settings, such as using dynamic memory, are easier to consider if there are no other processes to contend with. If you have limited hardware, this might be your best option.

Multiple instances allow for each instance to cater to a different set of rules, SLAs, and so on. The biggest advantage is that you can stop the services for each instance individually. Multiple instances also mean more complexity when it comes to system design and management on all levels, but the rewards of dedicated memory and separate processes (with the exception of shared components) might be worth the trade-off.

When thinking about single versus multiple instances—as stated in the introductory paragraph of this section—I/O, processor, and memory are the key considerations.

Because the architecture of SQL Server 2000 is different with instances, in particular named instances, you should test older applications to ensure that they still work properly. Named instances technically require MDAC 2.6 to connect properly. A default instance should be fine. In the event there is a problem if named instances are used, there are a few potential ways to solve any issues if the application cannot connect to a named instance:

The first stop for any SQL Server system should be the architecture of its disk subsystem. If you are looking to combine workloads or to upgrade, it is imperative to know not only how much disk space you will need but, if you are consolidating, what workloads will work well together from an I/O perspective. That is a serious challenge faced by customers both big and small. Read-only reporting databases have different I/O requirements than heavily used OLTP systems with many writes and a smaller number of reads. Do not take this configuration lightly; you might initially save cost by consolidating, but if you have underperforming applications, the consolidated environment will be a failure.

There are a few important considerations when determining how many processors the new servers will contain. As with disk and memory, prior benchmarking and application and system profiling data will need to exist to help you make a more accurate decision. Here are some of the main issues:

You might now encounter more network traffic flowing to one SQL Server. As with any other planning consideration, do you currently know how many concurrent users are usually connected to the SQL Server at various times, especially under maximum load? From a memory perspective, each connection consumes 12 KB added to the network packet size multiplied by three:

Therefore, in addition to the memory required for the normal SQL Server usage for things like procedure cache and queries, you need to take into account network connections. This factors into whether you decide to consolidate on one instance or multiple instances.

Once you know how much memory user connections take up for each database, you need to worry about network bandwidth. Can what is coming in and going out be handled by the proposed networking on the server? For example, if you have one 10-Mb card but your overall required throughput is 20 MB, do you add another network card and IP address (both of which add a bit of system overhead) or get a higher bandwidth network card and ensure that the network itself can handle the load?

Other networking issues include the following:

Security is an obvious concern. Are there different standards for all of the applications, databases, and servers targeted? If there are, will they be in conflict with one another if they are combined? Or, for example, is something like IPSec required, but you are consolidating to a cluster where IPSec is not designed for failover, meaning the application will need to be changed or possibly not consolidated but put instead on its own instance or server?

Logins and how applications and users actually access SQL Server are the biggest areas of concern for security in a consolidation effort. User migration could consume a great deal of planning time. The "Networking" section earlier in this chapter already mentioned domain connectivity and domain accounts, but here the focus is on the actual logins. Do you have servers that have a mix of both Windows Authentication and mixed mode? Will that introduce risk when workloads are combined?

Also, security has changed a bit if you are only familiar with SQL Server 6.5 and earlier. SQL Server 7.0 introduced the concept of roles, so you might want to rethink how security is done for a particular application. This might become scope creep, so do not consider or implement it if it will impede the consolidation effort.

Here are some issues to consider with logins:

Each instance of SQL Server 2000 needs to be made highly available to meet your SLAs. Because this book is all about high availability, putting solutions together, planning, and so on, this section is pretty self-evident.

If replication is configured on any, or many, of the databases being considered, planning will certainly be impacted. Each database or publisher needs a distributor, and the distributor can live on the publisher, the subscribers, or be hosted on a separate system. Do you plan on having a 1:1 ratio of publishers to distributors? That is probably unrealistic in a consolidated environment, which means that you will need to do some up-front capacity planning to ensure that the distributor can handle multiple publishers. Disk I/O will be impacted greatly, followed by processor and memory, especially depending on the type of replication implemented. This also means that the distributor will have to exist in a completely separate instance or, most likely, on a different server to ensure good performance and high availability. You might consider a remote distributor as well. Remote distributors for various publishers can be consolidated under one SQL Server instance, but distribution databases per publisher should be distinct. One instance with a publisher and distributor might be acceptable in a nonconsolidated environment, but it will not be acceptable in a consolidated environment.

You also have to consider the base snapshot directory and its location per publisher: will you share one per SQL Server instance or use another one?

Also consider the subscribers, as they will be impacted, too, if you move publishers and distributors. This is important if you have to disable replication during the implementation process.

One of the hardest tasks for any upgrade, consolidation, or migration effort will be migrating objects, such as alerts, SQL Server Agent jobs, and Data Transformation Services (DTS) packages. For alerts, jobs, and operators, you can easily script them; however, before applying them to the consolidated SQL Server, ensure that there are no naming conflicts and that when you migrate to the new server, the proper user is associated with SQL Server Agent jobs. For DTS packages, you have a few options to migrate the packages to other servers, but once you move them, you might need to make modifications to them. Do not assume that they will run as is. Also ensure that the package is compatible. As of SQL Server 7.0 Service Pack 2, Microsoft changed the format from SQL Server 7.0 RTM and Service Pack 1. In that case, you would have to completely re-create the package under SQL Server 2000. Remember to account for time in your testing phase for testing and possibly fixing these types of issues.

Prior to migration, think about the following considerations that impact the eventual administration of each consolidated SQL Server instance:

Chargeback is the process of assessing the cost to utilization of the hardware so business units or customers can be charged appropriately. If chargeback is a consideration, you have a few options. One is to use SQL Server. Third-party tools such as ARMTech from Aurema can assist you in your cost accounting for system resource usage.

System databases are an important consideration, especially in a consolidation effort. The system databases contain data and structures that are relevant to the entire server installation. Unlike user databases that can exist independently of each other, system databases are closely associated with each other as well as to the user databases from the source server.

How will master, model, and msdb be configured? Because you are now combining multiple servers, each with its own copy of the databases, you cannot just restore multiple copies, as the last restore will overwrite the previous copy. Remember that msdb includes all of your jobs, alerts, and operators (as well as history for some features such as log shipping), so plan on a size of at least 45 MB, and then add the additional amount per instance as well as about 10 percent overage.

Besides these space requirements, you need to analyze master, model, tempdb, and msdb to detect duplicate database objects and document them. This will extend from logins, stored procedures, user-defined data types, and tasks through to the specifics of objects. For instance, an object of the same name will represent or perform actions particular to the source server, or an object of a different name will represent or perform actions that accept modifications at the server level. This becomes particularly difficult when modifications have been made to these databases outside of logins. Remember to include any and all startup stored procedures.

You must identify any elements that are not part of a standard SQL Server build or installation, whether they are modifications or new objects. Review each object for relevance in a consolidated environment. Review each object to determine if duplicity exists at a more granular level. Review each object for explicit references, such as path names, server names, and task names. Do not address tasks found in msdb, as those are handled separately and must be dealt with on their own. Only search for nondefault objects found in tempdb. Also consider in advance how you will respond to application needs for previous compatibility levels. In general, these tips can save you much trouble in having to "update" applications, but remember that being fully on the current version usually minimizes support difficulty and costs.

You must also take into account the collations and sort orders between servers. It is more likely that databases of varying collations will coexist in a consolidated environment than in numerous stand-alone servers. You can encounter unexpected results after migrating to a consolidated environment, and you must realize that certain applications depend on the collation and sort order setting of the database. If they are not set properly, the applications will not work after the switch to the consolidated environment.

Temporary tables will be created in tempdb with collation settings of the tempdb if the collation syntax is not included. Passwords in a case-insensitive SQL Server are converted to uppercase before being stored or used. Passwords in a case-sensitive SQL Server are not converted to uppercase. Because of this difference, passwords originally encrypted on a case-sensitive server and later transferred to a case-insensitive server cannot be used unless all alphabetic characters in the password are uppercase.

Similarly, understand how you need NULL to behave in your queries. If you turn the database option ANSI_NULLS on, all comparisons to a null value evaluate to NULL, which equates to unknown. If this option is off, comparisons of non-Unicode data evaluate as true if both values are null (that is, NULL = NULL is true). This is set to Off by default, so you should see the latter behavior. Also look at any other ANSI-related setting that might affect your environment.

Here are other technical considerations to think about prior to migration:

When you start to design the new environment, you need to take into account every aspect from the ground up: administration and operations (including monitoring), performance, backup and recovery, chargeback (if necessary), disaster recovery, high availability, security, and so forth. Do not assume that you can just use what you have in place for your current system, as the new environment might be different. Also, with more databases and SQL Server instances, you will have different rules and options that need to be reconciled. At this stage, if you identify new tools that need to be either built or acquired, document and plan for those, too.

One of the important factors for the DBA is how the applications, users, and data will be migrated to the new consolidated environment. At this stage, any potential SQL Server 6.5 and SQL Server 7.0 migration issues should be completely isolated and identified, from Transact-SQL incompatibilities to system settings and everything in between. You might also need an intermediate staging server. Determine the order in which you will migrate the applications, users, and data. Resolve any conflicts in object names and location. Worry about the end users’ experience and how you will make the effort as transparent as possible for them. Also determine how you will notify your end users about the move. How will business processes be impacted?

Avoid scope creep. You are consolidating, not adding new functionality or enhancements to applications or data schemas. Problems could be identified during the migration planning that, as long as they will not break in the consolidated environment, should be left alone. Once you have consolidated, you might want to consider these problems, but trying to solve them at this stage only causes the consolidation to take much longer than necessary.

Finally, do not waste time adding applications to a consolidation until the application is reasonably stable. Stability is crucial; many of the troubleshooting tools that might be required to diagnose the flaky application (for example extra perfmon counters, debug builds, and so on) could adversely affect the performance of the entire system.

Prior to actually doing the planned upgrade or consolidation in a production environment, you must build and test all of the new procedures, tools, and the entire migration. The capacity and capability of the environment used for testing should be the same as, if not close to, the final production environment. However, you may be constrained by budget, resources, and other limitations. The importance of this stage is to isolate, identify, document, and fix any defects, errors, or problems that you encounter so a production effort will, one hopes, be flawless. Devising the proper test plans will ensure a successful production rollout. The plans should identify the following:

Building a test platform is not an easy task. In some cases, it does not need to be an identical system or set of systems from the standpoint of number of processors, amount of memory, or total storage accessible by the cluster nodes, but in all other ways the test platform and the production environment should be identical. The ability of the system or systems to be partitioned affects this as well. Partitioned systems can be set up so that a small partition has exactly the same BIOS, chipset, devices (include firmware and BIOS versions), driver versions, kernel components (for example, antivirus, storage management, security monitor, system management utilities, firewall, disk mirroring, and so on), and user mode applications. All versions of all components should match the production environment and configuration, otherwise the confidence gained from testing an update or upgrade is lowered. Systems that do not support partitioning must be mirrored separately, but it is still important to ensure that all the components are identical between the test system and the production system. The same logic applies to clusters.

The most critical factor affecting whether the update or upgrade will be successful (and not cause any unplanned downtime) is interactions between different versions of hardware and software components and not the total size of the system as measured by number of processors, amount of memory, total storage, number of network or storage adapters, and so on. Thus, a test system or partition need not be exactly the same capacity as the production system, but it should be representative. An example for the use of a partitionable system might be that the production environment is 12, 16, or even more processors, but to be representative, the test partition might only need to contain 4 CPUs. Similarly, the production system might include 32 GB, 64 GB, or even more RAM, but to be representative, the test partition might only need to contain 8 GB of RAM.

Continuing with the same logic, it might not be necessary to fully reproduce the storage capacity of the production system, but only to provide the same behavior and characteristics, scaled to match the smaller test configuration. Some storage arrays support multiple systems and provide internal partitioning. The test system could be attached to the same partitionable storage resource as the production system but scaled down, providing the same storage behavior and characteristics for the test system as the production system. Finally, if you are using a server cluster, it is important to model the test cluster to match the production cluster configuration as closely as possible. Doing this ensures that planned and unplanned failovers allow the application or entire solution to function correctly, guaranteeing the highest possible availability. You can accomplish this testing with just two cluster nodes even if you have a four-node cluster in production, but if you do not match capabilities as closely as possible, your testing may be somewhat invalid.

The reasons that versions are the key factor to test, rather than size or capacity, include the following:

The use of a scaled-down environment is usually possible, with the most glaring exception being when the production system is running close to its maximum capacity, as measured in processor utilization, memory utilization, paging activity, network bandwidth, storage queues, and so on. In this case, even small changes in the behavior of components might cause the production system to begin to function noticeably more slowly and not be responsive enough for production use. This is unavoidable and simply a function of how any system responds when the work demand exceeds its ability to complete that work. In a worst case, the system might effectively be unusable. Thus, customers should always leave headroom in the production system that can absorb an unexpected performance impact. This also gives the system the same headroom for spikes in demand. As a rule of thumb, many customers limit average utilizations of various system resources to between 65 percent and 70 percent. This guideline, however, may not apply to your systems at all. There is a difference between peak usage and overall average use of systems. Those same customers begin planning to expand or otherwise upgrade the system capacity when those levels are consistently exceeded.

Once the test systems are built and are "identical" with the production systems, baseline testing should occur to validate functionality and assess performance characteristics and how they scale in comparison to the production systems.

There is some expense involved for the test system or systems, adapters, fabrics, networks, storage, and possible clusters and other hardware components. This is also true for the various software components, such as the applications and utilities that run on the system. However, you need to measure these expenses against the cost of encountering unplanned downtime that results from a failure, or when the update or upgrade goes bad and results in hours—or even more—of downtime. For all of these components, both hardware and software, customers should ask their vendors about costs and licensing, vendor policy on product instances used strictly for testing purposes, and other concerns.

Finally, it might be necessary to have some clients and a small network set up so that loads can be applied to the system or components using synthetic or dummy loads supplied by vendors or built by the customer.

It is important to determine the risks—whether based on people, technology, or cost—that will be measured against. The success of a plan depends on knowing where potential failures can occur so you can mitigate them and see if the risks are worth taking. The best thing you can do to determine these risks is to test the entire process. However, you should also discuss the risks with anyone who has migrated these applications in the past. They might save you a lot of trouble by helping you to avoid any problems they encountered.

Upgrading a stand-alone server is straightforward. If you are using the same hardware, whatever functionality that server hosts will be completely unavailable during the upgrade process. This puts you at risk in the event that things do not go as planned during or after the upgrade and you need to roll back to your previous state. If you are doing this on the same hardware, at that point you will be relying on your backups. It is always best to configure a new server and then decommission the older one after you confirm that it is up and running in the way that it should be.

Upgrading servers in a server cluster is not dissimilar to upgrading stand-alone servers, as each node would individually need to be upgraded. A cluster presents different challenges, however. First and foremost, is your hardware solution still on the cluster Hardware Compatibility List (HCL) for your operating system choice? It might be, but it also might not. If it is not, you will have to buy new hardware. Could you upgrade on your current hardware and have it still work? Maybe, but if problems occur, you will technically have an unsupported solution. The biggest problem would be in terms of driver compatibility for your hardware, especially for components like RAID controllers. Do not put yourself in this situation.

You also now have more than one server that needs to be dealt with because it is an entire solution. How do you handle a multiple-server upgrade and keep your servers somewhat available? The best approach is to use new hardware, but if you are going to utilize the same hardware, the answer is actually easy; perform a rolling upgrade. A rolling upgrade is when you take the resources currently owned by one node and manually fail them over to another node while you are upgrading its operating system. This is where all of your planning comes into place. If you planned your system resource usage properly, you should have no performance impact after a failover and there should be no need to cause another availability interruption by failing the resources back (or to yet another node) until you are going to upgrade the other nodes in the cluster.

One significant difference between a stand-alone server and a server cluster in terms of an upgrade is that your environment can be a bit more available (depending on how you have things configured). Because a Windows service pack is done per installation of Windows, in an N-node cluster, you will hopefully have N – 1 nodes still completely up and available (assuming no other problems). Because of this, you use a process similar to that of a rolling upgrade of the operating system. First, notify users that the clustered process (such as SQL Server) will be unavailable at certain specific times. You can then manually fail the resources over to another node and start servicing requests again. At the same time, you can also be upgrading node 1. However, here is where your planning comes into place. If you planned your system resource usage properly, you should have no performance impact after a failover, and there should be no need to cause another availability interruption by failing the resources back (or to yet another node) until you are going to upgrade the other nodes in the cluster.

Applying SQL Server 2000 service packs is not the same as applying a service pack to a clustered server running SQL Server 7.0. The older process for SQL Server 7.0 involved using a wizard to uncluster SQL Server 7.0. You would then apply the service pack and recluster. It was an awkward procedure at best, and it caused problems for some. With a SQL Server 2000 service pack, as with the main installation process, the service pack is cluster-aware. It will detect that it is going to be applied to a virtual server.

However, there is no concept of a rolling upgrade as there is with the operating system, so SQL Server is unavailable during the entire operation. During the installation process, the installer will then proceed to not only upgrade the database, but all binaries on each node defined for that specific virtual server. Therefore, you run the service pack install once and have it applied to all nodes. You do not run the service pack install on each node; however there is one (and only one) exception to that rule: should you encounter a catastrophic failure on one node and have to rebuild it (see Chapter 5, and Chapter 6, for details), you do not have to rerun the setup to patch all nodes. You can just patch the newly repaired node. This is possible because when the node is added back into the SQL virtual server definition, a registry key of need_sp_key is added to the newly rebuilt node, indicating it needs to have a service pack applied to it. You can now run the SQL Server 2000 service pack installation on the node, and Setup checks for need_sp_key and proceeds to update the binaries on that node only because the databases were already updated. This also allows you to fully service requests from applications or clients because SQL Server 2000 is not put into single-user mode. On the new node you are updating, you will see some entries in the Sqlsp.log file that will look similar to the following:

[args]
NumRemoteServers=1
Server.1=<newnodename>
[Server.1]
NumRemoteServices=1
...

These entries confirm that the unattended setup processes are running on the one node only. From a GUI standpoint, you will see the standard "Setup Is Performing Required Operations On ..." messages, but behind the scenes, it is doing the right thing.

If you employ log shipping, you must also understand the impact of a SQL Server 2000 service pack. First and foremost, a service pack upgrade does not break log shipping. Setup automatically detects user databases (as well as filegroups) that are not able to be written to and skip those. Because a database that is being log shipped on a secondary is either in NORECOVERY or STANDBY, it cannot be written to or updated. These skipped databases are documented in the Sqlsp.log file mentioned earlier. The following message is also displayed during the process:

Setup has detected one or more databases and filegroups which are not writable.

It is fully supported to log ship from any version of SQL Server (RTM, Service Pack 1, Service Pack 2, and Service Pack 3 as of the writing of this book) to another because there are no metadata changes that would affect log shipping. If there were to be any metadata changes to the user databases themselves in future service packs, this might require you to have all databases at the same service pack level. Read the documentation that ships with the service pack to see if there are any user database metadata changes that are included.

You should, however, consider service pack upgrades to an instance of SQL Server if your primary is at one level and your secondary is at another.

Replication and SQL Server 2000 service packs are a bit more complicated a combination than log shipping or failover clustering. The order in which you upgrade your instances participating in a replication chain absolutely matters, specifically:

There are cases where you literally have a read-only database (such as in replication) or one that is used for reporting but needs to be upgraded (that is, it is a Subscriber) to the latest service pack. Because read-only databases are skipped in the service pack install for SQL Server, these need to be upgraded. To accomplish this task, follow these steps prior to applying the service pack or after, depending on your needs. It is better to do it beforehand so that you have only one availability outage and not multiple outages: