Sybase Installation Failed

Almost at the end of the installation, installation fails with error and aborts the installation of the Sybase. The situation repeats and ends always in the same way.What can cause this error?

The installation of Sybase is indeed tricky. Before I succeeded with it, I have failed 1000 times. The successful one installation was the 1001st trial :)

Sybase listener is not starting with the new settings in interfaces file

One of the final step in Sybase installation can be change in the port that the server binds to, or adding a specific network interface, where sybase should listen on. This can be done using the sp_listener procedure and change in the interfaces file. Here,  there is an example of interfaces file and the failed command sp_listener. We want to allow Sybase to listen on the external interface 192.168.10.20:

SAP01
        master tcp ether SAP01 5000
        master tcp ether 192.168.10.20 5000
        query tcp ether SAP01 5000

SAP01_BS
        master tcp ether SAP01 5001
        query tcp ether SAP01 5001

SAP01_XP
        master tcp ether SAP01 5002
        query tcp ether SAP01 5002

SAP01_JSAGENT
        master tcp ether SAP01 4900
        query tcp ether SAP01 4900

and the port change from within of Sybase:

[root@SAP01 ~]# su - sap

-bash-4.3$ isql -Usa -SSAP01 -Ppassword

3>
4> sp_listener 'status'
5> go
 proto  host  port  status
 -----------------------------------------------------
 tcp   SAP01  5000  active

DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)

1> sp_listener 'start','192.168.10.20:5000'
2> go
Msg 15936, Level 16, State 1:
Server 'SAP01', Procedure 'sp_listener', Line 107:
An error was encountered when starting a listener with protocol tcp, host
192.168.10.20, port 5000.
Msg 15038, Level 16, State 1:
Server 'SAP01', Procedure 'sp_listener', Line 107:
The specified port 5000 is invalid.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 1)"

Well, one line in the configuration looks sometimes differently.  In my case in the interfaces file there was no entry for:

How to install "Interactive SQL"

We sometimes confuse two different pieces of software:

• Interactive SQL which supports Sybase
• SQL Central which supports SQL Anywhere

To connect to SAP ASE / Sybase  you need to have "Interactive SQL", not SQL Central.

There is no separate download of Interactive SQL, or at least I'm not aware of such a package. What you need to have is the full installation of the package, however you don't need to install the entire Sybase suit on your windows machine. During the installation only the client tools should be selected.

To download SAP Adaptive Server Enterprise in Evaluation or Developer edition you need to register on SAP website and ask for the download link. This can be done using:

https://www.sap.com/cmp/syb/crm-xu15-int-asewindm/index.html?url_id=text-us-sapcom-ase-trial-software

As internet is not static, with time this link may stop working and you will need to look for the downloads using a web search engine. This is ... politics of SAP.  See also:

https://archive.sap.com/discussions/thread/3565945

This is inconvenient, specially when you are playing with Linux/Unix installation. Usually one of the final steps is to configure client, so that it can work with the server. When you install server you usually download only the Linux/Unix Sybase install files. So in such case you should download the Windows install files and from it I installed the Windows Sybase client.

Those files should be available in the SDK FOR SAP ASE 16.0

Sybase 16/ SAP ASE Installation and Configuration online course

While building the blog something nice has been created. This is an online course dedicated to installation and initial configuration of Sybase 16 / SAP ASE.

Using this link you can have it in an 80% lower price:
https://www.udemy.com/sap-ase-16-sybase/?couponCode=SAPDB10

Below a couple of words about the course and some example movies. Have fun:

Sybase 16 / SAP ASE Installation and Configuration

• Preparing machine for Sybase installation
• Linux installation
• System requirements for Sybase installation
• Sybase 16 installation
• Starting and stopping Sybase
• Post installation configuration:configuring Sybase for automatic start, configuring network interface
• Configuration files, scriptes, binaries, errorlogs
• Configuring Sybase

This course is designed for  administrators beginning their adventure with Sybase database. We assume, you don’t know Sybase and you didn’t work too much with Linux. It is good if you have some experience with other database systems, however even without that knowledge you should still be able to understand the course.

 

Sybase database is known as well as  SAP Adaptive Server Enterprise. It is used in very specialized implementations, where stability and performance counts the most.

Course begins with some practical manuals allowing to build your own test environment. The next phase is Linux installation and it’s initial configuration, so you should be able to install Sybase in next steps. Installation finishes with the test procedure allowing to verify whether the server is correctly installed.

