Database Access with Visual Basic .NET (3rd Edition)

Running a true database server is a significant departure from sharing a Microsoft Jet database file. You have new concepts to learn and new things to worry about. However, SQL Server 2000 is much easier to set up and maintain than previous versions, especially version 6.5 and earlier.

In this section we get you started with the minimum required to get a database up and running under SQL Server 2000, which actually comes in several different editions:

• SQL Server 2000 Standard Edition Basic database server, appropriate for a workgroup or department.

• SQL Server 2000 Enterprise Edition Includes all features of Standard Edition and offers added performance and other features required to support the largest enterprises, Web sites, and data warehousing applications.

• SQL Server 2000 Personal Edition Appropriate for mobile users who are often disconnected from their networks but need SQL Server as their local data store and for running stand-alone applications on a client-workstation computer using SQL Server. Unlike the Standard and Enterprise Editions, which require a server version of Windows NT or Windows 2000, the Personal Edition can also be run on Windows 2000 Professional, NT 4.0 Workstation, and Windows ME or 98. This edition limits the server's performance when more than five batches are being executed at the same time.

• SQL Server 2000 Developer Edition Includes all the features of the Enterprise Edition but is licensed only to developers who are developing and testing SQL Server applications and may not be used as a production server.

• SQL Server 2000 Desktop Engine (MSDE) Provides most of the functionality of the Standard Edition. This component may be freely distributed as part of small applications or demo versions. The size of the Desktop Engine database is limited to 2 GB, and like the Personal Edition, its performance is limited when more than five batches are being executed at the same time. However, it doesn't include any of the graphical development or managerial tools.

  Note

  Every edition of VB.NET or VS.NET includes the MSDE edition of SQL Server 2000. The Enterprise Developer and Enterprise Architect editions of Visual Studio also include the Developer Edition of SQL Server 2000.

  Keep in mind the following important points.

  • MSDE doesn't include the SQL Server graphical tools described in this chapter. Thus you won't actually be able to perform the demonstrations and samples illustrated (you do, however, have some limited graphical data tools to access MSDE within the VS.NET development environment).

  • The Developer Edition of SQL Server licenses you for development only. To create a production application with SQL Server, you must obtain the required server and client access licenses for SQL Server 2000.

• SQL Server 2000 Windows CE Edition Used as the data store on Windows CE devices and capable of replicating data with any of the other SQL Server 2000 editions.

Determining Installation Requirements for SQL Server 2000

To install SQL Server 2000, Microsoft says that you'll need a Pentium (or compatible) processor running at a minimum of 166 MHZ, 95 to 270 MB of hard disk space (250 MB typical, 44 MB for the Desktop Engine), a CD-ROM drive, Internet Explorer 5.0 or later, and a supported operating system. The memory (RAM) requirements are as follows:

• Standard Edition 64 MB minimum

• Enterprise Edition 64 MB minimum, 128 MB recommended

• Personal Edition 64 MB minimum on Windows 2000; 32 MB minimum on other operating systems

• Personal Edition 64 MB minimum on Windows 2000; 32 MB minimum on other operating systems

• Developer Edition 64 MB minimum

• Desktop Engine 64 MB minimum on Windows 2000; 32 MB minimum on other operating systems

If you've actually tried to run SQL Server on a 166 MHZ processor with 64 MB of memory, please try to stop laughing and resume reading now. These specifications are minimum requirements. SQL Server may very well run on a machine this anemic, but in the real world the minimum requirement is the biggest, baddest computer you can realistically afford. It is supposed to be the computer that runs your entire business; scrimping on the hardware will only cause you grief later. If there's one area you want to consider maxing out on your computer, it's memory. In practice, if you have a limited budget, you are usually better off investing in additional memory than in additional CPU speed. A modest memory upgrade can go a long way in improving your system's performance.

Note

Because this book is designed to be a survey of database-oriented solutions in VB.NET, we don't explore every SQL Server feature. The SQL Books Online documentation that comes with SQL Server is the best source for this detailed information. If you're looking for a book that is more tutorial in nature, check out Microsoft SQL Server 2000 DBA Survival Guide by Spenik and Sledge (Sams Publishing).

Installing SQL Server 2000

After you've designated a computer for use with SQL Server, you can proceed with installation. It is fairly straightforward, with a few minor exceptions.

• It takes a long time.

• It asks you a lot of weird questions that most conventional applications don't ask.

We can't help you with the fact that it takes a long time, but we can give you some pointers about the questions posed by SQL Server's setup application.

In general, and certainly for simple developmental configurations, you should accept the default options that are offered by the dialog pages of the setup wizard. The following comments refer to the dialogs that require a bit more thought.

In the Setup Type dialog box shown in Figure 3.1, you get to choose among typical, minimum, and custom setups, as well as the paths to the folders for the SQL Server programs and data files. Be sure that you have enough disk space on the drive where you store the data files and that they are on a path that is regularly backed up.

In the Services Accounts dialog shown in Figure 3.2, the default is a Domain User account, but you may want to use the Local System account if you aren't on a domain or have your own dedicated development server machine. On this dialog page you can determine whether SQL Server should start automatically when Windows is started. If you select this option, bear in mind that SQL Server will be started as a service from Windows. Services Accounts act as if they're part of the operating system; they don't appear in the Task Manager, and they can't be shut down like normal applications can. In the next section we give more information on how to manage a service running under Windows, but you might also see the Controlling the Way SQL Server Starts Up section later in this chapter.

For a production server, it is preferable to use the default Windows Authentication Mode shown in Figure 3.3. This mode takes advantage of the existing Windows NT/2000 user account and security mechanisms. When an attempt to connect to the SQL Server is made, it uses the user's account information to authenticate her and, if the user (or her group) has been granted access to the SQL Server, she is in. This approach is simple and provides a single location for managing user accounts and groups.

In some situations it may be necessary to use Mixed Mode. In addition to enabling Windows Authentication, Mixed Mode also allows SQL Server Authentication. The latter requires the definition of user accounts within SQL Server, against which login attempts are tested. The main advantage of this mode is that it doesn't require a trusted connection between the server and the connecting workstation, making it the mode of choice if UNIX or Web clients are accessing the database. However, it does require additional work and redundant account management (Windows accounts and SQL Server accounts).

Note

Often, you will find it convenient to configure a development machine in Mixed Mode so that you can simply use the preinstalled sa (system administrator) account. Just be sure to develop a more robust and secure approach for your production machine at the very least, be sure to assign a good password to the sa account!

Starting and Stopping SQL Server with SQL Service Manager

The SQL Service Manager is used to start and stop SQL Server. You use it in situations where you need to take down the server to perform certain tasks, or if you just don't want to run SQL Server on a particular machine (on a development machine, for example).

You don't have to stop SQL Server under normal circumstances, which goes along with SQL Server's role as an enterprise database system. The idea is that you're supposed to start it up and leave it running all the time, come heck or high water. Yet, in certain rare instances, you must stop the server to perform certain tasks, such as changing configuration options on the server or performing a hardware upgrade to the computer on which the server resides. When one of these situations comes up, use SQL Service Manager to take down SQL Server and bring it back up again.

SQL Service Manager doesn't have to be running for SQL Server to do its work. The SQL Service Manager exists merely to give you control over the activation and deactivation of your server. After your server is in production mode, you probably won't often use SQL Service Manager.

When you launch it (by selecting its icon in the SQL Server program group), SQL Service Manager looks like the window shown in Figure 3.4.

If SQL Server is running, the status indicator is a green arrow; if it's not running, the indicator is a red square. To start SQL Server, click on the Start/Continue button; to stop it, click on the Stop button. It really is easier than making toast.

Controlling the Way SQL Server Starts Up

After you set up SQL Server, the operating system automatically launches it when your server computer is started. Through the Services control panel, you can control whether SQL Server always starts when your computer starts. To view the current state of SQL Server and control how it runs when your computer is started, follow these steps.

1. Launch the Windows Control Panel. Select Administrative Tools from the Control Panel.

2. Select Services from the Administrative Tools.

3. The Services control panel appears. Scroll through the list of services until you find MSSQLServer.

If you just installed SQL Server on your machine, the MSSQLServer service status is Started and its start-up is Automatic. To stop the MSSQLServer service from the Services control pane:

1. Click on the Stop button

2. After a few seconds, SQL Server is stopped

3. To restart SQL Server, click on the Start button in the Services control panel

Note

Starting and stopping SQL Server by using the Services control panel is essentially the same as starting and stopping it from the SQL Service Manager, albeit less colorful.