Pass presentation SQL Server

Presentation by Bill Graziano,SQL Server MVP on 2005 pass community summit. I think this presentation is precise and go to the point of what you need. With the additional knowlege you got it can be used as a guide to database programming and administration. Worth viewing it. To view it click Pass presentation

SQL Server 2005 & SQLDMO

I have recently uninstalled SQL Server 2005 and got the following error while trying to run enterprise manager for SQL Server 2000. I was trying to find out why SQL Server 2005 will affect the installation of SQLDMO.dll file.

The error I was getting was :
SQLDMO has not been registered; please re-run SQL Server setup and contact system administrator.

The way to solve this issue is to register the dll rather than re-running SQL Server 2000 setup. First check that sqldmo.dll exists in binn dirctory of your SQL Server installation then run regsvr32 sqldmo.dll from command line.

SQL Server 2005 Roadshow

Have you missed the launch of SQL Server 2005 roadshow ? To register and attend SQL Server 2005 roadshow during first quarter of 2006 , go to Sql Server Europe

Database mirroring

When Microsoft first comes up with this idea, I was delighted with the concept of database mirroring. I thought it will provide bulletproof solution with minimal cost for setting up redundancy solution for small and medium size business who are the one struggling to make ends meet. But, when they have decided to disable on the release of SQL Server 2005, I was gutted. By default, database mirroring is disabled in the new release but you can still be able to enable using trace flag 1400 as a start up parameter. Microsoft does recommend not using database mirroring in production environment. It might be a case that Microsoft is not ready to release this feature, I do understand their thinking. If they are not confident then it is the right direction.

You can read data mirroring overview, SQL Server 2005 Editions that supports mirroing and various technical aspects of this technology by going to SQL Server 2005 Database mirroring

SQL Server 2005 Books Online

SQL Server 2005 Books Online is out and can be downloaded now. Download Books online

SQL Server 2005 Samples

SQL Server 2005 Samples and Sample Databases (December 2005) is out and you can down load from SQL Server samples (Dec 2005)

SQL Server 2005 for DBAs

Last time I mentioned about features of SQL Server 2005. That is a good start to get an idea of what SQL Server 2005's new features are. Today, I am going to point you to An Overview of SQL Server 2005 for the Database Administrator. You can find this on microsoft web site or click An Overview of SQL Server 2005 DBAs

Log shipping

Have come accross many times people asking about how perform log shipping. If you are running Enterprise edition, log shipping can be done through EM. The problem is always where to start. Content posted on www.sql-server-performance.com web site is best place to start. To view details SQL Server Log shipping

SQL Server 2005(After Launch)

I have compiled some of the sites that give you a comprehensive review of how SQL Server 2005 is received. The following links will help you explore different views from different angle.
- SQL Server 2005 News & Reviews
- Microsoft unwraps flagship database SQL Server 2005
- Database Wars: The Next
- Microsoft, SAP Team Up Against Oracle
- SQL Server 2005 piles it on
- SQL Server 2005 May Tempt Many to Jump Platform
- SQL Server 2005: I Said Be Cautious, Not Petrified

Are just few of the one which catched my eye. Please send me any one you know off and will add to the list.

SQL 2005 Feature Pack

Feature Pack for Microsoft SQL Server 2005 is out in November. The standalone packges includes:
- Latest versions of redistributable components for SQL Server 2005
- Latest versions of add-on providers for SQL Server 2005
- Latest versions of backward compatibility components for SQL Server 2005
To see detailed explanation and download click SQL 2005 Feature Pack

Sysprocess to Isolate Performance

Nice article by Terry Troisi on sql-server-performance.com on how to use sysprocess table in master database to isolate perfomance issues. To veiw more details click Sysprocess to Isolate Performance issues part-1

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.

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

The DBA Game

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

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.

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