316
C
HAPTER
23
Mobile data strategies
Unlike
svcutil.exe
, this process will not generate a .config file to store the endpoint
information. This information is stored in the client proxy code.
To use this tool:
1
Open a Visual Studio command window.
2
Change the directory to your mobile application directory.
3
Enter this command:
NETCFsvcutil.exe http://<machinename>/yourservice/service.svc
The preceding command should generate two source files. The one named
after your service will contain the proxy class you will use to consume the
WCF
service you created. Add these classes to your project and compile the code.
The code can be found at this book’s website, http:
//www.manning.com/
SQLServerMVPDeepDives.
Comparison
Much thought has been given to creating solutions to ensure that data remains in sync
during periods of connectivity. All of the techniques—
RDA
,
MR
, and

Summary
copies the functionality of
MR
. If you require a
DBA
-centric approach with little code
to set up the process, then
MR
is your choice. Both
MSF
and
MR
will keep your data in
sync. With the techniques and code samples we have shown in this chapter, you will be
able to take advantage of these methodologies to transfer, create, modify, and repli-
cate your data from your mobile device to your desktop server.
About the author
John Baird began his computer programming career while in the
US
Navy. In 1982, he helped form and direct the first
PC
-based
computer processing department for training and manpower in
the Department of Defense. After leaving the military in 1988,
John began a varied career as a consultant developing business
applications ranging from computer-based training to vertical
market software for resellers.
Today, John is working for the industry leader in financial soft-
ware for fund administration. John was awarded his
MVP

