Managing and Administering SQL Server 2005 Matthew Stephen ITPro Evangelist Microsoft Ltd http:blogs

1
Managing and Administering SQL Server
2005Matthew Stephen ITPro Evangelist Microsoft
Ltdhttp//blogs.technet.com/mat_stephen
2
Welcome to this TechNet Event

• FREE bi-weekly technical newsletter
• FREE regular technical events hosted across the
  UK
• FREE weekly UK US led technical webcasts
• FREE comprehensive technical web site
• Monthly CD / DVD subscription with the latest
  technical tools resources
• FREE quarterly technical magazine

We would like to bring your attention to the key
elements of the TechNet programme the central
information and community resource for IT
professionals in the UK
To subscribe to the newsletter or just to find
out more, please visit www.microsoft.com/uk/techne
t or speak to a Microsoft representative during
the break
3
What We Will Cover

• SQL Server 2005 New Features
• New Administration Tools
• SQL Server Express 2005
• SQLCMD
• SQLiMail
• Maintenance and Performance

4
Agenda

• SQL Management Studio
• Web Data Administrator
• SQL Express Manager
• SQLCMD
• SQLiMail
• Database Maintenance Workflow
• SQL Profiler
• Monitoring Tools

5
SQL Management StudioNew Features

• Replaces Enterprise Manager
• Improved User Interface
• Object Explorer
• Contextual Toolbars
• Integrated SQLCMD Mode
• Online Restore
• SQL Server remains online
• Only data being restored is unavailable during
  restore

6
SQL Management StudioNew Features

• Online Indexing
• Query while Indexes are Rebuilding
• .NET Framework 2.0
• Visual Studio Integration
• User Defined Functions, Data Types, Procedures
• Use Any CLR-Compliant Language
• Dedicated Administrator Connection
• Fast Recovery

7
Agenda

• SQL Management Studio
• Web Data Administrator
• SQL Express Manager
• SQLCMD
• SQLiMail
• Database Maintenance Workflow
• SQL Profiler
• Monitoring Tools

8
Web Data AdministratorBasic Features

• IIS or Cassini PWS
• Windows or SQL-based Authentication
• Streamlined Interface for Remote Management
• Import and Export Capabilities
• T-SQL Query Editor

9
Agenda

• SQL Management Studio
• Web Data Administrator
• SQL Express Manager
• SQLCMD
• SQLiMail
• Database Maintenance Workflow
• SQL Profiler
• Monitoring Tools

10
SQL Express ManagerBasic Features

• SQL Server Express replaces MSDE
• .NET Framework
• Simplified Database Management
• Tree view of database objects
• Wizards for common tasks
• Local and remote databases
• Powerful T-SQL Query Editor
• Freely Downloadable and Distributable

11
SQL Express ManagerUser Interface
12
Agenda

• SQL Management Studio
• Web Data Administrator
• SQL Express Manager
• SQLCMD
• SQLiMail
• Database Maintenance Workflow
• SQL Profiler
• Monitoring Tools

13
SQLCMDBasic Features

• Replaces ISQL and OSQL
• Batch Functionality
• Command-line variables
• Nested scripts
• Script Chaining
• SQLCMD Mode in Management Studio

14
SQLCMDBatch functionality
LON-DC-01 CustomerDB
LON-WK-001
LON-SQL-01 ContactsDB
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
BACKUP.SQL
N\BACKUPS
C\gt SQLCMD Q iC\SCRIPTS\BACKUP.SQL
vPATHN\BACKUPS
15
Agenda

• SQL Management Studio
• Web Data Administrator
• SQL Express Manager
• SQLCMD
• SQLiMail
• Database Maintenance Workflow
• SQL Profiler
• Monitoring Tools

16
SQLiMailBasic Features

• Replaces SQLMail
• SMTP Replaces MAPI
• Multiple Profiles and Host Databases
• Support for Clusters and 64-bit Servers
• Uses SQL Service Broker

17
SQLiMailProcess Flow
SQL script EXECUTE sendmail_sp
SMTP Server
msdb
SQLiMail Configuration sendmail_sp Stored
Procedure
Service Broker
Email messages Logs
18
Agenda

