Chapter 1: Installing and Configuring SQL Server 2005

Overview

In this chapter, you will learn about:

·         SQL Server editions

·         Instances of SQL Server

·         Installing SQL Server 2005

·         Configuring SQL Server 2005

·         Using configuration tools like SQL Server Surface Area Configuration tool and SQL Server Configuration Manager

·         Connecting to SQL Server via SSMS, SQLCmd, and dedicated administrator connection

·         Troubleshooting the installation

·         Books Online

SQL Server Editions

SQL Server 2005 has several editions, each with unique capabilities or restrictions as outlined in Table 1-1.

Table 1-1: SQL Server Editions
Edition	Comments
Enterprise	Designed to scale to the largest On-Line Transaction Processing (OLTP) database needs. Supports failover clustering of 8 nodes.
Developer	Fully functional Enterprise version, but licensed for use as a development or test version, not as a production server.
Standard	Scaled down version of SQL Server. Intended for small- to medium-sized organizations. Supports failover clustering of 2 nodes.
Workgroup	Designed for small organizations as an entry-level database.
Evaluation	180-day evaluation edition of Enterprise edition. If you don’t have a copy of SQL Server 2005, you can use this to learn and evaluate it.
SQL Server Express	Free version that is integrated with Visual Studio 2005. Can be managed with SQL Server Management Studio Express.
SQL Server Compact	Previously called SQL Server 2005 Everywhere. Designed for mobile applications.

Note: Exam Tip

The tests are primarily focused on Enterprise and Standard editions. While it’s good to be aware of all the editions, for the test, focus on key features of these two editions.

Table 1-2 compares many of the key features of SQL Server 2005 Standard and Enterprise editions.

Table 1-2: SQL Server 2005 Feature Comparison
Feature	SQL Server 2005 Standard Edition	SQL Server 2005 Enterprise Edition
Clustering	2 nodes	8 nodes
Database Snapshots	No	Yes
Database Mirroring	Safety Full Only	Yes
Multi-Instances	50	50
Memory Enhancements	Few	Many
Max memory	Operating-system limit	Operating-system limit
Symmetric Multiprocessing (SMP)	4 CPUs	Operating-system limit

I’d like to stress a couple things about Table 1-2. First while SQL Server 2005 Standard Edition supports clustering, it only supports two nodes. Second “operating-system limit” means that whatever the operating system supports, this edition of SQL will support. If the operating system can support 8GB, these editions of SQL can support 8GB. Of course, this means we also need to understand the operating system features.

Note

While SQL Server Books Online indicates that SQL Server 2005 is limited to only 16 instances, this was changed in July 2006. An MSDN (Microsoft Developer Network) article entitled “Maximum Capacity Specifications for SQL Server 2005” clarifies that 50 instances are now supported on all editions except Workgroup edition.

Operating Systems and Supported Benefits

Where will you install SQL Server 2005? If you’re working in a production environment, you’ll install it on a server product. While SQL Server 2005 can be installed on Windows Server 2000, you get the most capabilities by installing it on Windows Server 2003.

The Server 2003 edition you choose depends on the capabilities that you need. Table 1-3 compares some of the capabilities of the different Server 2003 editions.

Table 1-3: Windows Server 2003 Feature Comparison
Feature	Server 2003 Standard	Server 2003 Enterprise	Server 2003 Datacenter
Memory	4GB	64GB	128GB
Clustering	No	8 nodes	8 nodes
SMP	4 CPUs	8 CPUs	32 CPUs

Note that if you install SQL Server 2005 Enterprise edition on Windows Server 2003 Standard, you’ll be limited to 4GB of RAM. SQL Server can handle more, but not more than the operating system supports.

For learning purposes, you can install the Evaluation edition of SQL Server on your desktop.

Caution

If you’re installing SQL Server 2005 on Windows Vista, you will need to apply SQL Server 2005 Service Pack 2 after the installation for it to work properly.

Instances

While SQL Server 2005 can handle hundreds of databases on a single server in a single instance, it’s sometimes necessary to create a separate instance of SQL Server on the same machine.

A separate instance of SQL Server is similar to a separate instance of any program you might launch. Say you open two documents in Notepad—one is a letter to Mom and the other is your résumé. You will have two separate instances of Notepad running. When you modify your résumé, it doesn’t affect the letter to Mom.

Separate instances of SQL Server work the same way. By installing a separate instance, you can create databases in the different instances that are completely discrete from each other. Additionally, separate instances can have many independent settings such as enabled services, settings for collations, and more. Some of the most important independent features are related to security, as described in the “Security Impact”’ section later.

The first instance installed on SQL Server 2005 is typically the default instance. Other instances are referred to as named instances.

In Figure 1-1, you can see the default instance at the top with the name of the server (INSTVS). The Named Instance is highlighted below it, intuitively named INSTVS\ MYNAMEDINSTANCE. You can label your named instance anything you like. Notice how each instance has a full set of folders (Databases, Security, Server Objects, and so on) that can be manipulated for that instance.

Figure 1-1: Multiple instances in SQL Server Management Studio

Identifying SQL Server 2005 Versions

What version is this? What service pack is installed?

Let’s look at the version information. You’ll notice that after the name of the server are some numbers (SQL Server 9.0.1399).