one can know everything, and a good
DBA
recognizes that—even embraces it. A
good
DBA
is always looking to improve their systems, learn tips and tricks, and
prototype new configurations. A good
DBA
sets up alerts to be notified of errors
and problems as soon as they begin to happen, possibly solving them before they
turn into disasters. Just as in life, the sooner you know there’s a problem, the eas-
ier it may be to fix! (Did you know that you can set up
SQL
Server Alerts based on
error severity levels—not just specific error numbers? For severity levels 19 and
higher, a generic alert should notify you that an error has occurred.)
So, how does a
DBA
make this happen? Not only does a good
DBA
proactively
look for faults before they become catastrophic but, when something does go
wrong, a good
DBA
takes the time to do a root-cause analysis to make sure the risk
is avoided in the future. Leveraging the
SQL
Server toolkit is imperative in mak-
ing this happen. Auditing, leveraging the Dynamic Management Views (
DMV

skills. Many of these topics are the particular passions of the authors, and they’ve
donated their time and experiences to help us all become better
DBA
s. We truly want
to thank them for their time and especially their passion.

SQL
Server is a passion for both of us and we’ve enjoyed reading and learning from
these chapters and experiences. Whether you’re a seasoned
DBA
or a newcomer to
SQL
Server, we know you’ll find material in this section that will help you learn, grow, and
become a better
DBA
.
About the editors
Paul S. Randal and Kimberly L. Tripp are a hus-
band-and-wife team who own and run SQL-
skills.com, a world-renowned
SQL
Server consulting
and training company. They are both
SQL
Server
MVP
s and Microsoft Regional Directors, with over
30 years of combined experience on
SQL
Server.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
321
24 What does it mean
to be a DBA?
Brad M. McGehee
If you ask ten database administrators (
DBA
s) what their job roles are, you’ll get
ten different answers. For example, one organization may need a
DBA
to manage
the day-to-day administration of
SQL
Server instances, whereas another organiza-
tion may want a
DBA
to write both Transact-
SQL
(
T-SQL
) and
C#
code. Another
organization might want a
DBA
who specializes in
SQL
Server Integration Services
(
SSIS

s commonly per-
form. The second section classifies these tasks into specialty roles, as you will rarely
find a
DBA
who performs all of these tasks all the time. Throughout this chapter, I try
to answer the question: “What does it mean to be a
DBA
?”
Typical DBA tasks: A to Z
DBA
s perform so many different tasks that it is hard to categorize them all. The
alphabetized list that follows outlines the common
DBA
tasks. This list is by no
means comprehensive, and some of the tasks overlap.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
322
C
HAPTER
24
What does it mean to be a DBA?
APPLICATION INTEGRATION
Most organizations use some combination of in-house and third-party applications;
and few of these applications work in isolation. Therefore,
DBA
s often have to make
disparate applications talk to one another, using a database as the means of sharing
data.
DBA

periods, for specific data, or it might be required 24/7 for all data.
DBA
s often work
with people outside their organization, such as external auditors, to perform this task.
And to further complicate the lives of
DBA
s, many industries are subject to specific
regulations on how data is accessed and protected, which means that
DBA
s have to
comply with some specific rules, with the potential for significant penalties, should
they not fully comply.
BACKUP AND RECOVERY
One of the most fundamental aspects of the
DBA
’s job is to protect the organization’s
data. This includes making periodic backups of data and keeping it safe from acciden-
tal or intentional destruction. In addition, a well-developed recovery plan needs to be
implemented and tested so that when problems arise, data and access to critical sys-
tems can be restored quickly.
BUSINESS INTELLIGENCE AND DATA WAREHOUSING
Another emerging trend is for
DBA
s to be tasked with the creation and maintenance
of data warehouses and business intelligence applications, so organizations can better
use their data to make more effective business decisions. Increasingly,
DBA
s are spe-
cializing in this fast-growing area.
Licensed to Kerri Ross <[email protected]>

DBA
s are application developers who specialize in writing code to access data
stored in
SQL
Server databases. Although this is most commonly done using
T-SQL
, it
can include writing code that is used to access
SQL
Server data. Many
DBA
s decide to
make this their area of specialty, because of the depth of knowledge required to be a
good database applications developer.
DEVELOPING AND MAINTAINING BEST PRACTICES
Successful
DBA
s are proactive in their work, and one of the best ways to be proactive is
to develop sound database best practices, and to implement them in a consistent man-
ner. The better organized and managed the database operations, the more efficient
they will be. One of the many ways that
DBA
s can perform this task is to create a run-
book that describes the organization’s best practices and internal procedures. This
document is the
DBA
’s bible for performing all
DBA
-related functions.
DISASTER RECOVERY

24
What does it mean to be a DBA?
log shipping, clustering, database mirroring, and other technologies. Because of the
specialized knowledge required for high availability techniques, many
DBA
s choose
this as their main area of focus.
INSTALLING, CONFIGURING, AND UPGRADING SQL SERVER SOFTWARE
One of the most time-consuming of all database tasks is installing, configuring, patch-
ing, and upgrading
SQL
Server instances. Although it might sound like an easy task, it
usually isn’t. One of the difficulties is finding a good time to perform such work, espe-
cially in 24/7 shops. In addition, each time
SQL
Server is patched or upgraded, there
is the risk that after the patch or upgrade the applications that access the database, or
the databases themselves, might not work properly. On top of this,
DBA
s often have to
battle third-party vendors who are unwilling to certify that their application will work
after you have made the changes. This requires the completion of planning, testing,
and backout preparation before implementing major changes.
LOAD BALANCING
Over time, the load put on individual databases changes.
DBA
s are responsible for
monitoring workloads and figuring out how to maximize hardware resources to get
the best
SQL

s. Some
DBA
s at large organizations do this full time, whereas others
combine people management with other DBA duties. On the other hand, when some
DBA
s get a taste of management, they give it up to get back to more technical work.
Managing people is not for everyone.
MANAGING SQL SERVER–BASED APPLICATIONS
A surprise for many new
DBA
s is the realization that they are responsible not only for
managing
SQL
Server and its databases, but in many organizations, they are also
responsible for managing any applications that access the database. In some organiza-
tions, the
DBA
spends more time managing applications than
SQL
Server itself.
MANAGING TEST ENVIRONMENTS
In many organizations,
DBA
s manage test environments that not only include test
SQL
Servers and test databases, but also test applications. The purpose of this is to allow
applications (both in-house and third-party) to be tested before new versions of either
the application or
SQL
Server are rolled out into production.

In many organizations,
DBA
s become involved in negotiating service level agreements
(
SLA
s). An
SLA
is an agreement between parties in which one is the customer and the
other is the service provider. The customer (the owner of the business application
accessing
SQL
Server databases) and the
DBA
(the service provider), must agree on
acceptable levels of service, such as defining what is an acceptable response time for a
specific type of transaction. Part of the negotiating process often requires the
DBA
to
set expectations and educate the customer on what is feasible given the resource con-
straints of the organization.
OPERATING SYSTEM SETUP, CONFIGURATION, AND ADMINISTRATION
Just as many
DBA
s manage their own hardware, many
DBA
s also manage the operating
system on their
SQL
Server instances. Not only must
SQL

SQL
Server replication.
DBA
s often research various ways
data can be replicated from server to server, decide on the most appropriate method,
implement replication, and then manage it after it is up and running.
REPORT WRITING
With the advent of
SQL
Server Reporting Services, many
DBA
s find themselves writing
reports against databases. This might include writing the
T-SQL
code to extract the
data, or it could include the creation and formatting of physical reports. This is a new
and growing area, and some
DBA
s are specializing in it.
RUNNING JOBS
Virtually every
SQL
Server has jobs that run periodically. They might include backups,
data imports or exports, index rebuild jobs, and data integrity checks.
DBA
s are respon-
sible for determining what jobs are needed, creating the jobs, and scheduling them.
SCRIPTING
DBAs often write their own
T-SQL

DBA
s test
because they want to ensure the high availability and integrity of their data.
TROUBLESHOOTING
Virtually every day,
DBA
s troubleshoot one problem or another. Often when a prob-
lem occurs, the
DBA
is expected to drop everything and focus on resolving the prob-
lem at hand. This can result in long hours and high levels of stress. As with many
technical positions, effective
DBA
s are good problem solvers.
WORKING WITH TEAMMATES
DBA
s rarely work alone. In most cases,
DBA
s interact with many people, including
other
DBA
s, developers, end users, product-knowledge specialists, vendors, accoun-
tants, hardware experts, and networking experts. Because of this,
DBA
s need good
people skills.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
327
DBA specialties

s specialize.
DBA SYSTEM ADMINISTRATOR
The
DBA
system administrator is a generalist who knows a little about many different
subjects. Also known as a production
DBA
, the
DBA
system administrator is generally
in charge of setting up, configuring, and maintaining test and production
SQL
Server
instances. This can include such routine tasks as monitoring, performance tuning,
backups and restores, security, creating jobs, and so on. In medical terms, think of the
DBA
system administrator as the general practitioner. All of the other
DBA
specialists
are more like medical specialists.
DBA DATABASE ARCHITECT
Think of a
DBA
database architect as the employee who sees the big picture of data
storage and access in an organization, and whose job it is to research the organiza-
tion’s data needs, research the available options, and then recommend policies the
organization should implement. In many organizations, the title of database architect
is another name for the lead
DBA
,

tors,
DBA
database designers, project managers, and other developers to develop and
maintain applications.
DBA HIGH AVAILABILITY AND DISASTER RECOVERY SPECIALIST
For many of today’s online businesses, it is more critical than ever that
SQL
Server is
available around the clock. Therefore,
DBA
s often need to specialize in high availabil-
ity. Their job is to determine what high availability methods are best for their environ-
ment, and then to implement and maintain them, helping to prevent the loss of data
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
328
C
HAPTER
24
What does it mean to be a DBA?
in the first place. Other
DBA
s specialize in disaster recovery, after the fact. In other
words, they are brought in to try to recover data from corrupted databases because
existing high availability and disaster recovery procedures failed.
DBA BUSINESS INTELLIGENCE SPECIALIST
DBA

BI
(business intelligence) specialists design, create, and maintain data ware-

list all the possible
DBA
specialties on which you might want to focus, so don’t con-
sider the list in this chapter to be anything more than an informal guide.
You may find that you want to develop your own specialty, one that is even more
focused than the broad specializations I’ve listed. For example, you might want to focus
on performance tuning, clustering,
SSIS
, or replication only. Nothing prevents you
from specializing as much as you like, other than the fact that the more narrow your
specialty, the fewer jobs available.
As we have seen, being a
DBA
means many different things. It is not a single job title
with a single skill set. Instead, the job title of
DBA
encompasses a wide range of job tasks
and roles that are tied together with a single common denominator—
DBA
s are the
guardians of the organization’s data. As
DBA
s, it is our job not only to protect an orga-
nization’s data, but also to make it available to everyone in the organization who needs
it to perform their jobs effectively. How well we fulfill this responsibility determines
how successful we are as
DBA
s.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

SQL
Connections, SQLTeach, SQLBits,
SQL
Saturdays,
TechFests, Code Camps,
SQL
Server user groups, and other
industry seminars. He blogs at www.bradmcgehee.com
.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
330
25 Working with
maintenance plans
Tibor Karaszi
Just like a car or a house, you want to maintain your databases so that they run
smoothly and safely over time. You probably have actions you want to perform that
are specific to your application and data, but some actions are common to most
databases, like backups and defragmentation. This is why Microsoft created mainte-
nance plans that make the implementation of standard actions easy. We specify
which of these standard actions we want to perform, for which databases, and at
what intervals—no
T-SQL
coding required. Of course, some
DBA
s want more con-
trol over the actions than the maintenance plans allow for, but for those who are
not full-time
DBA
s, maintenance plans are a good solution.

inside
SSMS
. For beginners, I recommend using the wizard, and, as you get more
comfortable using
MP
s, you can dive into the Designer.
The
SSIS
package is saved directly to the msdb database (not out in the filesystem
as a .dtsx file) along with some metadata in the msdb database, indicating that the
plan exists. This metadata allows
SSMS
to list your plans in the Maintenance Plans
folder, where you can right-click and edit them. If you choose to schedule the plan,
which you typically will want to do, then one or more Agent jobs will be created.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
331
What is a maintenance plan, and how do I create one?
Versions and service packs
If you are using
SQL
Server 2005, I strongly encourage you to be at least on Service
Pack 2 (
SP2
) on both the server and the client (
SSMS
).
SP2
added some functionality,

An
SSIS
package consists of several
SSIS
tasks, where each task is of a certain type
(backup database, check integrity, and so on). You decide whether to schedule the plan
(typically you will want to do this), which will create one or several Agent jobs, where
each job has one Agent schedule.
As of
SP2
, you can decide whether you want to have one schedule per task or one
schedule for the entire plan (the latter was the only option prior to
SQL
Server 2005
SP2
). Having several schedules will result in several Agent jobs—one per schedule.
There are advantages with both alternatives; therefore, think a few minutes about how
you want to do this. As an example, say you want to perform these tasks:

Weekly defragmentation and removal of backup history

Daily database backup, removal of old backup files, and checking of database
integrity

Transaction log backup every 15 minutes
If you have one schedule per task, you can have only one plan, but you will end up
with six Agent jobs (called subplans), each scheduled separately. This means you have
to decide for each job when it is to be executed. If you don’t want the jobs to overlap
each other, you’ll have to plan out the timing.
On the other hand, if you want only one schedule for the plan, you will end up