SQL Server 2000 – Particulars and History So you want to learn something about databases – SQL Server in particular? That's great because databases are pervasive – they are everywhere, though you may not have really thought about this up until now. In this chapter, we'll be looking at some of the different varieties and brands of databases available both today and throughout history.
Chapter 1 A Brief History of Databases SQL Server is an RDBMS – or Relational Database Management System. RDBMS systems are at the pinnacle of their popularity at the moment. Using an RDBMS as the basis for data storage is plainly "the way it's done" for most applications nowadays – but it wasn't always this way. In this section, we're going to take a look back in time and examine some of the other databases used in the past.
SQL Server 2000 – Particulars and History ❑ RDBMS systems: Data for the masses, but with much better data integrity. These systems do more than just store and retrieve data. They can be thought of as actually caring for the integrity of the data. Whereas VSAM and ISAM databases typically store data very well, the database itself has no control over what goes in and out (OK, Access has some, but not like a true RDBMS).
Chapter 1 Use ANSI compliance not as a religion but, rather, where it makes sense. Go for ANSI code where it means little or no difference in performance (such as with queries), but also don't be afraid to make judicious use of specialized features that may offer some functionality or performance gain that ANSI can't give you. Just document these areas where you use them so that, if you are faced with porting to a new RDBMS, you know where to look for code that may not run on the new system.
SQL Server 2000 – Particulars and History System/Subsystem Description Editions Full-Text Search This is an optional part of the main installation. If you want this functionality, you need to actively choose it – it's not installed by default. Full-Text Search provides the functionality to support more robust word lookups. If you've used an Internet search engine and been left in awe of the words and phrases that you can find, Full-Text Search is the tool for you.
Chapter 1 System/Subsystem Description Editions Data Transformation Services Data Transformation Services (DTS) has expanded enormously in SQL Server 2000. A great range of different functionality in transforming data, either within a database, or transferring information in or out, is now available, including the ability to customize tasks and workflows.
SQL Server 2000 – Particulars and History Windows CE Edition The Windows CE Edition will be used on Windows CE devices. It will be extremely limited in its functionality as, obviously, these devices have an extremely limited capacity. Applications using Windows CE and SQL Server are still quite limited at present and it's really only possible to have any sort of useful application built on the more expensive CE products.
Chapter 1 Standard Edition The Standard Edition is the mainstream edition of SQL Server. This is the edition that's going to be installed for the majority of SQL Server users. The Standard Edition supports multiprocessing with up to four CPUs and 2GB of RAM. However, it doesn't support some of the more advanced features. For example, only a subset of the Analysis Services features is supported. You need to purchase a separate license for each Standard Edition instance you install on a machine.
SQL Server 2000 – Particulars and History Hardware and OS Requirements The stated minimum hardware requirements for SQL Server are pretty easy to reach these days: ❑ Pentium 166 or better (Alpha is no longer an option, and Microsoft has stated that there will be no future development for that platform). ❑ At least 64MB memory, although 128MB is recommended for the Enterprise Edition.
Chapter 1 I have a definite belief in the power and flexibility of the n-tier approach we'll be talking about shortly – but don't believe for a minute that I think it's the only solution. The moment you let yourself be backed into thinking one approach is the right one for everything, will be the moment that you start turning out sub-standard work.
SQL Server 2000 – Particulars and History 2-Tier Architecture (Client-Server) 2-tier, or client-server systems, first started becoming popular in the early 90's. There were actually two sub-types to this architecture: client-centric (smart client) and server-centric (smart server).
Chapter 1 Server-Centric This lives on the notion that computing power is cheaper in PCs than in host systems, but tries to gain some of the advantages of centralized systems. Only user services are distributed to the client. Only information that actually needs to be displayed on the screen is sent to the client. Business and data services remain at the server. Network bandwidth is far more host system like.
SQL Server 2000 – Particulars and History Three-Tier This model, and the closely related one that follows (n-tier), are the much-hyped architectures of today. If you hear someone talking about how everything needs to be done one way regardless of what it is – they are almost certainly talking three-tier or n-tier computing.
Chapter 1 Advantages Disadvantages Allows for (actually encourages) component-based development, which can increase reusability. There is a much greater need for security and infrastructure. For example, MTS/COM+ for the whole process of looking after your COM modules, or MSMQ if you are using any sort of messaging. Two medium servers are often cheaper than one large server. The separation of business and data services makes two servers an option.
SQL Server 2000 – Particulars and History Advantages Disadvantages Even more upgrades can be done entirely at the server level. Often increases the number of network connections (which are frequently the slowest and most unreliable part of your system). An increasing number of homogeneous products are available off-the-shelf – pre-made software is cheap. Typically there is still considerably more downtime than with a host system.
Chapter 1 Data Access Models Certainly one of the biggest issues to deal with these days is how to access your database within the various options in Microsoft's "Alphabet Soup" of data access architectures. Which models are available in what circumstances depends primarily on the version of SQL Server, and the choice and version of your programming language. There are four different access models that Microsoft considers as being current for accessing SQL Server. These are: ❑ ADO: ActiveX Data Objects.
SQL Server 2000 – Particulars and History In addition to these current object models, there are a few others that you may run across and should know about: ❑ RDO: Remote Data Objects. This was the speed leader for a couple of years. DAO and ODBC used to be the only options for VB programmers. DAO was easier than ODBC to develop in, but it was slow, and the object model was still too deep and complex. ODBC on the other hand, was fast, but required tons of code just to get ready to make a connection.
Chapter 1 Microsoft Certification There are three SQL Server related exams that participate in different certifications offered by Microsoft. These include exams on development, administration, and data warehousing. Note that, at the time of writing, there are only exams for SQL Server 7.0. The SQL Server 2000 exams are due to go to Beta in January 2001. If you are interested in becoming certified visit http://www.microsoft.com/ technet/training/default.
SQL Server 2000 – Particulars and History 27
Chapter 1 28