The 9.0 refers to SQL Server 2005. SQL Server 7.0 was 7.0, SQL Server 2000 was 8.0, and SQL Server 2005 is 9.0.

The four-digit number refers to the release: 1399 is the Release To Manufacturing (RTM) release, 2047 is SQL Server 2005 Service Pack 1. When SP2 was first released, it had a release number of 3042 (as in 9.00.3042.0). Shortly thereafter came a critical update to SP2, and the release number for the general distribution release of SP2 was 3050.

Look up Knowledge Base (KB) article 321185 on the Web for more information (search KB 321185 with your favorite search engine).

Security Impact

One of the primary reasons to create separate instances is for security factors.

As an example, let’s consider two departments, the HR department and the R&D department. Both departments currently manage their own server with their own database administrator (DBA) groups. They have full access to the server, can add logins, and manage all the security settings.

You’re tasked with consolidating both departments onto the same server. If both were consolidated onto the same instance and each group still needed to be able to set instance level settings, then you’d be forced to grant the HR DBA group and the R&D DBA group full access to the server. You no longer have a separation of privileges between the two departments. Personnel information (such as salary data or Privacy Act data) would be available to DBAs in the R&D department. Proprietary research data would be available to DBAs in the HR department.

Instead, you could create two separate instances on one server—one as a default instance for the HR department and another as a named instance for the R&D department. You can then grant the HR DBAs full access to their instance, but they wouldn’t have access to the R&D instance. Likewise you’d provide full access for the R&D department DBAs to their instance, but they wouldn’t have access to any of the HR databases in the HR instance.

In short, separate instances enable you to provide a separation of privileges and access.

Consolidating

You may be tasked with consolidating databases onto as few servers or as few instances as possible. Since SQL Server 2005 can run multiple databases at the same time, this is certainly possible.

When consolidating databases, you have two primary decision points: hardware and security. First you must determine if the hardware can handle the load. Next you determine whether the consolidation will violate any security practices you want to implement.

Hardware

Imagine having two identical servers, each hosting a single database. Both servers are averaging a Processor: % Processor Time of 10 percent. Putting both databases on one server causes the Processor: % Processor Time to increase to 20 percent. This is certainly acceptable.

Of course, we’d need to look at more than just the CPU. In Chapter 6, we cover measuring the core four resources—memory, CPU, disk, and network—by using Performance and System Monitor. These would be the primary server resources we’d look at to determine if we could consolidate both databases onto a single server.

Security

Assuming we can consolidate the databases onto a single server from a hardware perspective, we now need to determine if we should put all databases onto a single instance, or create multiple instances. If we need to grant instance-level access, we would create a separate instance.

While an instance is not literally a separate server, it is easier to look at it this way. If you have one group of DBAs managing the first server and a different group of DBAs managing the second server, and you need to maintain this separation after the consolidation, you’d create different instances. If it’s the same group of DBAs managing both servers, you can consolidate both servers onto a single instance.

Installing SQL Server 2005

If you’re going to learn SQL Server 2005, you need to install it. In this section, I talk about some basics and then guide you through the process of installing it on your system. No, you won’t have to buy it. Microsoft provides free evaluation copies for download.

Prerequisites

You need to make sure your hardware supports SQL Server 2005. SQL requires a minimum of 512MB for both Standard and Enterprise editions, but as they say, “don’t try this at home.” You’re likely to see a very slow system. At least 1GB of RAM is recommended.

If you want to install Reporting Services, you’ll need to install IIS and ASP .NET 2.0. We’ll cover Reporting Services in more depth in Chapter 14.

The setup program will check for several software components. If they’re not installed, it will install them. This includes Microsoft Windows .NET Framework 2.0, SQL Server Native Client, and SQL Server Setup support files.

Also, several different operating systems have different service pack (SP) requirements. Since SQL Server 2005 was released after many current operating systems, service pack upgrades were issued so they would work optimally with SQL Server. Make sure you have the most recent service pack upgrade on your operating system and you’ll be safe.

Windows Vista was released after SQL Server 2005. For SQL to run on Vista, you’ll need to apply SQL Server’s SP2 after the installation of SQL.

When you start the installation, the prerequisites of your system are checked. If you don’t meet them, you’ll get an error saying you need to upgrade your system to meet the prerequisites. Thankfully, these errors usually are fairly straightforward to correct.

Exercise 1.1: Install SQL Server 2005

1.      Insert the DVD into your DVD drive. With autorun enabled, the SQL Server 2005 startup program will launch automatically. If autorun doesn’t work, you can browse to the Servers folder and double-click Splash.hta. You should see a display similar to Figure 1-2.

Figure 1-2: SQL Server Evaluation Launch screen

2.      Under the Install section, click “Server components, tools, Books Online, and samples.”

3.      On the End User License Agreement page, if you agree, check “I accept the licensing terms and conditions.” Click Next.

4.      On the Installing Prerequisites page, SQL Server will check your system for the necessary prerequisites. If any components are missing, the SQL Server Component Update will launch and install any necessary components. If necessary, click Install to begin the process. Once installation is completed, the Installing Prerequisites page will indicate success as shown in Figure 1-3. Click Next.

