CORSO DI SQL SERVER 7 Amministrazione

CORSO DI SQL SERVER 7
Amministrazione
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
INDICE
L’AMMINISTRAZIONE DI SQL SERVER............................................................................... 3
AVVIARE E FERMARE I SERVIZI DI SQL SERVER ............................................................................ 3
LA GESTIONE DEI SERVER ............................................................................................................. 4
Registrare ed eliminare un server o un gruppo ........................................................................ 4
Altre azioni possibili con i server e i gruppi............................................................................. 5
Starting, Pausing, and Stopping SQL Server............................................................................ 5
Le proprietà del server ............................................................................................................ 6
LA STRUTTURA DI SQL SERVER ........................................................................................... 7
INTERFACCE CLIENT ..................................................................................................................... 7
OLE DB .................................................................................................................................. 7
ODBC ..................................................................................................................................... 7
Application.............................................................................................................................. 7
Driver...................................................................................................................................... 7
Data source ............................................................................................................................. 7
RDO........................................................................................................................................ 7
ADO........................................................................................................................................ 7
DB-Library.............................................................................................................................. 7
LA STRUTTURA DI COMUNICAZIONE .............................................................................................. 8
Net-Library e le utility correlate .............................................................................................. 8
ODS (Open Data Services) ...................................................................................................... 9
LOGIN, USER, ROLE E GROUP ............................................................................................. 11
Creazione di un login ............................................................................................................ 11
Comprendere i ruoli .............................................................................................................. 11
Aggiungere uno user.............................................................................................................. 12
Server role, database role, login e user.................................................................................. 13
PROCEDURE DI AMMINISTRAZIONE PRINCIPALI......................................................... 14
AUTOMATING ADMINISTRATIVE TASKS ...................................................................................... 14
What is Automated Administration?....................................................................................... 14
What are the Components of Automatic Administration? ....................................................... 14
SQLSERVERAGENT ................................................................................................................... 15
Defining Jobs ........................................................................................................................ 15
DATABASE MAINTENANCE PLAN WIZARD .................................................................................. 16
Update Data Optimization Information ................................................................................. 17
Database Integrity Check ...................................................................................................... 18
Specify the Database Backup Plan......................................................................................... 18
Specify Backup Disk Directory .............................................................................................. 19
Maintenance History ............................................................................................................. 20
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
L’AMMINISTRAZIONE DI SQL SERVER
Microsoft® SQL Server™ administration applications, and the accompanying services, are
designed to assist the system administrator with all administrative tasks related to maintaining and
monitoring server performance and activities.
Molte delle attività che saranno qui descritte possono essere svolte mediante l’Enterprise Manager
(EM) di SQL Server. SQL Server Enterprise Manager is a graphical tool that allows for easy,
enterprise-wide configuration and management of Microsoft® SQL Server™ and SQL Server
objects. SQL Server Enterprise Manager provides:
•
A scheduling engine.
•
Administrator alert capability.
•
Drag-and-drop control operations across multiple servers.
•
A built-in replication management interface.
You can also use SQL Server Enterprise Manager to:
•
Manage logins, permissions, and users.
•
Create scripts.
•
Manage devices and databases.
•
Back up databases and transaction logs.
•
Manage tables, views, stored procedures, triggers, indexes, rules, defaults, and user-defined
data types.
Avviare e fermare i servizi di SQL Server
SQL Server può essere avviato automaticamente ogni volta che viene avviato Windows. Sotto
Windows 9x è possibile avviare e fermare i servizi con il Server Service Manager, mentre sotto
Windows NT è anche possibile gestire l’avvio di SQL Server direttamente dal Pannello di
Controllo/Services. In entrambi i modo è possibile effettuare l’avvio automatico (alla partenza del
Sistema Operativo) o quello manuale.
I servizi principali sono i seguenti:
• MSSQLServer: il motore principale di SQL Server
•
SQLServerAgent: il servizio per la gestione automatica di SQL Server.
• MSDTC: per il coordinamento delle transazioni distribuite
You can pause SQL Server before stopping the server. Pausing SQL Server prevents new users
from logging in and gives you time to send a message to current users asking them to complete their
work and log out before you stop the server.
If you stop SQL Server without pausing it, all server processes are terminated immediately.
Stopping SQL Server prevents new connections and disconnects current users.
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
La gestione dei server
Registrare ed eliminare un server o un gruppo
Prima di poter utilizzare e amministrare un server locale o remoto SQL Server è necessario
registrarlo.
Per registrare un server utilizzando EM (Enterprise Manager) è necessario per prima cosa scegliere
il gruppo al quale assegnare il server, e se non è presente, è necessario crearlo premendo il tasto
destro del mouse in EM su SQL Server Group. Allo stesso modo si procede per registrare il server,
e viene avviato un Wizard che chiede nell’ordine:
1. il nome del server
2. l’autentication mode
3. il gruppo al quale assegnare il server
Figura 1: Wizard per la registrazione del server
In modo analogo è possibile procedere per eliminare un server o un gruppo. Nella figura seguente è
possibile vedere come con il tasto destro del mouse sia possibile effettuare tutte le azioni sui server
e sui gruppi:
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
Figura 2: azioni che è possibile svolgere sui server registrati
Altre azioni possibili con i server e i gruppi
Tra le azioni possibili, quelle più importanti sono:
• eliminare il server
•
connettere e disconnettere il server
•
fermare (stop), mettere in pausa (pause) e riavviare il server (start). When you pause
Microsoft® SQL Server™, users who are connected to the server can finish tasks, but new
connections are not allowed. For example, you can pause SQL Server for a few minutes and
send a shutdown message to connected users before shutting it down. You can also resume a
SQL Server service.
Starting, Pausing, and Stopping SQL Server
Before you log in to Microsoft® SQL Server™, you need to know how to start, pause, and stop
SQL Server. After you are logged in, you can perform various tasks such as administering the
server or querying a database.
Because SQL Server is integrated with the Microsoft Windows NT® Service Control Manager, it
can be started and stopped as a Windows NT service (MSSQLServer), either locally or remotely.
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
SQL Server can be started automatically as a service each time Windows NT starts. This option can
be specified during installation by using SQL Server Setup, and after installation by using SQL
Server Enterprise Manager or the Services application in Control Panel.
You can log off from the Windows NT network without shutting down SQL Server.
When you start SQL Server, you are starting the SQL Server service (MSSQLServer); SQL Server
Agent runs as a service named SQLServerAgent. If the MSSQLServer and SQLServerAgent
services are not configured to start automatically, you must start them manually. After you start the
MSSQLServer service, users can establish new connections to the server. After you start the
SQLServerAgent service, you can use SQL Server’s scheduling features.
You can pause SQL Server before stopping the server. Pausing SQL Server prevents new users
from logging in and gives you time to send a message to current users asking them to complete their
work and log out before you stop the server.
If you stop SQL Server without pausing it, all server processes are terminated immediately.
Stopping SQL Server prevents new connections and disconnects current users.
Le proprietà del server
Selezionando dal menu di Figura 2 la voce Properties è possibile modificare le impostazioni del
server (che quindi, come detto in precedenza, può essere amministrato in remoto mediante EM).
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
LA STRUTTURA DI SQL SERVER
Interfacce client
OLE DB
OLE DB is an API that allows COM applications to consume data from OLE DB data sources.
OLE DB data sources include data stored in many different formats, not just SQL databases. An
application uses an OLE DB provider to access an OLE DB data source. An OLE DB provider is
a COM component that accepts calls to the OLE DB API and does whatever is necessary to process
that request against the data source.
Microsoft SQL Server™ version 7.0 introduces a native Microsoft OLE DB Provider for SQL
Server that can be used by OLE DB applications to access the data in SQL Server.
ODBC
ODBC è un’API per l’accesso ai db che rappresenta uno standard industriale. E’ una delle
interfacce più utilizzate per l’accesso ai db ed è anche riconosciuta come standard. SQL Server
dispone di una interfaccia ODBC nativa.
ODBC architecture has four components that perform the following functions.
Component
Application
Driver
Manager
Driver
Data source
Function
Calls ODBC functions to communicate with an
ODBC data source, submits SQL statements, and
processes result sets.
Manages communication between an application and
all ODBC drivers used by the application.
Processes all ODBC function calls from the
application, connects to a data source, passes SQL
statements from the application to the data source,
and returns results to the application. If necessary,
the driver translates ODBC SQL from the
application to native SQL used by the data source.
Contains all information a driver needs to access a
specific instance of data in a DBMS.
RDO
Remote Data Object. Interfaccia ad oggetti strettamente legata ad ODBC in quanto rende facilmente
accessibili le funzionalità di ODBC.
ADO
ActiveX Data Object. Interfaccia ad oggetti a più alto livello basata su OLE DB
DB-Library
API specifica di SQL Server che mette a disposizione delle applicazioni tutte le macro e le funzioni
necessarie per stabilire connessioni, formattare query, inviarle al server e restituire i risultati. E’
l’interfaccia originaria di SQL Server.
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
La struttura di comunicazione
Net-Library e le utility correlate
Affinche due SQL Server (oppure un client e un SQL Server) siano in grado di comunicare tra loro
è necessario che sia sul client che sul server siano installate le Net-Libraries1. Esistono NetLibraries per diversi protocolli, tra cui il TCP/IP, inoltre alcune Net-Libraries come le Named Pipe
o Multiprotocol supportano diversi protocolli.
Solitamente le Net-Liraries sono installate durante il setup di SQL Server, e possono essere gestite
mediante le seguenti utility:
• SQL Server Network Utility: This application allows you to activate, deactivate, and
reconfigure server Net-Libraries to listen for clients on their corresponding network protocols.
Se si utilizza Windows 9x, è necessario limitarsi al TCP/IP o Multiprotocol.
•
SQL Server Client Network Utility: questa applicazione consente di effettuare una nuova
configurazione per connettersi con uno specifico server oppure modificare la Net-Libraries di
default
E’ ovvio che l’utilizzo di un particolare protocollo di comunicazione per SQL Server è possibile
solamente se questo è installato sotto Windows.
Grazie a queste librerie è ora possibile la comunicazioni tra client e server. Microsoft® SQL
Server™ supports several methods of communicating between client applications and the server.
When the application is on the same computer as SQL Server, Windows Interprocess
Communication (IPC) components, such as local named pipes or shared memory, are used. When
the application is on a separate client, a network IPC is used to communicate with SQL Server.
Un IPC ha due componenti:
• Una API
•
1
Un protocollo
A library of functions for managing network connections and routing. Each Net-Library allows SQL Server to use a
particular network protocol. Le Net-Library sono le interface di rete di SQL Server
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
Figura 3: comunicazione in rete tra un client e un server
In Figura 3 è riportato lo schema di comunicazione tra un client ed n server SQL. Il caso più tipico è
quello di una applicazione che utilizza OLE DB (magari mediante interfaccia ADO). L’OLE DB
Application utilizza OLE DB Provider per SQL Server, il quale a sua volta per accedere all’SQL
Server in rete utilizza le Net-Library opportune. La comunicazione viene poi stabilita utilizzando
l’opportuno protocollo di rete e le Net-Library del server, fino ad arrivare all’SQL Server.
Durante le procedure di testing interne a Microsoft la Net-Library TCP/IP Socket è risultata essere
la soluzione di rete più veloce. Il numero di porta predefinito per questo protocollo è il 1433.
If SQL Server is running on a Microsoft Windows NT® computer, the Named Pipes Net-Library is
used for local communications. For local connections with no network card, Windows NT uses the
file subsystem to implement a named pipe connection. Inoltre, per la comunicazione di rete, le
Named Pipes ricorrono a meccanismo di IPC indipendenti dal protocollo, per cui è possible
utilizzare un qualsiasi protocollo sottostante. Tuttavia, dal punto di vista delle prestazioni non
risulta essere la migliore scelta, pertanto è consigliabile utilizzare TCP/IP, anche se non bisogna
rimuovere Named Pipes perché viene utilizzata per le comunicazioni locali.
ODS (Open Data Services)
Si tratta di un’interfaccia tra la Net-Library del server e il server stesso. Il suo scopo principale è
quello di gestire la rete:
• Resta in attesa di nuove connessioni
•
Annulla le connessioni non riuscite
•
Accetta l’annullamento dei comandi
•
Coordina i servizi di threading di SQL Server
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
• Restituisce al client i risultati e i messaggi
Open Data Services functions as an interface between server Net-Libraries and server-based
applications, such as Microsoft® SQL Server™ and extended stored procedure DLLs.
The client and server communicate through a proprietary data protocol known as tabular data
stream (TDS), with Net-Libraries transporting TDS between the client and server. Open Data
Services transforms the TDS protocol into an API, and provides a function library for packaging
and unpackaging client requests and server responses through TDS.
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
LOGIN, USER, ROLE E GROUP
Logins, users, roles e gruppi sono i concetti fondamentali per la gestione dei meccanismi di
sicurezza si SQL Server. Infatti, gli utenti che si vogliono connettere a SQL Server devono
possedere un login ID. A questo login ID sono assegnati dei permessi ai vari oggetti del database
(table, view, stored procedure, ecc.).
Il login può essere lo stesso login utilizzato dagli utenti per accedere ad SQL Server (e questo
semplifica la gestione dei login da parte degli amministratori di sistema) oppure è un login
specificato direttamente all’interno di SQL Server.
Creazione di un login
Per la creazione di un login premere il tasto destro del mouse sulla cartella Security\Logins e
selezionare New Login… Impostare il login ID (name), la password, il database al quale deve
accedere il login che si sta creando ed eventualmente il linguaggio di default.
Può anche essere necessario associare un login ad uno o più server roles.
Comprendere i ruoli
I meccanismi di sicurezza di SQL Server 7 includono dei ruoli predefiniti con associati diversi tipi
di diritti. In tal modo, se dobbiamo registrare degli utenti che svolgono particolari attività, è
possibile associarli direttamente a specifici ruoli.
I ruoli si suddividono in Server roles e in Database roles. Il primo tipo includono tutte le azioni a
livello di server, e quindi esterni ai singoli database. Ogni utente che appartiene ad un server role
può aggiungere altri utenti.
Fixed server role
sysadmin
serveradmin
setupadmin
securityadmin
processadmin
dbcreator
diskadmin
Description
Performs any activity in SQL Server.
Configures server-wide settings.
Adds/removes linked servers, and execute some
system
stored
procedures,
such
as
sp_serveroption.
Manages server logins.
Manages processes running in SQL Server.
Creates and alters databases.
Manages disk files.
The permissions of the sysadmin fixed server role span all of the other fixed server roles.
Non è possibile creare nuovi server role.
Il secondo tipo di ruoli sono i database role. Questi sono definiti a livello di database e riguardano
tutte le attività che uno user può effettuare. In particolare, sono specificati tutti i diritti di un utente
sugli oggetti del database. Ogni membro di un determinato database role può aggiungere altri utenti
al ruolo.
I ruoli possono essere creati, ma ne esistono alcuni predefiniti, come i seguenti:
Fixed database role Description
Performs the activities of all database roles, as
db_owner
well as other maintenance and configuration
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
activities in the database.
Adds or removes Windows NT groups,
db_accessadmin
Windows NT users, and SQL Server users in the
database.
Sees all data from all user tables in the database.
db_datareader
Adds, changes, or deletes data from all user
db_datawriter
tables in the database.
Adds, modifies, or drops objects in the database.
db_ddladmin
db_securityadmin Manages roles and members of SQL Server
database roles, and can manage statement and
object permissions in the database.
db_backupoperator Backs up the database.
db_denydatareader Sees no data in the database.
db_denydatawriter Changes no data in the database.
The permissions of the db_owner fixed database role span all of the other fixed database roles.
In aggiunta ai ruoli precedente, viene creato anche il ruolo public.
The public role is a special database role to which every database user belongs. The public role:
• Captures all default permissions for users in a database.
•
Cannot have users, groups, or roles assigned to it because they belong to the role by default.
•
Is contained in every database, including master, msdb, tempdb, model, and all user
databases.
• Cannot be dropped.
Per creare un nuovo database role è sufficiente portarsi nella cartella roles del database , premere il
tasto destro del mouse sulla cartella (o nella parte destra della finestra dell’Enterprise Manager) e
selezionare “New database role…”. Compare una finestra nella quale è possibile specificare il nome
del ruole e gli utenti associati al ruolo.
Una volta creato il ruolo, selezionando “Properties” (sempre con il tasto destro del mouse) è
possibile selezionare i diritti (grant) per quel ruolo mediante il pulsante “Permissions”.
Aggiungere uno user
Per aggiungere uno user si procede in modo analogo a quanto fatto per l’aggiunta di un database
role. Premendo il pulsante destro del mouse sulla cartella Users del database su cui si sta lavorando
e selezionando “New database user…” compare una finestra nella quale è possibile specificare il
login (tra quelli non ancora associati al database), il nome dell’utente (che può essere uguale al
login) e i database role da associare all’utente. Di default ogni nuovo utente è associato al ruolo
Public.
guest User
The guest user account allows a login without a user account to access a database. A login assumes
the identity of the guest user when all of the following conditions are met:
• The login has access to Microsoft® SQL Server™, but does not have access to the database
through his or her own user account.
•
The database contains a guest user account.
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
Permissions can be applied to the guest user as if it were any other user account. The guest user can
be deleted and added to all databases except master and tempdb, where it must always exist. By
default, a guest user account does not exist in newly created databases.
For example, to add a guest user account to a database named Accounts, execute from SQL Server
Query Analyzer:
USE Accounts
GO
EXECUTE sp_grantdbaccess guest, guest
Server role, database role, login e user
Quando si crea uno user lo si associa ad un particolare login. I diritti dello user sono la somma dei
diritti del login con quelli aggiuntivi dello user. In particolare, i diritti di uno user sono una
combinazione dei diritti:
• associati al server role per quanto riguarda il login (uno user è associato ad un solo login mentre
un login può essere associato a più server role)
•
•
sono la somma dei diritti di tutti i database role associati allo user (infatti uno user può essere
associato a più database role)
a questi vanno aggiunti i diritti particolari che è possibile associare al singolo user (mediante il
pulsante “Permissions” delle proprietà dell’utente).
Inoltre è possibile che uno user che non ha diritto di accesso ad un database (perché questo diritto
non è stato impostato per il login associato) possa comunque accedere al database ed avere tutti i
diritti associati al database role public se nel database è presente l’utente guest.
Per cambiare database è possibile utilizzare il comando USE da Query Analyzer.
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
PROCEDURE DI AMMINISTRAZIONE PRINCIPALI
Automating Administrative Tasks
What is Automated Administration?
Automated administration is the programmed response to a predictable administrative responsibility
or server event. Administrators, application writers, and analysts operating data warehouses can
benefit from task automation. To automate administration:
• Establish which administrative responsibilities or server events occur regularly and can be
administered programmatically.
•
Define a set of jobs and alerts.
•
Run the SQL Server Agent service.
What are the Components of Automatic Administration?
Jobs, operators, and alerts are the three main components of automatic administration.
Jobs
You can use jobs to define an administrative task that can be executed one or more times and
monitored for success or failure each time it executes. Jobs can be:
• Executed on one local server or on multiple remote servers.
•
Executed according to one or more schedules.
•
Executed by one or more alerts.
• Made up of one or more job steps.
Job steps can be executable programs, Windows NT commands, Transact-SQL statements,
Microsoft ActiveX Script™, or replication agents.
Operators
An operator is an individual responsible for the maintenance of one or more computers running
SQL Server. In some enterprises, operator responsibilities are assigned to one individual. In larger
enterprises with multiple servers, many individuals share operator responsibilities.
Operators are notified in one or more ways:
• E-mail
•
Pager (through e-mail)
• net send
You can choose to define an operator’s e-mail alias as an alias assigned to a group of individuals. In
this way, all members of that alias can be notified at the same time.
Alerts
An alert is a definition that matches one or more SQL Server events and a response, should those
events occur. In general, an administrator cannot control the occurrence of events but can control
the response to those events with alerts. Alerts can be defined to respond to SQL Server events by:
• Notifying one or more operators.
•
Forwarding the event to another server.
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
•
Corso su SQL Server 7 – Amministrazione
Executing a job.
SQLServerAgent
SQLServerAgent is a Microsoft® Windows NT® service that executes jobs, monitors Microsoft
SQL Server™, and fires alerts. SQLServerAgent is the service that allows you to automate some
administrative tasks. As such, you must start the SQLServerAgent service before your local or
multiserver administrative tasks can run automatically. SQL Server Agent is also supported on
Windows® 95/98 platforms
Defining Jobs
The first step in implementing a job is to define it. The primary attributes of a job are:
• Name
•
Category
•
Owner
•
Description
•
Job steps
•
Schedules
•
Notifications
Job Name
Every job must have a name. Job names must be unique only if they originate from the same server.
For example, if SQL Server is set up to run on a target server, a locally created job and a
downloaded job from a master server can share the same name. A job name can be no more than
128 characters.
Job Category
Job categories allow you to organize your jobs for easy filtering and grouping. For example, you
can organize jobs in to categories that correspond to company departments, such as payroll, human
resources, or finance.
During installation, SQL Server creates these local job categories within SQL Server Agent:
• [Uncategorized (Local)]
•
Database Maintenance
•
Full-Text
•
Jobs from MSX
•
REPL - Alert Response
•
REPL - Checkup
•
REPL - Distribution
•
REPL - Distribution Cleanup
•
REPL - History Cleanup
•
REPL - LogReader
•
REPL - Merge
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
•
REPL - Snapshot
•
REPL - Subscription Cleanup
Corso su SQL Server 7 – Amministrazione
• Web Assistant
By default, local jobs are assigned to the [Uncategorized (Local)] job category.
There is also one default master server job category: [Uncategorized (Multi-Server)]. Multiserver
categories exist only on a master server. When a multiserver job is downloaded, its category is
changed to Jobs from MSX at the target server.
Job Owner
The job owner is the individual who creates a job or for whom the job is created. By default, SQL
Server Agent populates the owner information with the SQL Server login ID of the job creator.
Only members of the sysadmin role can give ownership of a job to another user or can alter the
attributes of a job owned by another user.
Job Description
A job can be run by other users on both the local and remote computers running SQL Server. By
writing a description at the time a job is created, the job owner can ensure that other users will
understand quickly the purpose of the job.
A job description must not exceed 512 characters.
Job Steps
A job step is an action that the job takes on a database or a server. Every job must have at least one
job step. Job steps can be operating system commands, Transact-SQL statements, Microsoft®
ActiveX Script™, or replication tasks.
Database Maintenance Plan Wizard
The Database Maintenance Plan Wizard can be used to help you set up the core maintenance tasks
that are necessary to ensure that your database performs well, is regularly backed up in case of
system failure, and is checked for inconsistencies. The Database Maintenance Plan Wizard creates
a Microsoft® SQL Server™ job that performs these maintenance tasks automatically at scheduled
intervals.
The maintenance tasks that can be scheduled to run automatically are:
• Reorganizing the data on the data and index pages by rebuilding indexes with a new fill factor.
This ensures that database pages contain an equally distributed amount of data and free space,
which allows future growth to be faster.
•
Compressing data files by removing empty database pages.
•
Updating index statistics to ensure the query optimizer has up-to-date information regarding the
distribution of data values in the tables. This allows the query optimizer to make better
judgements about the best way to access data because it has more information about the data
stored in the database. Although index statistics are automatically updated by SQL Server
periodically, this option can force the statistics to be updated immediately.
•
Performing internal consistency checks of the data and data pages within the database to ensure
that a system or software problem has not damaged data.
•
Backing up the database and transaction log files. Database and log backups can be retained for
a specified period. This allows you to create a history of backups to be used in the event that
you need to restore the database to a time earlier than the last database backup.
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
The results generated by the maintenance tasks can be written as a report to a text file, HTML file,
or the sysdbmaintplan_history tables in the msdb database. The report can also be e-mailed to an
operator.
Update Data Optimization Information
This table shows the options and their functions that are available in the Update Data
Optimization Information dialog box.
Reorganize data and index pages Cause the indexes on the tables in the database to be
dropped and re-created with a new FILLFACTOR. The
FILLFACTOR determines how much empty space to leave
on each page in the index, thereby reserving a percentage
of free space on each data page of the index to
accommodate future expansion. As data is added to the
table, the free space fills because the FILLFACTOR is not
maintained. Reorganizing data and index pages can
reestablish the free space.
Reorganize pages with the Cause the indexes on the tables in the database to be
dropped and re-created with the original FILLFACTOR
original amount of free space
that was specified when the indexes were created.
Change free space per page Cause the indexes on the tables in the database to be
dropped and re-created with a new automatically calculated
percentage to
FILLFACTOR, thereby reserving the specified amount of
free space on the index pages. The higher the percentage,
the more free space is reserved on the index pages and the
larger the index grows. Valid values are from 0 through
100.
Update statistics used by query Cause the distribution statistics of each index created on
user tables in the database to be resampled. The
optimizer.
distribution statistics are used by Microsoft® SQL
Server™ to optimize navigation through tables during the
processing of Transact-SQL statements. To build the
distribution statistics automatically, SQL Server
periodically samples a percentage of the data in the
corresponding table for each index. This percentage is
based on the number of rows in the table and the frequency
of data modification. Use this option to perform an
additional sampling using the specified percentage of data
in the tables.
Generate distribution statistics by sampling the percentage
Sample % of the database
of data in the tables. The higher the percentage, the more
accurate the statistics, but the longer the sampling takes. If
the specified value does not generate a sufficient sample,
SQL Server determines an adequate sample size
automatically. Valid values are from 1 through 100.
Remove unused space from Remove any unused space from the database, thereby
allowing the size of the data files to be reduced.
database files
Remove unused space from the database only if the
When it grows beyond
database exceeds the specified size (in MB).
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
Amount of free space to remain Determine the amount of unused space to remain in the
database after the database is shrunk (the larger the
after shrink
percentage, the less the database can shrink). The value is
based on the percentage of the actual data in the database.
For example, a 100-MB database containing 60 MB of data
and 40 MB of free space, with a free space percentage of
50 percent, would result in 60 MB of data and 30 MB of
free space (because 50 percent of 60 MB is 30 MB). Only
excess space in the database is eliminated. Valid values are
from 0 through 100.
Set the frequency that the data optimization tasks
Schedule
(scheduled using SQL Server Agent) are executed. The
default is every Sunday at midnight.
Change the default schedule.
Change...
Database Integrity Check
This table shows the options and their functions that are available in the Database Integrity Check
dialog box.
Use this
Check database integrity
To do this
Check the allocation and structural integrity of user and
system tables, and indexes in the database, by running the
DBCC CHECKDB Transact-SQL statement. This ensures
that any integrity problems with the database are reported,
thereby allowing them to be addressed later by a system
administrator or database owner.
Check the data and index pages in the database during the
Include indexes
integrity tests.
Attempt to repair any minor Attempt to correct any minor problems detected during the
database integrity tests automatically. It is recommended
problems
that this option be selected.
Check only the data pages in the database during integrity
Exclude indexes
tests. This does not check indexes. This option executes
faster than selecting Include indexes because fewer pages
in the database are checked.
Perform these tests before doing Cause the database and/or internal data integrity tests to be
executed before backing up the database or transaction log.
backups
If the integrity tests detect inconsistencies, any subsequent
database or transaction log backup is not backed up.
Set the frequency that the data integrity tasks (scheduled
Schedule
using SQL Server Agent) are executed. The default is
every Sunday at midnight.
Change the default schedule.
Change...
Specify the Database Backup Plan
This table shows the options and their functions available in the Specify the Database Backup
Plan dialog box.
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
Use this
To do this
Back up the database as part of Cause the entire database to be backed up as part of the
maintenance tasks. Backing up the database is important in
the maintenance plan
case of system or hardware failure (or user errors) that
cause the database to be damaged in some way, thus
requiring a backed-up copy to be restored.
Verify the integrity of the Check that the backup set is complete and all volumes are
backup on completion of the accessible by executing the RESTORE VERIFYONLY
Transact-SQL statement.
backup
Back up the database to the specified tape device. The tape
Tape
device is attached to the computer containing the
Microsoft® SQL Server™ database that is being backed
up.
Back up the database to the disk that is located on the
Disk
computer containing the SQL Server database that is being
backed up. See Specify Backup Disk Directory to specify
the location of the database backup.
Set the frequency that the database backup tasks
Schedule
(scheduled using SQL Server Agent) are executed. The
default is every Sunday at midnight.
Change the default schedule.
Change...
Specify Backup Disk Directory
This table shows the options and their functions that are available in the Specify Backup Disk
Directory dialog box.
Use this
To do this
Use the default backup directory Back up the database to the \Mssql7\Backup disk directory
located on the computer containing the Microsoft® SQL
Server™ database that is being backed up.
Back up the database to the specified disk directory located
Use this directory
on the computer containing the SQL Server database that is
being backed up.
(...)
Change the default disk directory used to back up the
database. Only drives on the computer containing the SQL
Server database that is being backed up can be selected.
Create a subdirectory for each Create a subdirectory under the specified disk directory
containing the database backup for each database that is
database
being backed up as part of the maintenance plan.
Delete database backups automatically that are older than
Remove files older than
the specified period. A history of database backups should
be maintained in the event that the database needs to be
restored to a point in time earlier than the last performed
backup. Retain as many backups as disk space allows and
as far in the past as is necessary.
Define the file name extension used for each file that
Backup file extension
contains the database backup. The default file extension is
Ing. Andrea Bulgarelli
0339/4709751
[email protected]
http://web.tiscalinet.it/andbulga
Corso su SQL Server 7 – Amministrazione
BAK.
Maintenance History
This table shows the options and their functions that are available in the Maintenance History
dialog box.
Use this
To do this
Write
history
to
the Write the report as rows to this table on the server upon
msdb.dbo.sysdbmaintplan_history which the maintenance plan was executed. The report
contains the steps executed by the maintenance plan,
table on the local server
including database name, activity, date, result (success
or failure), and any error information. It includes one
row for each activity, per database, per execution date.
Write
the
report
as
rows
to
the
Write history to the server
msdb.dbo.sysdbmaintplan_history table on a remote
server. Windows NT Authentication is used to connect
to the remote server. The report contains the steps
executed by the maintenance plan, including database
name, activity, date, result (success or failure), and any
error information. It includes one row for each activity,
per database, per execution date.
(...)
Change the remote server to which the report is written.
Only computers running SQL Server can be selected.
Specify the maximum number of rows in the table that
Limit rows in the table to
represent history for this plan only. If the number of
history rows in the table for this plan exceeds this value,
older rows for this plan (representing the earliest
recorded history) are deleted. Setting this value can
prevent the table from becoming too large and filling the
msdb database (if auto-grow is not permitted). The
default is 10000.