This chapter covers what’s new in enterprise database administration. It doesn’t just look at the product from a feature list, but groups the features according to some tasks common to database administration.

This chapter is from the book

This chapter is from the book

In the
beta
history
of the
SQL
Server
2005
release,
significant
emphasis
has
been
put
on the
new
features
for
developing
applications.
Let’s
just
say
that
Microsoft
loves
developers.
But
what
happened
to the
features
for
those
who
get
paged
in the
middle
of the
night
when
mission-critical
systems
hang?
This
chapter
looks
at features
for
the
unsung
heroes
of corporate
IT—the
database
administrator
(DBA).
For
the
DBA,
SQL
Server
2005
will
change
everything
about
the
way
administration
is accomplished.

This
chapter
covers
what’s
new
in enterprise
database
administration.
It doesn’t
just
look
at the
product
from
a feature
list,
but
groups
the
features
according
to some
tasks
common
to database
administration.
This
chapter
specifically
discusses
the
following:

Infrastructure
management. How
do installation and configuration
work?

SQL
Server monitoring. We’ll
divide this into reactive and
proactive and see how it gets
done.

Then
we’ll
look
at one
of the
major
efforts
for
the
SQL
Server
2005
product—the
emergence
of Very
Large
Database
(VLDB)
management—especially
in the
area
of backup
and
recovery.
Along
the
way,
we’ll
cover
replication,
high
availability,
general
data
availability,
and
business
intelligence
for
the
database
admin.

Before
we get
to those
features,
though,
we should
look
at the
new
tool
set,
because
it is
a major
shift
from
Microsoft
Management
Console–(MMC)
based
tools
to Visual
Studio.
For
the
database
developer
and
administrator,
user
interaction
and
the
tool
set
have
many
similarities.
Hopefully,
in the
end,
this
will
lead
to greater
productivity
and
better-quality
database
applications.

At
a Glance: What’s
New for the Database Administrator

Microsoft
SQL
Server
2005
represents
the
cohesion
of
the
developer
and
administrator
instrumentation.
Of
all
the
Microsoft
Server
products,
SQL
Server
has
made
the
most
significant
progress
related
to
scalable
and
extensible
database
management
and
authoring
tools.
SQL
Server
management
tools
now
include
the
following:

A new authoring, management, and
operations tool suite. Enterprise
Manager, Query Analyzer, and more
have been replaced with an integrated
tool set known as SQL Server Management
Studio.

New APIs are included for remote
management of SQL Server database
servers.

New technologies are introduced
for removing barriers to availability,
both general and high availability,
via a portfolio of technologies.

Routing administrative tasks such
as backup and restore have been
enhanced to decrease the maintenance
and recovery windows, allowing
for greater database availability.

The
new
management
tools
range
from
the
small,
such
as creating
synonyms
for
database
objects,
to the
dramatic,
such
as the
introduction
of .NET
assemblies
into
the
database.
What’s
more,
the
tools
used
by DBAs
have
been
completely
redesigned
and
rewritten.

When
we look
at SQL
Server
2005
from
a database
administration
point
of view,
we can
group
the
features
around
certain
job
functions.
For
example,
what
features
does
SQL
Server
2005
have
for
remote
management
of servers,
including
setup?
What
features
will
allow
a DBA
to find
and
mend
a blocking
process
or a
poorly
configured
stored
procedure?
One
of the
big
challenges
that
DBAs
face
is how
to keep
database
systems
available
as applications
and
databases
constantly
change.
What
about
the
mundane
but
important
tasks,
such
as disaster
planning,
security
maintenance,
resource
allocation,
and
modeling
of future
resource
needs?
How
does
SQL
Server
2005
address
these
issues?