Figure 1-3: Prerequisites installed successfully

5.      On the System Configuration Check page, the installation program will scan your computer’s configuration and determine if any components are missing or required for certain features. (This sometimes occurs very quickly, and you may miss seeing it.)

Once scanning is completed, the program will launch the “Welcome to the Microsoft SQL Server Installation Wizard” page. Click Next to continue.

6.      The System Configuration Check page will list any issues that you need to address. Errors must be resolved to continue with the installation. Warnings are informative. For example, you will likely see a warning that to support Reporting Services, you need to install IIS. Since we won’t use Reporting Services until Chapter 14, we won’t install IIS now. (IIS was installed on my system, so the Status shows “Success” in Figure 1-4.)

Figure 1-4: System Configuration Check with zero errors

If desired, you can view the report and save the report to a file. Click Next once you’ve addressed the identified issues.

7.      After a moment, the Microsoft SQL Server Installation wizard will launch.

8.      On the Registration Information page, enter information in the Name and Company text boxes. Click Next.

9.      On the Components To Install page, select SQL Server Database Services, Notification Services, Integration Services, and “Workstation components, Books Online and development tools,” as shown in Figure 1-5. Click Advanced.

Figure 1-5: Components To Install page

10.  On the Feature Selection page, add the AdventureWorks Sample OLTP database, as shown in Figure 1-6. Click the + next to “Documentation, Samples, and Sample Databases.” Click the + next to Sample Databases. Select “Will be installed on local hard drive” for the AdventureWorks Sample OLTP database. Click Next.

Figure 1-6: Selecting the AdventureWorks sample database

11.  On the Instance Name page, leave Default Instance selected. Click Next.

12.  On the Service Account page, select “Use the built-in System account,” and select Local system. Under “Start services at the end of setup,” ensure SQL Server is selected, as shown in Figure 1-7. Select Next.

Figure 1-7: Selecting the Service Account

Note: Exam Tip

For a production server within a domain, you will typically use a domain user account, as Microsoft recommends. It will allow the service to access domain resources based on the permissions of the domain user account. For a development or training system, the built-in System account will suffice.

13.  On the Authentication Mode page, ensure Windows Authentication mode is selected, and click Next.

14.  On the Collation Settings page, under SQL Collations, accept the default of “Dictionary order, case-insensitive, for use with 1252 Character Set,” as shown in Figure 1-8 and click Next.

Figure 1-8: Selecting the collation settings

Note: Exam Tip

The Collation setting defines the sorting behavior of the server. The setting on this page applies to the entire instance. However, we can change the collation on individual databases, tables, and columns. We can even set the collations that are used within a query.

15.  On the Error And Usage Report Settings page, click Next.

16.  On the Ready To Install page, click Install. Be patient. This will take a while. Once complete, the Setup Progress page should look like Figure 1-9.

Figure 1-9: Setup Progress successfully completed

17.  On the Setup Progress page, click Next.

18.  On the Completing Microsoft SQL Server 2005 Setup page, read the summary notes and click Finish.

Configuring SQL Server 2005

One thing I admire about Disney movies is their depth, or ability to entertain on many levels. At the same time that a child enjoys some slapstick, adults laugh at a joke that the kids just don’t get.

SQL Server 2005 also has depth. Many system administrators will find that they can install SQL Server and need only minor maintenance for smaller databases. In a typical configuration, SQL Server works out of the box. I’m not an Oracle guru, but I’m told that this is a significant difference between Oracle and SQL Server. Oracle servers require all the tweaking no matter what the size of your database.

On the other hand, for large databases, SQL Server has a lot of different configurations that you can manipulate to get better performance for different applications.

In this section, we’ll cover a few key configuration settings that you should know as an SQL Server administrator.

Service Accounts

SQL Server runs with several services. One way of looking at this is that SQL Server can start when the computer starts. It doesn’t need a user to log on and launch the program. Instead, a service can be configured to start automatically and launch the application.

For this to be successful, the service starts under the context of a service account. The same way you can log on with a regular account and have certain privileges, or log on with an administrator account and have different privileges, the account you use with the service account will determine the privileges SQL Server has when it starts. Of course, that begs the question, what account should you use for the service?

Note: Exam Tip

Two of the three MCITP exams (70-443 and 70-444) mention that it’s expected that you’re working in an enterprise- or a medium-sized organization. Since the recommended size of a workgroup is less than 20 users, Microsoft is implying you are in a domain environment. When configuring the service accounts in a domain, Microsoft recommends using a domain account so that access to network resources can be granted.

Microsoft recommends using the same domain account for the main SQL Server service and the SQL Server Agent service when using SQL Server in a domain environment. If network resources are not needed in a domain, a local user account could be used. Configuring a domain account will allow SQL Server to interact with other network services and to perform many server-to-server activities.

In a workgroup, you could use the Local Service account. This is a special built-in account that allows the service to start and run. In general, it would not be used to access network resources; however, network resources that don’t check credentials could be accessed.

Microsoft specifically recommends you do not use the Network Service for the SQL Server service and SQL Server Agent service.

