Posted on by uCertify

MCTS Installing and Configuring SQL Server 2005 Short Notes

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 on 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 the 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.

To analyze the capabilities of the simulation software of MCTS 70-431 certification exam, you can download a demo version and evaluate it free of cost. Click the link below:

70-431 – MCTS: SQL Server 2005

Download link: https://www.ucertify.com/exams/Microsoft/70-431.html

 

  • 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 is stored in the master database.
  • In order to secure data that is 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.
  • 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.
  • In order to use the MUST_CHANGE argument within the CREATE LOGIN statement, the CHECK_POLICY and CHECK_EXPIRATION arguments are set to on.
  • 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.
  • The secedit command is used to analyze the security of a server.
  • 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 OLEDB Provider dialog box to restrict nonadministrators to execute queries using the OPENROWSET and OPENDATASOURCE functions.

Implementing High Availability and Disaster Recovery

  • If a DBA has to take a backup of a database and also wants to make the database online, Database Mirroring should be used.
  • 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 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 mirror a database, configure the Sales database with the Full recovery model.
    • Back up the Sales database.
    • Restore the Sales database on another server.
    • Copy all the system objects to an instance that hosts the mirror database.
  • The different database mirroring roles are Principal, Mirror, and Witness Server.
  • 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.
  • The PERSISTED argument of the CREATE TABLE statement can be used to ensure that data in the computed column is physically stored in the table.
  • In order to transfer the backup of a transaction log from a database server to another database server, log shipping is used.
  • 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

Supporting Data Consumers

  • The TOP…WITH TIES option can be used only with the SELECT…ORDER BY clause.
  • The WITH TIES option can be used to retrieve those rows that are based on a given criterion.
  • In order to change the collation setting for an existing database, the ALTER DATABASE statement is used.
  • In order to perform the search operation in a database, use the WHERE clause with the SELECT statement.
  • The LEFT OUTER JOIN clause is used as follows:
    SELECT * FROM Order o LEFT OUTER JOIN OrderDetails od ON o.OrderID=od.OrderID
  • 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 column alias while retrieving data from a table, use the following syntaxes:SELECT cust_name AS Customer_Name, cust_age AS Customer_Age, cust_order AS Customer_Order FROM Customers

    SELECT cust_name AS “Customer_Name”, cust_age AS “Customer_Age”, cust_order AS “Customer_Order” FROM Customers

  • The INSERT…TOP statement is used to insert the first set of rows from one table to another.
  • The DISTINCT argument is used to eliminate the duplicate rows in the specified table.
  • In order to retrieve all records in a sequence from two tables, use the following syntax:SELECT * FROM table1 JOIN table2 ON primary_key_column_name=foreign_key_column_name
    ORDER BY column_name
  • The @@TRANCOUNT system global variable is used to count open transactions.
  • 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.
  • If a user adds the XSINIL option to the ELEMENTS directive in the FOR XML clause, SQL Server 2005 will generate an empty XML element for NULL values.
  • The xml datatype is used to store xml data.
  • In order to enable external applications to access data through HTTP endpoints, use the following syntax of the CREATE ENDPOINT statement:CREATE ENDPOINT ep_Systech
    STATE = STARTED
  • In order to send SOAP requests to the endpoint through the Web methods, use the following code:

    CREATE ENDPOINT ep_Sales
    AS HTTP (
    PATH = ‘/sql’,
    AUTHENTICATION = (INTEGRATED),
    PORTS = (CLEAR),
    SITE = ‘stSQL’
    )    FOR SOAP (
    WEBMETHOD ‘GetSalesInfo’ (NAME=’Sales.dbo.usp_WeeklySalesInfo’),
    WEBMETHOD ‘GetCustomersInfo’ (NAME=’Sales.dbo.usp_CustomersInfo’),

    BATCHES = DISABLED,
    WSDL = DEFAULT,
    DATABASE = ‘Sales’,
    NAMESPACE = ‘http:// stSQL.com/’
    )

  • If the Endpoint State setting is set to STOPPED, connection to the database cannot be established.
  • In order to change the message, the STATUS setting should be set to OFF. When the STATUS is set to OFF, the queue becomes unavailable and an existing message can be changed but cannot be removed.
  • The ACTIVE FOR BEGIN_DIALOG should be set to ON in order to initiate the Service Broker conversation.
  • 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.
  • In order to copy data in XML format the BCP utility format option should be used.
  • The OPENROWSET function accesses remote data without configuring the linked server.
  • The bulk-logged recovery model prevents the transaction log from becoming full.
  • The SSMS, BIDS, Command Prompt tools are used to start Import/Export Wizard.
  • Bulk-logged recovery model is used to import data.
  • 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.
  • In merge replication parameterized row filter can be used.
  • 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.
  • The Keep The Publisher Change conflict resolution policy maintains the data consistency on the publisher.
  • In order to prevent the trigger from firing during the process of replication, the 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 the 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 the Distribution and Merge agent at the Subscriber.

