Database Technology Trends

Data explosion and the always present request for faster data retrieval led to the introduction of more granular data distribution on physical disks. Even with faster disks, the increase in parallel processing created more contention on a single table.

The solution to these data base challenges was to split a single table into multiple partitions that could be accessed independently and still maintain the data integrity of a single table. A second solution was the introduction of row level locking to drastically reduce data insert contention.

Still, more data distribution challenges call for other solutions. Partitions were slowly introduced with Sybase ASE 11 to reduce contention and support parallel processing. However, it was not until ASE 15 that partitions jumped back into the spotlight.

Benefits Using Semantic Partitions

There are several advantages to using semantic partitions. For example, you’ll have reduced maintenance time and more predictable maintenance tasks that will reduce “just in case” maintenance, because you can perform maintenance tasks on just one or more partitions, instead of the whole table (which many DBAs practice).

A second benefit is for applications that are dealing with date and time based data, like sales data, telecommunication information, bank transactions or patient insurance information. They will all experience a dramatic performance gain with range partitioning.

Reduced maintenance windows and improved query performance with range partitioning also boosts productivity on more than one level.

Two of the semantic partition types offer specific gains:

• Hash partitioning doesn’t provide the flexibility of range partitioning. Yet, its strength is in spreading the data over as many partitions as possible to provide a performance boost to non-time based queries.
• Rolling range partitions benefit from archive data placements onto cheaper hardware.  This means that data can “age”, and older static data rows will reside on partitions that can be moved from” tier 1” storage to” tier 3” storage. Cost savings between “tier 1” and “tier 3” storage is significant. The more data, you can move to “tier 3” storage, the bigger the savings. Rolling range partitions are not a function of Sybase’s semantic partitions, but a technique to effectively manage date driven data. The key is to create date range partitions without a MAX limit. This will manage the data growth and future data distribution into the range partitions.

Reduce Maintenance Time Using Semantic Partitions

By using the new semantic partitions in Sybase ASE 15, DBAs are learning that having increased operational scalability is a genuine reality, as a result of reduced maintenance time.

Recent internal benchmarking at a client installation tracked time reductions for update statistics tasks y a factor of 25 – 30. The 25 – 30 factor is absolutely correct . . .  reductions for update statistic tasks by double digit factors, not percentages! These reductions are significant, and allow DBAs to reclaim their maintenance window. In other words, by using semantic partitions, a 3 hour update statistics job completes in 6 minutes.

Improve Availability and Maintenance Using Semantic Partitions

By reducing the maintenance windows, application availability automatically increases.  As mentioned earlier, reducing maintenance jobs from 3 hours down to 6 minutes will repurpose at least 2 hours for application use, which translates in 2 hours of added productivity for applications and users.

Semantic partitions also allow DBAs to be more selective and pro-active in the choice of maintenance tasks. In large systems, it is not uncommon for maintenance to be spread over every day of the week to guarantee a complete weekly cycle The time window allowed for maintenance is too short to complete the task in one step. Having the ability to complete this maintenance in the short time frame is a huge advantage. By using the semantic partitions, previously occupied system resources quickly become available again to applications.

What Type of Partitions Does Sybase Use?

There are different types of partitions that can be used to take control over the data distribution of a single table. Each table can maintain its own partition type.

Round-Robin Partitioning

This was the first partition type introduced in Sybase ASE 11.0.3. The data is evenly distributed amongst the total number of partitions available. There are drawbacks with this partition type. The most severe is that you cannot dynamically add a new partition to an already partitioned table. The only way to add a new partition is to unpartition the original table, and then repartition the table.

Another drawback to Round-Robin partitioning is that partitions need to be rebalanced manually and often. Each time you rebalance, you have to drop and recreate the clustered index for this table. Most installations used a very low number of partitions, no more than 10, because the rebalancing was labor- intensive, complex, and time-consuming.

This is the only partition type available prior ASE 15.

Semantic Partitioning Options