Additionally,
as database
products
conform
more
to standards,
and
the
manufacturers
copy
each
other,
the
question
then
becomes, "What’s
innovative?" What
has
Microsoft
delivered
in features
that
will
solve
a problem
you
will
have
tomorrow
because
Microsoft
is thinking
ahead?
When
we look
at SQL
Server
2005,
it’s
important
to separate
the
features
from
the
marketing
message.
Sure,
Database
Snapshot
is new
and
innovative,
but
is it
useful?
The
release
of any
product
is a
combination
of reaching
for
future
capabilities—the
next
big
thing—and
making
the
product
solve
the
most
common
issues,
making
the
product
more
complete.
In the
case
of Database
Snapshot,
its
usefulness
is constrained
by its
usage
scenarios.
It’s
always
interesting
to hear
from
customers
how
they
found
a new
use
for
a particular
technology.
Sometimes,
these
creative
usages
are
the
cause
of customers’ issues.
Other
times
they
find
legitimate
new
uses
that
then
influence
new
features
in the
next
release.
This
chapter
looks
at the
database
management
features
not
as a
feature
list,
but
from
a DBA
task
orientation
perspective.
Before
we do
that,
however,
we must
look
at the
center
of all
the
change:
SQL
Server
Management
Studio.

SQL Server Management Studio

In SQL Server 7 and 2000, the tools suite was based entirely on MMC. The MMC
tool is not designed for real scalability. Customers complained that Enterprise
Manager took a long time to open extremely large databases with complex schema.
The fact is that Microsoft develops products in a "We’ll get
there" style. SQL Server Management Studio is one of the few
"We’ve gotten there" tools supplied by Microsoft. Compared to
other management tools delivered by Microsoft, SQL Server Management Studio is
brilliant. In contrast, you can look at replication; it’s still lacking in
clear tools strategy. First, know that SQL Server Management Studio is built on
the same underpinnings as Visual Studio 2005. Things such as Help and the myriad
of panes can really clutter up your screen. On the upside, SQL Server Management
Studio includes the following:

Full support for management of instances of SQL Server 7.0, SQL Server
2000, SQL Server 2005, and Analysis Services 2005. Management Studio dialogs
automatically customize to show only the appropriate choices and features,
depending on the version of the database server the user is working with.
Nonmodal dialogs allow the user to multitask and do more things at
once.

A new integrated Query Editor lets you create queries for all the SQL
Server technologies. Additionally, the Query Editor has customization
capabilities that make it easier to work with large batch files and complex
queries.

Built-in support for source control. Whether you’re using
Microsoft SourceSafe or Visual Studio Team System, SQL Server files can be
controlled in the same way as other development pieces. You can use any
source-control system that uses the Source Safe Control Interface API.

The SQL Server Management Studio implements the SQL Server Management Objects
(SMO), which is a new set of managed classes that replace the SQL Server Data
Management Objects (SQL-DMO). This major architectural change brings significant
enhancements in performance.

SMO’s first important optimization over SQL-DMO is delayed
instantiation. As you run your application, SMO retrieves objects and properties
as needed. You’ll notice this right away in the Object Explorer. The key
to this optimization is making many small round-trips to the server instead of
getting everything up front, as SQL-DMO does and which is overkill in many
scenarios. SMO also lets you prefetch entire collections. In addition, you can
retrieve objects by using a set of predefined properties. The bottom line is
that the programmer has control over SMO behavior, which lets you build an
application that suits your needs.

The SMO object model is also cached, meaning that it doesn’t propagate
object changes to the server immediately. Instead, it caches them until you
decide to apply (or discard) the changes. This caching yields fewer round-trips
to the server because all changes are sent as one set of batches.

SMO provides advanced scripting functionality as part of the new Scripter
object. This object lets you discover database-object dependencies, which
results in an object tree. You can create an ordered object list from that tree
and then generate a script from the list and optionally specify scripting
options (a superset of SQL-DMO’s scripting options). This architecture
gives you maximum control over each scripting phase, letting you build
specialized, customized scripting solutions.

Additionally, SMO includes a script-capture mode that lets you capture the
Transact-SQL code that SMO generates when your application performs an operation
on an object. For example, a Visual Basic guru can use SMO to grab the
Transact-SQL that his or her application generates.

Now that you understand the architectural structure of the how the Management
Studio works, let’s look at the tools in more detail.

A Connected or Disconnected State

