SQL Server 2000 Administration

1
SQL Server 2000 Administration

• John Syre
• Collaborative Data Services
• Fred Hutch Cancer
• Research Center

2
CDS Brownbag Series(In the spirit of sharing)

• This is the 12th in a series of seminars
• Materials for the series can be downloaded from
  https//cds.fhcrc.org/downloads.aspx
• Sign up to be on our email list

Next Brown Bag June 6 Creating Web Apps with
ASP.NET 2.0 and Visual Studio 2005 By Paul
Litwin, 1230-145 p.m (refer to web site for
location)
3
Agenda of Topics

• Installing SQL Server
• Designing Implementing Databases
• Creating Managing Tables
• Designing Configuring SQL Server Security
• Importing and Exporting Data
• Backing Up SQL Server Databases
• Restoring SQL Server Databases
• Automating SQL Server Administration
• Monitoring SQL Server

4
Installing SQL Server

• Do not install SQL Server on a system that is
  also a domain controller.
• Disable SQL Server ports on your firewall
• Use the most secure file system NTFS
• If possible, put database and transaction log on
  different physical disks.
• My preference is not use the SQL Server
  installation directory for database files and
  backups.

5
Typical Raid Installation
Better for writes
Put on different drives for better performance
6
Non-RAID with Separate Disks

• If your configuration allows then each of the
  following should be put on different disks to
  optimize performance
• Databases
• Transaction Logs
• tempdb

7
System Databases Created

• Databases created during an installation
• Master
• The master database records all of the system
  level information for a SQL Server system.
• Msdb
• The msdb database is used by SQL Server Agent for
  scheduling alerts and jobs, and recording
  operators.
• Model
• The model database is used as the template for
  all databases created on a system.
• Tempdb
• Holds all temporary tables and temporary stored
  procedures. Note order by creates a temporary
  table in tempdb.

8
Create Database using Enterprise Manager

• Using tools in Enterprise Manager
• Select the Run a Wizard from the Tool Bar
• From the dialog select Database-gtCreate Database
  Wizard
• This provides a step by step process. Defaults
  are presented where available.
• Right click on databases in the left explorer
  window and select New Database
• Under the 3 tabs General, Data Files and
  Transaction Logs specify the database name,
  location, and other properties.
• You will have to enter the correct values for
  locations, etc.

9
Set database Properties
10
Databases Properties

• If you want to see details about an existing
  database right click on the database in the
  explorer window select Properties

11
Create Database DDL
USE master GO CREATE DATABASE MYDB ON PRIMARY
(NAME 'MYDB_Data', FILENAME
'f\sqlserverdata\MSSQL\data\MYDB_Data.MDF',
SIZE 5MB, FILEGROWTH 10) LOG ON (NAME
'MYDB_Log', FILENAME 'f\sqlserverdata\MSSQL\d
ata\MYDB_Data.MDF', SIZE 5MB, FILEGROWTH
10) GO
12
Create/Manage Tables with Table Designer

• Provides graphical interface to easily select
  column data types
• When making changes automatically handles
  constraints for you

13
Table Designer Features

• Set properties for columns
• Create and manipulate relationships between
  tables, including choosing primary and foreign
  keys for the relationship
• Create and manipulate indexes, primary keys, and
  unique constraints attached to a table
  Indexes/Keys
• Create and manipulate check constraints
• Changes are not applied until you close the
  designer

14
Table Creation Recommendations

• Dont use spaces in names
• Always create a primary key
• Try to avoid NULLs, use default values instead
• Be careful of names being too long, some tools
  use the name of table in creating other entities
  like stored procedures
• Take into consideration other databases you may
  want to support when naming tables
• Some say always create a clustered index (only
  allowed one per table). My take, It depends on
  the situation.

15
DDL for Creating Tables
CREATE TABLE dbo.Allergies ( PatientId
int NOT NULL , AllergyId int IDENTITY (1,
1) NOT NULL , Allergen varchar (50)
, AllergyResponse varchar (50) , CONSTRAINT
PK_Allergies PRIMARY KEY CLUSTERED
( AllergyId ) )
16
DDL for changing a Table
ALTER TABLE Alergies ADD EnteredDate datetime
NOT NULL
ALTER TABLE dbo.Allergies ADD CONSTRAINT
FK_Allergies_Patients FOREIGN KEY
( PatientId ) REFERENCES dbo.Patients
( PatientId ) ON DELETE CASCADE ON UPDATE
CASCADE GO

• Note can only add one column at a time. New
  columns are added to the end of the table
  definition.

17
Designing Configuring Security

• Use the Microsoft Baseline Security Analyzer
  (MBSA). Download from http//www.microsoft.com/tec
  hnet/security/tools/mbsahome.mspx
• Install the latest service packs
• Implement good administrative and development
  policies
• Limit privilege level of SQL Server Services
• Use Windows Authentication Mode for internet
• Audit connections to SQL Server

18
Audit connections to SQL Server

• Steps required
• Expand a server group.
• Right-click a server, and then click Properties.
• On the Security tab, under Audit Level, click
  Failure.

You must stop and restart the server for this
setting to take effect.
19
Security Checklist

• Too many members of the sysadmin fixed server
  role.
• Blank or trivial passwords.
• Weak authentication mode.
• Excessive rights granted to the Administrators
  group.
• Incorrect access control lists (ACLs) on SQL
  Server data directories.