Sybase introduced semantic partitions in its ASE 15 version. Semantic partitioning uses the data values to decide which partition the data resides in vs. the session id.

Sybase has 3 types of semantic partitioning: range, hash and list.

Range Partitioning

The Range partitioning is the most flexible and adaptable partition type in ASE 15. Some of the advantages to using range partitioning are:

• Reduces maintenance time by eliminating the need to run maintenance tasks on older static data rows.
• Implements rolling, alternating or migrating partitioning schemes to constantly archive older data rows.

Range partitioning offers, by far, the most features and flexibility to manage time-period aware data. In today’s data explosion, the bulk of the data is growing in this area. An excellent use for range partitioning is financial transaction information, with time-period aware data.

Hash Partitioning

Like the Round-Robin partitioning, hash partitioning is distributed evenly over the available partitions. The advantage to hash partitioning is that no rebalancing is necessary. The Hash partition type is most effective with data that does not have a life cycle, like sales records, or data that does not require any form of range queries. This partition type is most likely used on primary keys.

The drawback is you have the same flexibility limitations for hash partitioning you had with Round-Robin partitioning.

List Partitioning

This partition type allows you to dynamically add new partitions without unpartitioning and repartitioning the table. Like hash partitioning, the list partitioning offers the best performance on queries with exact matches. This means “column = constant” works best with this partition type.

A natural fit for list partitioning data is data organized by geographic region or separating branch data by store location. The key to a successful list partitioning implementation is low cardinality. Based on performance benchmarks cardinality of less than 10 is most effective with list partitioning.

New Index Types

With the introduction of semantic partitions, Sybase also introduced a new type of indexes. Global and local indexes are being used in tandem to effectively manage the data on partitions. Local indexes are used to create an index on a single partition. Local indexes have no knowledge about data outside the partition they are managing. Thus, creating a unique key on a local index is not possible. The advantage to using local indexes is that the index tree has few levels, which reduces the amount of I/O. For example, if you can reduce local index levels from 6 or 7 to 3 or 4, your I/O reduction will be significant. You will have less contention on the index root page also. 

Global indexes spawn all partitions for the entire table, and index all rows in the entire table. To enforce primary key uniqueness, a global index is necessary. As a rule of thumb, use global indexes only if absolutely necessary. A performance benchmark study compared a drop partition task performed on a partitioned table with a global index vs. local indexes. The tasks completed in seconds when using local indexes, but ran for a couple of hours to keep the global index in sync.

Semantic Partitions Offer Full Control over Data Placement

When partitions were first introduced, the primary goal was to reduce contention during insert statements by creating several last data pages. Up to this point, clustered indexes were the only method to distribute inserts.

Prior to the semantic partitions in Sybase ASE 15, only Round Robin partitioning was available as a partition type. Round Robin partitioning solved the insert contention problem, and improved parallel SQL execution, but left another serious problem. Round robin partitioning gave DBAs no control over the data placement within the partitions.

With the introduction of semantic partitions, users now have full control over data placement within individual partitions of a table. This opens the door to very finely tuned maintenance tasks that slash the time required to complete these activities. Prior to semantic partitions, all maintenance tasks were at the table level. At that time, maintenance on all partitions of a table needed to be completed to finish the maintenance. Partitions now can be used to spawn several database processes in parallel, speeding up execution significantly.

Where Semantic Partitions Are Not a Good Fit

Semantic partitions can improve almost any data distribution situation. In many cases, there are performance improvements with queries as well. It is fair to say that adding semantic partitions offers improvement opportunities with almost no side effects.

However, applying semantic partitions to all tables is not a free ride for DBAs.  Administrative overhead still exists. DBAs need to keep an eye on partitions to ensure optimal performance to the applications.

Internal analysis showed that partitions have no effect on contention elimination when used on data row locking scheme tables. This means that row level locking provides enough separation to deal with table contention.

Before using range or hash partitioning, DBAs need to consider:

