- Introduction to SQL Server 2000
- Availability Features
- Environmental/Hardware Features
- Database Engine
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Availability Features
With the introduction of the Internet to "web-enable" businesses, demand for service has increased exponentially. Customers expect to be able to browse and order products online at any time—when they're at home in the morning reading the newspaper, or even at 2 a.m. when they should be in bed asleep!
Whatever the individual circumstances of the customer, they all want reliable, efficient service at all hours. Recognizing these demands, Microsoft provides a number of key features in SQL Server 2000 to assist businesses in delivering highly available and fast applications.
NOTE
Some of the features discussed in this article are not available with all editions of SQL Server 2000 and Windows 2000. You may need to purchase Windows 2000 Advanced Server or Windows 2000 DataCenter in conjunction with SQL Server 2000 Enterprise Edition.
Clustering Support
Clustering is not a new concept in the RDBMS world; in fact, some of the first-ever clusters were developed by Digital Equipment Corporation (DEC), and Oracle Corporation utilized these clusters as early as 1987!
However, this is the first time that Microsoft has implemented clustering inside SQL Server as a feature that's easy to implement out of the box. SQL Server v7.0 supported clustering, although it definitely wasn't as easy to implement in that version as in SQL Server 2000.
There are two main types of clustering in SQL Server 2000:
Active/active clustering. In this type of clustering, an instance of SQL Server 2000 runs on each node (server) in the cluster, and actively serves requests. If a node fails, another node in the cluster takes over processing the failed node's work, as well as its own. As you can imagine, this has a performance impact on your servers and applications.
Active/passive clustering. Suppose you have a two-node cluster, with one of the nodes actively servicing requests while the other node sits idle. If the active node fails, the second node takes over the workload of the first node and continues processing while the first node is taken out of service and repaired. This avoids the performance impact of active/active clustering, but is more expensive because hardware sits idle.
Federated Database Servers
To improve the performance of large-scale enterprise applications, SQL Server 2000 incorporates distributed partitioned views (which are updatable), allowing your data to be horizontally partitioned over many different servers, sometimes known as groups or federations. For example, in a four-server federation, with a 10,000 row table you might have the following arrangement:
Server A contains records 1–2,500
Server B contains records 2,501–5,000
Server C contains records 5,001–7,500
Server D contains records 7,501–10,000
When a user submits a query to the distributed view, SQL Server queries the appropriate server for the requested data.
While this scheme isn't load balancing, it offers enterprises a simple and easy way to scale out their RDBMS solution. To provide more processing power, you can add another server and change the indexes to incorporate the new server.
NOTE
Each server in the federation is treated independently, and therefore can contain other data or databases without reliance or dependence on any other server within the federation.
Transaction Log Shipping
To further help provide reliable and highly available solutions, Microsoft has implemented transaction log shipping, which allows a company to keep a "warm" standby server on hand. As transactions are performed against the database, they're written to the log file (usually an .ldf extension). These transaction log files are "shipped" (sent) to a second instance/server of SQL Server 2000 at a time period specified by the database administrator. Suppose an update occurs to Table A on Server A. After a specified time has elapsed (such as 15 minutes), the same update to Table A occurs on Server B. In this example, if Server A fails, Server B can be rolled into production with only a 15-minute period of data latency.
Replication
Just as its predecessors did, SQL Server 2000 fully supports replication. Replication allows administrators to keep the data and database objects from multiple servers in sync, with a lower administrative overhead.
Replication may be configured for any of the following settings:
Snapshot. As the name suggests, this is a snapshot of the data at a specific point in time. The data for a subscriber to the replication publisher is refreshed in its entirety. The subscriber doesn't need to monitor data changes. This is generally used for a one-way replication scenario, and where read-only data is required by the subscriber.
Transactional. With transactional replication, the subscriber receives an initial snapshot of the data and objects, and then updates as the data changes. This requires the subscriber to be notified of changes, which then must be applied as required. This method is generally used in a one-way replication scenario.
Merge. Merge replication allows several instances of SQL Server to interact autonomously. When the data changes within one instance of SQL Server, the other instances are notified and accept the changes, and vice versa. All servers can collect data and act upon the changes independently. This type of replication requires more overhead to manage and can result in more data conflicts than other types of replication. This is generally used for a two-way replication scenario.
These prominent availability features of SQL Server 2000 allow many organizations to provide reliable and scalable solutions for their clients. Now let's take a look at SQL Server 2000 integration by examining the environmental features of the RDBMS.