Posted on by uCertify

MCITP 70-444 Short Notes: Exam passing Tips

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.

    • Pass Microsoft MCITP Database Administrator Certification in first attempt.

  • In order to troubleshoot instance performance, run the SQL Trace system stored procedures to gather database activity, create a trace in a template by using SQL Server Profiler.
  • SQL Server Profiler can be used to monitor the queries, which are running slow.
  • Template is used to define a default configuration for a trace.
  • The permissions necessary for executing a DMV are SELECT, VIEW SERVER STATE, OR VIEW DATABASE STATE.
  • A user assigned to the sysadmin role can connect to a database using the Dedicated Administrator Connection.
  • SQL Server Configuration Manager and Surface Area Configuration Manager tools are used to configure and enable database connection.
  • The Database Engine Tuning Advisor tool is used to align the partitioned indexes.
  • Run the SQL Server Profiler and trace server activities to gather information about the resources used by SQL Server.
  • The SQL Server database engine requires the SQL Server Agent service to perform jobs, alerts, or operators. If any of them is not performing, it indicates that the service is not running.
  • In order to connect to the DAC, specify ADMIN: stSQL in the Connect to the Server dialog box.
  • The SQL Server Profiler tool is used to create a trace.
  • Database Engine Tuning Advisor tool is used to tune indexes.
  • The SQL Server: Transactions: Free Space counter is used to monitor space in tempdb database.
  • The SQL: StmtRecompile event class is used to monitor which stored procedure is the cause of slow performance.
  • The Surface Area Configuration Manager and tools are used to configure network library.
  • Server Browser service is a tool used to connect to an SQL Server instance if a DBA is not able to connect to that instance through DAC. It can be configured either during the installation of SQL Server or by using the Surface Area Configuration Manager tool.
  • The Deadlock graph event group displays a graphical representation of those tasks and resources that are involved in a deadlock.
  • The TSQL_SPs template is used to capture detailed information about a stored procedure. If the DBA suspects that the stored procedure Proc1 compiles every time before its execution, the SP:RECOMPILE event can be added.
  • Hardware petitioning is used to design a database to take advantage of available hardware architecture.
  • The SP_AUTOSTATS stored procedure is used to display and change the auto-update settings for tables, views and indexes in the current database.
  • The methods of defragmenting indexes are as follows:
    • Drop and recreate indexes
    • CREATE INDEX…WITH DROP EXISTING
    • DBCC DBREINDEX
    • DBCC INDEXDEFRAG
  • The indexes missing after defragmentation are created by using the CREATE INDEX statement.
  • The Activity Monitor tool should be used to retrieve information about locks.
  • The transformation of a query from a high-level language to a low-level language is called query processing. It is a stepwise process in which a query is created in a high-level language. A parser parses the syntax of this query. The parser verifies relations, attributes, and objects, etc., used in the query. The query is then optimized by transforming it into equivalent expressions that can be executed more efficiently. An execution plan is then created to evaluate the transformed query and the output is generated.
  • The Average Wait Time counter is used to view the average amount of wait time for each lock request that resulted in the failure of a task to complete.
  • 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.
  • The NORECOMPUTE argument of the UPDATE STATISTICS statement disables the auto update feature.
  • The DBCC INDEXDEFRAG is the process of defragmenting index that can be done while the database is online.
  • The SQL Server: Databases: Percent Log Used counter is used to display the space used by the transaction log.
  • Run Activity Monitor tool is used in order to get the information about the users and the locks they hold.
  • The DBCC SQLPERF statement is used to display the statistics of SYS.DM_OS_WAITS_STATS DMV.
  • The sys.dm_exec_query_stats DMV is used to display the aggregate values of performance statistics for query plans.
  • The SET SHOWPLAN_TEXT ON statement is used to display the information about the queries without executing them. It displays the information in the form of simple text.
  • The TSQL_Duration template is used to capture all the SQL statements and their execution time. It groups the SQL Statements by duration.
  • The ENCRYPTION argument is used to prevent the replication of a view when the database is replicated.
  • Query Editor is a component in SQL Server Management Studio (SSMS). It is used to view queries and stored procedures.
  • The Start tracing immediately after making connection check box is checked to create a trace successfully.
  • In order to perform the search operation in a database, use the WHERE clause with the SELECT statement.
  • The SYS.DM_DB_INDEX_PHYSICAL_STATS DMV is used to view the information about the fragmented indexes.
  • The WITH SCHEMABINDING clause safeguards a view definition against any structural modification of the underlying table.
  • The entity integrity is enforced by using the PRIMARY KEY constraint.
  • FILLFACTOR specifies a percentage that indicates how much free space will be in the leaf level of each index page.
  • The clustered index is used to sort the data in a table according to the column on which the clustered index is defined.
  • FN_TRACE_GETTABLE system function reads all the rollover files until it reaches to the end of the trace. It returns a table with all the valid columns for a specified trace.
  • In order to create an XML index that is based on the node value and path columns from the primary index, you will create an XML VALUE secondary index.
  • In order to remove fragmentation, rebuild the indexes.
  • The ORDER BY clause is used to sort data in more than two databases having different collation settings.
  • The Full-text index is used to search for a word in a table.
  • In order to defragment an index, use the DBCC INDEXDEFRAG statement.
  • In order to optimize the performance of the slow-running stored procedure, you will have to execute the sp_recompile system stored procedure for each of the slow-running procedures.
  • In order to ensure that the indexes are up-to-date, update the statistics of the indexes on each table.
  • The SET TRANSACTION ISOLATION LEVEL statement is used to set isolation levels to control locks. The arguments of this statement are Read Committed, Read Uncommitted, Serializable, Snapshot, and Repeatable Read.
  • The locks and deadlocks can be monitored through the System Monitor and the SQL Server Profiler tool.
  • The READ UNCOMMITTED isolation level is the cause of dirty read.
  • If a DBA wants to allow other users to read data that has not yet been committed, the READ UNCOMMITTED isolation level should be used.