• Although the range partitioning is the most powerful semantic partition type, studies show there were no performance improvements past the date range search arguments on time period based data.
• Applying hash partitioning to time based data is not a good idea. It can have a negative impact on both the query performance and the maintenance time allocated to maintain this constellation.

Sybase provides the tools and metrics to determine the best use of semantic partitions. The two considerations mentioned above will help DBAs make the best decisions for their situation. DBA’s can use Sybase’s expertise, along with deciding what their own best performance needs are.  

What’s Next

Sybase is planning to introduce several new features on semantic partitions in the near future. All of them will further enhance the performance and the maintainability of partitioned data.
The most important future enhancements are:

• Unique keys on local indexes
• Merge partitions
• Split partitions

Sybase is committed to provide the best tools supporting DBAs to tame the data volume explosions and providing the highest level of database uptime to end-users and applications.

Conclusion

At first glance, semantic partitions may only seem to be valuable during maintenance tasks, but a closer look under the covers reveals that there are more benefits than meets the eye.

Hash partitioning and even Round-Robin partitioning over many partitions can dramatically improve performance over primary key data access. Hash partitioning thrives with equality search arguments in queries. List partitioning over low cardinality data, like geographic location distribution, can open new maintenance opportunities for DBAs maintaining a global data and user community. Performing data maintenance on partitions for geographic regions that are separated by global time zones allows DBAs to maintain optimal performance on databases that do not allow for downtime.

Rolling range partitions provide the capability to automatically “roll off” archive data, based on date ranges, onto cheaper disk hardware. This will save money and does not require additional maintenance. With range partitioning, maintenance tasks can be applied to individual partitions without the need to perform maintenance on all partitions of a table. With the datachange() function, DBAs can determine which partitions need maintenance. This allows for surgical precision in maintenance efforts and dramatically reduces the time frame to execute these tasks.

About the Author

Peter Dobler is an accomplished IT database professional who “makes a difference” by improving efficiencies and reducing costs for small and medium-sized businesses.  He founded Dobler Consulting, (www.doblerconsulting.com) a Tampa, Florida consulting firm that delivers implementation expertise for Oracle, Sybase, and MS SQL Server in 2000. His 25 plus years in technology started in Basle, Switzerland in 1985. Peter uses his extensive experience to hone his talent as a proven resource for producing streamlined IT solutions. He currently engages in strategic alliances and special projects with Sybase, the enterprise software and services company. He can be reached at: mailto:pdobler@doblerconsulting.com or 813-322-3240.

Read his Database Trends Blog (Behind the Scenes of Database Evolution) at: http://www.peterdobler.com, and his Technology Tips Blog (Step-by- step Instructions on Today’s Challenging Technology) at http://www.techtipsntrick.com.
Other Articles of Interest:
“Sybase ASE 15.5 — The Need for Speed”, Database Journal, www.databasejournal.com, May 2010

Reviews on Peter Dobler’s articles

“IMDB said to be the New Frontier of Database Architecture,” http://www.dbajobsandcareers.com, Database Blog, Review, June 1, 2010
“Performance Enhancement and the In-Memory DBMS Opportunity”, International Sybase User’s Group,  http://www.isug.com/common/Index.html, March, 2010

Keeping up with the news from so many different sources is getting harder and harder these days. That’s why I try to offer a one-stop-shop for your major database vendor news. You get the scoop and if you want to dig deeper, the links are right there. Let’s get started.

Sybase

I am not anticipating any new Sybase ASE version before this summer. None are announced. This is due to the massive efforts of certifying SAP R/3 on Sybase ASE, which is expected by the end of Q2. There were many joined events between SAP and Sybase since Sybase’s acquisition by SAP. The most notable is SAP TechED. The latest was in Berlin and drew a lot of interest, especially into Sybase mobility as well as Sybase IQ and ASE.

This year Sybase TechWave 2011 will be hosted within the SAP TechED USA in Las Vegas. This is the much needed boost for Sybase to a larger audience.

