You can run applications in Client-Server, Main Frame, PC-LAN or Web environment
SQL Server Database engine provides
Reliable storage - Traditional IDE drives or RAID arrays
Rapidly access data - Using Indexes, Read data and insert into data cache ( needs sufficient memory)
Maintain data consistancy : - Allow one client at a time to change the data. Transactional consistency has several levels, each provides trade-off between accuracy and concurrency.(Locking and performance)
Tight security:- Two types of security, one at Server level and other at the database.
Enforce data :- This can be achieved through database objects, including rules, defaults, triggers, integrity, user datatypes and other data-integrity constraints.
SQL ENTERPRISE MANAGER(SQL-EM) :- is the central console from which most database tasks can be coordinated.1) Manage many server in a convenient interface.2)set server options and configuration values such as memory and processors to use, the default language, and default location of the data and log files.3) Manage logins, database users and database roles.4)Schedule automated jobs through SQL Agent.5)Create, Backup and restore databases.6)Browse table content 7)Manage Database objects such as tables, indexes, and stored procedures. 8)configure and manage replication.9) Import and export data.10) Transfer data between servers, SQL Servers and otherwise. 11) Provide frontend to other applications, such as the Data Transformation service designer, Query analyzer, Database diagraming and SQL Mail.12) Use Plain old T-SQL by 'ISQL' or 'OSQL'.
SQL SERVICE MANAGER
The Service manager is a small applet that allowa easy control of several services.
*SQL Server, the database engine * SQL Agent or job schedular * SQL Search, a full text engine * OLAP Server a sepaerate service used for Data warehousing.
It will poll each service every few seconds(Configurable through options) to determine its state. A check box also exists for automatic starts of SQL services, but in some maintance job it is reimportant to disable this property. All services can be stopped and started.
SQL SERVER AGENTAllows convenient execution of scheduled scripts and maintenace jobs. It is required to use replication services. The agent also handles automated alerts e.g. If database run out of space. The Agent is a window service that runs on the same machine as the SQL Server engine. The Agent is controlled through SQL-EM or the SQL Service Manager or the ordinary Windows service manager. The Agent also handles alerting. The alert system watch for a particular event, and then respond to this event by paging an operator, sending Email, running a predefined job, or any combination of these. SQL Agent can be configured to distribute common jobs to multiple servers through the use of Multiserver Administration.If you had 20 servers running the same job you can check the completion status in a moment rather logging on 20 servers and check the status.
SQL QUERY ANALYZER It is possible to have connection to different servers. 2000 Version included browser/template manager and an integrated stored procedure debugger.
SQL PROFILER: The SQL Profiler is a client tool that captures the queries and results flowing to and from the server. It is analogous to network sniffer. The profiler has ability to capture and save a complete record of all the traffic passed to the server. You can set filters to drill down the results. It can Capture all the queries sent to SQL Server, the exact SQL statement to the server from the application. Trap the access violation. It shows basic performance of each query and easily identify the the culprits.Audit server Activity in real time.
SQL Profiler allows you to trace the procedures and queries being executed in SQL Server and capture the run-time, reads, writes, execution plans, and other processing information. This help to find which queries are providing substandard performance , which one are executed most often, which indexes are being used by queries
(like set showplan on)
Data Tranformation Service(DTS)Th is a powerful tool to move the data from one form to another. rather just from text form to Database. If the data is simple you can use BCP utility at command line. DTS offers a simple interface, the DTS Designer, through the SQL EM.It imports text file using a fast bulk insert, Using ODBC or OLEDB connect to differnt database (Oracle,DB2 etc) to use either source or target of the data. Write VBscript or Java script to cleanse/transform data to your specification. Connect to ftp site and download file (new 2000).
Read infornation from ini file to drive any DTS tasks such as server name to connect, the database in which execute specific task, the table name to use, or the stored procedure to call. Perform all these tasks at a scheduled or recurring basis.
Replication: Replication is a server based tool that allows data to be synchronized between two or more DB
It can send data from SQL Server to SQL , Oracle, Access or any other databases via ODBC or OLEDB. It provides three kind of replications
1) Snapshot replication:- The server takespictures or snapshot, of the data in a table at a single point in time, the target data is simply replaced at each update. This is appropriate for small data sets, infrequent update periods or for management simplicity, or for one time replication operation.
2)Transactional Replication: For every insert/update/delete the server sends copy of the operationto every downstream database. Keep in mind it does not guarentee identical databases at any given point in time. Rather it guarantees that each change at the source will eventually propogated to the targets.Good for Web applications.
3)Merge Replication: It allows data to be modified by the subscribers and synchronized at later time. This would be helpful for a sales database that is replicated from a central SQL Server out to dozen sales laptops. Ths sales team can make changes to their Laptop database and later synchronized with the Central database.
4) Immediate Updating:- It allows a replication target to immediately modify data at the source. This is accomplished by using a trigger to run two-phase commit transaction.It is performance intensive.
Microsoft search Server(Full text serach):- It provides full text seraching, useful for searching large text field. You specify tables or entire database that you want to index. It is maintained outside the SQL engine in a special full text indexes Normal search select * from resume where description like '%Master%' Full text queryselect * from resume where contains(description,'%Master%') [You can use * for descript]
Analysis Services (OLAP Server): It provides essential services for using a data warehouse.(online analytical processing) Based on select criteria, data cubes are created on this data to allow powerful searches called data mining.Perform trend analysis to predict the future. combine otherwise disconnected variables to gain insight.