Pass 70-431 certification exam in the first attempt. Full featured Tests. 435 questions with answers and 309 study notes articles and exam tips:

70-431 – MCTS: SQL Server 2005

Download link: https://www.ucertify.com/exams/Microsoft/70-431.html

 

Maintaining Databases

  • 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 SP_ADD_JOBSERVER stored procedure is used to set the server for the job.
  • An alert job is created on the monitor server, and it generates an error if the backup has not been completed successfully.
  • The RAISERROR() statement is used to write an error to the Windows application log.
  • 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.
  • 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 methods of defragmenting indexes are as follows:
    • Drop and recreate indexes
    • CREATE INDEX…WITH DROP_EXISTING
    • DBCC DBREINDEX
    • DBCC INDEXDEFRAG
  • When the UNIQUE argument is used with the CREATE INDEX statement, two rows are not permitted to have the same index key.
  • When the transaction log is full, users are unable to update data in a database.
  • The syntax of adding a new file group to a database is as follows:ALTER DATABASE database_name
    ADD FILE
    (NAME = Sales_Data6,
    FILENAME = ‘D:\Sales\Sales_Data6.ndf’,
    SIZE = 250MB,
    MAXSIZE = 2GB,
    FILEGROWTH = 100MB) TO FILEGROUP filegroup_name
  • The REPAIR_REBUILD option of the DBCC CHECKDB statement repairs the database without any data loss.
  • In order to repair the database, the DBCC CHECKDB statement will be executed.
  • In order to remove fragmentation, rebuild the indexes.
  • A 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 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 ORDER BY clause is used to sort data in more than two databases having different collation settings.
  • The WITH INIT option of the backup statement is used to overwrite anything in the backup device.
  • 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.
  • 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.
  • The DBCC CHECKDB statement is used to repair a damaged database.
  • The steps of moving a SQL Server database from a previous version to SQL Server 2005 are as follows:
    Detach the Purchase database from the previous version of SQL Server.
    Copy the database to the server having SQL Server 2005 installed.
    Attach the database to SQL Server 2005 database.
  • The operations performed on the execution of the DBCC CHECKDB statement is as follows:
    • DBCC CHECKALLOC
    • DBCC CHECKTABLE
    • DBCC CHECKCATALOG

Monitoring and Troubleshooting SQL Server Performance

  • Memory: Pages/sec counter is used to detect whether or not the memory bottleneck is caused by I/O overhead.
  • 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 TSQL_Duration template is used to capture all the SQL statements and their execution time. It groups the SQL statements by duration.
  • The System Monitor tool is used to know the cause of slow authentication.
  • The Start tracing immediately after making connection check box is checked to create a trace successfully.
  • The DBCC_TRACESTATUS stored procedure is used to view the status of a trace flag.
  • Run the SQL Server Profiler and trace server activities to gather information about the resources used by SQL Server.
  • The SQLServer: Buffer Manager performance object is used to check the amount of RAM used by a computer.
  • The Database Engine Tuning Advisor tool is used to align the partitioned indexes.
  • In order to trace a database activity and replay it, run the SQL Server Profiler to monitor database activity and log the data to a trace file.
  • The SQL Server Profiler tool is used to create a trace.
  • The Deadlock graph event group displays a graphical representation of those tasks and resources that are involved in a deadlock.
  • 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 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.
  • The Activity Monitor tool should be used to retrieve information about locks.
  • The locks and deadlocks can be monitored through the System Monitor and the SQL Server Profiler tool.
  • 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.
  • The QUERY_GOVERNOR_COST_LIMIT option can be used to restrict the execution of queries that are taking a long time to execute.
  • A user assigned to the sysadmin role can connect to a database using the Dedicated Administrator Connection.
  • 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 database mail is enabled through the SQL Server Surface Area Configuration tool.
  • The SP_HELP_JOB stored procedure is used to retrieve information about a particular job or all the jobs in a database.
  • The SQL Server Agent tool is used to run a job automatically at a specified time.
  • The SQL Server Agent Service is used to run jobs, monitor SQL Server processes, automate administrative tasks, and send mail through the NETSEND command.
  • The sys.dm_exec_query_stats DMV is used to display the aggregate values of performance statistics for query plans.
  • The permissions necessary for executing a DMV are SELECT, VIEW SERVER STATE, OR VIEW DATABASE STATE.
  • The state_desc column of the sys.dm_db_mirroring_connections DMV displays the current state of the connection.
  • The READ UNCOMMITTED isolation level is the cause of dirty read.
  • The SYS.DM_EXEC_QUERY_STATS DMV is used to check the performance of the queries.
  • The SYS.DM_OS_WAIT_STATS DMV is used to find out the statistics of waits.