Sybase IQ 15.3 is expected to be released soon. The beta tests are going for quite a while. The highlight for Sybase IQ 15.3 is the PlexQ Distributed Query Platform, a Massively Parallel Processing (MPP) architecture that accelerates highly complex queries by distributing work to many computers in a grid configuration.

Sybase IQ 15.3 PlexQ Distributed Query Platform solution offers the following benefits:

• Extreme performance: enables users to harness the power of multiple compute resources in a Sybase IQ PlexQ environment to simultaneously execute queries across the grid. This is the next step after Sybase IQ 15 introduced multiple writers in the multiplex grid.
• Increased scalability: empowers system managers to easily and cost effectively scale out Sybase IQ PlexQ nodes with commodity hardware and managing SLAs in a high demand environment.
• Architectural flexibility: allows system managers to quickly and easily group subsets of compute resources as logical entities. This allows DBA to effectively utilize workload balancing and resource sharing.

2011 will be a very interesting year for Sybase and as I stated many times before, SAP made all the difference. Sybase is on its way back to gaining market shares as well as market interest.

Click here to read more about Sybase IQ.

Oracle

No, Oracle 12g is not here, yet. There is little or no information available on this topic. The only leaked information so far is that raw devices will no longer be supported. Well, you saw this coming, right? With the maturity of ASM you will probably skip raw devices even sooner.

Let’s focus on the latest release 11g R2. Many of the new features are focused on the cluster.

Single-Instance RAC: This will replace the custom DBSTART scripting DBAs had to do to automatically start all the Oracle instances upon system start. This will implement the restart features every node in a cluster is accustom to.

Cluster Time Synchronization Service: Synchronizing system times across all RAC nodes can be a chore. If you’ve ever experienced a node eviction within a Real Application Cluster database’s cluster configuration, you know how difficult it can be to tie together the train of events that caused the eviction.

ASM: Many new features that offers better support for clusterware.

ACFS: The ASM Clustered File System is the successor of the OCFS and the OCFS2. This allows for better management of the voting disk in a RAC configuration.

Improvements to software installation and patching: The previously standalone Cluster Verification Utility (CVU) is now integrated into the Oracle Universal Installer. Plus there is a new zero-downtime patching for clusterware.

There are several performance improvement features in 11g R2, especially for data warehouse environments.

These include:

• Instance “Caging”
• Automatic Degree of Parallelism
• Parallel Data Cache: In-Memory Execution.
• Faster Refreshes of Materialized Views

Data Guard: New disaster recovery options and better standby database management.

All these improvements and new features are part of the paradigm shift of Oracle that moves the database management closer toward a self-tuning, self-managed and self-healing database system.

Click here to read more about Oracle 11g R2

SQL Server

After 2 releases of SQL Server 2008 and SQL Server 2008 R2, Microsoft is releasing the new SQL Server 2011, code name Denali. At a first glance this is a developer’s release. Many new features are geared to better support SQL developers and a better integration into Visual Studio is also a key improvement.

Here are some of the new features:

Multi-Subnet Failover Clustering: This feature allows to geographically separate cluster nodes to provide disaster recovery and high availability. This feature depends on Windows Server failover cluster.

Sequences: Oracle style sequences for unique key value generation. Finally!

Query Paging: This is a feature that MySQL offers for years and now SQL Server is offering as well. Paging through result sets has some distinct performance advantages and simplifies coding efforts for developers.

The most significant change is the drop of DTS package support. This is not actually an announced feature, but an observation within SQL Server Management Studio. The Data Transformation Services menu option under the Legacy section has been removed. I think it is time for the hold outs to convert their old DTS packages to SSIS.

A little side note, SQL Server Management Studio now offers support for multi-screen environments.

Click here to download a trial of SQL Server 2011

Conclusion

Here you have it, a potpourri of new database technologies across vendors and systems. I hope you found something of interest to you and as always, please leave feedback.