Optimizing and implementing a data recovery plan

  • The size of the database can be increased by increasing the value of the MAXSIZE argument or by enabling the auto-grow setting by using the ALTER DATABASE statement.
  • If a DBA has to take a backup of a database and also wants to make the database online, Database Mirroring should be used.
  • The SP_ADD_JOBSERVER stored procedure is used to set the server for the job.
  • The Service Broker can be attached to a database by using the FOR ATTACH clause of the CREATE DATABASE statement.
  • When the transaction log is full, users are unable to update data in a database.
  • Disk Management is a folder in the Computer Management console used to view the volume status. It displays the currently active volumes on the server. It also displays the active and inactive drives connected to the server.
  • Services Console is a tool used to determine the services that have not started though they are configured to start automatically. It generates a list of service name, status, startup type, an account used by a service to log on.
  • The Service Broker tool is used to establish communication between two databases.
  • The ALTER DATABASE statement is used to enable service broker in an existing database.
  • The different database mirroring roles are Principal, Mirror, and Witness Server.
  • 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.
  • In order to perform log shipping following steps are to be taken:
    • Back up the transaction log at the primary instance.
    • Copy the transaction log file to the secondary instance.
    • Restore the log backup on the secondary server instance.
  • Database Mirroring allows a user to maintain an exact copy of a database on a different server. It is used to increase database availability. It is implemented on a per-database basis and works only with databases that use the full recovery model.
  • In order to transfer the backup of a transaction log from a database server to another database server, log shipping is used.
  • The master database is a system database that stores system level information such as login accounts, linked servers, and system configuration settings. It also stores information about initialization of SQL Server and other databases.
  • Full database backup includes all the filegroups and database objects.
  • File backup is used to recover the damaged files in a database. It is useful when the database contains several files on different volumes.
  • 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. The database can be recovered only to the point it was last backed up. This model is appropriate for the read-only databases.
  • The SP_SPACEUSED stored procedure displays the number of rows present in a table or indexed view in the current database. It also displays the space used by a Service Broker queue and the disk space reserved by the whole database.
  • The DBCC SQLPERF stored procedure is used to determine the space used by the transaction log.
  • The full recovery model is used to recover a database to the point of failure.
  • The READ_WRITE_FILEGROUPS clause excludes the backup of read-only file groups.
  • The bulk-logged recovery model prevents the transaction log from becoming full.
  • In order to mirror a database, configure a database with the Full recovery model.
    • Back up the database.
    • Restore the database on another server.
    • Copy all the system objects to an instance that hosts the mirror database.
  • The WITH INIT option of the backup statement is used to overwrite anything in the backup device.
  • Bulk-logged recovery model is used to import data.
  • In order to create a backup device, use the Server Objects folder.
  • The BACKUP DATABASE statement is used to take the backup of a specified database.
  • The undo phase of the restoration process rolls back the uncommitted transactions.
  • The piecemeal restoration process is used to restore damaged files.
  • The STOPAT argument of the RESTORE statement is used to restore the database at a specific point in time.
  • If a snapshot of a database is present on the same computer on which the database is placed, the database cannot be detached.
  • In order to create a database snapshot following syntax should be used:
    CREATE DATABASE snapshot name ON (NAME=logical_file_name, FILENAME=’os_file_name’) AS SNAPSHOT OF database_name
  • In order to restore database from a database snapshot, following syntax should be used:
    RESTORE DATABASE database_name FROM database_snapshot
  • The operations performed on the execution of the DBCC CHECKDB statement is as follows:
    • DBCC CHECKALLOC
    • DBCC CHECKTABLE
    • DBCC CHECKCATALOG
  • The DBCC CHECKDB statement is used to repair a damaged database.
  • The REPAIR_REBUILD option of the DBCC CHECKDB statement repairs database without any data loss.
  • In order to repair the database, DBCC CHECKDB will be executed.