Before you get started with SQL Server Management Studio, you’ll notice
something radical. The old Enterprise Manager user interface is gone. Moreover,
you now have a new connection dialog to work with. The connection dialog allows
users to provide both logon credentials and specific connection properties. The
connection dialog can connect directly to SQL Server Engine, Analysis Services,
Reporting Services, Integration Services, and SQL Server Mobile Edition. The
Mobile Edition connection is interesting because the mobile database is often
found on a Pocket PC device.

Besides being able to connect to previous versions—meaning SQL Server
2000 and SQL Server 7.0—the connection dialog allows you to decide which
database, network method (TCP/IP), named pipes, and shared memory is used to
connect. Additionally, you can choose to encrypt your connection and provide
specifics such as database, connection time-out, and network packet size.

As soon as you are connected to a database server, you immediately notice the
new layout of the windows. As with previous versions, you can view registered
servers. You can review the database objects found under the registered server
via the new Object Explorer window. It’s important to note that you see
only objects you have security permissions for.

Object Explorer

At the highest level, the database, not the server, is the central container
for all the objects found therein. This is a significant departure that makes
good sense. First, it’s more secure, because the metadata security found
in SQL Server 2005 allows for least privileges—all the way down to the
database. On another level, having the database as the central axis for all the
objects related to the database makes administration easier. When you click the
plus symbol next to a database, only those objects directly related are sent
back. In previous versions, you had to write queries to get back all the objects
related to a database. In large database deployments such as SAP, this new
organizational model is a time-saver.

Microsoft strives to not do any take-backs on features between releases. The
new SQL Server Management Studio is no exception. I recommend taking the time to
get to know the features found in SQL Server 2000 that are renamed and moved in
SQL Server 2005. You can still do the following:

Create a database diagram

Create database tables via the Visual Database Tools (VDT)

Create security objects

Create replication objects such as publications

Monitor replication

To create a database diagram, you will find a new folder called Database
Diagrams under the specific database objects found in the Object Explorer. In
Figure
3-1, notice how the database is now the new container for all the
subordinate objects. This is a big improvement over previous versions.

Creating a Database

There are multiple ways to create a database in SQL Server 2005. My favorite
is to right-click the Databases folder in the Object Explorer and select New
Database. This pulls up the friendly new Create Database dialog. This nonmodal
dialog is an easy-to-use tool for getting all your database settings worked out.
It has three tabs. The first, General, supplies the needed naming text boxes.
The second tab, Options, contains more specific settings such as auto-shrink and
auto-close. You can also specify the cursor behavior, several miscellaneous
settings (as Microsoft calls them), and recovery and state values. The third
tab, Filegroups, is where you build file group allocations.

The Create Database dialog gives you the option of scripting all the settings
to a Query Editor window, a file, the Clipboard, or a job. I find this new
feature quite handy. I usually script my new databases, save the files in
SourceSafe, and then start to build the objects. If your company has a standard
database format, the scripting feature will not be lost on you.

Creating Tables

With your database in place, you can start creating tables. If you are
designing a table structure from scratch, you can use the VDT and create the
database via a database diagram, or you can right-click the table folder and
select New Table. I prefer to work with the database diagram, because I like to
build relationships between tables visually. Whenever you save the database
diagram, the tables are created. This makes iteration very easy. Generally, I
try to create all my tables, right-click the database icon, and select Generate
Scripts to capture all the changes to the database structure. The script is
again checked into source control to allow for rolling back if there’s an
issue.

If you aren’t a visual person, you might want to use the built-in
templates. You can find them in the new Template Explorer box; select View,
Templates. With your database and tables in place, let’s look at the new
Query Editor.

Query Editor

SQL Server Management Studio contains a host of new features. One of the
first tools you’ll use is the new Query Editor (QE), which replaces Query
Analyzer. QE is much more than a simple query text writing application. QE
provides the following:

Disconnected editing to allow access to the Query Editor without
establishing a connection to an instance of SQL Server.

Color coding of Transact-SQL syntax to improve the readability of
complex statements.

Automatic statement formatting, including automatic indenting.

Templates that can be used to speed development of Transact-SQL
statements for creating SQL Server objects. Templates are files that include the
basic structure of the Transact-SQL statements needed to create objects in a
database.

A graphical diagram of the showplan information showing the logical
steps built into the execution plan of a Transact-SQL statement.

