MCDBA 70-228 Exam passing tips

MCDBA 70-228 Exam passing tips

Rating:

Installing and Configuring SQL Server 2000

• A linked server allows a user to run distributed queries that access data from multiple heterogeneous data sources stored on either the same or different computers.


A master server stores the central copy of job definitions for jobs that run on the target servers.

• The maximum file size for the audit log is fixed at 200MB.


• In order to change the collation settings, reinstall SQL Server 2000 on a server with the same collation settings as on another server.


• In order to install two instances of SQL Server on the same computer, install the first instance as a default instance and the other as a named instance.


• Collation controls the physical storage of character strings in SQL Server.


• To run the SQL Server Upgrade Wizard, a DBA must have a default instance of Microsoft SQL Server 2000 installed on a computer.


• The Named Pipes protocol is used during the upgrade of SQL Server 7.0 to SQL Server 2000.


• In order to install SQL Server 7.0 after SQL Server 2000 has been installed, following steps should be taken:
  
  
  1. Perform a complete reinstallation of SQL Server 7.0.
  
  
  2. Perform a complete uninstall of SQL Server 2000.
  
  
  3. Restore the database that are backed up in SQL Server 7.0.


• If a table or a view exists in another database on the same SQL 2000 Server, use a fully qualified name using database.owner.object_name.


• A DBA should create a linked server and use the OPENQUERY statement to run the distributed queries.


• The @provstr argument of the sp_addlinkedserver stored procedure is used to specify an OLE DB provider-specific connection string.


• Double hop is a process in which a user connected to an instance of SQL Server can run a distributed query on another instance of SQL Server.


• In order to configure a linked server and map a login, execute the sp_addlinkedserver system stored procedure to establish the linked server and the sp_addlinkedsrvlogin system stored procedure to map the login.


• In order to check the growth of a database, a DBA should configure Microsoft Windows System Monitor to log disk space utilization.


• To ensure that e-mail messages are sent to an operator whenever a job fails, a DBA should configure SQLAgentMail to use a valid MAPI profile.


• System Monitor is a Windows utility, which can be configured to monitor various activities including processor utilization and available disk space.


• Multiple instances of SQL Server 2000 can run on one computer. The computer can support a default instance of SQL Server and additional named instances of SQL Server.


• SQL Server 2000 supports secure socket layer (SSL) encryption to encrypt all data transmitted between an application computer and a SQL Server instance on a database computer.


• In order to implement the Windows account policy on SQL Server for database users, a System Administrator should configure Windows Authentication for SQL Server and create a login for the Domain Users group.


• Members of the db_accessadmin role can assign permissions to other users to enable them to access and query a database.


• RAID-1 is a type of RAID (Redundant Array of Independent Disks) for standardizing and categorizing fault-tolerant disk systems by using disk mirroring. In mirroring, data is kept on two physical disks.


• Hardware partitioning is used to design a database to take advantage of available hardware architecture.


• In order to know the cause of the slow authentication, System Monitor tool should be used.

• To improve database performance, place files and filegroups across multiple disks, multiple disk controllers, or RAID systems.


• RAID (redundant array of independent disks) is a disk system that uses multiple disk drives to provide higher performance, reliability, storage capacity, and lower cost. Windows NT/2000 supports RAID level 0, 1, and 5.
  
  
  • RAID 0, the stripe set , provides enhanced performance, but it does not provide fault tolerance.
  
  
  • RAID 1, the mirror set, creates a replica of data from one hard drive to another and provides fault tolerance.
  
  
  • RAID 5, the stripe set with parity, is the same as the stripe set, but it also provides fault tolerance.


• TORN_PAGE_DETECTION is a recovery option, which allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages. By default, this option is enabled for the database.


• SQL Server 2000 failover clustering increases server availability by allowing a server to automatically switch the processing for an instance of SQL Server from a failed server to a working server.


• If a database snapshot is present on the same computer on which the database is present, the database cannot be deleted.


• RAID-0 provides best performance and no data redundancy.


• The NORECOMPUTE argument of the UPDATE STATISTICS statement disables the auto-update feature.


