SQL Server 2005 has several editions, each with unique
capabilities or restrictions as outlined in Table
1-1.
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.
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.
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.
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.
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.
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.
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).
Look up Knowledge Base (KB) article 321185 on the Web for
more information (search KB 321185
with your favorite search engine).
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.
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.
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.
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 SD3+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 SD3+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.