• Plaintext sa password in setup files.
• Excessive rights granted to the guest account (If
  you dont need it, best to delete it).
• Improper configuration of the Everyone group,
  providing access to certain registry keys.
• Improper configuration of SQL Server service
  accounts.
• Missing service packs and security updates.

20
Limit privilege level of SQL Server Services.

• SQL Server Engine/MSSQLServer
• Run as a Windows domain user account with
  regular user privileges.
• SQL Server Agent Service/SQLServerAgent
• Run as a Windows domain user account with
  regular user privileges. Note may not be able to
  if VBScripts need special privileges.

21
Importing Exporting Tables

• DTS (Data Transformation Services)
• Preferred method of handling data
• Most flexible of the methods
• BCP (Bulk CoPy)
• Command line utility (import export)
• Faster method than DTS for large data sets
• Bulk Insert (import only)

22
DTS Overview

• Provides a set of tools that lets you extract,
  transform, and consolidate data from disparate
  sources into single or multiple destinations
• Can use graphical DTS Package Designer to build
  transformations or can program command line
  executable packages using DTS object model.

23
DTS Designer
24
Types of Backups

• Simple (does not allow transaction log backups)
• Operations that are not logged
• Bulk load operations
• Select Into
• Create Index
• Text/image operations
• Full(allows transaction log backups)
• All operations fully logged.
• Bulk Logged (allows transaction log backups)
• Operations that are not logged
• Same as Simple mode

25
(No Transcript)
26
Use Simple when

• Your data is not critical.
• Losing all transactions since the last full or
  differential backup is not an issue.
• Data is derived from other data sources and is
  easily recreated.
• Data is static and does not change often.
• Space is limited to log transactions. (This may
  be a short-term reason, but not a good long-term
  reason.)

27
Use Bulk Logged

• Data is critical, but logging large data loads
  bogs down the system.
• Most bulk operations are done off hours and do
  not interfere with normal transaction processing.
• You need to be able to recover to a point in
  time.

28
Use Full when

• Data is critical and no data can be lost.
• You always need the ability to do a point-in-time
  recovery (transaction logs must also be backed
  up).
• Bulk-logged activities are intermixed with normal
  transaction processing.
• You are using replication and need the ability to
  resynchronize all databases involved in
  replication to a specific point in time.

29
Switching between modes

• What if you want to have Full mode backups but
  you have a large bulk load data operation??
• Use the following command
• ALTER DATABASE Northwind SET RECOVERY BULK LOGGED
  
• Or use Enterprise Manager

30
When to Backup the master database

• ALTER DATABASE
• CREATE DATABASE
• sp_addlogin
• sp_droplogin
• Modified system configuration parameters

31
Creating a Backup Plan

• Enterprise Manager select the Run a Wizard
  tool. Expand Management select Database
  Maintenace Plan Wizard

32
Selecting Databases

• Select the databases you want to backup from the
  list

33
Set Options

• Select options
• Specify time interval

34
Checking database integrity

• You can optionally check the integrity before
  backing up the database
• Additional dialog windows for specifying
  Tranaction Log backups and historical logs

35
Recovering Databases

• Timeline showing database usage and backup
  strategy

• First recover the database backup
• Apply Transaction Log from 1200 noon
• Next apply Transaction Log from 600 PM
• Have to again modify 10 records

36
Restore Database Tool

• From Enterprise Manager, select from the Main
  menu
• Tools -gtRestore Database
• Select the database to restore to (Note you can
  can pick a different database name to restore to)

37
Restore Options

• Select additional options as needed
• If loading a database backup created on another
  system may need to modify path locations

38
SQL Server wont Start!! master database corrupted

• May have a bad master database to recover
• Execute rebuildm.exe shown on right to rebuild
  master database. Located in (installdir)\Microsoft
  SQL Server\80\Tools\Binn
• Restore last backup of master
• Re-attach databases right click on Databases
  then All Tasks-gtAttach Databases

39
Automating SQL Server Administration

• ALERTS (configured in SQL Agent)
• Examples of event alerts
• Database out of space
• SQL Server was abnormally terminated
• Database is corrupted
• Table is corrupted
• Examples of performance condition alerts
• Transaction log almost full
• Number of merge conflicts exceeds an user-defined
  threshold
• Examples of custom alerts
• Low inventory
• Aborted download

40
SQL Agent

• Installed with SQL Server it is a task scheduler
  and alert manager.
• Featurescapability to email or page an operator
  when an event occurs
• Provides history logs of scheduled jobs
• Runs as a separate windows service SQLServerAgent
  
• Make sure it is set to autostart

41
Automating SQL Server Administration

• ALERTS (configured in SQL Agent)
• Following are examples of event alerts
• Database out of space
• SQL Server was abnormally terminated
• Database is corrupted
• Table is corrupted
• Following are examples of performance condition
  alerts
• Transaction log almost full
• Number of merge conflicts exceeds an user-defined
  threshold
• Following are examples of custom alerts
• Low inventory
• Aborted download

42
Monitoring SQL Server

• SQL Profiler
• System Monitor
• SQL Server Enterprise Manager
• Error Logs
• Transact-SQL Statements

43
tempdb log full!!

• Error The log file for database 'tempdb' is
  full.
• If you have a development SQL Server life is
  easy, just re-start SQL Server. The tempdb is
  rebuilt on start up.
• If a production server, then much more painful.
• Refer to http//www.aspfaq.com/show.asp?id2446
  for steps to correct.