Until next time,

Peter Dobler

Overview

Back in the 90’s, a group of German engineers put together the world’s first grid computing network. When putting together this network, they used over 100 PCs running on the first version of the Linux operating system. It was a great success, and everybody called it the dawn of a new technology that would change the computing world forever.

What these engineers didn’t understand was how database engines worked at the time. They also didn’t realize they were setting important trends in hardware development. The database engines were calling for Massive Parallel Processing (MPP) hardware systems that offered dozens of CPUs on one single server platform.

Database engines have evolved over the years. Sybase’s database engine, ASE Cluster Edition, has propelled its ASE technology to the next level. For many DBA’s, the new technology will be uncharted territory. Let’s look at some specifics to help you master the technology, and make it accommodate to your business and current computer systems.

Benefits of Sybase ASE Cluster Edition

For more than 2 decades, Sybase DBAs worked with the principle that every database belongs to one and only one Sybase server instance. Sybase ASE Cluster Edition tears down these limitations, by making it possible to share a single database with up to 32 Sybase instances on as many server nodes.

The benefits of having multiple instance accessing and writing to the same database are large in scale, and offer ease of use and flexibility:

• Built in high availability with instant failover from one instance to another. Applications can take advantage of server side failover without losing connectivity with minor changes to the client library.

• Consolidating many underutilized Sybase ASE instances into a Sybase cluster will ease the management burdens and provide instant high availability to these smaller servers.

• If you’re already using standby servers for your most critical applications, you can reduce the number of standby servers by moving these applications into a cluster environment. For example: instead of having 3 standby servers to protect 3 individual applications, you can move the 3 applications into a 4 node Sybase cluster and reduce the standby server to 1 node.

Avoiding Risks When Upgrading Your Existing Database Server

To take advantage of the benefits Sybase’s ASE Cluster Edition provides, you need to upgrade your current Sybase ASE server to the Sybase ASE Cluster Edition release. Before you jump to the installation CD, here’s a word of caution. Performing a heavy lift on a production system has many associated risks. You have to exercise due diligence to mitigate these risks to a minimum.

The major key success factors for this server upgrade are a rock solid plan and endless testing for this server upgrade. This is no different than any other major Sybase ASE upgrade. Another important factor is to give yourself enough time to do the job correctly, so that you will have a successful upgrade. 

Choosing Your Upgrade Options

To upgrade your existing Sybase ASE server to the ASE Cluster Edition, you have 2 upgrade options available. This article focuses on the in-place upgrade option and gives you the exact steps to upgrade to ASE Cluster Edition, while remaining on your existing hardware. There are many reasons why moving to new cluster hardware is not an option at all. Budget constraints are one; inflexible client access configurations are another reason.

Sybase makes it easy for you to upgrade from an existing Sybase ASE server to a Sybase ASE Cluster Edition without abandoning your existing hardware.

Move the database to a new ASE Cluster Edition installation.

This option requires you have spare hardware available, and offers the most benefits for your applications. If your application has a 24/7 requirement with no downtime option, installing a new cluster in combination with up-to-the-last transaction replication via Sybase Replication Server is your only option.

With great benefits come great requirements. In order to create a minimally intrusive upgrade with new hardware, all applications must be able to switch their database server access. This might be not an issue for internal clients, but it may be a problem for remote access clients. If you have remote access clients, it’s one more thing to think about and plan for resolution.

The basic steps for a cross server migration are:

• Create a new Sybase ASE Cluster Edition server on spare hardware.
• Export and load the user logins from the old database server to the new cluster.
• Dump and load your database(s) from your existing server to the cluster.
  With the XPDL technology, you can cross OS platform dump and load databases. Please reference the Sybase ASE Cluster Edition Installation manual for availability and limitations.

• (optional step) With Sybase Replication Server, the transactions since the last dump and load can be loaded into the new cluster.
• Synchronize the database users with the server logins.
• Redirect client access to the new server.