The ability to organize work items into solutions, projects, and files
using a specialized folder structure.

Nonmodal Dialogs

One of the more interesting and useful changes to how DBAs and developers
will use SQL Server Management Studio is found in the new user interface dialog
boxes. In previous versions of SQL Server, an administrator would use the Backup
Database Wizard and execute a backup job. The dialog for accomplishing this was
modal, meaning that you had to wait until the job finished. In very large or
slow operations, this wastes considerable time. SQL Server 2005 changes this
with nonmodal dialogs. The new dialog box style provides more of the information
you need to accomplish a certain task, but the walk-through wizard is gone.

Let’s say a DBA needs to perform several tasks, including creating a
backup job, writing a Transact-SQL script to create a database, and adding a
user account. The administrator launches SQL Server Management Studio and
creates and executes the backup job. The backup job takes some time to complete,
but the administrator can perform the other tasks because the backup dialog is
no longer modal. The new dialog style offers the following features:

Scripting from any dialog box. Administrators and developers can create
a script from any dialog box so that you can read, modify, store, and reuse the
scripts. Scripts can be written directly to a Query Editor window, to a file, or
to the clipboard.

Scheduling or immediate execution of management actions. Every
management action can be scheduled in the SQL Server Agent or run
immediately.

As you can see in Figure
3-2, the nonmodal dialog is both more complex and
more flexible. Managers shouldn’t hear database administrators say they
are waiting on an action to complete before doing the next task.

Now that you understand the basics of SQL Server Management Studio, we can
look at how SQL Server 2005 changes how day-to-day tasks are accomplished. One
last thing to remember about SQL Server Management Studio is that you can only
see and act on items at the level of security authorization for the login used
to connect to SQL Server.

Customizing the SQL Server Management Studio

After you get over the shock of the new layout, you’ll want to start
changing it to meet your work style. You have several ways to customize the look
and feel of the "shell." You can use the Views menu to add and remove
toolbars. If you are familiar with Visual Studio, these are easy to understand.
Additionally, you can change the look and feel of the free-form Query Editor by
using line numbering. When you use the Go verb, you can collapse and expand
large blocks of text.

One of the more useful changes you can make is changing the keyboard scheme
to reflect SQL Server 2000. By default, the F5 key doesn’t execute the
current window queries. Figure
3-3 shows the Options window, with the keyboard
scheme set to SQL Server 2000. You can customize any of the keys, which can save
typing and possible headaches.

Figure 3-3 The Options
screen, found under the Tools menu in SQL Management Studio.

Projects and Solutions Using SQL Server Management Studio

When Microsoft decided to bring together the database management and
development tool set, one of the key attractions was the ability to use a source
control system to manage database projects. SQL Server Management Studio can
take advantage of any source control system. More importantly, SQL projects can
now be organized via a project hierarchy. To use a project, simply select File,
New Project in Management Studio. Figure
3-4 shows a typical SQL Server
project.

It’s a little confusing how Microsoft has set up this system. When you
go to the File menu to create a new project, you see the solution name included
in the dialog. The folders are organized with one automatically created project.
The project contains folders for connections, queries, and miscellaneous items.
If you right-click the solution name, which is the highest-level folder, you can
add and remove projects and even import other projects. This method of
organization allows for easier working. I find projects useful, because you can
have a single solution with projects that contain items for each phase of
development. Combining this methodology with source control means that I have an
organized and efficient approach to working with database objects.

The ability to define a connection, or a connected use for each object in my
project, has some usefulness. Let’s say that you’re developing an
application that has several users. Each user has a specific set of privileges,
and those privileges affect query execution. You could create a separate
connection for each user. With this connection and associated query, you could
test the query under the user’s security roles, which helps expose issues
with the batches and security settings.

Getting Help

Although it doesn’t need an entire chapter, the new SQL Server Books
Online contains some new functionality worth mentioning. Books Online now
includes not only local search capability, but also configurable
automatic/simultaneous searching on the Internet. The Books Online Internet
search gives you results from MSDN, CodeZone Communities, and more. You can
customize the search results. Open Books Online by clicking the F1 button, and
select Tools, Options. Figure
3-5 shows the options available for searching.