SQL Server 2000 Administration in 15 Minutes a Week: Administration Fundamentals

Chia sẻ: Huynh Thang | Ngày: | Loại File: DOC | Số trang:16

0
94
lượt xem
23
download

SQL Server 2000 Administration in 15 Minutes a Week: Administration Fundamentals

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Welcome to the first article in my series SQL Server Administration in 15 Minutes a Week. In this series we will cover the skills needed to successfully administer a Microsoft SQL Server 2000 machine. This series will also help you prepare for the Microsoft 70-228 exam "Administering Microsoft SQL Server 2000 Enterprise Edition." In this initial article we will look at an overview of the options and requirements when installing SQL Server 2000.

Chủ đề:
Lưu

Nội dung Text: SQL Server 2000 Administration in 15 Minutes a Week: Administration Fundamentals

  1. SQL Server 2000 Administration in 15 Minutes a Week: Administration Fundamentals Part 1 by Michael Aubert www.2000trainers.com Welcome to the first article in my series SQL Server Administration in 15 Minutes a Week. In this series we will cover the skills needed to successfully administer a Microsoft SQL Server 2000 machine. This series will also help you prepare for the Microsoft 70-228 exam "Administering Microsoft SQL Server 2000 Enterprise Edition." In this initial article we will look at an overview of the options and requirements when installing SQL Server 2000. - System Requirements - Available Editions and Scalability - Instances and Upgrade Paths System Requirements Remember that this article is only an overview. We will cover specific system requirements; however, some topics presented are very complex and will be covered later in greater depth. In this series I am assuming that you are using the Enterprise Edition or an edition equivalent to Enterprise Edition of SQL Server 2000. (We will talk about the different editions of SQL Server 2000 in the “Available Editions and Scalability” section later on.) If you are not using Enterprise Edition you will still be able to do just about everything with a few exceptions. If you don’t have any edition of SQL Server 2000, you can download the Enterprise Evaluation Edition from Microsoft’s website at: http://www.microsoft.com/sql/evaluation/trial/2000/download.asp CPU Intel-compatible 166MHz or higher Memory 64MB (128MB recommended for Enterprise edition) Hard Disk 250MB for a typical SQL Server database installation 130MB for Analysis Components 80MB for English Query Operating System Windows NT Server 4.0 (With SP5) or Windows NT Server 4.0 Enterprise Edition (With SP5) or Any version of Windows 2000 Server For a complete listing of the most up-to-date requirements and a listing for all editions of SQL Server, please visit Microsoft’s website: http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp Available Editions and Scalability In total there are seven different editions of SQL Server 2000 available: Enterprise Edition - This edition is the complete edition of SQL Server. It is designed for large mission-critical applications including support for Distributed Partitioned Views, log shipping, fail-over clustering, and advanced analysis (OLAP) features. Enterprise Edition can take advantage of up to 32 processors and 64GB of RAM (using Address Windowing Extensions, AWE). The maximum database size is 1,048,516 TB.
  2. Standard Edition - This edition is designed for small to medium-sized businesses that do not require the scalability, availability, or the more advanced features of Enterprise Edition. SQL Server 2000 Standard Edition supports up to 4 processors and 2 GB of RAM. The maximum database size is 1,048,516 TB. Professional Edition - This edition is based on Standard Edition but is optimized for personal use. Unlike Standard Edition, Professional Edition will run on Windows 2000 Professional, Windows NT Workstation 4.0, Windows ME, and Windows 98. Dual processors are supported, but the server is limited to 5 concurrent connections and a maximum database size of 2 GB. Developer Edition - Same as Enterprise Edition but it has a licensing restriction that it cannot be used in a production environment. Enterprise Evaluation Edition - Same as Enterprise Edition but only licensed for “demonstration, testing, examination and evaluation” and has a 120-day time limit. Windows CE Edition - This edition is designed to run on devices that use Windows CE. Desktop Engine (MSDE) – This edition (Microsoft calls it an “edition”) is only the database engine of SQL Server 2000. There are no graphical tools provided and the database size and workload are limited. However, this edition has the smallest footprint of all the editions of SQL server. Support for up to 32 processors, 64GB of RAM, and the ability to use multiple servers make SQL server 2000 a very scalable database system. What determines the maximum number of processors and RAM is the edition of SQL Server and the Operating system you are running. The three tables below show you the maximum number of CPUs and amount of Memory supported on a few different operating systems. SQL Server 2000 editions running on Windows 2000 Advanced Server: Edition Max Memory Max CPUs Enterprise Editions 8GB 8 Standard Edition 2GB 4 Personal Edition 2GB 2 SQL Server 2000 editions running on Windows 2000 Datacenter Server: Edition Max Memory Max CPUs Enterprise Editions 64GB 32 Standard Edition 2GB 4 Personal Edition 2GB 2 SQL Server 2000 editions running on Windows NT Server Enterprise Edition: Edition Max Memory Max CPUs Enterprise Editions 3GB 8 Standard Edition 2GB 8 Personal Edition 2GB 2
  3. As for hard disk storage, your needs and budget are going to run out a long time before the maximum theoretical NTFS hard drive storage space is reached. Redundancy, acceptable downtime, and speed are all factors in what type of storage solution you will need. The most common storage solution implemented today is RAID, also known as Redundant Array of Independent (or Inexpensive) Disks. There are two types of RAID: Software and Hardware. Software RAID is normally slower but is included with Windows NT 4.0 and 2000. Hardware RAID gives the best performance but is more expensive. These two RAID types can then be divided into the different RAID levels. The most common RAID levels are RAID 0 (striping), RAID 1 (mirroring), RAID 5 (striping with parity), and RAID 10 (a combination of mirroring and striping). In a later article we will take a deeper look into RAID and its benefits for your databases. Clustering is the last topic we are going to discuss in the scalability section. Clustering allows you to use multiple servers to provide redundancy and/or load balancing. Although the cluster may be made up of several servers, the cluster appears as one “virtual server” to the network. SQL Server 2000 is a fully "cluster aware" application that can operate in a clustered environment provided by the operating system. NT Server 4.0 introduced clustering with the ability to have a complete duplicate of the server. This provided protection if any portion of the server failed, but there were no load-balancing capabilities. Windows 2000 Advanced Server and Datacenter Server add a “load-balancing” capability to clustering. We will be going into how to design and setup clusters in a later article. Instances and Upgrade Paths Before we can talk about upgrade paths we need to discuss default and named instances. When you install more than one instance of SQL Server 2000 you end up with multiple instances that operate independently (for the most part). You can have a maximum of 16 instances, each operating with its own memory space and settings. The default instance is identified by the computer’s network name. SQL Server 6.5, 7.0, and 2000 can all act as the default instance, but only one default instance can operate at a time (this is called “version switching”) if multiple versions are installed. An application that uses client software from versions earlier than SQL Server 2000 can access only a default instance. Named instances are identified by the computer’s network name followed by the instance name ( servername\instancename ). Only SQL Server 2000 can act as a named instance, but you can have many named instances operating at the same time. You can also Run SQL Server 6.5 or 7.0 as the default instance with one or many SQL Server 2000 named instances running at the same time. There are a few restrictions on named instance names: -- You can’t use “Default” or “MSSQLServer” -- Named instances are limited to 16 characters -- The first character must be a letter (A-Z, a-z), an ampersand (&), or an underscore (_) -- You can’t use punctuation marks, dashes, asterisks, or spaces -- The name is NOT case sensitive Instances Summary Table: Default Instance Named Instance What version(s) of SQL 6.5, 7.0, 2000 2000 only server can run as this particular type of instance? Can multiple instances of No* Yes this particular type
  4. operate at the same time? How is this particular type servername servername\instancename of instance identified? * You can still have multiple versions of SQL Server (6.5 along side either 7.0 or 2000) installed on the computer, but only one of the versions can operate at a time. For more information about instances have a look at the following link and its related articles: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_runsetup_2xmb.asp Once you understand the concept of default instances and named instances, understanding the available upgrade paths to SQL Server 2000 is not difficult. - An installation of SQL Server 6.0 can’t be upgraded directly to SQL Server 2000 -- you must first upgrade to SQL Server 6.5 or 7.0. Once the databases have been upgraded you can then upgrade to SQL Server 2000 or install SQL Server 2000 along side 6.5 or 7.0 - SQL Server 6.5 and 7.0 can be upgraded directly to SQL Server 2000 - SQL Server 2000 can be installed as the default instance along side SQL Server 6.5 but only a single version can be active at a time. You can use “version switching” to switch between the different versions of SQL Server. - SQL Server 2000 can be installed as a named instance in addition to the default instance of SQL Server 6.5, 7.0, or 2000 installed on the computer. One default instance and a number of named instances can operate at the same time. That wraps up the first article. Next week we will take a look at the different installation types and an overview of the installation process. Please send any questions, comments, and feedback to my email. Also check out the 2000trainers.com website where you can find tutorials and practice exams on Windows, SQL Server, and Exchange. I hope you found this article helpful and I look forward to your feedback. Mike maubert@swynk.com www.2000trainers.com
  5. Learn SQL Server 2000 in 15 Minutes a Week: Basic Installation Part 2 by Michael Aubert www.2000trainers.com Welcome to the second article in my series SQL Server Administration in 15 Minutes a Week. In last week’s article, we took a look at an overview of the options and requirements when installing SQL Server 2000. This week we will look at how to perform a basic installation of SQL Server 2000. The topics for this article include: - Before the Installation - Installation Overview Before the Installation Use the following as a checklist before you install SQL Server 2000: - Have your SQL Server 2000 CD, Installation Key, and operating system CD handy - You should be logged on using an account that has Administrative rights. - Disable any Antivirus software that is running as it can sometimes effect the installation. - Shut down any services or applications that depend on SQL Server, Data Source Names (DSN), or Open Database Connectivity (ODBC). - Close any unnecessary applications that access the registry - Close the Event Viewer (Microsoft says so!) Installation Overview 1. When you place the SQL Server 2000 CD-ROM in your drive, you are greeted with the screen shown below. If you don’t see the SQL Server menu, you most likely have autorun disabled. To start the SQL Server menu manually use the Windows Explorer and run autorun.exe from your CD-ROM drive. You can also use “Run…” from the start menu and enter d:\autorun.exe where d: is the letter of your CD-ROM drive.
  6. Click on "SQL Server 2000 Components” to view the available components you can install. 2. Now we are presented with three options on the components screen. Lets continue our installation by installing the Database Server. We will look at the Analysis Service and English Query components in a later article.
  7. Click on “Install Database Server” to begin the SQL Server 2000 Installation. 3. The installation of the Database Server now begins.
  8. Click Next to continue the installation. 4. We are now prompted to select which computer we would like to install SQL Server on. There are three options: Local Computer – Installs SQL Server onto the local computer Remote Computer – Installs SQL Server onto a remote computer on the network Virtual Server – Used for installing SQL Server onto a Server Cluster In this article we will look at an installation of SQL Server 2000 on a local computer only. In the next article of this series I will discuss Remote and Clustered installations. Select “Local Computer” and click Next. 5. Now that we have selected where we would like to install SQL Server we have to choose what we would like to do in this installation. Create a new instance – Allows you to install a new instance of SQL Server or the Client Tools Upgrade, remove, or add components – Allows you to work with an existing instance that is already installed Advanced options – Allows you to create custom installation files, rebuild registry keys, and maintain Virtual Servers for Failover Clustering For now we will look at how to create a new instance. In my next article I will cover how to upgrade an existing instance, perform unattended installations, and configure a clustered server.
  9. Select “Create a new instance of SQL Server, or install Client Tools” and click Next. Learn SQL Server 2000 Administration in 15 Minutes a Week: Basic Installation (Page 2 of 4) 6. The next few screens are common and fairly simple so I will cover them quickly. Enter your name and company information and click Next. 7. To continue the installation we are asked to read the licensing agreement.
  10. Read the agreement and click “Yes” if you agree or click “No” if you do not agree with the licensing terms. Note if you click “No” the installation will be terminated. 8. Next we need to choose what type of Installation we would like to perform. Client Tools Only – Installs the Client Tools that allow you to manage a remote SQL Server Server and Client Tools – Installs an instance of SQL server and the Client Tools Connectivity Only – Installs the Data Access Components (DAC) and Network Libraries so applications can access a SQL Server instance
  11. Select “Server and Client Tools” and click Next. 9. The Instance Name is the next option we are presented with. If you are installing SQL Server as a Default Instance you leave the “Default” checkbox checked. If you need to install a Named Instance you must uncheck “Default” and you will then be required to enter a valid name for the instance in the “Instance Name” textbox. For information on what is and is not a valid Named Instance name, have a look at my first article in this series: SQL Server 2000 Administration Fundamentals Leave the “Default” checkbox checked and click Next. 10. We must now select the setup type. Typical – Installs required files and the most commonly used optional files Minimum – Installs only required files Custom – Installs required files and then you will be prompted to select which optional files you would like installed. Custom setup also allows you to set the collation and network libraries that are used. Type System Files Program Files Database Files Typical/Custom* 182,917K 38,205K 34,432K Minimum 75,116K 12,127K 25,600K *By adding and removing components the Custom installation size will change. On this screen we are also prompted to select the destination folders for the Program Files and Data Files. Program Files – The files that make up the SQL Server engine and tools Data Files – The SQL Server databases The default drive selected to install SQL Server 2000 is the System Drive (the same drive that contains your Windows installation). Finding the default directories is a little different and
  12. depends on whether you are installing a default or named instance. Use the table below to locate the default directories. Instance Type Program Files Directory Data Files Directory* Default \Program Files\Microsoft SQL \Program Files\Microsoft SQL Instance Server\Mssql\Binn Server\MSSQL$InstanceName\Binn Named \Program Files\Microsoft SQL \Program Files\Microsoft SQL Instance Server\Mssql\Data Server\MSSQL$InstanceName\Data * Where "InstanceName" is the name of your SQL Server Instance. Select “Custom” and unless you have a specific reason to change the destination folders (such as not having the disk space needed) leave them as default and click Next. Select “Custom” and unless you have a specific reason to change the destination folders (such as not having the disk space needed) leave them as default and click Next. Learn SQL Server 2000 Administration in 15 Minutes a Week: Basic Installation (Page 3 of 4) 11. At this point the installation gives you the opportunity to select optional components.
  13. Click Next to continue the installation. 12. Choosing the proper service accounts (the accounts that SQL Server will use to authenticate to Windows when it needs to access resources such as NTFS hard drives or other computers) is an important one. SQL Server 2000 uses two accounts: one for SQL Server and one for the SQL Server Agent. The SQL Server account is used by the SQL database engine which provides all the database related services. The SQL Server Agent account is used by the SQL Server Agent which provides alerts and job scheduling for your SQL Server. For now just understand there are two different accounts; the differences will become apparent when we discuss the SQL Server Agent in more detail. The first option you must select is whether you want the two services to use the same account and have the SQL Server agent start automatically or if you want to customize the accounts for each service. If you select to use the same account, you then have the option to use the Local System account or use a Domain User account. If this is your only SQL Server on the network and you don’t plan on having this server interact with any other SQL servers or remote storage servers, you can use the Local System account. If you want to interact with other SQL servers or pull information off of another server you are going to need to create a user account in your domain. If you do use a domain account for SQL Server, it is recommended that you create an account for just SQL server and not use an existing user account. This is important because if your network’s account policy is to have the password expire every 30-90 days you would have to change the account passwords on all your SQL Servers every 30-90 days. To overcome this problem, set the “Password Never Expires” on the account you use for your SQL Servers. Customizing each service’s account gives you the same options as using the same account for each service, but allows you to set the account each service uses independently. It also gives you the option to start the SQL Server Agent at startup or not. We will look at services in more detail in the next article.
  14. Select “Use the same account for both services. Autostart SQL Server Agent Service,” “Use the Local System account,” and click Next. 13. Selecting the Authentication Mode is the next option we are presented with. The process of authenticating (using your User Name and Password) to a SQL Server for access to the databases can be accomplished by the SQL Server or by the operating system. If you select “Windows Authentication Mode” all authentication will be handled by the Windows Security Users and Groups. In other words the accounts that you use to login to a Windows computer are also used to access the SQL Server databases. Having the ability to use Windows’ Security provides benefits such as streamlined authentication and much simpler user management. It is recommended to use only Windows Authentication Mode unless you have a spasific reason not to. “Mixed Mode” authentication provides both Windows Authentication and SQL Server Authentication. Unlike Windows Authentication, SQL Server Authentication is handled by SQL Server and does not rely on the operating system. Because SQL Server Authentication is independent, authentication can be provided for platforms other than Windows (ex: Unix). When you choose Mixed Mode authentication you are asked to provide a password for the system administrator (sa) account. This account is available so you can authenticate as a “sysadmin” via SQL Server Authentication. When you use only the Windows Authentication Mode the sa account is disabled. There are two final notes about Windows Authentication (for either of the two modes): The Windows Security groups BUILTIN\Administrators on the local computer (the computer SQL Server is installed on) and the group Domain Admins (if the SQL Server computer is part of a domain) are automatically granted “sysadmin” rights to the SQL server. Also remember you must use Windows NT or Windows 2000 to provide Windows Authentication. If you are using Windows 98, for example, you can only use SQL Server Authentication. Security is a big issue and will take one or two articles to examine in more detail. For now it is only important to understand the two modes of authentication.
  15. Select “Windows Authentication Mode” and click Next. 14. Another important decision you have to make is the default collation. There are three parts to the collation setting: - Character Set - Sort Order - Case Sensitivity The character set specifies what ASCII code page will be used. ASCII (pronounced "ask-ee") code pages contain the codes (numbers 0 through 255) that correspond to the appropriate characters. For example, the ASCII code for the letter A is 65. Characters 0 through 127 are called the "Standard ASCII Set" and are the same from one code page to another. The character codes 128 through 255 are in the "Extended ASCII Set" and will varied between different code pages. Take a look at ASCII code pages 1252 and 437. There are two major types of sort orders: Dictionary and Binary. You have seen a Dictionary sort order before if you have ever used a dictionary. Just like in a dictionary, Dictionary sort order arranges the data in alphabetical order. When you use a dictionary sort it's simple to predict in what order your data will be returned to you. Binary sorts, on the other hand, are a little more complicated to understand and predict. A binary sort is preformed on the numbers that make up the ASCII letters. Binary sorts can return results that are not always expected because the numbers that make up ASCII codes are not in dictionary order. However the benefit of a binary sort order is its fast speed. Case sensitivity comes into play when you start to make comparisons. For example, if you had a statement that was looking for the word "trainers" and you used a collation that was case-insensitive; "Trainers", "TRAINERS", and "TrAiNeRs" would all be returned. If you used a case-sensitive collation only "trainers" would be returned. Now that you understand what makes up a collation lets look at the settings available on the collation setup screen. You can choose to use Windows' Locale settings to set the collation or use a SQL Server Collation. By using Windows' Locales you use the Collation information that
  16. is provided by the operating system. SQL Collations are provided for compatibility when you need to interact with older SQL Servers or upgrade a database. Just remember that you are only setting the default for the server. You can select another collation for each database, or even each table, individually. Select "SQL Server Collations", "Dictionary order, case-insensitive, for use with 1252 Character Set.", and click Next.
Đồng bộ tài khoản