• The detach/attach processes are used to transfer a database from one server to another.


• In order to send a copy of the msdb database, a DBA should configure the job to backup the msdb database to a temporary file and send the file as an e-mail attachment.


• The stored procedure sp_detach_db detaches a database from a server. The syntax of using the stored procedure is as follows:
  
  sp_detach_db [ @dbname = ] 'dbname'
  [ , [ @skipchecks = ] 'skipchecks' ]



• When a DBA tries to detach a system database, SQL Server 2000 produces error number 7940 because the master, model, msdb, and tempdb system databases cannot be detached.


• In order to prevent users from modifying the data for certain tables, a DBA should create a read-only filegroup and include the tables in the filegroup.


• To enhance query performance, a DBA should take the following actions:
  
  
  • Create a new filegroup on a new hard disk.
  
  
  • Drop the existing nonclustered indexes and re-create them on the new filegroup.


• The NOLOG clause is used to remove the inactive part of the log without taking a backup.


• In order to create objects on the SECONDARY filegroup without specifying the filegroup with the CREATE statement, a DBA should set the SECONDARY filegroup as the default filegroup.


• NOLOG and TRUNCATEONLY are synonymous.
.

• Filegroups are designed for optimizing system performance by placing tables on one filegroup and indexes on another.


• File size can be defined in KBs (kilobytes), MBs (megabytes), GBs (gigabytes), or TBs (terabytes). By default, file size is defined in MBs.


• When the Auto shrink database option is OFF, truncating log does not reduce the size of the log file but marks all the log data before the last checkpoint as inactive. Shrinking the log file removes the inactive part of the log file and reduces its size.


• Microsoft SQL Server 2000 supports multiple collations.


• The SQL Server: Databases: Percent Log Used counter is used to display the space used by the transaction log.


• A trigger is a special kind of stored procedure that executes automatically when UPDATE, INSERT, or DELETE statement executes.


• Creating triggers to repopulate full-text indexes will repopulate the indexes with every update, delete, or insert of data. This will increase overhead on resources and deteriorate performance.


• The ON UPDATE CASCADE constraint specifies that if an attempt is made to update a primary key value in a row, the value for the foreign key referencing the key column will also be updated with the same value.


• A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.


• A fill factor to reserve a percentage of free space on each leaf level page of the index.


• The UNIQUEIDENTIFIER datatype stores a 16-byte binary value (GUID) that is unique even between different databases.


• A stored procedure can be used to implement business logic or as a security mechanism, but it is not executed automatically.


• INSTEAD OF triggers are created on a view in order to make the view updatable.


• In order to create a new table with a different name that contains the same data of an existing table, a DBA will use the SELECT INTO statement.


• The IDENTITY property is used to generate automatic numbers for a column.


• In order to modify CHECK constraint, use the ALTER TABLE statement.


• The ENCRYPTION argument encrypts the entries in the sys.syscomments catalog that contains the text of the CREATE VIEW statement. This argument is used to prevent the replication of a view when the database is replicated.


• The INSTEAD OF trigger is fired even if an operation fails.


• When a stored procedure is executed by using the EXECUTE statement, permissions related to the statement are checked in the context of the user who executes the procedure.


• The CHECK constraint is used to enforce domain integrity.


• A datatype for a column cannot be changed if the column is set to primary key.


• In order to defragment index while the database is online, DBCC INDEXDEFRAG should be used.


• The UPDATE statement is a Data Manipulation Language (DML) statement, which is used to change the values of specified columns in one or more rows in a table or a view.


• If the HAVING clause is used without the GROUP BY clause, it acts as a WHERE clause. This clause can be used to search records based on aggregates.


• In order to use LEFT OUTER JOIN following syntax should be used:
  SELECT * FROM table1 LEFT OUTER JOIN table2 ON tabel1.primary_key_column_name=table2.foreign_key_column_name


• The DEFAULT constraint is a constraint in which a default value is given to a column if the value for that column is unknown. If a user does not provide any value for that column, the default value is automatically inserted. If a default value is not provided, then NULL is inserted. If a column does not allow NULL value and a default value is also not assigned for that column, an error is sent by the database engine.

