Even though this is for ASE12.5.3 in a specific company the methodology can be applied to any dataserver upgrade.
Prerequisites from Unix
– You need to make sure you have sufficient space under /opt/sybase, need about 1.5GB to be on safe side.
– Need to create a volume to put the devices on e.g. /sybdevices/asyb01
Initial build
1) create a new directory underneath /opt/sybase in which to put the binaries, e.g /opt/sybase/asyb01ds
2) copy the tar ball ASE1253.tar from mjolner:/tore into this newly created directory, and untar it.
3) vi the SYBASE.sh, SYBASE.csh and SYBASE.env files and change the servername to the server you are creating with the following command;
e.g. %s/asyb01ds/dsyb01ds/g
4) Source the SYBASE.sh file with a “. SYBASE.sh” command and cd to $SYBASE/$SYBASE_ASE/bin
5) Do an export DISPLAY to the PC you are working from.
6) Type srvbuild to start building the new server. Create a dataserver, backupserver, monitor server, historical server, XP server and Job scheduler.
7) Place the devices in the directory given e.g. /sybdevices/asyb01 and make the master device 100MB in size and the master database 50MB. Make the Sybsystemprocs device and database 200MB
e.g.
/sybdevices/a1syb/master.dat
and
/sybdevices/a1syb/sybsystemprocs.dat
8) Assign the ports as per the listing in the Port assignments section of this document
Port assignments: With the hostnames being that of the Unix machine where the server is installed.
9) Do not worry about the error log path etc, this will all be configured later, just accept the defaults.
10) Hit start and the Sybase server should create the various components, keep an eye out for any errors in the installation log. Whne finished decline localizing the server and exit the srvbuild application.
Customisation for ******
These are the necessary steps to install the new ufjisyb scripts structure for a new Sybase server;
All the files and directories that you need are contained in the tarball ufji_maint_scripts.tar in merak:/opt/sybase/ufjisyb so for a new server do the following;
1) Create the directory /opt/sybase/ufjisyb/<servername> and untar the file into that directory.
2) Rename the server to the required one and also rename the server directories in the log directory.
3) In terms of modifying the scripts in /opt/sybase/ufjisyb/<servername>/scripts directory not much needs to be done;
Runsql
– change the value of DSQUERY to the Sybase server name
runsql_script
– change the value of DSQUERY to the Sybase server name
RUN_ds.sh
– You need to change the value of the MASTER variable which point to the master device
4) You need to alter the names on the various maintenance scripts e.g. capella.dbcc to CIDS_DEV_SQL1.dbcc
5) You need to copy the existing config file $SYBASE/$DSQUERY.cfg into the new /opt/sybase/ufjisyb/<servername>/config directory. Do this before you begin using the start and stop scripts i.e after the binaries are in place
6) In the /opt/sybase/ufjisyb/<servername>/logs directory change the servername directories to the new ones e.g. /opt/sybase/ufjisyb/asyb01ds/logs/asyb01ds
7) Copy over the .common_settings file from /opt/sybase on an existing installation, if it is not already there.
8) In addition it is necessary to create new goto files in the /opt/sybase directory in the format, goto_<servername> e.g goto_CIDS_DEV_SQL1
This file needs to be edited in the same way as goto_CIDS_DEV_SQL1 on merak is at the moment.
This involves changing the value for DSQUERY, SYBASE and possibly the DBA_PROJ and DBA_PROJECT if necessary and any of the other SYBASE variables if required, say if it’s a new Sybase version. You can check the Sybase variables required against the SYBASE.sh file in the $SYBASE directory
9) You need to move the entries in $SYBASE/interfaces file into /opt/sybase/interfaces on caph and then run the distribute_interfaces.sh script on caph to copy these new entries to all the Sybase servers. You then create a soft link from $SYBASE/interfaces on the machine you are creating the new Syabse server to point to /opt/sybase/interfaces.
In addition you will need to arrange for these entries to be added to the sql.ini file in w:\sybase\ini\sql.ini. Ask a member of desktop support to do this.
10) You also need to create the matching Login, Tables, Stored procs and Rules in the Sybase server
This is done by running Maint_scripts_create_tabs_procs_rules_and_login.sql found in merak:/opt/Sybase/ufjisyb
e.g from merak run
isql –Usa –P –Sasyb01ds
–i/opt/sybase/ufjisyb/Maint_scripts_create_tabs_procs_rules_and_login.sql
11) You can now try stopping and starting the Sybase server using the srvstop.sh and srvstart.sh scripts.
12) Create the following directory for dumping into;
/sybdumps/<srvname>
13) Once the Sybase server is up and running the logins can be migrated from the existing Sybase server and this is done as follows;
–a) From the old Sybase server run;
CREATE TABLE tempdb..t_syslogins
(
suid smallint 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(50) NOT NULL,
name varchar(50) 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
)
go
insert into tempdb..t_syslogins
select * from master..syslogins
where name not in (“sa”,”sybufji”,”probe”)
go
select * into tempdb..t_syssrvroles from master..syssrvroles
where srid > 25
go
select r.* into tempdb..t_sysloginroles from master..sysloginroles r, master..syslogins l
where l.suid=r.suid
and l.name not in (“sa”,”sybufji”,”probe”)
go
–b) Create the following tables in tempdb on the destination dataserver;
CREATE TABLE tempdb..t_sysloginroles
(
suid smallint NOT NULL,
srid smallint NOT NULL,
status smallint NOT NULL
)
go
CREATE TABLE tempdb..t_syssrvroles
(
srid smallint NOT NULL,
name varchar(30) NOT NULL,
password varbinary(30) NULL,
pwdate datetime NULL,
status smallint NULL,
logincount smallint NULL
)
go
CREATE TABLE tempdb..t_syslogins
(
suid smallint 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(50) NOT NULL,
name varchar(50) 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
)
go
–c) bcp the tables from old to new with the following commands;
bcp tempdb..t_syslogins out /sybdumps/t_syslogins.out –Usybufji -S<oldserver> -c -t \, -r \:
bcp tempdb..t_syssrvroles out /sybdumps/t_syssrvroles.out –Usybufji -S<oldserver>
-c -t \, -r \:
bcp tempdb..t_sysloginroles out /sybdumps/t_sysloginroles.out –Usybufji
-S<oldserver> -c -t \, -r \:
–then
bcp tempdb..t_syslogins in /sybdumps/t_syslogins.out –Usybufji -S<newserver> -c -t \, -r \:
bcp tempdb..t_syssrvroles in /sybdumps/t_syssrvroles.out –Usybufji -S<newserver>
-c -t \, -r \:
bcp tempdb..t_sysloginroles in /sybdumps/t_sysloginroles.out –Usybufji
-S<newserver> -c -t \, -r \:
–d) Next move the logins from the temp tables to the main tables with the following commands run from the destination server;
sp_configure “allow_updates”,1
go
alter table tempdb..t_syslogins add procid int NULL
go
update master..syslogins
set suid=3003
where suid=3
go
update master..sysloginroles
set suid=3003
where suid=3
go
insert into master..syslogins
select * from tempdb..t_syslogins
go
insert into master..syssrvroles
select * from tempdb..t_syssrvroles
go
insert into master..sysloginroles
select * from tempdb..t_sysloginroles
go
sp_configure “allow updates”,0
go
–e) You should now be able to log into the server using the old login and password
Configure Server parameters
This is a bit tricky as ASE12.5 handles memory differently from ASE12, so the best way to do this is to look at the old ASE 12. Basically everything that is not default can be copied over to the new config file except for the following;
1) Set “max memory” parameter to be the same as the old “total memory” value, also set the parameter “allocate max shared memory” to 1 so that all the memory is allocated at start up time.
2) procedure cache is now a fixed amount and not a percentage so take a 10% value of the max memory to start off with.
3) It might be an idea to use cache partitioning or even named caches. For Production tempdb can be bound to its own tempdb cache thus isolating it from the standard default data cache.e.g.
a) EXEC sp_cacheconfig ‘tempdb_cache’,’1000.000M’,mixed,strict
go
EXEC sp_poolconfig ‘tempdb_cache’,’1000M’,’2K’
go
EXEC sp_poolconfig ‘tempdb_cache’,’200M’,’16K’
go
EXEC sp_poolconfig ‘tempdb_cache’,’2K’,’wash=60M’
go
b) sp_bindcache tempdb_cache,tempdb
c) sp_cacheconfig tempdb_cache,”cache_partition=4″
4) Set the following parameters to be the same as the original;
number of open database, number of open indexes, max network packet size, number of devices, number of worker processes, max parallel degree, max scan parallel degree, additional network memory, max online engines, identity burning set factor, number of user connections, stack size, number of locks, xp_cmdshell context, cis bulk insert batch size, max cis remote connections, cis packet size, cis rpc handling
5) Stop and start the Sybase server to initialise all these settings.
Create user databases
1) First prevent the master device from being used in create database commands with the following sql command;
EXEC sp_diskdefault ‘master’,defaultoff
2) The first database to alter is tempdb, so create a 1GB data device and 1GB log device and alter tempdb onto these. You will need to remove the logsegment form the data device afterwards.
3) Create the other devices based on what exists in the existing Sybase server, suggested naming convention for the device files is;
<servername>_data/log<nr>
e.g asyb01ds_data1
4) Once the devices have been created you can create the database using the for load option.
5) Dump the database from the old server and load into the new
6) Apply the various database options and database owners from the old server.
New ASE12.5.3 features
1) Compressed dumps will be introduced. the results from tests are very impressive;
Basically an uncompressed dump of bildb takes 1 min 30s
A compressed dump which compresses smaller than using the compress command takes 1 min 40 s (Sybase compression factor of 2)
To compress the uncompressed dump with compress takes 2 min 10s
To compress with gzip takes 4 minutes
To dump a compressed dump to smaller than gzip takes a combined time of 4 min 05s. Sybase compression factor of 6
Dump plus compress 3 min 40s
Dump plus gzip 5min 25s
Compressed dump 4 min 05s
Therefor for the nightly dumps there is a choice between speed and size. If you don’t care about size then compression ratio of 2 is the best but if you want to keep the current compressed dump size then ratio 6 is the best but it does mean that the time to dump all the databases increases.
The time to load both an uncompresse and compressed dump is exactly the same
I would advise using a compression ratio of 2 and granting Sybase a bit more space in the /sybdumps, approx 17% more required, that way the dumps will only take a bit longer whereas restoring from dumps will bemuch quicker than before.
The syntax to dump a file is e.g.
dump database bildb to “compress::2::/sybdumps/bildb_uncompressed.dmp”
To load a compressed database it is e.g.
load database bildb from “compress::/sybdumps/bildb_uncompressed.dmp”
Sybmigrate utility
This utility allows you to migrate from a 2K page size for the server to 4K, 8K or even 16K.
It can only work between 2 ASE12.5 servers though so I will keep a 2K ASE12.5.3 Sybase server available for this process.
Steps to perform the migration;
1) Load the database into the ASE12.5.3 2K dataserver, currently asyb01ds on Unix machine stella. Also create a corresponding blank database on the 4k (target) server with the same name as the source database. You also need to create the same users, DBO etc in the target database as in the source database with corresponding logins.
It is assumed that the logins and roles have already been migrated from the source dataserver to the destination server as described earlier.
Also set the target database to have select * into option switched on.
2) On the target dataserver ASE12.5.3 2k you need to set up target dataserver as a remote server as follows in isql and also add it to the interfaces file;
sp_addserver <source dataserver>
go
also
sp_addremotelogin <source dataserver>
go
3) On the Source dataserver make sure the target dataserver is in the interfaces file and;
sp_addserver <target dataserver>
go
4) On both servers type
sp_configure “allow remote access”,1
go
5) If you get any problems try dropping and recreating the servers,remotelogin a few times. Also make sure the following entry is in the goto files
NN_SYB_PACKETSIZE=512
Test that it all works by typing exec <target server>…sp_who from the source dataserver
6) Make sure that the following parameters are the same on both servers;
cis bulk insert array size
cis bulk insert batch size
cis connect timeout
cis cursor rows
cis packet size
cis rpc handling
7) from the command line on the source dataserver type sybmigrate
a) The target database is the 4k server, e,g, mjolner_4k_1253ds
b) The source is the 2k dataserver e.g. asyb01ds
c) The login HAS TO BE “sa” for it to work
8) First of all you need to click on “setup source database for migration”,
a) Specify the database you want to migrate e.g. murex2000db
b) Click Add path then next
c) On the next screen select a logical device with enough space to house the repository database, and click setup
d) The setup will now start, so check the output log as it will tell you if there are any errors or config parameters which need to be changed to allow the migration to work.
9) When the setup is finished and the log shows the setup as complete click on OK to take you back to the main menu.
10) Select “Migrate database objects and data” and click next
a) On the next screen click on migrate to start the migrate process from the source database to the target.
b) Again check the log for any errors, if there are any carry out the actions suggested and rerun the migrate.