1 / 37

A Technical Overview of Microsoft SQL Server 2005

A Technical Overview of Microsoft SQL Server 2005. Melville Thomson IT Pro Evangelist (UK). http://blogs.technet.com/melville. What Will We Cover?. Security enhancements Manageability enhancements Availability enhancements. Helpful Experience.

armen
Download Presentation

A Technical Overview of Microsoft SQL Server 2005

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A Technical Overview of Microsoft SQL Server 2005 Melville Thomson IT Pro Evangelist (UK) http://blogs.technet.com/melville

  2. What Will We Cover? • Security enhancements • Manageability enhancements • Availability enhancements

  3. Helpful Experience • Experience managing and maintaining Microsoft® SQL Server™ • Experience administering databases Level 200

  4. Agenda • SQL Server 2005 Architecture • Advanced Security • Improved Management Features • High Availability

  5. Enterprise Product Integration

  6. SQL Server 2005 Core Architecture

  7. Background Services

  8. SQL Server Management Studio • Simplify common management tasks • Integrated management interface • Server transparency • SQL Server Management Object extensibility

  9. Integrated Management Environment Template Explorer and Solution Explorer Object Explorer Registered Servers SummaryPane

  10. demonstration Demo • Using SQL Server Management Studio • Using Object Explorer

  11. SQL Command-line Tool Type SQLCMD to enter Interactive Mode Type Exit to exit Interactive Mode Run “SQLCMD –o” to direct output to a file “SQLCMD –S” to connect to Named Instance Run “SQLCMD –i” to run against a pre-built script file

  12. SQLCMD Scripting Functionality LON-DC-01 CustomerDB LON-SQL-01 ContactsDB N:\BACKUPS SQLCMD –Q –i C:\SCRIPTS\BACKUP.SQL –v PATH=“N:\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”

  13. demonstration Demo • SQL Scripting Features • Using Queries and Scripts • Using Solution Explorer

  14. Agenda • Overview of SQL Server 2005 • Advanced Security • Improved Management Features • High Availability

  15. Security Mechanisms Authorization Encryption Authentication

  16. SQL Server 2005 Schema Object Permissions Schema

  17. Surface Area Configuration Services disabled and features not installed by default New Installation 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 2000 SQL Server 2005

  18. demonstration Demo • Surface Area Configuration • Securing SQL Server with SAC • Exporting and Importing Configurations

  19. SQL Object Namespace Server Database Schema Object LON-SQL-01 .AdventureWorks .Sales .Customer

  20. User Schema Separation LON-SQL-01 .AdventureWorks .Customer Prior to SQL Server 2005, object namespace included object owner Schema as part of namespace removes this issue Change of ownership required application rewrites .User .Sales .User1

  21. demonstration Demo • User-Schema Separation • Understanding User-Schema Separation

  22. SQL Agent Proxies Agent Proxy SQL Server Active Directory

  23. demonstration Demo • SQL Agent Proxies • Using SQL Agent Proxies

  24. Agenda • Overview of SQL Server 2005 • Advanced Security • Improved Management Features • High Availability

  25. Database Mail MailHost Database SQL Service Broker

  26. demonstration Demo • Database Mail • Configuring Database Mail • Sending and Checking E-mail

  27. Mirrored Backups Mirror 1 Mirror 2

  28. demonstration Demo • Mirrored Backups • Using Mirrored Backups

  29. Agenda • Overview of SQL Server 2005 • Advanced Security • Improved Management Features • High Availability

  30. Database Snapshots SQL Server 2005Enterprise Edition Maintain historicaldata for report generation Safeguard data against administrativeerror Safeguard data against user error

  31. Database Tuning Advisor

  32. Workload and Results .trc file .sql script Table Database Tuning Advisor Databases

  33. SQL Profiler • Debug statements • Analyze performance • Stress testing • Audit database activity

  34. demonstration Demo • SQL Profiler • Using SQL Profiler • Generating a Deadlock • Analyzing Deadlock with SQL Profiler

  35. Replication Enhancements • Redesigned replication wizards • Initializing replication subscribers • Pre-computed partitions • Transactional parallelism

  36. Internet Replication Binary over TCP/IP XML over HTTP/HTTPS Subscriber Firewall Publisher IIS https://myserver.mydomain.com

  37. Session Summary • Secure by default, by design • New integrated management tools • Expanded and enhanced availability

More Related