• When Auto update statistics is set to ON, existing statistics are automatically updated when the statistics become out-of-date because data in the tables has changed.


• Full-text catalogs and indexes are not stored in the database to which they belong. The catalogs and indexes are managed separately by the Microsoft Search service. This is why rebuilding should be done after restoring the database.


• In order to improve query performance after a large amount of data is added to the database, a DBA should update the database statistics.


• Indexes are not automatically created for the foreign key columns. Adding indexes to the foreign key columns accelerates the query response time.


• Index can be used to gain fast access to specific information in a database table.


• A large number of indexes on a table affect the performance of the INSERT, UPDATE, and DELETE statements because all indexes must be updated as data in the table changes.


• A standby server is a secondary server that can be brought online if the primary database server fails.


• The SQL Compilations/sec counter indicates the number of times the compilation occurs.


• Normalization is the method used to optimize databases.


• The DBCC SHOWCONTIG statement displays fragmentation information for the data and indexes of the specified table and helps a user to determine whether the table or index is heavily fragmented.


• The easiest way to defragment a table is to rebuild the table's CLUSTERED index.


• The number of locks placed by database is reduced if UPDLOCK is used, as it does not block other users from reading the same data.


• The READ UNCOMMITTED isolation level specifies that data modified by other transactions can be read by the statements in the current transaction though the modifications are not yet committed. This isolation level does not issue shared locks.


• The default file name extension for the primary data file is .mdf. The default file name extension for the secondary data file is .ndf.


• A filegroup is a logical grouping of database files. Filegroups are used explicitly for placing database objects onto a particular set of database files. This improves performance and simplifies the backup procedure.


• The sp_helpdb stored procedure provides information about all files that make up a particular database.


• Creating three filegroups, FGA, FGB, and FGC on separate disks and placing tables and indexes on separate filegroups will provide the best performance.


• Partitioning a database improves performance and eases maintenance by splitting a large table, either vertically or horizontally, into smaller individual tables.


• The sp_depends stored procedure is used to retrieve information about the dependencies of database objects.


• Backup utility is used to take backup of the database, transaction log files, or one or more files or filegroups.


• In order to recover as much data as quickly as possible and to minimize server downtime, a DBA should take the following actions:
  
  
  1. Back up the transaction log.
  
  
  2. Restore the entire database to a new location.
  
  
  3. Restore subsequent transaction logs in sequence.
  
  
  4. Use the STOPAT option to restore the final transaction log backup.
  
  
  5. Import the deleted data from the new location.


• In order to recover as much data as possible, a DBA should take the following actions:
  
  
  1. Backup the transaction log by using the NO_TRUNCATE option.
  
  
  2. Restore the most recent full database backup.
  
  
  3. Restore the most recent differential backup.
  
  
  4. Restore all transaction logs since the most recent differential backup.


• Log shipping allows the transaction logs from one database (source) to be constantly replicated to another database (destination).


• The Full Recovery model uses database backups and transaction log backups to provide complete protection against media failure.


• A full database backup includes all the filegroups and database objects.


• Full recovery model is used to recover a database to the point of failure.


• The WITH INIT option of the backup statement is used to overwrite anything in the backup device.


• The simple recovery model is the simplest form of recovery model as it requires least administrative efforts. The backup of transaction log is not required in this recovery model. When the simple recovery model is implemented on a database, it automatically truncates the transaction log backup.


• If the database is interrupted during a backup operation, a DBA has to restart the interrupted backup after resolving the issue. To restart the backup operation, a DBA has to execute the interrupted backup statement again by specifying the RESTART clause.


• DBCC CHECKIDENT checks the current identity value for the specified table and, if needed, corrects the identity value. The syntax of using the stored procedure is as follows:
  DBCC CHECKIDENT
  ( 'table_name'
  [ , { NORESEED
  | { RESEED [ , new_reseed_value ] }
  }
  ]
  )


• Use DBCC CHECKDB with the PHYSICAL_ONLY option to check the physical structure of a page and record headers.


• The DBCC CHECKTABLE statement checks the integrity of table data, index, text, ntext, and image pages for the specified table or indexed view.