Designing a strategy to monitor and maintain a database solution

  • The Memory: Available Bytes counter measures the amount of free physical memory on the server. The average value of the counter should be greater than 5MB, otherwise the server experiences a performance hit due to memory pressure.
  • The Network Interface: Bytes total/sec counter should be used to monitor connectivity problem.
  • The SQLServer:Databases: Percent Log Used counter is used to monitor the space used by a transaction log.
    • Pass Microsoft MCITP Database Administrator Certification in first attempt.

  • The DBCC_TRACESTATUS stored procedure is used to view the status of a trace flag.
  • Security log is used by SQL Server to record security events, such as failed login attempts. It can be used to track security breaches and any changes that might be made to the security system. Only those users who are members of the sysadmin fixed server role can view the security log.
  • The SP_HELP_JOB stored procedure is used to retrieve information about a particular job or all the jobs in a database.
  • The SP_ADD_JOB stored procedure is used to create a new job to be executed on the server. After a job is created, various job steps are defined for the job by using the SP_ADD_JOBSTEP stored procedure. The schedule for the job is created by using the SP_ADD_SCHEDULE stored procedure. The schedule is then attached to the job by using the SP_ATTACHED_SCHEDULE stored procedure. The job is set to run on the server by using the SP_ADD_JOBSERVER stored procedure.
  • The SQL Server Agent tool is used to run a job automatically at a specified time.
  • An alert job is created on the monitor server and generates an error if the backup has not been completed successfully.
  • The RAISEERROR() statement is used to write an error to the Windows application log.
  • The SYS.DM_EXEC_QUERY_STATS DMV is used to check the performance of the queries.
  • The sysadmin role allows a user to view the security log.
  • The SYS.DM_OS_WAIT_STATS DMV is used to find out the statistics of waits.
  • The Surface Area Configuration Manager tool is used to start the SQL Server Agent service automatically.
  • The SQL Server service is used to enable user to connect to a database.
  • The SQL Server Management Studio tool is used to create database diagram.
  • The steps of applying an update are as follows:
    • Perform a full backup of the operating system. Perform the backup by using the Windows backup utility. The backup of the operating system includes system state data.
    • Take a full Automated System Recovery backup by using the Windows backup utility.
    • Take a full backup of all the databases. The backup includes all the user and system databases.
  • A Database diagram is used to view complex relationships between tables.
  • In order to create an update trigger, use the following script:
    CREATE TRIGGER MyTrig
    on SalesData
    AFTER UPDATE
    AS
    BEGIN
    DECLARE @wt INT
    DECLARE @rt INT
    DECLARE @tot INT
    SELECT @wt=Weight FROM SalesData
    SELECT @rt=Rate FROM SalesData
    SELECT @tot=@wt*@rt
    SELECT @tot
    END
    GO
  • The NOINDEX argument is used to specify that the non-clustered indexes in user tables are not checked during the execution of the DBCC CHECKDB statement. The NOINDEX argument does not affect system tables.
  • The SP_ADDUMPDEVICE stored procedure is used to create a backup device.
  • The Shrink database maintenance task is used to reduce disk space.
  • In order to make failover clustering run automatically through Database Mirroring, set up a new server and configure it as a witness for database mirroring.
  • In order to create a linked report follow the following steps:
    • Open Internet Explorer.
    • Open Report Manager by navigating to localhost/reports.
    • Click the report for which linked report is to be created.
    • Click the Properties tab.
    • On the General Properties page, click Create Linked Report.
    • Enter the name and description for the linked report.
  • The On Demand option is used to view a new instance of report with updated data.
  • The BIDS tool is used to create reports.

