MCTS: Microsoft SQL Server 2005 Implementation and Maintenance: Study Guide: Exam 70-431 [With CD-ROM] - Softcover

Jorden, Joseph L.; Weyn, Dandy

Softcover

ISBN 10: 0470025654 ISBN 13: 9780470025659

Verlag: John Wiley & Sons, 2006

Inhaltsangabe

With the release of SQL Server 2005, Microsoft is introducing a new multi-exam certification program. The Microsoft SQL Server 2005 Implementation and Maintenance exam (70-431) is the first stop for everyone entering this new certification track, and serves as both a single exam certification as well as the entry exam for the MCITP-level certifications. This book provides an introduction to the development and administrative aspects of SQL Server 2005 and features practical guidance for all aspects of the exam. Get a great start in your certification process today with this comprehensive guide which includes: * Hundreds of challenging practice questions plus two bonus exams * Leading-edge exam preparation software, including a test engine Authoritative coverage of all key exam objectives, including: * Installing and Configuring SQL Server 2005 * Implementing High Availability and Disaster Recovery * Supporting Data Consumers * Maintaining Databases * Monitoring and Troubleshooting SQL Server Performance * Creating and Implementing Database Objects Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Über diesen Titel

Über die Autorin bzw. den Autor

Joseph L. Jorden, MCTS, MCSE, has been administering and developing databases for the last twelve years. Currently he is the Lead Developer for the Jelly Belly Candy Company. Dandy Weyn, MCTS, MCSE, MCSA, MCDBA, MCDST, MCITP, is an independent SQL Server technologist based in Belgium, who delivers training and consultancy all over the world. He is a frequent speaker at Microsoft seminars and has more than ten years experience with relational databases in enterprise environments.

Von der hinteren Coverseite

With the release of SQL Server 2005, Microsoft is introducing a new multi-exam certification program. The Microsoft SQL Server 2005

Implementation and Maintenance exam (70-431) is the first stop for everyone entering this new certification track, and serves as both a single exam certification as well as the entry exam for the MCITP-level certifications. This book provides an introduction to the development and administrative aspects of SQL Server 2005 and features practical guidance for all aspects of the exam.

Get a great start in your certification process today with this comprehensive guide which includes:

• Hundreds of challenging practice questions plus two bonus exams
• Leading-edge exam preparation software, including a test engine

Authoritative coverage of all key exam objectives, including:

• Installing and Configuring SQL Server 2005
• Implementing High Availability and Disaster Recovery
• Supporting Data Consumers
• Maintaining Databases
• Monitoring and Troubleshooting SQL Server Performance
• Creating and Implementing Database Objects

Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

MCTS: Microsoft SQL Server 2005 Implementation and Maintenance Study Guide

Exam 70-431By Joseph L. Jorden Dandy Weyn

John Wiley & Sons

Copyright © 2006 John Wiley & Sons, Ltd
All right reserved.
ISBN: 978-0-470-02565-9

Chapter One

Installing Microsoft SQL Server 2005

MICROSOFT EXAM OBJECTIVES COVERED IN THIS CHAPTER:

  Verify prerequisites.

  Upgrade from an earlier version of SQL Server.

  Create an instance.

Remember the first time you bought a bicycle? You probably just got a box full of bicycle parts from the store with a label on the front that read "some assembly required." If you're like most people, you probably set the instruction booklet somewhere on the floor and just started picking out parts that looked like they should fit together. In the end, you probably had something that didn't even remotely resemble the bicycle you saw on the showroom floor and an overpowering desire to read the assembly instructions.

SQL Server 2005 should have a label right on the box that says "some assembly required" just to remind you to read the instructions first, not last. Just like with the first bicycle you bought, with SQL Server if you read the instructions after the install, you will end up with a mess. This mess is not easy to clean up, though; in some instances, you may even need to reinstall SQL Server.