Beware, however. Even though this domain account functions as a service account, it’s a regular user account and is subject to all the same restrictions. If you work in a domain environment, you probably change your password regularly. Not because you come to work one day and say, “I think I’ll change my password today.” Instead, Group Policy settings force you to change your password after so many days. Common Group Policy settings force a password change after 45 or 90 days, but the domain administrator can change this to whatever she deems appropriate. A warning pops up saying you need to change your password. You’re usually given a few days to do so. If you don’t, you get locked out and can’t log in again until you change your password.

That works fine for you and me. However, what about the domain account used to start your SQL services? The service doesn’t know how to change the account’s password. When the warning comes, it’s ignored. So if you don’t change the password for the service account, the account will be locked out and the services will no longer start.

Here’s the scenario. You install and configure SQL Server and everything is working fine. About six weeks later, everything stops and you get a phone call. “SQL’s broke” they say.

Tip

When configuring domain accounts as service accounts, administrators have two primary choices to manage the password change issue: (1) Set the account properties so the password never expires. You should still change the password regularly, but account lockouts will be prevented. (2) Create a process where you regularly change the passwords of system accounts prior to expiration of the passwords.

One last comment on service accounts. The principle of least privilege needs to be applied. What’s that mean? Give only the access needed. In the past we often had to add service accounts to the local administrators group in order for general features to work. This is not the case today. Except for unique situations, it is not recommended to add the SQL Server service account to the local administrators group.

Services

Several different services are associated with SQL Server 2005, and you need to know what most of them are and what they do. Table 1-4 shows several key services and some of the associated features they power.

Table 1-4: SQL Server Services
Service	Comments
SQL Server Database Services	Needed for SQL Server.
SQL Server Agent	Needed for SQL Server Agent Jobs, Operators, and Alerts.
Analysis Services	Used for OLAP (MCITP focuses on OLTP).
Reporting Services	Needs IIS as a supporting service.
Notification Services	Used for Notifications.
Integration Services	Needed to create and run SQL Server 2005 Integration Services (SSIS) packages.
SQL Server Browser	Listens for incoming requests. Needed for remote DAC.
SQL Writer	Used for backup with Volume Shadow Copy Service.

Note: Exam Tip

Make sure you’re aware of the different services and related features. For example, while the SQL Server Browser service does a lot more, administrators should know this is required for a remote dedicated administrator connection.

You can view these services the traditional way via the Services applet. The following exercise can be used to configure a service in the Services applet.

In this exercise, we’ll configure the SQL Server Agent to start automatically. The SQL Server Agent is used to run maintenance Jobs, respond to events by raising Alerts, and notify users by using Operators.

Exercise 1.2: Configure a Service via the Services Applet

1.      Launch the Services applet by clicking Start, pointing to Administrative Tools, and clicking Services. (From here on, sequences such as the preceding will be given in abbreviated form, for example: “Launch the Services applet by choosing Start | Administrative Tools | Services.”) If the Administrative Tools menu is not available via your Start menu, you can access it via the control panel.

2.      Scroll down to the SQL Server Agent service. You should see something similar to Figure 1-10.

Figure 1-10: Services applet

You may notice that the figure shows three SQL Server Agent services, but you probably only have one SQL Server Agent service. That’s because on my system, three separate instances of SQL Server are running: the default instance (MSSQLSERVER), an instance named MYNAMEDINSTANCE, and an instance named MCITPINSTANCE. This helps illustrate that separate instances have separate configurable services.

3.      Double-click the SQL Server Agent service to open up the properties page for the service. On the General tab, change the Startup Type from Manual to Automatic.

4.      Start the service by clicking Start. The properties page of the SQL Server Agent service should now look like Figure 1-11.

Figure 1-11: SQL Server Agent started and configured to start automatically

5.      Select the Log On tab. If this is your desktop testing server where you followed Exercise 1.1, you’ll notice the service is currently set to log onto the system by using the Local System account.

Note: Exam Tip

While setting the service to use the Local System account will work for a development and learning system, it would typically be set to a domain account on a production server. For example, you could create a user account named SQLService in a domain named MCITP. You would then select This Account, enter MCITP\SQLService, and enter the password for the account. Of course, you could use any name for the user account, but naming the account SQLService is a little more intuitive than naming it Joe or Sally. It’s a common practice to use the same account for both the SQL Server Service (MSSQL) and the SQL Server Agent Service.

6.      Click OK. You have configured the SQL Server Agent service to start automatically.

7.      Use the same procedure you just used with the SQL Server Agent service to enable and configure the SQL Server Browser service to start automatically. This is a required service to access the dedicated administrator connection (DAC). If you look back at Figure 1-10, you’ll notice that there is only SQL Server Browser service for the server, not one service per instance.

A couple of miscellaneous services you should be aware of include IIS and Terminal Services.

Internet Information Services (IIS) allows your computer to act as a Web server. While it is commonly referred to as the “IIS service,” the actual service in the Services applet is the World Wide Web Publishing (W3SVC) service. This is required for Reporting Services, and we’ll enable it in Chapter 14.

Many servers will be located remotely in a server room with extra physical security. It is very common for administrators to remotely log into the server to administer it instead of going into the server room. Two tools built into Server 2003 are Remote Desktop Connection (RDC) and Remote Desktops. Both require the Terminal Services service to be running on the remote server to operate.

