1 / 64

Comprehensive Guide to SQL Server Administration: Databases, Security, and More

This comprehensive guide covers all aspects of SQL Server administration, including creating databases, managing security, performing backups, and utilizing key features like Query Analyzer and Enterprise Manager. Learn about database structure, logins, security options, data types, tables, constraints, stored procedures, transactions, views, user management, roles, and more. Whether you're a beginner or looking to enhance your SQL Server skills, this guide provides valuable insights and practical demonstrations to help you navigate the world of SQL Server effectively.

stinchcomb
Download Presentation

Comprehensive Guide to SQL Server Administration: Databases, Security, and More

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. Overview • What is SQL Server? • Creating databases • Administration • Security • Backup

  2. What is SQL Server? • Database management software • Own file structure • Own logins • Own security • Access through 2 main ways: • Query Analyzer • Enterprise Manager

  3. What is SQL Server? • Consists of 3 Services: • MSSQLServer • Data & query processing • SQL Server Agent • Scheduled jobs & alerts • Microsoft Distributed Transaction Coordinator • Handles data from multiple sources

  4. Logging In • Windows NT Authentication • SQL Server Authentication

  5. Query Analyzer • Command-based interface • Complete access to SQL Server • Useful for creating scripts

  6. Enterprise Manager • Graphical User Interface • Often more intuitive • Easier to visualize

  7. Viewing Available Databases • Query Analyzer • Execute sp_helpdb system stored procedure

  8. Viewing Available Databases • Enterprise Manager • Expand Databases folder

  9. Creating a Database • Enterprise Manager • Right-click Database folder • Select New Database…

  10. Demonstration • Creating medical database • Set growth and file size options

  11. Transaction Log • Records data modifications • Rolls forward completed transactions • Rolls back incomplete transactions

  12. Data Types • At least 25 data types • char, int, float • uniqueidentifier, datetime, image • Can define own data types

  13. User-Defined Data Types • Based on existing data types • Created with sp_addtype system stored procedure

  14. Creating Tables • Query Analyzer

  15. Creating Tables • Enterprise Manager • Right-click Tables • Click New Table…

  16. Creating Tables • Enterprise Manager (cont.) • Choose name

  17. Creating Tables • Enterprise Manager (cont.) • Create columns

  18. Demonstration • Creating tables for medical database • Product • Patient • Usage

  19. Constraints • Maintain data integrity • Domain Integrity • Entity Integrity • Referential Integrity

  20. Constraints (cont.) • Primary Key • Entity Integrity • Foreign Key • Referential Integrity • CHECK Constraint • Domain Integrity

  21. Add Primary Key • Query Analyzer

  22. Add Primary Key • Enterprise Manager • Right-click column of interest • Set Primary Key

  23. Add Foreign Keys • Query Analyzer

  24. Add CHECK • Query Analyzer

  25. View Constraints • Query Analyzer • Execute sp_helpconstraint system stored procedure

  26. Rules • Specify allowed values for columns • Example • Allowed states: PA, WV, OH

  27. Creating Rules • Query Analyzer • Create rule with CREATE RULE statement • Bind rule with sp_bindrule system stored procedure • Unbind with sp_unbindrule

  28. Create Rules • Enterprise Manager • Input only rule name and definition

  29. Create Rules • Enterprise Manager (cont.) • Bind rule • Open rule properties

  30. Creating Rules • Enterprise Manager (cont.) • Bind Rule (cont.) • Click Bind Columns… on Rule Properties • Select table and column

  31. Inserting Data • Query Analyzer • Use INSERT statement

  32. Inserting Data • Enterprise Manager • Right-click table name • Select Open Table ► Return all rows

  33. Inserting Data • Enterprise Manager (cont.) • Type values as needed

  34. Stored Procedures • Collection of SQL statements • Can accept variables • Can be used for security • Users can be granted right to use stored procedure, even if they do not have access to underlying tables

  35. Creating Stored Procedures • Query Analyzer • Use CREATE PROCEDURE statement • Define variables as @variable_name

  36. Using Stored Procedures • Use EXEC procedure_name • Pass values by reference or position

  37. Transactions • Process statements as a group • Must be committed • “All-or-nothing” – All statements are committed or none are • If interrupted, transaction is rolled back automatically

  38. Transactions • Query Analyzer • Use BEGIN TRANSACTION and COMMIT TRANSACTION statements • Use ROLLBACK TRANSACTION to cancel

  39. Transaction Example • Committed transaction • Rolled back transaction • Interrupted transaction

  40. Views • Specify how data is seen • Focus data • Security • Grant permissions on Views

  41. Creating Views • Query Analyzer • Use CREATE VIEW statement

  42. Creating Views • Enterprise Manager • Add tables

  43. Views • Enterprise Manager (cont.) • Select column names • Enter Aliases

  44. Security • User Management • Roles • Logins • Users • Program Security

  45. Roles • Operations that members of role can perform • Users are assigned roles

  46. Creating Roles • Type Role name • Select Standard Role

  47. Creating Roles • Open Role properties • Click Permissions button

  48. Creating Roles • Set allowed actions • Table actions • Allowed Views • Allowed Stored Procedures

  49. Logins • Allow users to log in to SQL Server • Defined under Security folder

More Related