In the following lessons it will be presented what configuration files Sybase uses. You will also develop your own script allowing to start and stop the SAP ASE server.  You will learn how to configure the operating system to start and stop the database automatically when server boots.
In next lessons you will learn about configuration files like: interfaces file, network interface configuration. During this process you will see how to solve some problems that may appear during installation.  You will get familiar with the errorlog and sp_configure procedure.

This course is available only in English. Each lesson ends with a short quiz and a lab.

Sybase Course content:

•  How to prepare virtual machine for the course
• How to install Linux on it
• How to configure Linux for SAP ASE installation
• How to install SAP ASE
• How to validate the installation
• How the start-up procedure works
• How to start/stop ASE, how to configure it to automatic start/stop
• What environmental variables are used by ASE
• Which files are the most important, what kind of information they contain and how and when it is used
• How to configure ASE to listen on external network interfaces
• How to troubleshoot some common problems with installation or configuration
• How to install Interactive SQL on Windows
• How to work with sp_configure and how to work with configuration files

Example video

 

 

 

 

  

Courses can be run on iPhone, iPad and Android as well!

Activate your course in the browser. Next install a dedicated Udemy application on your mobile device. Go to „My courses” and learn whenever and whereever! If the Internet connection is a problem for you, the course can be downloaded for offline watching (only in mobile application)

Granting access to database (user or guest)

It is not enough to create a login to have access to the database. Each login requires additionally a user in the database where the login should have access to.  You can compare it to entering a building and an office in the building. Login allows only to enter the building and some public accessible places (in Sybase those "public places" are master and tempdb databases). To enter an office (user database) you need additionally to have user account.

So to create the user in the database MyDb for a login named myAppLogin you just issue command like this:

USE MyDb
sp_adduser myAppLogin

Starting from now the login myAppLogin can start using the database. The user can be removed using the following code:

USE MyDb
sp_dropuser myAppLogin

What is very important - you cannot remove a user when it is "in use", so there is an active session in the database using this user, however when the session was not found, the user will be removed.

Creating new login. Locking and unlocking the login

To gain access to the Sybase, you need to have a valid login. To create a login for your application type:

sp_addlogin myAppLogin, 'Passw0rd', MyDb, null, 'Application ABC', null, 8, 3

The parameters of the commands are:

• name of the login
• the password
• the name of a default database (warning: if you create a login with the default database, the user in the database is not created automatically. This step needs to be done manually)
• default language
• the description of the account
• time (in days) after that the password will expire. Leave null to have a never expiring password
• number of chars that the password should have. This is important for future password changes that will be done by the login
• number of failed logins, after which the account will be locked

After the login has been created you can test in syslogins whether the information about a new login should be available.

Sybase security fundamentals: logins vs users

Sybase and MS SQL are like an older and younger sisters, so if you know how the access in organized on one of the platforms, you know immediately how it in general works in another database system.
So, starting from beginning:

• To access the Sybase’s server databases you don’t need direct access to operating system where the server is installed, nor the files, where the database’s files are stored. The reason for this is, that Sybase server just listens on requests from clients and then gets data from the system, however this is done by the Sybase processes, not directly by the client.
• To connect to Sybase, you need to have a valid login. This login can be a locally created login, so just user name with password, but this login can be taken from Kerberos, LDAP or PAM. Usually dba’s decide to use the locally defined logins, but using external directory of users have huge advantages.

USE master
GO
SELECT * FROM syslogins

• The login alone is not enough. To access a specific database you need to have a user account as well. The user account is created per database and you can think about this like about an association of a login name with a database. If the login has such association, than is allowed to access the database. We say in such case that the login has a user in the database.

Making external backups: QUIESCE DATABASE

One of more advanced backup options in Sybase is, that Sybase supports making database dumps using external tools. Generally the problem should not be very complicated. Sybase's databases are at the end of it's implementation just files on disk! However....:

• When you copy database files all of them need to be from exactly the same time, and it will be very complicated/impossible to copy hundrets of Gigabytes in the same moment
• All the transactions should be hold in the time when the external backup is run

That is why in Sybase there is a command allowing to freeze all the transactions, so that the files can be easily copied using the external tool. Sybase has found a way this conditions can be met - just all the transactions should be frozen for the time of external copy process. In the same time data can be read, however if you decide to initiate the transaction, that this transaction will hang until the freeze will be finished. Unfortunately since that time also all read operations will hang waiting for the freeze end. 

Take a look at this example:

QUIESCE DATABASE Maintenance1 HOLD MyDb
GO
USE MyDb
GO
SELECT * FROM TEST
GO
INSERT TEST VALUES('A new value')

Here administrator plans to make a copy of MyDb files. So he initiates quiesceing of that database. Such operation needs to have a name, that is here Maintenance1. After this operation INSERT will stop working. The first SELECT will be executed with no additional delay, but the INSERT will hang.

In the same time in a second session similar activities can be initiated:

USE MyDb
GO
SELECT * FROM TEST
GO

In this example, the SELECT will hang as well!. That is caused by a still not executed insert from the first session:

However, when the maintenance will be finished with a command like:

QUIESCE DATABASE Maintenance1 RELEASE
GO

All the other frozen INSERTs or SELECTs will be executed in the correct order:

Such copied files can be later taken to another server, put into correct location, and when data_server will be started with the -q option, than all the quiesced databases will be treated as "in recovery" allowing to:

• restore them
• or
• leave as "in restore" and with time apply additional log dumps, making the second server up-to-date.

Sybase cumulative/incremental dump and load

There is one option named 'allow incremental dumps', by default not set, that would allow to perform incremental backups of a database.

Incremental backup contains only datapages that were modified since the last full database backup. This allows to build more compact recovery scenarios. For a huge database, where users usually don't perform huge updates, it may be easier to perform full backup once a week and an incremental backups everyday.. If required the transaction dumps can be done as usually.

Let's examine an example of incremental backup. In the code below: we create the devices and database, change option allowing incremental dumps for the database and perform one full backup, two incremental and finally one log dump:

USE master
GO

DISK INIT NAME ='myDevice_A', 
          PHYSNAME = '/opt/sap/databases/myDevice_A.dat', 
          SIZE=10240
GO

DISK INIT NAME ='myDevice_B', 
          PHYSNAME = '/opt/sap/databases/myDevice_B.dat', 
          SIZE=2560, 
          SKIP_ALLOC = true
GO

CREATE DATABASE myDB
ON myDevice_A = '20M'
LOG ON myDevice_B = '5M'
GO

sp_dboption 'myDB','allow incremental dumps', 'true'
GO

USE myDB
GO

CREATE TABLE T1(id INT)
GO

INSERT T1 VALUES (1)
GO

DUMP DATABASE myDB TO '/home/dumps/01_myDB_full.bak' 
GO

INSERT T1 VALUES(2)
GO

DUMP DATABASE myDB CUMULATIVE TO '/home/dumps/02_myDB_cumulative.bak'
GO

INSERT T1 VALUES(3)
GO

DUMP DATABASE myDB CUMULATIVE TO '/home/dumps/03_myDB_cumulative.bak'
GO

INSERT T1 VALUES(4)
GO

DUMP TRAN myDB TO '/home/dumps/04_myDB_tran.bak'
GO

USE master
GO

Now we will try different restore scenarios. The first just restores the full backup. No surprise, only one record will be visible in the restored table.

Log backup after catastrophic failure

The theory says, that a database admin should always have up to date backups... unfortunately this is not always true. It is possible that you have full database backup, you perform transaction backups every 3 hours, you even have the backup and recovery plan, but... Sybase due to some hardware failure stopped working and you cannot start it and the business waits and waits...

There is a utility called sybdumptran that allows to make a transaction log backup despite the non working sybase server.

Take a look at the following SQL code:

DISK INIT NAME ='MyDbDataDev', 
     PHYSNAME = '/opt/sap/databases/MyDbDataDev.dat', 
     SIZE=10240
GO

DISK INIT NAME ='MyDbLogDev', 
     PHYSNAME = '/opt/sap/databases/MyDbLogDev.dat', 
     SIZE=2560
GO

CREATE DATABASE MyDb
ON MyDbDataDev = '20M'
LOG ON MyDbLogDev = '5M'
GO

DUMP DATABASE MyDb TO '/home/dumps/MyDb_full.dmp' WITH INIT
GO

USE MyDb
GO

CREATE TABLE TEST(info char(30))
GO

INSERT INTO TEST VALUES('Before the 1st log dump')
GO

DUMP TRAN MyDb TO '/home/dumps/MyDb_log_1.dmp'
GO

INSERT INTO TEST VALUES('Never dumped')
GO

SHUTDOWN

As you see a new database on new devices has been created. Next we took a full backup, next user inserted one record, next the transaction low was dumped, next the user continued to insert the next record and then something went wrong and server stopped! Now you will see how to take a log dump despite stopped server: