3 4
Appendix
You should choose the Enterprise Edition because you need all of the high performance and fault-tolerant features of the Enterprise Edition.
First of all, you need the SQL Server service. Next, you need to configure IIS to access SQL Server 2000 using some of the client communication components. The specific client communication components will depend on your specific configuration.
As the database administrator, you have the most ability to affect performance by optimizing the physical database component, including choice of hardware and placement of data and log files.
You must decide whether the clients who will access SQL Server 2000 from the Novell network will be authenticated by the Windows operating system prior to attempting to access SQL Server 2000. If they will not be authenticated by the Windows operating system, you must configure SQL Server 2000 to use Mixed Mode authentication and provide each user with a SQL Server login.
Probably, but additional information is needed. The processor is fast enough for all editions and all Windows operating systems editions. However, the question does not tell us how much hard drive space is available on the laptop. Assuming enough space can be made available, this laptop could be used to install the Personal edition on any Windows operating system. This laptop does not have enough memory to properly test any SQL Server 2000 edition on any Windows 2000 Server edition. It does have sufficient memory to test any SQL Server 2000 edition on any Windows NT 4.0 Server edition.
You should use a domain user account for the SQL Server and SQL Server Agent services. A domain user account is required for access to Microsoft Exchange Server.
If you want to configure support for NWLink IPX/SPX during setup, you must perform a Custom setup. The option to configure SQL Server 2000 to listen on NWLink IPX/SPX is not available when a Typical setup is performed. However, because all network libraries are installed during all types of setups, you can also use the Server Network Utility to configure NWLink IPX/SPX after SQL Server 2000 is installed. Finally, because your network supports TCP/IP as well as NWLink IPX/SPX, support for TCP/IP might be sufficient for your needs without configuring NWLink IPX/SPX in SQL Server 2000.
Installing a named instance of SQL Server 2000 on the same computer as an installation of SQL Server 7.0 will replace all of the SQL Server 7.0 client tools and utilities with the SQL Server 2000 versions of these tools and utilities. It will also install the SQL Server 2000 version of Books Online in place of the SQL Server 7.0 Books Online. However, it will leave your SQL Server 7.0 databases intact and functioning using the SQL Server 7.0 database engine.
You should use the capability of the SQL Server 2000 Setup program to record an unattended .ISS file for you to use to perform unattended installations. This allows you to perform identical installations on multiple computers without having to interactively navigate your way through the SQL Server 2000 Setup program screens each time you install SQL Server 2000.
You can use SQL Server Enterprise Manager or any text editor to review the current error log or any of the previous six error logs.
The SQL Server 2000 Setup program locks down certain folders within the NTFS file system to prevent unauthorized tampering. Only the service account used by the SQL Server and SQL Server Agent services and members of the local Administrators group can access the unique program and data files for each instance. The reason Gloria cannot view all of the new files that were added is most likely because she is not a member of the local Administrators group on the computer upon which SQL Server was installed. This is not a problem. This is by design. If Gloria will be administering this SQL Server 2000 installation, she might need to be added to the local Administrators group. However, she does not have to be a member of the local Administrators group to administer this SQL Server installation. Chapters 10 and 11 cover security in detail.
You should use SQL Server Enterprise Manager to change the service account for both the SQL Server and the SQL Server Agent services. This will set the appropriate access permissions in the NTFS file system and the Windows registry for this dedicated domain user account. It will also update the Microsoft Search service with respect to the use of this domain user account by the SQL Server service.
You would probably begin with SQL Server Enterprise Manager. Although you could use Osql or SQL Query Analyzer to test connectivity, SQL Server Enterprise Manager is the primary tool for administering SQL Server 2000 objects.
The first issue you need to consider is whether you need to upgrade the hardware or software to support SQL Server 2000. Windows NT 4.0 must be running Service Pack 5. Next, you must install SQL Server 2000 as a named instance. When you do this, you need to be aware that the Setup program will upgrade the SQL Server 7.0 client tools to SQL Server 2000 client tools. This includes SQL Server Enterprise Manager and SQL Query Analyzer. Finally, you must have sufficient hard drive space for the named instance of SQL Server 2000.
During the version upgrade, full-text catalogs were disabled. You need to repopulate the full-text catalogs manually. Maintaining full-text catalogs is covered in Chapter 12.
You will have to re-create any server settings and SQL Server Agent jobs and alerts. Also, you cannot upgrade any databases involved in replication.
The SQL Server Upgrade Wizard is only installed when you install an instance of SQL Server 2000 as the default instance. The reason for this is that you can only upgrade to the default instance. If the wizard does not appear, you probably installed SQL Server 2000 as a named instance, rather than the default instance.
A clustered index on a table causes the data pages (and the index pages) to be physically ordered in the data file based on the key value in the clustered index. A nonclustered index does not physically order the data pages. Only the index pages of the nonclustered index are physically ordered.
The Simple Recovery model does not rely on transaction log backups to truncate the transaction log. Rather, the checkpoint process automatically truncates the inactive portion of the logical log at the end of each checkpoint.
You should not create scripts that use Transact-SQL statements to query system tables directly because the underlying system tables might change between releases of SQL Server. If such a change does occur, you would have to rewrite those scripts. Microsoft sometimes modifies system tables with new releases to add new functionality.
Use the direct method in SQL Server Enterprise Manager. The Create Database Wizard does not allow you to create a database with multiple data files on separate disks, but the direct method does.
You can change the database recovery model for a database on the Options tab in the Properties dialog box for the database. You can also use the ALTER DATABASE Transact-SQL statement.
The transaction log file grew so large because it was set to autogrow and because the recovery model was set to full. The bulk load operation was fully logged and generated many transaction log records. To reduce the size of the transaction log, you should first back up the transaction log file and then use the DBCC SHRINKFILE or DBCC SHRINKDATABASE command. You cannot shrink the transaction log using SQL Server Enterprise Manager.
You can procure between three and six small disks. Set up a mirror of the transaction log files using the software RAID capabilities of Windows 2000 Server. Combine the remaining disks using RAID 0 and move the data file to this array. Because you have fault tolerance on the transaction log, the lack of fault tolerance on the data files may be acceptable if the performance gain is significant.
You have a number of options. You can use DTS to perform transformations of data during the import. You can use a text editor and manually perform a search and replace. You can import the data into a temporary table and use Transact-SQL to massage and scrub the data. Although each of these methods will work, DTS provides the most automated method and, because this is a task you will have to perform repeatedly, DTS provides the best solution.
A task that follows the On Success precedence constraint will only execute if the preceding task completes the task it intended to complete. A task that follows the On Completion precedence constraint will execute when the preceding task completes, regardless of whether that task achieved the task it intended to complete.
You could use DTS Designer to edit the existing package to connect to multiple data sources (each salesperson's notebook) once per week and upload the expense report. You could use Message Queuing to queue a spreadsheet upload task that occurred weekly for each salesperson. When the last salesperson has uploaded data, the package could notify an administrator using the Send Mail task.
You should save it as a structured storage file, using both an owner and a user password. You can then mail or otherwise distribute the package to your salespeople without the package being viewable or editable.
Because the table will be truncated prior to new data being inserted, you should drop the nonclustered index and rebuild it after the data insert is complete. However, you should not drop the clustered index. Rather, you should specify in your Bcp command or BULK INSERT statement that the data is already ordered.
Using fault tolerance for your disk subsystem does not protect your data from all forms of disaster. For example, multiple disks could fail simultaneously, your disk subsystem could be stolen, or a natural disaster could strike. In addition, you might need to roll your database back to an earlier point in time because of user or application error.
It depends. The benefit of regular differential database backups is to speed the restoration process. If your database fails at 5:00 P.M. , you will need to restore each transaction log backup since the full database backup the night before. If the transaction log backups are reasonably small and are either on a network file server or only on a few tapes, the benefit of differential database backups might not be significant. However, if you must insert a separate tape for each transaction log backup or if each transaction log backup is large, performing a differential database backup every two hours could substantially reduce your data restoration time.
There is little restoration benefit in this scenario. All of the data in this database comes from existing data sources. If the Full Recovery model is not used and the entire database is lost because of some disaster, you can restore data to the point of the most recent transaction log backup. You can regenerate any more recent data relatively easily from the original data sources.
Yes, you should regularly archive the backups from disk to tape for permanent storage. This will protect your backups in case the disk containing the backup files should fail.
There are two excellent ways to become familiar with the available backup options and the Transact-SQL syntax. The first is to use SQL Server Enterprise Manager, including the Create Database Backup Wizard. This will assist you in understanding how each backup option works. The second is to use the sample Transact-SQL scripts in this chapter as well as SQL Server Books Online on a sample database and practice modifying and running backup scripts.
A major advantage to using SQL Server Enterprise Manager rather than Transact-SQL (other than not having to learn the Transact-SQL syntax) is that SQL Server Enterprise Manager will use the backup history in the msdb database to assist you in selecting the necessary backup sets to perform a complete database restoration as quickly as possible.
You must start SQL Server 2000 in single-user mode to perform a master database restoration.
Given the information in the question, the answer is no. The primary benefit to enabling SQL Server authentication is to permit users who will not be previously authenticated by the Windows operating system to access a SQL Server 2000 installation. The downside to permitting SQL Server authentication is significantly weaker security. This is primarily because of the lack of account policy protections that are enforced by Windows 2000 (or Windows NT 4.0) and the lack of encryption for user names and passwords on the wire (unless SSL is enabled for the entire session).
A user cannot see databases to which he or she has no access rights. In general, for this member of the help desk staff to see and access the Northwind Reports database, she must be granted direct rights to the database, be added to a Windows group that has access, or be added to a database role in that database. Enabling the guest user account in the Northwind Reports database will also enable the member of the helpdesk staff to see and access the Northwind Reports database.
You should create Transact-SQL scripts rather than use SQL Server Enterprise Manager because you can create many users with a single script, whereas SQL Server Enterprise Manager would require hundreds of separate clicks to create this many users.
The problem begins with the fact that membership in the db_datawriter and db_datareader fixed database roles does not grant any permissions to execute stored procedures. Therefore, the problem must relate to permissions on the stored procedures themselves. It is likely that the public role has been granted execution rights on the stored procedures they are able to execute. With respect to the remaining stored procedures, either the member of the group that can execute them is receiving permission through membership in another group, or the members that cannot execute them belong to a group that has been denied the right to execute those specific stored procedures.
You can create an application role with very specifically delimited rights to the database. You can work with a developer to create an application that only allows the users to perform the specific tasks they need to perform and have the custom application access the database using the application role and an encrypted password. Finally, you can ensure that none of the users of the application have any access rights to the SQL Server 2000 installation other than through the custom application.
Possibly. If each different grouping of permissions maps directly to a single Windows 2000 group and you will never allow SQL Server logins, there is no advantage. However, if you might need to permit SQL Server logins in the future, or if there are multiple Windows 2000 groups that need the same grouping of permissions, assigning permissions to a user-defined database group will ease the administrative task over the life cycle of the SQL Server 2000 installation.
You can configure a minimum memory setting guaranteeing that a sufficient minimum amount of physical memory will always be available to the SQL Server 2000 instance.
You must configure the SQL Server Agent service to use a domain user account.
You must configure a virtual directory in IIS pointing to the database and specify the security context of connecting users. You must also specify the types of XML queries that will be permitted.
You must either assign another person as the fail-safe operator or disable the fail-safe operator feature.
Verify that the schedule is enabled; verify that the job is enabled; and verify that SQL Server Agent is running.
Performance condition alerts sample the performance object counters every few seconds. If your transaction log fills up very quickly, this sampling rate is not frequent enough to back up the transaction log before it fills. Set a lower threshold for the alert to solve the problem.
The first automation task is backup. Use the Database Maintenance Plan Wizard to automate the backup of all system and user databases. Next, consider the other tasks you can automate with the Database Maintenance Plan Wizard and automate those that apply. Next, evaluate the types of events and conditions for which you should define alerts. This will provide you with advance notice of potential problems.
Yes. You can create a multiserver job to perform this task and notify you as the MSXOperator only if a job fails. If you do not receive notification, you can generally assume that the job completed successfully on all servers. However, if the e-mail system within your company fails or the Messenger service stops running, you might not receive notification of failure.
You should start by reviewing any performance baseline information that is available. You need to determine whether there are any immediate resource limitations affecting performance. Thereafter, you can begin identifying more subtle performance issues, such as inadequate indexes and long-running queries.
Task Manager is the most appropriate tool for this task. It can be placed in the system tray and used to quickly display overall processor, memory, and I/O activity on the computer.
You should use SQL Profiler to determine the number and frequency of blocking locks. By performing this step over time, you can determine whether the number of blocking locks is stable or increasing (or perhaps decreasing). You also capture sufficient information to determine whether specific SQL batches or stored procedures are causing the majority of the blocking locks. You can then determine the necessary steps to improve concurrency and performance, such as rewriting scripts and stored procedures, or changing the design of the database.
Because this information is for sales analysis, the users probably do not need up-to-the-minute data. Depending upon the size of the database and the number of changes, you would implement either snapshot or transactional replication. Replication could occur once a day, probably late in the evening.
The Windows Me and Windows 98 Subscribers probably cannot access the initial snapshot folder. By default, the initial snapshot folder is accessible only using the hidden administrative share. This share is accessible only to users that are members of the local Administrators group of the Distributor. Solve this problem by using an explicitly created share for the snapshot folder and grant the required permissions to this share.
There is little benefit to a dedicated server because the Distributor plays a very limited role in merge replication. It serves primarily as a storage location for history information.
The cause of the distribution database being larger than anticipated could be the retention of transactions in the database for longer than anticipated. By default, they are held in the distribution database until all Subscribers have downloaded the transactions or a default period of 72 hours. You should check the maximum retention period to verify the maximum retention period. You should also check whether one or more Subscribers is taking a long time to download changes. Also, if anonymous Subscribers are permitted, all transactions will be kept for the maximum length of time.
With failover clustering, failover to a secondary node occurs automatically with only a brief delay and without the need for users to connect to another server and restart incomplete transactions. With standby servers, a database administrator must manually promote the standby server to primary and demote the primary server. During this time, the database is unavailable. In addition, users must connect to a different server (or the standby server must be renamed to the original server name) and incomplete transactions must be restarted.
The minimum number of computers is one. You could copy all transaction logs to a single server, which could also function as a monitoring server. If a production server fails, you can create the necessary logins and promote the standby server to primary for the databases affected. The standby server would continue to function as the standby server for the other production servers. Obviously, the capacity of a single server to handle this load is an issue. Also, this is a single point of failure, which is not advisable. The recommended number of servers will vary depending upon additional facts not presented here, such as size of databases and capacity of each server. However, you would probably want to have at least two standby servers and a separate monitoring server to eliminate single points of failure and provide excess capacity.