Creating and Implementing Database Objects

  • In order to create a new table that contains the data of an original table, the SELECT…INTO statement is to be used.
  • If a user want to remove any number of rows from a table without causing a block on the table, you should use the SWITCH operator with the ALTER TABLE statement.
  • The IDENTITY property of a column is used to generate automatic numbers for the column.
  • In order to modify the CHECK constraint, use the ALTER TABLE statement.
  • The ENCRYPTION argument is used to prevent the replication of a view when the database is replicated.
  • The ENCRYPTION argument of the CREATE VIEW statement prevents users from reading the script written for the view.
  • A view is a type of virtual table. The data accessible through a view is not stored in the database as a distinct object. Views are created by defining a SELECT statement. 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.
  • DML triggers are executed when insert, update, or delete operations are to be performed through triggers on a table.
  • 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 syntax of the CREATE FUNCTION statement that returns a single scalar value is as follows:
    CREATE FUNCTION function_name { parameter_datatype } RETURNS return_datatype
  • In order to remove binding of a function from a table, following actions shall be taken:
    Drop the function.
    Modify the function without using the SCHEMABINDING argument in the ALTER FUNCTION statement.
  • The CREATE FUNCTION statement supports the WITH SCHEMABINDING clause, which binds the function to the schema of an object (such as a table, view, or another user-defined function) that the function references to. Any object referenced by a schema-bound inline function cannot be dropped because a schema-bound inline function references the view.
  • The LTRIM function is used to remove leading spaces from a table.
  • The VARYING argument of the CREATE PREOCEDURE is used to specify a result set that can be used as an output parameter. A stored procedure can create the output parameter dynamically in this case.
  • 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.
  • 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.
  • 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.
  • 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 entity integrity is enforced by using the PRIMARY KEY constraint.
  • 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, 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 the table to enter valid values in the table.
  • The syntax of creating an XML index is as follows:
    CREATE INDEX index_name FOR XML
  • The ALTER INDEX statement is used to change the IGNORE_DUP_KEY setting after the creation of an index.
  • The WITH SCHEMABINDING clause safeguards a view definition against any structural modification of the underlying table.
  • 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.
  • The SYS.INDEXES stored procedure is used to view the row per index of a tabular object such as tables, indexes, etc.
  • 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 ensure that the indexes are up-to-date, update the statistics of the indexes on each table.
  • In order to implement a user-defined type on a column, following syntax should be used:
    CREATE TABLE OrderDetails
    (
    Name VARCHAR(20)
    Age INT
    CITY my_type
    )
    where CITY is a column and my_type is the user-defined type
  • 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 provides result with the return statement.
  • The model database is used to save an alias datatype so that it can be used by user databases.
  • The SP_HELP_FULLTEXT_SYSTEM_COMPONENTS stored procedure is used to retrieve the information about the registered word-breaker.
  • In order to update the modified rows of the full-text index, you will specify the INCREMENTAL argument with the ALTER FULLTEXT INDEX statement.
  • In order to change the ACCENT_SENSITIVITY setting for the catalog, you will use the ALTER FULLTEXT CATALOG statement with the REBUILD option.
  • In order to search for records that match exact word in a query, you will use the CONTAINS() function. This function searches for records using the exact word or word prefix specified in the query.
  • Hardware petitioning is used to design a database to take advantage of the available hardware architecture.
  • The CREATE PARTITION FUNCTION statement is used to create a partition for a table.
  • Create a partition function is the first step of creating a partition scheme.
  • In order to optimize the execution of a query, partition the table horizontally and place each partition on a separate filegroup.

70-431 – MCTS: SQL Server 2005

Download link: https://www.ucertify.com/exams/Microsoft/70-431.html