Creating the Database

Database Permissions

To create a database, you must have CREATE DATABASE permissions. To grant CREATE DATABASE permissions to a user, the user’s login must first be mapped to a database user in the master database.

To check whether a user has CREATE DATABASE permissions, log in to Management Studio with the login account of the user whose permissions you want to check, and run the following:

USE master
IF PERMISSIONS() & 1 = 1
PRINT ‘User has CREATE DATABASE permissions.’

To check whether a user has enough permissions in the database, log in to Management Studio with the login account of the user whose mapping you want to check. Change the database context to the required database, and run the following command:

select case when IS_MEMBER ('db_owner')=1 
or IS_SRVROLEMEMBER ('sysadmin')=1 
or ( 
    IS_MEMBER ('db_ddladmin') = 1 and 
    IS_MEMBER ('db_datareader')=1 and 
    IS_MEMBER ('db_datawriter')=1 and 
    IS_MEMBER ('db_denydatareader')=0 and 
    IS_MEMBER ('db_denydatawriter')=0 and 
    EXISTS ( 
            select 1 
            from fn_my_permissions(NULL, 'DATABASE') 
            where permission_name = 'ALTER' 
            ) 
     ) 
     then 'User has enough permissions' 
     else 'User does not have enough permissions' 
end

Database File Layout

When you create a database, it must consist of at least one data file (with an .mdf extension) and one transaction log file (with an .ldf extension). You can optionally create additional data files (.ndf), as well as additional log files (.ldf).

To enhance performance, you may want to create several data files. Microsoft SQL Server stripes the data among the data files, so that if you do not have RAID controllers that stripe your data, you can spread the data files over several regular physical disks and, in this way, have the data striped. The log, however, is read sequentially, so that there is no performance gain in adding more log files. An additional log file should be created on a different disk when your existing log is out of disk space.

Data and Log Placement

• Log files. Changes are not flushed to the database until they are written to the log, and the log architecture dictates serial writes, so it is advisable that there be as little interference as possible with the log activity. It is usually sufficient to place the log on a RAID 1 system because of the serial writes to the log. If you have processes reading from the log (for example, triggers accessing the inserted and deleted views which are formed from the log records or transactional replication), or several log files for different databases, consider placing the log file(s) on a RAID 0+1 (striped mirror) system.
• Data files. Data files should be placed on a RAID 0+1 system for optimal performance.

File and Database Properties

When you create a database you can specify the following properties for each file (.mdf, .ndf, .ldf):

• NAME. The logical file name which you can use later when you want to alter one of the properties.

• FILENAME. The physical file path and name. Make sure the destination directory is not compressed (right-click the directory in Windows Explorer, select Advanced, and verify that the Compression check box is not selected).

• SIZE. The initial file size.

• MAXSIZE. Determines the maximum size to which the file can grow. If this argument is omitted, or if you specify Unlimited, the file can grow until the disk is full.

• FILEGROWTH. The automatic growth increment of the file. This argument can be specified as either a percentage of the existing file size, or as a fixed size.

  An autogrowth operation invoked by a modification sent by a client that timed out cannot be completed successfully. This means that the next time a client sends a modification, the autogrowth process starts at the beginning and may also time out.

  To avoid this problem, it is recommended that you either expand the files manually every time the database nearly reaches full capacity (for example, 20 percent free), or set the growth increment to a fixed size that takes less time to be allocated than the client’s timeout setting. Using a small growth increment is not recommended because it increases file system fragmentation. On the other hand, if you use a very large increment, modifications sent by clients might incur connection timeouts while waiting for the automatic expansion to finish. For large databases, a percentage growth increment can lead to exponential growth of the database and should be avoided.

  For more information on this problem, refer to Microsoft Knowledge Base Article - 305635 (http://support.microsoft.com/kb/305635).

• ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT. Set the database properties ON for ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT with the following script:

  ALTER DATABASE [${dbName}] SET ALLOW_SNAPSHOT_ISOLATION ON

  ALTER DATABASE [${dbName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  ALTER DATABASE [${dbName}] SET READ_COMMITTED_SNAPSHOT ON

  ALTER DATABASE [${dbName}] SET MULTI_USER

  Note  

  • The above database properties MUST be set for UCMDB database even if the database is not on AlwaysOn.

  • If the database is set on AlwaysOn, the above database properties must be set before you add the database on AlwaysOn.

The tempdb Database Settings

The frequent expansion of the tempdb system database can affect the database’s performance, especially in large Microsoft SQL Server installations. The size of the tempdb, therefore, should be large enough to avoid the need for early expansion. Its growth increment should be large enough to avoid fragmentation, yet not too large to expand in a reasonable amount of time. Create the tempdb with a minimum, initial size of 1 GB and with a growth increment of 50 MB. The tempdb database should be striped across several disks, ideally on a RAID 0+1 controller. It is recommended to move the tempdb database to its own set of disks.

To ensure that there is enough disk space for the tempdb to grow during times of heavy usage (for example, when aggregating or sorting data), it is recommended that you leave at least 20 GB free disk space on the drive where the tempdb is located.

File Groups

File groups are logical groupings of data files. Each of the following objects can be placed in its own file group unit:

• A table’s data
• A table’s large objects (text, ntext, image columns)
• An index

Data is inserted proportionally into all files belonging to the file group in which the object is stored, according to the amount of free space in each file. The .mdf file is placed in a file group called PRIMARY, which is marked as Default when the database is created (the default file group for objects when no file group is specified). If you do not place other data files (.ndf files) in their own file groups, they are also placed in the PRIMARY file group. Note that you can change the Default file group later on.

File groups can be used for performance tuning or maintenance. For details, see Microsoft SQL Server Books Online at http://www.microsoft.com/downloads.

Following is an example that demonstrates how to use file groups for maintenance:

• Partial Restoring. Microsoft SQL Server does not support the restoration of a single table. Even if you place a single table in a file group, you cannot restore a file group to a point in time earlier than the rest of the data. Instead, you must apply all log file backups in order to synchronize the file group with the rest of the data. Microsoft SQL Server supports partial restoration to a database with a different name. A partial restoration allows you to restore a single file group, and supports point-in-time restoration. However, you must restore the PRIMARY file group because it contains the SYSTEM tables.

  To be able to restore a single table to a point in time if a logical error occurs, you need to design the file groups in your database as follows:

  • Ensure that the .mdf file is the only file in the PRIMARY file group.

  • Place each large table in its own file group.

  • Place all small tables in a separate file group.

System Databases

The following system databases are especially important for the smooth performance of Microsoft SQL Server:

• tempdb. Numerous Microsoft SQL Server activities—such as creating local and global temporary tables, creating work tables behind the scenes to spool intermediate query execution results, and sorting—implicitly or explicitly use the tempdb system database.

  If your system is not configured properly, the tempdb database can become a performance bottleneck, so it is very important to determine the tempdb database’s original size correctly.

  For more information on setting database sizes, see The tempdb Database Settings.

  To move tempdb’s files, use the ALTER DATABASE tempdb MODIFY FILE command, and restart Microsoft SQL Server.

• master, msdb, model. These databases, although crucial for the operation of Microsoft SQL Server, are smaller than tempdb because they store only meta data.

  It is strongly recommended to use a fault tolerant disk—ideally, RAID 1—for these databases.

To check the database’s properties, run the following:

EXEC sp_helpdb <database name>