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 you migh thave, 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 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.