Prevent Problems by Taking Backups

Regardless of which option you choose to upgrade your ASE server, you must take good backups before you start. Yes, take really good backups!

In case of a glitch in the upgrade process, dump and load, or any other unplanned incident, you might not have the luxury of time to work through the problem. You are forced to reverse the upgrade to the original state. In most cases, this is a backup-restore process.

Prerequisites for the Sybase In-Place Upgrade

For this example, I’m using the example of upgrading an existing non-clustered Sybase ASE 15.0.3 server to a new 2 node Sybase ASE Cluster Edition 15.5 server with the in-place upgrade methodology. For simplicity I call the 2 nodes syb1 and syb2.

I want to discuss what is possible with the in-place upgrade, how the upgrade is done, and how to mitigate any problems.

Unlike cluster server preparations from other database vendors, Sybase’s prerequisites are fairly minimal and straight forward. Sybase requires you to have all database devices for your existing server on a SAN device. They help you put your database devices on the SAN device, using the old Sybase disk mirror to bail you out, and transfer all your devices to the SAN. Sybase does this for you without any downtime or negative impact to your server.

In addition to working with SAN devices, it’s helpful to understand the concepts of: shared disk architectures and cluster topologies, failover of nodes with network architectures, and knowledge of SCSI-3 devices, with I/O Fencing.

The basic steps to implement the upgrade are:

• Create a disk mirror of every local database device to a SAN device. To prepare for ASE Cluster Edition, you should make the SAN database devices slightly larger than the local devices to accommodate some ASE Cluster Edition overhead.

Command Example:

disk  mirror
name = "logical device  name" ,
mirror = "physicalname"

Please consult the System Administration manual for additional options that apply to your environment.

• Monitor the log file to see when the mirror is 100% in sync.
• The final step is to “break” the mirror and have the ASE server run entirely on the SAN storage.

  Command Example:

  disk  unmirror
  name = "logical device name"
  , side = "primary"
  , mode =remove 

Because every node in the cluster must see the database devices the same exact way, you should use logical device links as physical device names. This added abstract layer will protect your cluster from outages if nodes try to take over and the physical device names are different. Remember: logical device links as physical device names are your friends.

Understanding Database Prerequisites

Once you have your database devices available on your SAN device, you can focus on the database prerequisites. In order to qualify for an in-place upgrade, your existing ASE server must be on one of these releases and ESD levels:

• ASE 12.5 through ASE 12.5.4 ESD #8
• ASE 15.0 through ASE 15.5

Note: If you are on an earlier version, upgrade to a supported in-place upgrade version first, and then proceed with the Sybase ASE Cluster Edition 15.5 upgrade.

Sybase ASE Cluster Edition is a 64 bit system only. If you are on a 32 bit version of ASE, you need to apply additional steps to prepare for this upgrade scenario. For a more detailed list on system prerequisites, please reference the Sybase ASE Cluster Edition System Upgrade Guide.

Install the Sybase ASE Cluster Edition Software

Shared Installation

With the new 15.5 version, you have to option to install the software either as shared installation or private installation. If you use the shared installation, you need to have access to a shared filesystem that is accessible from every node in the cluster. Although the shared installation is more convenient, it presents other risks like a single point of failure if the shared filesystem goes offline.

Private Installation

The private installation provides a dependency separation between nodes for added stability and protection. The private installation installs the Sybase software on each node, and does not require a shared filesystem. You need to maintain a strict file structure and placement discipline, because every node must access the software identically.

Pre-Upgrade Steps

Once you completed the prerequisites, you need to shift your focus to preparing the databases, and the database server.

• If you are upgrading Adaptive Server, the previously installed version of the server must be running. If you are upgrading Backup Server, Historical Server, Monitor Server, or XP Server, those servers must not be running.
• Stored procedure text in the syscomments table is required for the upgrade. If you deleted the text, you must add it back again.