In this chapter, we will present the instructions for installing SQL Server 2005 so that you need do it only once. We'll start by covering the prerequisites, explaining the required hardware and software that need to be in place before you begin the install procedure. Then we'll move into installing SQL Server, covering each step of the Setup Wizard and pointing out topics that require special attention. Since you might be upgrading from a previous version of SQL Server, we'll also walk you through the upgrade process. Finally, since not all installs go perfectly, we'll provide some troubleshooting techniques to ensure that SQL Server gets up and running.

Meeting the Prerequisites

You will need a few pieces in place on your machine before you will be able to install SQL Server 2005, the first of which is Internet Explorer (IE) 6.0 Service Pack 1 (SP1) or newer. Many people see this requirement and instantly think SQL Server requires IE to serve data. That is not the case. The only parts of SQL Server 2005 that require IE are the Microsoft Management Console (discussed later in this book) and Books Online (BOL).

You must also be certain your machine meets the minimum hardware requirements before you can install SQL Server 2005. Otherwise, SQL Server may run very slowly, or not at all. Each edition of SQL Server has a different set of hardware requirements. Table 1.1 lists the hardware requirements for the Express Edition, Table 1.2 lists the Workgroup Edition requirements, and Table 1.3 lists the Standard Edition, Developer Edition, and Enterprise Edition requirements.

At this point you are probably wondering why there are so many versions of SQL Server 2005 and which one is right for you. The following discussion compares the versions and shows you what each edition does:

Express Edition Express Edition supports one central processing unit (CPU), supports up to 1GB of RAM, and has a maximum database size of 4GB. It does not have full 64-bit support, but it will run on 64-bit operating systems using the Windows-on-Windows (WOW) technology.

Workgroup Edition Workgroup Edition supports two CPUs, supports up to 3GB of RAM, and has no maximum database size limit. It does not have full 64-bit support, but it will run on 64-bit operating systems using the WOW technology. In addition, this edition provides backup log-shipping, full-text search, the SQL Server Agent scheduling service, and the Report Builder.

Standard Edition Standard Edition supports four CPUs, supports as much RAM as the operating system (OS) can support, and has no maximum database size limit. It has full 64-bit support. In addition to all the features that Workgroup Edition provides, Standard Edition has database mirroring, failover clustering, the Database Tuning Advisor, Notification Services, Integration Services with basic transforms, and Hypertext Transfer Protocol (HTTP) endpoints.

Enterprise/Developer Edition These two editions support as many CPUs as the OS allows, support as much RAM as the OS can support, and have no maximum database size limit. They have full 64-bit support. In addition to all the features that the Standard Edition and Workgroup Edition provide, these editions offer partitioning, parallel index operations, indexed views, online indexing and restoration, fast recovery, Integration Services advanced transforms, Oracle replication, the scale-out of report servers, and data-driven subscriptions (for Reporting Services).

Now you have the hardware and OS in place, but you have still more to consider before you can install SQL Server.

Preparing to Install

Before you actually install SQL Server, which you'll do in Exercise 1.1, you'll need to understand a few topics, so in this section we'll discuss some of the decisions you need to make before installing.

Choosing Default Instances or Named Instances

One of the first choices you need to make is whether this SQL Server is the default instance or a named instance. That may seem a bit confusing if you are new to SQL Server; named instances are essentially like running multiple SQL Servers on one machine. The most common time to run multiple instances is when you need to run multiple versions of SQL Server but you have limited hardware resources. By using this method you can have SQL Server 2005 running as a named instance and SQL Server 7.0 or 2000 running as the default instance. Your client machines will see two distinct SQL Servers on the network, even though they are both running on the same machine.

The default instance is selected by default (no pun intended) and should be left that way for the first installation of SQL Server on a machine. Subsequent installations on the same machine can be given installation names of up to 16 characters. Clients will then use this new name to refer to the new instance.

Choosing Service Accounts

