SQL Server Groups and Registrations

In many cases you will want to reorganize how your SQL Servers (or Instances) are listed in Enterprise Manager. To make organizing your SQL Servers simpler, Enterprise Manager allows you to create Server Groups. Creating a new group is not complicated…start by right clicking “Microsoft SQL Server” on the Enterprise Manager tree.

Select “New SQL Server Group…” from the popup menu.

The Server Groups screen allows you to create new groups as Top level (under “Microsoft SQL Server”) or as a Sub-group under an existing group.

Enter a name for the group in the “Name:” textbox and click OK.

You can continue to add Groups and Sub-groups until you have the ideal layout for your organization.

Modifying Server Registrations

To access an instance of SQL Server in Enterprise Manager you must first register the server. When you install SQL Server, setup automatically registers the instance in Enterprise Manager. If you need to move a SQL Server Registration to another group in Enterprise Manager or if you need to update the authentication information you can simply right click the server in the Enterprise Manager tree and select “Edit SQL Server Registration Properties…”

To change the group the server is listed under, select another group name from the “Server Group:” drop-down list.

The “Display SQL Server state in console” checkbox allows you to enable or disable the automatic display of status icons. If you disable this option the server’s icon will appear without a status indicator when you open Enterprise Manager.

The “Show system databases and system objects” checkbox will allow you to hide system objects. System objects include system databases, tables, stored procedures, etc… (more on system databases later in the next article)

Finally, the “Automatically start SQL Server when connecting” checkbox will automatically start the SQL Server service if the server is stopped when you try connecting.

Once you have made your changes click OK.

Navigating SQL Server Enterprise Manager

The Enterprise Manager is a graphical interface tool used to administer your SQL Servers. You can use the Enterprise Manager to configure SQL Server options, create/edit/view databases, perform maintenance and backups, and do quite a few more tasks that we will look at over the next few weeks.

Back a couple of articles we saw that the SQL Server 2000 setup creates a program group on the Start Menu named “Microsoft SQL Server.” To launch the Enterprise Manager Click Start > Programs > Microsoft SQL Server > Enterprise Manager

If you have ever used the Microsoft Management Console (MMC) you will notice that the Enterprise Manager looks familiar. This is because the Enterprise Manager is a MMC snap-in like many of the tools provided with Windows 2000. If you would like more information on using the Microsoft Management Console have a look at the following link:

http://www.microsoft.com/windows2000/techinfo/howitworks/management/mmcover.asp

By expanding “Microsoft SQL Server” and then expanding “SQL Server Group” you can locate the SQL Server you have installed on your local system. Expanding the tree further allows you to manage databases, security, replication, etc…

Right clicking the SQL Server’s icon in the tree view will also popup a menu with options that allow you to start/stop the server, view the server messages, access the server’s properties, etc…

Enterprise Manager also provides you with a quick look at the status of your SQL Servers. Depending on the icon displayed in the tree view you can tell if SQL Server is running, stopped, paused, and if you are currently connected to the server. Use the following table to check the status of your SQL Servers:

  • Server Running, You are currently connected
  • Server Running, You are not currently connected
  • Server Paused
  • Server Stopped

SQL Server Service Accounts

The last topic we are going to cover in this article is how to change the service accounts SQL Server 2000 uses. If you remember from my last article, during the installation of SQL Server we are prompted to choose what accounts we would like SQL Server to use. If you ever need to change the accounts used, the process is fairly simple.

To access the Services console in Windows 2000 open Control Panel and select Administrative Tools.

Open Services to launch the Services console.

When you scroll down the list of services you should see MSSQLServer and SQLServerAgent if your SQL Server is installed as a Default Instance. If you have one or more Named Instances installed, the name of the services are MSSQL$InsName and SQLAgent$InsName (where InsName is the instance’s name) respectively.

Once you have located the appropriate service, you can double click to view the properties for that service.

On the General tab you can stop, start, pause, and resume the service. Setting the Startup type allows you to specify if the service should start when Windows starts (Automatic), should only start if the service is required or if you start it by hand (Manual), or if the service should never run even if required (Disabled). You can also give this service a description — handy if you have many named instances installed.

The last screen we are going to look at in the Services section is the Log On tab. From here you can select the account the Service will use. You can select to use the Local System account or you can use another account (ex: a Domain account). Clicking the Browse button lets you see a list of available users to select from.

SQL Server Clusters

Windows 2000 Advanced Server and Datacenter Server both provide Cluster Services including Network Load Balancing and Fault Tolerance. SQL Server 2000 is a fully “cluster aware” application that takes full advantage of the Fault Tolerant services that are provided by Windows. However Network Load Balancing (or NLB) is really not designed for read/write services like SQL Server 2000 – Network Load Balancing is designed for services such as ISS, HTTP, FTP, etc.

Each server in a Fault Tolerant Cluster is referred to as a node. Each node in the cluster must have access to a shared SCSI bus. Although each node’s operating system is located on the individual servers, the shared applications (In this case SQL Server) are located on the SCSI bus that is accessible by all the nodes in the cluster.

To determine when a node has failed, nodes frequently send out what are called “heartbeat messages” to each other over the network. In the event that heartbeat messages are no longer detected from a specific node, the other servers in the cluster treat the node as failed and transfer the services from the failed node to another node. The actual process of detecting a failed node and transferring the services is a little more complicated, but I could write a hole other article on just that topic (and I may do that).

There are two types of nodes in a Fault Tolerant Cluster:

  • Primary Node: One per cluster
  • Backup Node: One per cluster using Advanced Server or up to three if using Datacenter Server

