Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

The DBA's Toolbox, Part 1

Last updated Mar 28, 2003.

This guide, and indeed the entire InformIT.com site, is designed to help you succeed as a Database Technology Professional. I've got hundreds of articles here on concepts, processes and tools for everything from configuring SQL Server to working with Business Intelligence.

With all of this information, it can be overwhelming to keep up. I write an article every week, and I've been at this for over five years. There are also dozens of other professionals writing about database technology on InformIT in locations other than this guide. There is just a lot of information to digest.

Every once in a while I take an article or two and focus more specifically on how to take it all in. I'll focus on a particular set if tasks or concepts and use that information to point to more in-depth explanations. In this overview, I'd like to talk to the DBAs who are trying to put it all together.

I've always found it useful to think about three areas when I'm learning a new skill: concepts, processes, and tools. It seems to me that concepts are the things you do, processes are how you do them, and tools are the things you use to accomplish the tasks.

I've covered the general concepts for the various features and components in SQL Server in a series of tutorials called Microsoft SQL Server Features. I have hundreds of process articles dotted throughout the site, but to bring them all together I've created a DBA checklist, and there's more of those to come. Although I've covered the native SQL Server Tools for SQL Server 2000 in another article here and SQL Server 2005 here, I thought it would be useful to approach a discussion of tools just like I did in the checklist — from a task-based approach. I'll need to break this tutorial into two parts, so make sure you read the next article as well.

You might wonder whether you can do everything you need to do with SQL Server using only the tools that ship with it. You can. But in this article I'll explain which of those tools I've found work best for a particular task. And I'll also bring up some other tools that I've found very useful in my experience as a DBA.

Before I begin, I want to say a word or two about those third-party applications. I'm not recommending these tools over others; these are simply the ones I've found that worked well in the past. Many other vendors make fantastic software to work with SQL Server, and Microsoft updates their tools all the time. That's all meant to say that your mileage may vary — if you've found an even better tool to do something, you should stick with that. It's all about getting the job done quickly and correctly.

With all of that said, let's take a look at the tools you can use to work with SQL Server from a process perspective. One final note — some of these "tools" I mention won't be an EXE file that you run. In fact, you're using one of the tools now — InformIT.com. I've already mentioned how we have many articles, books, guides and so on to help you. I'll mention a few of these references along the way as "tools."

Installing and Configuring the SQL Server Platform

You have to start out with installing the package. I've explained some basic thoughts around the platform you're installing on here, but there are also multiple tools you can use to perform the installation.

You might be surprised to hear that. Most DBAs have opened a share or a CD/DVD drive, found SETUP.EXE and pressed ENTER. You're then led through a series of screens that creates the setup. But that's not the only way to install the features you want.

Rather than running SETUP.EXE alone, you can run it with a "switch," or a slash and a letter that makes it do something else. One of these switches will record your installation selections, from packages to drive locations. It can then store these selections in an "unattended installation file" (UIF), which you can use with other distribution software to duplication the installation.

In the past I've used Microsoft's SMS and even the Windows Server itself to "push" SQL Server installations. In one of the jobs I held I wrote our firm's installation package for our software that also used a UIF to install and configure the SQL Server box.

Task

Tool

Process/Reference

Install SQL Server

Installation CD

Run SETUP.EXE.

Books Online Installation Reference

Multiple Installations of SQL Server

Installation CD and Unattended Installation File

Run SETUP.EXE with command line switches.

Command-line Switches Reference

Multiple Installations of SQL Server

Microsoft SMS

Develop installation packages, select targets, set schedule.

Now called "System Center Configuration Manager"

Multiple Installations of SQL Server

Windows Server

You can use Windows Policies to push out software based on policy conditions.

I've got an overview here that will help you configure the database server once it is in place. It explains the tools you can use for that.

Designing and Implementing Databases

After the database server is up and running, you'll need to put a database or two on it. You can certainly restore a database or have one automatically installed by an application, but if you are designing your own database you have a few choices for the tools.

Before you dive into the tools, however, I recommend you learn about creating a database properly. I have a series of articles, overviews and tutorials you can use to find out more.

If you are old-school or hard-core, one of the best ways to create a database is with Transact-SQL commands. There are two editors you can use from Microsoft: SQL Server Management Studio for SQL Server 2005 or Query Analyzer for SQL Server 2000. You can also use the new Visual Studio Team Edition for Database Professionals which has a good editor.

On the third-party front, I've used tools from Idera and Quest to run queries. They have some interesting features that you should check out if this is your method for creating databases.

For heavy-duty design work before you create the database, you have more choices still. SQL Server 2000 and 2005 include something called a Data Diagram feature. It is a fairly lightweight tool that can look at a database and show you a graphical representation of the tables and relationships it has. You can add tables or modify the ones you have. Personally, I never use this tool any more. It simply isn't powerful enough, and it doesn't follow database diagram standards for the graphics.

Instead, I've used three other tools to create database designs. The first is decidedly low-tech: a pencil and paper, or more lately, PowerPoint. I do this just to get a design across to others, or to quickly sketch out something for myself. The disadvantages here are too numerous to mention, but it does work.

A better tool is Erwin. If you have multiple database platforms you have to work with, and you need to create logical and physical models and then have them created on those platforms, create reports and even help you with the design process, this is worth a look. It isn't cheap, and it has a high learning curve, but it is one of the most complete design tools out there.

But what I've been using lately is Visio Enterprise. I've reviewed the tool elsewhere on this guide so I won't re-hash that here, but one thing I will repeat here: it's best suited to SQL Server, even though it does support other systems.