SQL Server Surface Area Configuration Tool

The SQL Server Surface Area Configuration tool allows you to configure features and services with just a few clicks.

This tool seems to be driven, at least in part, by Microsoft’s SD³+C philosophy. That’s “Secure by Design, Secure by Default, and Secure in Deployment and Communications.” The whole idea is to reduce the attack surface of SQL Server and to enhance security from the moment it’s installed.

Imagine two boxers fighting. They don’t face each other squarely, but instead face sideways. Part of the reason for this is to provide leverage and power in the offense, but just as importantly, it gives their opponent less of a target in defense. They are reducing their attack surface.

The same philosophy applies to any server. Give the attacker less to attack, and you have less chance of being attacked. If you don’t need Internet Information Services (IIS), simply disable the service, and you are no longer susceptible to IIS attacks.

Two ways the Surface Area Configuration tool helps you do this is by limiting features and limiting services.

SQL Server Surface Area Configuration for Features

With SQL Server Surface Area Configuration for Features you can enable or disable the features listed in Table 1-5.

Table 1-5: SQL Server Surface Area Configuration Features
Feature	Comments
Ad Hoc Remote Queries	Enable OPENROWSET and OPENDATASOURCE support
CLR Integration	Enable CLR integration
DAC	Enable remote DAC
Database Mail	Enable Database Mail stored procedures
Native XML Web Services	Enable HTTP endpoints
OLE Automation	Enable OLE Automation
Service Broker	Enable Service Broker endpoints
SQL Mail	Enable SQL Mail stored procedures
Web Assistant	Enable Web Assistant
xp_cmdshell	Enable xp_cmdshell

On the CD Exercise 1.3: Launch SQL Server Surface Area Configuration for Features

1.      Choose Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration.

2.      Select Surface Configuration For Features. You should see something similar to Figure 1-12.

Figure 1-12: SQL Server Surface Area Configuration for Features

3.      Click CLR Integration to see what this will enable or disable. Note the default setting is not enabled.

4.      Click DAC to see what this will enable or disable. Note the default setting is not enabled.

5.      Click SQL Mail and then Database Mail. Read what each will do, and note the default settings of both.

6.      Click DAC and xp_cmdshell, and notice the default settings. At this point, you may notice a trend. None of these features is enabled. That’s SD³+C in action—secure by default.

7.      Look at any other settings that interest you. Click Cancel to return to the main screen.

SQL Server Surface Area Configuration for Services and Connections

In addition to configuring services via the Services applet, you can also configure many SQL Server services via the SQL Server 2005 Surface Area Configuration tool. You don’t have as many options with this tool, but you can change the startup type and start and stop the service.

The following exercise will lead you through changing the remote connections, examining the service states, and restarting the MSQLServer service.

Exercise 1.4: Launch SQL Server Surface Area Configuration for Services

1.      You should have the SQL Server Surface Area Configuration tool open from the previous exercise. If not, launch the tool. Click “Surface Area Configuration for Services and Connections.” You should see a display similar to Figure 1-13.

Figure 1-13: SQL Server Surface Area Configuration for Services and Connections

2.      By default, the MSSQLServer | Database Engine | Service folder is selected. Note that the Startup Type is set to Automatic and the Service Status is Running.

3.      Select Remote Connections. If you’re using the Evaluation edition, this will be set to Local Connections Only. Instead choose Local And Remote Connections and below that, “Using both TCP/IP and named pipes,” so that your display is similar to Figure 1-14.

Figure 1-14: Enabling TCP/IP and Named Pipes

4.      Click OK. In the Connection Settings Change Alert dialog box, click OK to acknowledge you must restart your computer for this setting to take effect.

5.      Click “Surface Area Configuration for Services and Connections.”

6.      With the MSSQLServer | Database Engine | Service folder selected, click Stop to stop the service. After it has stopped, click Start to restart it. In the next exercise, you’ll see how this has enabled the TCP/IP protocol for this instance.

7.      Select the SQL Server Agent Service. Note that the Startup Type is set to Automatic and that the Service Status is Running. This was done in Exercise 1.2 via the Services applet. As with many tasks in Microsoft products, you have more than one way to get the job done.

SQL Server Configuration Manager

You also have the capability of configuring protocols that your SQL Server will use for communication. If you’re in a typical Server 2003 domain environment, you’ll be using the TCP/IP protocol suite, but SQL Server does support other protocols.

Note: Exam Tip

While several protocols are available, Microsoft tests often focus on Microsoft environments. In a typical Microsoft domain environment, you will have TCP/IP and Named Pipes enabled. If you need to choose between TCP/IP and Named Pipes, TCP/IP is more efficient, but often both will be enabled. One weakness with Named Pipes is in a WAN environment, which requires Named Pipes to create more connections, making it slower than TCP/IP.

By using the SQL Server Configuration Manager, you can configure protocols used on a per-instance basis. The four protocols available are as follows:

·         TCP/IP This protocol is most common in a domain or environment.

·         Named Pipes This protocol is also available in a domain or networked environment.

·         Shared Memory This protocol works only on a single machine. It is used when the network is not accessed.

·         VIA This protocol, Virtual Interface Adapter, is used with proprietary VIA hardware.

