Microsoft technical palcement paper with answers
1 What do ?jobs? and ?alerts? mean in SQL Server ?
Jobs: Using SQL Server Agent you can create and schedule jobs that automate routine administrative tasks. Database administrators create jobs to perform predictable administrative functions either according to a schedule or in response of events and conditions. Jobs can be simple operations containing only a single job step or can be extremely complex operations containing many job steps. SQL Server Agent is responsible for management and execution of all jobs. Agent must be running for jobs to be executed. SQL server 2k supports jobs containing operating system commands.
Alerts: database administrators define alerts to provide event and performance condition notification and to execute jobs in response to SQL server events or performance conditions. E.g. when the log is 90% full an alert can be configured to fire a job that executes a job to back up and truncate the transaction log.
2 How many groups of roles are supported in SQL Server ? Explain them
SQL Server uses roles. Two layers of access exist: access to the SQL Server and access to a database object within the server. Each can be configured separately. There are four database roles, namely: _ Public? Essentially anyone who has enough rights to connect to the database; the lowest role possible in terms of database permissions. _ db_owner? Someone who has full rights to this database, including the right to delete it altogether, create objects, and so on. _ db_data_reader? Someone who is allowed to read the data without any modifications, and who cannot create objects. _ db_datawriter? Someone who is allowed to read and write data, but who cannot create objects. These roles are contained in every database, including system databases. Every user will belong to at least one of them
3 What is T-SQL ?
Transact-SQL is a language containing the commands that are used to administer instances of SQL Server; to create and manage all objects in an instance of SQL Server; and to insert, retrieve, modify, and delete data in SQL Server tables. Transact-SQL is an extension of the language defined in the SQL standards published by the International Organization for Standardization (ISO) and the American National Standards Institute (ANSI).
A Transact-SQL statement is a set of code that performs some action on database objects or on data in a database. SQL Server supports three types of Transact-SQL statements: DDL, DCL, and DML.
A DDL statement supports the definition or declaration of database objects such as databases, tables, and views. Three DDL commands: create, alter and drop.
Data control language is used to control permissions on database objects. The DCL commands are grant and revoke.
Data manipulation language is used to select, insert, update, and delete data in the objects defined with DDL
4 What is the lock types supported in SQL Server ?
There could be thousands of concurrent users trying to read or modify the database, sometimes exactly the same data. If not for locking, your database would quickly lose its integrity. The following basic types of locks are available with SQL Server: – Shared locks: Enable users to read data but not to make modifications. – Update locks: Prevent deadlocking (discussed later in this session). – Exclusive locks: Allow no sharing; the resource under an exclusive lock is unavailable to any other transaction or process. – Schema locks: Used when table-data definition is about to change?for example, when a column is added to or removed from the table. – Bulk update locks: A special type of lock used during bulk-copy operations.
5 What is OLAP ?
Online Analytical Processing (OLAP) is by far the most complex and advanced SQL Server components. Companies are using OLAP more and more as they try to make sense of their tons of accumulated data. OLAP is used in the mysterious field called ?data
Analysis,? The standard database table represents a flat matrix; SQL Server 2000 Analysis Services use the notion of cubes. The data and corresponding objects are multidimensional, having more dimensions than our four-dimensional space-time continuum; the number of dimensions is limited only by your imagination and hardware capabilities. You must install SQL Server Analytical Services. SQL Server 2000 Analysis Services presents the data from these fact and dimension tables as multidimensional cubes that can be analyzed for trends and other information that is important for making informed business decisions.
6 Which are the two authentication modes in SQL Server 2k ?
There are two authentication modes in SQL Server 2k: – Windows authentication: if user is already authenticated on the windows domain as valid windows user, SQL Server 2k can be requested to trust authentication by the operating system and allow the user assess to SQL Server 2k based on these credentials. You call a connection using windows authentication as a trusted connection.- SQL Server authentication: if the user accessing either has not been authenticated on the windows domain or wants to connect using a SQL Server 2k security account the user can request that SQL Server 2k directly authenticate the user based on submission of a username and password.
7 Which are the different services in SQL Server ? How do you manage them ?
Replication Service: SQL Server 2000 replication enables sites to maintain multiple copies of data on different computers, in order to improve overall system performance, while ensuring that all the different copies are kept synchronized.
DTS: By using DTS, you can build data warehouses and data marts in SQL Server by importing and transferring data from multiple heterogeneous sources interactively or automatically on a regularly scheduled basis.
Analysis Services: Analysis Services provides tools for analyzing the data stored in data warehouses and data marts.
Metadata services: SQL Server Meta Data Services provides a way to store and manage metadata about information systems and applications. This technology serves as a hub for data and component definitions, development and deployment models, reusable software components, and data warehousing descriptions.
Reporting services: used to generate reports from the data in the database
8 What is the lock types supported in SQL Server ?
There could be thousands of concurrent users trying to read or modify the database, sometimes exactly the same data. If not for locking, your database would quickly lose its integrity. The following basic types of locks are available with SQL Server: – Shared locks: Enable users to read data but not to make modifications. – Update locks: Prevent deadlocking (discussed later in this session). – Exclusive locks: Allow no sharing; the resource under an exclusive lock is unavailable to any other transaction or process. – Schema locks: Used when table-data definition is about to change?for example, when a column is added to or removed from the table. – Bulk update locks: A special type of lock used during bulk-copy operations.
9 How many groups of roles are supported in SQL Server ? Explain them
SQL Server uses roles. Two layers of access exist: access to the SQL Server and access to a database object within the server. Each can be configured separately. There are four database roles, namely: _ Public? Essentially anyone who has enough rights to connect to the database; the lowest role possible in terms of database permissions. _ db_owner? Someone who has full rights to this database, including the right to delete it altogether, create objects, and so on. _ db_data_reader? Someone who is allowed to read the data without any modifications, and who cannot create objects. _ db_datawriter? Someone who is allowed to read and write data, but who cannot create objects. These roles are contained in every database, including system databases. Every user will belong to at least one of them
10 Explain the Physical Structure of a Database
The SQL data is stored in the database. The data is organized into logical components that are visible to user however data is stored as files on hard disk. Each SQL server has four system databases: master, temp, msdb and model and multiple user databases. How many user databases depend from organization to organization?
The fundamental unit of data storage is page. The page size in SQL is 8KB. Every page contains a page header which is 96 bytes and stores system information such as the type of the page, amount of free space on the page, and object that owns the page.
Extents are another unit which is used to allocate space to pages and indexes. A extent is 8 continuous pages or 64 KB.
SQL server 2k has 3 types of data files: Primary data files which is the starting point of the database and points to other files, secondary data files comprise of data files other than primary data files and thirdly the log files to recover database in the event of a disaster.