High Availability Options for Siemens Teamcenter on SQL Server Maximize availability in support of world-class product lifecycle management Microsoft Corporation Published: August 2010
Technical Reviewers:
Richard Waymire, Solid Quality Mentors Christopher Gill, Teamcenter Centers of Excellence, Siemens PLM Software
Abstract Product lifecycle management (PLM) is an enterprise, business, and information strategy that enables development and delivery of world-class products. Siemens PLM Software’s Teamcenter® powers innovation and improves productivity by connecting people with the product and process knowledge they need to effectively function in a globally oriented product lifecycle. Teamcenter’s proven lifecycle management solutions are built on an open PLM foundation. Microsoft® SQL Server® database software provides an ideal database platform for Teamcenter. With SQL Server’s reliability, scalability, and performance, organizations can be assured an unmatched Teamcenter experience. This whitepaper provides recommendations for configuring high availability options for the SQL Server database platform running Teamcenter. This paper complements the detailed support documentation provided on the Siemens support Web site.
Copyright Information The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This whitepaper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE IN FORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may b e reproduced, stored in, or introduced in to a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent appli cations, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in a ny written license agreement from Microsoft, the furnishing of this document does not give you a ny license to these patents, trademarks, copyrights, or other intellectual property. © 2010 Microsoft Corporation. All rights reserved. Microsoft, SQL Server, Hyper-V, MSDN, and Windows are trademarks of the Microsoft group of companies. All other trademarks are property of their respective owners.
Copyright Information The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This whitepaper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE IN FORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may b e reproduced, stored in, or introduced in to a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent appli cations, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in a ny written license agreement from Microsoft, the furnishing of this document does not give you a ny license to these patents, trademarks, copyrights, or other intellectual property. © 2010 Microsoft Corporation. All rights reserved. Microsoft, SQL Server, Hyper-V, MSDN, and Windows are trademarks of the Microsoft group of companies. All other trademarks are property of their respective owners.
Table of Contents OVERVIEW ............................................................................................................................................................. 5
SIEMENS TEAMCENTER: THE SOLUTION FOR PRODUCT LIFECYCLE MANAGEMENT ............................ .......................................... ............................ ............................. ...............5 SQL SERVER: AN ENTERPRISE-READY DATABASE PLATFORM FOR TEAMCENTER ............................ .......................................... ............................ ............................ ................. ...5 BETTER TOGETHER: TEAMCENTER ON THE SQL SERVER DATABASE PLATFORM............................ .......................................... ............................ ............................ ................... .....6 THE TEAMCENTER ARCHITECTURE ......................................................................................................................... 7
CLIENT APPLICATION TIER ............................ ........................................... ............................. ............................ ............................ ............................ ............................ ............................ ............................ ................. ... 7 WEB APPLICATION TIER........................... ......................................... ............................ ............................ ............................ ............................ ............................ ............................ ............................. ...................... .......7 ENTERPRISE TIER .......................... ......................................... ............................. ............................ ............................ ............................ ............................ ............................ ............................ ............................ ................. ... 7 RESOURCE TIER............................ ........................................... ............................. ............................ ............................ ............................ ............................ ............................ ............................ ............................ ................. ... 8 HIGH AVAILABILITY OPTIONS FOR SQL SERVER .................... ............................... ....................... ....................... ...................... ...................... ...................... ...................... ............... .... 8
UNDERSTANDING AVAILABILITY TERMS............................ .......................................... ............................ ............................ ............................ ............................ ............................ ............................ ................ 8 Recovery Point Objective (RPO) ........................... ......................................... ............................ ............................. ............................. ............................ ............................ .......................... ............8 Recovery Time Objective (RTO) .......................... ........................................ ............................ ............................ ............................ ............................ ............................ ............................ ................8 Downtime............................ ........................................... ............................. ............................ ............................ ............................ ............................ ............................ ............................ ............................ ................. ...8 Nines ............................ .......................................... ............................ ............................. ............................. ............................ ............................ ............................ ............................ ............................ ........................ ..........9
YOUR AVAILABILITY STRATEGY ........................... ......................................... ............................ ............................. ............................. ............................ ............................ ............................ .......................... ............9 UALITY HARDWARE ........................... FIRST LINE OF DEFENSE: Q UALITY ......................................... ............................ ............................. ............................. ............................ ............................ ................... .....9
SETTING YOUR AVAILABILITY GOALS ............................ ........................................... ............................. ............................ ............................ ............................ ............................ ............................ ................ 10 PROCESS AND ADMINISTRATION........................... .......................................... ............................. ............................ ............................ ............................ ............................ ............................ ...................... ........10 Proper Staff Training ............................ .......................................... ............................ ............................ ............................ ............................ ............................ ............................ ........................... .............11 Systems for Testing Deployments ........................... ......................................... ............................ ............................ ............................ ............................ ............................ ...................... ........11 Systems for Testing Recovery.................................... .................................................. ............................ ............................ ............................ ............................ ............................ .................... ......11
PURCHASING THE RIGHT SKU OF SQL SERVER ........................... ......................................... ............................ ............................. ............................. ............................ ............................ ................. ...12 SINGLE-SYSTEM AVAILABILITY ............................................................................................................................. 12
BACKUP AND RECOVERY STRATEGIES TO IMPROVE AVAILABILITY............................ .......................................... ............................ ............................ ............................ ...................... ........12 Database Backup Models......................... Models........................................ ............................. ............................ ............................ ............................ ............................ ............................ ...................... ........13 Database Recovery Models ........................... ......................................... ............................ ............................. ............................. ............................ ............................ ............................ ................. ...13
GUIDELINES FOR IMPLEMENTING A BACKUP AND RECOVERY PLAN ........................... ......................................... ............................ ............................ ............................ .................... ......13 Importance of Practicing Recovery ........................... ......................................... ............................ ............................ ............................ ............................ ............................ .................... ...... 14
RESOURCE GOVERNOR ............................ .......................................... ............................ ............................ ............................ ............................ ............................ ............................ ............................. .................... .....14 USING MULTIPLE SYSTEMS TO IMPROVE AVAILABILITY ................. ............................ ...................... ...................... ...................... ...................... ...................... ............... .... 15
LOG SHIPPING ........................... .......................................... ............................ ........................... ............................ ............................. ............................. ............................ ............................ ............................ ................. ... 16 Log Shipping Servers ............................ .......................................... ............................ ............................ ............................ ............................ ............................ ............................ ........................... .............16 Setting Up Log Shipping using SSMS .................................. ................................................ ............................ ............................. ............................. ............................ ........................ ..........17 Switching to the Secondary Server ................................... ................................................. ............................ ............................ ............................ ............................ ........................... .............24 Other Log Shipping Details................................... ................................................. ............................ ............................. ............................. ............................ ............................ ........................ ..........24
FAILOVER CLUSTERING ............................ .......................................... ............................ ............................ ............................ ............................ ............................ ............................ ............................. .................... .....25 Failover Clustering Concepts ............................ .......................................... ............................ ............................ ............................ ............................ ............................ ............................ ................25 Installing a Failover Clustered Instance of SQL Server ............................ ........................................... ............................. ............................ ............................ ................. ...27
Maintaining a Failover Clustered Instance of SQL Server ...................................................................................36 When Should You Use a Failover Cluster? ...........................................................................................................37
DATABASE MIRRORING .................................................................................................................................................37 REPLICATION ...............................................................................................................................................................38 SUMMARY ........................................................................................................................................................... 38 LINKS FOR FURTHER INFORMATION .................................................................................................................... 39
Overview Product lifecycle management (PLM) is an enterprise, business, and information strategy that enables companies to establish Global Information Networks, essential for developing and delivering world-class products in today’s highly competitive international marketplace.
Siemens Teamcenter powers innovation and improves productivity by connecting people with the product and process knowledge they need to effectively function in a globally oriented product lifecycle. Teamcenter’s proven digital lifecycle management solutions ar e built on an open PLM foundation.
The performance and availability of Teamcenter—and of the underlying Microsoft SQL Server database platform—is critical to the success of the PLM platform. Responsiveness to users and reliability of the PLM solution are integral to realizing the business benefits of an integrated product development system. This whitepaper, intended for database administrators (DBAs), presents high availability options for Teamcenter on the SQL Server database platform. This guidance can help DBAs optimize uptime for Teamcenter, keeping the system’s essential functions available to meet critical business needs . Note that Teamcenter supports Microsoft® SQL Server® 2005 and SQL Server ® 2008 database software. Unless there is a specific difference, this document uses “SQL Server” to refer to all supported versions.
Siemens Teamcenter: The Solution for Product Lifecycle Management You can leverage PLM to provide total visibility into workflows and decision making at all stages in the product lifecycle. PLM provides unique opportunities to:
Maximize innovation throughout your product lifecycle, which translates into higher revenues, greater market share, faster time to market, and improved portfolio success rates. Transform the decision making processes you use to determine what products you should offer and how these products should be brought to market. Increase the value of your product knowledge by managing this information as an intellectual asset on an enterprise basis and leveraging it across multiple programs, projects, and revenue-generating initiatives.
Minimize lifecycle cost by replacing time-consuming manual processes with accelerated, fully automated solutions.
Teamcenter is the world’s most widely used PLM system . It is backed by Siemens PLM Software’s leadership in delivering Global Innovation Networks that enable companies to make unified, information-driven decisions at every stage in the product lifecycle.
SQL Server: An Enterprise-Ready Database Platform for Teamcenter Microsoft SQL Server provides an ideal database platform for Teamcenter. SQL Server is a highperformance, integrated database and business intelligence solution for data management and analysis. This easy-to-implement, easy-to-support foundation provides a multifunctional solution for large-scale
online transaction processing (OLTP), data warehousing, and e-commerce applications and a solution for data integration, analysis, and reporting. SQL Server can help companies manage large volumes of mission-critical data and run software applications—such as Siemens Teamcenter—to optimize their business performance. SQL Server can extract and transform data from a variety of sources, including XML data files, flat files, and relational data sources, and then load it into one or more destinations. In addition to rapid data mining, analysis, processing, and reporting capabilities, SQL Server has built-in features that give you a secure, reliable, and productive data management environment that truly protects your data —helping reduce the potential risk of corruption. With its scalable infrastructure, SQL Server has the capability to grow with your business and keep up with your toughest data challenges.
Better Together: Teamcenter on the SQL Server Database Platform Running Siemens Teamcenter on SQL Server delivers measureable value by channeling product and process data into a manageable, globally integrated resource. Benchmarking tests confirm that SQL Server scales to meet the performance needs of even the largest enterprise customers, while providing lower initial costs and licensing fees.
The Teamcenter Architecture Best practices for Teamcenter on the SQL Server database platform start with configuration of the system. It is helpful to system. It is helpful to review and have a thorough understanding of the Teamcenter architecture, which
Figure 1 shows.
Figure 1 - Teamcenter Runtime Architecture
The Teamcenter suite is a service-oriented architecture (SOA)-based distributed application suite that uses a four-tier architecture.
Client Application Tier Users interact with Teamcenter through desktop and browser-based clients. Web browser clients access UI and business services from the Web server in the Web Application Tier via HTTP/S. The preferred model for desktop clients is to access the Web application tier through WSDL-based SOA interfaces, also over HTTP/S. Based on HTTP/S, both types of client can effectively access Teamcenter across low or high latency network connections—as well as securely traverse corporate firewalls without needing to open additional application-specific ports. Some deployments with older clients, however, use non-SOA interfaces to interact with the Enterprise Tier directly.
Web Application Tier Teamcenter deployments use Web application servers to expose the SOA service endpoints for all client types. Both REST-style (standard HTTP POST) and SOAP-style requests are supported by Teamcenter components executing on industry-standard Web servers, such as Microsoft IIS, as well as servers based on Java and J2EE technology. SOA Framework components running on these application servers are responsible for normalizing the request into a single common format, which is passed directly onto Teamcenter’s Business Logic Server in the Enterprise Tier.
Enterprise Tier Teamcenter’s Business Logic Server, along w ith Teamcenter’s SOA server-side components, resides in
the Enterprise Tier. The Business Logic Server is primarily implemented in C++.
Resource Tier The Resource Tier houses the Teamcenter SQL Server database as well as bulk file repositories, usually on distinct servers. The Microsoft SQL Server server(s) and database(s) hosted in this tier are the focus of this paper.
High Availability Options for SQL Server SQL Server 2005 and SQL Server 2008 have a variety of technical capabilities to address your goals for system availability. As a DBA or systems administrator, you should understand these capabilities so that you can make good choices to optimize your system availability. Key considerations include which edition of SQL Server to purchase and which technologies to deploy on a single system —and may include how to configure multiple systems to work together to ensure your availability goals can be met.
Understanding Availability Terms A good starting point to any conversation about availability is ensuring that a common language is used. Term definitions are essential to a successful discussion about designing and configuring a high availability solution and meeting your availability requirements. Here are some essential availability terms to understand. Recovery Point Objective (RPO) RPO indicates how much data you are willing to either lose or manually re-enter (if possible) in the event
of a system failure. Recovery Time Objective (RTO) RTO indicates how much downtime you are able to accept on a particular system before the impact is
too much for your business to tolerate. Downtime Downtime is defined (for the purposes of this paper) as the time that the database system is not
available to your application. If the database system is unable (for any reason) to perform work on behalf of your application, then the database is considered to be “down.” There are two types of downtime: planned and unplanned.
Planned downtime. Many systems have a predefined maintenance window or time that is set
aside on a daily or weekly basis for maintenance work to be performed on a system. From a SQL Server perspective, this could be time that is set aside for reorganizing indexes, rolling out upgrades, performing testing on the production system, or creating backups. Planned downtime is a way to prevent the additional load that these activities place on the system from reducing the system’s performance. Planned downtime typically does not include items such as loading data, which is a normal part of application workflow in many environments.
Unplanned downtime. Unplanned downtime represents the time that the database system is
unavailable to perform work for your applications, outside the normal maintenance window. This kind of downtime may represent a wide variety of issues with the system, from a runaway query consuming all available resources, to a database that has become corrupt and must be restored, to a server crash that makes the entire server unavailable. Nines Availability is frequently spoken of as the number of nines you want to achieve. In most cases,
businesses want system availability to be within the 3 nines (99.x %) range. You can use the following equation to calculate the number of hours of downtime your system can tolerate during a year, based on the number of nines you want to achieve: Total available hours/year – (Total available hours/year * desired availability/100)
The total available hours per year is 8,760 (365 days * 24 hours). So if you want 99% system availability during the year, the equation would look like this: 8760 – (8760 * 99/100)
The resulting number of hours of downtime for the year is 87.6. The following table shows a common set of system availability figures based on the number of nines you want to achieve: Availability Goal 99% 99.9% 99.99% 99.999%
Hours of Downtime per Year
87.6 8.76 .876 .0876
Minutes of Downtime per Year
5,256 525.6 52.56 5.25
Your Availability Strategy Your availability strategy will vary significantly based on your RPO and RTO needs. Do you have maintenance windows for routine maintenance, or are you required to have your application available 24x7x365? As with many kinds of systems, the higher your availability requirements, the more money it will cost to achieve those requirements. What’s the cost to your business if RPO is not zero? Will you lose your customers if you lose data they entered into your system? If your RTO is zero, what kinds of processes and technology will you need to purchase, deploy, and configure to achieve your goals?
First Line of Defense: Quality Hardware One of the first considerations for your system availability is high quality hardware. In cloud computing, data centers that use inexpensive, replaceable “pizza box” servers reduce se rver costs; this model works
reasonably well with the right infrastructure or when data persistence is not an issue. However, with a database server, data persistence is critical, which limits the pizza-box approach for the database server. That is not to say, however, that the machines running the database tier must necessarily be expensive. Prices have dropped dramatically for a quality system with significant amounts of disk and memory. A well configured database server from any of the standard hardware vendors can cost much less than $50,000 and still be scalable and high performing. Your hardware costs may be affected by ensuring that other components also promote higher system availability. Redundant networks, network cards, power supplies, and so on add to the cost of a highly available system, but having them will eliminate different points of failure that could lead to system downtime. Another key aspect of system availability to consider when investing in hardware is the storage subsystem. If you want to take advantage of mirrored copies of the data and transaction logs in storage, or more advanced technologies such as remote mirroring of the disks (more for disaster recovery than availability purposes), a higher initial investment may be required. If you do not use these capabilities, consider RAID 5 or some other level of RAID for basic protection against disk failure. Disk failure should be an expected event. Having the right infrastructure in place to deal with this expected failure is essential to maintaining system availability.
Setting Your Availability Goals To determine your high availability needs and which technologies to use, such as which edition of SQL Server to implement or whether to employ multiple systems, you need to know your availability goals. What RPO do you want to achieve? What RTO do you need? If your SQL Server installation is supporting a business-critical system that must always be available, then the combination of people, process, and systems you need to achieve that availability goal will be significantly more than that for a system that can tolerate more planned or unplanned downtime. Many companies define a formal process for setting their availability goals. This paper will not attempt to outline that process, but it does assume that you have some process in place for determining those targets and helping answer such questions as:
Do I need a dedicated SQL Server DBA?
Can I largely automate this system and have it be under someone’s desk, or do I need the
system monitored at all times to ensure that our high availability goals are met? If you have an IT or data center department, the staff may want a formal Service Level Agreement (SLA) to define exactly what the expectations (and the costs) are for meeting these goals.
Process and Administration Although they are not often mentioned in technical whitepapers, other important factors affecting system availability are the processes and people in place to properly support the system.
Proper Staff Training If your availability goals extend beyond 99%, you need to have a primary SQL Server DBA and, if
possible, a backup DBA on staff. If you run only one instance of SQL Server, you may be able to crosstrain a DBA from some other product to also support SQL Server. It is important to have a backup DBA to fill in when the primary DBA is away for any reason. This training doesn’t have to be a formal certification class, but it does need to ensure basic knowledge
of administering and troubleshooting the version of SQL Server that you deploy to support your application. If you can’t afford to have such expertise on your staff, an alternative is to find a partner
company that can provide support and escalation help if you have difficulties with your database. There are many resources to help DBAs and other systems administrators keep current on SQL Server, Windows, and the environmental ecosystem that surrounds your database. Attending conferences, reading books and whitepapers, and being involved with local user groups can help DBAs improve their skills and be better prepared to provide the SQL Server availability your organization needs. Systems for Testing Deployments A key factor in whether a system has sufficient availability has nothing to do with your production
server. Having test systems—and using them—greatly improves the availability of your production databases. You can thoroughly test application changes or customizations on test systems before deploying them to your production system. This practice ensures that routine problems, coding errors, or administrative configuration mistakes will be discovered before they have a chance to disrupt your company’s ongoing business operations.
Frequently, test systems are seen as a target to save money. The hardware costs, combined with the software licenses, can be a significant investment. However, not having such systems substantially increases the likelihood of system availability problems because you cannot test application changes adequately. And a developer’s workstation configuration is not similar enough to your production systems to use as a test system. In the long run, the downtime caused by untested deployments will cost more than the hardware and software for a test system. Systems for Testing Recovery Another important investment area is having one or more systems on which to practice backup and
restore operations. A competent and well trained DBA will have a deep understanding of backup and restore. However, there are many issues related to restoring a backup to a point in time that aren’t obvious until you attempt to perform a restore operation. Questions include:
Are the backups available?
Do you understand how to determine the latest differential backup?
Which transaction log backups will you need to be able to finish the restore?
These are not questions you want the DBA asking during a critical recovery operation, with your business losing money every minute the system is down.
If you have a test deployment system, your DBA may be able to use it to also practice recovery operations. Organizations will need to determine that possibility on a case-by-case basis. However, practicing restore operations on a regular basis will keep the DBA sharp and ensure that, if the need arises, the staff is well prepared and recovery operations will proceed smoothly.
Purchasing the Right SKU of SQL Server As you move up the scale of improved availability, you will find that the features of the higher-end editions of SQL Server may be beneficial. The SQL Server team continues to put the focus of SQL Server Enterprise Edition (and with SQL Server 2008 R2, Data Center Edition) on performance and scalability. For example, the following chart, adapted from Microsoft’s SQL Server Compare Edition Features page, shows that the vast majority of availability technologies in this paper are available only in the Enterprise (and Data Center) editions of SQL Server. Feature Database Mirroring
Enterprise Edition
Standard Edition
Fully supported
Log Shipping Failover Clustering Online Indexing Mirrored Backups Backup Compression Resource Governor Hot-add Memory and CPU Fast Recovery Online Page and File Restore Database Snapshots
Fully supported 16-node failover clustering Yes Yes Yes Yes Yes Yes Yes Yes
Yes, but single-threaded, high safety only Fully supported 2-node failover clustering No No No No No No No No
Single-System Availability When you start talking about availability and SQL Server or do a quick online search for SQL Server availability, you will undoubtedly find many references to multi-system solutions such as log shipping, failover clustering, and database mirroring. However, let’s begin our disc ussion with single-system availability, exploring how to improve your availability on a single server without using a secondary (or tertiary) server. With proper administrative efforts, along with effective policies and procedures, you can achieve good availability with a single server.
Backup and Recovery Strategies to Improve Availability A critical part of any system’s availability strategy includes backup and recovery. In the event of a single
system failure, your only recovery option is to restore from a backup. In fact, if the failure is serious enough, you may have to re-install Windows and SQL Server before you can recover the data. Siemens recommends that you carefully plan, document, and test a backup and recovery strategy and that you store backup files to disk (local or network) or tape. These backups should not be stored on the
same server as the database. As an extra precaution, you should store a copy of the backups off-site for disaster recovery. Database Backup Models A well-planned backup and recovery strategy helps protect your database against data loss caused by a
variety of failures. The scope of a backup can be a whole database, a partial database, or a set of files or filegroups. For each of these, SQL Server supports full and differential backups: Full backup. Contains all the data in a specific database or set of filegroups or files as well as
enough transaction log information to allow the data to be recovered.
Differential backup. Includes database changes since the last full backup.
Transaction log backup. Records any database changes.
Database file or filegroup backup. Allows copies of specific database files; this type of backup is
not recommended for Workforce Central Database Recovery Models There are three recovery models for databases in SQL Server:
Full recovery model. This is the best model for preventing critical data loss and restoring data to
a specific point in time. This model is generally used by enterprise production systems. If the transaction log is available, you can get up-to-the-minute recovery and point-in-time restore if the end of the transaction log is backed up and restored. The trade-off for the Full recovery model is that it can negatively affect the performance of other operations due to increased logging. The administrative overhead is also higher than with the Simple recovery model.
Simple recovery model . This model is appropriate if the data backed up is not critical, the data is
static or does not change often, or if data loss is not a concern for the organization. In this situation, the organization loses all transactions since the last full or last differential backup. This model is typical for test environments or production databases that are not mission critical.
Bulk-logged model . This model is for environments that have critical data but for which logging
large amounts of data degrades system performance or whose bulk operations need to be performed after hours so they do not interfere with normal transaction processing. This model is sometimes used temporarily during data loading and then the system is moved back to the Full recovery model, to improve the performance of data loading operations. The recovery model you select determines which kinds of backups and recovery operations are allowed. It also requires you to change allocations for transaction log space and affects the size of the transaction log backups (if you are using them). In general, Siemens recommends that you have your database in the Full recovery model.
Guidelines for Implementing a Backup and Recovery Plan Keep the following in mind as you develop and implement a backup and recovery strategy:
If you choose the SQL Server Full recovery model, the transaction log can grow very quickly and should be scheduled for backup at multiple times throughout the day. Performing multiple backups ensures that the log is truncated frequently and provides for improved data recoverability, meaning a smaller RPO with less data loss.
If you choose the Simple recovery model, schedule full database backups at least once per day. With this method, data recoverability is limited to the last full database backup.
Database backups should be stored off-site when possible to improve your disaster recovery capabilities. If possible, create a mirrored backup media set, sending one copy off-site and keeping the other copy local for faster restore operations in the event of a non-catastrophic event.
Have at least one backup device for each database, and give that device a name that clearly describes the database with which it is associated. For example, for a database called “TeamcenterDB,” name the backup device “TEAMCENTERDB_BACKUPS ”. Back up a database immediately after you make extensive changes or perform non-logged operations. Back up the master database any time you perform an operation that alters the structure of any database. Otherwise, if the master database fails, you lose information about those changes. Also be sure to include regular backups of the master and msdb databases as part of your overall backup strategy. The master database contains information about the databases on your system, and the msdb database stores information about scheduled jobs. Security information, such as who can log in to the system, is also stored in master.
Enable the CHECKSUM option on your backup operations to help ensure the integrity of your backups.
If using SQL Server Enterprise Edition, enable backup compression to save space for your backup jobs and speed up restore operations. Checksum is automatically enabled for compressed backups.
It is essential that you test your backup and recovery plan. If possible, simulate a disaster such as an air conditioning failure during a heat wave to make sure that your emergency procedures function as planned. At the very least, rebuild the database from scratch using your backups, and make sure that your system can access the restored database. Importance of Practicing Recovery It can’t be emphasized enough: You must practice your restore/recovery operations on a regular basis.
System availability will likely be affected by how often your DBAs have practiced restoring the database and/or otherwise recovering from system failures. Your DBAs must be comfortable with restore operations, locations of the database backups, how to request and get a copy of off-site backups, and other aspects of the backup and recovery plan.
Resource Governor Resource Governor is an application you can use to restrict the amount of CPU and memory that queries are able to take from SQL Server. Resource Governor is a great option if you can “tag” connections to SQL Server that you know may consume resources that would take away from higher priority workloads. For example, you could restrict system maintenance activities from consuming more than 25% of the CPU on your SQL Server installation.
Resource Governor requires the Enterprise Edition of SQL Server 2008 (or later). For more information about Resource Governor, see the Resource Governor topic in SQL Server Books Online.
Using Multiple Systems to Improve Availability As you have seen above, there are many things you can do on a single system that affect your availability. Solid operational best practices and a strong backup and recovery strategy will do more than just about anything else to help ensure availability. And in many cases, sites simply do not have multiple systems and/or copies of SQL Server to take advantage of to improve their system availability. However, for mission-critical systems such as Teamcenter, you may want to consider further protection options to ensure the availability of your SQL Server database in the event of a single-system failure. These additional options include:
Log shipping
Failover clustering
As you will see as we walk through these options, these features provide much faster availability than having to perform a restore operation on a single-system implementation. As with single-system availability, there is no one right answer when choosing technology. If you have the SQL Server Standard Edition, some choices will not be available to you. You may also be constrained by other factors, such as whether you can afford the storage space to keep a second (or third) copy of your database, logs, and backups. The best approach to choosing a technology is to define your availability goals, review the available technologies, and implement the appropriate technologies that will help you meet your goals. A Note on Domain Membership and Service Accounts
For nearly all multi-server availability solutions, there is the need to move data and/or information around between multiple systems. For this to work properly, the SQL Server and SQL Server Agent services will need to have service accounts that can work across the network. Although Teamcenter does not make a specific recommendation for service accounts, you will find that each of the options for multiple-system installations are easier to implement if you use Windows servers that meet the following criteria:
The servers are members of the same domain (or where a trust relationship is established). The servers use service accounts rather than using machine accounts (such as LocalSystem or NetworkService).
Note: Technically, in newer versions of Windows (such as Windows Server 2008 or Windows Server 2008 R2), NetworkService can work across the network.
For the purposes of this whitepaper, the examples use domain user accounts that support the SQL Server and SQL Server Agent services.
Log Shipping Log shipping, one of the oldest high availability technologies in the SQL Server product, sends copies of the transaction log from your primary server to a second copy of your database, installed on another instance of SQL Server. In theory, you can have both instances of SQL Server on a single physical server, but that will not really improve your availability. When the transaction log is copied to the secondary server, it is applied in a recovery operation to the existing copy of your database. As each new copy of the transaction log arrives, it too is recovered. You determine how current the secondary server’s copy of the database is by deciding h ow often transaction log backups are created and sent to the secondary server. Log shipping can also have a third server, known as the “monitor” server, involved in the setup. The
monitor server is used to:
Keep the history of the log shipping operations
Send out an alert if log shipping fails for some reason
Because log shipping is based on the transaction log backups, your database must not use the Simple recovery model. Log shipping is automated through the SQL Server Agent service, so ensure that SQL Server Agent is running. Because log shipping is based on making backups of the transaction log, those now become your production backups. For a restore operation, you need all of the transaction log backups since the last full backup. For example, if you log-ship every 1 minute to a remote system during the day, you may have to restore hundreds of small transaction logs from the last full backup to the current point in time. Log Shipping Servers Let’s look in more detail at the servers involved with log shi pping.
Primary server. The primary server is the instance of SQL Server where your production
Teamcenter SQL Server database resides. You need to connect SQL Server Management Studio (SSMS) to the primary server to perform administrative tasks when setting up log shipping.
Secondary server . The secondary server is the instance of SQL Server with a “warm standby”
installation of your production database available. You can have more than one secondary server for disaster recovery purposes. It is recommended that the Windows server you use as a secondary server have roughly the same hardware specifications as the primary server in terms of CPU, memory, and disk I/O to ensure adequate performance if the primary server fails. There are two ways that you can initi alize your secondary server’s copy of your production Teamcenter database. You can initialize the database as part of the configuration process for log shipping. Or you can perform a manual recovery of a production database backup yourself before you begin log shipping.
Monitor server (optional). A monitor server is used to keep track of the log shipping process
itself and to alert the DBA team of any failures in the log shipping process. A monitor server is optional, but if you have a third instance of SQL Server on a separate machine, it is recommended that you also use it for this role. The example configuration we will look at in a moment does not use a monitor server. For more information about monitor servers, see the Monitoring Log Shipping topic in Books Online. Setting Up Log Shipping using SSMS The easiest way to set up, configure, and manage log shipping is to use SQL Server Management Studio
(SSMS). You can use Transact-SQL (T-SQL) directly to perform these steps, but for a basic log shipping configuration, there is no advantage to using T-SQL directly. The setup of SSMS starts by having a primary and secondary server installed and configured. As stated earlier, it’s easiest if you have used domain user accounts for the SQL Server and SQL Server Agent services to simplify security configurations. Let’s look at an example of how to set up log shipping through SSMS. This example uses
RWClustN1\LogShipPrimary as the production server instance and RWClustN2\LogShipSecond as the secondary instance. 1. Create a file share location for a copy of the transaction log backups that will be moved from the primary to the secondary server. To do this, log on to the secondary server (RWClustN2), and create a share on a drive with enough space to handle the transaction logs. For this example, the share is called logshipfiles (\\rwclustn2\logshipfiles). Make sure that you:
Give the SQL Server and SQL Server Agent service accounts full control over the share and full control permissions at the file system level Allow file and print sharing through the Windows Firewall
2. Start SSMS and connect to the primary server instance. Note: You do not have to be physically using SSMS on the primary server instance; you can use SSMS remotely from your desktop.
3. Right-click the database you want to log ship, and then select Properties, Transaction Log Shipping. The dialog box shown in Figure 2 will appear.
Figure 2 - Transaction Log Shipping database properties
4. Check the option called Enable this as a primary database in a log shipping configuration . Then click the Backup Settings… button. The dialog box shown in Figure 3 will appear.
Figure 3 – Transaction Log Shipping job settings
5. On this dialog box, complete the following settings:
Network path to backup folder. Specify where you want the transaction log backups to be
placed for your log shipping. For this example, the backup folder is the remote server.
Delete files older than. Specify how often transaction logs should be deleted. For this
example, transaction logs that are older than 3 days (72 hours) should be deleted.
Alert if no backup occurs within. Specify when to send an alert that no backup has
occurred. For this example, the setting matches the Delete files older than setting.
Job name and Schedule. Specify a name for the transaction log backup job and how often
you want it to occur. For this example, the default settings are used. To reduce the chance of data loss, specify a more frequent schedule (for example, every minute). Note: In practice, the amount of activity in y our logs may affect your ability to shrink the window for performing and shipping log backups.
Set backup compression. Indicate whether to compress the backups. This example assumes
that SQL Server Enterprise Edition is being used, so compression is an option.
Click OK when you have completed these settings, and return to the transaction log shipping
Database Properties screen. 6. On the Database Properties screen, add under the list of secondary server instances and databases, and select the secondary server. For this example, the secondary server is rwclustn2\logshipsecond. The Secondary Database Settings dialog box in Figure 4 will appear.
Figure 4 – Secondary Database Settings
7. If you have not already initialized a backup of your production database, use the default settings on this screen to have SSMS do the initialization. Important : If you have a large database or are trying to set this up in the middle of the day, use
an offline backup to restore to the secondary server. Then in this dialog box, select the backup you want to use.
8. Click the Copy Files tab to get to the screen that Figure 5 shows.
Figure 5 – Copy Files for secondary database settings
9. Enter the file path of the location for your log shipping files. This is the same path you entered earlier. Then, enter a name for the job and a schedule. The frequency of the copy jobs should correspond to the frequency you specified for the transaction log backups. Click the Restore Transaction Log tab, and the dialog box in Figure 6 will appear.
Figure 6 – The Restore Transaction Log tab of the Secondary Database Settings dialog box
10. On the Restore Transaction Log tab, use the default setting of No recovery mode. Note: This whitepaper does not discuss the standby mode option. If you want to use standby mode, see the topic in Books Online.
On this screen, you also have a number of choices for configuring the restore of your backups, including delaying the restore if you want to have a grace period before transaction logs are restored in full. You can also adjust how often the copied transaction logs are restored on your standby server. For this example, the default settings are used. Now, click OK to return to the completed Database Properties screen, which Figure 7 shows.
Figure 7 – The completed Log Shipping properties dialog box
11. Click OK on this screen, and you have finished configuring transaction log shipping for your database. Figure 8 shows the Save Log Shipping Configuration screen that confirms the configuration. Note: Because log shipping is configured for a single database, you would simply repeat this process if you wanted to log ship another database.
Figure 8 – The Save Log Shipping Configuration screen shows the log shipping actually taking place
Switching to the Secondary Server Once you have completed the log shipping configuration, test that you can switch to the secondary
server. When the automated jobs are running, they will back up the transaction log on your primary server, copy them physically to your secondary server, and then restore them to your secondary instance of SQL Server. They will restore the database with the NORECOVERY option so that transaction logs can continue to be restored. Imagine a scenario where you are able to back up the last bit of the transaction log, but otherwise need to switch to your secondary server. How should you handle this case? 1. Take the transaction log backup on your primary server and copy it to your secondary server. 2. Verify that your last log shipping job has run. 3. Restore the last bit of your transaction log without the NORECOVERY option. This will bring the database online and make it available for your users. Other Log Shipping Details This is where you have a little bit of a pause for further consideration about your log shipping
implementation. How do your users switch over to the secondary server? How do they know that something went wrong with the primary server? How do you switch back to the primary server? These topics are beyond the scope of this whitepaper but are issues you must research before deploying log shipping with your production system. Note that one of the major drawbacks of log shipping is that there is no automated client failover or failback solution. However, in terms of automation of the log shipping itself and ease of configuration, this is a straightforward high availability option.
Failover Clustering Failover clustering is high availability solution that was introduced in SQL Server 6.5. However, the failover clustering implementation has been extensively modified and rewritten in several releases, including SQL Server 2000, 2005, and SQL Server 2008. These modifications have included dramatic changes in the setup program, where much of the work of installing, configuring, and maintaining a failover cluster configuration is done. Conceptually, however, failover clustering has not changed much in all these releases. For Teamcenter, we will discuss failover clustering for SQL Server 2005 and SQL Server 2008. Failover Clustering Concepts The first step in understanding failover clustering is to understand the terms that are used (or misused)
in the technical community. Additionally, failover clustering is much easier to understand once you think of a clustered instance of SQL Server as running on its own logical computer. A cluster is simply one or more computers that are logically thought of as being in a “clustered”
relationship. Logically speaking, a single computer is a cluster of one. When you add a second computer, things get more interesting. A cluster typically means that the computer(s) that are part of the cluster are working for some common purpose. In a Windows Failover Cluster environment, the computers are physically and logically connected by various communications mechanisms and usually have at least one shared disk resource between them. A more typical SQL Server cluster has several disk resources shared between at least two computers. Creating a Windows Failover Cluster requires a series of configuration steps that vary slightly depending on the operating system. This whitepaper assumes Windows Server 2008 R2 as the operating system, and you can read about Windows Failover clustering here. The basic hardware for a cluster is essentially the same as for any other standalone computer but will have a couple of key differences. Although technically not required anymore, most clusters will have at least one disk that is physically connected to both computers in a cluster. Note: Depending on the version of the operating system, you may have many nodes in your cluster. However, for simplicity’s sake , this whitepaper will use a 2-node cluster as the standard configuration.
Additionally, there are typically at least two network cards in each computer: one for the public network, and one for the computers in the cluster to “talk” to each other without interference from public networks. Once you have the required computers in a cluster, you install the Failover Clustering Role in Windows Server and then create a logical failover cluster. The rest of this paper assumes you have completed that process and have a cluster with at least one shared disk resource.
A logical Computer When you install SQL Server as a clustered instance, you are asked for several components that look a
lot like you are setting up a computer. Underlying a clustered deployment is the fact that Windows is logically creating a “virtual” computer on the network to host your SQL Server instance.
A virtual computer is not the same thing as a virtual machine, which has a separate operating system running for you. However, the virtual computer has a unique IP address (or multiple IP addresses), a unique “computer” name assigned to your clustered instance, and the shared disk resources t hat the clustered SQL Server uses to store data and log files. All of these resources will be installed in a cluster group by SQL Server setup. On top of all of these resources is an instance of SQL Server, including the SQL Server and SQL Server Agent services. If you wanted to, you could also install a clustered instance of SQL Server Analysis Services. However, Analysis Services is typically not needed to support a Teamcenter environment. Once everything is installed, you have a “virtual” instance of SQL Server running on a single node of the
cluster (a computer in the cluster). Logically, however, you can think of having a virtual SQL Server available for connections just as if it were installed standalone on that computer. The difference is that you connect to the instance using the computer name assigned to the cluster —not the physical computer name on which SQL Server happens to be running at the moment. The key advantage of a failover cluster is that when the system fails over to the other computer —in this example, the other computer in the two-node cluster—the name of SQL Server remains the same. The same is true of the IP address(es). So while there may be some amount of downtime while switching nodes, from the client’s perspective, they will notice only that SQL Server is down for a very brief time (seconds or minutes) and then is available again to receive connections. Unlike with log shipping, with failover clustering, there is no administrative intervention, and clients don’t need to know the name of the secondary server or make any changes to their client-side configurations. After a failover, things just work. Active-Active Some users might want to have an active-active cluster configuration, which is multiple computers that
serve up a single workload of SQL Server. Currently, that is not possible with SQL Server. As an alternative, you implement a separately installed SQL Server instance: one instance on one node of your cluster, and another instance on the other node of the cluster by default. An active-active cluster is a way to further justify the expense of the hardware for a cluster so that one server is not sitting idle waiting for a failover to happen. However, one drawback to this strategy is that if there is a failure of one of the nodes, there will be significantly reduced CPU and memory capacity because the two instances of SQL Server are now running on a single computer. So why not add a third computer and in the event that node1 or node2 fails, have the default failover go to node3? That is certainly possible, and you can continue to scale that out for up to 16 nodes. However, you need the Enterprise or Datacenter editions of Windows and SQL Server to implement that strategy.
Active-Passive The active-passive configuration strategy is pretty straightforward. You have a clustered instance of SQL
Server installed, one computer (node) hosting the instance of SQL Server and SQL Server Agent, and the other node available in case anything goes wrong. Other than the cost of hardware that is idle except when a failover occurs, active-passive is an ideal configuration. Why? When a failure does occur and the memory and CPU configuration are identical for the two computers, operations continue shortly after a failover as if nothing had happened. From an end-user perspective, the Teamcenter application continues to function, and the same level of CPU and memory is available to process queries and support the application. In addition, you need only one SQL Server license for a 2+ node active-passive configuration. Installing a Failover Clustered Instance of SQL Server The installation procedure for SQL Server failover clustering has changed with every release of SQL
Server. For the examples in this paper, we us the setup steps for SQL Server 2008. There are two types of installations of failover clustering: a one-part setup or a two-part setup that includes a “prepare” phase. The example in this section demonstrates the one -part setup of a failover cluster. Note: To save space, this whitepaper shows only the unique dialogs for a failover cluster.
1. To start, launch the SQL Server setup utility. To do this, click the Installation link in the SQL Server Installation Center, and then select the option called New SQL Server failover cluster installation, shown in Figure 9.
Figure 9 – SQL Server Installation Center
The installation program checks the setup support rules, and then the setup support tools installation runs, as with a normal setup. After the setup support tools are installed, the setup support rules are checked again, this time focusing on a number of cluster-specific checks, as Figure 10 shows.
Figure 10 – Setup Support Rules with Cluster Validation
If the word “Warning” appears in the Status column for a rule, investigate what the warning
means. For purposes of this example, disregard the warnings. 2. Click Next to open the Product Key dialog box, and do the following: a) Enter the product key and accept the license agreement. b) For Feature Selection, select the database engine services. You do not need Analysis Services or Reporting Services for Teamcenter. c) Select a unique instance name. The instance name must be unique across all nodes of your cluster. d) If this is a new cluster, select a default instance. If you have other instances of SQL Server already installed on your cluster, select a named instance. 3. On the Cluster Resource Group screen, shown in Figure 11, you see what appear to be several errors. They are not errors; the text just indicates that other cluster groups already exist in the cluster. Simply click Next to create a new group for SQL Server.
Figure 11 – Cluster Resource Group
4. Now click Next to arrive at the Cluster Disk Selection dialog box, shown in Figure 12. Select the shared disks that you want to use for your SQL Server data and transaction log files. You should have at least one disk for database files and one disk for transaction log files.
Figure 12 – Cluster Disk Selection
5. Clicking Next will bring you to the Cluster Network Configuration dialog box, shown in Figure 13. On this screen, you can either specify manual IP address configurations for both IPv4 and IPv6 or use DHCP. DHCP is easier to use, but many server administrators prefer fixed IP addresses for servers.
Figure 13 – Cluster Network Configuration
6. When you click Next, you’ll see the Cluster Security Policy dialog box, shown in Figure 14. You can use either Service SIDs (i.e., use built-in accounts with new capabilities in Windows Server 2008 and above) or domain groups, as in earlier versions of SQL Server. Service SIDs cause fewer system management issues, so if you do not have to use domain groups for another reason, use Service SIDs.
Figure 14 – Cluster Security Policy
7. Click Next to arrive at the Server Configuration dialog box, shown in Figure 15, where you select your service account. This is very similar to any other setup of SQL Server, but note that you can’t change the startup type for the services because the clustering service will take care of starting and stopping the services.
Figure 15 – Service Account Configuration
8. After you configure the service accounts, click Next. The Service Account Configuration dialog box will appear. Add yourself as an administrator, and click Next to access screens that let you turn on error reporting and run the cluster installation rules. When the Install button appears, click it to run the installation. When the process finishes, you will have successfully installed a clustered installation of SQL Server. Although you now have a single- node cluster installation of SQL Server, this won’t improve your system availability yet because the cluster is capable of running only on a single node. You need to install SQL Server on a second node. 9. On Node 2, select the installation link again, and click the option to Add node to a SQL Server failover cluster , as Figure 16 shows. After the Setup Support Rules dialog box, you will see a
Cluster Node Configuration dialog box, which Figure 16 shows. Your instance should be automatically selected for you to add the current node to your cluster. Click Next.
Figure 16 – SQL Server Installation setup screen
10. Click OK once the rules run, and then install the SQL Server Setup Support Files. Click Next when that’s completed, and click through the setup support rules, product key dialog, and license terms, as before. You will now see the Cluster Node Configuration dialog box, as Figure 17 shows.
Figure 17 – Cluster Node Configuration
11. Your instance should be automatically selected for you to add the current node to your cluster. Click Next to accept the default (assuming it is correct), and then click Next for the service accounts, which you can’t change—they need to be the same as the other node of the cluster. Enter any passwords if you used domain accounts. 12. Click Next again to select your error reporting options to Microsoft, and then view the Add Node Rules for clustering. You should see only success messages; click Next. Finally, select Install to run the setup on this node of the cluster. Once setup is complete, you will have a 2 node cluster of SQL Server. If you wanted a 3- or 4-node cluster, you would repeat this process on each node for each instance of SQL Server that you install in your cluster. Maintaining a Failover Clustered Instance of SQL Server Maintaining a failover clustered instance of SQL Server is very similar to maintaining any other instance
of SQL Server. However, there are a couple of critical exceptions:
Service control. You don’t use the SQL Server Configuration Manager utility to control
clustered services. Instead, you use the Windows Failover Cluster Manager, shown in Figure
18. For example, if you want to stop and restart SQL Server Agent, you select Take this resource offline in the context menu, and then select Bring this resource online to complete the restart.
Figure 18 – Windows Failover Cluster Manager
Service packs. When you install a service pack on a failover cluster, you must follow the
directions in the readme file for the service pack. You install the service pack on each node of the cluster. Follow the directions carefully to ensure a successful installation of the service pack. When Should You Use a Failover Cluster? Basic protection from single-system failure is provided by a Windows Failover Cluster. If you want to
provide protection from system or software errors, failover clustering is often a good choice. It is particularly appealing because IT doesn’t have to change c onnection strings and users don’t have to know that there is high availability software running at all. The shared disk is a single point of failure, but that vulnerability can be alleviated through technology such as disk mirroring or, if you have a Storage Area Network (SAN), using SAN mirroring to protect your data and transaction log files.
Database Mirroring Teamcenter does not support database mirroring.
Replication Siemens does not support using replication in the database with Teamcenter.
Summary Siemens Teamcenter PLM software, coupled with Microsoft SQL Server database software, provides your enterprise with a solid platform for building an agile, globally competitive product development process. SQL Server provides an ideal database platform for Teamcenter. Its built-in features deliver reliability and security on a scalable foundation that helps organizations reliably manage large mission-critical workloads and complex business applications. Using the high availability options discussed in this paper can help you optimize the availability of Siemens Teamcenter for product lifecycle management and can help you avoid and minimize problems. The links in the following section provide even more resources to ensure a successful implementation of Teamcenter on SQL Server.
