CDC - Change Data Capture

Quite useful for Auditing in finding the insert, update & delete in a database and this can be placed in another new table.

--TO activate the CDC
EXEC sys.sp_cdc_enable_db_change_data_capture

--To enable CDC on table
EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema = 'dbo',
@source_name = 'myTable', @role_name = 'cdc'

This enables tracking down the changes and auditing much easier the way it was in the past. I really have to spend a lot of time on this in past, but this new feature is really cool.

Compressing Backup

Compressing the backup is first provided by 3rd party vendors such as redgate, Quest, etc., But this option is wide open in SQL Server 2008 onwards which has become more popular. I have personally used this and found the result is 60% of the compression is possible and leaving more empty space. Another good part is it takes very less time to compress the big backup files.

Once you have created a backup plan and compressing technique you will be able to view the T-SQL code that reflects the backup and compression process you have created. When you click on the "View T-SQL" Button that will produce the actual T-SQL code that the server executes. But there is always a warning message on this T-SQL Script screen "The T-SQL shown here may not necessarily be the exact T-SQL executed at the server due to any conditional logic you configured for this task."

BACKUP DATABASE [db] TO DISK = N'C:\Backup\db.bak' WITH
NOFORMAT, NOINIT, NAME = N'db-Full Database Backup', SKIP,
NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

Knowing the GUI and T-SQL is always better for a day to day DBA job.

So, good to go and why don't you give a try.

Sparse Columns

This is a new feature available to support the columns with the NULL values. Any column having NULL values having sparse column enabled will decrease the file size but enabling this feature other than NULL value columns will increase the file size. So be cautious while enabling this option.

This option can be enabled using GUI or T-SQL code. By editing an existing table and select the Is Sparse option in the Column Properties which can be seen in the below screen.

The same can be achieved using the following T-SQL.

CREATE TABLE dbo.Table
(
Column 1 int NULL,
Column2 varchar(30) SPARSE NULL
) ON [PRIMARY]
GO

SQL Server 2008 New Features

With SQL Server 2008 version there are quite a number of new features are added in. Those are listed below.

1. Installation of SQL Server 2008 with more options
2. Compressed Backups
3. Enhanced configuration
4. Management of Audits with CDC (Change Data Capture)
5. New Table Value Parameter
6. Filestream Data types
7. Sparse Columns
8. Encryption Enhancements
9. High Availability
10. Performance
    1. Performance Data Management
    2. Resource Governor
    3. Freeze Plan
11. Declarative Management Framework (DMF)
12. Development Improvements
    1. LINQ Supports
    2. MERGE Statement
13. Spatial Data type
14. Analysis Services Improvements
15. ETL Enhancements
16. Few on Reporting Services
    1. No longer use of IIS
    2. Better Graphing
    3. Export to Word Support
17. Some of the Deprecated & Discontinued Features