Term
| What are the minimum hardware requirements for installing SQL Server 2008? |
|
Definition
32-Bit
1.0 Ghz PIII or Higher
512 MB RAM
64-Bit
1.6Ghz 64-bit Processor
512 MB RAM |
|
|
Term
| What is required to install the 32-bit version of SQL Server on a 64-bit OS? |
|
Definition
| Windows on Windows (WOW) must be enabled |
|
|
Term
| What OS's support ALL versions of SQL Server 2k8? |
|
Definition
Windows Server 2008 Standard or higher
Windows Server 2003 sp2 OR HIGHER |
|
|
Term
| Which OS's support Developer, Evaluation, and Express Server? |
|
Definition
Windows XP Pro SP2 or higher
Windows Vista Home Basic or higher |
|
|
Term
| Which OS's support only SQL Server Express? |
|
Definition
Windows XP Home SP2 or higher
Windows XP Home Reduced Media Edition
Windows XP Tablet SP2
Windows XP Media Center 2002 SP2 or higher
Windows XP Pro Reduced Media
Windows XP Pro Embedded Edition SP2
Windows Server 2003 Small Business R2 or higher |
|
|
Term
| Which OS does not support the .NET framework and therefore does not support SQL Server 2008? |
|
Definition
| Windows Server 2008 Server Core |
|
|
Term
| Which version of .NET framework is required to install SQL Server 2008? |
|
Definition
|
|
Term
| Which version of .NET framework is required to run SQL Server 2008? |
|
Definition
|
|
Term
| What additional software is required to install SQL Server 2008? |
|
Definition
MDAC 2.8 SP1 or higher
Shared Memory, Named Pipes, TCP/IP
IE 6 SP1 |
|
|
Term
| How do you verify the MDAC version |
|
Definition
Regedit--> HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/DataAccess/FullInstallVer key
|
|
|
Term
| What is the Service Broker Sevice? |
|
Definition
| An integrated message queuing system which provides asynchrinous data processing capabilities. (Does not require the user to wait for processimg to complete). |
|
|
Term
|
Definition
| Builds standardized reports and report models which can be distributed throughout an organization. Allows for subscriptions to automated reports |
|
|
Term
| SQl Server Analysis Services |
|
Definition
| provides OLAP and Data mining capabilities |
|
|
Term
Describe the general design objectives for each SQL server edition. |
|
Definition
Enterprise-Designed for large organizations needing full power of SQl platform
Standard-Small to midsize businesses
Workgroup-Small departmental projects
Express-Freely redistibutable desogned for embedded apps and basic storage needs for small apps
Compact-Designed as an embedded DB
Developer-Full featured and designed for developers. May not be used in production environments
Evaluation-Full featured but not allowed in production environment and time bound to 180 days
|
|
|
Term
| Describe the Hardware support (CPU, RAM, Storage) of each SQL edition. |
|
Definition
Standard/Workgroup
4 CPU
Unlimited RAM
Unlimited Size
Express/Compact
1 CPU
4GB DB Size
1 GB RAM |
|
|
Term
| Describe SSIS SUpport for each edition of SQL Server 2008 |
|
Definition
Enterprise
Data Mining
Fuzzy Lookup
OLAP Processing
Standard/Workgroup
Import/Export Wizerd
Package Designer
Express/Compact
No support |
|
|
Term
| Which editions support the entire feature set of SQL Server? |
|
Definition
Enterprise
Developer
Evaluation |
|
|
Term
| Which editions of SQL Server are designed as storage engines for embedded applications and have a limited hardware and feature support? |
|
Definition
|
|
Term
|
Definition
Controls how SQL Server treats character data for storage, retrieval, sorting, and comparison operations.
Defines the supported character set in terms of case sensitivity, accent sensitivity, and kana sensitivity.
Can be defined at the instance, database, table, and column level
|
|
|
Term
| What are the 2 authentication Modes? |
|
Definition
Integrated (Windows Only)
Mixed Mode (Windows or SQl Security)
|
|
|
Term
| Which version of SQl Server 2008 support instances? How Many? |
|
Definition
| Enterprise supports up to 50 instances. |
|
|
Term
| What is SQl Serve Configuraion Manager responsible for? |
|
Definition
Start/Stop/Pause Services
Change service accounts and passwords
Manage start up modes of services
Configuring service start up parameters
|
|
|
Term
| What 5 databases are installed by default in all SQL Server instances? |
|
Definition
Master-system level information for the instance
MSDB-Used by SQL Agent
Model-templte for new db's inthe instance
Resource-Read Only db which stores system objects
tempdb-temporary workspace |
|
|
Term
| How do you disable instance Enumeration? |
|
Definition
SQl Server Configuration Manager
SQL Server Network Configuration
Right Click Network Protocols-->Properties
HideInstance =Yes |
|
|
Term
| Instance Enumeration (Enumeration Request) |
|
Definition
| Allows SQl to broadcast it's presence on the network. Allows users to discover the presence of SQL server on the network which creates a potential security vulnerability. |
|
|
Term
| How do you change the filestream access level? |
|
Definition
Exec sp_configure 'filestream_access_level', 2
GO
Reconfigure
Go
0=disabled
1=enabled for TransactSQL
2= enabled for TSQl and file system access |
|
|
Term
| Where are all email messages logged? |
|
Definition
| MSDB and Windows Application log |
|
|
Term
What transport protocol does Database Mail use?
|
|
Definition
|
|
Term
| What are the column number and size restrictions when defining an index key on a clustered index? |
|
Definition
| No more than 16 columns or 900 bytes |
|
|
Term
| How many clustered indexes can be defined on a table? |
|
Definition
|
|
Term
| What is a forwarding pointer? |
|
Definition
| When a row of data is moved in a nonclustered index the index inserts a forwarding pointer to the new data location rather than updating the index with the new location |
|
|
Term
| What is the maximum number of non clustered indexes that can be defined on a table? |
|
Definition
|
|
Term
| What columns are included when an index is built |
|
Definition
| Only the values in the index key (16 columns/900bytes max) |
|
|
Term
| Values from included columns do not count against the 16 column/900 byte index key limitation. Where does the data from included columns appear in the index? |
|
Definition
|
|
Term
| What is the query optimizer? |
|
Definition
| Determines whether a particular index should be used to satisfy a query based on value distribution statistics |
|
|
Term
What is a filtered index?
What are the restrictions?
|
|
Definition
An index built with a WHERE clause.
Must be non-clustered
Cannot be created on a computed column
Cannot undergo type conversion |
|
|
Term
|
Definition
| An index option which specifies how much free space to leave on the leaf level during a CREATE or REBUILD operation. fillfactor=percentage full |
|
|
Term
|
Definition
| Index option which causes FILLFACTOR to be applied to intermediate pages. |
|
|
Term
|
Definition
| Index option which causes SQL to use tempdb for the intermediate tables it uses during a CREATE INDEX operation. |
|
|
Term
|
Definition
Index option
In a multi-row insert to a table containing a unique index only the duplicate roqw will be rejected rather than all the rows in the transaction. |
|
|
Term
WITH ONLINE=OFF (default)
|
|
Definition
SQL locks the table during index creation
In a non-clustered index this will allow ONLY SELECT statements
Clustrered indexes will allow no operations |
|
|
Term
What statement do you use to defragment an index?
|
|
Definition
|
|
Term
| How do you defragment an index at the leaf level only? |
|
Definition
|
|
Term
| What happens to the underlying table when a clustered index is disabled? |
|
Definition
|
|
Term
| What storage structure is used for full text indexes? |
|
Definition
|
|
Term
| Which editions of SQL server provide row and page level compression? |
|
Definition
|
|
Term
| When a able is compressed which indexes are autimatically compressed also? |
|
Definition
Clustered indexes.
Compression must be enabled seperately in non clusterd indexes |
|
|
Term
| Which editions of SQL server support compressed backups |
|
Definition
|
|
Term
| Which roles can use profiles to send email from SQL Server? |
|
Definition
| Members of DatabasMailUser |
|
|
Term
| Which Command would you use if you need to alter the index key columns or the included columns in an index? |
|
Definition
| CREATE INDEX....WITH DROP_EXISTING=ON |
|
|
Term
| What actions can not be completed using the ALTER INDEX command? |
|
Definition
Repartition Index
Move the Index to another location
Modify the index key columns
Change the Included columns |
|
|
Term
| How does disabling a clustered index impact the underlying table? |
|
Definition
| Renders it inaccessible until the index is dropped or rebuilt |
|
|
Term
| How do you determine the degree of fragmentation that exists on an index? |
|
Definition
| sys.dm_db_index_physical_stats |
|
|
Term
| what level of fragmentation as reported by sys.dm_db_index_physical_stats should prompt you to defragment or reorganize and index? |
|
Definition
|
|
Term
| what level of fragmentation as reported by sys.dm_db_index_physical_stats should prompt you to Rebuild an index? |
|
Definition
|
|
Term
Which stored procedure allows you to rename an index?
how else can objects be renamed? |
|
Definition
|
|
Term
| How do you re-enable a disabled index? |
|
Definition
|
|
Term
| How can you modify index options without rebuilding the index? |
|
Definition
|
|
Term
|
Definition
| Defines how data is stored in a data file and maintains information on where the data would belong if it were returned to a SQL Server database. (BCP) |
|
|
Term
| How do you create a format file? |
|
Definition
| bcp db.schema.table_name nul -f path\filename.fmt |
|
|
Term
When running BCP the account you are logged in as must have what permissions to do the folowing:
Import data to SQL Server
Export Data to a file |
|
Definition
Import data requires SELECT, INSERT, ALTER TABLE
Export data requires SELECT Permissions |
|
|
Term
| In order to bulk export data which tool must you use |
|
Definition
|
|
Term
| Where are objects for Policy Based Management(DMF) stored? |
|
Definition
|
|
Term
| What is the limitation on complex conditions created in the advanced editor for in Policy Based Management? |
|
Definition
| Any policy that incorporates the complex condition must be executed manually and can not be scheduled. |
|
|
Term
| What are the 4 execution modes for policies? |
|
Definition
On demand-evaluates conditions when executed by a user
On change, prevent-Uses a DDL trigger to prevent changes which violate the policy
On change, log only-Checks the policy automatically when a change is made..uses the event notification infrastructure
On Schedule- |
|
|
Term
| What two levels do policy subscriptions occur? |
|
Definition
|
|
Term
| Which property of the policy category object will allow sysadmins to require all databases within the instance to subscribe to the policy? |
|
Definition
|
|
Term
| What are the 4 types of backup? |
|
Definition
Full-Backs up all data pages
Differential
Transaction Log
Filegroup |
|
|
Term
| What is the limiting factor for the speed of a backup? |
|
Definition
| The device to which data is being written |
|
|
Term
| What operations are NOT allowed during a full backup? |
|
Definition
Add or remove a DB file
Shrinking the DB |
|
|
Term
| Which two parameters are required for a backup command? |
|
Definition
DB Name
Backup Device name |
|
|
Term
| What is the max number of backup devices you can stripe daa across? |
|
Definition
|
|
Term
| How many copies of a backup can you create in one operation? What command would you use? |
|
Definition
| 4 copies usinig the MIRROR TO option |
|
|
Term
| What are the requirements when using the MIRROR TO clause in a backup? |
|
Definition
All devices same media type
Each mirror uses same number of backup devices
WITH FORMAT option must be specified |
|
|
Term
| How can you save time during a backup? |
|
Definition
| Compress the backup tpo reduce the amount of data written to disk. SQL Server 2008 only |
|
|
Term
| When CHECKSUM is speified during a backup , what is the default behavior when an error is encountered? |
|
Definition
| STOP_ON_ERROR..The backup terminates with an error |
|
|
Term
| What are the disadvantages to a simple recovery model? |
|
Definition
| T-log is not backed up so the database can only be restored to the most recent backup |
|
|
Term
| What is the disadvantage to a bulk logged recovery model? |
|
Definition
| It only support restoring complete backups. It is intended as an adjunct to Full recovery model and should only be used duriing bulk logged operations to reduce transactionlog activity. Switch back to Full recovery model immediately after bulk logged operations complete |
|
|
Term
| When using simple recovery how do you ensure minimal data loss between full backups? |
|
Definition
| Differential backups capture all extents that have changed since the last full backup. Reduces the number of transaction log backups are required to restore data after the full backup. |
|
|
Term
| Wha is the purpose of a filegroup backup? |
|
Definition
| Full backups capure all the pages across the database. USe filegroup backups to backup portions of a database to save time and space. |
|
|
Term
| Some filegroups may be marked read-only and therefore do not need to be constantly backed up. What option allows yo to only back up filegroups not marked as read-only? |
|
Definition
| BACKUP DATABASE READ_WRITE_FILEGROUPS TO... |
|
|
Term
| What is the limit in the number of corrupt pages in a database before the database goes offline? |
|
Definition
| At 1000 pages SQL Server takes the database offline and places it in a suspect state |
|
|
Term
| What is the purpose of a maintenance plan? |
|
Definition
| Provides a mechanism to graphically create job workflows that support common administrative tasks such as backups, shrinkin g databases, re-indexing, updating statistics, performing consistency checks |
|
|
Term
| What is a service master key? |
|
Definition
| Created automatically the first time an instance is started. It is the key used to encrypt all other keys below it. It should be backed up immediately following any change to the service account or service account password. |
|
|
Term
| What is a Database Master Key? |
|
Definition
| Created prior to the creation of a certificate, or key. It is the root of the database encrytion hierarchy. |
|
|
Term
| What must you do prior to backing up the DMK? |
|
Definition
| OPEN MASTER KEY DECRYPTION BY PASSWORD
BACKUP MASTER KEY TO FILE='path to file' ENCRYPTION BY PASSWORD='password' |
|
|
Term
| Why should you not store Master key and certificate backups with data backups? |
|
Definition
| BAckups should be stored offsite which poses a risk of them being compromised. Master key/certificates are required to decrypt encrypted backups. Storing them is a different location helps ensure the security of off site backups |
|
|
Term
| How do you verify the validity of a backup? |
|
Definition
|
|
Term
|
Definition
| Log sequence Number. Starts at 0 and increments to infinity. Never repeats and cannot be reset to a previous value. Each operation that affects the state of the db increments the LSN |
|
|
Term
| Which roles allow you to create a backup? |
|
Definition
| SYSADMIN fixed server role or db_owner or db_backupoperator fixed database roles. |
|
|
Term
| How do you create a logical device name for a physical backup device? |
|
Definition
| EXEC sp_addumpdevice 'disk|tape' 'logical name' 'c:\pathtodevice\backupfile.bak' |
|
|
Term
| How can you view all logical devices tat have already been created? |
|
Definition
| SELECT * from sys.backup_devices |
|
|
Term
Which ports should the network administrator configure the firewall to forward communication to the default instance of SQL Server?
Which ports do named instances listen on? |
|
Definition
TCP 1433/UDP 1434
Named nstances listen on dynamically assigned ports |
|
|
Term
| What should you to to optimize query performance against a table containing large amounts of unstructured data or formatted binary data (i.e.Word Documents)? |
|
Definition
| Create a full text index. |
|
|
Term
| What column types support full text indexes? |
|
Definition
| Char, varchar, nvarchar, image, varbinary(max) |
|
|
Term
| A DB admin notices that the msdb database has grown much larger than expected. He notices that the growth is due to the large amount of database mail messages being sent. How do you delete messages older than 30 days? |
|
Definition
| msdb.dbo.sysmail_delete_mailitems_sp @sent_before |
|
|
Term
| what roles have permissions to execute sysmail_delete_mailitems_sp? |
|
Definition
sysadmin fixed server role (all emails)
DatabaseMailUserRole (owned emails) |
|
|
Term
| In a slow WAN LAN or dial up network what is the preferred network protocol? |
|
Definition
|
|
Term
| Which network protocol offers more functionality, ease of use, and more configuration options when network speed is not an issue? |
|
Definition
|
|
Term
| Which feature integrates applications that use SQL Server with the NTFS file-system? |
|
Definition
|
|
Term
| How do you enable FILESTREAM on a SQL Server instance? |
|
Definition
SQL Server Configuration Manager-->Instance--> Properties-->Filestream Tab
AND
sp_configure filestrea_access_level <0|1|2> |
|
|
Term
| which service must be enabled to utilize the Volume Shadow Copy Serve (VSS) framework for backups? |
|
Definition
|
|
Term
| If a service must interact with network services, access domain resources, or use linked server connections what kind of service account should be used? |
|
Definition
| Minimally privileged domain user account |
|
|
Term
| If a server is not part of a domain what kind of service account should be used? |
|
Definition
| Local user account without administrator privilege |
|
|
Term
| NT\AUTHORITY local service account should be used for both the SQL Server and SQL Server Agent service account T or F |
|
Definition
| False local service account is not supported for the SQL Server or SQL Server Agent services. |
|
|
