This is the final tip in a series describing the case history of a database upgrade from SQL Server 2000 Active/Active cluster running on Window 2000 Server to a Windows Server 2003 on SQL Server 2005 Active/Active cluster. Consultant Matthew Schroeder will walk you through the technical and decision-making process of real-world IT and database management teams. The article is based on two online upgrades: a commercial website and an eBay ordering system. For confidentiality reasons, certain details of the actual project have been changed.
Typically, database administrators start off with architecture already in place, which leads to confusion on how to monitor SQL Server during the upgrade process. By far, the most important part of an upgrade is the planning, but companies rarely have the resources to stage a fully loaded upgrade test. Unfortunately, the real load test of the SQL Server upgrade process comes when the system is stressed under production.
The challenge
Our hypothetical company is using either database mirroring, replication or some combination of the two to upgrade a system. Let's examine how to monitor the replication and database mirroring to ensure that all data is flowing across in a reliable manner.
Monitoring SQL Server replication
Monitoring the replication portion to ensure that all records in the conversion database are properly pushed to the destination requires that you right click Replication and then select Launch Replication Monitor. This will bring up the monitoring screen, as seen in Figure 1.
Figure 1: Click on Replication, then Launch Replication Monitor for a view of system activity. (Click on image for enlarged view.)
This screen gives you a snapshot view of activity occurring. Is the subscription running? What's the performance and latency? A very nice feature that Microsoft added in SQL Server 2005 is tracer tokens. A tracer token is basically a packet of data inserted into the replication chain. It allows you to measure time to the distributor -- this can be on the same machine as the publication or on its own box -- and the time it takes from distributor to the subscribing server. (See Figure 2.)
Figure 2: Tracer tokens in SQL Server 2005 measure time to the distributor and time from the distributor to subscribing server. (Click on image for enlarged view.)
When the initial database snapshot is copied over for the subscription, it will show with an exclamation point. This is not a problem and is only due to the load generated by copying the database snapshot. Once the two databases are synchronized, the exclamation point should disappear -- unless the servers are really overloaded.
If the exclamation point persists, then you will want to use a tracer token to see if latency is introduced at the publisher, distributor or subscriber. Typical factors affecting latency include CPU, I/O, network and RAM -- in that order.
If one of these areas is in fact slowing performance, switch to the All Subscriptions table and double click on the subscription to get the error listing. At this point, the culprit is usually a schema, job or initialization error.
Monitoring database mirroring in SQL Server
Monitoring Database Mirroring can be confusing, since there is no separate folder for it. Once database mirroring is set up, right click either the principal or mirror databases, tasks and then select Launch Database Mirroring Monitor. A screen as shown in Figure 3 will pop up.
Figure 3: Once database mirroring is set up, Launch Database Mirroring Monitor. (Click on image for enlarged view.)
If you initialized the mirror instance with a fairly recent transaction log, the databases should already be synchronized by the time you get to this screen. If the database mirroring setup for high-safety -- i.e., the transaction has to commit on the mirror prior to committing on principal -- then this screen will let you know what, if any, overhead is being incurred by the "Mirror commit overhead." It will also give you a good idea of how far behind your mirroring environment may be, if you're using "high-availability" mode.
Performance issues are typically in the same problem areas as they are in replication, so if there are any issues displayed on this screen, they should be easy to find.
Summary
In this five-part series, we walked through sample architecture layouts for upgrade plans for both the OS/SQL Server and for SQL Server live application upgrades. The architectures can be used together or separately. We also walked through how to monitor database mirroring and monitor database replication, depending on what upgrade methodology you choose.
You should review replication/database mirroring performance periodically throughout the upgrade. You can set up alerts in database mirroring, which can then be used by SQL Server Agent to send DBAs warnings or MOM/SCOM (MS system monitoring tools) to pick up the events and send warnings to the appropriate groups. When compared to SQL Server Agent or MOM/SCOM, replication is more difficult to receive alerts from as it does not send alerts natively.
Upgrading Active/Active cluster to Windows Server 2003/SQL Server 2005
Part 1: Team composition and upgrade option pros and cons
Part 2: Restoring a SQL Server database to a transition server
Part 3: SQL Server high availability when upgrading to SQL Server 2005
Part 4: Upgrade live applications to SQL Server 2005 for high availability
Part 5: Monitor database mirroring and replication after upgrade
| ABOUT THE AUTHOR: |
|
Matthew Schroeder is a senior software engineer working on SQL Server database systems, ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. Matt currently works for the gaming vendor, IGT, providing services to gaming companies. He also works as an independent consultant, specializing in SQL Server, Oracle and .NET for industries such as gaming, automotive, e-commerce, entertainment, banking and non-profit. Matt specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. He is a Microsoft certified MCITP, Database Developer, has a master's degree in computer science and more than 12 years of experience in SQL Server/Oracle. He can be reached at cyberstrike@aggressivecoding.com.
|
|
