Chapter 3. Getting Started with SQL Server 2000

IN THIS CHAPTER

• Setting Up and Running Microsoft SQL Server 2000

• Getting Started with SQL Server 2000: The Basics

In the past, many Visual Basic programmers got their first exposure to database programming through the Jet database engine shared by Visual Basic and Microsoft Access. As soon as database applications grew beyond a few hundred records or a few users, programmers commonly ran into limitations. Multiuser contention for data, poor performance, and lack of advanced data and server-management features caused many programmers to turn to an alternative architecture to resolve their database problems. That architecture is client-server (or distributed) computing.

Client-server computing is not to be confused with multiuser computing, which Jet supports just fine. In a multiuser architecture, a number of users share the same data over a network. That is, the database file or files reside on a central server, which all the user workstations can access. The key is that in an architecture such as Jet, which is not a client-server architecture, all the processing is done on the client workstations. That is, in order to retrieve a single row defined by an SQL SELECT statement from a table containing 50,000 rows, all the rows (or at least their indexes) must first be transferred to the client workstation. No intelligence exists on the other side of the network that can process requests and returning data.

However, client-server architecture has some sort of back end not the body part of a programmer who sits in a chair for 18 hours a day but rather a piece of software responsible for retrieving and caching data, arbitrating contention between multiple users, and handling security. This software, Microsoft SQL Server, for example, receives requests from client workstations, executes the requests on the server computer, and then returns only the results to the client machine. Thus, in the case of requesting a single row from a table with 50,000 rows of data, the SELECT statement is transferred to the server, the server's database software executes the statement and then returns the single row to the client. The savings in network traffic is obvious; another performance benefit is that server machines are usually stronger (that is, faster CPUs and more memory) than client machines, so the actual statement execution and retrieval of the data are faster.

If you're using Visual Basic.NET (VB.NET), Microsoft SQL Server is your obvious choice for a database back end. Not only is it a powerful and easy-to-use database system, but a copy of SQL Server also is included with every edition of VB.NET and Visual Studio.NET (VS.NET). We clarify which editions of SQL Server are included with which editions of VB.NET and VS.NET, after describing the different SQL Server editions.

TIP

You should avoid using Jet (MDB) databases in anything but the simplest or most memory-limited applications. Introduction of SQL Server 2000 Desktop Engine (MSDE) eliminates the need to use Jet databases for prototyping and/or low-cost systems. By using a freely distributable, SQL Server-compatible database right from the start, you will never need to make query, code, or design changes when your system needs to "grow up."

In this chapter we focus on getting started with SQL Server 2000. Our intention is to give you a whirlwind introduction to setting up and using SQL Server to prepare you for the material and examples in the remainder of this book. If you're new to SQL Server, the material in this chapter should be enough to get you started and comfortable with that server. If you're familiar with SQL Server, you may still find this chapter to be a useful refresher, and you may even learn one or two new things as well. The following is a typical scenario.

Say that you're working as the member of a client-server or distributed development team. You have a database server that is 95 percent functional which is to say that it isn't really functional at all. You still need to get your work done, but the server component of the application just isn't "ready for prime time."

What's more, you may have only one or two server-side programmers at your disposal. Because server-side programmers tend to have the most rarefied set of skills, this situation tends to happen often in client-server development organizations. They're the hardest kind of programmer for companies to hire and retain, and as a consequence, they can be the most stressed-out bunch of people you'd ever encounter. Consequently, they are the hardest to get hold of when something goes wrong. Moreover, client-side programmers often can't get their work done until server-side programmers fix what's wrong with the server.

This is The Drama of the Gifted Server Programmer.

If you've ever been in a distributed development project involving more than two developers, you'll understand this drama. One solution is to prototype your client-side application, using a mocked-up Jet data source first and then hooking up your application to the server when it is ready. Designating an ODBC data source or using an OLE DB data link are two easy ways to do that. The layer of abstraction offered by ODBC or OLE DB permits you to easily create and use a prototype version of the database in your application, switching to the "live" database whenever you want.

Placing one or more layers of abstraction between the client and the server also keeps client-side programmers from overburdening the server-side programmer. For the server-side programmer, that means exposing views or stored procedures that provide data services to clients; for VB.NET programmers, it means creating code components that do much the same thing. For information on strategies involving stored procedures and views, see the sections Creating and Running Stored Procedures and Using Database Views to Control Access to Data later in this chapter.