1 / 67

데이터베이스 관리자를 위한 Microsoft SQL Server 2005 관리 및 유지보수 도구

데이터베이스 관리자를 위한 Microsoft SQL Server 2005 관리 및 유지보수 도구. 이 상 옥 차장 ( solee@microsoft.com) IT Evangelist Developer & Platform Evangelism ( 주 ) 한국마이크로소프트. 목차. 설치 : SQL Server 2005 SQL Server 2005 관리자 도구 모니터링 : SQL Server 2005 데이터베이스 유지보수 SQL Server 2005 보안 재난복구 데이터 가용성 복제.

haven
Download Presentation

데이터베이스 관리자를 위한 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. 데이터베이스 관리자를 위한 Microsoft SQL Server 2005관리 및 유지보수 도구 이 상 옥 차장(solee@microsoft.com) IT Evangelist Developer & Platform Evangelism (주)한국마이크로소프트

  2. 목차 • 설치: SQL Server 2005 • SQL Server 2005관리자 도구 • 모니터링: SQL Server 2005 • 데이터베이스 유지보수 • SQL Server 2005보안 • 재난복구 • 데이터 가용성 • 복제

  3. 설치: SQL Server 2005 새로운 기능 • Windows Installer • SNAC 사용(MDAC 9.0 대체) • Feature Tree • 트리 구조의 컴포넌트 설치 화면 • System Configuration Checker (SCC) • 설치 준비작업 진단 • (WMI) Windows Management Instrumentation 사용 Contd…

  4. 하드웨어 요구사항: 최소

  5. SQL Server Editions (32 Bit) • Standard Edition • 클러스터링 지원 안됨, indexed views, etc. • 16 인스턴스 (라이센스 필요) • Enterprise Edition • 모든 기능 지원, 대용량 데이터베이스 용 • 50 인스턴스 (추가 라이센스 없음) • Developer Edition • 개발자용으로 모든 기능제공하며 테스트 및 개발용도로 사용 • Express Edition • 무상 배포용으로 클라이언트, 간단한 작업용으로 사용 • Mobile Edition • Smart Devices에 설치 • 메인 데이터베이스와 복제 및 동기화 지원

  6. SQL Server Editions (64 Bit) • SQL Server 2005 Enterprise Edition (64-bit) • 모든 기능 지원, 대용량 및 고성능 데이터센터에 사용 • SQL Server 2005 Developer Edition (64-bit) • 모든 기능 지원, 개발자 용으로 테스트 및 개발에 사용

  7. SQL Server 2005관리자 도구 • SQL Server Management Studio • SQL Computer Manager • sqlcmd 도구 • SQL Management Objects

  8. What Is SQL Server Management Studio? • Integrated management and development environment • Based on Visual Studio .NET • Incorporates functionality of Enterprise Manager, Query Analyzer, and Analysis Manager in previous releases • Used to manage relational databases, Analysis Services, Reporting Services, andSQL Server CE databases • Includes tools for creating Transact-SQL, XMLA, MDX, and XQuery scripts

  9. Open Registered Servers window 1 Provide SQL Server instance details 2 Test the registered server 3 Verify that the server appears under Registered Servers 4 How to Register a Server

  10. What Is Object Explorer? • Window for browsing and managing objects • Object Explorer folders • Object Explorer buttons

  11. Click New SQL Server Query 1 Type a Transact-SQL statement 2 Click Execute and connect to SQL Server 3 Browse the results 4 How to Execute Transact-SQL Queries

  12. What Is a SQL Server Management Studio Solution? • Collection of connections and queries • Project templates

  13. How to Create a SQL Server Management Studio Solution Click File, New, Project 1 Select the SQL Server Scripts template 2 Add a connection 3 Set the connection properties 4 Create a new query 5 Set the query properties 6 Type the Transact-SQL statements 7 Save the project 8

  14. What Is SQL Computer Manager? • Console snap-in for managing SQL Server services and connectivity • Icons: • Services • Server Network Configuration • Client Network Configuration

  15. What Is the sqlcmd Utility? • Command-line tool for executing Transact-SQL statements and scripts • Uses OLE DB to run Transact-SQL batches • Replaces osql • Enhancements over osql • Variables • Query server information • Passes error information to calling environment • Dedicated Administrator Connection • Commands

  16. Open a command prompt window 1 Execute sqlcmd 2 Type Transact-SQL statements and sqlcmd commands 3 Type GO 4 Type QUIT to close sqlcmd 5 How to Use the sqlcmd Utility Interactively

  17. Open a command prompt window 1 Execute sqlcmd 2 Type Transact-SQL statements and sqlcmd commands 3 Type GO 4 Type QUIT to close sqlcmd 5 How to Use the sqlcmd Utility Interactively

  18. Open a command prompt window 1 Execute sqlcmd 2 Type Transact-SQL statements and sqlcmd commands 3 Type GO 4 Type QUIT to close sqlcmd 5 How to Use the sqlcmd Utility Interactively

  19. 모니터링: SQL Server 2005 • SQL Profiler • DDL Triggers • Event Notifications • Metadata Views

  20. SQL Profiler Enhancements in SQL Server 2005 • Profiling Analysis Services • Tracing Showplan and deadlock events • Saving results as XML • Aggregating data

  21. How to Save a Trace as XML • Create and execute a trace • File menu, Save As, Trace XML File • Specify name and location for file

  22. Lesson: Using DDL Triggers • What Are DDL Triggers? • How to Create DDL Triggers • How to Manage DDL Triggers • Demonstration: Creating a DDL Trigger

  23. What Are DDL Triggers? • Triggers to trap DDL statement execution • Database or server scope Process: DDL statement executed UPDATE STATISTICS someTable 1 DDL action performed 2 Trigger fires EventData 3

  24. What Are Event Notifications? • Trap SQL Server events, including • DDL events • DML events • Trace events • Use Service Broker architecture to transmit events toa service • A message type and contract are predefined • Create a queue, a service, and a route

  25. Querying Static Metadata • Provide information about database objects, for example: • Database files • Tables • Indexes • In <Database name>\Views\System Views folder • Directly query the system metadata USE AdventureWorks SELECT * FROM sys.tables

  26. Querying Dynamic Metadata • Provide information about the current state of a server, for example: • Locks held • Threads • Memory usage • In <Database name>\Views\System Views folder • Directly query the system metadata SELECT * FROM sys.db_tran_locks

  27. 데이터베이스 유지보수 • Managing Indexes • Database Tuning Advisor

  28. SELECT * FROM MyTable ALTER INDEX … REBUILD … SELECT column::query(…) FROM … XML Index CREATE INDEX … ON MyTable(…) WITH (ONLINE = ON) CREATE INDEX … WITH (MAXDOP=3) XML Column ALLOW_PAGE_LOCKS = ON ALLOW_ROW_LOCKS = ON New Index Features in SQL Server 2005 • ALTER INDEX statement • Online index operations • Parallel index operations • Locking options • Indexes with included columns • Partitioned indexes • XML indexes

  29. What Is the Database Tuning Advisor? Workload Results .trc file .sql script Table DTA Databases

  30. Create a new session 1 Specify the workload 2 Set tuning options 3 Perform the analysis 4 Examine the results 5 Implement the recommendations 6 How to Use the Database Tuning Advisor

  31. SQL Server 2005보안 • SQL Server 2005 Security Overview • Managing SQL Server 2005 Security • Managing Permissions • Managing Certificates

  32. Security Enhancements in SQL Server 2005 • Password policy for SQL Server logins • Hierarchical security scopes • Separation of user and schema • Limited metadata visibility • Declarative execution context

  33. What Are Principals? Securables Permissions Principals Windows Group Domain User Account Local User Account Windows SQL Server Login Server Role SQL Server User Database Role Application Role Group Database

  34. What Are Securables? Securables Permissions Principals Windows Group Files Domain User Account Registry Keys Local User Account Windows SQL Server Login Server Server Role SQL Server Database User Schema Database Role Application Role Group Database

  35. What Are Permissions? Securables Permissions Principals Windows Group Files ACL Domain User Account Registry Keys Local User Account Windows GRANT/REVOKE/DENY CREATE ALTER DROP CONTROL CONNECT SELECT EXECUTE UPDATE DELETE INSERT TAKE OWNERSHIP VIEW DEFINITION BACKUP SQL Server Login Server Server Role SQL Server Database User Schema Database Role Application Role Group Database

  36. What Are Schemas? Namespaces for database objects dbo Products (Server1.AdventureWorks.dbo.Products) SalesData Orders (Server1.AdventureWorks.SalesData.Orders)

  37. Understanding Permissions • Assign permissions using: • Object Explorer in SQL Server Management Studio • Transact-SQL • GRANTREVOKEDENY • Can perform action if: • Permission granted to the principal or a collection containing principal • - AND - • Permission not denied to the principal or a collection containing principal

  38. Connect to server 1 Right-click login and click Properties 2 Permissions page, click Add Objects Add the server <servername> Assign permissions 3 4 5 How to Manage Server Permissions In Object Explorer USE master GRANT ALTER ANY DATABASE TO [SERVERX\Bill]

  39. What Are Certificates? • Digitally signed document containing a public/private key pair • Obtained from: • Certificate authority • Certificate server • Used for: • Authentication – a message signed by the private key is guaranteed to be sent by the owner of the certificate • Encryption – a message encrypted with a public key can only be decrypted with the matching private key, and vice versa

  40. How to Manage Certificates • Creating certificates CREATE CERTIFICATE AWCert WITH SUBJECT = 'CertificateForAdventureWorks' , ENCRYPTION_PASSWORD = 'P@ssw0rd' • Exporting certificates DUMP CERTIFICATE AWCert TO FILE = 'C:\MyCert.cer' • Deleting certificates DROP CERTIFICATE AWCert

  41. 재난복구 • Disaster Recovery in SQL Server 2005 • Using Database Snapshots • Backup and Restore Operations

  42. Database snapshots • Online restore operations • Backup media mirroring • Improved verification 12:00 Snapshot Disaster Recovery in SQL Server 2005 • Database snapshots • Online restore operations Checksum12345678 • Backup media mirroring Checksum12345678 Checksum12345678 • Improved verification

  43. What Are Database Snapshots? • Read-only, consistent view of a database • Specified point-in-time • Modifying data • Copy-on-write of affected pages • Reading data • Accesses snapshot if data has changed • Redirected to original database otherwise Page Page 12:00 Snapshot

  44. Script the object in the database snapshot 1 Execute the script in the source database 2 Repopulate the object (if appropriate) 3 Caution: Not a substitute for a comprehensive backup and restore strategy How to Use a Database Snapshot to Recover Data INSERT INTO Production.WorkOrderRouting SELECT * FROM AdventureWorks_dbsnapshot_1800.Prod.WorkOrderRouting UPDATE HR.Department SET Name = ( SELECT Name FROM AdventureWorks_dbsnapshot_1800.HR.Department WHERE DepartmentID = 1) WHERE DepartmentID = 1

  45. How to Perform an Online Restore • Only in Enterprise Editions • Only for full or bulk-logged recovery models • By default, restoring a file or a page is automatically an online restore • Filegroup offline when any file within it is being restored • Database offline during restore of any files in the primary filegroup

  46. How to Recover the master Database • If SQL Server instance is accessible Start SQL Server in single-user mode Restore your latest backup of the master database Restart the server • If SQL Server instance is not accessible Rebuild master database using Setup.exe Restore master database Restore msdb and model databases 1 2 3 1 2 3

  47. 데이터베이스 가용성 • Introduction to Data Availability • Introduction to Database Mirroring

  48. Introduction to Data Availability

  49. Update Update Update Principal Database Client Mirror Database What Is Database Mirroring? Principal Database Client Mirror Database

  50. Server Roles in Database Mirroring Principal server • Server holding the principal database • Users connect to this server Mirror server • Server holding the mirror database • Users connect to this server only after failover Witness server Monitors connectivity between partners and initiates automatic failover

More Related