Microsoft SQL Server 2005 New Features

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

http://3.bp.blogspot.com/_wK9KWrjiI_...E/s400/001.png
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

http://3.bp.blogspot.com/_wK9KWrjiI_...Hi0/s400/1.png

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

http://1.bp.blogspot.com/_wK9KWrjiI_...CmE/s400/2.png


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.


http://1.bp.blogspot.com/_wK9KWrjiI_...3B8/s400/3.png

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

http://2.bp.blogspot.com/_wK9KWrjiI_...s8s/s400/4.png

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

http://2.bp.blogspot.com/_wK9KWrjiI_...0aw/s400/5.png

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

http://2.bp.blogspot.com/_wK9KWrjiI_...Vgg/s400/6.png

- 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

http://3.bp.blogspot.com/_wK9KWrjiI_...7ts/s400/7.png

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.

http://4.bp.blogspot.com/_wK9KWrjiI_...0xE/s400/8.png

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



http://4.bp.blogspot.com/_wK9KWrjiI_...g4/s400/10.JPG

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.

http://1.bp.blogspot.com/_wK9KWrjiI_...nM/s400/11.png

b- Objects on which depends

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

http://3.bp.blogspot.com/_wK9KWrjiI_...fE/s400/12.png

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"

http://3.bp.blogspot.com/_wK9KWrjiI_...D0/s400/13.png

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

http://1.bp.blogspot.com/_wK9KWrjiI_...TY/s400/14.png
















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

http://3.bp.blogspot.com/_wK9KWrjiI_...SI/s400/15.png

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