There were major changes in architecture & engine of SQL Server 2005 as compare with SQL Server 2000. So as per my observation and experience in live environment, many different kind of error used to come while upgrading, I don’t mean that upgrade doesn’t work at all but personally I have came across so many different kind of error so rather than upgrading from SQL Server 2000 to SQL Server 2005, I would like to follow different approach which has been given below.

1.)    Detach all use databases (don’t need to have system databases with us)

2.)    Back up all the job if you have any in your SQL Server 2000

3.)    Create login transfer script so that all logins of your SQL Server 2000 works fine in SQL Server 2005 with same passwords. There are few different approaches for this but I would like to point out one of the Microsoft’s link for this task. http://support.microsoft.com/kb/246133

4.)    Now, this is the time to uninstall SQL Server 2000

5.)    Install SQL Server 2005 and upgrade it with latest hot fixes and patches

6.)    Attach all your databases which were detached from SQL Server 2000

7.)    Restore all jobs which were backed up from SQL Server 2000

8.)    Run the transfer login script which we have generated in step 3 above.

Isn’t it simple to go for? I find this easiest way to go for rather than upgrading the server. This is my personal point of view which I used to follow. The main reason of writing this article is, yesterday one of my friend was upgrading his SQL Server 2000 to 2005 and came across one weird error which he was not able to solve. I suggested him to follow these steps in future and also suggested few steps which might resolve the error he was facing. I used to get update from him today, If the way I suggested had helped him to resolve the error, I will provide full case study in my blog so everybody can take advantage of the same.

Reference: Ritesh Shah               

People may face problem in selecting or deleting duplicate records from database. This is not a new problem; every SQL Developer may face this kind of situation at least once in his/her careers. Now a days, especially after SQL Server 2005, it is becoming really easy to find duplicate records and delete it with the help of CTE (Common Table Expression), basically CTE is introduced in SQL Server 2005 version so long way back in SQL Server 2000, there was no CTE and people were using logical trick to overcome this issues.

I have already written articles on that topic which I want to share with you today.

Click here to look at the trick which works in SQL Server 2000/2005/2008.

Click Here to look at the new method with CTE which should work in SQL Server 2005+ version only.

I see many of the .NET developer still now aware with these kinds of techniques and facing problem due to unawareness. This is the main reason I wrote follow up post for my past two articles.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Today I have been asked by someone that I am not able to copy MDF and LDF file from our production server.  Whenever I want to copy it, I have to stop SQL Service. This is very common problem and not everybody aware with why this happens? So I tempted to write something small about this.

If you are using your database, you are not able to copy the data or log files; you can do it without even stopping services of SQL with following small script.

 

ALTER DATABASE YourDatabase

SET OFFLINE WITH ROLLBACK IMMEDIATE;

--Now Manually copy your file(s) to anywhere you want

--and set your database online

--during this process your database will be offline

--It's not good but better than stoping SQL Server services

ALTER DATABASE YourDatabase

SET ONLINE;

Actually take a backup of database and move backup file anywhere could be good but even if you wish to copy MDF or/and LDF file, you can have above approach.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

As a DBA we may need to set the JOB for various task and those tasks will be saved under MSDB database. You can see it from EM or from SSMS GUI tools but it would help sometime to execute query to see the list of available JOB in SQL Server 2000+ versions.

There are two ways to achieve that task. Have a look at it.

--with stored procedure

EXEC MSDB..sp_HELP_JOB

--with sysjobs view

select * from msdb..sysjobs

Both of the above statements will give you list of JOB have been set in SQL Server Agent along with so many other important details.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

When I have moved my SQL Server 2000 database to SQL Server 2005 first time in past, I faced user and login synchronization problem. I want to share it with you as I have seen this question in forums many times.

When you move your database from SQL Server 2000 to SQL Server 2005, logins and users of SQL Server 2000 will be out of sync in SQL Server 2005. This is really very annoying. After digging I came to know what the problem is and I have developed one small script which is very useful and handy to make your login and user in sync back in your new server.

USE AdventureWorks --give your own database name which you have

--restored from SQL Server 2000 to SQL Server 2005

GO

DECLARE @UserName Nvarchar(50)

DECLARE c CURSOR DYNAMIC

FOR SELECT DISTINCT [Name] FROM [sysusers] WHERE [sysusers].[uid] < 16000 AND [Name] NOT IN ('guest', 'sa', 'dbo','public','sys','INFORMATION_SCHEMA') ORDER BY [Name]

OPEN c

FETCH NEXT FROM c INTO @UserName

WHILE @@FETCH_STATUS=0

BEGIN

EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL, @UserName

FETCH NEXT FROM c INTO @UserName

SELECT @UserName

END

CLOSE c

DEALLOCATE c

GO

Enjoy Upgrading!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Difference between Microsoft SQL-Server 2000 and Microsoft SQL-Server2005:

This question is one of the mostly asked question in an interview but let me tell you that in depth answer of this question could generate one complete book but let us see the difference here in brief.

Almost features of Microsoft SQL-Server 2000 are available in Microsoft SQL-Server 2005 with some improvement and enhancement along with some new feature. Let us see those in brief.

-- GUI improvement (SSMS –SQL Server Management Studio)
-- T-SQL enhancement
--CLR integration
--Table Partitioning
--Database Mirroring
--DDL trigger support

Along with “Database Mirroring” as a high availability in Microsoft SQL Server 2005 there are old feature of SQL Server 2000 are also available like replication, clustering and log shipping but it was working for only SQL-Server 2000’s enterprise edition but in Microsoft SQL Server 2005, it is available for standard edition also.

Scalability was limited to 2GB and 4CPU in standard edition of Microsoft SQL Server 2000 but in Microsoft SQL Server 2005, it supports 4 CPU and no RAM limitation in standard edition.

As long as encryption concern there was no built in option available in Microsoft SQL Server 2000, you have to have third party tool and expertise to use that tool while in Microsoft SQL Server 2005 it comes up with inbuilt encryption support.

Apart from that, MS SQL Server 2005 comes up with BI reporting tool as known as “YUKON”.

Reference: Microsoft Site