Database Technology Trends

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.

There are 2 major database vendors working on their next big version of their database engines. Microsoft and Oracle are getting ready to release their best database system yet. Well, in the near future at least. Nevertheless here are some preliminary infos that leaked from the development teams.

Microsoft SQL Server 2010

Just last year in August we were introduced to SQL Server 2008, which finally brought us backup compression and data compression amongst many other new features. Many SQL Server customers are still recovering from the SQL Server 2005 migration and find it difficult to keep up with this breath taking speed of new releases.

So what’s new in SQL Server 2010?

It will build on the data warehouse improvement of SQL Server 2008 and adds even more support for multi terabyte databases.

The main focus of SQL Server 2010 will be on “managed self services”. Self tuning will be achieved by interpreting the Dynamic Management Views (DMV). Is this the death of the DBA? Not at all, it will redefine the skills and duties of a DBA in the day to day operations. But then again, let’s see if and how this works.

Emphasis on policies is another big change. Many policies are already available since SQL Server 2005, but in SQL Server 2010 they will be enforced by default.

The last improvement is focused on better email integration and integration into the Web 2.0 environment. Imagine; SQL Server goes Twitter.

After all, these are preliminary information available through some rumor mills and a little bit from the Microsoft website. One thing is for sure, with the release of SharePoint 2010 (beta available now), SQL Server 2010 will become even more important.

I’m pretty sure that there will be more information available soon.

Oracle 12g

Yes, you heard right. Oracle 12g is around the corner. There is not much information available on this new release. The only detail that leaked so far is that Oracle 12g won’t support raw filesystems anymore. This is bad news for RAC environments. The OCR and the voting disk relay on raw filesystems via CFS like OCFS.

The word is that ASM will step in and close the gap in 12g. Also, more emphasis on NFS will be placed as well.

Other than that, there’s not much information regarding functionality enhancements available. As soon as I get more details I will post it.

Sybase is not in the radar to release yet another major release in the near future. The focus is on synchronizing the ASE 15.0.3 release with the Sybase ASE CE (Cluster Edition) version. There is also a new project that will replace Sybase Central with a web based management tool. The ASE (standard and cluster edition) is already available.

Sybase just released a couple of major new releases in their product line, Sybase IQ 15, Sybase Replication Server 15 and Sybase ASE Cluster Edition mid last year. There are new major releases in planning, but not released in the near future like Microsoft and Oracle. That’s at least to my knowledge. One thing is remarkable with Sybase; they had the best quarter in Q1 of 2009 and I can’t wait to get the results for Q2.

One thing is always interesting to observe. This constant competition and the need to outperform drive these vendors to constantly push the envelope and we as the consumer will get better, faster and cheaper products.

The downside is that we have to constantly upgrade our systems. Over time this creates enormous strains on IT staff and budgeting. It seems that the pace of new major database releases has picked up noticable and it remains up to the IT managers to make the right call at the right time. The current cutting in staff and budgets is no help either.

Database vendors are packing more and more value added features into their systems to gain more customers and sell their product. Hopefully we will see a speedy recovery of the economy to enable these companies to bring back staff and put all these great features to work soon.

Thanks,

Peter Dobler

It was summer of 2000 when I first learned about Sybase IQ and its revolutionary column vector database technology. As a long time Sybase ASE and Oracle DBA I was used to database engines that organize data in a row by row method. For quite some time I had difficulties to think in column terms and not in row terms.

A column vector database requires totally different methodology for performance and tuning efforts. Nothing is straight forward and the message that more data volume doesn’t make a difference in the query performance is not easy to understand. For example: A traditional database engine allows the usage of only one index per table in the same query. Sybase IQ has no limits. If each column in the query requires a different index, it will use a different index. In fact, by default every column is an index.

Getting my hands around the fact of having queries perform up to a 1000 times faster on Sybase IQ than on traditional row based RDBMS systems is no easy matter either. Of course in an Oracle implementation with the OLAP technology similar results can be achieved. However, you are paying for the underlying OLTP engine regardless if you’re using it or not. Sybase IQ doesn’t have this overhead.

One of the key features of Sybase IQ is its data compression. I worked with Sybase IQ systems that easily exceeded 80% compression ratio. Meanwhile, every database vendor introduced data compression into their database engines, but Sybase IQ is the undisputed leader in the highest compression ratio of them all.

This post is not meant to explain how Sybase IQ works and why it is so superior in analytical query processing compared to its row processing based counter parts. Please click here to read more about Sybase IQ’s amazing technology.

I know that there are other data warehouse systems out there that are equally as fast as Sybase IQ and some are even faster, but in this article I am focused on the Sybase IQ engine and its recent setting of a new benchmark record for TPC-H transactions. This record is all about saving money while providing blazing fast performance. Please click here to read the detail report on this milestone.

OK, back to what’s new in version 15 of Sybase IQ.

There are two major improvements in the new release that are worth mentioning.

1. The overall query performance was once again dramatically improved and yields in an average 20%-50% performance gain, compared to the previous Sybase IQ release.

What does this mean for your business?

Analytical queries are typically CPU hungry monsters that can eat up your entire processing resources. Producing results faster means more queries will be processed in the same time window.

It also means the hardware upgrade can be postponed for a while. Considering that the associated QA requirements to move an entire production system to a new hardware platform can be a very expensive proposition and combined with the cost of the new hardware maybe not worth the investment. In comparison; a standalone upgrade of the database engine might be worth the effort.

It further means that cheaper server hardware on Linux can be used to build Sybase IQ multiplex systems that produce high end performance results on a slim budget. Due to Sybase IQ’s architecture there are no added network constraints either. 

2. Multiple writer nodes in a single multiplex environment.

This is an enormous step forward. Previously a typical Sybase IQ was build with one big server that acts as the writer node and many smaller servers for the reader nodes. The thinking was to provide the best hardware to the CPU intensive load jobs to minimize the load windows. The downside of this architecture was that in a failure situation, one of the smaller servers would take over the writer part and then would be helplessly overwhelmed in case the writer node couldn’t be fixed in time for the next load.

It is also economically not practical to devote high-end, expensive server hardware to a job that only last for a fraction of a daily work load. Having multiple writer nodes solves this problem once and for all.
Utilizing all the available processing power in a multiplex environment ultimately leads to faster load performance, which can be solved without upgrading the writer node server hardware over and over again.

Also, another data load performance improvement is the new feature of loading data directly from clients. This means that data can be loaded from files using a simple SQL statement instead of copying data files onto a server and then using the bcp command.

Of course there are other major improvements in security, flexibility and integration support, but the two improvements above are the major contributors to any cost savings or cost avoidance initiative a business is taking on these days.

Sybase also improved their client apps to better manage Sybase IQ, easier develop applications for Sybase IQ and more effectively monitor Sybase IQ. Once the Achilles heel of Sybase, these tools are now very usable and mature.

From a cost/performance point of view, Sybase IQ is a force to be reckoned with and due to its column vector architecture there is no other major database engine in the market like it. To support Sybase’s strong performance in technology they also had their best financial year ever in 2008 and the best quarter on record in Q1 2009.

I hope you enjoyed my brief introduction into Sybase’s data warehouse engine Sybase IQ and its latest version 15 features.

Take care,
Peter