Sql server 2000 administration
This presentation is the property of its rightful owner.
Sponsored Links
1 / 43

SQL Server 2000 Administration PowerPoint PPT Presentation


  • 80 Views
  • Uploaded on
  • Presentation posted in: General

SQL Server 2000 Administration. John Syre C ollaborative D ata S ervices Fred Hutch Cancer Research Center. 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

Download Presentation

SQL Server 2000 Administration

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql server 2000 administration

SQL Server 2000 Administration

John Syre

Collaborative Data Services

Fred Hutch Cancer

Research Center


Cds brownbag series in the spirit of sharing

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 2005By Paul Litwin, 12:30-1:45 p.m (refer to web site for location)


Agenda of topics

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


Installing sql server

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.


Typical raid installation

Typical Raid Installation

Better

for writes

Put on different drives

for better performance


Non raid with separate disks

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


System databases created

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.


Create database using enterprise manager

Create Database using Enterprise Manager

Using tools in Enterprise Manager

  • Select the Run a Wizard from the Tool Bar

    • From the dialog select Database->Create 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.


Set database properties

Set database Properties


Databases properties

Databases Properties

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


Create database ddl

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\data\MYDB_Data.MDF',

SIZE = 5MB,

FILEGROWTH = 10%)

GO


Create manage tables with table designer

Create/Manage Tables with Table Designer

  • Provides graphical interface to easily select column data types

  • When making changes automatically handles constraints for you


Table designer features

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


Table creation recommendations

Table Creation Recommendations

  • Don’t 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.”


Ddl for creating tables

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]

)

)


Ddl for changing a table

DDL for changing a Table

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

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


Designing configuring security

Designing & Configuring Security

  • Use the Microsoft Baseline Security Analyzer (MBSA). Download from http://www.microsoft.com/technet/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


Audit connections to sql server

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.


Security checklist

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 don’t 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.


Limit privilege level of sql server services

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.


Importing exporting tables

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)


Dts overview

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.


Dts designer

DTS Designer


Types of backups

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


Use simple when

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.)


Use bulk logged

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.


Use full when

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.


Switching between modes

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


When to backup the master database

When to Backup the master database

  • ALTER DATABASE

  • CREATE DATABASE

  • sp_addlogin

  • sp_droplogin

  • Modified system configuration parameters


Creating a backup plan

Creating a Backup Plan

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


Selecting databases

Selecting Databases

  • Select the databases you want to backup from the list


Set options

Set Options

  • Select options

  • Specify time interval


Checking database integrity

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


Recovering databases

Recovering Databases

Timeline showing database usage and backup strategy

  • First recover the database backup

  • Apply Transaction Log from 12:00 noon

  • Next apply Transaction Log from 6:00 PM

  • Have to again modify 10 records


Restore database tool

Restore Database Tool

  • From Enterprise Manager, select from the Main menu

    Tools ->Restore Database

  • Select the database to restore to (Note: you can can pick a different database name to restore to)


Restore options

Restore Options

  • Select additional options as needed

  • If loading a database backup created on another system may need to modify path locations


Sql server won t start master database corrupted

SQL Server won’t 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->Attach Databases…


Automating sql server administration

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


Sql agent

SQL Agent

  • Installed with SQL Server it is a task scheduler and alert manager.

  • Features:capability 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


Automating sql server administration1

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


Monitoring sql server

Monitoring SQL Server

  • SQL Profiler

  • System Monitor

  • SQL Server Enterprise Manager

  • Error Logs

  • Transact-SQL Statements


Tempdb log full

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?id=2446 for steps to correct.


  • Login