SQL 2008 What's new

Policy-Based Management

Policy-Based Management enables the efficient management of multiple SQL Server instances from a single location. Easily create policies that control security, database options, object naming conventions, and other settings at a highly granular level. Policies can evaluate servers for compliance with a set of predefined conditions and prevent undesirable changes being made to servers.

Data compression

Data compression reduces the amount of storage space needed to store tables and indexes, which enables more efficient storage of data. Data Compression does not require changes be made to applications in order to be enabled.

Transparent Data Encryption

Transparent Data Encryption enables data to be stored securely by encrypting the database files. If the disks that contain database files become compromised, data in those files is protected because that data can only be de-encrypted by an authorized agent. SQL Server performs the encryption and de-encryption directly, so the process is entirely transparent to connecting applications. Applications can continue to read and write data to and from the database as they normally would. Backup copies of encrypted database files are also automatically encrypted.

Add Hot CPUs and Hot memory

Hot-add CPUs, a feature available with the 64-bit edition SQL Server Enterprise, allows CPUs to be dynamically added to servers as needed, without the need to shut down the server or limit client connections. Hot-add memory enables memory to be added in the same way.

Change Data Capture

Use Change Data Capture (CDC) to track changes to the data in your tables. CDC uses a SQL Server Agent job to capture insert, update and delete activity. This information is stored in a relational table, from where it can be accessed by data consumers such as SQL Server 2008 Integration Services. Use CDC in conjunction with Integration Services to incrementally populate data warehouses, enabling you to produce more frequent reports that contain up-to-date information. It also allows sync-enabled mobile and desktop applications to perform efficient data synchronization between client and server, without requiring changes to the database.

LINQ

Language Integrated Query (LINQ) is a .NET Framework version 3.5 feature that provides developers with a common syntax to query any data source from client applications. Using LINQ to SQL or LINQ to Entities, developers can select, insert, update, and delete data that is stored in SQL Server 2008 databases using any .NET programming language such as C# and VB.NET.

FILESTREAM data

FILESTREAM enables binary large object (BLOB) data to be stored in the Microsoft Windows NTFS file system instead of in a database file. Data that is stored using FILESTREAM behaves like any other data type and can be manipulated using T-SQL select, insert, update and delete statements. Unlike traditional BLOB storage, FILESTREAM data is logically shackled to the database while being stored efficiently outside the database in the NTFS file system. FILESTREAM data participates in all SQL Server transactions and backup operations, along with the rest of the database.


DATE / TIME Data Types

SQL Server 2008 introduces several new date and time based data types. DATETIME2 references the Coordinated Universal Time (UTC) instead of the system time for greater accuracy and can store date and time data to a precision of 100 nanoseconds. The new DATE and TIME data types enable you to store date and time data separately. The new DATETIMEOFFSET data type introduces time zone support by storing date, time and offset such as ‘plus 5 hours’.

Spatial data with GEOGRAPHY and GEOMETRY data types

New GEOGRAPHY and GEOMETRY data types allow spatial data to be stored directly in a SQL Server 2008 database. Use these spatial data types to work with location-based data that describes physical locations, such as longitude and latitude.

GEOGRAPHY enables you to represent three-dimensional geodetic data such as GPS applications use. GEOMETRY enables you to represent two-dimensional planar data such as points on maps. Spatial data types help you to answer questions like ‘How many of our stores are located within 20 miles of Seattle?’

SQL 2005 What's New

Database Administration:

Database Mirroring

Extend log shipping capabilities with the database mirroring solution. You will be able to use database mirroring to enhance availability of your SQL Server systems by setting up automatic failover to a standby server.

Online Restore

With SQL Server 2005, database administrators are able to perform a restore operation while an instance of SQL Server is running. Online restore improves the availability of SQL Server because only the data being restored is unavailable; the rest of the database remains online and available.

Dedicated Administrator Connection

SQL Server 2005 provides a dedicated administrator connection that administrators can use to access a running server even if the server is locked or otherwise unavailable. This capability enables administrators to troubleshoot problems on a server by executing diagnostic functions or Transact-SQL statements.