You can also use programming tools to create databases. In fact, Visio is included with the Enterprise version of Visual Studio. And as I've mentioned, The Visual Studio Team Edition for Database Professionals can also help you design a database during the development process. This is a new tool from Microsoft, and it has some very powerful constructs to help you, especially with the programmatic elements.

Task

Tool

Process/Reference

Run Transact-SQL (T-SQL) Statements

SQL Server Query Analyzer (2K)

Open Query Analyzer tool, connect to a database.

InformIT overview of QA

Run Transact-SQL (T-SQL) Statements

SQL Server Management Studio (2K5)

Open SSMS, open a Query Window.

InformIT overview of SSMS

Run Transact-SQL (T-SQL) Statements

TOAD (Quest Software)

Connect to a database engine, open query.

TOAD evaluation at InformIT.com

Create Database Design Graphically

SQL Server Enterprise Manager (2K)

Connect to a server.

Right-click database folder. Select "Create New Database" from the menu that appears.

Create a database with any options you want.

Open the database, right-click the "Database Diagrams" folder, and select "Create New Diagram" from the menu. (you'll be asked about creating some objects. Let it do that).

Bypass the "Add Tables" request.

Right-click in the screen to create tables and relationships.

InformIT overview of EM

Create Database Design Graphically

SQL Server Management Studio (2K5)

Connect to a server.

Right-click database folder. Select "Create New Database" from the menu that appears.

Create a database with any options you want.

Open the database, right-click the "Database Diagrams" folder, and select "Create New Diagram" from the menu. (you'll be asked about creating some objects. Let it do that)

Bypass the "Add Tables" request.

Right-click in the screen to create tables and relationships.

InformIT overview of SSMS

Create Database Design Graphically

Visual Studio Enterprise Edition

See this reference for more information.

Create Database Design Graphically

Erwin

See this reference for more information.

Create Database Design Graphically

Visual Studio Team Edition for Database Professionals (a.k.a. "Data Dude")

See this reference for more information.

Configuring Security and Access

After you've installed the system and created a database and its objects, you need to allow people to start using them. You'll need to configure database roles, grant the appropriate rights and permissions to various objects and then create server and database logins, and finally tie those together.

I like to add Roles to a database, and then grant rights on the database objects to those Roles. I then add Logins to the server and then users to the database, and then put the users in the Roles. You can grant rights directly to users, but I don't recommend that. I'm also not discussing Application Roles in this list, but I do cover them in the Security section of this site.

You can read this series of tutorials for the process, and here are the tools you can use for that:

Task

Tool

Process/Reference

Create Database Roles

Transact-SQL Commands

Open a query tool, use the CREATE ROLE command.

CREATE ROLE Reference

Create Database Roles

Enterprise Manager (2K)

Open EM, connect to a server.

Navigate to and open a database.

Right-click Roles, Select New Database Role from the menu.

Follow the prompts.

More about Roles here.

Create Database Roles

SQL Server Management Studio (2K5)

Open SSMS, connect to a database

Open a database

Right-click the Security/Roles/Database Roles node, select New Database Role from the menu.

Follow the prompts

More about Roles here.

Grant Rights to Roles

Transact-SQL Commands

Open a query tool, use the GRANT command.

GRANT Reference

Grant Rights to Roles

Enterprise Manager (2K)

Open EM, connect to a server.

Navigate to and open a database.

Right-click an object (such as a table), Select Properties from the menu.

Click the Permissions button.

Select the permissions you want from the list that shows, assign them to the proper Role.

Follow the prompts.

More about Permissions here.

Grant Rights to Roles

SQL Server Management Studio (2K5)

Open SSMS, connect to a server.

Navigate to and open a database.

Right-click an object (such as a table), Select Properties from the menu.

Click the Permissions object.

Click the Add Button.

Select the permissions you want from the list that shows, assign them to the proper Role.

Follow the prompts.

More about Permissions here.

Create Server Logins

Transact-SQL Commands

Open a query tool, use the CREATE LOGIN command.

CREATE LOGIN Reference

Create Server Logins

Enterprise Manager (2K)

Open EM, connect to a server.

Open the Security node.

Right-click the Logins object, select New Login from the menu that appears.

Follow the prompts.

More about Server Logins here.

Create Server Logins

SQL Server Management Studio (2K5)

Open SSMS, connect to a server.

Open the Security node.

Right-click the Logins object, select New Login from the menu that appears.

Follow the prompts.

More about Server Logins here.

Create Database Users

Transact-SQL Commands

Open a query tool, use the CREATE LOGIN command.

CREATE USER reference.

Create Database Users

Enterprise Manager (2K)

Open EM, connect to a server.

Navigate to and open a database.

Right-click Users, Select New Database User from the menu.

Follow the prompts.

More about Database Users here.

Create Database Users

SQL Server Management Studio (2K5)

Open SSMS, connect to a database

Open a database

Right-click the Security/Users node, select New User from the menu.

Follow the prompts

More about Database Users here.

Add Users to Roles

Transact-SQL Commands

Open a query tool, use the CREATE LOGIN command.

ALTER GROUP reference.

Add Users to Roles

Enterprise Manager (2K)

Open EM, connect to a server.

Navigate to and open a database.

Right-click a Role, Select Properties from the menu.

Click the Add button.

Follow the prompts.

More about Roles here.

Add Users to Roles

SQL Server Management Studio (2K5)

Open SSMS, connect to a database

Open a database

Right-click the Security/Roles/Database Roles node, select Properties from the menu.

Click the Add button.

Follow the prompts

More about Roles here.

We'll stop there for now and pick up the rest of this tutorial in the next installment.

InformIT Articles

I've got a section here where I review other tools for the DBA and database developer.

Online Resources

Microsoft has more tool tutorials on these tools here.