TCP/IP and Named Pipes will be enabled in a typical Microsoft environment. TCP/IP is the most common protocol on networks today. It’s actually a full suite of protocols, and the TCP/IP protocol suite is required in a Windows 2003 domain environment. Named Pipes is a relatively fast protocol in a LAN network. However, it suffers from performance problems when traveling over WAN links.

Shared memory works only on a single machine. It is the default protocol when installing SQL Server 2005 Evaluation edition. VIA is proprietary and is used with VIA hardware.

When using TCP/IP, SQL will use TCP port 1433 to listen for connections by default. This is configurable in this tool, but if no port is listed, port 1433 is used.

In the following exercise, we’ll launch the SQL Server Configuration Manager and identify the status of the four supported protocols.

Exercise 1.5: Launch SQL Server Configuration Manager

1.      Launch SQL Server Configuration Manager. Choose Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager.

2.      Under SQL Server 2005 Network Configuration, select Protocols For MSSQLServer. You should have a display similar to Figure 1-15.

Figure 1-15: SQL Server Configuration Manager

3.      In the details pane, note that four protocols are listed: Shared Memory, Named Pipes, TCP/IP, and VIA. Notice that Shared Memory, Named Pipes, and TCP/IP all show a status of Enabled, and VIA shows a status of Disabled.

4.      If your setting is different for any of the protocols, double-click the protocol and select Yes or No as needed for the Enabled property. Click OK.

On the Evaluation edition, Shared Memory is enabled by default. In the previous exercise, we enabled Named Pipes and TCP/IP with the “SQL Server Surface Area Configuration for Services And Connections” tool. This is what you would typically find on a production server.

Collations

Collations are simply the rules used to store and sort data. Different languages follow different sorting rules. Microsoft is an international company, and SQL Server could be found in just about any country.

The default collation is determined by the operating system. If you installed SQL Server on a system that was installed in the United States, it would default to a Latin1_General collation, and data would be interpreted and displayed using the 1252 (Latin1 ANSI) code page.

Collations can be set at the server level so that the same collation applies to all databases. This is the default. If you have a database that needs to be stored with a different language, you can set the collation on the database level. You can also set the collation on individual columns and even override the collation in queries.

Connecting to SQL Server 2005

Now that SQL Server is installed and configured, we can connect to it. In this section, we’ll discuss three primary ways to connect to SQL Server 2005: SQL Server Management Studio (SSMS), SQLCmd from the command line, and a dedicated administrator connection (DAC).

SQL Server Management Studio (SSMS)

Throughout this book, we’ll be using SSMS to create, manipulate, monitor, and manage SQL Server instances, databases, and objects. While it certainly isn’t the only tool available, it is the primary tool used by DBAs managing and maintaining an SQL Server implementation.

In the following exercise, we connect to SQL Server using SSMS.

On the CD Exercise 1.6: Connect to SQL Server Using SSMS

1.      Choose Start | All Programs | Microsoft SQL Server 2005, right-click SQL Server Management Studio, and select Run As.

2.      In the Run As dialog box, select The Following User, enter the username and password of an account with administrative privileges on your system, and click OK.

Caution

It’s not recommended to log onto your system with an account with administrative privileges. One reason is that if your system becomes infected with a virus while you’re logged on, it will have your privileges. Instead, as a best practice, log on with a regular account, and then use secondary logon (Run As) to run programs that need elevated rights. The built-in administrators group has been placed into the sysadmin role by default, granting full access to your SQL Server. If you (or someone else) tried to launch SSMS with a different account and you weren’t granted access, you wouldn’t be able to connect. We’ll discuss server and database access in more depth in Chapters 8 and 9.

3.      In the Connect To Server dialog box, notice that the Server Type is Database Engine, the Server Name is the name of your server, and the Authentication is Windows Authentication, as seen in Figure 1-16. Click Connect.

Figure 1-16: SSMS Connect To Server dialog box

4.      You will see SSMS open; leave it open. We’ll explore SSMS in Exercise 1.8.

SQLCmd

SQLCmd is a command-line utility that allows you to enter Transact-SQL statements, system procedures, and script files from the command prompt, in the Query Editor (while in SQLCmd mode), and within a job in SQL Server Agent.

Once we explore Transact-SQL, the power of SQLCmd will be much more apparent. For now, just be aware that almost anything you can do with Transact-SQL, you can do from a script and launch it from SQLCmd.

The great strength of any command-line utility is that it can be automated. SQLCmd is no exception. Whether you need to automate the import of data with bcp, export data to an XML file, or perform unique maintenance of databases, such as detaching, copying, and reattaching databases in the middle of the night, you’ll find that SQLCmd can help.

A less apparent benefit of SQLCmd is that you can use it to connect to SQL Server with the dedicated administrator connection when your SQL Server is not responding.

Dedicated Administrator Connection (DAC)

The dedicated administrator connection (DAC) allows you to access and troubleshoot an otherwise unresponsive SQL Server.

There are two methods you can use to connect with the DAC: SSMS and SQLCmd.

Tip