Snapshot Isolation

Snapshot Isolation (SI) level is provided at the database level. With SI, users can access the last committed row using a transitionally consistent view of the database. This capability provides greater scalability.

Data Partitioning

Data partitioning is enhanced with native table and index partitioning that enables efficient manageability of large tables and indexes.



Development:

Hosted CommonLanguage Runtime

With SQL Server 2005 developers can create database objects using familiar languages such as Microsoft Visual C# .NET and Microsoft Visual Basic .NET. Developers can also create two new objects—user-defined types and aggregates.

Native XML Support

Native XML data can be stored, queried, and indexed in a SQL Server database—allowing developers to build new classes of connected applications around Web services and across any platform or device.

Security Enhancements

The security model in SQL Server 2005 separate users from objects, provides fine-grain access, and enables greater control of data access. Additionally, all system tables are implemented as views, providing more control over database system objects.

Transact-SQL Enhancements

SQL Server 2005 provides new language capabilities for developing scalable database applications. These enhancements include error handling, recursive query capabilities, relational operator PIVOT, APPLY, ROW_NUMBER and other row ranking functions, and more.

Reliable Messaging for Asynchronous Applications

Service Broker is a robust messaging infrastructure that provides reliable transactional delivery of critical messages between servers—with the scalable high-performance that is expected with asynchronous queuing.

Visual Studio Integration

Tight integration with Microsoft Visual Studio and the .NET Framework streamlines development and debugging of data-driven applications. Developers can build database objects, such as stored procedures, using any .NET language and can seamlessly debug across .NET and Transact-SQL (TSQL) languages.


Business Intelligence:

Integration Services (SSIS)

SQL Server Integration Services (SSIS) is a next generation data integration platform that can integrate data from any source. SSIS provides a scalable and extensible platform that empowers development teams to build, manage, and deploy integration solutions to meet unique integration needs.


Reporting Services

SQL Server Reporting Services is a comprehensive, server-based reporting solution designed to help you author, manage, and deliver both paper-based and interactive Web-based reports.

10 Steps to Securing your SQL Server - nice article

Nice article

Fixed Server Roles

Members of the bulkadmin fixed server role can run the BULK INSERT statement.

Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

The diskadmin fixed server role is used for managing disk files.

Members of the processadmin fixed server role can terminate processes that are running in an instance of SQL Server.

Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.

Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.

Members of the setupadmin fixed server role can add and remove linked servers, and also execute some system stored procedures.

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.

For details on role-level permission, execute sp_srvrolepermission rolename, e.g., sp_srvrolepermission processadmin

Trap InputBuffer for all processes

declare @spid varchar(10)
declare @sql varchar(50)

Create Table #inputbuffer ([EventType] nvarchar(30),[Parameters] int,[EventInfo] nvarchar(4000),[SPID] varchar(10))

declare activesp cursor fast_forward for
select spid from master.dbo.sysprocesses where dbid > 1

open activesp
fetch next from activesp into @spid

while @@fetch_status = 0
begin
set @sql = 'dbcc inputbuffer(' + @spid + ')'
insert into #inputbuffer([EventType], [Parameters], [EventInfo])
exec (@sql)

update #inputbuffer set spid = @spid where spid is null

fetch next from activesp into @spid
end

close activesp
deallocate activesp

if @@cursor_rows <> 0
begin
close sprocket
deallocate sprocket
end


select * from #inputbuffer

drop table #inputbuffer

GO

Re-organize or Rebuild index?

select object_name(A.object_id), I.Name, A.avg_fragmentation_in_percent,*
from sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'detailed') as A -- detailed shows the record_count
inner join sys.indexes as I
On A.object_id = I.object_id And A.index_id = I.index_id

avg_fragmentation_in_percent:
<=30 - Reorganize
>30 - Rebuild

Database Recovery phases

There are 3 phases to a database recovery:

1. Data Copy: Involves copying all data, log, index pages from the backup file/media to the databases files.

2. Redo/Roll-forward: Involves applying the logged transactions to the data copied from the backup to roll forward that data to the recovery point. This includes uncommitted data too; therefore, the database is in an inconsistent state.

3. Undo: Involves rolling back the uncommitted transactions, bringing the database in a consistent state.

Finally, the Recovery brings the database online for users.


When NORECOVERY option is used, the restore process only completes the 1 phase - Data Copy. Therefore, neither committed transactions are rolled forward nor uncommitted transactions are undone/rolled back.

Some exercises on backup/recovery

create database test
on
(
name = 'test_data',
filename = 'D:\Data\test.mdf',
size = 2,
maxsize = unlimited,
filegrowth = 10%
)
log on
(
name = 'test_log',
filename = 'E:\Log\test.ldf',
size = 1,
maxsize = unlimited,
filegrowth = 10
)

alter database test set recovery simple
alter database test set recovery bulk_logged

use test

create table t1 (c int)

backup database test to disk = 'F:\testfull2.bak' with init
backup database test to disk = 'F:\testdiff2.bak' with differential,init
backup log test to disk = 'F:\testlog3.bak'

insert t1 select 1
insert t1 select 2
select * from t1

restore headeronly from disk = 'F:\testdiff1.bak'
restore headeronly from disk = 'F:\testdiff2.bak'
restore headeronly from disk = 'F:\testfull1.bak'
restore headeronly from disk = 'F:\testfull2.bak'
restore headeronly from disk = 'F:\testlog1.bak'
restore headeronly from disk = 'F:\testlog2.bak'
restore headeronly from disk = 'F:\testlog3.bak'


drop database test
use master
restore database test from disk = 'F:\testfull2.bak' with norecovery
move 'test_data' to 'F:\Data\test_data.mdf',
move 'test_log' to 'F:\Log\test_log.ldf'

restore database test from disk = 'F:\testdiff2.bak' with norecovery
sp_helpdb test

restore log test from disk = 'F:\testlog3.bak' with norecovery
restore database test with recovery
drop database test

select * from t1

sp_detach_db test
create database test on (filename = 'F:\Data\test_data.mdf') log on (filename = 'F:\Log\test_log.ldf') for attach

Activity views

select * from sys.dm_exec_connections
select * from sys.dm_exec_sessions
select * from sys.dm_exec_requests

More on Backups

Full backup results in:

1. Backs up any activity that took place during the backup

2. Backs up any uncommitted transactions in the transaction log. (When the database is recovered from this backup, the uncommitted transactions are rolled back.)


Transaction log backup results in:
1. Backs up the log from the last successful [Backup Log] statement to the end of the current log.

2. Truncates the log up to the beginning of the active portion of the log and discards the information in the inactive portion.

Active portion of the transaction log starts at the point of the oldest open transaction and continues to the end of the log.

A Tail log backup includes the portion of the log that has not been backed up (active portion) - this backup does not truncate the log and is used when the data files are damaged but the log is undamaged.

Differential backup results in:
1. Backs up the parts of the database that have changed since the last full database backup.

2. Backs up only the extents that changed since the last base backup.

3. Backs up the activity that took place during the differential backup and any uncommitted transactions as well - this is similar to full backup.

Simplifying Database Mail

Easy steps to configure this:

1. Add the SQL Server Agent service account to the msdb users and then to the DatabaseMailUserRole.

2. Configure the Database Mail to create a default SQL Mail profile - keeping 'Anonymous Authentication' for the SMTP account.

3, Map this SMTP profile to the SQL Server Agent - Properties -> Alert System.

4. Restart Agent.

Tail log

In most cases, under the full or bulk-logged recovery models, SQL Server 2005 and later versions require that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.

SQL Server 2005 and later versions usually require that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact. When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.

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. Also, if the log files are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost.


If the database is online, before starting a restore sequence, back up the tail of the log using WITH NORECOVERY whenever the next action you plan to perform on the database is a restore operation:

BACKUP LOG database_name TO WITH NORECOVERY

If the database is offline and does not start.
Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use either WITH CONTINUE_AFTER_ERROR or WITH NO_TRUNCATE.

BACKUP LOG database_name TO [WITH { CONTINUE_AFTER_ERROR | NO_TRUNCATE }