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