170 likes | 237 Views
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
E N D
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 • Max secondary files 32,766 • Extension for file .ndf • Log files • Holds transaction logs • Extension for file .ldf
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
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)
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
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)
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
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
Specifying a Recovery Model • How to configure recovery models • In SSMS • Alter Database command
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
Configuring Server Security Principals Cont. • Fixed Server Roles • Sysadmin • serveradmin • setupadmin • Securityadmin • Processadmin • Dbcreator • Diskadmin • bulkadmin • Add user to role • execute sp_addsrvrolemember command
Configuring Database Securables • Managing Database Users • Create user • Alter user • Grant connect • Revoke connect • Managing Orphaned Users • Execute sp_change_users_login
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
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
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
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
Configuring Linked Servers • Allows access to other databases to allow distributed queries on data • Configuring the security model • Self-mapping • Delegation • Remote credentials