When you first turn on your Windows machine and try to use it, you are presented with a dialog box that asks you for a username and password. That username and password give you access to the machine (and the network) with whatever privileges your administrator has seen fit to assign. Many services, such as programs running in the background, require a user account just like you do. This special user account, called a service account, gives the service access to the machine and network with the privileges it requires to get its work done.

The SQL Server services require a user account to run, so you need to pick one of three types, as shown in Table 1.4.

If you opt to use a user account (local or domain), you must first create it using the appropriate tool for your operating system. If you create only one account to be used by both SQL Server and SQL Server Agent services (discussed later in this book), then you must add the user account to the Administrators local group; otherwise, replication (also discussed later in this book) will not function properly. If you decide you want greater control over the security on your network, then you can add two separate accounts, one for the SQL Server service and one for the SQL Server Agent service. A good reason to do this is that only the SQL Server Agent service really requires administrative authority; the other can get by just fine as a standard user.

Selecting an Authentication Mode

Another important decision is which authentication mode to use. Chapter 6 discusses authentication modes in detail, but it is good to know a little about them for setup purposes. To access SQL Server, your users need to log in to the server. And to log in to the server, they need an account. The type of account they use depends upon the authentication mode that is set. If you select Windows Authentication Mode, then only clients that have an Active Directory account will be able to access the system. If you have other clients (like Novell or Unix), then you should select Mixed Mode.

You can change the authentication mode at any time after installation; in other words, if you choose the wrong one for your needs, it is OK.

Choosing a Collation Setting

In versions of SQL Server prior to SQL Server 2000, it was necessary to choose a character set, a sort order, and a Unicode collation setting. In SQL Server 2005, these three entities have been combined to form the collation setting. You can choose from two collation settings: SQL Collation and Windows Collation.

SQL Collation is for backward compatibility with older versions of SQL Server and does not control Unicode character storage. If you need to replicate with older versions of SQL Server or you will be switching between SQL Server 2005 and SQL Server 7.0 and older, then you should use SQL Collation. If you are installing SQL Server 2005 on a machine with an older version of SQL installed, then the setup program will detect the necessary collation for you; otherwise, you need to select the proper collation.

Windows Collation uses the collation (code page, sort order, and so on) of the underlying operating system and controls Unicode and non-Unicode sorting and storage. If you choose Windows Collation, then you have two more issues to worry about: the collation designator and the sort order.

Selecting a Collation Designator

As you read this book, you see the characters as lines, curves, and various shapes. If you read Cyrillic, then you see different shapes for the characters than someone reading German or English. Computers need to read and interpret characters just like we do; the only problem is that computers don't see them as various shapes-they see them as different combinations of 1s and 0s. It makes sense then that if your computer is storing German data, it must store different characters, or combinations of 1s and 0s, than an English server stores. How these characters are stored is controlled by the collation designator.

If you decide to use Windows Collation, then it is best to use the collation of the underlying operating system; for example, if you are running a German server, then you will most likely choose a German collation designator. The easiest way to find your collation designator is to look in the Control Panel under the regional options; you should use the locale displayed there as your collation designator. The most common selection is Latin1_General.

Selecting a Sort Order

All the data you are storing on your server must be sorted from time to time, usually during queries or indexing (discussed later in this book). We sort it because looking at a mass of unsorted data is hard on the brain, whereas looking at a nicely ordered report of data is pleasing to the eye. The sort order defines how SQL sorts and compares your data during queries or indexing. This sort order is the second part of the collation setting.

Several sort options are available. The default sort order is case, accent, kana, and width insensitive. This means SQL Server will not pay attention to case or special character marks when sorting, when indexing, or when performing queries. Some options can change this behavior, and if you are familiar with previous versions of SQL Server, then you will want to pay attention because they have changed:

Binary Using the default sort order, SQL Server will view characters as characters; by using binary, SQL Server will view characters as binary representations. This is the fastest sort order available, but it is case, accent, and kana sensitive.

