Wednesday, November 30, 2005

Hyperthreading hurts server

Some developer beleive that HT technology hurts performance. For details see HT Technology

Moving System Databases

Moving System Databases - A Checklist
Moving user databases from one server to another is straight forward process. Moving system database requires quite more attention and planning. Christoffer Hedgate a regular columnst on SQLServerCentral.com have written a checklist that will help you to move system databases from one server to another. You can find this article on Moving system databases

Changing SQL Licensing

This article is about changing the licencing mode of SQL Server 2000 from per processor licence to per seat licence. Is it at all possible. This method is not documented in SQL Server 2000.

To change SQL Server licensing you go to control panel and one of the licensing mode is greyed out and you are only able to add either devices if it is per seat or processor if the licensing is per processor.

To change the licensing mode from one type to another you can do the following. Do this at your own risk as it involves changing the registry key.
In the registry Navigate to: Hkey_Local_Machine\Software\Microsoft\Microsoft
SQL Server\80 \MSSQLLicenseInfo\MSSQL8.00\Mode

Change the mode to
- 0 if you want per seat licence
- 1 if you want to use control panel to change the licencing mode
- 2 if you want per processor licence

I have applied this changes to development environment and it hasn't affected anything.

Monday, November 28, 2005

Clustering SQL Server 2000

Brian Knight writes this step by step guide of clustering windows 2000 and SQL Server 2000.To read his guide click Step-by-Step Guide to Clustering Windows 2000 and SQL Server 2000

Thursday, November 24, 2005

The DBA Game

If you haven't already played this game you can find it on DBA Game

Wednesday, November 23, 2005

Why Upgrade to SQL Server 2005

For most of us SQL Server 2000 does what we wanted it to do. So, why upgrade to SQL SERVER 2005. The Jump from SQL Server 7 to 2000 is more of an incremental one. But, the jump from 2000 to 2005 will probably come as a shock to most DBA's/Developers who used to various interfaces and management tools. I will try to write various articles on this but for now I want to bring your attention to one of the paper that have been out this month on why upgrade to SQL Server 2005. Technical reviewers of this paper are Jonathan Tom and Rawee Kambhiranond. This paper summarises the benefits of upgrading to SQL Server 2005. Click Why Upgrade to SQL Server 2005 to view the full article.

House Keeping Part II

Is your entire database backed up?
I am sure most of you agree with me but don’t be surprised to find out that some of your database never backed up. I have come across this situation many times and have decided to put a simple script to tell me the backup status of all databases in one instance of SQL Server.

select cast(@@servername as varchar(15)) Server_Name,
cast(d.name as varchar(20)) Database_Name,
substring(suser_sname(d.sid), 1, 17) Database_Creator,
'Never Backed Up' Last_Backup_Date
from master..sysdatabases d
where d.name not in (select distinct database_name from msdb..backupset where server_name=@@servername)
and d.name not in ('tempdb')
union all
select cast(server_name as varchar(15)),
cast(database_name as varchar(20)),
substring(suser_sname(d.sid), 1, 17),
cast(max(backup_finish_date) as varchar(20))
from msdb..backupset b join master..sysdatabases d on b.database_name=d.name
where server_name=@@servername
group by Server_Name, Database_Name, d.sid



Please feel free to send me your comments.

Tuesday, November 22, 2005

Beware of Mixing Collations

Have you ever had problem with collations. I am not going to go into details of this problem. Some of us might have experienced this problem at one stage and wondered how to solve it. I found a nice article on this and would like to share with you. Collation problems and solutions

Drop merge articles

One of the dilemas that most DBA's face is problem with dropping merge articles without dropping the subscribers. Microsoft will recommend to drop the subscriber and then drop an article. I came accross were you would be able to trick SQL Server and drop artcles without dropping merge replication. In most cases it worked for me and I am sure it will work for you too. The following are the codes just to do this (I don't recommend using this on production server and please backup your database before running this):

use PublisherDBName
go
update sysmergepublications set snapshot_ready = 0
exec sp_dropmergearticle @publication = 'NameofPublication', @article = 'NameofArticle',@force_invalidate_snapshot = 0
update sysmergepublications set snapshot_ready = 1

Tuning SQL Server Hardware

Performance Tuning SQL Server Hardware
A freind of mine asked me if I know of an article that is used to tune SQL Server hardware. After googling and sufing the web I find out this artcle which is very useful and would like to share with you. You can view this article on http://www.sql-server-performance.com/hardware_tuning.asp. I use this site when I run into trouble. They have got so many useful tips on all aspects of performcace tuning.

Monday, November 21, 2005

COALESCE AND ISNULL

Using coalesce requires casting the data Type while isnull results in the same data type as underlying table

Using coalesce when there are null values in the column require you to cast the data type.

Create table sourceTable(TableId tinyint)

Insert into sourceTable(TableId)
SELECT 1 AS TableId
UNION
SELECT NULL


select COALESCE(tableId, 0)
into table1
from SourceTable


select ISNULL(tableId, 0)
into table2
from SourceTable


Table1 is created with Int data Type while Table2 is created with tinyint data type. I don’t know if this is by design.

Data Minining community

I would like to congratulate the team behind the SQLServerDataMining.com. They are doing fantastic job. If you need to know and practice data mining this is the place to be. I recommend anybody to start from this site Data Mining

