Transparent Data Encryption (SQL server 2008)

Transparent Data Encryption (TDE) is a new feature in SQL server 2008.
TDE is a new feature in SQL Server 2008; it provides real time encryption of data and log files. Data is encrypted before it is written to disk; data is decrypted when it is read from disk. The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it. There is absolutely no code that needs to be written to perform the encryption and decryption. There are a couple of steps to be performed to prepare the database for TDE, then the encryption is turned on at the database level via an ALTER DATBASE command.

SQL Server Management Studio Support for SQL Azure

Do you know that SQL Server 2008 R2 November CTP have added support for Server Management Studio Support for SQL Azure ? I cannot wait to play with it.

Microsoft SQL Azure™

Microsoft SQL Azure is the part of the new windows Azure platform. It seems to me that Microsoft is trying to address, get into and/or use the new hype surrounding cloud computing. I don't see this offering different from oracle on demand that has been there for long. I am not sure also after seeing oracle on demand how good the offering is.

Microsoft claims that
SQL Azure Database provides Internet-facing database and advanced query processing services and is the ideal solution for customers building new applications or integrating with existing investments into the cloud.
I will wait and see.

Microsoft sql server case studies

Who start using sql server ? Some eye catching case studies can be found @Microsoft SQL server case studies

SQL Server 2008: Benchmarks

Top Ten TPC-E database performance benchmarks carried out by TPC (Transaction processing benchmark council) @ SQL Server 2008: Benchmarks

SQL Server compared to Oracle

Microsoft view of comparison between SQL server 2008 and oracle 11G. Microsoft claims that Microsoft® SQL Server® 2008 outperforms Oracle in the areas that matter to business. I am not sure what this mean. But you can see this interesting comparison by Microsoft @SQL Server compared to Oracle

Microsoft SQL Server Playback Program

Microsoft have introduced a Microsoft SQL Server Playback Program. For more information on how the program works you can download and see and view

program overview

goals of the program

if you would be able to participate

processes and procedures that is required

benefit to customers who participate in the progaram

and more can be found @ Microsoft SQL Server Playback Program

Microsoft SQL Server 2008 R2

What is new in Microsoft SQL Server 2008 R2?

1. Master Data Services
Microsoft SQL server 2008 R2 contains a master data management applications. Master data services have got the following tools.

Master Data Services Configuration Manager. This configuration manager will help you to create and configure master data services databases and web applications

Master data services web service. This component is more useful to developers as they would be able to extend or develop custom solutions for master data services

Master Data Manager. This is used by users to manage master data

2. PowerPivot for SharePoint

Microsoft SQL Server PowerPivot for SharePoint extends SharePoint 2010 and Excel Services to add server-side processing, collaboration, and document management support for the PowerPivot workbooks that you publish to SharePoint.

3. Multi-Server Administration and Data-Tier Application
Full details of this new feature can be found @Multi-Server Administration

4. Support or 256 logical processors

Support for 256 logical processor is now added in R2 but this requires to be run on Windows 2008 R2. This is a big jump from previous maximum value of 64 processors. Quite a bit jump in my opinion.

5. Support for visualization of geographic spatial data in Reporting Services
Support for reports with visual geographic mapping is now supported in SQL Server 2008 R2.

SQL Server 2008 R2 (CTP)

The SQL Server Team announced today the release of the SQL Server 2008 R2 November Community Technology Preview (CTP). This is now availabel for download and installation for development and test system.

The anticipated ship date is going to be the first half of 2010 if things goes as planned.

To download and view the new features go to SQL Server 2008 R2 (CTP)

The Curse and Blessings of Dynamic SQL

Use dynamic SQL in various version of sql server starting from 6.5, 7.0, 2000, 2005 and 2008. It is indeed a very good reference. To view this click The Curse and Blessings of Dynamic SQL

Is it time to upgrade to SQL server 2008

I always tend to wait for SP1 to be released before tempting to upgrade prodution servers to new editions of SQL server. Now the CTP for Service Pack 1 of SQL Server 2008 is out, is it the time to upgrade ?

Project Madison

Project Madison is Microsoft’s collaborative hardware and software solution for high-end data warehousing. It looks that microsoft is moving agressively into the data warehouseing market. For detailed info on what project Madison you can see the overview @ more on project Madison

Data Warehousing (SQL server 2008)

I have heard many times that SQL Server 2008 is revamped in the area of data
warehousing. In trying to find out what the major areas that can support data warehouse that is different from SQL server 2005 I came accross the following new features in SQL server 20008.