Designing a database data management strategy

  • The ServerStorage argument is used to save an entire package in a SQL Server database. It is supported only when the package is saved in the MSDB database.
  • In MSDB database, a package should be saved to secure it.
  • The db_dtsoperator fixed database role has only the read permissions on SSIS packages.
  • The user-defined roles are added to the MSDB database.
  • In order to restart the package from the point of failure, restart the package using the package checkpoint.
  • The SaveCheckpoints property is mandatory to restart a failed package.
  • The dtutil command prompt utility is used to move an SSIS package from one server to another.
  • The SSMS, BIDS, Command Prompt tools are used to start Import/Export Wizard.
  • Windows administrators use the dtexec utility to schedule task for SSIS packages.
  • The db_dtsadmin, db_dtsltduser, and db_dtsoperator roles are used to secure an SSIS package.
  • A view is a type of virtual table. The data accessible through a view is not stored in the database as a distinct object. Defining a SELECT statement creates views. The result set of the SELECT statement forms the virtual table.
  • The SCHEMABINDING argument of the CREATE VIEW statement is used to prevent the deletion of a table for which a view is created.
  • In order to use the result of a query with the other SELECT statements, you create a table-valued function that accepts parameters as input and provide result with the return statement.
  • The SQL Server Integrated Services (SSIS) service is used to merge data from heterogeneous data sources into SQL Server database.
  • The LEFT OUTER JOIN is used as follows:
    SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.primary_key_column_name=table2.foreign_key_column_name
  • 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.
  • 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 view.
  • In order to modify the CHECK constraint, use the ALTER TABLE statement.
  • The CHECK constraint is used to enforce domain integrity.
  • The UNIQUE constraint does not allow duplicate values. It can be implemented on multiple columns in a table.
  • When Primary key constraint is defined on a column, the datatype of the column cannot be changed.
  • The Queue Reader replication agent checks the conflicts during the replication.
  • The Keep The Publisher Change conflict resolution policy maintains the data consistency on the publisher.
  • The FOREIGN KEY constraint is used to apply referential integrity.
  • 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.
  • The AllowNulls property should not be set to Yes while assigning a PRIMARY key.
  • A constraint should be applied on a table to enter valid values in the table.
  • The table datatype is used to store result set.
  • The model database is used to save an alias datatype so that it can be used by user databases.
  • The >@provstr argument is used to provide a connection string in the sp_addlinkedserver stored procedure.
  • 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.
  • The sysadmin server role avoids the configuration of permission settings.
  • The DisallowAdhocAccess setting is used in the OLE DB Provider dialog box to restrict nonadministrators to execute queries using the OPENROWSET and OPENDATASOURCE functions.
  • Only the transactional replication can be applied if a Publisher or the Subscriber server contains the database other than SQL Server such as Oracle.
  • The Log Reader agent is used to monitor the transaction log of each database that is configured for transactional replication. It copies the transactions that are marked for replication to the distribution database.
  • The Distributor server saves the data related to replication.
  • 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 replmonitor database role should be applied to monitor a replication.
  • The transactional replication copies only modified data to other server.
  • In order to prevent the trigger from firing during the process of replication, NOT FOR REPLICATION clause should be used.
  • The Central Publisher is a replication model in which the Publisher and the Distributor roles are maintained on the same SQL Server 2005 computer.
  • A user should be assigned to the sysadmin fixed server role to monitor replication.
  • The transactional replication supports tracer tokens that validate the connection between Publisher and Distributor, the Distributor and Subscribers. The tracer token is a facility in the Replication Monitor tool that is used to measure latency. It is a record sent by the Distributor database to the Subscriber database.
  • The sp_help_agent_profile stored procedure displays or removes the profile of a specified agent.
  • In order to use pull subscription, Run Distribution agent and Merge agent at the Subscriber.
  • DML triggers are executed when insert, update, or delete operations are to be performed through triggers on a table.
  • In order to secure data that is data transmitted over a network, set the value of the ForceEncryption option on a server to Yes.
  • Disable the trigger to stop it from firing without dropping it.
  • The AFTER trigger should be fired after database engine completes all the actions.
  • The INSTEAD OF trigger is fired even if an operation fails.
  • The sp_depends stored procedure is used to retrieve information about the dependencies of database objects.
  • The local temporary stored procedure is created by prefixing the # symbol before the procedure name. The connection that created the stored procedure can execute it. The stored procedure is deleted when the connection is closed.

