After SQL Server has been successfully installed on a server, the system administrator undertakes various responsibilities to manage it. Some of these responsibilities include creating database devices, managing space on existing devices, creating
databases and monitoring space usage, creating segments, creating backup devices, backing up and restoring databases, creating SQL Server logins, managing security, configuring SQL Server for optimal performance, designing databases, performance, and
tuning, and diagnosing SQL Server problems. Depending on the size of the applications and the number of SQL Servers in an organization, one or more system administrators might be assigned to perform these functions. SQL Server provides a special login
called sa (System Administrator). This login is responsible for managing SQL Server. Anyone who can connect to SQL Server using the sa login and password can perform system administrative tasks.
SQL Server provides a graphical tool, called SQL Enterprise Manager, that enables system administrators to manage multiple SQL Servers in an organization. Although graphical tools are convenient for performing day-to-day operations, they do not provide any details on how a particular task is executed at SQL Server. For this reason, this chapter focuses on the commands that are used to accomplish such tasks (such as creating devices, databases, segments, and so on). You can send all these commands to SQL Server by using a query tool such as ISQL/W.
SQL Server supports multiple databases, both system and user. Before a user database can be created on a SQL Server, it is necessary to initialize database devices on which the various user databases can be created. A database device can hold one or
more databases, depending on the space available on the device.
A database device is a file on an operating system, and it must be initialized before it can be used for creating databases on it. A database device on a SQL Server is created (or initialized) by use of the DISK INIT T-SQL. command. After a device has
been successfully initialized, multiple databases can be created on that particular device. This is the syntax of the DISK INIT command:
DISK INIT NAME = "logical name of the device", PHYSNAME = "physical name ", VDEVNO = device number, SIZE = size of device on 2K blocks [, VSTART = virtual address]
All the parameters of the DISK INIT command are described here:
Only an sa can issue a DISK INIT command, and permissions cannot be granted on this command to other users. The following example creates a logical device called test_dev that is 64M in size:
DISK INIT NAME = "test_dev", PHYSNAME = "c:\dbdata\test_dev.dat ", VDEVNO = 2 SIZE = 32768
As a result of the DISK INIT command, a row is inserted into the sysdevices system table in the master database. Information such as logical name, physical name, type of device (database or backup), status (default, mirrored), and size is stored in this table.
SQL Server enables you to designate database devices as default devices. By designating default devices, you do not have to provide the names of database devices at the time of database creation. In such cases, space for a database is allocated from the
default devices. The devices are used in the order of their logical names; that is, when space on the first device is consumed, the next device is used for further allocation. A device can be designated as a default device by use of the sp_diskdefault
system stored procedure. The syntax for sp_diskdefault is as follows:
sp_diskdefault "logical name" , <defaulton | defaultoff>
When a device is created using the DISK INIT command, the device is created with the defaultoff option. The following example sets device test_dev as a default device:
Exec sp_diskdefault "test_dev", defaulton
The status column of the sysdevices table is a bit-mapped field that is used by SQL Server to indicate various attributes of a device. These attributes include default disk, mirror enabled, serial writes, and so on. Therefore, when you execute sp_diskdefault to turn default on for a device, SQL Server updates the bitmap of the status column in sysdevices and sets it to 1.
After a new installation of SQL Server, MASTER is the only device that's available on the server. The SETUP program designates MASTER as the default device. You should remove MASTER from the list of default devices by executing this command:
Exec sp_diskdefault "master", defaultoff
Disk mirroring provides fault tolerance in the event of a disk failure. This enables the secondary (mirrored) device to become active, in case of a primary device failure, until the faulty device is fixed and reinstalled. Fault tolerance to a system can
be provided at various levels by using
Hardware-based RAID provides the most reliable and configurable fault tolerance. In the event of a disk crash, the system continues to run because the mirror device takes over. RAID-based disk controllers recognize the new drive after the faulty drive
is replaced, bringing the system back to the level of tolerance where it was before the disk failure. Hardware-based fault tolerance does not cause any performance degradation to the system because it is handled at the controller level and is completely
transparent to the operating system and the applications. Hardware-based fault tolerance, however, suffers from a cost disadvantage. Check your hardware installation guide to see whether your hardware supports fault tolerance.
Windows NT–based fault tolerance can be done only on Windows NT Server. Windows NT–based fault tolerance allows logical drives to be mirrored. In the event of media failure, the system continues to run, but unlike with hardware-based fault
tolerance, you must shut down the system to repair the drive that failed. Operating system–based fault tolerance is less expensive than hardware-based tolerance, but it adds more overhead to system processors and can contribute to performance
degradation, especially for applications that are CPU intensive.
Fault tolerance is supported by SQL Server also for database devices. SQL Server T-SQL commands enable you to mirror, unmirror, and remirror a database device. In case of a device failure, the mirror device takes over and the system continues to run.
The syntax for mirroring a database device is as shown here:
DISK MIRROR NAME = "logical device name", MIRROR = "physical name of the mirror file", [, WRITES SERIAL | NOSERIAL ]
Only an sa can run this command, and permissions cannot be granted to other users to run this command. Mirroring is enabled after successful execution of the DISK MIRROR command. All the parameters of the DISK MIRROR command are described here:
The following example creates a mirror device called test_dev.mir for a database device called test_dev:
DISK MIRROR NAME = "test_dev", MIRROR = "e:\dbmirror\test_dev.mir" Creating the physical file for the mirror... Starting Dynamic Mirroring of 32768 pages for logical device 'test_dev'. 512 pages mirrored... 1024 pages mirrored... 1536 pages mirrored... ..... ..... ..... 31744 pages mirrored... 32256 pages mirrored... 32768 pages mirrored...
When you issue the DISK MIRROR command, SQL Server updates the mirrorname column of sysdevices for the logical device specified in the command. It also updates the status column to indicate that mirroring is enabled. For the example given previously, this is the entry in the sysdevices table:
SELECT * FROM master..sysdevices
WHERE name = "test_dev"
low high status cntrltype name phyname mirrorname stripeset
----------- ----------- ------ --------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------- ------------------------------
33554432 33587199 738 0 test_dev c:\dbdata\test_dev.dat e:\dbmirror\test_dev.mir (null)
(1 row(s) affected)
After a device is mirrored, it is possible to unmirror it without shutting down SQL Server. SQL Server provides disk unmirroring options such as unmirror the primary device (permanently or temporarily) and unmirror the mirror device (permanently or
temporarily). The T-SQL command DISK UNMIRROR causes mirroring to be disabled for a database device. This is the complete syntax for this command:
DISK UNMIRROR NAME = "logical device name" [, SIDE = PRIMARY | SECONDARY ] [, MODE = RETAIN | REMOVE ]
Only an sa can run this command, and permissions cannot granted to other users to run this command. Mirroring is disabled after successful execution of the DISK UNMIRROR command. All the parameters of this command are described here:
The following example temporarily disables the secondary side of the test_dev device:
DISK UNMIRROR NAME = "test_dev", SIDE = "SECONDARY", MODE = "RETAIN"
If a device is unmirrored permanently, you must reissue the DISK MIRROR command to enable fault tolerance. If, however, the mirroring is disabled temporarily, you can remirror the device by using the DISK REMIRROR command. The complete syntax of this
command is shown here:
DISK REMIRROR NAME = "logcal device name"
The NAME parameter is the logical device name for which mirroring was previously turned off using the DISK UNMIRROR command. The following example shows you how to reactivate fault tolerance for the test_dev device:
DISK REMIRROR NAME = "test_dev" Starting Dynamic Mirroring of 32768 pages for logical device 'test_dev'. 512 pages mirrored... 1024 pages mirrored... 1536 pages mirrored... ..... ..... ...... 31232 pages mirrored... 31744 pages mirrored... 32256 pages mirrored... 32768 pages mirrored...
You can mirror the MASTER device by using the DISK MIRROR command. It is also possible to specify a mirror device for the MASTER device at the time of SQL Server startup by supplying the -r mirror filename option. In the event of loss of the MASTER device, SQL Server can be started with this filename and the -d option.
From a flexibility, recoverability, and performance point of view, fault tolerance is best provided at the hardware level or by the operating system. Using SQL Server for fault tolerance is not advisable. In fact, future releases of SQL Server might not support disk mirroring.
After a device has been created using the DISK INIT T-SQL command, it is possible to increase the size of a device by using the DISK RESIZE command. After a device has been created, it is not possible to reduce the size of the device. Only an sa can run
this command. The syntax of this command is as shown here:
DISK RESIZE NAME = "logical device name", SIZE = new size in 2KB Pages
The following example increases the size of the device test_dev to 80M:
DISK RESIZE NAME = "test_dev", SIZE = 40960
If you specify a size that is smaller than the existing device size, SQL Server returns an error and the command fails.
Databases are backed up on dump devices. These devices are used to restore databases in the event of database corruption or device failure. SQL Server database dump devices are different from the database devices. You cannot create a database on a dump
device, and it is not possible to dump a database on a database device. But as with database devices, you create dump devices by providing a logical name and a physical location of the backup file. SQL Server dump devices are created using the
sp_addumpdevice system stored procedure. A dump device is not tied to a database. SQL Server enables you to perform backups on disk files, tapes, and diskettes. Unlike database devices, a dump device file can be a file on a network. This is the syntax of
sp_addumpdevice:
sp_addumpdevice {"disk" | "tape" | "diskette" }, "logical_name", "physical_name",
[, {{cntrltype [, noskip | skip [, media_capacity]]} | {@devstatus = {noskip | skip}}}]
All the parameters of this procedure are described here:
The following example creates two disk dump devices, one on a local hard drive (testdb_l_dump) and one on the network (testdb_n_dump):
Exec sp_addumpdevice "disk", "testdb_l_dump", "d:\dump00\dbdumps\testdb.dmp" Exec sp_addumpdevice "disk", "testdb_n_dump", "\\backupserver\e$\dump00\dbdumps\testdb.dmp" 'Disk' device added. 'Disk' device added.
When you execute the sp_addumpdevice procedure, SQL Server does not create the physical dump device file and also does not validate the path. Dump file path validity and appropriate permissions are checked at the time of backup.
The system stored procedure sp_helpdevice provides information about a specific device when a device name is passed as a parameter. If no parameters are passed to sp_helpdevice, it displays information about all the devices on SQL Server. The following
example displays information about the device test_dev:
Exec sp_helpdevice "test_dev:" device_name physical_name description status cntrltype device_number low high ------------------------------ ---------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ --------- ------------- ----------- ----------- test_dev c:\dbdata\test_dev.dat special, MIRROR ENABLED, mirrored on 'e:\dbmirror\test_dev.mir', serial writes, reads mirrored, physical disk, 64 MB 738 0 2 33554432 33587199
A database or a dump device can be dropped by using the sp_dropdevice system stored procedure. The two parameters to this procedure are the logical device name (mandatory parameter) and DELFILE (optional parameter). A database device cannot be dropped
if a database is created on it. Without the optional DELFILE, the sp_dropdevice stored procedure simply removes a row from the sysdevices table of the master database and frees up the virtual device number. It does not delete the physical file. As a
result, you cannot create another database device with the same physical filename (however, you can create one with same logical name but with a different physical name). To delete the physical file associated with the logical name, either supply the
DELFILE parameter when executing sp_dropdevice or delete the file from the operating system command line after executing sp_dropdevice. The following example shows how to drop a SQL Server device:
Exec sp_dropdevice "test_dev", DELFILE File: 'c:\dbdata\test_dev.dat' closed. Device dropped. (0 row(s) affected) Physical file deleted.
SQL Server can hold up to 32,767 databases, including system databases. A database is a collection of objects such as tables, triggers, stored procedures, views, and rules that take up space in a database. Users of a database perform various kinds of
operations to select and modify data using various T-SQL commands such as SELECT, DELETE, UPDATE, and INSERT. SQL Server provides a mechanism by which all data modifications are first written to a part of the database called Transaction Log and then
written to the actual tables. This technique of writing first to the transaction log is also called a write-ahead scheme and is used for recovering transactions in the event of a SQL Server crash or shutdown. When a SQL Server is started after a crash or a
shutdown, the transaction log of the database is used to roll forward or roll back changes that took place before the crash or shutdown. All the committed changes that exist in the transaction log but not in the data tables are written (rolled forward) to
the relevant tables. All uncommitted transactions at the time of the crash/shutdown are undone (rolled back). This method of recovery is performed on all the databases of SQL Server after the restart. Therefore, it is obvious that the transaction log plays
an important role for up-to-the-minute recovery of transactions. At the time of database creation, you can specify the names of devices on which database objects and data will reside and the names of devices on which a transaction log will be created. A
database is created on the database devices by using the CREATE DATABASE T-SQL command. The relationship between a database and a device is many-to-many; that is, a single database can span multiple devices, and a single device can hold multiple databases.
The complete syntax of CREATE DATABASE is shown here:
CREATE DATABASE database_name
[ON {DEFAULT | device_name} [= size]
[, device_name [= size]]....]
[LOG ON device_name [= size]
[, device_name [= size]]....]
[FOR LOAD]
Typically, databases on a SQL Server are created by an sa. It is possible, however, to grant CREATE DATABASE permissions to other users. All the parameters of this command are explained here:
When you create a database, SQL Server copies all the system tables from the model database into the new template. These catalog tables are essential for the internal working of SQL Server. For this reason, a newly created database cannot be smaller
than the model database. One of the configuration parameters of SQL Server is database size, which represents the minimum size of the database. The default value for this parameter is 2M. If you do not specify any size information during CREATE DATABASE,
SQL Server creates the database of the size of the model database or the database size SQL Server configuration parameter—whichever is larger. You can set the database size configuration parameter value by executing the sp_configure system stored
procedure or by using SQL Enterprise Manager. In its simplest form, a database can be created as shown here:
CREATE DATABASE TESTDB
This example creates a database of 2M on the first default database device. If no default devices are available or if the space is less than 2M, SQL returns an error. The following example creates the database TESTDB (500M data, 100M log) on two
devices, DB01 and DB02 (250M each), and places the transaction log on LOG01 (100M):
CREATE DATABASE TESTDB ON DB01=250 , DB02 =250 LOG ON LOG01 = 100
Following are some of the things you should know about the CREATE DATABASE command:
If not enough space is available on a particular device, SQL Server does not give an error, but instead allocates whatever space is available on the device.
If you don't provide the LOG ON option during the CREATE DATABASE command, it is still possible to create a log device for the database later by executing the sp_logdevice stored procedure.
When you create a database, the system tables affected in the master database are sysdatabases and sysusages. The table sysdatabases stores the information about the database name, creation date, owner, database options (readonly, single user, and so on). The table sysusages maps database allocation to various devices.
Each database on a SQL Server is assigned an owner. This login is recognized by a special name within that particular database: DBO (database owner). This login, being the owner of the database, has all the privileges within that database. The login ID
that creates the database (usually the sa) automatically becomes the owner of the database. In a multiserver, multidatabase environment, sometimes it is necessary to assign database ownership to individual logins to distribute database responsibilities to
individuals and reduce workload on the system administrator. This way, the system administrator can focus more on server-related issues (such as device creation and configuration). A database ownership is changed by executing the sp_changedbowner system
stored procedure. This procedure must be executed by the sa from within the context of a database. A database context is similar to a working directory on an operating system. A user on a SQL Server can be a valid user of multiple databases and at any
given time must be working in a particular database. This is accomplished via the USE T-SQL command. This command enables a user to switch from the current working database to another (provided that the user is a valid user in the other database).
Therefore, to change the ownership of a database, the sa must take these steps:
The following example changes the ownership of the database TESTDB to the SQL Server login TESTDB_ADM:
USE TESTDB go Exec sp_changedbowner TESTDB_ADM go
There can be only one owner (DBO) of a database. It is possible, however, to impersonate a database owner's privileges within a database by executing the sp_addalias system stored procedure. This way, individual logins can maintain their identity and
have the DBO privileges at the same time. Aliases are created in a database by which one or more database users can be mapped to a particular user, thereby inheriting all the privileges held by that user. This technique enables a database user to
impersonate another database user. Aliases are generally created to impersonate a DBO so that multiple user IDs can perform the function of a DBO. The syntax of this stored procedure is
sp_addalias login_name, name_in_database
in which login_name is the login ID you want to map, and name_in_database is the user name to which you want to map. It is important to note that the login_name cannot be an existing user of the database. The following example maps
the SQL Server login Johnm to DBO:
USE TESTDB go Exec sp_addalias Johnm, "dbo" Alias user added.
SQL Server enables you to set certain database options for an individual database. These options can be turned on or off by the DBO or the system administrator. All these options are set by execution of the sp_dboption system stored procedure. The
complete syntax of this procedure is as follows:
sp_dboption [ database_name, option_name, { TRUE | FALSE }]
If you execute this stored procedure without providing any parameters, it displays all the options that can be set using this procedure. All the parameters of this command are explained here:
The following example sets the trunc. log on chkpt and select into/bulkcopy options on for database TESTDB:
Exec sp_dboption "tempdb", "trunc. log on chkpt", TRUE Exec sp_dboption "tempdb", "select into", TRUE CHECKPOINTing database that was changed. CHECKPOINTing database that was changed.
The following example displays the options that have been set on the database TESTDB:
Exec sp_dboption "TESTDB" The following options are set: ----------------------------------- select into/bulkcopy trunc. log on chkpt.
If you want to set certain options on all the database, set those options on the model database. When a new database is created, it will inherit all the options from the model database. The following example sets the trunc. log on chkpt option to TRUE for the model database:
Exec sp_dboption "model", "trunc. log on chkpt", TRUE
All the databases created this command has been executed will have trunc. log on chkpt option set to TRUE.
SQL Server provides the sp_renamedb system stored procedure to rename an existing database. A database must be brought into single-user mode before it can be renamed. The following example renames a database called TESTDB to DEVDB:
Exec sp_dboption 'TESTDB', 'single user', true go Exec sp_renamedb 'TESTDB', 'DEVDB go Exec sp_dboption 'DEVDB', 'single user', false go CHECKPOINTing database that was changed. DBCC execution completed. If DBCC printed error messages, see your System Administrator. Database is renamed and in single-user mode. System Administrator (sa) must reset it to multi-user mode with sp_dboption CHECKPOINTing database that was changed. DBCC execution completed. If DBCC printed error messages, see your System Administrator.
Each database on SQL Server is created with a fixed amount of space. As objects are created in a database and data is added, the amount of free space decreases. For a database to stay active, it is important that there always be enough room to grow. A
database size can be increased with the ALTER DATABASE command. This command is similar to the CREATE DATABASE command. This command can be run by the sa or by the database owner (DBO), provided that the DBO has CREATE DATABASE permissions. Following is
the complete syntax of this command:
ALTER DATABASE database_name
[ON} {DEFAULT | database_device } [=size],
[, database_device } [=size...]
FOR LOAD
The minimum size by which a database can be expanded is 1M. The following example increases the size of the database TESTDB by 100M on device DB03.
ALTER DATABASE TESTDB ON DB03 =100
SQL Server enables you to reclaim database space if it has been over-allocated. The SQL Server T-SQL command DBCC SHRINKDB enables you to reclaim only unused database space. The syntax of this command is as follows:
DBCC SHRINKDB (database_name [, new_size [, 'MASTEROVERRIDE']])
Before this command can be executed, the database must be brought into single-user mode with sp_dboption. The new_size parameter is specified in terms of 2K pages. If you are not sure how much to specify as the new size, run this command without
specifying the new_size value, and SQL Server returns the minimum size the database can be reduced to. The following example reduces the size of TESTDB to 160M.
exec sp_dboption 'TESTDB', 'single user', TRUE
go
USE TESTDB
go
DBCC SHRINKDB("TESTDB,81920)
go
CHECKPOINTing database that was changed.
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
The system stored procedure sp_helpdb provides information about a specific database when a database name is passed as a parameter. If no parameters were passed to sp_helpdb, it displays information about all the databases on SQL Server. The following
example displays information about the TESTDB database:
Exec sp_helpdb "TESTDB" name db_size owner dbid created ------------------------ ------------- ------------------------ ------ ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- TESTDB 400.00 MB TESTDB_ADM 8 Mar 8 1996 select into/bulkcopy, trunc. log on chkpt., ANSI null default device_fragments size usage ------------------------------ ------------- -------------------- DB01 250.00 MB data only DB01 250.00 MB data only DB03 100.00 MB data only LOG01 100.00 MB log only
To find out the space used within a database, execute the sp_spaceused stored procedure. This procedure reports space usage at the table level or for the entire database. This procedure reports space usage for the current database. The following example
shows the space usage of the database TESTDB:
USE TESTDB go Exec sp_spaceused go database_name database_size unallocated space ------------------------------ ------------------ ------------------ TESTDB 700.00 MB 567.63 MB reserved data index_size unused ------------------ ------------------ ------------------ ------------------ 135552 KB 56810 KB 63670 KB 15072 KB
If a database is not required any longer, it can be dropped with the DROP DATABASE T-SQL command. When a database is dropped, all the objects and data contained in it are removed, and allocation on various devices is released. The syntax for this
command is as follows:
DROP DATABASE database_name [, database name ]
The following example drops the database TESTDB:
DROP DATABASE TESTDB
This command can be run only by the sa or by the database owner. It is not possible to drop a database if it is in use.
When a database is initially created on SQL Server with the CREATE DATABASE command, all the space allocated to the database is shared by all the objects of the database, and there is no way to allocate space to specific database objects. A segment is a
mechanism by which one can manage the growth of database objects on particular device segments. This helps in better placement of data and improved performance. When you first create a database, SQL Server creates three segments in the database:
SQL Server enables you to create user-defined segments in a database. These segments are a subset of the space allocated to a database. At the time of table creation and index creation, you can specify the segments on which these objects should be
created. After an object is defined on a particular segment, it cannot grow beyond the segment boundary. If a segment gets full, however, it is possible to extend the size of a segment. You create segments in a database by executing the sp_addsegment
system stored procedure from the database in which you want to define the segment. The syntax of this stored procedure is
sp_addsegment segment_name, logical_device_name
in which segment_name is the name of the segment you want to create, and logical_device_name is the name of the device on which the database is created.
The following example creates the table_seg segment on DB01 and the index_seg segment on DB02 for the database TESTDB:
USE TESTDB go Exec sp_addsegment table_seg, DB01 Exec sp_addsegment index_seg, DB02
Any object that gets created on segment table_seg can use all the DB01 space that is assigned for the database. It is important to note that table_seg cannot use all the space on device DB01, but only the space allocated to the database TESTDB. The
following example creates the table test_table on table_seg and a nonclustered index on index_seg:
create table test_table ( col1 int, col2 int ) on table_seg create unique nonclustered index nc1 on test_table ( col1 ) on index_seg.
By creating test_table on table_seg and a nonclustered index on index_seg, you can control the placement of the data and index segment on different devices, and the growth of one does not interfere with that of the other. Also, the devices DB01 and DB02
are on two different controllers, so there is a big performance improvement because the I/O is now split across controllers. A logical diagram of the use of segments in a database is shown in Figure 30.1.
FIGURE 30.1. Using database segments.
If you create a table on one segment and its clustered index on another segment, the entire data moves to the segment on which the clustered index is defined. The reason is that in case of a clustered index on a table, data is physically sorted on the key, and the bottom of the index page is the table data; therefore, you cannot isolate the data from the index.
As objects are created in database segments and data is added, the amount of free space decreases. For a database to stay active, it is important that there be enough space for objects to grow. It is possible to extend a segment on devices on which the
database is created. A segment can be extended with the sp_extendsegment system stored procedure. The syntax for this stored procedure is
sp_extendsegment segment_name, logical_device_name
in which segment_name is the name of the segment you want to extend, and logical_device_name is the name of the device on which the database has been created.
The following example extends the table_seg segment on DB03 for the database TESTDB:
USE TESTDB go Exec sp_extendsegment table_seg, DB01
If the database size is increased on a particular device, the segment defined on that device automatically gets expanded to make use of the additional space.
The system stored procedure sp_helpsegment provides information about the objects in a segment when a segment name is passed as a parameter. If no parameter is passed to sp_helpsegment, it simply lists all the segments on a database. The following
example displays information about the table_seg segment of the TESTDB database:
USE TESTDB go Exec sp_helpsegment "table_seg" segment name status ------- ------------------------------ ------ 3 table_seg 0 device size ------------------------------ ---------------------- DB01 250MB DB03 100MB table_name index_name indid ------------------------------ ------------------------------ ------ test_table idx1 0
A segment can be dropped if no objects are defined on it. SQL Server returns an error if you try to drop a segment that is being used. If there are objects on a database, you need to reassign them to a different segment before you can drop a segment.
You can drop a segment by executing the sp_dropsegment system stored procedure. A segment can be dropped from a specific device, or it can be dropped completely. The syntax of sp_dropsegment stored procedure is as follows:
sp_dropsegment database_name, [device_name]
The following example drops the table_seg segment from the DB03 database device for the database TESTDB:
USE TESTDB go Exec sp_dropsegment "table_seg", "DB03" go DBCC execution completed. If DBCC printed error messages, see your System Administrator. Segment reference to device dropped.
The system stored procedure sp_helpdb also provides information about the segments on a database.
Segments are particularly useful when you want to split a large table across multiple devices. This technique can help improve performance of the system because I/Os on the large table can now be split across multiple drives. Although the use of segments can provide better overall system performance, segments add more complexity and reduce overall manageability of the database. Hardware-based RAID can also distribute load across various disks and might provide better performance than the use of segments alone.
In a business environment, it is important to protect business-critical information stored in a database. SQL Server can prevent unauthorized access to data at the following levels:
This is the first step of user authentication in which SQL Server determines whether a login is a valid SQL Server login. SQL Server security can be configured to handle user connections in one of the following ways:
In this security mode, SQL Server validates the user logins directly. To access a SQL Server, a user must supply a user ID and a password. This security mode does not require any user account on Windows NT. This is the default security mode for SQL
Server. User logins are created on SQL Server with the sp_addlogin system stored procedure. Following is the syntax for this procedure:
sp_addlogin login_id, [password, [,defdb [ ,deflanguage ]]]
All the parameters of sp_addlogin are explained here:
Creating a login with a default database does not make a login a valid user of the database. After a login has been created on SQL Server, it is necessary to add this login to all the databases this login should be allowed to access. You accomplish this task by executing the sp_adduser system stored procedure from within a database. The stored procedure sp_adduser is discussed later in this chapter.
In this security mode a user should have a Windows NT account, and login authentication is done by the operating system. After a login is verified by Windows NT, SQL Server does not validate the login, and a user can access SQL Server. Integrated
security has advantages over standard security because it provides better security (C2 compliance, password expiration and aging, and so on) and better manageability at the enterprise level (accounts can be created at machine level or at domain level). The
disadvantages are that integrated security works only with the Named Pipes protocol and the Multi-protocol, and each user who uses integrated security to log on to SQL Server is mapped to a default login of SQL Server (or as an sa, if the user has
administrative privileges). Because all users are mapped to one login of SQL Server, all the users of SQL Server have same permissions within a database.
This mode of security combines the standard security and integrated security methods. In this mode, if a login request is first matched against an account name on Windows NT and if a match is found, integrated security rules are applied. Otherwise, SQL
Server validates the login ID using standard security mode by validating the user name and password.
Mixed security is very useful if you forget the sa password. Because Standard security does not let you connect to SQL Server without a valid password, you can still connect to SQL Server by using your network account name, and if the account name is a part of the administrative group, SQL Server lets you connect as an sa. After you are connected, you can change the sa password by using the sp_password system stored procedure.
To access a database, a user needs to be assigned to the database. A user can be a user in multiple databases. Before a user can be added to a database, the user should already have a login created (the sa must have created the login by using the
sp_addlogin procedure) on SQL Server.
SQL Server also enables you to create groups within a database. A group is a logical way of defining a collection of users. Groups in SQL Server provide a convenient method of managing object permissions. After a group is created, it is possible to
grant and revoke object permissions to the group, and all the users who exist in the group automatically inherit the permissions assigned to the group. In SQL Server, a user can belong to only one group. A group in a database can be created with the
sp_addgroup system stored procedure. The syntax is as follows:
sp_addgroup "group_name"
Either the DBO or the sa can run this command. The following example creates three groups called readonly, readwrite, and developer in the database TESTDB:
USE TESTDB go Exec sp_addgroup readonly Exec sp_addgroup readwrite Exec sp_addgroup developer go New group added. New group added. New group added.
It is not necessary for a user to be a part of any user-defined group. Each database of SQL Server has a built-in group called public. All the users of a database by default are a part of this group.
All users in a SQL Server database, by default, belong to the group public. Users can be added to a database with the sp_adduser system stored procedure. The syntax for this command is as follows:
sp_adduser user_name, [name_in_database, [, group_name]
All the parameters of this procedure are described here:
This stored procedure can be executed only by the DBO or the sa. The following example creates a user called Tom who has a SQL Server login name of testuser and assigns it to the readonly group in the database TESTDB:
USE TESTDB go Exec sp_adduser testuser, Tom, readonly go
Information on the users and groups of a database can be obtained via the sp_helpuser and sp_helpgroup system stored procedures. Both of these stored procedures accept user name/group name as input parameters. If no input parameters are provided, they
display information about all the users/groups of the database. The following example provides information about the user Tom in the database TESTDB:
USE TESTDB go Exec sp_helpuser "Tom" go UserName GroupName LoginName DefDBName UserID SUserID -------- --------- --------- --------- ------ ------- Tom readonly testuser TESTDB 3 11
You can drop an alias user from a database by executing the sp_dropalias stored procedure by passing the login_id to the procedure. You cannot drop an alias if objects are owned by the user in the database. Therefore, to drop an alias you must drop the
objects owned by the user.
You can drop a user from a database by executing the sp_dropuser stored procedure by passing the user name to the procedure. You cannot drop a user if objects are owned by the user in the database. Therefore, to drop a user you must drop the objects
owned by the user.
You can drop a group from a database by executing the sp_dropgroup stored procedure by passing the group name to the procedure. You cannot drop a group if users are assigned to it. Therefore, before you can drop a group, you can either drop users from
the database by executing sp_dropuser or assign them to a different group by executing the sp_changegroup system stored procedure.
You can drop a login from a SQL Server by executing the sp_droplogin system stored procedure by passing the login name to the procedure. You cannot drop a login if it is created as a user in a database. Therefore, to drop a login you must drop the user
from the databases where it exists.
The following examples demonstrate how the previously listed actions can be accomplished:
USE TESTDB go Exec sp_dropalias John Exec sp_dropgroup developer Exec sp_dropuser Tom Exec sp_droplogin testuser go Alias user dropped. Group has been dropped. User has been dropped from current database. Login dropped.
SQL Server enables a user on one SQL Server to execute a stored procedure on another SQL Server. To provide this functionality, all the steps similar to the ones mentioned previously (such as create login and add user) are taken at the remote server. The various stored procedures used to accomplish this are sp_addserver, sp_addremote_login, and sp_remoteoption.
A database is a collection of objects (such as tables, views, and procedures). It is important to allow and restrict access on these objects to various users of the database, depending on the privileges of a user, as governed by the business. Users
within a database are assigned permissions. Some logins of SQL Server have more privileges than others. A user connection to a SQL Server can be at one of the following privilege levels:
Unless specific permissions are granted, even the DBO (and the sa) don't have any permissions on an object created by a user. The workaround for this situation is that a DBO impersonates the user by the SETUSER T-SQL command and then grants permissions to himself.
SQL Server also enables system administrators to grant permissions to run certain T-SQL administrative commands, such as CREATE DATABASE and DUMP DATABASE, to some users of SQL Server.
One of ways you can provide security on table data is by using views in a database. Views provide a snapshot of data from one or more underlying database tables. To an end-user, a view looks like a regular table. By using views, you can restrict direct
access to the database tables by providing a horizontal (that is, specific rows) and vertical (that is, specific columns) snapshot of the data from underlying tables. Therefore, views help simplify data representation and provide logical data independence.
Because permissions are required to be granted on the view and not on the underlying tables, the end-users need not know about the underlying table structures and data from which the view was created. Thus, views provide excellent data security.
Enhanced data security can be achieved via SQL Server stored procedures. A user who has permissions to execute a stored procedure does not require any permissions on the underlying tables used within the procedure. Therefore, by granting execute
permissions on stored procedures and revoking all permissions on the underlying tables, you can prevent any direct access to the database tables. This way, all data manipulation on the tables can be done entirely with stored procedures.
Database backups are required to recover from failures such as widespread database corruption or media failure. SQL Server provides various T-SQL commands to back up data in a database. One of the features of SQL Server backups is that users can access
the database while backups are being performed. This method of backing up, called dynamic dumps, is quite useful in a 7x24 environment, in which it is not feasible to secure any downtime. Because backups are very I/O intensive, they do affect system
performance; therefore, it is important to perform database backups during a time of low activity on the server. Backups are generally performed by the sa or DBO, but it is possible to grant access to back up a database to a user of the database. SQL
Server enables you to perform data backups at the following levels:
Table-level backups are useful in situations in which most of the tables in a database are static in nature. In such cases backing up a few active tables of the database can save time compared with backing up the entire database. The T-SQL command to
back up a table is this:
DUMP TABLE [ [database_name].owner.]table_name TO dump_device, [dump_device2 [, dump_device3.....]] WITH options
All the parameters of this command are explained here:
It is also possible to dump a table without specifying a logical dump device name. You can specify the absolute path by specifying disk="path" in the DUMP TABLE command.
The following example dumps the employee table to a disk file:
DUMP TABLE TESTDB..employee TO disk="c:\temp\employee.dmp" with INIT, STATS=10 Msg 3211, Level 10, State 5 10 percent dumped Msg 3211, Level 10, State 5 20 percent dumped Msg 3211, Level 10, State 2 100 percent dumped Msg 4035, Level 10, State 1 Database 'TESTDB' table 'employee' (24 pages) dumped to file <1> on device 'c:\temp\employee.dmp'.
A table can be restored with the LOAD TABLE command. The syntax is similar to that of the DUMP TABLE command. The following example restores new_employee, which has the same structure as the employee table, from a previously taken dump:
LOAD TABLE TESTDB..new_employee FROM disk="c:\temp\employee.dmp" with STATS=10 Msg 4039, Level 10, State 1 Warning, file <1> on device 'c:\temp\employee.dmp' was dumped from database 'TESTDB'. Msg 3211, Level 10, State 6 10 percent loaded Msg 3211, Level 10, State 6 20 percent loaded Msg 3211, Level 10, State 6 30 percent loaded Msg 3211, Level 10, State 6 40 percent loaded Msg 3211, Level 10, State 6 50 percent loaded Msg 3211, Level 10, State 6 60 percent loaded Msg 3211, Level 10, State 6 70 percent loaded Msg 3211, Level 10, State 6 80 percent loaded Msg 3211, Level 10, State 6 90 percent loaded Msg 3211, Level 10, State 6 100 percent loaded
Database dumps are performed to back up the entire contents of a database. Database backups are done with the DUMP DATABASE T-SQL command. This command copies all the object definitions, including SQL Server internal objects, and the data in the dump
device files. Database dump files are used to recover databases in case one of the device (on which the database is created) crashes. A database dump file can also be used to restore a database on another server, provided that the character set and sort
order are compatible. For example, you might set up a database on a test server and want to keep it in sync with a database on a production server. Each night after the database backups are performed on the production server, you can take that dump file
and load it on the test server. The syntax of DUMP DATABASE is similar to that of the DUMP TABLE command. The following example dumps the TESTDB database to a logical device called testdb_dump:
DUMP DATABASE TESTDB TO testdb_dump WITH INIT, STATS=50 Msg 3211, Level 10, State 2 50 percent dumped Msg 3211, Level 10, State 2 100 percent dumped Msg 4035, Level 10, State 1 Database 'TESTDB' (324 pages) dumped to file <1> on device 'testdb_dump'
A database can be restored with the LOAD DATABASE command. To load a database, you need to take into account the following rules:
The following example loads the database TESTDB from the logical dump device testdb_dump:
LOAD DATABASE TESTDB FROM testdb_dump WITH STATS=30 30 percent loaded Msg 3211, Level 10, State 2 60 percent loaded Msg 3211, Level 10, State 2 90 percent loaded Msg 3211, Level 10, State 2 100 percent loaded
SQL Server enables you to back up incremental changes in a database by providing the DUMP TRANSACTION T-SQL command. By doing incremental backups, you can ensure full recoverability of the database. The syntax of DUMP TRANSACTION is similar to that of
DUMP TABLE and DUMP DATABASE. This is the complete syntax of this command:
DUMP TRANSACTION {database_name | local_variable_name}
TO dump_device [, dump_device2 [, dump_device3...]]
[WITH { TRUNCATE_ONLY | NO_LOG | NO_TRUNCATE }
options
When you dump the transaction log, it backs up all the committed changes that took place since the last transaction log dump. After the successful dump, SQL Server clears those transactions from the transaction log. In some cases (such as test or
development environments), if you don't want to make a backup copy of the transaction log of a database, and you only want to clear out the transaction log, you should use the dump option of TRUNCATE_ONLY. When you are using this option, it is not
necessary to specify a dump device. The transaction log of database is created with finite space, and it can fill up completely over a period as a result of data modifications taking place in the database. If the transaction log becomes completely full,
you cannot back up the transaction log on a device or use the TRUNCATE_ONLY option. You must use the NO_LOG option to clear out all the committed transactions from the transaction log. This option removes all committed transactions without creating a
backup copy. Therefore, it is quite important to back up your databases using the DUMP DATABASE command if you have used the TRUNCATE_ONLY or NO_LOG option to create space in the transaction log.
In the event of a database device failure, you can still back up the transaction log to a dump device by using the DUMP TRANSACTION command with the NO_TRUNCATE option. The following example illustrates a typical sequence of database backups. The
database dumps are taken each day at 6:00 a.m., and incremental transaction log backups are taken every hour (that is, 7:00 a.m., 8:00 a.m., and so on).
6:00 AM: DUMP DATABASE TESTDB TO testdb_dump with init. 7:00 AM DUMP TRANSACTION TESTDB TO disk="e:\tranlog\testdb_tran_1" with init 8:00AM DUMP TRANSACTION TESTDB TO disk="e:\tranlog\testdb_tran_2" with init
Assume that the database in the preceding example is created on the logical device DB01 and has a transaction log created on LOG01, and assume that a disk crash occurs at 8:30 a.m. These are the three possible failure scenarios:
In scenario 1, because LOG01 is accessible, you can still back up all the changes that took place between 8:00 a.m. and 8:30 a.m. by dumping the transaction log with the NO_TRUNCATE option. To bring the system back up to the point of failure, you can
now load the database dump (6:00 a.m.) and the transaction log dumps (7:00 a.m., 8:00 a.m., and 8:30 a.m.).
In scenario 2, failure on the transaction log device renders the database useless (even though DB01 is still active). Therefore, in this case you can recover the system only up to 8:00 a.m., and all the changes made after that are lost. For this reason,
it is strongly recommended that you mirror the transaction log device of the database.
In scenario 3, because both of the devices have failed, you can bring the system back up to 8 a.m., and all the changes made after that are lost.
It is quite important to keep an up-to-date backup copy of the master database. Recovery of the master database is different from that of other databases. For the master database, data and transaction logs are on the same device. Therefore, it is not
possible to back up incremental changes. To recover the master database, you must go back to the latest copy of the master database dump. Any changes made to the master database after the dump cannot be recovered. To recover a master database, you must
take the following steps:
c:\mssql\binn\sqlservr /dc:\mssql\data\master.dat /m
SQL Server provides various T-SQL commands and system stored procedures to perform administrative tasks. Certain tasks such as database backups and transaction log dumps are executed more frequently. It is important that database and transaction logs be backed up regularly to provide full recovery in the event of a disk crash. Based on the activity on your system, you can schedule backups for tables, databases, and transaction logs.
