Data Management

Build Your Skills: SQL Server survival skills for non-DBAs

Learn to do some SQL Server duties even if youre not a database administrator.

It's not uncommon, especially in smaller organizations, to find network administrators who are responsible for administering Microsoft SQL Server and its databases. The problem is that database administration has very little to do with the role of network administration. Database administration is a highly specialized IT skill that requires training and experience. Because of this, smaller organizations often cannot afford to hire a dedicated database administrator (DBA), and instead ask the network administrator to assume those responsibilities.

The network administrator who is put in this situation often faces a considerable challenge. Without specific knowledge of database administration, there are limits to what the network administrator can do. To help you if you're faced with such a situation, I'm going to present a crash course on the basic aspects of SQL Server. I'll discuss some of the problems that you may encounter, and help you to understand how to recognize the kinds of problems you can fix—as well as the kinds of problems you can't.

A valuable reference companion
SQL Server comes with some outstanding built-in help in the form of Books Online. You should get in the habit of referring to it often. You can use the help in Books Online by going to Start | Program Files | Microsoft SQL Server | Books Online. The SQL Server Books Online works the same way as the Windows 2000 Resource Kit Books Online, with which you may already be familiar.

Where do the databases come from?
A SQL Server database is normally part of an N-tier application, in which the user accesses the database through an application interface. The application was written to issue SQL (structured query language) commands to Microsoft SQL Server to manipulate the database in some way. In all likelihood, an application developer or development team designed the database. It's important to understand that the design of the database is critical to the performance of the system. Performance is always a major issue with databases, especially as they grow in size. If the database was not well designed in the first place, performance will probably suffer. Although your users may ask you to fix the performance problem, you can't fix it if it involves issues of poor design.

Understanding database files
Every SQL Server database consists of at least two files: a data file and a transaction log file. The data file has the extension .MDF and the transaction log file has the extension .LDF. For instance, if you have a database called Sales, you might have these two files:

It is also possible that there can be more than one data file for a given database, and more than one transaction log file. Data files created after the first one will have the extension .NDF. It's important to understand that if the database needs to be moved or restored, all of the associated files must be moved or restored, not just a single data file.

Your window into SQL Server
Although SQL Server comes with several administrative tools, the one that non-DBAs will find most useful is the Enterprise Manager. In order to use others, such as Query Analyzer or Profiler, you would need to have a thorough understanding of the SQL language. Fortunately, Enterprise Manager does not require such knowledge. The admin tools can be installed on a workstation so that you won't have to access the SQL Server itself in order to administer it.

After installing the admin tools, you may need to register a given server with the admin tools so that you can access it. To do that, right-click on SQL Server Group and select New SQL Server Registration (see Figure A). A wizard will appear that will allow you to register one or more servers found on the network.

Figure A

You should be aware that for SQL Server to be functional, the service MSSQLSERVER must be started. In Figure A above, notice the green arrow in the circle next to the icon of a server in the right pane. This indicates that the SQL Server is functioning (in this case, the MSSQLSERVER service is running). If you see a red square instead of the green arrow, it is probably because the service needs to be started.

With Enterprise Manager, you can see all the SQL Servers in your network, and all databases within each SQL Server. Expand the server by clicking the + next to its name, and then expand the databases by clicking the + next to the word Databases (see Figure B).

Figure B

The databases listed in Figure B are all part of the default installation of SQL Server. The Northwind and Pubs databases are sample databases that you can use to experiment and learn on, or you can delete them if you prefer. The other databases—master, model, msdb, and tempdb—are all integral to the operation of SQL Server and should not be deleted or modified in any way.

Allowing users access to SQL Server
If you're a network administrator responsible for supporting SQL Server, one of the major responsibilities you will incur is allowing users to access the SQL Server databases, and ensuring that they do not have more rights than they need. SQL Server security can be rather complex, so I will cover only the basics here.

Authenticated logon to the network does not automatically grant access to SQL Server. Access to SQL Server must be explicitly granted with SQL Server itself. Just as you manage access to network shares by groups rather than by individuals, it is best to manage access to SQL Server by groups. That way, if a new user requires access to the database, all you have to do is add him or her to the appropriate group.

