1 / 17

SQL Server 2005 Ch. 2

SQL Server 2005 Ch. 2. Configuring SQL Server 2005. 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

nhung
Download Presentation

SQL Server 2005 Ch. 2

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. 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

More Related