Designing a strategy to manage and maintain database security

  • Asymmetric encryption is a type of encryption that uses two keys – a public key and a private key pair for data encryption. The public key is available to everyone, while the private or secret key is available only to the recipient of the message.
  • The secedit command is used to analyze the security of a server.
  • The Microsoft Baseline Security Analyzer (MBSA) tool is used to check for security updates.
  • In order to use MUST_CHANGE argument within the CREATE LOGIN statement, CHECK_POLICY and CHECK_EXPIRATION arguments are set to on.
  • In order to decrypt a symmetric key, use the OPEN SYMMETRIC KEY statement.
  • The ACTIVE FOR BEGIN_DIALOG should be set to ON in order to initiate the Service Broker conversation.
  • The login information is stored in the master database.
  • In order to enable a user to connect to the database, a DBA should add the user to that database.
  • Members of the db_access role can assign permissions to other users to enable them to access and query the 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 the db_ddladmin role have the permission to execute the T-SQL statements.
  • The Password History setting is used to prevent the use of old password.
  • The sp_helpuser stored procedure is used to display the database-level principals information.
  • A Denial-of-Service (DoS) attack is mounted with the objective of causing a negative impact on the performance of a computer or network. It is also known as network saturation attack or bandwidth consumption attack. Attackers make DoS attacks by sending a large number of protocol packets to a network.
    • Pass Microsoft MCITP Database Administrator Certification in first attempt.

  • The System Monitor tool is used to diagnose DoS attack.
  • The first step to prevent infection is to disconnect the infected server.