Beginning SQL Server 2000 Administration Part 1

Beginning SQL Server 2000 Administration Part 1

I often see questions and posts about how you get started with SQL Server 2000. Mostly from people whose job is something else, but got stuck with the task of administering or working with SQL Server 2000. This article is geared more towards network type administrators, but developers may find it interesting as well.

I'm not going to explain how to install the server, what's required, etc. All of that is covered ad nauseam in other places and besides, chances are there already is a server and it's been dropped in your lap. See some of the references for more basic information on the product. So here are some hints, tips, and resources for getting started with SQL Server 2000 Administration.

What's This Server?

The first things you should understand is what SQL Server 2000 is and what it is not. It's a database, as is Access, but this is a server, which means that some user account, could be local system, could be a domain user, could be your account, actually logs in and starts a process or service. In this case, it's the MSSQLServer service. I know it could be different with named instances, but this is a beginner's article, so let's stick with that.

This is an important point, and one that sometimes eludes people, even those not new to SQL Server. This is truly a server application, which is fundamentally different from something like Access. How so? Read on and let me try and explain it.

When you open an Access database, you actually (or your machine) downloads the file into memory and reads the file. The Access application, running on your machine, interprets and displays the data and any SQL you write (or pick from the GUI) is executed on your machine by the Access application against the file you have open. It's very similar to you pressing CTRL-F in Word and having Word search for some phrase.

This is not what happens with SQL Server. Instead, picture some guy, we'll call him SQLGuy. He sits at the server (MyServer) and when it boots, he logs in. His account is MSSQLServer and he opens up a chat session on port 1434. He opens up your database file using Excel (actually this is a whole lot of spreadsheets that correspond to your tables) along with a security matrix and has them sitting on his screen while he reads the paper and waits for a chat message to come in.

You fire up Query Analyzer and connect to "MyServer", looking for the "Northwind" database. Your connection fires up a chat session on the server and SQLGuy checks out your connection. After consulting his security matrix, he sees that you have been granted the right to log on as well as rights to the "Northwind" collection of spreadsheets, so he accepts your chat session and waits. You see the chat session (your query window) open and type:

select * from customers

and hit ALT-X (or CTRL-E). This sends the "chat" to the server. SQLGuy reads this message and again consults his security matrix to see if you have access to this particular spreadsheet. You do, so he cuts and pastes the entire "Customers" spreadsheet into the chat session and sends it back to you. Query Analyzer is pretty smart on your side and interprets this data, placing it in a grid at the bottom of window.

Is this really what happens? No, but it could be. And it shows how SQL Server functions. The SERVER responds to your requests (under the service account login), decides if you have access, and then retrieves (or stores) the information requested and passes the results back to you. You (or your workstation) are actually "requesting" that the server perform some work for you. You are not doing the work.

This is an important thing to remember as it comes into play with may types of SQL Server functions; sending mail, retrieving files, performing backups and restores, and more, all occur ON THE SERVER, not on your machine. I can't say it enough, everything with SQL Server occurs on that machine, not on your machine. So while you may say "I ran this query", actually you requested the server run the query and send the results back.

Hopefully that's made some sense; if not, post a question below and maybe some others can put a better analogy down. Let's move on.

Tables and Queries?

While administering SQL Server isn't the hardest thing in the world and doesn't require an extensive knowledge of SQL, it does require a rudimentary knowledge of how you do this. At the very least, when you call me or someone like me, they'll ask you to run a query and you should understand what this means.

First let's look at how data is stored in SQL Server. Open Excel, start a new worksheet and type the following in the first few cells (A1, A2, and A3): "CustomerID" in A1, "CustomerName" in A2, "City" in A3. Now below CustomerID, type "ALPHA" in B1, "Bob First" in B2 and "LA" in B3. Continue on with the next two rows entering so you have something like this:

Or you can click here to download an Excel 2000 sample.

You've just created a table, or its equivalent in Excel. If you double click the "Sheet1" at the bottom of Excel, you can name your table. I changed mine to "Customers". There is a header, the A row, which has the column names. There are rows of data, all the rows below the A row, that have data. The B row has the data for customer "ADAPT". Now if I asked you for all the customers, you'd send me this spreadsheet. This is equivalent to the following SQL:

select * from customers

The asterisk (*) means give me all the columns. You'll see this often in SQL; not that it's good, but you'll see it often. If I asked you for only the customers who are in Denver, you'd send me the single row in the spreadsheet that has a city of Denver, the "OMEGA" row. This is known as "qualifying" the query (or question), or using a WHERE clause. I want to see all customers WHERE the city is "Denver". In SQL this is:

select * from customers where city = "Denver"

That's most of what a basic query is. I won't bore you with joins, aggregates, etc. At least not right now. For now let's just look at how you execute a query.

There is really only one good way to execute a query. Lots of people will disagree, but let's keep this simple. You run it in Query Analyzer. Which can be accessed in numerous ways. First you can have Enterprise Manager running and select Tools | SQL Query Analyzer. This will connect you to the server which is selected in Enterprise Manager and open a query window (like our chat session above). Or you can go through Start | Programs | Microsoft SQL Server | Query Analyzer. This will prompt you for a login:

You can pick or type a server name, choose the type of connection, and enter your name and password if needed. I'm assuming you already know your credentials to access the server; these are the same ones you use in Enterprise Manager when you register the server. Or in Control Panel | Administrative Tools | Data Sources.

Lastly you can use my preferred method. Start | Run | isqlw.exe. This is the executable for Query Analyzer. Why is it isqlw and not qa? It's a holdever and doesn't matter. In any case, connect to a server, get Query Analyzer open and type the first query above, "select * from customers". To execute it, you can click the green arrow , hit CTRL-E or ALT-X and you will see....

Most likely an error. Never fear, it's not your fault. You see, just like when you run a program you have to be "in" the folder where the program resides (ignore PATH for a moment), you need to be in the correct "database" that has the "Customers" table. On any SQL Server with the sample databases, this is the Northwind database. Click the drop down at the top of Query Analyzer (noted by the arrow)

and select the "Northwind" database and run the query again. You'll get a bunch of data in the bottom of the Query Analyzer window. If "Northwind" isn't an option, and it isn't on many production servers, type this instead:

select * from sysobjects

and you'll get something. I guarantee that one.

There, that's it for running a query. Next time someone asks you to run a query, you'll be set. Just be sure they send you the query they want you to run. 🙂

Conclusions

A basic introduction to SQL Server and one that gets you doing things right away, I'll tackle a few more subjects next time to help you expand your understanding of the server. If there's something you'd like to see, let me know.

References

Here are a few for learning about SQL Server:

• SQL Server Product Overview from Microsoft
• Overview of Installing SQL Server 2000
• SQL Server 2000 Administration Fundamentals - More Server fundamentals than Admin ones, but not bad.
• Getting the Most out of SQL Server 2000's Query Analyzer, Part I - A series of things to help you learn about Query Analyzer.
• Query Analyzer Tricks - A few things I've written about.

Steve Jones

©dkRanch.net April 2004

Return to Steve Jones Home