• The DBCC SQLPERF statement is used to display the statistics of SYS.DM_OS_WAITS_STATS DMV.


• The SQL Server provides Database Consistency Checker (DBCC) statements to check the physical and logical consistency of a database.


• SQL Profiler is a tool that captures events from a SQL Server. The events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem.


• The SET DEADLOCK_PRIORITY statement controls the way SQL Server resolves a deadlock.


• The sqldiag utility is used to gather and store diagnostic information and the contents of the query history trace.


• The Index Tuning Wizard suggests an optimal set of indexes for a database based on the workload file.

• A template is a valid XML document, consisting of one or more SQL queries.


• In order to implement a high level security on the predefined queries, a DBA should create a virtual directory that allows only template queries and use Windows Integrated Authentication.


• A DBA cannot create a virtual directory without specifying the physical directory.


• In order to complete the data load as quickly as possible, a DBA should allow data to be bulk copied into a single table from multiple clients in parallel using the bcp utility or BULK INSERT statement.


• Creating triggers to repopulate corresponding full-text indexes is not a good choice, as it will repopulate the indexes with every update, delete, or insert of data. This will increase overhead on resources and deteriorate performance.


• Indexes are automatically updated when data in a table is inserted, deleted, or updated. When bulk insert is done, it may slow down the computer.