Binary code point This works much the same as binary sorting but has some additional functionality. This sort order uses Unicode code points when sorting, which allows SQL Server to sort on the locale as well as the data. This means English data would be sorted separately from Japanese data stored as Unicode. This too is case, accent, and kana sensitive.

Case sensitive This simply tells SQL Server to use dictionary sort order and pay attention to case.

Accent sensitive This tells SQL Server to use dictionary order and pay attention to accent marks.

Kana sensitive This tells SQL Server to use dictionary order and pay attention to kana marks, which are used in many Asian languages.

Width sensitive This tells SQL Server to treat single-byte characters and double-byte characters as different characters.

Here's the catch: once you have installed SQL Server, you cannot change the collation setting. To change it, you must reinstall SQL Server and rebuild all your databases. So, choose wisely; it is usually best to use the default sort setting of case insensitivity and build sensitivity into your applications if you need it.

Upgrading from a Previous Version

You can directly upgrade to SQL Server 2005 from SQL Server 2000 SP3 or SQL Server 7.0 SP4. Most of the upgrade operations are handled during setup, so you don't need to run any special wizard or installation program. To make sure you are completely prepared, though, you need to run the Upgrade Advisor.

To use the Upgrade Advisor, you first need to install the .NET Framework 2.0 and then install the Upgrade Advisor. The first time you run the Upgrade Advisor, you should run the Analysis Wizard, which will analyze various parts of your existing SQL Server installation and let you know whether they are ready for upgrade (see Figure 1.1).

Specifically, the Analysis Wizard checks the following:

* Database engine

* Analysis Services

* Notification Services

* Reporting Services

* Data Transformation Services (now called SQL Server Integration Services, or SSIS)

The wizard generates a report based on its findings, which you can view using the Upgrade Advisor Report Viewer (see Figure 1.2). Anything marked with a green icon is ready to upgrade. A yellow icon indicates a potential problem that can usually be fixed after the upgrade is complete. Anything marked with a red icon needs to be fixed before an upgrade can take place.

Once you've made sure your system meets all the requirements and you make all the necessary decisions about setup, you are ready to install SQL Server 2005.

Installing SQL Server 2005

Now you are ready to install SQL Server 2005 on your own machine. Follow the steps in Exercise 1.1 to do so (these steps are for installing the Standard Edition, but the steps are similar for all editions).

Now that you have SQL installed, you should make sure it is running. Go to Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. Select SQL Server 2005 Services, and check the icons. If the icon next to SQL Server (MSSQLServer) service is green, then your installation is a success (see Figure 1.3).

Installing a Second Instance

Because SQL Server 2005 has the capability of running multiple instances of itself on the same machine, it is a good idea to try installing more than one instance. In Exercise 1.2, you will create a second instance of SQL Server on the same machine using a different sort order.

You can now test the second instance of SQL Server using the same method for testing the default instance. Go to Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. Select SQL Server 2005 Services, and refer to the icons. If the icon next to SQL Server (Second) instance is green, then your installation is a success (see Figure 1.4).

Troubleshooting the Installation

If it turns out that your install failed, you can take a few steps to troubleshoot it. The first place to check when you have problems is in the Windows Event Viewer. SQL will log any problems it encounters in the Application log, so check there first. If you find a problem, then you can take the error number and some of the text of the message and look them up on the Microsoft support website (http://support.microsoft.com/support) or in TechNet.

(Continues...)

Excerpted from MCTS: Microsoft SQL Server 2005 Implementation and Maintenance Study Guideby Joseph L. Jorden Dandy Weyn Copyright © 2006 by John Wiley & Sons, Ltd. Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Details zur Ausgabe

• Verlag: John Wiley & Sons
• Erscheinungsdatum: 2006
• Sprache: Englisch
• ISBN 10: 0470025654
• ISBN 13: 9780470025659
• Einband: Tapa blanda
• Anzahl der Seiten: 678
• Kontakt zum Hersteller: Nicht verfügbar