SQL Server 2005 Book - Microsoft SQL Server(TM) 2005 Implementation and Maintenance

Bought Microsoft SQL Server(TM) 2005 Implementation and Maintenance through Amazon.
I always like book from Solid Quality Learning. It gives good explanation and case studies.

This book gives overview of new function in SQL 2005. Example SQL mirroring, SQL Snapshot, XML, Partition, Service Brokers.

SQL Server 2005 Service Pack

SQL Server 2005 Service Pack 1
http://www.microsoft.com/downloads/details.aspx?familyid=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&displaylang=en

SQL Server 2005 Cumulative hotfix package (Build 2153)http://support.microsoft.com/kb/918222/
Accroding to the document,
- We must install each component package for your operating system
- We must enable the SQL Server Management Object (SMO) and SQL Server Distributed Management Object (SQL-DMO) extended stored procedures before You must enable the SQL Server Management Object (SMO) and SQL Server Distributed Management Object (SQL-DMO) extended stored procedures before you install the hotfix package install the hotfix package
- We must install all component packages in the order in which they are listed in this articleelse we may receive an error message.SQL Server 2005Microsoft SQL Server 2005 Analysis ServicesMicrosoft SQL Server 2005 Integration ServicesMicrosoft SQL Server 2005 Notification ServicesMicrosoft SQL Server 2005 Reporting ServicesSQL Server 2005 Tools

More information about SQL Server 2005 cumulative hotfix package

SQL Server 2005 migration

Lot of people ask how we going to migrate SQL Server 6.5, SQL Server 7, SQL Server 2000 to SQL Server 2005

If you still using SQL Server 6.5, you can't direct upgrade to SQL Server 2005. You will need to perform 2 step job. First update SQL Server either to SQL 7 or SQL 2000 and then upgrade to SQL 2005

If you using SQL Server 7.0 or SQL server 2000, you have have few method for migration
1. Side by side migration using Copy database wizard
2. Backup and Restore
3. Attached and detach
4. direct upgrade from SQL 2000 to SQL 2005 (Only applicable for SQL 2000)

SQL Server 2005 SSIS

SQL 2005, My first Microsoft SQL Server 2005 Integration Services (SSIS) explore with this sample
1. This SSIS will loop each file in the folder, Extract sample data from the file
2. define Lookup transformation to obtain value of current key and time key
3. Write data to destination file.

Control flow


Data flow


This SSIS exercise and detail explanation can get from "Creating a Simple ETL Package Tutorial" in microsoft website
You also can get Hand on Training for SSIS from microsoft.

SQL Sever 2005 Linked Server problem

I create linked server for sql Server 2000 32 bit in SQL Server 2005 64 bit.
When I try to run the query (select * from testserver.northwind.dbo.Customers) in SQL2005 it give me the below message :

OLE DB provider "SQLNCLI" for linked server "testserver" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "testserver" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "testserver". The provider supports the interface, but returns a failure code when it is used.

Check in google for SQL Server 2005 linked server problem and found this in microsoft website
You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server

CAUSE
This problem occurs because the system stored procedures were not upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4. You must manually upgrade the system stored procedures after you install SQL Server 2000 SP3 or SQL Server 2000 SP4.


RESOLUTION
To resolve this problem, install SQL Server 2000 SP3 or SQL Server 2000 SP4 on the 32-bit SQL Server 2000 server. Then, manually run the Instcat.sql script that is included with SQL Server 2000 SP3 or SP4 on the 32-bit SQL Server 2000 server.

more detail
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

SQL Server 2005 - Prepare for Migration

Prepare for SQL Server 2005 migration
1. backup SQL Server 2000 db and restore to localpc
2. fixed userid in the restore db
3. find Microsoft SQL Server 2005 Upgrade Advisor
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en

Upgrade Advisor
1. Start UPGRADE ADVISOR ANALYSIS WIZARD
We have two options:
a.Launch upgrade advisor analysis wizard
b.Launch upgrade advisor report viewer
We will select option a.

2. Select SQL server and which component wants to analysis :
SQL Server
Analysis Services
Notification Services
Reporting Services
Data Transformation Services

4. Select which authentication mode. Windows Authentication or SQL Authentication mode

5. Select which database want to analysis
We can save the analysis file to analyze trace file or to SQL batch file

6. Last Step, Upgrade Advisor will show summary of the server, component and database, which want to analysis
If previous upgrade advisor report exists, Upgrade advisor will be given warning "Previous upgrade advisor report exists and will be overwritten"
We can select whether want send reports to Microsoft

7. Upgrade advisor will start analysis the component

8. Wait for while depand how big is your database, Upgrade advisor will end with a analysis report

9. We able to filter report by server, Instance or component, Filter by issue (Pre-upgrade issue, migration issue, solve issue)

>More about SQL Server 2005 Upgrade advisor

You can find SQL Server 2005 Upgrade Handbook here

You also can get 350 page document contain how to move SQL SERVER 7 OR SQL SERVER 2000 to SQL SERVER 2005 here