SQL Server 6.5 Administration

Storage Units
  • Devices - Database devices are operating system files. They can store multiple databases and parts of databases.
  • Databases - Databases are created on one or more devices. When more than one device is used, the devices can exist on separate hard drives. Minimum of 1 MB in size.
  • Allocation Unit - When a database is created, space is allocated in 1/2 MB increments (256 pages or 32 extents) known as an allocation unit.

  • Extents - An extent is 16K (8 contiguous 2K pages). When a table or extent is created, SQL Server allocates an extent. Each object is allocated its own extent. Extents are never shared. Because there are 32 extents per allocation unit, there can be up to 32 objects per allocation unit.
  • Page - The basic unit of storage is a 2K (2048 bytes) page.
  • SYSTEM Tables
    When SQL Server is installed, there are four system databases created:

    Master Controls the user databases and the operation of SQL Server as a whole (default size 25 MB). No user is allowed direct access to it.
    Model Provides a template or prototype for new databases. Contains the system tables which belong in every database. Items that are to appear in all new databases should be placed here.
    Tempdb Provides storage for temporary tables and other temporary storage needs such as intermediate results of GROUP BY, ORDER BY, DISTINCT and cursors.
    Msdb Supports the SQL Executive Service. This service is a scheduler that enables such activities as replication, task scheduling, and alert management.

    Master DB:

    sysdatabases Databases on SQL Server
    sysdevices Available database and disk devices
    syslogins User accounts
    sysmessages System error messages
    sysservers Remote servers
    sysusages Disk space allocated to each database

    MSDB:

    sysoperators Administrative personnel information, including email addresses and pager numbers
    sysalerts User-defined alerts
    systasks User-defined tasks
    syshistory Tracks whether or not a task was executed
    sysnotifications All currently active alerts and their notification operator information

    Database Objects
  • Table - Collection of columns and rows
  • View - An alternate way to look at data from one or more tables
  • Default - Value that SQL Server inserts into a column if the user does not enter a value
  • Rule - Specifies and controls what data can be entered into a table
  • Stored Procedure - Precompiled collection of SQL statements
  • Trigger - Special form of a stored procedure that is executed automatically when a user modifies data in a table
  • SYSTEM Stored procedures

    sp_addserver Register remote servers
    sp_adduser Adds a user to a DB. (params: login_id, username, group)
    sp_addlogin Creates a login_id for SQL server
    sp_addextendedproc Add an Extended Procedure to the MASTER database
    sp_changegroup Insert an existing user in a group
    sp_helpuser Verify current users
    sp_configure Can configure TempDB into RAM, worker threads, etc.
    sp_spaceused Show amount of spaced used for a database
    sp_dbremove Remove a damaged database (also DBCC DBREPAIR -- old)
    sp_who Provide information about connected users (including domain info)
    sp_monitor Shows info on number of pages read

    sp_dropuser & sp_changedbowner will change a user to a dbo.

    To send your manager the results of a sql server query as an attached mail message:

    • xp_sendmail @recipients = 'manager', @query = 'select * from Yourname',@attach_results = 'TRUE'

  • xp_logevent - logs user-defined error messages.
  • xp_cmdshell - Can call an MS-DOS batch file or NET commands.
  • You can only use extended procedure when it's typed with small caps --XP_senDMAIL is not valid.

    XP.DLL provides a framework for extended stored procedures.

    SQL Server Commands
    CREATE DATABASE database_name
    [ON {DEFAULT | database_device} [= size]
        [, database_device [= size]] ...]
    [LOG ON database_device [= size]
        [, database_device [= size]] ...]
    [FOR LOAD]     (prevents users from updating DB. Used to restore from backup)

    ALTER DATABASE database_name
        [ON {DEFAULT | database_device} [= size]
        [, database_device [= size]] ...]
    [FOR LOAD]

    DISK INIT
           NAME = ' '
           PHYSNAME = ' '
           VDEVNO = ' '
           SIZE = (size in 2k pages)

    DISK RESIZE
           NAME = logical_device_name
           SIZE = final_size (in 2K pages)

    DISK REINIT
           NAME = dev_name,
           PHYSNAME = 'path',
           VDEVNO = vitual_device_number,
           SIZE = size (in 2K pages)

  • DISK REINIT - used to restore information about the physical and logical device to the sysdevices table.
  • DISK REFIT - reconciles information to rebuild the sysusages and sysdatabases tables.
  • If you expand or shrink a database after the last backup of master, and then lose master, the DB is unrecoverable.

    After using an ALTER DATABASE, you should issue a DUMP DATABASE master.

    Cannot use the DUMP TRANSACTION command when 'trunc log on chkpt' is set to TRUE.

    DUMP DATABASE is normally performed after DUMP TRANSACTION.

    Only DBO or object owner has rights to run UPDATE STATISTICS on a table.

    REFIT might be able to restore schema changes in a DB after media failure even when not in last backup.

    makepipe and readpipe are used to test the inegrity of a pipe connection.

    ALTER DATABASE can be used to change the size of a transaction log.

    You can reduce the size of a database, but not a device.

    A dump device can contain dumps from multiple databases.

    A database can be dumped to multiple devices.

    DUMP TRANSACTION

    • WITH TRUNCATE_ONLY - Clears, but does not back up the log.
    • NO_LOG - Used when transaction log is completely full.
    • NO_TRUNCATE - Used during recovery of a failed device.

    Permissions that only SA can use:

    • Add/Drop extended stored procedures
    • Disk (Init, Mirror, Refit, Reinit, Remirror, Unmirror)
    • Kill
    • Reconfigure
    • Shutdown
    • DBCC commands (although some can be granted to DBO)

    DBCC Commands:
    CHECKALLOC Checks current or specified Database that all PAGES are correctly allocated and used.
    CHECKCATALOG Checks for consistency in and between SYSTEM tables.
    CHECKTABLE Checks table to see that INDEX and DATA pages are correctly linked.
    CHECKDB Runs same checks as CHECKTABLE on every table in the database.
    NEWALLOC Checks DATA and INDEX pages against corresponding extent structures. Does not stop on error.
    SHOWCONTIG Shows how many data pages are used by a table (and contiguous pages).
    MEMUSAGE Provides detailed reports on memory use (i.e. Server's memory at startup, current memory usage of 20 largest objects in the data cache & 12 largest objects in the procedure cache).
    SHRINKDB Used to shrink a DATABASE.
    SQLPERF (LOGSPACE) Reports on the currently used amount of Transaction log space, expressed as a percentage. Log must be separate from data.
    SQLPERF (LRUSTATS) Generates statistics about CACHE used since server was last started.
    SQLPERF (IOSTATS) Read and Write activity.
    SQLPERF (NETSTATS) Current and max number of worker threads.
    SQLPERF (RASTATS) Provides statistics about Read Ahead.

    Locks
    Lock Types
    Item Description
    Page A 2K data page or index page. This is the most common type of lock.
    Extent A contiguous group of eight data pages or index pages. This lock is only used for allocation.
    Table An entire table, including all data and indexes.
    Intent A special type of table lock to indicate the type of page locks currently placed on the table.

    Replication
    sysarticles Publications Server Information for each article created for replication.
    syspublications Publications Server One row for each publication created.
    syssubscriptions Publications Server One row per subscription from a subscribing server.
    MSlast_job_info Subscription Server Tracks the last successful transaction replicated.
    MSjob_commands Distribution Server Maintains a record of commands associated with a job.
    MSjobs Distribution Server Information about jobs, including date.
    MSsubscriber_jobs Distribution Server Information for each subscriber about the commands it needs to receive.

    In central publisher replication, subscription servers will generally treat replicated data as read-only.

    The publication process is not controlled automatically by the SQL Executive Service.

    There should be one cleanup process for each publisher/subscriber relationship.

    The distribution process operates on both transactions and synchronization jobs.

    Transactions are read from the transaction log of the source database and posted to the destination db.

    The log reader process moves transactions to the distribution database.

    On a heavily used server, use Central Publisher with Remote Distributor.

    Replication over an expensive communications link should use manual synchronization.

    The repl_subscriber account is installed into the msdb, master (and distribution) databases.

    Replication datafiles are kept in \MSSQL\REPLDATA or the working directory of the distribution DB.

    A distribution server requires a minimum of 32MB.

    A Scheduled Table Refresh replaces the entire table.

    If the Synchronization process is failing, try to run the SYNC task manually.

    To start replication after completing manual synchronization, choose 'Sync Completed' in Sub Server.

    Application Development
    SQL Enterprise Manager A graphical administration tool that simplifies managing a muliple-server environment. Uses SQL-DMO automation objects.
    SQL Service Manager Used only to start and stop SQL Server.
    SQL Executive Service Uses Transact-SQL to run scheduled tasks through the command prompt.
    SQL DMO Provides 32-bit OLE Automation objects. Exposes interfaces for all SQL Server management functions to OLE-compliant applications.
    SQL Server Distributed Mangement Framework (SQL-DMF) Encompasses all of the functionality provided by SQL-DMO. Includes a set of distributed management tools. Provides services for replication. Has three levels:
    • Lowest - Direct access to SQL Server via Transact SQL.
    • Middle - DMO.
    • Highest - SQL Server Enterprise Manager.
    SQL Distributed Management Objects This includes alerting and notification and all other features accesible through Transact-SQL. Applies the Component Object Model to SQL Server functions. Used for managing collections of properties, methods, objects, and events.
    SQLOLE Allows multiple SQL Servers to be managed from a single user application. OLE Automation is used most often with SQL-OLE.
    Open Data Services (ODS) Can be used to develop applications that will act as a Windows NT service, a remote stored procedure application, or a gateway application to other database management systems. Used most often to create extended stored procedures.
    DB-Library Functions often use the DBPROCESS parameter as their first argument. The dbopen function initializes the DBPROCESS structure. Can act as a connection between a front-end application and SQL Server. Only DB-Library can be used from MS-DOS (ODBC cannot).

    Performance
    Maximum number of user connections for SQL Server is 32,767.

    After SQL memory ALLOC, Procedure and Data Cache take remaining memory.

    A low cache hit ratio is one indication that you might benefit from putting tempdb in RAM.

    Make sure the number of user connections is less then maximum number of worker threads to avoid thread pooling.

    UPDATE STATISTICs when you add memory or key values change in an index, or after truncating table.

    Setting a database to Read-Only disables all locking.

    The setting for SQL Server memory is always allocated in 2K units.

    By default SQL Server uses 8MB if there is less than 32MB in server, and 16MB if there is greater than 32MB in server.

    The Current Activity tool of SQL Enterprise manager can graphically show locking information.

    Only users connection threads can be limited by 'max worker threads'.

    Default value for the locks configuration option is 5000 locks.

    SET SHOWPLAN ON reports join order and indexes used.

    SET STATISTICS IO ON shows actual number of table scans and pages read from cache and disk.

    Alerts
    Performance Monitor can kick off a backup of the transaction log at 75% full and send an alert to the operator.

    For alerts, Performance Monitor & SQLALRTR.EXE go hand in hand.

    An alert cannot be triggered for a SQL Server error which is not written to the NT event log.

    You can forward events and alerts to other servers (alert severity to be sent is configurable).

    Alerts can be configured to execute for a SQL Server error number or a string contained in a SQL Server error.

    SQL Server will examine sysalerts every time a SQL Server event appear in the NT event log.

    Clients
    Windows workstation on Novell network with Integrated security - use MULTIPROTOCOL.

    When configuring a windows client with TCP/IP to talk to SQLserver, entering the Server address of the Primary Domain Controller will not work.

    You can schedule .BAT, .CMD, TSQL, and replication commands using Tasks.

    To configure 16-bit clients to use standard security, modify the WIN.INI file on the client.

    You will be prompted for the port number for TCP/IP sockets clients during setup.

    The default service name for the Novell bindery is the name of the SQL Server computer.

    Use ADSP for Appletalk networks that aren't running TCPIP (otherwise use TCP/IP sockets)

    The SQL Client Configuration utility allows viewing and changing of DB-Library and Net-Library.

    It is recommended that Named Pipes not be removed even if other libraries are installed.

    Both Named Pipes and Multiprotocol allow integrated, standard, or mixed security.

    Named pipes will always work regardless of which other protocols are installed.

    Integrated security and encryption require Multiprotocol on the client and server side.

    When connecting to SQL Server from local client apps, make foreground and background equal.

    Logs
    Transaction log should be 10-25% of the size of the database.

    Explicit allocation of log space is performed either by the LOG ON clause of the CREATE DATABASE command or by using sp_logdevice with the ALTER DATABASE command.

    DISK INIT allows you to create a new device to be used exclusively for a transaction log.

    CREATE DATABASE cannot create a transaction log without a database.

    You can only disable Windows NT event logging by SQL Server from SQL Server Setup.

    Backups
    Use 'skip' to ignore the ANSI headers on backup tapes.

    Only two backup tasks are needed for backing-up your database once on Monday, Wednsday, and Friday AND backing-up your transaction log four times each day starting at 7:00am.

    Backup Management cannot be performed using stored procedures.

    Dumps to a tape device include a header, expiration date, and stripe set information.

    Accounts
    There is always a one-to-one relationship between sysusers and syslogins.

    Each login has only one user per database, but a login can use different names in each databases.

    A single username in a database can map to multiple logins using sysalternates.

    In order to use SQLMain under NT 3.5.1, The SQL Server service should use the LocalSystem account.

    When adding a user to the database, you must provide a valid SQL Login ID.

    Create a Windows NT account for SQL Executive service before running SQL Server Setup.

    Users can read & write to the database while you are backing-up using dynamic backups.

    The Local System account is always available for use by SQL Executive if its primary login is not available.

    When using integrated security, SQL Server login ids can only be assigned to users in trusted domains.

    You can change the SA password from SQL Enterprise Manager or with sp_password.

    You can create a new password for a user with SQL Enterprise Manager or sp_addlogin.

    Users who do not have accounts in the master database are given rights as Guest.

    You cannot drop a login that is designated as an object or database owner.

    A DBO or SA can access tables for a particular user without being GRANTED access by using SETUSER.

    Only the owner and sa can execute an existing task.

    With mixed, SQL Server uses integrated securiry if login name is blank or matches NT username.

    Miscellaneous
    Character Mappings
      _     '\'  
      #     '-'  
      $   (space)

    Default master Device is \MSSQL\DATA\MASTER.DAT.

    SQL Server registry info is in: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.

    A minimum of 50meg of disk space is required to upgrade from version 4.2 to 6.5.

    The default value for the recovery interval is five minutes.

    SQL Server can send, receive, and respond to received messages via email.

    A checkpoint always occurs after a database option is changed.

    Server Group Management can be performed using drag and drop.

    The only database option which can be set for master is 'trunc log on chkpt'.

    You must have a data source name in order to establish an ODBC connection to SQL Server.

    A broken chain is when there is a view based on a view based on a view, etc. and owner of a 'child' view loses permissions to one of the 'parent' objects.

    Forwarding events to a central server can reduce configuration time.

    The 'status' column in sysdatabase will tell you if a particular database is suspect.

    SQL Transfer Manager is used to copy data between SQL Servers.

    Transfer Manager cannot be driven using the ISQL command line utility.

    When using the SQL Transfer Manager, the source server can be a Windows NT, OS/2, or non-Microsoft SQL Server, but the destination server can only be Microsoft SQL Server.

    It is not possible to dump a database from one processor achitecture and load the dump into another architecture.

    When a server is PAUSEd, no new conection to any database is allowed.

    SQL Enterprise Manager 'Edit Database Devices' dialog only allows you to increase device sizes.

    A database can be dumped while in use, but not loaded while in use.

    You would use 'On-Demand Schedule' with an alert or when running a task manually.

    ODBC calls cannot be included in an outgoing SQLMail message.

    You can register a server group by choosing the Server Groups item from the Server menu in Enterprise Manager.

    You must have SELECT on table and some SYSTEM objects for BCP to copy from a table to a text file.

    You must have the INSERT privilege to use BCP to add ASCII data to a table.

    Use Control Panel-Services-Setup to make the MSSQLServer service start automatically.

    Special Thanks to Matthew Morris, MCSE+Internet, CNA, for writing the original Cramsession for this exam!