Basic SQL Maintenance Plan tasks:

1. Daily full backup, delete the old backups
Could use the SQL scripts or Quest Tool

2. Differential backup
Could use the SQL scripts or Quest Tool


3. Transaction backup (only for Full recovery model and need to be done after a full backup is performed).
Could use the SQL scripts or Quest Tool

4. Index Defrag
This feature will be removed in the next version of MS SQL server. It is not supported for use on system tables.

It defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes. therefore improving index-scanning performance.

DBCC INDEXDEFRAG shuffles index leaf pages in place. Therefore, if an index is interleaved with other indexes on disk, running

DBCC_INDEXDEFRAG against that index does not make all leaf pages in the index contiguous, To improve the clustering of pages, rebuild the index.

When it runs, index defragmentation occurs serially, which menas that the operation on a single index is performed using a single thread. No parallelism occurs. operations on multiple indexes from the same DBCC INDEXDEFRAG statements are performed on one index at a time.

It is an online operation. It does not hold locks long term. regardless of the database recovery model setting, it is always fully logged.

5. Index Rebuild -- to recreate the indexes on the tables in the database with a new fill factor. The fill factor determines the amount of empty space on each page in the index, to accommodate future expansion. As data is added to the table, the free space fills because the fill factor is not maintained. Reorganizing data and index pages can re-establish the free space. SQL 2005 or higheruse the ALTER INDEX. SQL 2000 use DBCC DBREINDEX.

It will drop the indexes on the tables in the database and re-create them with a new, automatically calculated fill factor.

thereby reserving the specified amount of free space on the index pages. the higher the percentage, the more free space is

reserved on the index pages, and the larger the index grows. Valid value are from 0 through 100.

Use the SORT_IN_TEMPDB option, which determines where the intermediate sort results, generated during index creation, are temporarily stored. If a sort opertion is not required, or if the sort can be performanced in memeroy, this option is ignored.

Use the ONLINE option which allows users to access the underlying table or clustered index data and any associated nonclustered indexes during index opertions.

6. Cycle SQL error log
make the SQL error log size under control so it can be opened and view through SSMS. EXEC master.sys.sp_cycle_errorlog;

7. Update statistics
The Database Engine keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index or indexes to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.

If lots of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated by using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

To see when the statistics were last updated, use the STATS_DATE function.

8. DBCC CheckDB

If the compatibility level is 100 (SQL Server 2008) or higher:

Unless NOINDEX is specified, DBCC CHECKDB performs both physical and logical consistency checks on a single table and on all its

nonclustered indexes. However, on XML indexes, spatial indexes, and indexed views only physical consistency checks are performed by default.

If WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on an indexed view, XML indexes, and spatial indexes, here present. By default, physical consistency checks are performed before the logical consistency checks. If NOINDEX is also specified, only the logical checks are performed.
These logical consistency checks cross check the internal index table of the index object with the user table that it is referencing. To find outlying rows, an internal query is constructed to perform a full intersection of the internal and user tables. Running this query can have a very high effect on performance, and its progress cannot be tracked. Therefore, we recommend that you specify WITH EXTENDED_LOGICAL_CHECKS only if you suspect index issues that are unrelated to physical corruption, or if page-level checksums have been turned off and you suspect column-level hardware corruption.

If the index is a filtered index, DBCC CHECKDB performs consistency checks to verify that the index entries satisfy the filter predicate.

If the compatibility level is 90 or less, unless NOINDEX is specified, DBCC CHECKDB performs both physical and logical

consistency checks on a single table or indexed view and on all its nonclustered and XML indexes. Spatial indexes are not supported.

9.00.3042 is SQL 2005 SP2 version.

What is new in SQL 2008

Four key areas:
Enterprise Data Platform
- Data Encryption (TDE and Backup Encryption, EKM, HSM)
- Resource management (Resource Governor)
- System Analysis
- Server Management (Performance Dashboard tool)
Synamic Development
- LINQ (Launguage Integrated Query)
- Business Data Entities eSQL(entity) LOB (Line of business)
beyond Relational database
- Large UDT (2005 UDT can not be larger than 8000 bytes) 2008 no size restriction
- Dates and Times
- File Stream new data type VarBinary In the past, we use BLOB. Now SQL functions like triggers, Full text search, and backup restore can be applied to binary data
- Spatial Data to allow Latitude, Longitude, and GPS-based data entries to be natively stored.
- Table Value Parameters: previous versions of SQL servers there was not native way to pass a table to a stored procedure. The workaround was to pass a large varchar or XML type and parse through it. 2008 support table parameters.
e.g. CREATE TYPE PartType AS Table(PartID varchar(50), Descr varchar(100), crateDate datetime);
CREATE PROCEDURE AddPart(@PartList PartType READONLY)
AS
SELECT * FROM @PartList

DECLARE @PartTable PartType;
INSERT INTO @PartTalbe values('Parts1', N'for wheel', '2007-09-30');
EXEC AddPart @PartTable


pervasive Insight
- Data Integration Features such as the MERGE statement, Parallelism, SSIS multiple processor improvements, and look up performance improvements.
- Analysis Service Improvements including BI Stack performance, Scale out analysis, Block computations and Perspectives.
- Microsoft Office 2007 Integration such as Exporting Reporting Service reports as Word docs, SSRS format and font improvements, and the Office Tool Bar.


Data type changes:
DATETIME
DATETIME2
DATE TIME
DATETIMEOFFSET

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.