Chapter 3: Tools for Accessing SQL Server

Overview

It's said that a craftsman's work is only as good as his tools. To some degree, I agree that this principle applies to SQL Server. However, many database professionals from the old school choose not to use sophisticated tools, just as many craftsmen use tools (chisels, carving knives, and so on) to do the work that is often simplified through automation. Many would even argue that the results are different, perhaps even better, when you remove automation from the equation. Regardless of the ideals to which you subscribe, a number of tools and applications are available that you can use to create and debug queries. What tools do you need? This depends a great deal on what you need to do.

Here's a breakdown of some of the common tasks you may need to perform with SQL Server:

• Administrative Tasks

  • Creating databases

  • Creating and managing server logins and database roles and users

  • Granting and managing security permissions

  • Scheduling backups

  • Auditing and error checking

  • Diagnosing failures and application errors

  • Performance tuning

  • Configuring data replication

  • Managing disk space and data files

• Database Management Tasks

  • Adding and managing tables, views, stored procedures, and functions

  • Creating indexes

  • Creating views, stored procedures, and functions

  • Importing, exporting, or transforming data

• Data Operations

  • Inserting, updating, and deleting records

  • Supporting application features

  • Defining business rules

  • Selecting records from a table or multi-table join

Whether you are using SQL Server 2000 or SQL Server 2005, this chapter walks you though similar exercises for each version of the product. I'm assuming that you have SQL Server installed on your local computer with all of the server and client tools. This is the default setting when you run the setup. If your database server is on another computer, you will need to install the client tools on your local computer to follow these directions. I am also assuming that you are using Integrated Windows authentication and that your Windows account has sufficient permissions to create objects and run queries against the database server. If you have installed SQL Server on your local computer with default options, this should be the case.

If you are working with a remote database server, you should talk to your system administrator and make sure you have the client tools correctly installed and that you have the appropriate permissions to run queries. As you work through these exercises, the only difference will be that you will be connecting to a remote server rather than the local server.