SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 203 of 365 Next >

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.

< Back Page 203 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address