Before you allow access to SQL Server to a group or individual, you must know what you will allow the user to see and do in SQL Server. Specifically, you must know:
  • What role, if any, the group/user will be assigned
  • What database or databases the group/user will have access to
  • To what extent, if any, that group/user will be allowed to modify the database

To a large extent, permissions within SQL Server and within a given database are managed by roles. There are eight Server Roles, as shown in Figure C, and 10 Database Roles, as shown in Figure D. An explanation of each of these roles can be found by doing a search in the SQL Server Books Online.

Figure C

Figure D

As you can probably guess by the names in Figure C, several of the server roles carry with them quite a bit of power, and they should be used sparingly—especially the System Administrators role, which has absolute control over everything in SQL Server. (You should be a member of this role if you're responsible for supporting this server.)

Generally speaking, no average user of a database should be a member of any of these roles. On the other hand, if there are developers within your organization who must work with SQL Server, they may need to be members of one or more of these roles, especially Database Creators, Bulk Insert Administrators, and Setup Administrators.

Within a given database, the average user will be a member of the Public role, shown in Figure D. A developer responsible for supporting that database will probably need to be a member of the db_owner role.

You can create a new SQL Server user by expanding the Security folder in Enterprise Manager, right-clicking Logins, and then selecting New Login, as shown in Figure E.

Figure E

This will bring up a dialog box with three tabs. In the General Tab, at the Name text box, you can click on the ellipsis to browse the domain to add a group or user, and then specify whether the user or group may access the SQL server by Windows Authentication or by a separate SQL server authentication, requiring a separate password. This option will appear only if both options were allowed at the time of SQL Server installation. Notice also in Figure F that you may deny access simply by clicking a radio button. In this screen, you can also specify the user's default database, which should be something other than one of the built-in databases.

Figure F

In the Server Roles tab, you can specify which, if any, Server Roles the new login will have. In the Database Access tab, you can specify exactly which databases the new login may have access to, and which database roles the new login will have for each database, as shown in Figure G.

Figure G

Moving a database
Another responsibility you may be asked to carry out in supporting a SQL server is moving the database to another location—perhaps because the server is being replaced, or the database needs to be moved from the server to a network storage device, or it needs to be moved to another SQL server. This is actually a very easy procedure that involves the following steps:
  1. Detach the database from the server.
  2. Copy or move the database files to their new location.
  3. Attach the database to the new server.

Before you detach a database, however, make sure that you know exactly where it is located. You can determine that by right-clicking on the database and selecting Properties. Click the Data Files tab, as shown in Figure H, and check the location.

Figure H

Incidentally, in Figure H under File Properties, you can see that it allows for the provision of either automatically allowing the file to grow in size over time or restricting file size. Either of these provisions can cause problems that you will need to watch. If the file size is restricted, then the database may need to have records routinely deleted. On the other hand, if it is allowed to grow automatically, you will need to monitor disk space carefully.

To detach the database from the server, right-click the database in Enterprise manager, select All Tasks, then Detach Database, as shown in Figure I below. The name of that database will disappear from the list.

Figure I

When it is time to attach a database, right-click on Databases under the name of the Server where you wish to attach the database, and select All Tasks, then Attach Database, as shown in Figure J.

Figure J

This will bring up a dialog box. At this point, simply click on the ellipsis as shown in Figure K, to browse to the location of the MDF file for the database you wish to attach.

Figure K

Keeping track of updates
Like any other program, problems are occasionally discovered with SQL Server that will require software updates and patches. One of the main reasons that the SQL Slammer virus was so successful was that many SQL Servers did not have the latest patches. You can keep track of available SQL Server updates at Microsoft's SQL Server Web site. At this time, the current Service Pack for SQL Server 2000 is SP3a, with a separate Service Pack 3 for the Books Online.

Basic survival
You need to be aware that there are issues within the databases themselves that can cause performance problems that may appear to be network problems. If your users report performance problems with a database and you're unable to find any problems with the network, consider talking to your manager about bringing in a DBA consultant. It could be something as simple as tuning indexes, but you won't know until you bring in an expert.

Microsoft SQL Server is a very complex system that requires expert knowledge, as well as experience. If you're a network administrator and find yourself in the position of having to support a SQL Server, what I have covered here will provide you with some basic survival skills.

Editor's Picks