It’s important to remember that only an administrator can run the DAC. This means either that the account you are using is in the BUILTIN\Administrators group (automatically mapped to the sysadmin server role) or that your account is added to the sysadmin role. Documentation often suggests using the sa account. We’ll explore this in Chapter 9, but if you try it now, it probably won’t work. The Server Authentication must be set to mixed mode (Windows and SQL Server Authentication mode) and the sa account must be enabled. If you’re running the default installation from the exercise in this chapter, the Server Authentication is Windows Authentication, and the sa account is disabled.

In the following exercise, we connect to the dedicated administrator connection using SQLCmd.

Exercise 1.7: Connect to DAC with SQLCmd

1.      Launch a command prompt with administrator privileges using the Run As command. (This can be done on Windows XP by choosing Start | All Programs | Accessories, right-clicking Command Prompt, and selecting Run As.)

2.      At the command prompt, type SQLCmd –S servername –A, where servername is the name of your server. The –A specifies the dedicated administrator connection. For example, if your server name were MCITP, you would enter SQLCmd –S MCITP –A.

Tip

If you don’t know or remember your server name (the name of your computer), you can enter hostname at the command prompt to get it. If you’ve installed the default instance, just the server name is required in the SQLCmd statement. If you’ve installed a named instance and want to connect to the named instance, you’d enter the serverName\namedInstance. For example, if your server name were MCITP and the named instance were MyNamedInstance, you’d enter SQLCmd –S MCITP\MyNamedInstance –A While using spaces on the command line is sometimes tricky, the space after the –S switch can be included or omitted.

3.      At this point, you have access to SQL Server with the DAC. Enter the following information on lines 1 and 2. Note that 1> and 2> are generated by SQLCmd. Don’t reenter them.

4.  1> SELECT @@Version

5.  2> Go

Before you press ENTER on line 2, your display should look similar to Figure 1-17.

Figure 1-17: Using the command prompt to access the DAC

6.      You have just connected with DAC and entered a basic T-SQL command using the global variable @@Version to get the version information for your server. Press ENTER to see the results of the SELECT @@Version command.

7.      Enter Exit and press ENTER to exit SQLCmd.

Connect to DAC with SSMS

You can also connect to the DAC with SSMS. However, it’s a little tricky because we can’t use the Object Explorer with DAC.

First launch SSMS as normal; however, don’t try to connect. Instead, in the Connect To Server dialog box, click Cancel. If you try to connect using the following directions now, you will get an error saying “Dedicated administrator connections are not supported (Object Explorer).”

At this point, you have SSMS open, but without any connections. Click the New Query button to create a new query window. Another Connect To Server dialog box will appear.

Change the Server Name when connecting to the server by adding the prefix of Admin:. In the Connect To Server dialog box, change the Server Name to Admin: serverName\serverInstance (where serverNameserverInstance is the name of the instance you want to connect to). For example, when connecting to the DAC on a server named INSTVS with an instance of MyNamedInstance, you would enter Admin:INSTVS\MyNamedInstance, as in is the name of your Server and Figure 1-18. To connect to the default instance on a server named MCITP, you’d enter Admin:MCITP.

Figure 1-18: Connecting to DAC with SSMS

Once you click Connect, you will have a new query window open with the dedicated administrator connection. Remember, the SQL Browser service must be running for the dedicated administrator connection to work properly.

Exercise 1.8: Touring SSMS

1.      Launch SSMS from the Start menu as you would normally.

2.      In the Connect To Server dialog box, click Connect using the default instance.

3.      You should see something similar to Figure 1-19. Notice that across the top, you have a typical menu bar. Below that is the Standard toolbar. On the left you have Object Explorer, which shows several folders holding the objects of SQL Server. Through this book, we’ll touch on most of these folders and their subfolders. On the right is the details pane. This will change based on what we are doing within SQL Server.

Figure 1-19: SSMS

4.      Right-click anywhere on one of the toolbars to expose the context menu for the toolbars. Notice that Standard and SQL Editor toolbars are selected. Select the Help toolbar and notice that you have four additional tool buttons on your toolbar.

5.      Click the New Query button on the Standard toolbar. This will open a query window in the details pane that you can use to enter T-SQL commands.

6.      In the query window, enter

7.   SELECT @@Version;

8.      Hover over the Blue Check button (to the right of “! Execute”), and notice that the tooltip says “Parse.” This button will parse your command, or in other words, check the syntax to make sure it can run. Click the Parse button.

9.      If your syntax is correct, you’ll have a results pane open saying the “Command(s) completed successfully.”

10.  Click the “! Execute” button to run your query. In the results pane, you’ll have a line that shows version information on your SQL Server. Note that you can also press F5 to execute the query. We’ll cover Transact-SQL in much more depth in Chapter 4.

11.  By default, your results are displayed in a grid. Some queries are displayed better in text. To change the results display, choose Query | Results To | Results To Text, as shown in Figure 1-20. Execute your query again and notice the different results in Figure 1-21. It’s the same data, but just a little more readable.

Figure 1-20: Changing the query result output

Figure 1-21: A query with results in text instead of in a grid

12.  Right-click your instance name (the top line in Object Explorer) and click Properties. This opens the properties pages of this instance. Notice that you have several different properties pages that are accessible from this window such as General, Memory, Processors, and more.

