Password policy enforcement

If check_policy is on,

• check_expiration is also on, unless set to off explicitly
• Password history is initialized with the value of current password hash

If check_policy is off,

• check_expiration is set to off
• Password history is cleared
• Value of lock_out time is reset

 

Server Roles and Fixed server roles

• Roles are like groups in Windows operating system
• Server-level roles, as the name suggests, are server wide in their permission scope
• Fixed Server Roles are Server Level Roles but their permissions cannot be changed
• Each member of a fixed server role, can add other logins to that same role
• The Fixed server level roles are bulkadmin, dbcreator, diskadmin, processadmin, sysadmin, serveradmin, setupadmin, securityadmin
• securityadmin should be treated like sysadmin because it can grant access to the Database Engine and can configure user permissions
• Public is a server level role, but not fixed because the permissions can be changed
• Every sql server login belongs to the public role. A user that has not yet been granted or denied permissions on an object, inherits the permissions that are granted to the public role on that object. If public permissions are granted to an object, the object is available to all users.

What can the roles do

1. Bulkadmin – Members of the bulkadmin fixed server role can run the BULK INSERT statement.
2. DBCreator – Members of the dbcreator role can create databases, and can alter and restore their own databses
3. Diskadmin – manages disk files
4. Processadmin – Members of the processadmin role can terminate processes that are running in a sql server instance
5. Securityadmin – Members of the securityadmin role mange logins and their properties. They can GRANT, REVOKE AND DENY server level, and database level permissions. They can reset passwords for sql server logins.
6. Server admin – Members of the serveradmin can change server – wide configuration options and can shut down server.
7. Setupadmin – Memebers of the setupadmin can add and remove linked servers and also execute some stored procedures
8. Sysadmin – Members of the sysadmin fixed server role can perform any activity in the server. By default, all members of the Windows BUILTIN\Administrators group, the local administrator’s group, are members of the sysadmin fixed server role.

Sources : http://msdn.microsoft.com/en-us/library/ms188659.aspx

http://msdn.microsoft.com/en-us/library/ms175892.aspx

http://msdn.microsoft.com/en-us/library/ms188659%28v=sql.90%29.aspx

 

Database Principals

Principals are entities that can request resources. Principal can either be an individual or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection.

sys.database_principals gives a list of all the principals

Windows-level principals

• Windows Domain Login
• Windows Local Login

SQL Server-level principal

• SQL Server Login

Database-level principals

• Database User
• Database Role
• Application Role

IMG Source: http://www.codeproject.com/KB/database/sql-server-logins.aspx

Source:http://msdn.microsoft.com/en-us/library/ms181127.aspx

 

Tail Log Backup

Tail Log backups will backup those transactions in the log file which have not yet been backed up since the last backup.

In case of a disaster of a database, and a point in time recovery is needed, a tail log backup must be taken first. If you begin to restore the database first, the opportunity to restore thail log of the backup is forever lost.

Not all restore scenarios require a tail-log backup. You do not have to have a tail-log backup if the recovery point is contained in an earlier log backup, or if you are moving or replacing (overwriting) the database and do not need to restore it to a point of time after the most recent backup.

Source: http://msdn.microsoft.com/en-us/library/ms179314.aspx

 

Backupset

This is a table in the msdb database. It contains a single row for each backup set. A backup set contains the backup from a single, successful backup operation. You can get information regarding the size of a backup vs the compressed backup size.

 

Estimate a backup size

SQL Server only backs up the data pages in the database that have been used currently. So a full backup is generally smaller than the database size

You can use sp_spaceused to estimate the full backup size by subtracting the unallocated space from database_size

Source: Pro SQL Server 2008 Administration by Ken Simmons and Sylvester Carstarphen

 

Backups

• Full Backups – Contains all the data in a database or set of files or filegroups and also enough log to allow for recovering that data
• Only operations not allowed during full backup are shrink database, and adding/removing database file
• When CHECKSUM is specified, SQL Server verifies the page checkum, if it exists, before writing the page to the backup. In addition, a checksumis calculated for the entire backup that can be used to determine if the backup has been corrupted. The default behavior for errors encountered during a backup is STOP_ON_ERROR. If an invalid page checksum is encountered during a backup, the backup terminates with an error. To continue past the error and backup as many pages as possible, you can specify the CONTINUE_AFTER_ERROR option
• To ensure concurrent access and consistency, following steps are performed during a backup operation
1. SQL Server locks the database, blocking all transactions
2. Places a mark on the transaction log
3. Releases the database lock
4. Extracts all the pages in the data files and writes them to backup device
5. Locks the database, blocking all transactions
6. Places a mark in the transaction log
7. Releases the database lock
8. Extracts the portion of the log between the marks and appends it to the backup device
• Differential Backup  –
• Based on latest full backup aka differential base
• Differential backup contains only data that has changed since the last full backup (differential base)
• Diff. backups are smaller and faster to create than differential base if done soon after a full backup.
• At restore, full backup is restored first, followed by most recent differential backup
• In simple recovery model, a diff. backup can use only one differential base. In full recovery models, differential file backups can use multiple bases, but gets confusing
• Transaction Log Backup
• Only used for full and bulk logged recovery models
• Steps involves first taking a full backup, followed by an unbroken chain of transaction log backups.
• Each log backup covers that part of the transaction log that was active when the backup was created, and everything else that was not backed up during the previous log backup
• Frequent backups of the log not only minimizes risk of work loss, but also enables log truncation. Log truncation typically occurs after every log backup.
• SQL Server 2005 and later allows simultaneous backup of transaction log file while the full backup is being taken.
• A new log chain is started only when the recovery model is switched from simple to full or bulk logged; or a full backup is taken

Source:
BOL http://msdn.microsoft.com/en-us/library/ms175477.aspx
MCTS 2008 – Training Kit – Mike Hotek

 

Database Snapshots

What is it:

• A read-only, static view of a database
• Consistent with the source database at the moment of the snapshot’s creation.
• When database snapshot is created, the source database will typically have open transactions. Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transactionally consistent.

Uses:

• Maintaining historical data for report generation
• Safeguarding data against administrative error
• Managing a test database – Before running the first round, an application developer or tester can create a database snapshot on the test database. After each test run, the database can be quickly returned to its prior state by reverting the database snapshot.
• Safeguarding data against user error – To recover from a user error, you can revert the database to the snapshot immediately before the error. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward

Restrictions:

• You can’t back up, restore, or detach a database snapshot
• The snapshot must be on the same instance as the source database.
• Full text indexes are not supported – though u can create a snapshot against a database that contains full text indexes
• FILESTREAM is not supported and any FILESTREAM data is inaccessible through the database snapshot – though you can create a snapshot against a database that is enabled for FILESTREAM.
• you can’t create a snapshot against a system database
• you cant’ drop, restore, or detach a source database that has a snapshot created against it
• you can’t reference filegroups that are offline, defunct or restoring.

When a Database Snapshot is created, SQL Server doesn’t allocate space on disk equivalent to the current size of the data fi les in the source database. Instead, SQL Server takes advantage of an operating system feature called sparse fi les. A sparse fi le is essentially an entry in the file allocation table and consumes almost no space on disk. As data is added to the fi le, the file automatically grows on disk. By using sparse fi les, the creation time for a Database Snapshot is independent of the size of the source database.

TIP DATABASE SNAPSHOT MAXIMUM SIZE
Because SQL Server maintains the Database Snapshot at the point in time that the Database Snapshot was created, the maximum size of the Database Snapshot is the amount of data that existed in the source database at the time of creation.

Source: From BOL

MCTS 2008 Training kit – Mike Hotek