Brian Freeman

Data Transformation Services

At some point in the life of most databases, there is a need to transfer large amounts of data into or out of the database. The two tools SQL Server provides for this task are BCP (bulk copy program) and DTS (Data Transformation Services).

Creating a Database

Now that you’ve successfully installed SQL Server, you’re going to move on to actually use it and look into creating and using databases. A new database is essentially an empty building—a library. It’s the shelving and the books on the shelving that makes it a library, and in this case the shelving is the database tables.

SQL Server Installation

We’ve covered the DBA’s role in an organization and how you might become one. From this chapter onward, we’re going to look at the practical, hands-on technical details that DBAs must deal with in their day-to-day life.

Monitor, Profile, and Tune

Using a tool and understanding how the tool should be used are two very different things. Even the most expensive tool won’t get a job done any faster unless you know how to take full advantage of its features. In this chapter, you’ll fine-tune a SQL Server instance for performance, identify some areas to look at proactively so that you can prevent situations in which people complain that the database is slow, and lay out some practical approaches for resolving performance problems.

Being a DBA

This book is a guide to the tasks required to install, configure, and maintain a SQL Server database environment. It is assumed that you may not be a dedicated database administrator (DBA); indeed, you may not even consider yourself a DBA at all. Instead, you may be, willingly or unwillingly, responsible for areas of SQL Server administration and maintenance as part of your wider company role. Indeed, it is not uncommon to find system engineers, developers, help-desk staff, and operations staff (among others) responsible for the day-to-day management of a SQL Server installation. This book presents information relevant to all the previously mentioned parties while still remaining a useful tutorial for the dedicated novice DBA.

DML and Querying

In the previous chapter we discussed the creation of databases and tables; now you need to understand how to get information in and out of those tables. While initially you may think that the querying of tables is a task for developers (not database administrators), in reality you cannot effectively manage a production SQL Server environment without a proficiency in SQL. Over time, SQL will become like a second language to you, and a query tool such as Query Analyzer will become a commonly run application on your workstation.

Data Warehousing and Analysis

Data warehousing has been around for over two decades. Yet, until a few years ago, only large companies could afford to build and maintain data warehouses. Microsoft changed this by introducing OLAP Services with SQL Server version 7.0 (in SQL Server 2000, OLAP Services are referred to as Analysis Services). What this means to you, as a DBA (database administrator), is that you and your colleagues are now more likely to develop and maintain a data warehouse than ever before.

Basic Management Tasks

Basic database management is the core of a database administrator’s (DBA) role. These tasks aren’t glamorous, difficult, or interesting, but the simple things that are necessary to keep any production SQL Server environment functioning.

Backup and Recovery

Backing up a database is the most important tasks of a DBA. By definition, this makes this chapter one of the most important in the book. So what do we plan on covering in this chapter?

Development with Transact-SQL

As a database administrator (DBA), writing Transact-SQL (T-SQL) code may or may not be your direct responsibility. However, as a DBA it’s your responsibility to ensure that database routines do what they’re supposed to do, and that these routines are as efficient as possible. Knowledge of T-SQL will provide you with the ability to determine which code is acceptable for your database. It will also allow you to set coding standards for your organization’s developers.