New certification from Microsoft 70-441

Yesterday I passed 70-441 by score of 966. Exam Details

PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 exam

Here are some notes about it:

- It has 6 testlets that contains 56 questions
"Testlet is actually a scenario based set of questions, covers from 5 to 12 questions"

1- A. Datum Corporation
2- Woodgrove Bank
3- Trey Research
4- Consolidated Messenger
5- Southridge Video
6- Coho Vineyard&Winery

- Each scenario consists of sections like:

- BACKGROUND
- EXISTING ENVIRONMENT
- BUSINESS REQUIREMENTS
- TECHNICAL REQUIREMENTS

- You can view scenario sections anytime while answering questions
- Each testlet has its own time limit, which tests your design capabilities under stress


I readed the books
- MCITP-Self-Paced-Training-Kit-Exam-Designing-Database-Solutions-by SQL 2005
- MCTS-certification-Microsoft-SQL-Server-2005-Database-Essentials-Step-by-Step
- Microsoft-SQL-Server-TM-2005-Applied-Techniques-Step-by-Step

Microsoft SQL Server 2005 New Features

Contents:

• Using SQL Server Configuration Manager
• Using SQL Server Surface Area Configuration
• Using Surface Area Configuration for Services And Connections
• Using Surface Area Configuration For Features
• Using Dependencies
• Using SQL Server Management Studio Templates

SQL server 2005 has many tools to help you analyze behavior, enable features, and study and enhance the performance of your server. The following sections will describe some of these tools.

1- Using SQL Server Configuration Manager

It is a Microsoft Management Console application that allows you to configure SQL Server 2005’s installed services, network configuration, and network protocols.

You can perform actions such as starting, pausing, and stopping services as well as defining the SQL Server and SQL Native Client network configuration.

Open it from
Start menu à choose All Programs à Microsoft SQL Server 2005 à Configuration Tools à SQL Server Configuration Manager



It can be used for running, stopping and restarting SQL Server services which are:

o SQL Server Integration Services
o SQL Server Full-Text Search Services
o SQL Server
o SQL Server Analysis Services
o SQL Server Reporting Services
o SQL Server BrowserSQL Server Agent




Using the SQL Server 2005 Network Configuration Node

It allows you to configure the network protocols used by each SQL Server instance by enabling or disabling the protocol.

Aliases:

Aliases are alternate names that can be used to connect to a SQL Server instance. Egypt_Server_Test will be the alias name that maps to the SQL Server instance egypt-aeid\Products , using the TCP/IP network protocol with port number 1433

2- Using SQL Server Surface Area Configuration

It is a tool that helps you enable, disable, start, or stop the features and services of your local and remote SQL Server 2005 installations. Stopping and disabling unused services allows you to reduce the surface area and makes your system more secure.

Open it from
--> Start menu --> choose All Programs --> Microsoft SQL Server 2005 --> Configuration Tools --> SQL Server Surface Area Configuration



2.1 Using Surface Area Configuration For Services And Connections

It allows you to configure the state of SQL Server services. You can stop, pause, resume, and start the SQL Server instance services. Moreover, you are able to configure whether the SQL Server instance will allow remote connections and, if so, which protocols to use



- Database Engine
- Analysis Services
- Reporting Services
- SQL Agent
- Full-Text Search
- Integration Services
- SQL Server Browser

All components have Service item for managing service settings:

Startup type : Use to configure how the service starts.
Automatic specifies that the service starts when the system starts.

Manual specifies that a user or dependent service can start the service, and that the service does not start when the system starts.

Disabled prevents the service from being started by the system, a user, or any dependent service.

Service status : Shows the status of the service.

Remote Connections for
- Database Engine
- Reporting Services

It is used to change the protocols on which SQL Server listens for incoming client connections. TCP/IP is preferred over named pipes because it requires fewer ports to be opened across the firewall



Local connections only
If selected, the Database Engine instance will only listen for shared memory connections on the local computer. Connections from remote computers will fail.

Local and remote connections
If selected, the Database Engine instance will listen for connections from the local computer and from remote computers using the selected protocols.

Using TCP/IP protocol only
If selected, the Database Engine instance listens for remote connections using TCP/IP, but not named pipes.

Using Named Pipes protocol only
If selected, the Database Engine instance listens for remote connections using named pipes, but not TCP/IP.

Using both TCP/IP and Named Pipes protocols
If selected, the Database Engine instance listens for remote connections using TCP/IP and named pipes.

2.2 Using SQL Server Surface Area Configuration for Features

It provides a single interface for enabling or disabling many Database Engine, Analysis Services, and Reporting Services features. Disabling unused features helps to secure your Microsoft SQL Server installations by reducing the SQL Server surface area.



SQL Mail, which is deprecated in favor of Database Mail, is disabled by default. Using this dialog, you can enable SQL Mail for backward compatibility.

3- Using Dependencies

Some database objects have dependencies upon other database objects. For example stored procedures may depend on objects like functions, tables, views …etcYou find dependencies by right click on any database object like tables, views, sp , fn ….etc





There are 2 types of dependencies for an object:

a- Objects that depend on

It displays a list of those objects that are dependency-tracked and that depend on the selected object.



b- Objects on which depends

It displays a list of those objects that are dependency-tracked, on which the selected object depends.



Using SQL Server Management Studio Templates

Templates are files that contain SQL scripts to help you create objects in the database.
You can create objects such as
o Databases
o Tables
o Views
o Indexes
o Stored procedures
o Triggers
o Statistics
o Functions

Open it from

Open SQL Server Management Studio --> View Menu --> Template Explorer
Find the sql files on hard disk from

"C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates"

Lets Create Stored Procedure ( New Menu )

Note: you can do the same step from the object explorer to a database, under programmability Stored Procedure, then by right click on that node


















Note: the created template contains many params and names needed to renamed, you can do that by the use of wizard (Ctrl + Shift + M)



Note: if you press OK, you won’t modify the names by wizard again.