• There can be two types of temporary tables, i.e., local and global. Local temporary tables have a single hash sign (#) as the first character of their names. They are visible only to the current connection for a user. Global temporary tables have two hash signs (##) as the first characters of their names. They are visible to any user after they are created.


• The DTS transaction settings can be configured in two ways: Package properties, which handle the global settings that affect transaction behavior across the entire package, and Step properties, which operate at the level of the individual task.


• An identity column in a table helps to maintain a unique numeric value.


• Data Transformation Services (DTS) provides flexibility while transferring data from one source to another.


• The Bulk Insert task provides the quickest way to copy a large amount of data into a SQL Server table or a view.


• The Bulk-logged recovery model is used to import data.


• In order to ensure that information in a DTS package cannot be read or modified by users, a DBA should set owner password for the package.


• The Always read properties from UDL file connection property specifies that the DTS package resolves connection information dynamically at run time.


• A DBA should save a Data Transformation Services (DTS) package to Meta Data Services to track changes made by the package to the database.


• Use the sp_addlinkedserver stored procedure to establish a linked server. The syntax of using the stored procedure is as follows:
  sp_addlinkedserver [ @server = ] 'server'
  [ , [ @srvproduct = ] 'product_name' ]
  [ , [ @provider = ] 'provider_name' ]
  [ , [ @datasrc = ] 'data_source' ]
  [ , [ @location = ] 'location' ]
  [ , [ @provstr = sp_addlinkedsrvlogin


• The impersonate option allows local clients to retain their username and password when accessing a remote server.


• A DBA should use the OPENROWSET function to access the database on a remote server.


• The int datatype takes 4 bytes and holds whole numbers data from -2^31 to 2^31, while the bigint datatype takes 8 bytes.


• In order to prevent any loss of data during the import operations, a DBA should use the ALTER COLUMN clause of the ALTER TABLE statement to change the data types to reflect the highest precision and scale values in the destination tables.


• The table datatype is used to store result set.


• Snapshot replication copies data exactly as they appear at one time.


• In order to configure the replication of a database, a DBA has to take the following actions:
  
  
  1. Create a single publication for all Subscribers.
  
  
  2. Enable horizontal filtering.
  
  
  3. Use pull Subscriptions.


• The Log Reader Agent first reads the publication's transaction log and identifies any modification made to data transactions that have been marked for replication. Then, the agent batch copies these transactions to the distribution database on the Distributor.


• In order to improve the performance for the snapshot replication, a DBA should configure the snapshot folder location on the alternate path and specify the pull replication.


• When a publication is enabled to support the Immediate Updating Subscribers option, a Subscriber can modify the replicated data.


• Data in columns with text, ntext, or image datatype are replicated only with snapshot replication.


• In SQL Server 2000, it is possible to store snapshot files in a location other than the default location.


• The Log Reader agent is used with the transactional replication. It is used to monitor the transaction log of each database configured for transactional replication. It copies the transactions that are marked for replication to the Distribution database.


• If a Publisher or a Subscriber contains databases other than SQL Server such as Oracle, transactional replication is the only replication that is used to replicate data.


• Replication Monitor is a tool that monitors the status of a replication. This tool provides information about the status and performance of publications and subscriptions.


• The Distributor server saves data related to replication and maintains the history of replication.


• The replmonitor database role should be applied to monitor a replication.


• The NOT FOR REPLICATION clause should be used to prevent a trigger from firing during the process of replication. In this case the trigger is replicated but not fired.

• Microsoft SQL Server authenticates users and logins through security modes. SQL Server supports two security modes:
  
  
  • Windows Authentication Mode (Windows Authentication)
  
  
  • Mixed Mode (both Windows Authentication and SQL Server Authentication)


• Configuring the SQL Server for Mixed-Mode authentication will allow users to access the server using SQL Server login name and password, even if they do not have Windows Authentication.


• The sp_grantlogin stored procedure allows a Microsoft Windows NT2000 user or group account to connect to Microsoft SQL Server by using Windows Authentication. The syntax of using the stored procedure is as follows:
  
  sp_grantlogin [@loginame =] 'login'



• The following statement is used to deny login to a user:
  
  sp_denylogin username



• In order to prevent a user from accessing a database, use the following syntax:
  sp_revokedbaccess username


• A view can be thought of as a virtual table. The data accessible through a view is not stored in the database as a distinct object.


• The sp_revokedbaccess stored procedure removes a database user account from the current database.


• In order to grant a user proper permissions so that a user can use a database, a DBA should set the database as a default database for the user.


• The sp_grantdbaccess stored procedure is used to add a Windows NT user account or group in the current database and enable it.


• A security account must be granted access to the current database before it can use the database.


• The EXEC sp_grantdbaccess 'Joe', 'JoeU' statement will add an account for Joe to the current database and give it the name JoeU.


• In order to make a user access his own database, a login should be created and the login should be mapped to the database.


• The sp_droprolemember stored procedure removes a member from a role in the current database.


• The REVOKE statement can be used to remove both, previously granted and denied permissions from users in the current database.


• Working with data or executing a procedure requires a class of permissions known as object permissions.


• Application roles restrict users to access data through a specific application only. Database permissions can be gained only by using specific applications and a user cannot logon directly to a database.


• The GRANT statement grants permissions to users on a database and its objects.


• If there are permission conflicts, the most restrictive permission takes precedence.


• The db_owner database role has full permissions on the database.


• The dbcreator server role has the create, alter, and drop permissions on the database.


• The sysadmin server role can perform any activity in SQL Server.


• If a user is not created in the database, the SQL Server login cannot access the database.


• The db_ddladmin role allows a user to issue all DDL (Data Definition Language) statements in a SQL Server database.


• Stored procedures can be used to implement business logic or as a security mechanism.


• Roles are similar to groups in Windows. Roles help in managing users by grouping them as a single unit. Permissions granted to, denied to, or revoked from a role also apply to members of the role.


• A check constraint specifies the data values or formats that are acceptable in one or more columns in a table.


• The COUNT(*) clause returns the number of rows in a specified table without eliminating duplicates.


• The REVOKE command can be used to remove both the previously granted or denied permission from a user in the current database.


• A DBA should use the RAISERROR statement to post the message to the Windows NT application log.


• The sp_helpuser stored procedure is used to display the database-level principals information.


• In SQL Server, a member of the sysadmin fixed database role is considered as the database owner (dbo).


• The database owner (dbo) is a user that has implied permissions to perform all activities in a SQL Server 2000 database.


• C2 auditing is a government specified standard that defines the security level. C2 auditing includes auditing for both successful and unsuccessful attempts to access statements and objects.


• SQL Profiler can be used for stepping through problematic queries to find the cause of the problem, finding and diagnosing slow-running queries, capturing the series of SQL statements that lead to a problem, and monitoring the performance of SQL Server to tune workloads.

Rating: