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/

TempDB orignial and current file size

The following script will help you in finding the file size of current tempdb and the size when SQL server last restarted.

SELECT
alt.filename
,alt.name
,alt.size * 8.0 / 1024.0 AS originalsize_MB
,files.size * 8.0 / 1024.0 AS currentsize_MB
FROM
master.dbo.sysaltfiles alt INNER JOIN tempdb.dbo.sysfiles files ON
alt.fileid = files.fileid
WHERE
dbid = db_id('tempdb')
AND alt.size <> files.size

Free Tools for the SQL Server DBA

Knowing what you don't know will always save you money. Before buying any tool you should consider asking a question, can I get a free tool for my requirement. Search around and analyse . To my surprise I found some companies brand their tool as free but after downloading it turns out to be not free rather free trial version. Don't put off by this. I have done it previously.

Always begin with an assumption that there is free tool that you could use. Do your research. It will save your company a lot of money and will make you a supremo.

Note also some of this free tools may not be user friendly. To mention just one SQLIO from Microsoft. It is not a tool that you just click and go. But have used it many times and very satisfied with the result that I got.

In general, free tools some times can compare with the tools that you spend a lot of money to do the same thing.

In addition, I came across an article written byDavid Bird for SQL server central and it will cover some of free tools that you will be able to use.

Distribution failure due to Data type differences

Recently I have faced with an issue were the published article column type is different from the subscriber column type. One of the publication articles column data type was varchar and the subscriber table (destination object ) data type was integer. The data in source object support to have a value similar to 012345 where the first character was the number zero and it didn't created any failures until somebody actually put in a value of C12345. This has lead to fail the replication. The only way to get around this was to delete the actual transaction from distribution database.
I used the following steps to do this:

use distributiondb
1. Get an article Id from MSarticles table
2. run exec sp_browsereplcmds @article_id = 'article Id'
3. get the xact_seqno of the article that caused this issue
4. delete the transaction from MSrepl_transactions.
- delete from MSrepl_transactions where xact_seqno =
5. delete the replication commands from MSrepl_commands
- delete MSrepl_commands where xact_seqno =
6. rerun the distribution agent
7. fix the route cause
Note that there is only one entry in MSrepl_transactions while you may have more than one entries in MSrepl_commands.

SQL Server 2000 virtual server install fails

Recently, I have tried to install SQL Server 2000 virtual server on windows server 2003 and the installation fails. I have installed numerous installations on windows 2000 server and never seen such an error. Tried to Google, went to various news groups and have been thinking of what it could be when I have discovered that this is a known issue that has been reported by Microsoft. For details of how to solve this issue go to
Microsoft help and support page

SQL Server Survival Guide

From time to time I refer to this sql server servival guide and would like to share with you. It focuses on SQL server 2000. For more details click SQL Server Survival Guide

Transactional replication optimisation

The following article from microsoft is a good starting point on how to optimise Transactional replication. Click the link for more info.Transactional replication Optimisation

Query replicated articles

Query replicated articles
If you are using an environment where there many publications and subscriptions it is difficult to find out the list of articles for particular subscriber.

The following code uses system tables in distribution to accomplish the task.
This query will retrieve publisher, publication, subscriber database, subsriber_id

select distinct pub.Publisher_db,pub.Publication, sub.subscriber_db,
sub.subscriber_id,art.article, art.destination_object from
distrib_.dbo.MSsubscriptions sub
join .dbo.MSPublications pub
on sub.Publication_id = pub.Publication_id
join .dbo.MSArticles art
on art.publication_id = pub.publication_id
where sub.subscriber_id = [subsriber_id]

If you have multiple distribution databases on the same server you can use union all with pre-fix of database name.

Removing registered servers

From time to time your registered servers will get out of date for example if the server is de-commissioned or the instance is removed. Using Enterprise manager some times frustrating. The easiest way to remove your registered server is by removing it from registry. The following step will help you how to do this.

a. Go to start menu and click run
b. Enter Regedit and click Ok
c. The registery editor will come up
d. Click on HK_Users and go to edit menu and click find
e. Type in Registered Servers X
f. Find the registered server from the list and delete

Adding the article's partition column(s)

Procedure to add articles partition column
exec sp_articlecolumn
@publication = N'PublicationName', @article = N'ArticleName', @column = N'ColumnName', @operation = N'add'
GO

Windows & SQL cluster

The following are usefull links if you are planning to do Windows & SQL cluster clustering project.
- Troubleshooting cluster node installations
- Designing and Deploying Clusters
- latest information about windows 2003
- Quorum Drive Configuration Information
- Recommended private 'Heartbeat' configuration on a cluster server
- Network Failure Detection and Recovery in a Server Cluster
-How to Change Quorum Disk Designation
- Server Clusters : Storage Area Networks