Sybase Session -8 Document

SYBASE SESSION 8-DOCUMENT

___________________________________________________________________________________________________ Copyrights Reserved Page 1 of 9

DEFINING SYSTEM ADMINISTRATION AND NAMING STANDARDS & ADMINISTERING VERY LARGE SQL SERVER DATABASES

Chapter Includes: SQL

Server Environment Approach

Defining

Environments

Development Test

Environment

Environment

Production Naming

Environment

Standards

VLDB Managing Checking

Database Dumps and Loads

Database Consistency

Data

Maintenance

Data

Partitioning Options

___________________________________________________________________________________________________ Copyrights Reserved Page 2 of 9

SQL Server Environment Approach: One

must approach development an SQL Server environment in a consistent

fashion or each development project is destined to waste time performing certain activities. The approach should be focused on providing flexibility for the developer and the structure for the database administrator.

Defining Environment: When

developing production software, you should assume there would be a

development environment, at least one test environment, and a production environment. For Is

each environment, we must determine the following.

the environment supported by a separate SQL Server, or does it share the

SQL Server with some other function. How

are the databases organized on the SQL Server? Is there a database per

environment rather than an SQL Server per environment?

Development Environment: The

development environment requires the maximum flexibility for

developers while enabling the necessary control structures to provide for consistent promotion of code.

Test Environment: Typical

environments include development, system test, volume/stress test,

user acceptance test, and production. Because

there are several test environments, this book considers them

conceptually as a single environment called “test”. Several We

test environments used in the software development life cycle(SDLC).

will test for function or performance.

Functional

Testing is used to confirm that elements of an application or

several distinct applications can work together. ___________________________________________________________________________________________________ Copyrights Reserved Page 3 of 9

Performance

testing is conducted to verify how the database performs under

peak numbers of users, data size, or both.

Production Environment: The

production environment is the last and most important environment in

the SDLC. It

is under maximum control of database administration, and all defined

production control must be implemented and observed. The

environment should have the following features.

The

SQL Server and database hardware used for the production should be

dedicated. An

initial load of data may be required.

Logins

and users are the actual production users.

Naming Standards: Naming

standards can be broken into two areas.

SQL

Server names and operating system names

SQL

Server names are the names you specify in the SQL Server environment.

Operating For

system names are the names you specify for files and directories.

the person retrieving or accessing an object, the name should completely

define its content without ambiguity.

Indicators: An

indicator is a string of characters embedded in a name to indicate

something about the type of object. They

are often used to indicate an object type.

Example: currdate_def

: it is used as a name for a default setting a column to the

current date and time.

VLDB: VLDB

stands for Very Large Database.

___________________________________________________________________________________________________ Copyrights Reserved Page 4 of 9

It

depends on how we define very large a fixed size such as 2GB etc

When A

a database restore time exceeds a certain threshold

VLDB is any database in which standard administrative procedures or design

criteria fail to meet business needs due to the scale of data.

VLDB Maintenance Issues: VLDB's

present a number of issues for the database administrator. Here are

top issues regarding maintenance of a VLDB Time

required to perform dumps and loads

Time

required to perform necessary database consistency checks.

Time

and effort required maintaining data.

Purging/

archiving data.

Managing

purged partitioned databases.

Managing Database Dumps and Loads: Database We

dumps are necessary to provide recoverability in case of disaster.

need database dumps to disk mirroring to protect

Here are the following problems Physical SQL

server failures

server failure

Database Table/

failure

index corruption

Controller/

disk failure

User error The

main problem with the database dumps and VLDBs is the duration of the

___________________________________________________________________________________________________ Copyrights Reserved Page 5 of 9

database dumps. Dump

time is proportional to the amount of the data in the database.

Checking Database Consistency: The

database consistency checker is a systems administration tool that

verifies pointers internal to a database and its structures. It

is highly recommended that dbcc checks be run prior to any database

dump to avoid dumping a corrupt database. The

dbcc commands will typically lock user tables, indexes, system tables

when running.

Data Maintenance: In

addition to performing database dumps and checking database

consistency on a VLDB, there are other data maintenance commands that need to be performed on a database. They include Update

Statistics

Archiving

Data

Updating Statistics: SQL

Server uses index statistics to choose the correct access path at query

optimization time. The

DBA will need to update statistics when the data distribution changes to

ensure valid information is contained on the statistics page.

Purge/Archive Procedures: The data in a VLDB may grow to a size approaching or exceeding the ___________________________________________________________________________________________________ Copyrights Reserved Page 6 of 9

available database size. At this point the decision will need to be made to expand the database or in order to purge or achieve data to free up space. When

purging or archiving data, a number of issues need to be addressed.

They are: Locking

implications

Performance Storage

implications

or retrieval of archived data.

Data Partitioning Options: When

dealing with VLDBs it may become necessary to partition the database

due to SQL Server size limitations or in order to meet backup and recovery or data maintenance requirements. There are two primary ways of Partitioning databases. Vertical

partitioning

Horizontal

Partitioning

Vertical Partitioning: It

is the process of drawing imaginary lines through a database schema, and

placing individual tables in different databases.

Horizontal Partitioning: Horizontal

partitioning of data breaking up tables into logical subsets and

placing them into the same or different databases. The

benefit of vertical partitioning over horizontal partitioning is that it

simplifies administration because all data for a given table will be in a single table in a single database. ___________________________________________________________________________________________________ Copyrights Reserved Page 7 of 9

Another

advantage of vertical partitioning is that you can hide the vertical

partitioning from the end user or application developer through the use of views. Advantage of horizontal partitioning in a VLDB is to separate active data from historical or inactive data. 1) What are the issues to be considered in developing an environment? 2) Give the disadvantages and advantages of the following Shared

database and shared objects and data

Individual

database and individual objects and data

3) Explain the following Test

environment

Production Naming

environment

standards

Abbreviation

standards

4) What are indicators in naming standards? Give the possible indicators for the following Database Server View Trigger Primary

key constraint

5) Give the syntax for naming the cursors 6) What is VLDB? What are the VLDB maintenance issues? 7) What are the steps to develop to develop a VLDB backup? 8) What is meant by database checking consistency? ___________________________________________________________________________________________________ Copyrights Reserved Page 8 of 9

Book For Reference Book: Sybase SQL Server 11 by Ray Rankins Jeffrey R. Garbus David Solomon

___________________________________________________________________________________________________ Copyrights Reserved Page 9 of 9