MCITP 70-443 Exam Passing Tips

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

MCITP 70-443 Exam Passing Tips

Rating:

Designing a Database Server Infrastructure

• The Memory: Available Bytes counter is used to measure the available memory.


• Capacity planning is a process to determine the hardware requirements for a database server. The process includes performance testing of Web applications, software applications and databases on the server. The result of this testing is used to analyze future hardware requirements.


• The Memory: Pages/sec and Memory: Available Bytes counters should be monitored to view the status of RAM requirement.


• The System Monitor tool is used to monitor the counters.


• The SQL Server Profiler tool is used to create deadlock graph.


• The SQL Server Profiler tool is used to tune queries to prevent locks and deadlocks.


• The memory counters used to measure the memory requirements are as follows:
  
  • Memory: Pages/sec
  
  
  • Memory: Available Bytes
  
  
  • SQL Server: Buffer Manager: Buffer Cache Hit Ratio
  
  
  • Physical Disk: Disk Reads/sec
  
  
  • Physical Disk: Disk Writes/sec


• The Processor: % Processor Time and System Processor Queue Length counters are used to measure CPU bottleneck.


• The memory bottleneck is caused due to the following processes:
  
  • Excessive paging
  
  
  • Low buffer cache hit ratio
  
  
  • High memory consumption
  
  
  • High disk I/O operations


• The sys.dm_tran_locks DMV is used to view the information about the currently active lock manager resources.


• In order to grant full control on the databases to different users, install separate instances of SQL Server for the users and grant them full control.


• The different editions of SQL Server 2005 are as follows:
  
  • SQL Server 2005 Express Edition
  
  
  • SQL Server 2005 Workgroup Edition
  
  
  • SQL Server 2005 Developer Edition
  
  
  • SQL Server 2005 Standard Edition
  
  
  • SQL Server 2005 Enterprise Edition


• If an endpoint is created on the Express Edition of SQL Server 2005, the server will act as a Witness Server.


• Enterprise edition of SQL Server 2005 supports Database Mirroring.


• RAID-1 is referred to as disk mirroring.


• The RAID-0 configuration provides fast performance.


• The Enterprise Edition of SQL Server 2005 provides the facility of installing fifty instances of SQL Server 2005.


• The SQL Server Integrated Services tool is used to import a database from other databases such as Microsoft Access.


• Take the following steps to import data from other databases such as Microsoft Access:
  
  • Create a database in SQL Server.
  
  
  • Start the Import and Export wizard.
  
  
  • Import the tables of the database in Microsoft Access to the database in SQL Server.


• The database server consolidation is a process of reducing the number of database servers. In this process, the databases on multiple servers are migrated to either a single server or to a smaller set of servers.


• The advantages of database server consolidation are as follows:
  
  • It reduces the cost and expenses of hardware resources, software licensing and monitoring, etc.
  
  
  • It optimizes hardware resources.
  
  
  • It helps to improve administration of databases.
  
  
  • It helps to improve security of server.

Design Security for a Database Server Solution

• A security policy is a document that is created to secure and manage a database server of an organization.


• The System Monitor tool is used to monitor a denial of service attack.


• The sysadmin role has full control on a server.


• The db_datawriter role is used to execute DML statements.


• Securables are used within SQL Server to assign permissions to the users.



• Assign the users to db_datareader, db_datawriter, and db_ddladmin roles in order to permit them to execute queries successfully.


• The asymmetric encryption uses public and private keys.


• An injection attack is a process in which an attacker tries to execute unauthorized SQL statements.


• In order to prevent the injection attack, validate user input.


• 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.


• The db_datareader role provides the permission of only viewing the data.

Designing a Physical Database

• The SQL Server Management Studio tool is used to create a database.


• In order to add a primary key to a column in a new table, use the following syntax:
  CREATE TABLE table_name(column1 datatype NOT NULL CONSTRAINT pk PRIMARY KEY).


• The ADD CONSTRAINT clause in the ALTER TABLE statement is used to add a constraint to a column of an existing table.


• In order to add a new column in an existing table, use the following syntax:
  ALTER TABLE table_name ADD COLUMN column_name datatype NULL


• In order to retrieve all the rows from a table and matching rows from other table, use the following syntaxes:
  SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.primary_key_column_name=table2.foreign_key_column_name
  
  SELECT * FROM table2 LEFT OUTER JOIN table1 ON table2.foreign_key_column_name=table1.primary_key_column_name


• CHECK constraint enforces domain integrity by limiting the values that are accepted by a column.


• In order to add a check constraint to a column, use the
  ADD CONSTRAINT clause with the ALTER TABLE statement.


• 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.


• In order to create a computed column, use the following syntax:
  ALTER TABLE table_name ADD computed_column_name AS formula


• In order to insert data into a temporary table from an existing table, use the following syntax:
  SELECT * INTO #temporary_table_name FROM original_table_name


• A temporary table is saved in the tempdb database.


• In order to prevent users from inserting wrong data, use the CHECK constraint.


• Use the ALTER DATABASE statement to add filegroups to an existing database.


• The syntax of dropping an index is as follows:
  DROP INDEX index_name ON object_name