Note: As a best practice: if you don’t want to display the text, hide it by using the sp_hide_text stored procedure instead of deleting it.

• Resolve reserved words using quoted identifiers. This is a simple check by installing the upgrade package and then executing sp_checkreswords.

Caution: This step is simple enough, but if omitted, can lead to serious issues during the upgrade process.  

• Perform some standard tasks that apply to any database server upgrade.
• Verify users are logged off.
• Check for database integrity. Run DBCC commands to complete this step.
• Back up the databases.  As mentioned before, this will be your lifeline in case of a failed upgrade.
• Ensure that master is the default database for the “sa” user.

• Prepare the database and devices for upgrade by following these steps:
• Disable auditing
• Disable Job Scheduler by ensuring the “enable Job Scheduler” is off.
• Archive auditing data and truncate auditing tables.
• Disable disk mirroring.

  Note: Sybase ASE Cluster Edition 15.5 does not support disk mirroring. This is important if you used the disk mirror approach to move your local database devices to the SAN. Please make sure that all device mirrors have been disabled.

• Verify that your $SYBASE environment variable points to the location of the new Adaptive Server software files you just installed.

Manual Upgrade of an Existing ASE Server

Your upgrade approach will be completely different based on the various upgrade options. I want to focus on the manual upgrade from a non-cluster ASE server to the ASE Cluster Edition 15.5.

For the full details of the manual upgrade, please review the Sybase ASE Cluster Edition Upgrade manual. The summary of the steps is:

• In order for Sybase ASE Cluster Edition to work and communicate, the unified agent must be running on each node of the cluster.

Note: Now is a good time to get into the habit of starting, and verifying the unified agent before starting any database server.

Start the Unified Agent:

$SYBASE/UAF-2_5/bin/uafstartup.sh &

• Start your existing Sybase ASE server. Change the $SYBASE and $SYBASE_ASE variables to reflect the new location of the software. This process must be repeated when a restart of the existing Sybase ASE server is required.
• Execute the $SYBASE/$SYBASE_ASE/upgrade/preupgrade command from the new software location to prepare your server for the upgrade. If there are errors reported, correct them and restart your existing Sybase ASE server. Repeat this step until no errors are displayed.
• Check your existing Sybase ASE databases for new “reserved words” by installing and executing the sp_checkreswords stored procedure. Correct any errors prior to continuing the upgrade process.

Caution: Omitting this step can lead to serious problems during the upgrade process.

• One important part of installing a Sybase ASE Cluster Edition server is the requirement to have at least 2 network connections; 3 connections are even better. The additional network connections are needed for the server to interconnect via a primary private network and an optional secondary private network. In our example, we are using 2 private interconnects. Plus the public network access.

• After shutting down the old server, you need to proceed with the cluster preparation. The first step is creation of a new cluster input file that describes your cluster environment. The first instance of the cluster must be the old server name. For this example, the filename mycluster.inp has been chosen.

In addition, you need to have the network interconnect working. This is the back bone connection between the cluster nodes.

Here is an example of the mycluster.inp file, based on a shared installation:

#all input files must begin with a comment

[cluster]
   name = mycluster
   max instances = 2
   master device = /dev/raw/raw1
   interfaces path = /sybase/
   traceflags =
   primary protocol = udp
   secondary protocol = udp

[management nodes]
   hostname = syb1
   hostname = syb2

[instance]
   id = 1
   name = syb1
   node = syb1
   primary address = syb1-ppriv
   primary port start = 38456
   secondary address = syb1-spriv
   secondary port start = 38466
   errorlog = /sybase/ASE-15_0/install/syb1.log
   interfaces path = /sybase/
   traceflags =
   additional run parameters =

[instance]
   id = 2
   name = syb2
   node = syb2
   primary address = syb2-ppriv
   primary port start = 38556
   secondary address = syb2-spriv
   secondary port start = 38566
   errorlog = /sybase/ASE-15_0/install/syb2.log
   interfaces path = /sybase/
   traceflags =
   additional run parameters =

• Create the quorum device with the input file create in step 6. This is the core of the share disk cluster.

  Start the new instance with the old master device:

  $SYBASE/$SYBASE_ASE/bin/dataserver\
  --instance=server_name\
  --cluster-input=mycluster.inp\
  --quorum-dev=/dev/raw/raw102
  --buildquorum
  -M$SYBASE

• You’re ready to run the upgrade utility. instance_name is the first instance in your cluster that has the same name as the server from which you are upgrading:

  $SYBASE/$SYBASE_ASE/upgrade/upgrade  -S instance_name –Ppassword

• Create a tempdb for each instance in the cluster.

Note: This step is important. Without having the global temporary database for the second node in place, the cluster won’t start.

1>create system temporary database tempdb1  for instance syb1 on tempdb1 = 100
2>go
1>create system temporary database tempdb2 for instance syb2 on tempdb2 =  100
2>go

tempdb1 and tempdb2 are new raw devices on the SAN, accessible by both nodes. The size of the tempdb is arbitrary.

• Restart the cluster with the quorum device in the run file:

  $SYBASE/$SYBASE_ASE/bin/dataserver
  --instance=server_name\
  --quorum-dev=/dev/raw/raw102\
  -M$SYBASE

• Finish the upgrade with running a few scripts as described in the Sybase ASE Cluster Edition installation manual.

Note: This is an abbreviated version of the entire install procedure, but it demonstrates how straight-forward the upgrade actually is. As always, please review the Sybase ASE Cluster Edition Upgrade manual for details, as the configurations may be different for your environment.

Once you upgraded your existing Sybase ASE server, you can add new nodes and convert your non-clustered ASE server into a multi node cluster with ease.

Conclusion

Upgrading your existing Sybase ASE server to Sybase ASE Cluster Edition is pretty straight forward. Especially if your ASE Server is on release 15.x. Keep in mind that with the 15.x release, a new query optimizer was introduced, and extra steps to mitigate possible performance degradation have to be exercised. Once you upgraded your ASE server, you now have access to new tools and methods to address availability and scalability challenges.

In my humble opinion, this is possibly the easiest upgrade path from a non-cluster database system to a shared disk cluster. Sybase ASE Cluster Edition brings your organization better database resources, uses less hardware, and strengthens your computer applications.

About the Author

Peter Dobler is an accomplished IT database professional who “makes a difference” by improving efficiencies and reducing costs for small and medium-sized businesses.  He founded Dobler Consulting, (www.doblerconsulting.com) a Tampa, Florida consulting firm that delivers implementation expertise for Sybase, Oracle, and MS SQL Server in 2000. His 25 plus years in technology started in Basle, Switzerland in 1985. Peter uses his extensive experience to hone his talent as a proven resource for producing streamlined IT solutions. He currently engages in strategic alliances and special projects with Sybase, the enterprise software and services company. He can be reached at: mailto:pdobler@doblerconsulting.com or 813-322-3240.
Read his Database Trends Blog (Behind the Scenes of Database Evolution) at: http://www.peterdobler.com, and his Technology Tips Blog (Step-by-Step Instructions on Today’s Challenging Technology) at http://www.techtipsntrick.com.

Other Articles of Interest:

“Sybase ASE 15.5 — The Need for Speed”, Database Journal, www.databasejournal.com, May 2010.
“Sybase ASE 15 – Semantic Partitions to the Rescue”, http://www.sybase.com/files/Feature_Articles/Sybase_ASE15_SemanticPartitions_article.pdf.

Reviews on Peter Dobler’s articles

“IMDB said to be the New Frontier of Database Architecture,” http://www.dbajobsandcareers.com, Database Blog, Review, June 1, 2010
“Performance Enhancement and the In-Memory DBMS Opportunity”, International Sybase User’s Group, http://www.isug.com/common/Index.html, March, 2010.