UTS Short Course SQL Server 2005 for Developers

1
UTS Short CourseSQL Server 2005for
Developers
2
Course Website

• http//www.ssw.com.au/ssw/events/2006SQL/
• Course Timetable
• Course Materials

3
About Adam

• Chief Architect for www.ssw.com.au - experience
  with
• internal corporate development and
• generic off-the-shelf databases
• Clients Enterasys Networks, Cisco, Microsoft
• Runs Teams of Developers
• President .NET User Group, Sydney
• Speaker for Microsoft Roadshows, Dev Conn, VSLive
• Microsoft Regional Director, Australia
• Email AdamCogan_at_ssw.com.au

4
(No Transcript)
5
Session 1 Managing and AdministeringSQL
Server 2005
6
What Will We Cover?

• SQL Server 2005 New Features
• New Administrative Tools
• Maintenance and Performance

7
Agenda

• Managing SQL Server 2005
• Maintaining Enterprise Databases

8
SQL Server Management Studio

• Simplifies common management tasks
• Integrated management interface
• Server transparency
• SQL Management Object extensibility

9
Integrated Management Environment
10
Integrated Management Environment
11
Integrated Management Environment
12
Integrated Management Environment
13
Integrated Management Environment
14
Demo

• SQL Server Management Studio
• Creating a New Database
• Creating a Login

15
Disaster Recovery

• Choose recovery model
• Manage backup media
• Create base functionality script

16
Demo

• Backup and Restore
• Creating a Backup Device
• Backing up a Database
• Backing up a Transaction Log
• Restoring a Database

17
SQL Configuration Manager
18
SQL Configuration Manager
19
SQL Configuration Manager
20
Demo

• SQL Configuration Manager
• Using the Configuration Manager

21
Surface Area Configuration
Services disabled and features not installed by
default
New Installation
SQL Server 2005
22
Surface Area Configuration
Services disabled and features not installed by
default
New Installation
SQL Server 2005
State of services and features carried over from
upgraded installation
SQL Server 2000
SQL Server 2005
23
Surface Area Configuration
Services disabled and features not installed by
default
SQL Server 2005
Surface Area Configuration for Services and
Connections
Surface Area Configuration for Features
State of services and features carried over from
upgraded installation
SQL Server 2005
24
SQL Command-line Tool
25
SQL Command-line Tool
26
SQL Command-line Tool
27
SQL Command-line Tool
28
SQL Command-line Tool
29
SQLCMD Scripting Functionality
30
SQLCMD Scripting Functionality
CONNECT LON-DC-01\MSSQLSERVER setvar db
CustomerDB BACKUP DATABASE (db) TO DISK
(PATH)\(db).bak CONNECT LON-SQL-01\MSSQLSERVER
setvar db ContactsDB BACKUP DATABASE (db) TO
DISK (PATH)\(db).bak
31
SQLCMD Scripting Functionality
SQLCMD Q i C\SCRIPTS\BACKUP.SQL v
PATHN\BACKUPS
CONNECT LON-DC-01\MSSQLSERVER setvar db
CustomerDB BACKUP DATABASE (db) TO DISK
(PATH)\(db).bak CONNECT LON-SQL-01\MSSQLSERVER
setvar db ContactsDB BACKUP DATABASE (db) TO
DISK (PATH)\(db).bak
32
SQLCMD Scripting Functionality
SQLCMD Q i C\SCRIPTS\BACKUP.SQL v
PATHN\BACKUPS
CONNECT LON-DC-01\MSSQLSERVER setvar db
CustomerDB BACKUP DATABASE (db) TO DISK
(PATH)\(db).bak CONNECT LON-SQL-01\MSSQLSERVER
setvar db ContactsDB BACKUP DATABASE (db) TO
DISK (PATH)\(db).bak
33
SQLCMD Scripting Functionality
SQLCMD Q i C\SCRIPTS\BACKUP.SQL v
PATHN\BACKUPS
CONNECT LON-DC-01\MSSQLSERVER setvar db
CustomerDB BACKUP DATABASE (db) TO DISK
(PATH)\(db).bak CONNECT LON-SQL-01\MSSQLSERVER
setvar db ContactsDB BACKUP DATABASE (db) TO
DISK (PATH)\(db).bak
34
Demo

• SQLCMD
• Using SQLCMD
• Using SQLCMD in non-interactive mode
• Using Management Studio Integration

35
Database Mail
36
Database Mail

• MailHost Database

37
Database Mail

• MailHost Database

SQL Service Broker
38
Agenda

• Managing SQL Server 2005
• Maintaining Enterprise Databases

39
Replication Enhancements

• Redesigned replication wizards
• Initializing replication subscribers
• Pre-computed partitions
• Transactional parallelism

40
Enterprise Replication Monitor
41
Enterprise Replication Monitor
42
Enterprise Replication Monitor
43
Internet Replication
44
Internet Replication
45
Internet Replication
46
Database Snapshots
47
Database Snapshots
Maintain historicaldata for report generation
48
Database Snapshots
Maintain historicaldata for report generation
Safeguard data against administrativeerror
49
Database Snapshots
Maintain historicaldata for report generation
Safeguard data against administrativeerror
Safeguard data against user error
50
Database Tuning Advisor
51
Database Tuning Advisor
52
Workload and Results
Database Tuning Advisor
53
Workload and Results
.sql script
Database Tuning Advisor
54
Workload and Results
.trc file
.sql script
Database Tuning Advisor
55
Workload and Results
.trc file
.sql script
Table
Database Tuning Advisor
56
Workload and Results
.trc file
.sql script
Table
Database Tuning Advisor
Databases
57
Workload and Results
.trc file
.sql script
Table
Database Tuning Advisor
Databases
58
Demo

• Database Tuning Advisor
• Analyzing Workload with DTA

59
Database Maintenance Plans
60
Database Maintenance Plans
61
Database Maintenance Plans
62
Demo

• Database Maintenance Workflow
• Using the Maintenance Plan Wizard
• Using the Maintenance Plan Designer

63
Dynamic Management Views

• Expose server state information

64
Dynamic Management Views

• Expose server state information
• Reference using SQL 2005 namespace

65
Dynamic Management Views

• Expose server state information
• Reference using SQL 2005 namespace

SELECT wait_type, wait_time_ms FROM
LON-DCSQL-01.AdventureWorks.sys.dm_os_wait_stats G
O
66
SQL Profiler

• Debug statements
• Analyze performance
• Stress testing
• Audit database activity

67
Demo

• Monitoring Tools
• Using Replication Monitor
• Using Job Activity Monitor
• Monitoring with SQL 2005 MOM Pack

68
Session Summary

• Efficient, intuitive management tools
• Automated database maintenance
• Prescriptive recommendations

69
Session 1 Lab

• Introduction to SQL Server 2005 Management Studio
• Download from Course Materials Site
• http//www.ssw.com.au/ssw/events/2006SQL/

70
Where Else Can I Get Help?

• Free chats and webcasts
• List of newsgroups
• Microsoft community sites
• Community events and columns

www.microsoft.com/technet/community