SQL Resource Database

1. Read-only hidden system database
2. Does not contain any user data or any user metadata.
3. mssqlsystemresouce.mdf mssqlsystemresource.ldf
4. each instance has only and only one resource database, ID is always 32767
5. in SQL 2005, resource database file need to be at the same location as Master database files.
6. in SQL 2008, Master and Resource database files can be at different locations.
7. in SQL 2008, it is under binn directory and don't recommend to move it since any service packs and hotfixes will move it back to the original location. :\Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Binn.
8. Backup resource database using file based or a disk based backup .
9. Restore it by coping the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the respective folders.

NET START MSSQLSERVER /f /T3608
NET START MSSQL$instancename /f /T3608

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME='new_path\mssqlsystemresource.mdf')

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME='new_path\mssqlsystemresource.ldf')

ALTER DATABASE mssqlsystemresource SET READ_ONLY


SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'mssqlsystemresource')

SQL Server 2008 TDE

To use TDE, follow these steps.

1 Create a master key

2 Create or obtain a certificate protected by the master key

3 Create a database encryption key and protect it by the certificate

4 Set the database to use encryption


The following example illustrates encrypting and decrypting the AdventureWorks database using a certificate installed on the server named MyServerCert.


USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'
go
USE AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON
GO

SQL 2008 Backup compression and Data Encryption

1. SQL backup compression and TDE encryption are mutually exclusive.
2. CPU impact (Expect 25% to 30% CPU utilization increase at least)
3. Require EE version, but any 2008 version can restore it
4. Compressed backup need to reside on the same media. Uncompressed backups reside on a different media. Compressed and uncompressed backups can not co exist in a media set
5. Previous bersions of SQL server can not read compressed backups.
6. NTbackups can not share a tape with compressed SQL server backups.


To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table, as follows:

backup_size:compressed_backup_size

SQLServer 2005 -- Dynaic Management Views

The dynamic management views (DMVs) in SQL Server 2005 are designed to give you a window into what's going on inside SQL Server. They can provide information on what's currently happening inside the server as well as the objects it's storing. They are designed to be used instead of system tables and the various functions provided in SQL Server 2000. This article provides an introduction to DMVs and covers a few of the basic views and functions.

The Dynamic Management Views are actually composed of both views and table-valued functions. Some apply to the entire server and are stored in the master database. Others are specific to each database. All are stored in the sys schema. They all start with dm_ in the name. They have been broken up into twelve categories:

Common Language Runtime Related Dynamic Management Views

I/O Related Dynamic Management Views and Functions

Database Mirroring Related Dynamic Management Views

Query Notifications Related Dynamic Management Views

Database Related Dynamic Management Views

Replication Related Dynamic Management Views

Execution Related Dynamic Management Views and Functions

Service Broker Related Dynamic Management Views

Full-Text Search Related Dynamic Management Views

SQL Operating System Related Dynamic Management Views

Index Related Dynamic Management Views and Functions

Transaction Related Dynamic Management Views and Functions

SQL Server 2005 Performance Dashboard Reports

Free report from Microsoft:
http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Overview
The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Reporting Services is not required to be installed to use the Performance Dashboard Reports.

AAP Certification

AAP (Accredited ACH Professional) Certification Exam
Overview:
120 Multiple Choice Questions
Including 20 non-graded pilot questions
3 Hours to Complete


Test Makeup:
General payment overview 10%
Marketing & products 8%
Risk management 15%
Rules and regulations 24%
Operational process/flow 28%
Technical standards/formats 15%

Granularity of Locks

Hierarchical lockable units:

assume set of resources to be locked is organized in a hierarchy, e.g. (database, areas, files, records) as a simple hierarchy. Each node of hierarchy can be locked, and in doing so, one implicity locks all of that node's descendants. There is shared access (S, shared-read access) and exclusive access (X, exclusive read/write access).
In order to lock a subtree rooted at node R in S or X, one must prevent others from obtaining incompatible locks on any ancestor of R. A new mode, "intention mode" is introduced to do this - all ancestors of a node are tagged with intention mode before the node itself is locked. Summary of modes:
null (NL): no access
intention-share (IS): allows requestor to lock descendant nodes in S or IS mode. (does no implicit locking)
intention-exclusive (IX): allows requestor to explicitly lock descendants in X, S, SIX, IX, or IS mode. (does no implicit locking)
share (S): access to node and all descendants without setting further locks. (implicitly sets S locks on all descendants)
share and intention exclusive (SIX): implicitly locks all descendants of node in share mode and allows requestor to explicitly lock descendant nodes in X, SIX, or IX mode. (for finer grained locking)
exclusive (X): exclusive access to node and all descendants. (implicitly sets X locks on all descendants)

To request a node,
before requesting S or IS on node, all ancestor nodes must be held in IX or IS mode.
before requesting X, SIX, or IX on node, all ancestors must be held in SIX or IX mode.
locks should be released either at end of transaction (in any order), or in leaf to root order in the middle of a transaction.

Source from: http://swig.stanford.edu/pub/summaries/database/locks.html