Custom Search
Wednesday, September 24, 2008
Tuesday, September 23, 2008
What Is a Server?
server [a Webopedia definition]
(ser4ver) (n.) A computer or device on a network that manages network resources. For example, a file server is a computer and storage device dedicated to storing files. Any user on the network can store files on the server. A print server is a computer that manages one or more printers, and a network server is a computer that manages network traffic. A database server is a computer system that processes database queries.
Servers are often dedicated, meaning that they perform no other tasks besides their server tasks. On multiprocessing operating systems, however, a single computer can execute several programs at once. A server in this case could refer to the program that is managing resources rather than the entire computer.
If you're not familiar with the functions of a server, but have heard the term in passing, you may think of a server as some mystical computer beast that performs amazing tasks and generally is a hands-off system. Before we delve into the inner-workings of a server, let's start by dispelling that "mystical" thing. From a hardware perspective, a server is simply a computer on your network that is configured to share its resources or run applications for the other computers on the network. You may have a server in place to handle file or database sharing between all users on your network, or have a server configured to allow all users to share a printer, rather than having a printer hooked up to each individual computer in your organization.
What makes the term server doubly confusing is that it can refer to both hardware and software. That is, it can be used to describe a specific software package running on a computer or the computer on which that software is running. The type of server and the software you would use depends on the type of network. LANs and WANs for example are going to use file and print servers while the Internet would use Web servers. In this article we provide an overview on some of the more common types of servers such as application servers, database servers, mail servers, and Web servers.
(ser4ver) (n.) A computer or device on a network that manages network resources. For example, a file server is a computer and storage device dedicated to storing files. Any user on the network can store files on the server. A print server is a computer that manages one or more printers, and a network server is a computer that manages network traffic. A database server is a computer system that processes database queries.
Servers are often dedicated, meaning that they perform no other tasks besides their server tasks. On multiprocessing operating systems, however, a single computer can execute several programs at once. A server in this case could refer to the program that is managing resources rather than the entire computer.
If you're not familiar with the functions of a server, but have heard the term in passing, you may think of a server as some mystical computer beast that performs amazing tasks and generally is a hands-off system. Before we delve into the inner-workings of a server, let's start by dispelling that "mystical" thing. From a hardware perspective, a server is simply a computer on your network that is configured to share its resources or run applications for the other computers on the network. You may have a server in place to handle file or database sharing between all users on your network, or have a server configured to allow all users to share a printer, rather than having a printer hooked up to each individual computer in your organization.
What makes the term server doubly confusing is that it can refer to both hardware and software. That is, it can be used to describe a specific software package running on a computer or the computer on which that software is running. The type of server and the software you would use depends on the type of network. LANs and WANs for example are going to use file and print servers while the Internet would use Web servers. In this article we provide an overview on some of the more common types of servers such as application servers, database servers, mail servers, and Web servers.
Application Server
Also called an appserver. A program that handles all application operations between users and an organization's backend business applications or databases. Application servers are typically used for complex transaction-based applications. To support high-end needs, an application server has to have built-in redundancy, monitors for high-availability, high-performance distributed application services and support for complex database access.
Print Server
Print servers are set up on a network to route print requests from other computer workstations on the network. The server handles the print file request and sends the file to the requested printer where it is spooled. A print server allows multiple users on a network to share the print
Database Server
A database server is an application that is based on the client/server architecture model. The application is divided into two parts: a front-end running on a workstation (where users collect and display the database information) and the
Mail Server
Almost as ubiquitous and crucial as Web servers, mail servers move and store mail over corporate networks (via LANs and WANs) and across the Internet. Today, most people think of mail servers in terms of the Internet. Mail servers
Web Server
At its core, a Web server serves static content to a Web browser by loading a file from a disk and serving it across the network to a user's Web browser. This entire exchange is mediated by the browser and server talking to each other using HTTP. Any computer can be turned into a Web server by installing server software and connecting the machine to the Internet. There are many Web server software applications, including public domain software from NCSA and Apache, and commercial packages from Microsoft, Netscape and others.
FTP Server
An FTP server is a software application running the File Transfer Protocol (FTP), the protocol for exchanging files over the Internet. FTP works in the same way as HTTP for transferring Web pages from a server to a user's browser and SMTP for transferring electronic mail across the Internet in that, like these technologies, FTP uses the Internet's TCP/IP protocols to enable data transfer. FTP is most commonly used to download a file from a server
Proxy Server
A server that sits between a client application, such as a Web browser, and a real server. It intercepts all requests to the real server to see if it can fulfill the requests itself. If not, it forwards the request to the real server. Proxy servers have two main purposes. Proxy servers can dramatically improve performance for groups of users. This is because it saves the results of all requests for a certain amount of time. proxy servers also Filter Requests to block or disallow specific types of outgoing or incoming requests to the server.
Different Servers for Different Jobs
The type of system you would choose for a server depends mainly on its application within your organization; how much data it will be responsible for storing and retrieving, the number of user requests that you expect will be sent to the server, and how many clients will be accessing the server are all things you will need to consider before choosing a server architecture and software package.
A server is not just one piece of equipment that has the capability to perform a specific task. From a basic file/print server, you could be looking at something as common as a Dell PC with 512MB of memory running Windows or Linux and configured to queue network printing. An example of midrange server, used for more intensive database storage and retrieval functions is HP's 900 midrange server with 32 PCI slots and 256GB of memory to handle the extra workload.
As you can see from this introduction to the many types and configurations of servers, the term is probably one of the most ambiguous tech terms you'll ever come across.
A server is not just one piece of equipment that has the capability to perform a specific task. From a basic file/print server, you could be looking at something as common as a Dell PC with 512MB of memory running Windows or Linux and configured to queue network printing. An example of midrange server, used for more intensive database storage and retrieval functions is HP's 900 midrange server with 32 PCI slots and 256GB of memory to handle the extra workload.
As you can see from this introduction to the many types and configurations of servers, the term is probably one of the most ambiguous tech terms you'll ever come across.
About SQL Server
Microsoft SQL Server is a Relational Database Management System (RDBMS) designed to run on platforms ranging from laptops to large multiprocessor servers. SQL Server is commonly used as the backend system for websites and corporate CRMs and can support thousands of concurrent users.
SQL Server comes with a number of tools to help you with your database administration and programming tasks.
SQL Server is much more robust and scalable than a desktop database management system such as Microsoft Access. Anyone who has ever tried using Access as a backend to a website will probably be familiar with the errors that were generated when too many users tried to access the database!
Although SQL Server can also be run as a desktop database system, it is most commonly used as a server database system.
Server Database Systems
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.
For example, a website could store all its content in a database. Whenever a visitor views an article, they are retrieving data from the database. As you know, websites aren't normally limited to just one user. So, at any given moment, a website could be serving up hundreds, or even thousands of articles to its website visitors. At the same time, other users could be updating their personal profile in the members' area, or subscribing to a newsletter, or anything else that website users do.
Generally, it's the application that provides the functionality to these visitors. It is the database that stores the data and makes it available. Having said this, SQL Server does include some useful features that can assist the application in providing its functionality.
SQL Server comes with a number of tools to help you with your database administration and programming tasks.
SQL Server is much more robust and scalable than a desktop database management system such as Microsoft Access. Anyone who has ever tried using Access as a backend to a website will probably be familiar with the errors that were generated when too many users tried to access the database!
Although SQL Server can also be run as a desktop database system, it is most commonly used as a server database system.
Server Database Systems
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.
For example, a website could store all its content in a database. Whenever a visitor views an article, they are retrieving data from the database. As you know, websites aren't normally limited to just one user. So, at any given moment, a website could be serving up hundreds, or even thousands of articles to its website visitors. At the same time, other users could be updating their personal profile in the members' area, or subscribing to a newsletter, or anything else that website users do.
Generally, it's the application that provides the functionality to these visitors. It is the database that stores the data and makes it available. Having said this, SQL Server does include some useful features that can assist the application in providing its functionality.
SQL Server - SQL Query Analyzer
The SQL Query Analyzer is the main interface for running SQL queries against your database. You can use the SQL Query Analyzer to create and run adhoc scripts, or you can create SQL scripts and save them for later use.
SQL Server - Views
In SQL Server, a view is a pre-written query that is stored on the database. A view consists of a SELECT statement, and when you run the view, you see the results of it like you would when opening a table. Some people like to think of a view as a virtual table. This is because a view can pull together data from multiple tables, as well as aggregate data, and present it as though it is a single table.
Benefits of Views
A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all of the data). Views can do the following:
Restrict access to specific rows in a table
Restrict access to specific columns in a table
Join columns from multiple tables and present them as though they are part of a single table
Present aggregate information (such as the results of the COUNT function)
Benefits of Views
A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all of the data). Views can do the following:
Restrict access to specific rows in a table
Restrict access to specific columns in a table
Join columns from multiple tables and present them as though they are part of a single table
Present aggregate information (such as the results of the COUNT function)
SQL Server - Stored Procedures
Stored procedures are a powerful part of SQL Server. They can assist programmers and administrators greatly in working with the database configuration and its data.
A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database (under the "Stored Procedures" node). Programmers and administrators can execute stored procedures either from the Query Analyzer or from within an application as required.
Transact-SQL, which is based on SQL (Structured Query Language), is the programming language used to interface between applications and their databases. Transact-SQL is a relatively easy language to learn and I highly recommend becoming familiar with it.
A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database (under the "Stored Procedures" node). Programmers and administrators can execute stored procedures either from the Query Analyzer or from within an application as required.
Transact-SQL, which is based on SQL (Structured Query Language), is the programming language used to interface between applications and their databases. Transact-SQL is a relatively easy language to learn and I highly recommend becoming familiar with it.
Remote Servers
The Remote Servers option allows you 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. If you need to execute stored procedures against a remote server, use a linked server.
Linked Servers
The Linked Servers option allows you to connect to another instance of SQL Server running on a different machine, perhaps remotely in a different city/country. This can be useful if you need to perform distributed queries (query a remote database). Setting up a linked server is quite straight forward in Enterprise Manager, all you need is details of the remote server, and the database that you need to query.
Server Roles
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.
Here are the server roles defined in SQL Server during setup:
Server Role Description
sysadmin Can perform any task in SQL Server.
serveradmin Can set server-wide configuration options, can shut down the server.
setupadmin Can manage linked servers and startup procedures.
securityadmin Can manage logins and database permissions, read logs, change passwords.
processadmin Can manage processes running in SQL Server.
dbcreator Can create, alter, and drop databases.
diskadmin Can manage disk files.
bulkadmin Can execute BULK INSERT statements.
As you can see, some of these roles allow very specific tasks to be performed. If you don't have many technical users, it's likely that you'll only use one or two of these roles (including sysadmin).
Here are the server roles defined in SQL Server during setup:
Server Role Description
sysadmin Can perform any task in SQL Server.
serveradmin Can set server-wide configuration options, can shut down the server.
setupadmin Can manage linked servers and startup procedures.
securityadmin Can manage logins and database permissions, read logs, change passwords.
processadmin Can manage processes running in SQL Server.
dbcreator Can create, alter, and drop databases.
diskadmin Can manage disk files.
bulkadmin Can execute BULK INSERT statements.
As you can see, some of these roles allow very specific tasks to be performed. If you don't have many technical users, it's likely that you'll only use one or two of these roles (including sysadmin).
Logins
SQL Server allows you to configure users and roles for anyone who needs to access SQL Server or any of its databases and their objects. Typically, you will have a database administrator who has access to everything. Then you will have users with varying levels of access, depending on the tasks they're allowed to perform.
Doing this can prevent inexperienced users from wreaking havoc on your database environment. Imagine if one morning you came to work, only to find that someone had accidentally deleted your main database!
In Enterprise Manager, you can use SQL Server Security section to restrict the tasks that each user can perform. If you have many users, you can assign them a role. Roles enable you to assign the same access rights across many users. Instead of assigning permissions against an individual user (or 'login'), you assign them against a role. You can then add users/logins into that role.
More about logins in later.
Doing this can prevent inexperienced users from wreaking havoc on your database environment. Imagine if one morning you came to work, only to find that someone had accidentally deleted your main database!
In Enterprise Manager, you can use SQL Server Security section to restrict the tasks that each user can perform. If you have many users, you can assign them a role. Roles enable you to assign the same access rights across many users. Instead of assigning permissions against an individual user (or 'login'), you assign them against a role. You can then add users/logins into that role.
More about logins in later.
SQL Server Security
SQL Server has a number of security features that assist database administrators in maintaining their database in a secure way. You can see the areas of security by expanding the "Security" folder within Enterprise Manager.
SQL Server - DTS
You may occasionally find yourself in the situation where you need to transfer a lot of data into your SQL Server database from another database or spreadsheet. Or, perhaps you need to export data from your database into another database or spreadsheet. In SQL Server, DTS makes this an easy task.
DTS (Data Transformation Services) is a set of graphical tools that allows you to transfer data between disparate sources into one or more destinations.
DTS Tools
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.
DTS (Data Transformation Services) is a set of graphical tools that allows you to transfer data between disparate sources into one or more destinations.
DTS Tools
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.
SQL Server Service Manager
SQL Server Service Manager is used to start, stop, and pause the various components on SQL Server. These components run as Windows services.
The following services can be stopped/started/paused via the SQL Service Manager:
Service Description
SQL Server service Implements the SQL Server database engine. There is one SQL Server service for each instance of SQL Server running on the computer.
SQL Server Agent service Implements the agent that runs SQL Agent jobs and alerts. There's one SQL Agent service for each instance of SQL Server running on the computer.
Microsoft Search service Implements the full-text search engine. There is only one full-text search engine regardless of how many instances of SQL Server are running on the computer.
MSDTC service Manages distributed transactions. There is only one MSDTC service regardless of how many instances of SQL Server are running on the computer.
MSSQLServerOlAPService service Implements the SQL Server Analysis Services. There is only one MSSQLServerOlAPService service regardless of how many instances of SQL Server are running on the computer.
The following services can be stopped/started/paused via the SQL Service Manager:
Service Description
SQL Server service Implements the SQL Server database engine. There is one SQL Server service for each instance of SQL Server running on the computer.
SQL Server Agent service Implements the agent that runs SQL Agent jobs and alerts. There's one SQL Agent service for each instance of SQL Server running on the computer.
Microsoft Search service Implements the full-text search engine. There is only one full-text search engine regardless of how many instances of SQL Server are running on the computer.
MSDTC service Manages distributed transactions. There is only one MSDTC service regardless of how many instances of SQL Server are running on the computer.
MSSQLServerOlAPService service Implements the SQL Server Analysis Services. There is only one MSSQLServerOlAPService service regardless of how many instances of SQL Server are running on the computer.
SQL Profiler
SQL Profiler is a handy tool that enables you to monitor events within your SQL Server (or more specifically, a SQL Server instance). For example, you could use SQL Profiler to check the performance of a stored procedure. You could also use SQL Profiler to troubleshoot a problem that's occuring in your production environment.
When using SQL Profiler, you can save the output to a "trace file" so that you can later analyze the events that occured during your trace.
When using SQL Profiler, you can save the output to a "trace file" so that you can later analyze the events that occured during your trace.
SQL Server Tutorial
SQL Server Summary
SQL Server is a powerful application, and this tutorial has provided an overview of the main concepts involved in administering SQL Server.
To recap briefly, we've:
created databases
created database tables
inserted data into those tables
used the Query Analyzer to run a query
created a login account
created and executed a DTS package
executed that same DTS package via a scheduled job
and more
What Next?
The most logical next step from learning SQL Server is to learn SQL itself (if you haven't already done this that is). SQL stands for Structured Query Language and enables you to perform database related tasks programatically. Most of the tasks in this tutorial can be done programatically using SQL.
Probably the most common use for SQL is to work with the data in the database. For example, to insert data, select data, update data, or to delete data. Any database driven website will have many SQL statements doing this stuff.
SQL Server is a powerful application, and this tutorial has provided an overview of the main concepts involved in administering SQL Server.
To recap briefly, we've:
created databases
created database tables
inserted data into those tables
used the Query Analyzer to run a query
created a login account
created and executed a DTS package
executed that same DTS package via a scheduled job
and more
What Next?
The most logical next step from learning SQL Server is to learn SQL itself (if you haven't already done this that is). SQL stands for Structured Query Language and enables you to perform database related tasks programatically. Most of the tasks in this tutorial can be done programatically using SQL.
Probably the most common use for SQL is to work with the data in the database. For example, to insert data, select data, update data, or to delete data. Any database driven website will have many SQL statements doing this stuff.
Subscribe to:
Posts (Atom)