Apart from the new features that is part of the SQL server release I think the following are the highlights in my opinion.

- Star join query optimizations
- Grouping sets
- MERGE SQL statements
- Change data capture
are the once that catch my eyes.

For detailed info on new features you can view at Introduction to New Data Warehouse Scalability Features in SQL Server 2008 - Technical article

SQL Server Fast Track Data Warehouse

On 23rd of Feb 2008, icrosoft announced SQL Server Fast Track Data Warehouse, a new set of Reference Architectures for SQL Server 2008 that enables customers to accelerate their Data Warehouse deployments and reduce cost.

I would like to hear anybody who have got experience with this. The hightlight of this annoncement is that customers will be able to start data warehouse design with templates provided by Avanade, Hitachi Consulting and Cognizant and HP. Once I looked at them I will love to post how good/user friendly it is and how good the templates are at helping you in acheiving a data warehouse that satisfies your specific business needs.

As per the anouncement the the highlights are I qoute "

Seven new Reference Architectures with storage capacities from 4 to 32 TB were unveiled in partnership with HP, Dell and Bull. Developed and tested by Microsoft, these architectures use balanced hardware optimized for Data Warehousing. As a result customers will get

* Better price performance than competitive solutions. Fast Track Data Warehouse offers similar performance to the competition at 1/5th the price
* Faster time to value and lower cost to setup and configure
* Better performance out of box through pre-tested hardware.

Name value pair part II

The article (name value pair part II ) that I published on SQLServerCentral.com is now pulished on best of SQL server central 2008 e-book and can be downloaded from Red-gate software for free.

Issues dropping distribution database

From time to time I came accross when using EM will not completely disable replication leaving distribution database. When you try to drop this database you will get the following error.
Cannot drop the distribution database 'distribution' because it is currently in use. The way to get around is to follow the following steps


USE master

GO
EXEC sp_configure 'allow updates', '1'
RECONFIGURE with override
GO
update master.dbo.sysdatabases
set category = 0
where dbid =

go
EXEC sp_configure 'allow updates', '0'
RECONFIGURE with override

GO
drop database

SQL Server 2008

SQL server 2008 is now shipping !!. To sum up waht is new in SQL server 2008 ?
The following are some that I have come accross.
- Automatic Recovery of Data Pages
- Log Stream Compression
- Resource Governor
- Predictable Query Performance
- Data Compression
- Hot Add CPU
- Policy-Based Management
- Streamlined Installation
- Performance Data Collection
- Language Integrated Query (LINQ)
- ADO.NET Object Services to simplify applicaton development
- DATE/TIME (Date, Time, Datetimeoffset and datatime2) data type
- HIERARCHY ID
- FILESTREAM Data
- Integrated Full Text Search
- Sparse Columns (yes new addition)
- Large User-Defined Types
- Spatial Data Types
- Backup Compression
- Partitioned Table Parallelism
- Star Join Query Optimizations
- Grouping Sets
- Change Data Capture
- MERGE SQL Statement (I have waited for long for this)
- SQL Server Integration Services (SSIS) Pipeline Improvements
- SQL Server Integration Services (SSIS) Persistent Lookups
- Analysis Scale and Performance
- Block Computations
- Writeback (on OLAP)
- Enterprise Reporting Engine
- Internet report deployment
- Manage Reporting Infrastructure
- Report Builder Enhancements
- Forms Authentication Support
- Report Server Application Embedding
- Microsoft Office Integration
- Predictive Analysis

SQL Server 2005 cloning

I have heard a lot on cloning oracle E-business suite. But not SQL Server. Microsoft still lags behind with the idea of cloning. But, I have noticed on Kalen Delaney blog how to do at least some sort of cloning. It is useful that you can script you database with statistics and histogram and able to re-run your execustion plan without loading actaul data. To see detailed info on this visit Kalen's blog on SQL server 2005 Cloning

EAV (name value pair)

part II of EAV or name value pair is now published on SQL Server centeral. Part II will see how to improve a name value pair database that has been implmented to do what a normalised database system should do.

The article will try to address the main issues that you face when using name value pair such as scalability and difficulty in getting a record out of a database.

To view full article you can go to Name value pair part II

Name value Pair

I have published a new article on name value pair on SQL server central (http://www.sqlservercentral.com/). Part I of the article discusses about the benefits , drawbacks and perception from different angles .

To view the article click http://www.sqlservercentral.com/articles/Database+Design/62386/