Installing a Sybase ASE15 Server

The following points apply for a new installation or an upgrade to an existing server. With an upgrade just create the new filesystem side by side the existing installation and migrate over the logins, config settings, user databases etc. In an upgrade the ideal is if you have enough space to run the 2 servers in parallel but if not then at least create the bare bones Sybase server (system databases) and then drop a user database on the existing sybase server, along with devices, and recreate them for the new server.

It is important that you create environment variable for the new server which do not include any reference to the old server so you will need to hack the .cshrc and SYBASE.csh a little bit so that when you source the new .cshrc it loads the environment variables from scratch. Refer to the FPS Sybase server .cshrc file for examples. Also it is important that you give the new server a different port number from the old server.

1)      You first need to make sure that the Unix machine is ready and has all the required patches installed etc.

2)      The next step is to get the filesystem (5GB) and various raw devices created by Unix. You will need as an example at least the following raw devices in place before installing the Sybase server;

/dev/vg_kapaks/rlv_master  (Mbytes) 224

/dev/vg_kapaks/rlv_sybsystemdev  (Mbytes) 128

/dev/vg_kapaks/rlv_sysprocsdev  (Mbytes) 320

/dev/vg_kapaks/rlv_temp01  (Mbytes) 2016


If you are doing an upgrade and space is limited then you could make the tempdb device smaller and then create a larger one later on.


3)      Next you need to install the actual server and for this you take the cd/dvd/tarball from Sybase and place it into $SYBASE/software. $SYBASE being the directory where you have decided to install Sybase, which must be different form any existing SYBASE installation. Unzip and extraxt the files.


4)      After you have untarred it you first set the SYBASe variable to where you want to install the server, e.g setenv SYBASE /progs/kapaks/sybase_15 and then run the setup by first starting the X server on your pc and then typing setup. You can also run it without a gui by typing setup –console.

During the install you will be asked various questions including information about the license, just continue without license for now, we will put in the license later as we get a 30 day trial anyway. Choose Full installation, Enterprise edition, Server license and when asked say continue installation without license. Also answer no to configure email alerts, this can be done later if needed.

Once all the software has been unloaded answer NO to remember ASE plugin password then you will be asked if you want to build the servers, go through the various fields filling in the relevant information and click on build. I would suggest initially building just the dataserver, backupserver and XP server. Other servers can be built later if required. Choose to custom configure all the servers. If this is a migration from an existing server remember to use the same port numbers as before and also to name the servers the same.


Use the following answers,

–          Answer Mixed to application type.

–          2k to page size, VERY IMPORTANNT if this is a migration otherwise maybe choose 4k               for a completely new sybase server installation.

–          Answer NO to enable PCI question.

–          NO to optimise ASE configuration.

–          Other values are fairly obvious hopefully.


5)      Hopefully this all goes fine and you now have a working Sybase server. You need to check whether the Sybase server needs to be localised to the Norwegian language, it almost certainly has to be. If you are migrating a server then you can easily check this in the old servers log file by looking for the line “SQL Server’s default sort order is”, if it says nocase_iso_1_nor then you need to install it for the new server. If it does then the next step is now to localise it to the Norwegian language and this is done by the following steps, if not you can jump over this step:

–          First source the SYBASE.csh file by doing source ~/SYBASE.csh

–          Copy the $SYBASE/charsets/iso_1/nocase_iso_1_nor.srt file over from another existing server and place it in the same folder on the new server i.e into  $SYBASE/charsets/iso_1/ folder and make sure it is readable by all.

–          Start $SYBASE/ASE-15_0/bin/sqlloc (having first started exceeed on your pc)

–          Select your Sybase server and enter the sa password, and then change the sort order to “Dictionary order, case insensitive, modified for Norwegian” and if necessary the default character set to iso_1 and click OK a few times.

–          If the sqlloc application fails you can use sqllocres –r <resource file> (sqlloc.rs). The resource file can be found in $SYBASE/ASE-15_0/init/sample_resource_files. Just change the template resource file by putting in the Sybase server name, sa password and putting in sqlsrv.default_characterset: iso_1 and sqlsrv.sort_order: nocase_iso_1_nor


6)      The above step should be enough but some servers have an extra language module installed, for no apparently good reason. If you are migrating from an old server then you can check for this in the master..syslanguages table. If there is a row called Norwegian then you can install this on the new server by running:

sp_addlanguage norwegian, norsk,                                                                                                                         ‘January,February,March,April,May,June,July,August,September,October,

November, December’,

‘Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec’,

‘Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday’,

mdy,  7



7)      The next step is to extend the temporary database onto it’s own device 1GB is a good starting point, and you could also consider creating a few extra temporary databases. This is not necessary if you are installing Sybase ASE15.0.3 which asks for a special temporary device at install. You should at the least consider creating a special temporary database to be used by logins with the sa_role.


8)      You should probably put in a sa login password at this point for added security, you do this by logging into the server and typing sp_password NULL, <new password>. After this is done place the password into the $SYBASE/$SYBASE_ASE/install/$DSQUERY file.


9)      You now need to adjust various server parameters like memory, number of open objects etc, take these from an existing Sybase server and adjust as needed. The easiest way to do this is to look at the existing servers .cfg file and do something like a cat DHT_KAPAKS.cfg | grep -v DEFAULT to find the non default values. Ignore the sections for monitoring and security related, these will be set when installed later on. Also ignore the bit about buffer pools, you create this after the Sybase server is rebooted with the new parameters so do this now, just set the buffer pools to the same values on the server being migrated from. Also set the parameter number of open partitions to the sum of number of open objects and number of open indexes.


10)   At this point you can migrate over logins if you are doing a migration from an existing server. This will wary a bit but for example if you are upgrading from Sybase version 12 to 15 then you would do the following steps, for other ASE versions you might need to change the temp table a bit:


–Create the temporary syslogins table on the new server

sp_configure ‘allow updates to system tables’,1

go

USE tempdb

go

–drop table dbo.temp_syslogins

–go

CREATE TABLE dbo.temp_syslogins

(

suid        int      NOT NULL,

status      smallint      NOT NULL,

accdate     datetime      NOT NULL,

totcpu      int           NOT NULL,

totio       int           NOT NULL,

spacelimit  int           NOT NULL,

timelimit   int           NOT NULL,

resultlimit int           NOT NULL,

dbname      varchar(30)   NULL,

name        varchar(30)   NOT NULL,

password    varbinary(30) NULL,

language    varchar(30)   NULL,

pwdate      datetime      NULL,

audflags    int           NULL,

fullname    varchar(30)   NULL,

srvname     varchar(30)   NULL,

logincount  smallint      NULL,

procid           int                NULL

)

LOCK ALLPAGES

Go

n  You may need to tweak the column types depending on which version of Sybase you are importing from, check ther source sysloginroles table to double check.



— Now bcp in the logins which was bcp’d out on the old Sybase server

— bcp tempdb..temp_syslogins in tore_syslogins.out -Usa -P -SMICOS2 -n


— Alter the table to add the new columns

alter table tempdb..temp_syslogins

add lastlogindate datetime NULL

add crdate datetime NULL

add locksuid int NULL

add lockreason int NULL

add lockdate datetime NULL

go


— Delete the sa and probe logins from the the temp_syslogins table

use tempdb

go

delete from tempdb..temp_syslogins where name in (“sa”, “probe”)

go


— Delete existing logins which match by name

delete from tempdb..temp_syslogins

where name in (

select t.name from tempdb..temp_syslogins t, master..syslogins s

where t.name =s.name

)

go


— Increase the suid’s.

ONLY necessary if you are merging multiple sybase servers into one.

update tempdb..temp_syslogins

set suid = suid + 6150

go


— Now copy the syslogins over to the master..syslogins table

insert into master..syslogins

select * from tempdb..temp_syslogins

go



— Create the sysloginroles table

USE tempdb

go

CREATE TABLE dbo.temp_sysloginroles

(

suid   smallint NOT NULL,

srid   smallint NOT NULL,

status smallint NOT NULL

)

LOCK DATAROWS

WITH EXP_ROW_SIZE=1

ON system

Go

n  You may need to tweak the column types depending on which version of Sybase you are importing from, check ther source sysloginroles table to double check.



— BCP in the data

— bcp tempdb..temp_sysloginroles in c1p16_sysloginroles.out -Usa -P -SMICOS2 -n


— remove the roles for sa and probe logins

delete from tempdb..temp_sysloginroles

where suid <=2

go



— Alter the table to make it compatible with ASE15

alter table tempdb..temp_sysloginroles

modify suid int not NULL

modify srid int not NULL

go



— Increase the suid’s

ONLY necessary if you are merging multiple sybase servers into one.

update tempdb..temp_sysloginroles

set suid = suid + 6150

go


— Copy the roles into master.sysloginroles

insert into master..sysloginroles

select * from tempdb..temp_sysloginroles

go



— The next steps relate to synchronising the suids after you have loaded the old              –database into the new server .

–First on the original server check whether there are any aliases set up or whether        –there are any users who have a different name to their syslogin name with the             –following on the original Sybase database.

select * from sysalternates

go

select l.suid,u.suid,l.name, u.name from master..syslogins l, sysusers u

where l.suid = u.suid

and l.name != u.name

go



–The following will resync the suids in a user database with the suid’s in syslogins.

update sysusers

set u.suid = l.suid

from sysusers u, master..syslogins l

where l.name=u.name

11)   You now need to set up the environment variables in .profile and .cshrc and also create the servername file containing the sa password, if you haven’t already done so, in the $SYBASE/ASE-15_0/install/ directory e.g for a Sybase server called MICOS1 this file would be called MICOS1 and would only contain the sa password, this is needed for the scripts to work. You can copy the environment variables from an existing Sybase server installation and just modify the values to your needs.


12)   You should now take a look at the $SYBASE installation folder and make sure that the interfaces, .cshrc , SYBASE.sh and SYBASE.csh files are all world readable.



13)   If this is a brand new machine then the next step is to generate the required licenses (always choose Un-served License). If this is just one server on an unclustered server, for example a test server, then that is fairly easy and the license file can be generated, (from https://sybase.subscribenet.com/control/sybs/login?nextURL=%2Fcontrol%2Fsybs%2Findex.

If it is a clustered server then you need to create a multimode license file for each machine in the cluster and place it underneath the SYSAM folder. You create a multimode license file by answering “2” to the question of “Number of machines to license”. You then enter both the machines it can run on and generate and download the license file.

The last step, for clustered and non-clustered Sybase servers is to place the licenses file under $SYBASE/$SYBASE_SYSAM2/licenses and if you want update the value for LM_LICENSE_FILE in the .profile and/or .cshrc file to point to it although this should not be necessary.


14)   Next we need to install the various standards that we have at EDB, first you need to create the scripts directory (sc) etc, so just copy this from an existing server installation and place it in an appropriate place, best thing is to use a tar ball for this and just get rid of the unnecessary files afterwards like logs etc.


15)   Next we create the additional Sybase devices from the raw devices on Unix.


16)   The next step is to install several specific EDB stored procedures etc:

–          First create a new database called syb_drift with 20MB data and 5MB log

CREATE DATABASE syb_drift

ON data01=’20M’

LOG ON logg01=’5M’

go

USE master

go

EXEC sp_dboption ‘syb_drift’,’trunc log on chkpt’,true

go

–          Type:

cdhs

cd sc

inst

(choose option 1 to install sybdrift, enter the sa password but answer no to                        “Vil du                 installere crontab” and “Vil du teste konsistens-sjekk, backup og                              overvaaking?”).

–          Next you need to install the relevant sp_thresholdaction stored procedure and this depends on whether or not you are using SQL Backtrack to backup the databases. If you are using SQL Backtrack then choose the following stored proc:

USE sybsystemprocs

go

IF OBJECT_ID(‘dbo.sp_thresholdaction’) IS NOT NULL

BEGIN

DROP PROCEDURE dbo.sp_thresholdaction

IF OBJECT_ID(‘dbo.sp_thresholdaction’) IS NOT NULL

PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.sp_thresholdaction >>>’

ELSE

PRINT ‘<<< DROPPED PROCEDURE dbo.sp_thresholdaction >>>’

END

go



USE sybsystemprocs

go

IF OBJECT_ID(‘dbo.sp_thresholdaction_old’) IS NOT NULL

BEGIN

DROP PROCEDURE dbo.sp_thresholdaction_old

IF OBJECT_ID(‘dbo.sp_thresholdaction_old’) IS NOT NULL

PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.sp_thresholdaction_old >>>’

ELSE

PRINT ‘<<< DROPPED PROCEDURE dbo.sp_thresholdaction_old >>>’

END

go

create procedure sp_thresholdaction_old

@dbname         varchar(30),

@segmentname    varchar(30),

@space_left     int,

@status         int

as

declare @devname varchar(100),

@before_size int,

@after_size int,

@before_time datetime,

@after_time datetime,

@error int


if @segmentname != (select name from syssegments

where segment = 2)

begin

print “THRESHOLD WARNING: database ‘%1!’, segment ‘%2!’ at ‘%3!’ pages”,

@dbname, @segmentname, @space_left

end

go

EXEC sp_procxmode ‘dbo.sp_thresholdaction_old’, ‘unchained’

go

IF OBJECT_ID(‘dbo.sp_thresholdaction_old’) IS NOT NULL

PRINT ‘<<< CREATED PROCEDURE dbo.sp_thresholdaction_old >>>’

ELSE

PRINT ‘<<< FAILED CREATING PROCEDURE dbo.sp_thresholdaction_old >>>’

go





create procedure sp_thresholdaction

@dbname         varchar(30),

@segmentname    varchar(30),

@space_left     int,

@status         int

as

declare @devname varchar(100),

@before_size int,

@after_size int,

@before_time datetime,

@after_time datetime,

@error int,

@cmd1 varchar(5000),

@cmd2 varchar(5000)


set @cmd1 = ‘$DT_SBACKTRACK_HOME/bin/dtsbackup ${DTPHYSICAL}/’ + @dbname + ‘ -log_only | tee $BACKUPKAT/ch/backtrack_logg’


set @cmd2 = ‘$BACKUPKAT/sc/m_sback_log ‘ + @dbname


–if @segmentname != (select name from syssegments where segment = 2)

begin

print “THRESHOLD WARNING: database ‘%1!’, segment ‘%2!’ at ‘%3!’ pages”,

@dbname, @segmentname, @space_left

exec xp_cmdshell @cmd1

exec xp_cmdshell @cmd2

exec xp_cmdshell @cmd1

end

go

EXEC sp_procxmode ‘dbo.sp_thresholdaction’, ‘unchained’

go

IF OBJECT_ID(‘dbo.sp_thresholdaction’) IS NOT NULL

PRINT ‘<<< CREATED PROCEDURE dbo.sp_thresholdaction >>>’

ELSE

PRINT ‘<<< FAILED CREATING PROCEDURE dbo.sp_thresholdaction >>>’

go



If however you are not using SQL Backtrack install the following version of sp_thresholdaction:

USE sybsystemprocs

go

IF OBJECT_ID(‘dbo.sp_thresholdaction’) IS NOT NULL

BEGIN

DROP PROCEDURE dbo.sp_thresholdaction

IF OBJECT_ID(‘dbo.sp_thresholdaction’) IS NOT NULL

PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.sp_thresholdaction >>>’

ELSE

PRINT ‘<<< DROPPED PROCEDURE dbo.sp_thresholdaction >>>’

END

go



USE sybsystemprocs

go

IF OBJECT_ID(‘dbo.sp_thresholdaction_old’) IS NOT NULL

BEGIN

DROP PROCEDURE dbo.sp_thresholdaction_old

IF OBJECT_ID(‘dbo.sp_thresholdaction_old’) IS NOT NULL

PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.sp_thresholdaction_old >>>’

ELSE

PRINT ‘<<< DROPPED PROCEDURE dbo.sp_thresholdaction_old >>>’

END

go

create procedure sp_thresholdaction_old

@dbname         varchar(30),

@segmentname    varchar(30),

@space_left     int,

@status         int

as

declare @devname varchar(100),

@before_size int,

@after_size int,

@before_time datetime,

@after_time datetime,

@error int


if @segmentname != (select name from syssegments

where segment = 2)

begin

print “THRESHOLD WARNING: database ‘%1!’, segment ‘%2!’ at ‘%3!’ pages”,

@dbname, @segmentname, @space_left

end

go

EXEC sp_procxmode ‘dbo.sp_thresholdaction_old’, ‘unchained’

go

IF OBJECT_ID(‘dbo.sp_thresholdaction_old’) IS NOT NULL

PRINT ‘<<< CREATED PROCEDURE dbo.sp_thresholdaction_old >>>’

ELSE

PRINT ‘<<< FAILED CREATING PROCEDURE dbo.sp_thresholdaction_old >>>’

go





create procedure sp_thresholdaction

@dbname         varchar(30),

@segmentname    varchar(30),

@space_left     int,

@status         int

as

declare @devname varchar(100),

@before_size int,

@after_size int,

@before_time datetime,

@after_time datetime,

@error int,

@cmd1 varchar(255)


select @cmd1 = ‘$BACKUPKAT/sc/dump_t ‘ + @dbname


–if @segmentname != (select name from syssegments where segment = 2)

begin

print “THRESHOLD WARNING: database ‘%1!’, segment ‘%2!’ at ‘%3!’ pages”,

@dbname, @segmentname, @space_left

exec xp_cmdshell @cmd1

end

go

EXEC sp_procxmode ‘dbo.sp_thresholdaction’, ‘unchained’

go

IF OBJECT_ID(‘dbo.sp_thresholdaction’) IS NOT NULL

PRINT ‘<<< CREATED PROCEDURE dbo.sp_thresholdaction >>>’

ELSE

PRINT ‘<<< FAILED CREATING PROCEDURE dbo.sp_thresholdaction >>>’

go




17)   The next step is to configure the new mda montables and that is done as follows:


sp_configure “enable monitoring”,1

go

sp_configure “SQL batch capture”,1

go

sp_configure “max SQL text monitored”,100000

go

sp_configure “sql text pipe active”,1

go

sp_configure “sql text pipe max messages”,10000

go

sp_configure “object lockwait timing”,1

go

sp_configure “per object statistics active”,1

go

sp_configure “statement cache size”, 10000

go

sp_configure “enable stmt cache monitoring”,1

go

sp_configure “deadlock pipe max messages”, 10000

go

sp_configure “deadlock pipe active”, 1

go

sp_configure “errorlog pipe active”,1

go

sp_configure “errorlog pipe max messages”,10000

go

sp_configure “wait event timing”,1

go

sp_configure “statement statistics active”,1

go

sp_configure “process wait events”, 1

go

sp_configure “plan text pipe active”, 1

go

sp_configure “plan text pipe max messages”, 10000

go

sp_configure “statement pipe max messages”, 10000

go

sp_configure “statement pipe active”,1

go


18)   Install any extra stored procedures, these can be found from an existing Sybase server. E.g sp__mda_hot_tables, sp_mda_io etc.



19)   At this stage you can create (for load) and start loading the user databases, using sql backtrack remote load procedure, for example from existing sybase server run;

dtsrecover /home/solo1/datatools/sbackups.physical/dhp_SOLP/commissiondb

-server TORIGODB -database commissiondb –user sa –password <pass> -copyover

Remember to perform  the suid resync section above. If you haven’t done so previously  you need to first create all the required sybase data devices from the raw partitions which should already be on the Unix machine.


20)   Install dbccdb

–          Run sp_plan_dbccdb to find the recommended size of the dbccdb and create it with for example:


USE master

go

CREATE DATABASE dbccdb

ON data11=’1500M’

LOG ON log01=’500M’

go

USE master

go

EXEC sp_dboption ‘dbccdb’,’trunc log on chkpt’,true

go

USE dbccdb

go

CHECKPOINT

Go

–          Run the scripts/installdbccdb script in Unix using isql.

–          Run the following sql with the value being the max number for processes displayed by sp_plan_dbccdb:

sp_configure “number of worker processes”, 6

go

–          If you haven’t already done so earlier create a 150MB 16k memory pool in the cache used by dbccdb, usually just default data cache.

EXEC sp_poolconfig ‘default data cache’,’150M’,’16K’

go

EXEC sp_poolconfig ‘default data cache’,’16K’,’wash=30M’

Go

–          Create the workspaces from the maximum recommended values as follows:

Use dbccdb

Go

sp_dbcc_createws dbccdb, ‘default’, scan_dbccdb, scan , ‘750M’

go

sp_dbcc_createws dbccdb, ‘default’, text_dbccdb, text , ‘200M’

go

–          Configure dbccdb for each of the user databases as follows, just use the max values for all the db’s:

sp_dbcc_updateconfig tlfbank, ‘max worker processes’, ’12’

go

sp_dbcc_updateconfig tlfbank, ‘dbcc named cache’, ‘default data cache’,                                          ‘200M’

go

sp_dbcc_updateconfig tlfbank, ‘scan workspace’, scan_dbccdb

go

sp_dbcc_updateconfig tlfbank, ‘text workspace’, text_dbccdb

go


21)   The next thing to install is auditing and unique sa logins and the details for this can be found in the document Auditing and Unique Logins.


22)   It is a good idea to create a second tempdb to reduce the load on the tempdb and that is done as follows:


create temporary database tempdb2 on tempdb_dev2=’1024M’

log on tempdb_log=’524M’

go

sp_tempdb ‘add’, ‘tempdb2’, ‘default’

go

sp_tempdb ‘show’

go


23)   You will also need to install SQL Backtrack if that is required.


24)   When you come to load the actual databases at migration or upgrade time make sure that you run update index stats against all the user tables AND all the system tables except syslogs and sysgams otherwise SQL Backtrack will run very slowly.


25)   The final step is to set up all the cron jobs to run, here just look at what is currently configured on the existing or similar servers. In some cases (check with Unix sa) you need to create a copy of the crontab in the $HOME directory for clustering etc. You do this with a command similar to;

crontab -l > $HOME/crontab.sybgunda

where sybgunda is the username.

26) Another thing you may find is that SQL Backtrack stops working if you have changed the ip address of the machine, this might be because all the TSM (OBSI settings) have changed. To fix this you need to find out what the new settings are in the dsm.sys file and then probably create a new adsm dump pool.