SQL Server Basics

An effective CSA administrator does not need advanced skills to manage SQL Server, but a basic understanding helps. Performing basic queries and tuning server settings are about the most complicated database tasks you have to complete. Knowing how to check that SQL Server is accessible by other applications is also valuable.

Basic Queries

Basic queries performed against SQL Server provide important information. First, they demonstrate that SQL Server runs and responsive. Second, they list the data in the selected table or tables. Finally, they display the time the query took, so that you can judge the performance of the SQL Server.

SQL Server has two primary interfaces for performing basic queries:

• The SQL Enterprise Manager—The SQL Enterprise Manager shows administrators all databases and allows administrators to make changes to databases and the settings of the server itself. The GUI is friendly (by database standards) and easy to use. Running a query from Enterprise Manager is as simple as right-clicking on a table and returning either all rows or a selected number of rows from the beginning of the table.

• The SQL Query Analyzer Tool—The SQL Query Analyzer tool is basically an enhanced CLI for the inner workings of SQL Server. The Query Analyzer is more powerful than the Enterprise Manager, but not nearly as easy to use.

To run a simple query from the Query Analyzer, select the CSA database (the default database name is CSAMC45), and then enter the query in the query window. The statement used to perform the query is the SQL SELECT statement.

The syntax for SELECT is: select * from 11 tablename"

Figure 11-8 shows the Query Analyzer and output from a select query.

Figure 11-8 SQL Query Analyzer

E

[ SQL Query Analyzer - [Query - C

Help

JnJxj Jajxj

' & a fl | &

csö ¡M | « ! m.

B 111_ri csamc45

[W^Ie

fffB

Object

Browser x| f

I select * from

-

-

-

-

g

g CSAMC45(CSAMC45VW]

g CSAMC45 33 (J csaanalysls45 B~ U csamc45 : 6User Tables 1 Bf dbo.admin j S-m dbo.admin | ffl-IHl dbo.admin 1 0 dbo.agent.

ffl U dbo.agent,_

EE 0 dbo.agent, ; ± 3 dbo.aleit | dbo.aleit_c | IB-DU dbo.aleil_( : IB-J§| dbo.aleil_(

SHU dbo.aleit_r @-M dbo.aleit_r

i-r1

¿•■{HI dbo.alerLf

±L

_

_

l-jU dbo.alertj 1 1] dbo.alerU ® U dbo.alert_:

id |testmode

name

deacription

hidden

iption_detBJi.

1

i |M

Servers -

Internally deployed

Default

jrnup Inr jenin,

d. . .

This

is a generi

® 0 dbo.analiis ® □ dbo.analiis ä 0 dbo.analvs ffl-im dbo.appolt

2

2 ¡Id

Systems -

Restricted Networking

Systems

which are under n<

it...

0

This

is a group

3

3 ¡H

<A11 tlindoras>

Auto-em

rollment group for

TJ. . .

4

1 W

Servers -

SQL Server 2000

Systems

running Microsoft

S. . .

0

This

is a group

El dbo.appck

5

5 ¡M

Desktops

- All types

Default

group for systems

t. . .

This

is a generi

fo-fffl dbo.appck

e

6 H

Systems -

Mission Critical

Systems

that need to be r

no. . .

This

is a group

B-fm dbo.arch_i lil-fHI dbo.aichitt

7

1 ¡N

Servers -

DNS Servers

Systems

running DNS servei

"3

0

This

is a group

1-jU dbo.archiv

8

8 H

Servers -

All types

Default

group for systems

t. . .

This

is a generi

1 O dbo.avdati

9

9 H

Servers -

Internally deployed

Default

group for servers

d. . .

0

This

is a generi

11 □ dbo.avliit IS F] dbo.backt EE 0 dbo.chanc ¿I dbo.dliectc dbo.diproip]

10

Id H

Servers -

Internally deployed

Default

group for servers

d. . .

This

is a generi

11

11 M

Servers -

All types

Default

group for systems

t. . .

This

is a generi

12

12 H

Desktops

- All typea

Default

group for systems

t. . .

0

This

is a generi▼(

I'l 1 JJ I

□ Grids |ilJ Messages |

^ Objects |m Templates| j

Query batch completed.

|C5AMC45 (8.0) C5AMC45\Admlnlstrator (75) |csamc45

| 0:00:00 |34row

s |Ln Col 22

| (Connections; 1 | ^ |

You need to put the table name in quotes. The results of the query are displayed in the lower window in Query Analyzer.

Processor Utilization

SQL Server is a resource-intensive application and can consume all the available resources on a host by itself. Performance problems on single-server CSA MC implementations are not uncommon. High-processor utilization is often realized in the form of performance problems and generally sluggish response of the server. Windows Task Manager is useful to determine what process uses most of the processing capacity of the machine and the total-processor utilization of all processes.

You can configure SQL Server to use single or multiple processors. If CSA is installed on a single server with four or more processors, you can enhance performance by allowing SQL Server to use only two of the processors and reserving the other processors for CSA application processes and tasks. Figure 11-9 shows the processor tab of the SQL Server properties.

Figure 11-9 SQL Server Processor Settings

Figure 11-9 SQL Server Processor Settings

Memory

Early versions of SQL Server required administrators to set the amount of memory reserved for SQL Server and any changes required reboot to take effect. SQL Server 2000 can dynamically tune memory usage and reservation and generally does a good job of it, but tweaking the settings can still yield performance increases. On large servers running CSA and SQL Server, be sure to reserve at least 1 Gigabyte of memory for Windows and CSA applications, and let SQL Server use the rest. In most cases, SQL Server uses only what it needs, but it is wise to set a limit so that any large or complex queries or database operations do not use all the machine's memory. Figure 11-10 is the Memory tab of the SQL Server properties.

Figure 11-10 SQL Server Memory Settings

Figure 11-10 SQL Server Memory Settings

ODBC Connection to Remote Database Server

Open Database Connectivity (ODBC) is the interface CSA uses to access SQL Server. ODBC connections are accessed through the Administrative Tools menu on the server. The CSA setup routine configures the ODBC connection to the SQL Server automatically using the information you provide.

The CSA MC does not work at all if the database connection is broken. Use the ODBC applet to test database connectivity and change configuration settings if needed. The ODBC configuration applet is wizard-based and clicking Next and accepting the defaults should take you to the final step where the database connection can be tested. Figure 11-11 shows the System DSN tab of the Data Sources Administrative Tools applet.

Figure 11-11 System DSN List

My Documents

My Documents

Was this article helpful?

0 0

Post a comment