Sybase Archives » Musings of an IT Implementor

Complications of using SAP ASE 16.0 in a HADR pair plus DR node Setup

Firstly, we need to clarify that HADR in SAP ASE speak, is the SAP ASE feature-set name for a HA or DR setup consisting of 2 SAP ASE database instances with a defined replication mode.

The pair can be either for HA or DR, but rarely both, due to the problem of latency.
The problem of latency is inverse to the solution of DR. The further away your second datacentre, the better, from a DR perspective.
Conversely, the worse your latency will become, meaning it can only seriously be used for DR, and not for HA.

If you can find a sweet spot between distance (better for DR) and latency (better for HA), then you would have a HADR setup. But this is unlikely.

As of ASE 16 SP03, an additional DR node is supported to be incorporated into a HADR pair of ASE database instances.
This produces a 3 node setup, with 2 nodes forming a pair (designed to be for HA), then a remote 3rd node (designed for DR).
The reason you may consider such a setup is to provide HA between the two nodes, maybe within an existing datacentre, then DR is provided by a remote 3rd node.
Since the two nodes within the HA pair would likely have low latency, they would have one replication mode (e.g synchronous replication) keeping the data better protected, with the replication mode to the third database being asynchronous, for higher latency scenarios, but less protected data.

In the scenarios and descriptions below, we are highlighting the possibility of running a two node HADR pair plus DR node in public cloud using a paired region:

Whilst an SAP application layer is also supported on the 3 node setup, there are complications that should be understood prior to implementation.
These complications will drive up both cost of implementation and also administrative overhead, so you should ensure that you fully understand how the setup will work before embarking on this solution.

Setup Process:

We will briefly describe the process for setting up the 3 nodes.
In this setup we will use the remote, co-located replication server setup, whereby the SAP SRS (replication server) is installed onto the same servers as the ASE database instances.

1, Install primary ASE database instance.

2, Install Data Movement (DM) component into the binary software installation of the primary ASE database instance.

3, Install secondary ASE database instance.

4, Install Data Movement (DM) component into the binary software installation of the secondary ASE database instance.

5, Run the setuphadr utility to configure the replication between primary and secondary.

This step involves the materialisation of the master and <SID> databases. The master database materialisation is automatic, the <SID> database is manual and requires dump & load.

Therefore, if you have a large <SID> database, then materialisation can take a while.

6, Install tertiary ASE database instance.

7, Install Data Movement (DM) component into the binary software installation of the tertiary ASE database instance.

8, Run the setuphadr utility to configure the tertiary ASE instance as a DR node.

This step involves the materialisation of the master and <SID> databases. The master database materialisation is automatic, the <SID> database is manual and requires dump & load.
Therefore, if you have a large <SID> database, then materialisation can take a while.

In the above, you can adjust the replication mode between primary and secondary, depending on your latency.
In Public cloud (Microsoft Azure), we found that the latency between paired regions was perfectly fine for asynchronous replication mode.
This also permitted the RPO to be met, so we actually went asynchronous all the way through.

POINT 1:

Based on the above, we have our first point to make.

When doing the dump & load for the tertiary database, both master and <SID> databases are taken from the primary database, which in most cases will be in a different datacentre, so materialisation of a large <SID> database will take longer than the secondary database materialisation timings.

You will need to develop a process for quickly getting the dump across the network to the tertiary database node (before the transaction log fills up on the primary).

Developing this fast materialisation process is crucial to the operation of the 3 node setup, since you will be doing this step a lot.

Operational Process:

We now have a 3 node setup, with replication happily pushing primary database transactions from primary (they go from the Replication Agent within the primary ASE instance), to the SRS on the secondary ASE node.
The SRS on the secondary instance then pushes the transactions into the secondary ASE instance databases (master & <SID>) and also to the SRS on the tertiary ASE database instance.