Performance SQL Server 2005

Troubleshooting Performance Problems in SQL Server 2005
(SQL Server Technical Article by Sunil Agarwal, Boris Baryshnikov, Tom Davidson, Keith Elmore, Denzil Ribeiro, Juergen Thomas)

The launches of SQL Server 2005 will definately bring about the adoption of this fantastic DBMS. In light of this I came across step-by-step guidelines for diagnosing and troubleshooting performance problems using features available in SQL Server 2005. To download or view this technical article please visit Troubleshooting Performance Problems (SQL Server 2005)

Thursday, November 17, 2005

SQL House Keeping Part I

Tables without primary key constraints

I have started this series of house keeping. It is all about doing simple things that you never got time to do or it has been overlooked. I will start with a simple stuff and will progress to difficult one.

In this first series, I will try to find out database tables without primary key constraints. As you all agree, primary key is fundamental part of relation database. Without primary key a table wouldn’t be in even the first normal form. I am not going into detail of the database normalization process in this series.

If you got database with hundreds of tables, it is not easy to identify tables without primary key. It is all up to the DBA/Developer to find out the tables with no primary key constraints. It is not re-inventing the wheel but to use already existing tools in SQL Server. To do this you need to understand some of the system objects.

To identify the above problem, I used two system objects namely sysconstraints and sysobjects.

I have also included some of the attributes of the above objects.

Sysconstraints : - the system object has seven attributes of which two is reserved for internal use of SQL Server. Here we are interested in Id (Id of the table that owns the constraint.) column of the object.

Sysobjects : this table is a very useful system object that a DBA need to understand and is used extensively by DBA’s. To find out more about this object please refer to books online.But for this purpose I am only interested in xtype and type columns, which I have listed what the columns holds and the meaning of each of the columns.

Source of following column type is SQL Server books online.
Values that will be held in type column of sysobjects table are:

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
V = View
X = Extended stored procedure

Values that will be held in xtype of sysobjects table

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure



The following select statements will display all user-defined tables without primary key. A simple select statement based on above system tables provides us with the objects that we need to work on. See SQL Statement below.

select name
from sysobjects
where id not in (select b.id from sysconstraints b, sysobjects c
where c.xtype = 'PK' and
c.id = b.constid) and
type = 'U'
order by name

Now you know which of user tables in your database that haven’t got a primary key and you would be able to work through the list. I will assure you from my past experience you will definitely find user defined tables without primary keys. I have encountered this numerous times.

Wednesday, November 16, 2005

Top 10 new features

Jermey from Jeremy Kadlec, Edgewood Solutions has but top 10 features of SQL Server 2005. If you want see a brief overview top 10 features such as
· Transact-SQL enhancements
· Common language runtime
· Service broker
· Data encryption
· SMTP mail
· MARS
· DAC
· SSIS
· Data Mirroring
Click
Top 10 Features of SQL Server
In my next posting, I will add enhancements and new additions to replication technology.

Views and bit datatype

SQL Server 2000 views doesn't reflect the underlining data from linked server when the data type is changed from nvarchar to bit

In this article I am going to discuss the problem that I have encountered when creating a view based on a column with varchar data type and change the data type to bit.The problem happens when you try to query the veiw from a linked server. I have included scripts to replicate this issue for you to experiment. To configure a linked server please refer Configuring Linked Servers.

First run the following script to create a table with one of the column being defined as varchar data type.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TrueFalses]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TrueFalses]
GO

CREATE TABLE [dbo].[TrueFalses] (
[TrueFalseId] [int] IDENTITY (1, 1) NOT NULL ,
[TrueFalse] [nvarchar] (100) NULL
) ON [PRIMARY]
GO
Next insert data into the above table
INSERT INTO TrueFalses(TrueFalse)
SELECT 'True'
UNION all
SELECT 'False'
UNION all
SELECT 'True'
UNION all
SELECT 'True'
Next create view based on above table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[test]
CREATE view test
AS
select * from TrueFalses


Run the following two queries from a linked server

select * from [Linked ServerName].[Database Name].dbo.TrueFalses
select * from from [Linked ServerName].[Database Name].dbo.test

Both the above queries result in the same data.

TrueFalseId,TrueFalse
1,True
2,False
3,True
4,True


Now let us update the TrueFalses table.

UPDATE TrueFalses
SET TrueFalse = 0
WHERE TrueFalse = 'False'


UPDATE slst_TrueFalses
SET TrueFalse = 1
WHERE TrueFalse = 'True'

Again run the query based on table and view from linked server. You will get the same results with the value of TrueFalse changed to 1 and 0. See results below.

TrueFalseId,TrueFalse
1,1
2,0
3,1
4,1


Then change the data type of the field from varchar to bit data type and query the result from linked servers.

ALTER TABLE TrueFalses
ALTER COLUMN TrueFalse bit


The query from the linked server based on the table will yield the following result.
TrueFalseId,TrueFalse
1,1
2,0
3,1
4,1


The query from the linked server based on the view will yield the following result.
TrueFalseId,TrueFalse
1,True
2,False
3,True
4,True



The above behaviour can be corrected by running ALTER statement on a view.

alter view test
as

select * from TrueFalses