The Primary Node is the system that does all the “work” in the cluster. The primary also controls the drives on the SCSI bus that contain the shared application. Unlike the Primary Node, Backup Nodes are only standbys who check to see if the Primary Node is still active. Backup Nodes have physical access to the SCSI bus, but do not access the drives unless they become the Primary Node.

The configuration of one primary doing all the work with one or more standbys is known as an Active/Passive configuration. If you are running large mission critical applications that can’t afford any downtime or loss of performance, this is the configuration for you. However, to use an Active/Passive configuration on a large system is very expensive.

If you don’t like the idea of having one server doing nothing most of the time or you can’t afford all the servers you would need, you can use an Active/Active configuration. An Active/Active configuration is more like an Active/Passive and Passive/Active put together. In this configuration each server in the cluster acts like a Primary Node controlling part of the shared SCSI bus and also acts like a Backup Node. Using multiple instances of SQL Server you can then create a load balancing effect (but not true load balancing). The downside to an Active/Active configuration is that in the event that one node fails the backup node must perform the work of both nodes. This can result in degradation in performance especially if you don’t have the hardware needed to support both applications on one server.

I have just scratched the surface on clustering. Clustering is a neat topic and if you would like to learn more have a look at the following links:

http://www.microsoft.com/windows2000/techinfo/planning/server/clustersteps.asp

http://www.microsoft.com/windows2000/techinfo/howitworks/cluster/clusterarch.asp

Remote Installations of SQL Server

Performing an installation of SQL Server 2000 on a remote system is a lot like performing a local installation of SQL Server. SQL Server Setup accomplishes a Remote Installation by creating a setup.iss file from the information you provide, copying the setup file and other files to the \admin$, directory and then starting a service on the remote server to begin an Unattended Installation on the remote system.

Due to the fact a Remote Installation is just an Unattended Installation that is started on a remote system for you, Unattended Installations have the same restrictions Remote Installations have. This means you can’t use a Remote Installation to setup Failover Clustering, Remove SQL Server, or Change the Optional Components that are already installed.

To begin a Remote Installation of SQL Server 2000, run the SQL Server Setup as you normally would. When you are prompted to select the computer you would like to install SQL Server on, select “Remote Computer” and then enter the name of the system you would like SQL Server (or the client tools) to be installed on. You can also click the Browse button to see a list of available systems.

After you have selected the system you would like to install SQL Server on click Next.

Select “Create a new instance of SQL Server, or install Client Tools” and click Next

The next Setup screen is unique to the Remote Installation. This screen prompts for the account and path information that will be used to install the SQL Server. The account you choose should have Administrative privileges on the remote system. An important thing to note is the account you enter on this screen is only used during the installation and has nothing to do with the Services or Logon accounts of SQL Server.

The Target Path you enter should be given in the Universal Naming Convention form. In this example I am installing the files on the “C” drive of SQL2. The Setup Source Files textbox specifies the location of the SQL Setup files. In this example the files are located on the server named CDSRV and are located in the SQL_ENT share.

Enter the appropriate information and click Next.

From this point on the Remote Installation will prompt you for the Installation Type, Service Accounts, Authentication mode, Collation, etc. (Just like a basic installation) Once Setup has all the information it needs, it will then begin an Unattended Installation on the remote system automatically.

Unattended SQL Server Installations

If you have ever deployed Windows 2000 in an environment with hundreds of systems you know about the benefits of performing Unattended Installations. Using a setup file, containing the installation parameters, you can automate the installation of Windows 2000. This same capability to automate an installation of Windows 2000 is available for SQL Server 2000. Using an Unattended Installation you can install the components needed for SQL Server, the Client Tools, or Connectivity Only. However, you can’t use an Unattended Installation to perform tasks such as setting up Failover Clustering or Change the Optional Components that are already installed.

SQL Server uses a batch file (.BAT used to begin the installation) and an Installation Setup Initialization file (.ISS that contains the information needed during the installation) to perform the Unattended Installation. Both the Batch and the Installation Setup file can be edited with a simple text editor such as Notepad. SQL Server even provides you with sample Batch and Installation files that can be edited and used as needed. The example files can be located on the root directory of your SQL Server 2000 CD.

There are three ways to create an Installation Setup Initialization file:

  • Use the file created after installing SQL Server 2000
  • Use the SQL Server 2000 Setup program
  • Create a file by hand or edit an existing file

The first way to create an .ISS file is to perform a normal installation of SQL Server 2000. Each time you install SQL Server 2000, setup automatically creates a setup.iss file located in the \Install folder. You can copy this file and then use it to install SQL Server on another system, back up the file incase you need to reinstall SQL Server, or use the file as a beginning template that can be edited by hand.

Installing SQL Server 2000

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 in the link 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.

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.

Click on “Install Database Server” to begin the SQL Server 2000 Installation.

3. The installation of the Database Server now begins.

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

SQL Server 2000 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 their 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:

What version(s) of SQL server can run as this particular type of instance?
Default Instance: 6.5, 7.0, 2000
Named Instance:    2000 only

Can multiple instances of this particular type operate at the same time?
Default Instance: No*
Named Instance: Yes

How is this particular type of instance identified?
Default Instance: servername
Named Instance: servername\instancename

* You can still have multiple versions of SQL server 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.

SQL Server 2000 System Requirements

Remember that this article is only an overview. We will cover specific system requirements, however some topics are vary large 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

SQL Server 2000 Editions and Scalability

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.

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 is 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.