Continuing our ‘in a nutshell’ series on SQL, we look at the key ingredient of SQL Server, the database. SQL Server ofcourse hosts databases which it serves up to users and interacting applications. Databases are a collection of ‘database objects’, specifically Tables, Columns, Stored Procedures, Triggers, Indexes (used for more efficient retrieval of data), Views and Functions, all of which are known as ‘securables’ because they are secured objects that can have permissions applied. The other database objects are the User (sql user or windows user) and database Role (what can be performed) which are both known as a ‘principles’. Essentially Principles are given Permissions to Securables.
Security aside, we are looking the database as a whole. SQL Server ships with several native databases and you can install some standard example databases from the Microsoft site (Adventureworks being one of them but is not installed by default). The main native SQL Server databases are as follows:
Master – The most important of the native databases. Directly altering this database is dangerous. It holds metadata about all of the user databases on the server. Should the master database become corrupt, it is likely SQL Server will no longer work as expected. The master database should be part of any server backup. The Master database contains :
– All roles and linked logins (database level) that the logged in (server level) user is associated with
– System configuration settings (data sorting information, security implementation, default language)
– Names and meta data about each database on the server
– Phyical Location of databases (mdf/ldf files on the disk/share)
– Assembly information (for external dll’s)
– System Error and Warning Messages
TempDB – As the name suggests, this is a temporary database that is erased and created every time the SQL Server session (service) is restarted. Think of this database as transitional storage space. Data being processed in a stored procedure (or being passed between procedures) is stored here. You as a user can use Tempdb to store data temporarily whilst you process it, but SQL server also uses TempDB all of the time. TempDB has a limited size so don’t fill it up with too much temporary information.
Model – The model database is the template for the creation of all new user databases. When you use the CREATE DATABASE command, SQL Server creates the user database using the pre-defined criteria stored in the Model database. You can specify settings such as initial database size and creation of a same table in all databases. You can edit this table for your own database creation needs.
MSDB – This database is responsible for storing all job information which is necessary for the SQL Server Agent service to run the jobs. In unix terms, tt is the crontab file for use by cron.
These native databases are critical to the operation of your SQL Server and assist you in the creation and maintenance of your databases. What are the types of user database you can create?… Simply there are 2 types, OLTP and OLAP and which one you choose depends on how the database will operate.
OLTP – Online Transaction Processing : This type of database is used for INSTANT updates of data and generally supports a front end interface (Web or Application). The front end makes calls to the database and can select, insert or update the data. OLTP databases deal with transactions ALL of the time and therefore data must be updated instantly. Since speed is essential to OLTP databases, heavy (level 3 +) normalization and the use of Indexes to speed up retrieval are critical factors in the design of an OLTP database. Finally, since OLTP has frequent instant changes, regular BACKUPS are essential.
OLAP – Online Analytical Processing : OLAP databases are less regularly updated by front end application interaction and the data generally remains fairly static. OLAP is used for reporting and analytical review. OLAP databases are usually non normalized databases and can be known as data warehouses.
Planning is a key aspect of database design and there are several considerations that must be visited when starting out:
– Who owns the data to be stored? (Talk to them as they will be the most knowledgable regarding the data to be stored).
– Understand how the data is relational. Creating relationships between tables will make for a more efficient database system.
– Gather delivery requirements. What should the storage of this data provide?, more efficient access, better reporting, levels of views for different privelidged users etc.
– Attempt to group the data into different but related areas so to map to different but related tables. e.g.Products, Customers, Customer Addresses, Shares and Transactions.
Creating a new Database
First off, you cannot name your database anything you want. Certain characters are restricted, including ” ‘ */?:\<> and – . You must also take into account some other important considerations which creating a new database such as the location of MDF (Master Data File wich contains the database data, but also contains links to all of the files used by the database) and LDF (trans log) files, what the initial size of the database is to be and how to set ‘Autogrowth’ specifying the percentage the database should auto expand by (this lets SQL server manage how much disk space is used – turned off, you have to manually increase the database size which maybe the case in low disk space environments). You can also setup more than one database file. You can setup a secondary file which will be written to when the first file is full (if autogrowth is switched off). This way you have more database files and if these files on are seperate volumes, data access is improved. You setup file groups and for each database file you create, you assign a file group.
It is best to be a computer administrator when creating a database. Being part of the group BUILTIN\Administrators (i.e local admin) is a member of the sql server role SYSADMIN, which gives you full database creation rights.
The T-SQL Bit :
CREATE DATABASE [FinancialAppress] ON PRIMARY
( NAME = N’FinancialApress’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
( NAME = N’MyDatabase’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase.ndf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
( NAME = N’MyDatabase_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
EXEC dbo.sp_dbcmptlevel @dbname=N’MyDatabase‘, @new_cmptlevel=70
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
EXEC [MyDatabase].[dbo].[sp_fulltext_database] @action = ‘disable’
ALTER DATABASE [MyDatabase] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [MyDatabase] SET ANSI_NULLS OFF
ALTER DATABASE [MyDatabase] SET ARITHABORT OFF
ALTER DATABASE [MyDatabase] SET AUTO_CLOSE OFF
ALTER DATABASE [MyDatabase] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [MyDatabase] SET AUTO_SHRINK OFF
Looks scary right? – Creating a database however, with all default values and a single file group setup is very east. Just type the following into a new query window (if using SQL Server 2000, select Query Analayzer first) :
CREATE DATABASE [MyDatabase]
Dropping (deleting) a database?, even simpler (and note, we USE the master database to drop the user database ‘MyDatabase’) :
DROP DATABASE [MyDatabase]
After reading the above, you should understand what native databases are shipped with SQL Server. How they support the operation of the server and the user databases, what types of databases you can create and finally what considerations to take into account when planning your database design. Finally we looked at the syntax of creating a new database.