# Presentation: 672513

## SYBASE DBA

- Migrating to Sybase System 11.5 & the Maintenance of CDR** **
**Brian Ramey**

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
Hello.  For those of you, I haven’t had the privilege to meet yet, my name is Brian Ramey, your Sybase Database Administrator.  I am now going to take you through an interactive presentation.  It will require a small participation factor, but you will be rewarded.

## What You Will Learn

**System 11.5.  Why and How we migrated?**
**CDR, behind the scenes.**
**Every question I’ll ask will have the same answer, “Better Performance”.**
**Brian’s addition to sign language.**

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
What has the DBA done for you lately?  The two latest and greatest items are: migrating Peter to Sybase System 11.5, correcting problems that would have destroyed CDR.  We will discuss why and how we have achieved migration to 11.5 and show a quick look at the behind the scenes work accomplished to save CDR. One quick note, every question I pose will have the same answer, “Better Performance”.  And that last bullet, whenever I do this (golf swing) I need everyone to say “You the man”.  Let’s continue..

## Why we Migrated to System 11.5

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
So, Why did we migrate to Sybase System 11.5?  Anyone?

## Why we Migrated to System 11.5

**Better Performance.**
**More Parallelism**
**Better Memory Management**

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
That’s right, “better performance”.  Sybase System11.5 offers more parallelism and better memory management.  These are just some of the things end users as well as developers will appreciate.  Queries will run more efficiently.  But there is another more selfish reason for myself that we migrated to Sybase 11.5.

## Why we Migrated to System 11.5

**Easier Maintenance.**
**DDL objects are created faster**
**New dbcc command**
**Sybase will soon not support previous versions**
**Sysaudits without the wait (coming soon)**

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
Simply put, easier maintenance.  Certain DDL objects will be created faster, an off shoot of the more parallelism above.  There is a new dbcc command to cut dbcc times by 50% or more.  This will allow us to ensure against corruption as your databases become larger.  As Sybase improves it’s line of relational databases, it has historically stopped supporting previous versions, once again, easier maintenance.  What we will have to begin soon is the auditing of users who choose to access the production database with PowerBuilder.  The improved Sysaudits performance will allow this to occur with minimal performance degradation.  With easier maintenance, you can expect what from your DBA?  That’s right, better performance.

## How we migrated to System 11.5

**Goals:**
**One nightly cycle**
**Transparent transition**
**No corruption**
**Obstacles:**
**No place to test new server**
**Limited disk area for dump/recovery**
**Limited system memory**

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
We had these Goals and obstacles to face when migrating to system11.5.  We wanted to perform the migration in one nightly cycle,  this would illuminate any downtime to developers or users.  We wanted a transparent transition.  This meant, that nothing could be changed that would affect user connectivity to the database.  The i/o port and naming convention used for the old database had to remain unchanged on the new system.  We also wanted to ensure that no databases were corrupted during the transition.  The obstacles made the goals ultimately more complex.  Because, we wanted to maintain transparency, there was no way to bring up the new server with one or two databases to test it prior to migrating the new system.  Because we could not test it, I was not allowed to remove the old server or upgrade the server in place.  I had to make sure that if something went wrong during the migration, we could shut down the new server, and bring the old one back up.  Limited disk space meant that databases copied out of the old database server had to be compressed one at a time to extract all of the databases.  Loading to the new database, the database was uncompressed and then loaded, one at a time.  Limited system memory for the operating system and the database server meant compression would take a considerable amount of time.  One factor in our favor, Ramey power tools.  A set of korn shell scripts designed to partial automate the process.

## Success Rate

**No Corruption of databases!**
**1.5 days to bring up the server.**
**Login problems on day #1.**

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
So how well did we accomplish our goals.  There was no corruption of any of the databases.  This was by far the most important issue.  I have a network of Sybase certified professionals that I correspond with.  Two of them have migrated to 11.5, both have had at least one database fail because of corruption, or reserved word conflicts.  The task took us a little longer than anticipated.  I stayed in the office until 7AM finishing the loading of the new databases, however, during the loading of login information,  only half of the logins were correctly transferred.  A new column in the syslogins table was the source of the  problem.  There was no errors reported by Sybase when I entered in the logins and spot checking the databases, everything seemed okay.  I have brought this issue to the attention of the International Sybase Users Group.  But this is a lesson learned, one that was annoying but not harmful.  And with a lesson learned we can expect what from future performance?

## Success Rate

**No Corruption of databases!**
**1.5 days to bring up the server.**
**Login problems on day #1.**
**Lessons learned: ****Better performance in future transitions.**

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
That’s right, we can expect better performance.

## CDR?

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
Earlier I said that there were some issues with CDR, so what were they?

## CDR?

**Corruption Problems.**
**Hardware Related**
**No Inserts or Indexes**
**Obstacle:**
**How to correct a 39 GB database during a typical weekend outage, quickly**

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
The CDR database was corrupted.  It was not a software error from the application running against the database or from Sybase itself.  The corruption was hardware related.  We could not insert rows past a particular disk segment and indexes were failing immediately during creation.  The obstacle that we faced was how to correct a 39 GB database during a typical weekend outage with little to no prep time?  To be honest, there was no guarantee that we’d be able to correct the corruption error.  The errors we had received were high in severity and it affected the allocation table.

The process that we used to correct the situation revolved around SQL Backtrack's ability to copy each table separately.  After the back up took place we deleted the database and recreated the database on different disk devices.  Then we recreated the database objects from SQL Backtrack and monitored the index creation.  All in all a three day process.

## Results

**Corruption was corrected.**
**Hardware issues resolved.**
**Re-creation of tables : disk defragmented.**
**All Indexes recreated : index allocation optimized.**

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
What were the results of this work?
1st:  There is no corruption in CDR.
2nd:  We have taken precautions to avoid similar hardware problems.
3rd:  The re-creation of tables and data, one at a time has defragmented the disks that the database resides on.  We have actually increased the size of the available disk area by removing page splits.
Lastly:  All indexes have been recreated, and for the next few weeks, you should have optimized index allocation throughout the database.  This equates to faster response times.

Faster Response times are good because it means you are seeing......

## Better Performance!

- Questions?

- 1998 Client Server Team Presentation: Sybase DBA

- 1/9/98

**Notes:**
Better Performance.

I open the floor for any questions.  Thank you, you’ve been a great audience.