While this is working, you can see the usual SRS output by connecting into the SRS DR Agent on the secondary node and issuing the “sap_status path” command.
The usual monitoring functions exist for monitoring the 3 ASE nodes. You can use the DBACockpit (DB02) in a Netweaver ABAP stack, the ASE Fault Manager or manually at the command line.

One of the critical processes with an ASE HADR setup, is the flow of transactions from primary. You will be constantly engaged trying to prevent the backlog of transactions, which could cause primary to halt database commits until transaction log space is freed.
By correctly sizing the whole chain (primary, secondary and tertiary transaction logs) plus sizing the inbound queues of the SRS, you should have little work to do on a daily basis.

POINT 2:

It’s not the daily monitoring that will impact, but the exceptional change scenarios.
As an example, all 3 ASE database instances should have the same database device sizes, transaction log sizes and configuration settings.
Remembering to increase the device, database, transaction log, queue on each of them can be arduous and mistakes can be made.
Putting a solid change process around the database and SRS is very important to avoid primary database outages.
Since all 3 databases are independent, you can’t rely on auto-growby to grow the devices and databases in sync. So you may need to consider manually increasing the device and database sizes.

Failover Process:

During a failover, the team need to be trained in the scenario of recovery of the data to whichever database server node is active/available/healthy.
The exact scenario training could be difficult as it may involve public cloud, in which case it may not be possible to accurately simulate.
For the 3 node SAP ASE HADR + DR node, the failure scenario that you experience could make a big difference to you overall recovery time.

When we mention recovery time, we are not just talking about RPO/RTO for getting production systems working, we are talking about the time to actually recover the service to a protected state.
For example, recovery of the production database to a point where it is once again adequately protected from failure through database replication.

Loss of the primary database in a 3 node setup, means that the secondary node is the choice to become primary.
In this scenario, the secondary SRS is no longer used. Instead the SRS on the DR node would be configured to be the recipient of transactions from the Replication Agent of the secondary ASE.
If done quickly enough, then re-materialisation of the tertiary database can be avoided as both secondary and tertiary should have the same point-in-time.
In practice however, you will find more often than not, that you are just re-materialising the DR node from the secondary.
In some cases, you may decide not to both until the original primary is back in action. The effort is just too much.

Loss of the secondary database in a 3 node setup, means that the primary becomes instantly unprotected!
Both the secondary node and the tertiary node will drift out of sync.
In this scenario, you will more than likely find that you will be pushed for time and need to teardown the replication on the primary database to prevent the primary transaction lo filling.

Loss of the tertiary database in a 3 node setup, means that you no longer have DR protection for your data!
The transaction log on the primary will start to fill because secondary SRS will be unable to commit transactions in the queue to the tertiary database.
In this scenario, you will more than likely find that you will be pushed for time and need to re-materialise the DR database from the primary.
Time will be of the essence, because you will need transaction log space available in the primary database and queue space in the SRS, for the time to perform the re-materialsation.

POINT 3:

Sizing of the production transaction log size is crucial.
The same size is needed on the secondary and tertiary databases (to allow materialisation (dump & load) to work.
The SRS queue size also needs to be a hefty size (bigger than the transaction log size) to accommodate the transactions from the transaction log.
The primary transaction log size is no longer now just about daily database transactional throughput, but is also intertwined with the requirement for the time it takes to dmp & load the DB across the network to the DR node (slowest link in the chain).
Plus, on top of the above sizings, you should accommodate some additional buffer space for added delays, troubleshooting, decision making.

You should understand your dump & load timings intricately to be able to understand your actual time to return production to a protected state. This will help you decide which is the best route to that state.

Maintenance Process:

Patching a two node ASE HADR setup, is fairly simple and doesn’t take too much effort in planning.
Patching a three node setup (HADR + DR node), involves a little more thought due to the complex way you are recommended to patch.
The basics of the process are that you should be patching the inactive portions of the HADR + DR setup.
Therefore, you end up partially patching the ASE binary stack, leaving the currently active primary SRS (on the secondary node) until last.
As well patching the ASE binaries, you will also have to patch the SAP Hostagent on each of the three nodes. Especially since the Hostagent is used to perform the ASE patching process.
Since there is also a SAP instance agent present on each database node, you will also need to patch the SAP Kernel (SAPEXE part only) on each database node.

POINT 4:

Database patching & maintenance effort increases with each node added. Since the secondary and DR nodes have a shared nothing architecture, you patch specific items more than once across the three nodes.

Summary:

The complexity of managing a two node SAP ASE HADR pair plus DR node should not be underestimated.
You can gain the ability to have HA and DR, especially in a public cloud scenario, but you will pay a heavy price in overhead from maintenance and potentially lose time during a real DR due to the complexity.
It really does depend on how rigid you can be at defining your failover processes and most importantly, testing them.

Carefully consider the cost of HA and DR, versus just DR (using a two node HADR setup with the same asynchronous replication mode).
Do you really need HA? Is your latency small enough to permit a small amount of time running across regions (in public cloud)?

Password-less SAP ASE Database Access

UPDATE: Since ASE 16.0 SP03, the aseuserstore binary is now available. This has the ability to construct a local, encrypted credential store for each Linux user, and can be easily used (just like hdbuserstore for HANA) to automate password-less database calls via isql with the “-k” parameter.

Like most companies, you will have the need to automate specific database tasks directly in the database.
As an example, frequent database backups.

In this post I go through some possible options, but discuss in-depth the use of the sybxctrl binary to facilitate password-less access to execute database tasks.

What are the DB Job Automation Options?

Within SAP ASE databases, if you have an attached ABAP stack, then DB13 can be used to schedule database level jobs, with the caveat that the ABAP stack must be running for jobs to run.

Another alternative, is to use the SAP ASE JobScheduler to automate tasks.
The JobScheduler comes with the SAP ASE system and runs as a separate process.
Using the JobScheduler you can configure jobs/tasks to be executed even if there is no ABAP stack.
However, it means that any configuration must be repeated across all databases and you will lack any central job scheduling control. Also, if the JobScheduler has a problem connecting to the database, then you will not get any backups (especially transaction log backups).

As a good catch-all and an alternative to the above options, there is another way.
Use an enterprise job scheduler (e.g. SAP Business Process Automation) combined with a centrally accessible server-side script to call the database routine/utility.
One central schedule and one script. Simple.
The added advantage being that the central job scheduler will also be responsible for controlling your SAP business processes. This allows an operator to tightly control and monitor the backup window around the critical business processes and also allows tighter integration of backups with cloud level operations (VM snoozing…).

There’s generally one problem with the use of a custom script, and that is where to put the password for the SAP ASE database login.
You can hardcode it into the script, and restrict permissions as much as possible, but this means you will have just one password (unless you put ALL the passwords for all the databases the script will be executed against). It’s also a big “NO” from the auditors and cyber security teams, since there are a undoubtedly some exploits available that will allow access to the script file in some form or another.

Introducing sybxctrl

A possible solution to the problem of where to put the password, is to use a little known binary of the SAP Kernel for ASE databases, called sybxctrl.
Within your usual DIR_CT_RUN location, there will exist somewhere, a binary called sybctrl and its twin, sybxctrl.
They are the same binary code but separate files.

What’s the difference? Well, it’s purely about Linux permissions.
After you install an ABAP Kernel (on Linux), you’re supposed to run the saproot.sh script, which prepares certain SAP binaries, adjusts ownerships and configures executable permissions.
With regards to sybctrl, it is owned by the Linux root user, and it also has the SUID permission set (numerical: 4750, character: swxr-x—).

When this binary is executed by any member of the sapsys Linux group (<sid>adm or syb<sid> users are members of this group), then it will switch executable context to that of the root Linux user. In essence, it executes almost as if you were the root user executing it.
This is a useful Linux feature, to be able to execute binaries in this way; however, it can also leave a security hole.

To provide a level of protection to the O/S during the standard Linux binary execution process, when an SUID enabled binary is executed the Linux environment puts a restriction around the contents of the user’s environment by arbitrarily setting variables like PATH and LD_LIBRARY_PATH.
This means that execution of sybctrl will limit the ability for loading of additional shared libraries in non-standard directory locations, which may be required for certain functions.

Why does sybctrl need root? This is fairly easy to explain, it’s because you normally log onto a Linux SAP system as the <sid>adm Linux user then start all components including the SAP ASE database. Except the SAP ASE database runs as Linux user syb<sid>. So, to be generic, the binary sybctrl is used and executed from <sid>adm via the root user (probably so that shared memory can be freed), before it switches to syb<sid> to start or stop the database.

So how does sybxctrl differ to its more powerful sybctrl? Simple, it doesn’t have the SUID permission set on it.
It doesn’t need to run via root for what it does, but it does need access to the SYBASE_OCS and other shared library locations. So, to ensure that those environment variables are maintained throughout execution, this separate copy of sybctrl was required.

How can we use sybxctrl?

As the <sid>adm Linux user, you can execute sybxctrl with no parameters to return the usage information (the parameters that it does accept).
Of interest to us for our password-less database script, are the parameters:
load_script
unload_script
exec_script

Here’s how we can use sybxctrl to embed a script into the database, then execute it without needing a password.
Before we can use the sybxctrl binary, you need to first ensure:
– You have Kernel 7.22_EXT pl500 and above (or 7.49+).
– You run the saproot.sh to correctly set up permissions on the SAP Kernel binaries.
– You run a “stopdb” as <sid>adm. This will actually copy across the sybxctrl from sybctrl.
– You run sybxctrl to load in a script.

During the execution of sybxctrl with the “load_script” parameter, a new database table called SYBSISQL is created in the DBO schema (not the usual SAPSR3[DB] schema!) of the <SID> ASE database. This table is where the script that you load into the DB, is stored.
Let’s now create a test script as Linux user <sid>adm:

echo "select user_name()\ngo" > /tmp/myscript.sql

All this script will do is print out the current database username.
Now we load the script into the database using the sybxctrl binary and the “load_script” parameter as follows:

sybxctrl load_script /tmp/myscript.sql -exe $SYBASE/$SYBASE_OCS/bin/isql -auth sapsa

You are prompted for the sapsa database user password.
Enter the password and the load should succeed.

What just happened?
You have just populated that new database table (<SID>.DBO.SYBSISQL) with the text of the script that you created, plus the details of how to run it (which binary and the full path, plus parameters).
We have told sybxctrl that we want to use the isql binary (SAP ASE command line SQL utility), and that we want it to execute as the sapsa database user (the “auth” parameter).

Now we’ve loaded the script, we can unload it and check it as follows:

rm /tmp/myscript.sql

sybxctrl unload_script myscript.sql /tmp/

NOTE: You remove the path from the name of the script.
You are prompted again for the sapsa database user password.
Enter the password and the unload should succeed.

We can now see the script contents:

cat /tmp/myscript.sql

Finally, we can now try and execute the script.
The execution happens as though it was a batch operation, so we need to provide an output file for any output from the script.

Execute the script as follows:

sybxctrl exec_script myscript.sql /tmp/myscript.out -auth sapsa

Did you notice, you didn’t need to enter the password for sapsa!

Check the output file that we specified:

cat /tmp/myscript.out

To remove the script from the database:

sybxctrl delete_script myscript.sql

Enter the sapsa password and the deletion should succeed.

We can now embed a pre-configured script into the SAP ASE database, which can be executed at the O/S level as any Linux user with permissions to run sybxctrl, without requiring a password and it will execute as the sapsa database user.
There are many possibilities for this setup when you understand the full parameter list of both sybxctrl and isql.
As an example, you could pipe in parameters to the embedded script…
Couple the script with some standard code and configure the ASE database to use ASE backup configurations and you could have an automated backup routine that is password-less.

Are there any problems in using sybxctrl?

Yes, a few.

sybctrl will be patched as part of the kernel patching, but synxctrl is not. So you must ensure that you either run a “stopdb” at some point to pickup the new script, or manually copy it into place after Kernel binary patching is completed.
isql will be patched as part of the ASE patching. This changes the checksum value of the binary file which is recorded during the “load_script” in the new SYBSISQL table.
Therefore, after ASE binary patching, you should unload and re-load the script to re-create the table entry.
Any embedded scripts will be carried along with any SAP system copies if you use the database backup/restore method. But not if you use the R3load tools.
If you use SAP Replication Server (SRS), this table and it’s contents will be replicated across to the secondary database (as the table is in the <SID> database).
Bear this in mind for the contents of the backup script, because they may need to be generic if you want to run it on secondary or tertiary databases in an SRS setup.

Happy scripting.

SAP on ASE (Sybase) – 10 Positive Points

Having been part of a major programme of work to move a complete SAP landscape from an Oracle based platform onto an SAP ASE (formerly Sybase) database based platform, I experienced running SAP on ASE for the first time.
My main experience with SAP has been on Oracle, SQL Server, DB2 and HANA.
So it was a new experience seeing it run on ASE.
There were a lot of pain points (a future blog article, I’ll link back here), and yet there are always some positive points.
Below I have documented 10 of what I feel are the positive points of running SAP on the ASE database platform in comparison with other database solutions (I’m not including HANA as it is far more expensive). This may or may not help you make your own decision.

1 License Cost
There is undoubtedly an incentive provided by SAP for migration from rival database vendors such as Oracle or IBM.
The cost of running SAP ASE is much reduced by aggressive discounting by SAP in return for eradicating these vendors from the SAP landscape.
You should therefore ensure that you seek to reap the best deal if you’re considering migrating to SAP ASE.

2 No Archive Logs
Unlike an Oracle database, the SAP ASE database does not have an archive log function.
This means, in simple terms, that there are only 2 parts to the database to worry about; the database data files and the transaction logs (equivalent to redo logs).
Whilst this may be considered to be additional risk to some, the advances in storage technology only help to negate the need for an additional process for transactional integrity.
This may not be clear cut to some (myself included) but to others, this represents a removal of added complication in the system architecture.

3 No Separate Connection Listener
Out of the box, the SAP ASE database software, when started, initiates only 3 server processes. The dataserver, the backupserver and the job scheduler.
The dataserver provides the database functionality and also listens on the required ports for incoming database connections.
For an SAP system this is the standard tcp/4901 port.
Unlike an Oracle database platform, there is no need for a standalone network connection listener.
The entire database software stack is started and stopped in one go.

4 Small Codebase
The SAP ASE software is small compared to IBM DB2 or Oracle. The download is roughly 1.3GB in size and the deployed (on disk) size is typically 2 to 3GB.
This means less demand for disk space for software binaries in an ASE environment. Typically this diskspace in an SAP landscape is replicated many many times for each SAP system type.
e.g. 3x ERP (dev, qa & production) would all required separate disk storage areas.
In a typical support environment, you would also be looking to retain the last 2 revisions of the database software binaries in your software repository.

5 Integrated Install
The mechanism for installing SAP ASE (recommended by SAP) is to use the SAP software provisioning manager (SWPM).
Part of the SAP Software Toolset, the SWPM provides a client-server based, common graphical user interface, for installing a multitude of SAP software.
The SAP ASE software is installed using the SWPM onto Windows, Unix or Linux and typically can be done in under 3 minutes depending on the initial database datafile size required.

6 Simple Patching
Routine patching of SAP software is well known in the industry for being a complete chore; although crucial.
There is complete underestimation, in my experience, of the fundamental effort required to successfully patch an SAP system and test it.
Often the decision is left until there is no other option.
Patching of SAP ASE is performed in 3 simple steps: 1, Download the patched software. 2, Using SAPControl install the patch. 3, Test.
There are no minor patches or binary only fixes in SAP ASE. An entire software stack is re-delivered (it’s small).
The database changes (DML and DDL) are applied as part of the patching process, but it’s all initiated from one command line string.

7 SAPControl Integration
Starting and stopping of the SAP ASE software can be performed in at least three different ways.
However, SAP have tried to make this as transparent as possible by integrating the ASE startup into the SAPControl framework.
This allows “startsap” to bring the entire SAP system up, including the database.
It also allows slightly more complex situation whereby the database can be completely started / stopped using the additional tools within the SAP software library, such as Landscape Virtualisation Management (LVM).
Incidentally, the SAPControl mechanism is also the method for initiating the SAP ASE software patching process.

8 DBACockpit Integration
Over the years SAP have standardised and massively improved the database management interface transaction DBACOCKPIT.
From this transaction, you can interactively manage the SAP ASE database through functions such as:
– Parameter configuration.
– Database space management.
– Performance measurement and analysis.
– Database maintenance and housekeeping tasks.

The benefit of the DBACOCKPIT is that for the past few years it is now configured automatically in SAP Solution Manager, so you can see all databases in the entire SAP landscape from within one transaction.
Some of the newer features of DBACOCKPIT (in Netweaver 7.02 SPS15+) are automated parameter configuration according to SAP recommended values.
This reduces the need to extract the recommended parameters and compare with you database’s configuration.

9 Solution Manager Integration
Along with DBACOCKPIT, the SAP ASE software is integrated into Solution Manager end-to-end monitoring.
SAP ASE database extractors pull database performance data into the SAP Solution Manager’s SAP BW module, for analysis by the BASIS team.
Tracking of database storage capacity can be easily measured and alerted on from within the built-in Solution Manager technical monitoring capability.

10 Published SAP Ntes
Unlike other database vendors, the details of issues within SAP ASE are published within the SAP notes system.
This means that accessing information on software bugs or issues, or finding information on performing technical administrative tasks, can be found within the SAP support portal.

SAP on ASE (Sybase) – 10 Lessons Learned

Continuing my SAP on ASE theme (see my other post on 10 positive points of SAP on ASE), I’m going to document the 10 best lessons I learned during the migration and implementation of SAP on SAP ASE (formerly Sybase).
Hopefully these will be useful to any implementation or migration project you may be undertaking.

1 Compatibility with SAP Business Suite
SAP provide SAP ASE for two groups of end-user; standalone use, or use with SAP Business Suite.
The SAP Business Suite is the name given to the suite of software packages such as ERP, BW, CRM, SRM etc.
Due to the 2 distinctions, there are always two delivery channels for the SAP ASE software binaries.

Certain software revisions of SAP ASE are certified (supported) for use with SAP Business Suite products, therefore you should always check if your use case is supported and that you download the relevant software version.

You should also ensure that you validate the information within the SAP notes, to ensure that it is relevant to your use case.
In some situations, there are SAP notes specifically only valid for SAP ASE in use with SAP Business Suite products.

2 Download the Latest
In my experience, the version of SAP ASE will make a big difference when considering the implementation project.
You should consult with your account manager to ensure that you plan to be on the latest release and patch level of SAP ASE possible for your deadlines.

The SAP ASE patches are released very frequently (I’ve seen 3 patches within 6 months); which usually contain some major fix for a possible data corruption scenario.

3 Stay Current
Revisions to the SAP ASE software are performed rapidly. The software stack is small and no minor fixes (binaries only or single binary fixes) are performed, so plan to patch the database at least once every 3 to 6 months minimum. If you fail to stay current, then you could face issues staying supported with other compatible software areas (such as SAP Replication Server).

A lot of the SAP notes will be updated to recommend the latest revision (usually for some major issue).
It has been known for a revision to be delayed dramatically (by months) only to be superceded almost immediately by a later revision.

So called “hot fixes” have also been seen, whereby the current revision receives an additional increment in between the previous and the latest. Usually this is only a stop-gap to fix an issue on a previous revision because the latest revision changes that functionality in a more intrusive manner (with respect to the code lines).

4 Ensure Server Parameters/Config
The configuration parameters of the SAP ASE database is performed against one core SAP note.
The SAP note has a rather messy layout and can be complex to digest manually.
Still, this is a required task and changing the parameters to those recommended by SAP is a must, since the out-of-the-box configuration is never really optimal or potentially even unstable (again refer to the SAP notes).
To ease this task, the method of checking these parameters (providing you’re SAP Netweaver revision is high enough) is through the DBACOCKPIT transaction.
Whilst this allows you to easily configure and apply the settings, it should never negate a manual review of the parameter settings. Plus, you may just learn something 😉 .

5 Confirm Statements with Your SAP account manager
Sometimes, I have come across erroneous support statement within the SAP notes or the help.sap.com website help pages.
This is potentially due to the two revisions of the SAP ASE software prevalent. Sometimes the SAP ASE standalone community forget that SAP ASE can run underneath SAP Business Suite.

Therefore, if you see a SAP note stating you’re not supported if you do “X” or have “Y” installed, query it with your account manager. It may not be relevant to your use case.

6 Set Relevant DB Parameters
Sometimes the parameters listed within the single SAP note (for that specific SAP ASE release), will be specific to SAP BW, or SAP ERP.

Since it’s entirely possible for you to be using the BW component heavily, inside your ERP system, you may wish to apply the BW related parameters (the old OLAP vs OLTP).
Therefore, you should double check the SAP note containing the SAP ASE recommended parameters, and not just blindly apply it.

7 Rigorously Test
It’s fairly simple and requires little effort to patch an SAP ASE database.
For this reason, you should expend the time saved, by testing.
As an example, I experienced an issue whereby I patched the SAP ASE software to a later revision, only to discover during a SAP system copy task, that a minor bug in the later software revision prevented the copy process from working.

In the end, after nearly a week of working on the problem (I immediately raised it with SAP also), I found (not SAP) that there was a workaround to get it working.
I also went on to note that the issue was later fixed in a later SAP ASE revision.

Therefore I decreed that, as part of upgrade testing, some of the standard internal department processes would be tested prior to upgrading the core systems.

8 Always Check the Bug List
As a follow on to the previous statement, you should check the bug listing of the next revision with eagle eyes.
You may not be experiencing a specific issue, but it could save you from potential corruption or getting into an unfixable situation.

Currently the SAP ASE bugs are not listed in individual SAP notes, but generally in the Release Information Note for the next revision (once it is solidified).
In addition to the RIN, there is one core SAP note for each SAP ASE release (e.g. 15.7 or 16.0) in which the up and coming revisions are noted.
Check this regularly.

9 Configure Your Own Housekeeping
One small shortfall in the SAP ASE software, is housekeeping.
The log files for the database, jobserver and backupserver do not rotate until the SAP ASE instance is restarted.
You may wish to keep these files tidy and compressed with your own housekeeping scripts.

There are also a couple of recommendations for retaining certain files such as the last configuration file, the dump history file and an export of the sysdevices table, on a separate file system (a bit like the old SystemV export of the partition table), just in case of a restore scenario.

10 Performance Tune Backups / Restores
Out-of-the-box the performance of backups and restores is adequate.
However on a 1.3TB database I saw backups (within 1 stripe only) taking in excess of 4 hours (to a DataDomain appliance).

By simply spending 2 days to performance tune, by adjusting just one SAP ASE parameter, I was able to reduce this by 1.5 hours (~30% improvement).

This will also be a future blog article as it was such a simple but effective task. Don’t accept the out-of-the-box settings.

Make sure that you test the restore capability and also ensure that you allocate adequate disk space for emergency backups (dumps) to disk (if you’re planning to backup to a third-party tool).

Make sure you watch out for future ASE articles for additional lessons-learned scenarios.