1 / 118

Microsoft SQL Server Administration

David Henson dhenson@certifiednetworks.com www.certifiednetworks.com. Microsoft SQL Server Administration. Logistics. Class Days: 10 Class Hours: 6:30pm – 10:00pm Notes/Handouts Demos Slack Channel. Recommended Reading. Microsoft SQL Server(TM) 2005 Administrator's Pocket Consultant

auzenne
Download Presentation

Microsoft SQL Server 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. 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. David Henson dhenson@certifiednetworks.com www.certifiednetworks.com Microsoft SQL Server Administration

  2. Logistics • Class Days: 10 • Class Hours: 6:30pm – 10:00pm • Notes/Handouts • Demos • Slack Channel

  3. Recommended Reading • Microsoft SQL Server(TM) 2005 Administrator's Pocket Consultant (Pro-Administrator's Pocket Consultant) • Microsoft Press

  4. Module 1: Overview • Course Outline • Lab Setup • Definitions • Tools • What is SQL Server • SQL Language Basics • SQL Server Network Integration

  5. Course Outline – Day 1 • Module 1: Overview • Module 2: Installation • Module 3: Objects and Securables • Module 4: Security And Principals

  6. Course Outline – Day 2 • Module 5: Backup • Module 6: Restore

  7. Course Outline – Day 3 • Module 7: Web and Email Integration • Module 8: Automation and Job Scheduling • Module 9: Transferring Data • Module 10: Replication

  8. Lab Setup • Windows Server • SQL Server 2016 • Standard, or Enterprise • Computer Names • IP Addresses

  9. SQL SMO(Formerly DMO) SQL Instance Principal Securable Definitions • Relational Database • DBA • Server • Client • Transact SQL • Login • User • Role • Database

  10. Tools • SQL Management Studio • Visual Studio .Net Current Version • SQL Server Configuration Manager • Command Prompt • SQLCMD • Ping, telnet, nslookup, net use • Database Tuning Advisor • Full-FeaturedText Editor • Notepad++(Free) • UltraEdit • Textpad • Profiler

  11. What is SQL Server? • Client Server Relational Database Operating System • Windows Service That Manages Database Files, Security and Concurrency

  12. SQL Language Basics • SELECT • INSERT • UPDATE • DELETE

  13. SQL Server Network Integration • Network oriented protocols • TCP/IP disabled on SQL Express by default • DNS • Windows Domain/Active Directory • Firewall Settings

  14. Lab 1A – Tools • In this instructor led lab, you will: • Preview SQL Management Studio • Preview Database Engine Tuning Advisor • Preview Profiler

  15. Module 2: Installation • Software Requirements • Hardware Requirements • SQL Server Versions • Licensing • Capacity Planning • SQL Services • Installation

  16. Requirements • Operating System • Windows • Linux/Mac/Docker for SQL 2017 • Hosted (AWS, Azure)

  17. SQL Server OS Versions

  18. SQL Server Licensing • Enterprise • Standard • Developer • Express • License Options • Core based • Server plus CAL (Client Access License)

  19. Capacity Planning • Drive Space • CPU • Memory • I/O Subsystem

  20. I/O Subsystem • RAID 0, 1, 5 or 10 • Logical Drive Structure • Physical Drive Structure • Considerations: • Performance • Ease and Cost of Management • Disaster Planning

  21. SQL Services • Service Names: • SQL Server (MSSQLServer) • SQL Server Agent (MSSQLServer) • SQL Server Browser • SQL Traffic director, allows Dedicated Admin Connection (DAC) • SQL Server Integration Services • SQLIS.com • SQL Server Fulltext Search • SQL Server Analysis Services • SQL Server Reporting Services • Starting/Stopping

  22. Service Accounts • LocalSystem • Local Account • Domain Account • Automatic Startup

  23. Installation • Options • SQL Server • Up to 16 instances • SQL Server Management Studio • SQL Server Data Tools (SSDT) • Administrative Rights

  24. What is an Object? • Type Of Objects: • Table • View • Stored Procedure • Trigger • DataType • Function (UDF) • Index • Constraint

  25. Synonyms • Allows an alias for a securable • New CREATE SYNONYM syntax

  26. Creating Objects • CREATE Statement • Example: CREATE TABLE Customers( CustomerID int identity, CustomerEmail varchar(50) )

  27. Altering Objects • ALTER Statement • Example: ALTER TABLE Customers ADD CustomerPhone varchar(40)

  28. Dropping Objects • DROP Statement • Example: DROP TABLE Customers

  29. Getting Metadata • Information Schema • Sysobjects table • Sp_helptext

  30. Setting Permissions • GRANT Statement • Example: GRANT SELECT ON Customers TO Public

  31. Lab 3A: Creating and Altering Objects

  32. Module 4: Security and Principals

  33. Surface Area Configuration • Best practice – reduce the “surface area” exposure of your system to minimum • Off By Default • Microsoft .NET Framework, • SQL Service Broker Network Connectivity, and • HTTP connectivity in Analysis Services • Xp_cmdshell • Ad Hoc Remote Query • Database Mail • Native XML Web Service • See Server Facets Tool in Enterprise Manager

  34. Levels of Security • Login – Server Level • User – Database Level • Object – Permissions to Access Objects • Application Security • Network Access • Encryption

  35. Windows Workgroup Model • Distributed Accounts • Each machine has locally maintained user and group database • Windows Server Not Required

  36. Windows Domain Model • Centralized Accounts • Active Directory is a single source for users and groups • Single Sign-On • Access all resources after logging in only once • Auditing

  37. Active Directory • Extension of the Domain Model • Leverages DNS Name Resolution • Better Security Protocols • Better Fault Tolerance • More Extensible • Group Policy

  38. Logins • Types: • Windows (Integrated) • SQL (Standard) • Tools • SQL Management Studio • Scripts

  39. Users • Tools: • SQL Management Studio • Scripts

  40. Roles • Role Is A Group of users • Purpose – Ease Permission Management • Role Types: • Standard • Application Role • Tools: • SQL Management Studio • Scripts

  41. Permissions • User Defined: • Action • Object Access • Object Execution • Tools: • SQL Management Studio • Scripts

  42. Lab 4A: Creating Accounts

  43. Lab 4B: Creating Users and Groups

  44. Lab 4C: Permissions

  45. Module 5: Backup

  46. Reasons for Data Loss • Accidental Update • Hardware Failure • Improper Application Design • Transactions Not Used • Other User Error • Intentional Distruction • Hackers • Viruses

  47. SQL Server Backup • Online Backup • Users can access data during backup • Types: • Full • Differential • Log • COPY_ONLY option • Does not disturb normal backups, truncate logs, or reset any flags • Use of Filegroups

  48. Increasing Backup Performance • Set appropriate BLOCKSIZE parameter • -2048 good for cdrom stored backups • With default setting, restore uses buffered io which is more flexible and slower • Stripe backup devices

  49. Restricted Activities During Backup • Modifying Database Properties • Autogrowth • Managing Indexes • Nonlogged Operations

  50. Backup Statement • Example: BACKUP DATABASE Lab3A To Disk=‘c:\Lab3A.bak’ WITH INIT, BLOCKSIZE=2048

More Related