13.  Click the Security properties page. This allows you to set the Server Authentication. With Windows Authentication mode, only Windows accounts (that have been granted access) can access SQL Server. If you have non-Microsoft clients that need access to your SQL Server, you can change this to SQL Server And Windows Authentication mode (sometimes called mixed mode) and create SQL Server accounts. Click Cancel to exit the properties pages.

14.  In the Object Explorer, open Databases by double-clicking Databases or by clicking the + next to Databases. You will see a folder named System Databases, a folder for Database Snapshots, and any user databases you may have. If you followed the installation exercise earlier, you should see the AdventureWorks user database.

15.  Open the AdventureWorks database folder. Here you’ll see folders holding all the objects of the AdventureWorks database. We’ll be in and out of this database throughout the book.

16.  In Object Explorer, open the Security folder. Here you can add Logins to allow people access to the server (not the database—that’s handled via Database Security). You can also configure Server Roles and Credentials. We’ll explore this more in Chapter 9.

17.  In Object Explorer, open the Server Objects folder. Here you’ll find several different objects that are used for topics such as High Availability, Data Access, and Data Recovery.

18.  Open the Replication folder. Replication is used to distribute copies or portions of your database to other servers. We’ll cover this in more depth in Chapter 11.

19.  Open the Management folder. As a database administrator, you’ll probably find yourself spending a lot of time here. You can configure and monitor Maintenance Plans, review logs, identify locks, and much more. We’ll cover elements in the Management folder throughout the book.

20.  Click the Notification Services folder. Notice that there is no content yet. Notifications are used to program methods that will send messages in response to events. Notifications are a powerful new feature of SQL Server 2005 but are not covered in the MCITP: Database Administrator certification.

21.  Click SQL Server Agent. If the service is not started, it will have a red down-arrow; if it is started and running, it will have a green up-arrow. If it’s not running, right-click SQL Server Agent and select Start. Click Yes to confirm you want to start the service. SQL Server Agent enables you to configure Jobs, Alerts, and Operators, and we’ll explore it in much more depth in Chapter 13.

System Databases

Every SQL Server instance has several system databases. You can view the System databases in SSMS. These are introduced in Table 1-6.

Table 1-6: System Databases
System Table	Description
Master	Primary system database. Holds metadata on all other databases.
Msdb	Holds SQL Server Agent information such as Jobs, Alerts, and Operators.
Model	This is the template or starting database of all new databases.
Distribution	Used for Replication.
Tempdb	Used as a workspace for temporary objects and intermediate result sets. Deleted upon shutdown. Re-created upon SQL startup.
Resource	Hidden by default. Only system database that is read-only. Contains copies of all system objects used by SQL Server that appear in each database.

Of course, these system databases are integral to the proper operation of your SQL Server. As an administrator, you should focus on the Master and MSDB databases, especially when it comes to protecting them with a disaster recovery plan.

 

 

SQL Logs

In addition to logs in Event Viewer, SQL Server logs information in different logs is accessible via Windows Explorer or SSMS.

Installation Log

When you install SQL Server, it creates a summary.txt file in the following directory:

ProgramFiles\Microsoft SQL Server\90\Setup Bootstrap\LOG\

This log file has a listing of the log files that were created during the installation. If the installation failed, you can look here to get some clues to what went wrong. For any problems, the Install section will be labeled as “Failed” instead of “Successful.”

Error Logs

Additionally, SQL Server maintains a series of error logs. These logs can be helpful in detecting any problems with SQL Server, especially related to an instance that doesn’t want to restart. You can look at the logs directly via Windows Explorer in the following directory:

ProgramFiles\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

Tip

That ./ in the MSSQL.1 path may look like a typo, but it’s not. The first instance is installed in the MSSQL.1 path. The second instance—surprise, surprise—is installed in the MSSQL.2 path. The default instance isn’t always MSSQL.1; it depends on when the default instance was installed.

Notice in Figure 1-23 that we have several instances. MSSQL.1 is where the first instance was installed, and MSSQL.5 is where the fifth instance was installed.

Figure 1-23: Windows Explorer showing folders for five SQL instances

A new log is created each time SQL Server is restarted. The last six logs are retained by default. The current log is named ErrorLog with no extension. The most recent log file is named ErrorLog.1, the previous ErrorLog.2, and so on. Each time SQL is restarted, the log files are named using the extension numbering system and a new log is created.

You can also view the logs in SSMS in the Management | SQL Server Logs folder. It allows you to view the Current and the last six archived logs. Figure 1-24 shows the Management folder opened to view the SQL Server Logs folder.

Figure 1-24: SQL Server Logs in SSMS

Self-Study Exercises

Use these additional exercises to challenge yourself.

·         Install a separate instance of SQL Server.

·         Connect to your new instance using SSMS.

·         Using Windows Explorer, locate and view the SQL Error logs for your new instance.

·         Create a service account. (If your computer is in a domain, create a domain user account. If your computer is in a workgroup, create a local user account.) Configure both MSSQL Server and SQL Server Agent services to start automatically on startup using this service account.

·         Launch a new query and run the query that will tell you what version you are running.

·         Enable your new instance to use remote dedicated administrator connection.

·         Connect to the DAC from the command line.

·         Enable the network protocols Named Pipes and TCP/IP for your new instance.