Installing and Configuring SQL Server 2005
- In order to change collation settings, reinstall SQL Server 2005 on a server with the same collation settings as another server.
- In order to install SQL Server 2005 on a computer on which SQL Server 2000 is already installed, install SQL Server 2005 as a named instance.
- Members of db_access role can assign permissions to other users to enable them to access and query the database.
- RAID-0, also known as disk striping, is made up of a disk set in which data is divided into blocks and spread equally in each disk. It provides best performance because data read and data write operations are not limited to a single disk, but to a set of disks. It does not provide data redundancy. Data once lost cannot be recovered.
- The Service Broker can be attached to a database by using the FOR ATTACH clause of the CREATE DATABASE statement.
- If a snapshot of a database is present on the same computer on which the database is present, the database cannot be deleted.
- In order to connect to the DAC, specify ADMIN: server_name in the Connect to the Server dialog box.
- The login information stored in the master database.
- In order to secure data that is data transmitted over a network, set the value of the ForceEncryption option on a server to Yes.
- In order to enable a user to connect to the database, a DBA should add the user to that database.
- The db_ddladmin fixed database role allows its members to add, modify, or drop objects in the database. Members of the db_ddladmin role can issue DDL statements (e.g. CREATE, ALTER, and DROP) in the database.
- Member of db_ddladmin role have the permission to execute the T-SQL statements.
- The sysadmin role allows a user to view the security log.
Optimizing the performance of database servers and databases
- Memory: Pages/sec counter is used to detect whether or not the memory bottleneck is caused by I/O overhead.
- Disk I/O bottleneck can be monitored by Physical Disk: % Disk Time and Physical Disk: Avg Disk Queue Length counters.
- The System Monitor tool is used to know the cause of slow authentication.
- The SQLServer: Buffer Manager performance object is used to check the amount of RAM used by a computer.
- The Physical Disk: %Disk Time counter measures the pressure on a physical hard disk array.
- Add more RAM if the reading of Memory: Pages/sec counter is high.
- Reduce the Maximum Worker Thread configuration settings if the value of System: Processor Queue Length counter is high.