• SQL Management Studio
• Web Data Administrator
• SQL Express Manager
• SQLCMD
• SQLiMail
• Database Maintenance Workflow
• SQL Profiler
• Monitoring Tools

19
Database Maintenance WorkflowNew Features

• Maintenance Plan Wizard
• Create Basic and Advanced Plans
• Maintenance Plan Designer
• Complex Workflow
• Visual Design Workspace
• View Generated T-SQL Statements

20
Agenda

• SQL Management Studio
• Web Data Administrator
• SQL Express Manager
• SQLCMD
• SQLiMail
• Database Maintenance Workflow
• SQL Profiler
• Monitoring Tools

21
SQL ProfilerNew Features

• Grant Trace Access without Granting SA
• New Selection User Interface
• New Security Events
• Trace Service Broker Activity
• Trace to XML File

22
SQL ProfilerTrace Access
Grant Alter Trace to DBDeveloper
DBAdmin SA Privileges
LON-SQL-01 SQL Server 2005
Start Trace with Profiler
DBDeveloper No SA Privileges
23
Agenda

• SQL Management Studio
• Web Data Administrator
• SQL Express Manager
• SQLCMD
• SQLiMail
• Database Maintenance Workflow
• SQL Profiler
• Monitoring Tools

24
Monitoring ToolsNew Features

• Log File Viewer
• Activity Monitor
• View Locks in Real-Time
• Agent User Role
• Enhanced Security
• SQL Server Agent Multiple Proxies
• One Proxy Per Subsystem
• WMI Event Alerts

25
Session Summary

• New Tools for Management and Administration
• Increased Availability and Flexibility
• Security Enhancements Throughout

26
Microsoft LearningTraining Resources for IT
Professionals
To see the detailed syllabus or to locate a
training provider please visitwww.microsoft.com/l
earning
27
Assess Your Readiness with Microsoft Skills
Assessment

• What is Microsoft Skills Assessment?
• Self-study learning tool to evaluate readiness
  for product and technology solutions, instead of
  job roles (certification)
• Windows Server System, Security, Visual Studio
  .NET, Office 2003
• Free, online, unproctored, and available to
  anyone
• Answers the question Am I ready?
• Determines skills gaps and provides learning
  plans with Microsoft Official Curriculum courses
• Post your High Score to see how you stack up
• Visit www.microsoft.com/assessment

28
Become a Microsoft Certified Systems
Administrator (MCSA)

• What is the MCSA certification?
• For IT professionals who manage and maintain
  networks and systems based on Microsoft Windows
  Server
• How do I become an MCSA on Windows Server 2003?
• Pass three core exams
• Pass one elective exam or two CompTIA
  certifications
• Where do I get more information?
• www.microsoft.com/mcsa

29
Become a Microsoft Certified Systems Engineer
(MCSE)

• What is the MCSE certification?
• Premier certification for IT pros who analyze the
  requirements and design, plan, and implement the
  infrastructure for business solutions based on
  the Microsoft Windows Server System
• How do I become an MCSE on Windows Server 2003?
• Pass six core exams
• Pass one elective exams from a comprehensive list
• Where do I get more information?
• www.microsoft.com/mcse

30
TechNet SubscriptionsHave You Heard the News?

• Software without time limits! Full-version
  evaluation software provides more flexibility to
  TechNet Plus subscribers.
• Complimentary technical support. Two free
  technical support incidents included with each
  TechNet Plus subscription save you time resolving
  mission-critical issues.
• Have the most current resources on hand to
  evaluate, implement, and support Microsoft
  solutionsdelivered monthly on CD or DVD, withno
  dependency on Internet connectivity or firewalls.

www.microsoft.com/technet/subscriptions
31
Where Else Can I Get Help?

• Free chats and webcastswww.microsoft.com/technet/
  community/chats
• www.microsoft.com/technet/community/webcasts
• List of newsgroups
• www.microsoft.com/technet/community/newsgroups
• Microsoft community sites
• www.microsoft.com/technet/community
• Community eventswww.microsoft.com/technet/communi
  ty/events
• Community columns
• www.microsoft.com/technet/community/columns

32
(No Transcript)