• The syntax of creating clustered index is as follows:
  CREATE CLUSTERED INDEX index_name ON object_name(column_name)


• A page split is a process of splitting an index page. The indexes have to be defragmented to remove page split.


• The Database Engine Tuning Advisor tool is used to check index fragmentation.


• The DBCC INDEXDEFRAG statement used to defragment the index while the database is online.


• In order to create a view, use the following syntax:
  CREATE VIEW view_name AS SELECT column1, column2 FROM table_name


• The SCHEMABINDING argument of the CREATE VIEW statement will prevent the deletion of a table.


• The CHECK OPTION argument forces the update of a view in accordance with its related tables.

Design a Database Solution for High Availability

• The Database Mirroring, Log Shipping, and Replication processes can be used to configure Failover Clustering.


• The Principal, Mirror, and Witness Server roles are used to configure database mirroring.


• The combination of TCP endpoint and DATABASE_MIRRORING payload is used to configure Database Mirroring.


• If witness server is not available, failover is to be done manually.


• In order to create endpoint, use the CREATE ENDPOINT statement.


• The principal role makes the server online and allows a database to respond to requests sent by an application.


• High Availability Operating Mode is a transfer mode that transfers data between the principal and mirror databases.


• The steps of failover are as follows:
  
  • The Principal server fails.
  
  
  • The Mirror server detects the failure and requests the Witness server to promote it to the Principal server.
  
  
  • The Witness server is unable to ping the Principal server, but pings the Mirror server.
  
  
  • It agrees for role reversal, and SQL Server promotes the Mirror server to the Principal server.
  
  
  • When the original Principal server recovers from the failure, SQL Server demotes it to the Mirror server.
  
  
  • Database mirroring provides the facility of automatic failover.


• Read-only data access is available from the Mirror server through a database snapshot.


• In order to create a database snapshot, use the following syntax:
  CREATE DATABASE snapshot_name ON (NAME=logical_file_name, FILENAME='os_file_name') AS SNAPSHOT OF database_name


• The Merge replication is used to send data from the Subscribers to the Publisher.


• Replication is the process of copying and distributing data and database objects from one database to another. The technologies involved in replication synchronize data between SQL Server databases to maintain database consistency.


• The Replication Monitor tool is used to monitor the replication status.


• The Queue Reader replication agent is used to view the conflict between a Publisher and Subscriber servers.


• The full and transaction log backups are used to configure log shipping.


• The alert job is created on the monitor server to generate an error if the backup has not been completed successfully.


• The full and bulk-logged recovery models are used to perform log shipping.


• The steps performed in log shipping are as follows:
  
  • 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.


• By setting a server to No recovery mode, the transaction logs can be restored on the secondary server and users cannot issue SELECT statements against the databases on the secondary server.


• The SQL Server Agent tool is used to run jobs automatically.


• In order to configure copy job, use the following steps:
  
  • Configure the copy job on the secondary server.
  
  
  • Configure the proxy account of the copy job on the secondary server.
  
  
  • Configure read permissions on backup directory and write permissions on the copy directory.


• Monitor server is an optional server instance that is used to monitor the health of the log shipping.


• In order to configure log shipping jobs, use the following steps:
  
  
  • Configure the backup job on the primary server.
  
  
  • Configure the copy job on the secondary server.
  
  
  • Configure the restore job on the secondary server.


• When the secondary server is set to No Recovery mode, the transaction log is restored regularly. Users cannot connect to the database in this mode.


• The DBCC CHECKDB statement is used to check the integrity of a database.


• Referential integrity is not enforced during log shipping.


• The SSIS tool is used to transfer logins from one instance to another.


• The primary server can be added to replication roles.

Design a Data Recovery Solution for a Database

• The full ad bulk-logged recovery models are set for the transaction log backup.


• The full recovery model is used to recover a database to the point in time.


• Full backup uses only a part of a transaction log.


• In order to plan a full and differential backup, first take a full database backup and then take a differential database backup.


• The Transaction log backup is most useful for disaster recovery.


• The BACKUP LOG statement is used to backup the transaction log.


• The RESTORE VERIFYONLY statement verifies a backup.


• The SP_ADDUMPDEVICE stored procedure is used to create a backup device.


• In order to restore the transaction log, use the following syntax:
  RESTORE LOG database_name FROM log_backup WITH NORECOVERY


• The members of sysadmin and dbcreator roles have the permission to restore a transaction log.


• In order to backup a transaction log use the following syntax:
  BACKUP LOG database_name TO backup_device


• The master database should be regularly backed up to prevent the loss of the installation of the SQL Server.

Designing a Strategy for Data Archiving

• The different types of data archiving methods are online, near-line, and offline.


• Data archiving is a process of removing unnecessary data from one database to another or to other storage device. The archived data can be stored either online or offline.


• Central Publisher topology is a replication topology in which Publisher and Distributor roles are configured on the same server.


• 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 static row filter is defined before the creation of an article. The article is restricted to have the same number of rows that are to be replicated to the Subscriber. The rows of a table in an article can be filtered by using the WHERE clause in the SELECT statement.


• The merge replication is used to archive data.

Rating: