From scratch

1.               run sp_plan_dbccdb from the master database to get an estimate of the sizes needed

2.               create a dedicated data and log device for the dbccdb database and create the database onto these, or just create it on existing devices

3.               run isql –U –P –S –i$SYBASE/$SYBASE_ASE/scripts/installdbccdb to install all the necessary tables and stored procs in the the dbccdb database

4.               Add segments into the dbccdb database;

use dbccdb

go

sp_addsegment scanseg, dbccdb, dbccdb_data1

go

sp_addsegment textseg, dbccdb, dbccdb_data1

Where dbccdb_data1 is the data device dbccdb was created on

5.         Add a 16K pool if it doesn’t already exist;

               sp_poolconfig "default data cache", "150M", "16K"

go

5.               Create workspaces;

     use dbccdb

go

sp_dbcc_createws dbccdb, scanseg, scan_pubs2, “scan”, “10M”

go

sp_dbcc_createws dbccdb, textseg, text_pubs2, “text”, “10M”

go

The values in MB are found from the highest values of the output from sp_plan_dbccdb earlier

6.   Configure databases into dbccdb; derive the required values from the sp_plan_dbccdb earlier

use dbccdb

go

sp_dbcc_updateconfig pubs2,”max worker processes”, “4”

go

sp_dbcc_updateconfig pubs2, “dbcc named cache”, “default data cache”, “10K”

go

sp_dbcc_updateconfig pubs2, “scan workspace”, scan_pubs2

go

sp_dbcc_updateconfig pubs2, “text workspace”, text_pubs2

go

sp_dbcc_updateconfig pubs2, “OAM count threshold”, “5”

go

sp_dbcc_updateconfig pubs2, “IO error abort”, “3”

go

sp_dbcc_updateconfig pubs2,”linkage error abort”, “8”

go

You will need to increase the number of worker processes parameter to 100 and bounce the Sybase server, after it comes up set max parallel degree to 10 and max scan parallel degree to 3

In case all the device files vanish and you have to start from scratch these are the steps to take;

1)         Run buildmaster –d <path to master device> -s <size in pages> (or dataserver in ASE12.5) to create a new master device

2)         Start the dataserver in single user mode

3)         log in using sa with null password and do the following

a)         update sysservers set srvnetname=”<Old backup server name>”

b)         alter database master on master =46MB (or whatever the master db size was before)

c)         shutdown and restart the server  and backupserver

d)         load the master database from a recent dump

4)          The server will shut down after the load, start it up again in single user mode, this time though log in with your ususal sa account and execute the following;

a)         In the sysusages table remove all but the first 2MB fragment for tempdb (dbid =2) and also change the segmap for this first fragment to 7

I)          update sysusages set segmap=7 where lstart=0 and dbid=2

go

II)         delete from sysusages wheredbid=2 and lstart >1000

go

b)         update sysdatabases set status=320 where name not in (“master”,”model”,”tempdb”,”sybsystemdb”)

c)         execute dbcc dbrepair (sybsystemprocs, dropdb)

d)         delete from sysdevices where name=”sysprocsdev”

e)         shut down the dataserver

5)        Edit the runserver file to start the dataserver up in normal mode and start it up again

6)        Log in normally and create a sysprocsdev device, then create the sybsystemprocs database on it (200MB) and load this database from a recent dump.

7)        Now you just need to extend tempdb and create the other databases.

Building new installation

This is quite straight forward, simply put the CD into the drive and run the installation script, select to create the demo database, it’s always quite usefull.

Starting the server

The following command can be used;

start_asiq @/tore/mjolner_iq/ASIQ-12_6/demo/asiqdemo.cfg

the contents of the .cfg file could be something like;

>>>>>>>>>>>>>>>>>>>>>>>>

# asiqdemo.cfg

# ————————————————————

# Default startup parameters for the ASIQ demo database

# ————————————————————

-n  mjolner_asiqdemo /tore/mjolner_iq/ASIQ-12_6/demo/asiqdemo.db

-x  tcpip{port=2638}

# The following parameters are also found in the configuration file

# $ASDIR/scripts/default.cfg.  Any parameters not specified below

# and not in the start up parameter list, will be added by start_asiq

# using default.cfg as a guide.

-c  48m

-gc 20

-gd all

-gl all

-gm 10

-gp 4096

-ti 4400

>>>>>>>>>>>>>>>>>>>>>>>

note that the –n parameter gives the name of the iq server followed by the path to the database file.

Connecting to database/server

from unix type ;

dbisql -nogui -host mjolner -port 2639 -jConnect -c “uid=DBA;pwd=SQL”

or if you want the gui version omit the –nogui option, you need to set the display first though.

From Windows use Interactive SQL or Sybase central

Useful commands

sp_iqstatus Display the current status of the IQ server/database incl used space etc.

sp_iqdbsize Displays the size of the current database

select * from sysiqfile Display information on the dbspaces

Creating a new database

The easiest place to do this from is using Interactive SQL logged into asiqdemo and then type the following;

create database ‘/tore/mjolner_iq/ASIQ-12_6/iq1/iq1.db’

IQ PATH ‘/tore/mjolner_iq/ASIQ-12_6/iq1/iq1.iq’

IQ SIZE 12000

message path ‘/tore/mjolner_iq/ASIQ-12_6/iq1/iq1.iqmsg’

temporary path ‘/tore/mjolner_iq/ASIQ-12_6/iq1/iq1.iqtmp’

temporary size 6000

page size 4096;

Importing data

To import a table from ASE into IQ use the following sequence;

1)         Extract the ddl for the table you want to transfer and create the table in the IQ database

2)         bcp out the data with a command like;

bcp murex2000db.MUREXDB.MPY_GLOB_DBF out MPY_GLOB_DBF.out -c -Usybufji –P<password> -SCIDS_DEV_SQL1 -t \|

3)         From a dbisql or interactive SQL session execute a command like;

load TABLE MPY_GLOB_DBF

(

TIMESTAMP    ‘|’,

M_IDENTITY   ‘|’,

M_VTYP       ‘|’,

M_VINT       ‘|’,

M_MLAB0      ‘|’,

M_SLAB0      ‘|’,

M_MLAB1      ‘|’,

M_SLAB1      ‘|’,

M_VDAT       ‘|’,

M_VREF       ‘|’,

M_VVAL       ‘|’,

M__INDEX_    ‘|’,

M__REPLICAT_ ‘\n’

)

FROM ‘/tore/MPY_GLOB_DBF.out’

ESCAPES OFF

QUOTES OFF;

Note that there may be some problems if the original table has timestamp or identity columns, but these can be worked around.

!! Ignore any errors occurring on object syslogs they are more than likely transient !!

The following error codes need the following actions;

100031

run dbcc tablealloc (table_name)

100035

run dbcc checktable (table_name, fix_spacebits)

Stored proc to hammer tempdb, useful for tests.

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

BEGIN

DROP PROCEDURE dbo.heater

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

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

ELSE

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

END

go

create procedure heater @limit int

as

begin

set nocount on

declare @error                    int,

@procname                 varchar(35),

@tc                       int,

@rc                       int,

@rs                       int,

/* Timing the process */

@elapsed_time1            datetime,

@elapsed_time2            datetime,

@snapdate                 datetime,

@upd_stats_diff           int,

@seconds                  numeric (10,4),

/* Counting rows. Declaring variables */

@total_deleted            int,

@total_inserted           int,

@total_updated            int,

@inf_initial              int,

@inf_final                int

select @elapsed_time1=getdate()

print “”

print “Starting time: %1!. “, @elapsed_time1

print “”

create table #heater (col1 int, col2 char(255), col3 char(255), col4 char(255))

declare @counter int

select @counter =1

while (@counter < @limit )

begin

insert into  #heater

values (@counter, “AAAAAAAAAAAAAAAAA”, “BBBBBBBBBBBBBBBBBBBBBBB”, “CCCCCCCCCCCCCCCCCCCCCCCCCC”)

select @counter = @counter + 1

if (@counter = @limit/2)

begin

select getdate()

exec sp_helpsegment “logsegment”

exec sp_helpsegment “default”

exec sp_helpsegment “system”

end

end

/* Timing the process */

select @elapsed_time2=getdate()

select @seconds=datediff(ss, @elapsed_time1, @elapsed_time2)

print “”

print “Ending time: %1!. Elapsed time: %2! seconds”, @elapsed_time2, @seconds

end

go

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

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

ELSE

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

go

EXEC sp_procxmode ‘dbo.heater’,’unchained’

go

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.

ASE:

Q) What are the main advantages and disadvantages of using identity columns?

A) The advantage is in the ordering of the table and creation of unique rows, disadvantage is identity gaps.

Q) How to bcp out a subset of rows from a large table?

A) Create a view with the subset and bcp out via it.

Q) What does set forceplan on do?

A) Forces the join order for queries to be the order in which the tables appear in the from clause.

Q) How do you find out which process has filled up tempdb?

A) select * from master..syslogshold but will need to create an additional temporary database and bind it to your login first.

Q) What are the options for doing an upgrade to a Sybase server?

A) Can do a Sybase upgrade but much better to create a new server and migrate over the databases/logins etc.

Q) What does the term covered query mean?

A) It is if the columns specified in a query (columns to be returned plus columns in where clauses) are completely satisfied by an index.

Q) What is the difference between an MRU and an LRU caching strategy?

A) It stands for most recently used and least recently used and defines which records are swapped out first, the default is LRU i.e. the pages least recently used get flushed out when more space is needed. MRU is referred to as fetch and discard and is ideal for queries where a page is only needed once by the query for example table scans on heap tables.

Q) Why would you want to use an MRU index?

A) MRU is ideal for queries where a page is only needed once by the query for example table scans on heap tables or where you have more than one table in a join query.

Q) In Adaptive Server 11.9.2 or earlier explain the difference between a character and a block device and when they are typically used or could be used

A) A character or un-cooked/raw device writes directly to the disk and guarantees transactions are written to the disk immediately. A block or cooked device does not write to the disk immediately, which can cause the database to lose its integrity should there be a media failure. Character devices are normally used for live databases. Using block devices in Tempdb can in some circumstances improve performance for applications that create a lot of temporary tables.

Q) Briefly explain what the difference between a transient and hard 605 error.

A) A transient error:

Operating system notifies SQL Server that an I/O operation is complete when it is not. Any time buffer information about a page is different from the disk information about the page (and the disk has the correct information).  ~ Normally a dbcc checkdb and dbcc checkalloc should produce no more 605 errors.

A hard error:

Normally hardware related can be caused by in-correctly formatted disks (overlapping partitions) or disk media failure.

Q) Explain the difference between slow and fast BCP and how to apply this.

A) A slow BCP leaves indexes in place and is the default.     Fast BCP involves dropping the indexes/triggers for the table in question. In fast BCP the indexes are recreated after the data has been loaded in. The BCP –A flag can be used to increase the default Network packet size. This can increase the speed of a BCP, however the under-lying UNIX o/s needs to be changed to support the larger packet size.  In addition you would need to allocate additional network memory in the Sybase dataserver which is used by the bigger network packets.

Replication server:

Q) What is the Save interval used for pertaining to the Stable Queue.

A) It determines how long the stable queue will wait until it deletes information already applied to the replicate db. It is specified using rs_configure save_interval(in minutes). It can be used for redundancy in the event of a replication system failure. The save interval lets the sending Replication Server re-create all messages saved during the save interval.

Q) What two Replication Server parameters should always be increased from the default values.

A) The 2 most important are memory_limit which sets the total memory available to the Replication server and sqt_max_cache_size which sets the limit of cache which is available to the stable queue

Q) When a DSI Thread suspends due to a transactional error, what are the two methods used to force resumption of the queue.

A) Resume connection to dataserver.database skip transaction

set autocorrection on for replication_definition with replicate at data_server.database

Q) What Replication Server command shows all the configuration parameters pertaining to a Replication Server.

A) rs_configure – run in RSSD for Replication Server.

Q) What is the ID Server in a replication environment?

A) It is the master replication server which holds information on all the other replication servers.

Q) What happens if the ID Server goes down in a multi repserver environment.

A) It runs ok until you try and update any rep defs etc or restart any of the other replication servers.

Q) How would you find what command caused a dsi to go into suspend?

A) Check the rep server error log.

Q) If the error log indicates that dsi is down due to duplicate key error what do you do?

A) First do a sysadmin log_first_tran to output the bad transaction, it can then be viewed with rs_helpexception. If you decided it can be skipped then do a resume connection with skip_transaction.

Q) What is autocorrection?

A) It is an option set on a rep def which converts updates and inserts into a delete followed by an insert, this is useful in dev environments where you tend to sometimes get a lot of duplicate transactions.

Q) How do you determine the time of the last transaction applied to the replicate database?

A) rs_lastcommit

Q) What is the easiest way to completely replicate a database from London to Singapore and New York?

A) Use MSA, database replication definitions.

Q) When was MSA (database replication definition) introduced?

A) Version 12.6.

Q) Does MSA replication work over a WAN?

A) Yes I think so, I can’t see why not, warm standby works over WAN.

MSSQL:

Q) If someone has a problem with a spid, how would you investigate it?

A) Use the activity monitor, and perhaps dbcc inputbuffer, dbcc opentran to show longest running tran.

Q) How would you put a table completely into memory?

A) You can’t

Q) How do you change the default location of a database?

A) You can specify the default location for data and log files in the server properties, database settings.

Q) What is the difference between dbcc reindex and dbcc index (defrag)?

A) dbcc reindex is an offline procedure, dbcc index (defrag) is performed online.

Q) How would you check the statistics for a table?

A) Run dbcc show_statistics against it.

Q) What things would you configure on the analysis server, high level?

A) Increase minimum allocated memory, increase read-ahead buffer size. Increase Process buffer size, enable Proactive caching, maybe increase maximum number of threads.

Q) There are a few option which MSSQL server lets you set which shouldn’t be set on a production server?

A) You should NOT enable Auto Shrink or Auto Close and a production database should be set to full or bulk logged recovery model NOT simple.

Q) What services can be clustered in SQL Server 2008?

A) SQL Server, SQL Server agent, analysis services, reporting services.

Q) How can you set the memory of the server to not go above 3GB?

A) In server properties set both the minimum and maximum server memory to 3GB.

Q) How would you move the tempdb?

A) Execute the following 2 commands: alter database tempdb modify file (name=tempdev|templog, filename=’c:\Sqldata\tempdb.mdf|templog.ldf’), then restart the server and the tempdb will be created on the new devices in the new location, you can then remove the old devices.

Q) In SQL Server 2005 what is port 1434 used for?

A) The SQL Server browser uses, by default, port 1434 to listen for incoming connections.

Q) Where is logging information kept within sql server?

A) You can access the logs from the SQL Server management Studio, both SQL Server, System and SQL Server Agent logs can be viewed but the actual physical logs are on the file system.

Q) How do you start a SQL Server form the command prompt in single user mode?

A) To start the default instance it’s sqlserver.exe –m for a named server it is sqlserver.exe –m –s <servername>.

Q) What are the differences between clustered and non clustered indexes?

A) A clustered index forms and orders the table where the leaf level is the actual data page whereas a non-clustered index is separate from the table and just points to the relevant pages within the table.

Q) How would you display fragmentation information for a specific table?

A) Use dbcc showcontig command against the table or index.

Q) What command would you use to check the integrity of a database, what option to check the physical allocation only?

A) You would use dbcc checkdb and if you only want to check the physical allocation then you add the Physical_only option, i.e dbcc checkdb (‘dbname’) with physical_only.

Q) What is locking and how would you get information on locks?

A) Locking is the process of ensuring data consistency in a database, so for example as one process is updating a row of data it will not allow another row to update it at the same time. You can get information on locks by using the sp_lock stored proc, querying the dm_tran_locks DMV and for more detail by running SQL Profiler.

Q) What is blocking and how would you check for it?

A) Blocking is a result of locking where one process holds resources that the other needs, it can be identified by using the sp_who stored proc, and drilling down with sp_lock, querying the dm_tran_locks DMV and for even more detail by running SQL Profiler.

Q) What are traceflags and when would you use them?

A) Trace flags are used to set specific server characteristics. For example it can be used to print out extra information about deadlocks with traceflag 1204 and 1222. They are either set at startup with the –T option or can be explicitly started with dbcc traceon(<traceflag number>).

Q) What are dynamic management views?

A) DMV’s were introduced in SQL Server 2005 and provide a simple way for accessing the state of a server and gathering diagnostic information without too much overhead on the SQL Server.

Q) What are the benefits of SQL Server 64 bit architecture?

A) The main benefit is that you can use much more memory than you could on 32bit and also you can take advantage of 64 bit Windows server.

Q) What is the dedicated admin port and when would you use it?

A) The Dedicated Admin Connection listens on a special port and allows an administrator to connect to an otherwise unresponsive server due to lack of user connections for example. It is accessed by running sqlcmd –A or by prefixing admin: to the instance name in SSMS.

Q) What is the Distributed Transaction Co-ordinator (DTC) used for.

A) The DTC manages transactions across a network of Windows based systems. MS DTC allows:

– Distributed transaction support for Microsoft SQL Server.

– Updated data residing on two or more Microsoft SQL Server systems.