Download
sql server 2005 ch 2 n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server 2005 Ch. 2 PowerPoint Presentation
Download Presentation
SQL Server 2005 Ch. 2

SQL Server 2005 Ch. 2

63 Vues Download Presentation
Télécharger la présentation

SQL Server 2005 Ch. 2

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. SQL Server 2005 Ch. 2 Configuring SQL Server 2005

  2. Configuring Log and Data Files • Primary data file • Mandatory • Startup and catalog information • Can also contain objects and user data • Extension for file .mdf • Secondary data file • Optional • Contains objects and user data • Max secondary files 32,766 • Extension for file .ndf • Log files • Holds transaction logs • Extension for file .ldf

  3. Configuring Log and Data Files Cont. • Filegroups • Logical structures that allow DBA’s to group and manage data files • Cannot hold log files • Each database will have a default filegroup that may include the primary data file and secondary data files • Each database can have up to 32,766 user-defined filegroups that hold secondary data files

  4. Configuring Log and Data Files Cont. • Use GUI interface (SSMS) • Use create database T-SQL statement (for initial database creation) • Use alter database T-SQL statement (for changing an existing database)

  5. Configuring Log and data Files Cont. • Best Practices • Do not place data files on same drive as OS • Place transaction log files on separate drive from data files • Place tempdb database on separate dive if possible

  6. Configuring Database Mail • Allows for sending messages from SQL Server 2005 to external entities • Messages include HTML messages, query results, file attachments • Database mail uses SMTP • Does not require MAPI client (unlike older versions)

  7. Configuring Database Mail Cont. • Mail Perquisites • Database Mail must be enabled via Surface Area Configuration tool or sp_configure stored procedure • Service Broker needs to be enabled. The default database for this is msdb and is enabled by default. • Database mail needs access to SMTP Server

  8. Specifying a Recovery Model Recovery model controls how SQL Server stores transactions in the transaction log • Full recovery • Database records all transactions • Simple recovery • Database minimally logs transactions • Bulk-load recovery • Database minimally logs select into and bulk insert transactions

  9. Specifying a Recovery Model • How to configure recovery models • In SSMS • Alter Database command

  10. Configuring Server Security Principals • Choosing between Authentication Modes • Windows Authentication • Mixed Mode Authentication • Can change in SSMS • How to Configure SQL Server logins • SSMS • Create login statement

  11. Configuring Server Security Principals Cont. • Fixed Server Roles • Sysadmin • serveradmin • setupadmin • Securityadmin • Processadmin • Dbcreator • Diskadmin • bulkadmin • Add user to role • execute sp_addsrvrolemember command

  12. Configuring Database Securables • Managing Database Users • Create user • Alter user • Grant connect • Revoke connect • Managing Orphaned Users • Execute sp_change_users_login

  13. Configuring Database Securables Cont. • Manageing database roles • Db_accessadmin • Db_backupoperator • Db_datareader • Db_datawriter • Db_ddladmin • Db_denydatareader • Db_denydatawriter • Db_owner • Db_securityadmin • Creating roles • Create role • Alter role • Adding user to role • Execute sp_addrolemember

  14. Configuring Database Securables Cont • Managing schemes • Schema is a collection of database objects such as tables, views, and stored procedures • Create schema command to create schemas

  15. Configuring Encryption • SQL Server uses the service master key for encryption of the following • Linked server passwords • Connection strings • Account credentials • All database master keys • Backing up the service master key • Backup service master key and restore service master key t-sql commands • Alter service master key regenerate • Creating master keys for databases • Create master key t-sql statement

  16. Configuring Encryption Cont. • Configuring Symmetric and Asymmetric keys • Used to encrypt data in databases • Use create symmetric key and create asymmetric key t-sql command to encrypt • Configuring Certificates • Certificates are the strongest encryption mechanism • Can impact query performance • Create certificate t-sql command

  17. Configuring Linked Servers • Allows access to other databases to allow distributed queries on data • Configuring the security model • Self-mapping • Delegation • Remote credentials