Microsoft SQL Server 2000

Microsoft SQL Server is a Relational Database Management System (RDBMS).  It’s design to run from the single user systems to large multiprocessor servers. 

SQL is commonly used as the background system to support various kinds of the business applications such as web and window based commercial or non-commercial business applications.

SQL 2000 server comes with number of tools (Enterprise Manager, Query Analyzer, SQL Profiler, Service Manager, and Data Transformation Services) which are very helpful to support the database administration and programmatic tasks also. It’s providing a user friendly application environment to easily understandable.

Although SQL Server can also be run as a desktop database system, it is most commonly used as a server database system.

As we know that Server based database systems are designed to run on a central server, so that multiple users can access the same data simultaneously. The users normally access the database through an application.

Enterprise Manager - This is the main administrative console for SQL Server installations. It provides you with a graphical "birds-eye" view of all of the SQL Server installations on your network. User can perform high-level administrative functions that affect one or more servers, schedule common maintenance tasks or create and modify the structure of individual databases.

 

Query Analyzer - It offers a quick and dirty method for performing queries against any of your SQL Server databases. It's a great way to quickly pull information out of a database in response to a user request, test queries before implementing them in other applications, create/modify stored procedures and execute administrative tasks.

 

SQL Profiler - It provides a window into the inner workings of your database. You can monitor many different event types and observe database performance in real time. SQL Profiler allows you to capture and replay system "traces" that log various activities. It's a great tool for optimizing databases with performance issues or troubleshooting particular problems.

Service Manager – This is used to control the MSSQLServer (the main SQL Server process), MSDTC (Microsoft Distributed Transaction Coordinator) and SQLServerAgent processes. 

An icon for this service normally resides in the system tray of machines running SQL Server. You can use Service Manager to start, stop or pause any one of these services.

Data Transformation Services (DTS) – It provide an extremely flexible method for importing and exporting data between a Microsoft SQL Server installation and a large variety of other formats. The most commonly used DTS application is the "Import and Export Data" wizard found in the SQL Server program group.

System Databases

SQL Server 2000 installs with the following databases which are known as the system databases -

Database	Type	Description
master	System database	Stores system level information such as user accounts, configuration settings, and info on all other databases.
model	System database	This database is used as a template for all other databases that are created.
msdb	System database	Used by the SQL Server Agent for configuring alerts and scheduled jobs etc
tempdb	System database	Holds all temporary tables, temporary stored procedures, and any other temporary storage requirements generated by SQL Server.
pubs	Sample database	This database can be used for training purposes, and is based on a book publishing company.
Northwind	Sample database	This database can be used for training purposes, and is based on a fictitious company called "Northwind Traders".

SQL Server 2000 has a number of security features that assist database administrators to maintaining their database in a secure way.

SQL Server 2000 allows user to configure users and roles for anyone who needs to access SQL Server or any of its databases and their objects.

Server Roles are available for various database administration tasks. Not everyone should be assigned to a server role. In fact, only advanced users such as database administrators should be assigned a server role.

Server Role	Description
sysadmin	This is the super server role and user with the server role can perform any task in SQL Server.
serveradmin	User with the server role can set server-wide configuration options, can shut down the server.
setupadmin	User with the server role can manage linked servers and startup procedures.
securityadmin	User with the server role can manage logins and database permissions, read logs, change passwords.
processadmin	User with the server role can manage processes running in SQL Server.
dbcreator	User with the server role can create, alter, and drop databases.
diskadmin	User with the server role can manage disk files.
bulkadmin	User with the server role can execute BULK INSERT statements.

 

The Linked Servers option allows users to connect to another instance of SQL Server running on a different machine, different environments, perhaps remotely in a different city/country. This is the most useful technique if user needs to perform distributed queries (query from one or more remote databases).

The Remote Servers option allows users to execute a stored procedure on another instance of SQL Server without establishing another connection. The Remote Servers option is only provided for backwards compatibility.

DTS (Data Transformation Services) is a set of graphical tools that allows you to transfer data between disparate sources into one or more destinations.

SQL Server DTS includes the following tools, which enable you to create, schedule, and execute DTS packages.

Tool	Description
DTS Import/Export Wizard	Enables you to transfer data to and from a SQL Server database.
DTS Designer	Enables you to build complex DTS packages containing workflow and event-driven logic. The DTS Designer can also be used to modify packages created with the DTS Import/Export Wizard.
DTS Run utility	Allows you to schedule and run a DTS package.
dtsrun utility	Allows you to run DTS packages from the command prompt and schedule them using batch files.
DTS Query Designer	A graphical user interface that allows you to build SQL queries.