SQL Server Storage Engine & Tools (SSQA.net)

SQL Server version has a known compatibility issues with Windows 7 RC. This warning is genuine and applies to all SQL Server editions, including all Express editions.

There is no need to guess about the rootcause of the above error message, as it clearly mentions about compatibility issues as a warning!

The above error was presented to me recently when I'm trying to install SQL Server 2005 & 2008 versions on my Windows 7 RC VM. The exclusive specificatin of this error is it included SQL Express editions too, as the  incompatibility was fixed in SQL Server 2008 Service Pack1 and SQL Server 2005 Service Pack 3.

 Without further thinking I simply hit 'Run Program' button, as it was the same case earlier that when you tried to install SQL Server 2000 on a Windows 2003 server edition. The usual practice (compulsory) is that once the setup has installed required compnents you must apply the latest service pack afterwards. Also I have seen that while installing SQL Server 2005 instance it has presented with this warning message multiple times during the installation process. As you may know that now Service Packs can be obtained using Windows Update program from the operating system, as this time I'm using a Virtual Machine that I have disallowed all sorts of networking connectivity to Internet so simply Downloaded SQL Server 2005 Service Pack 3 & Downloaded SQL Server 2008 Service Pack 1  which are the latest service packs.

Most of the MSDN blogs dictates to allow the service packs or any updates to be applied using  Windows Update, where you can do that by clicking "Get updates for more Microsoft products" to start obtaining updates for SQL Server. SQL Server service packs should show up on the list of optional updates. Whenever any new updates are available there will be a small icon flashing on bottom-right corner of your screen stating 'New Updates are available' and what I do is click on that icon to see "Check for updates" link on the left pane of main Windows Update window. As it is your system you must know what kind of updates are applied, that way you can control the installations too.

Then coming back to the SQL Express edition that I have installed in addition to 2005 & 2008 DB engine instances, so I have installed 2 additional instances of SQL Server 2005 Express and SQL Server 2008 Express (Runtime Only) by Downloading SQL Server 2005 Express Advanced Edition SP3 and SQL Server 2008 Express SP1, as you can assured that they have upto date service packaged, saves you the time of applying service packs to any existing SQL Express edition from your software library source. Microsoft has the practice to repackage all the SQL Express editions with latest service packs in order to ensure they are not missed as it is free to use.

Finally I would like to refer the one of the best post from Peter Saddow about Creating a merged (slipstreamed) drop containing SQL Server 2008 RTM + Service Pack 1 that will slipstream the original source media and SQL Server 2008 Service Pack 1. Once you have created this drop, you can install SQL Server 2008 SP1 in a single step!

SQL Server error: Msg 6101, Level 16, State 1, Line 1 Process ID -2 is not a valid process ID. Choose a number between 1 and 2048

Here is the quirky error that has caused a resources crunch on a SQL instance, Error : Process ID -2 is not a valid process ID.

The actual issue occurred during an update to a table that has been used by Online application to update the action log, intially the DBAs have thought it is a blocking problem and trying to catch up the SPID to kill with no success. As the existence of that process was on a minimum time and hard to catch hold, also another practice is to refer to SQL Server error log to see any DEADLOCK log is printed.

So the practice of inbuilt TSQL to catch orphaned transactions and the above problem has been occuring on regular basis causing more issues to the shared SQL Instance that is important to the business. TO catch up running SP_WHO2 identified that SPID as -2 causing a blocking chain with a dozen other transactions behind it trying to acquire resource. The obvious practice is to kill that SPID which is causing such a mayhem, but if you try to do so you will get specified on the subject line. Unless there is a big problem I wouldn't recommend to kill a SPID which might cause additional problems, when it is performing ROLLBACK operations and in this case say if you are trying to delete x 100 of rows on a busy table!

Ok let us go further in killing this issue then here is the fix the problem on killing this negative SPID, not that easy. One of the DBA suggested to restart the SQL Server services that will wipe this blocking and negative SPID, you bet it will not as it is treated as open transaction and further issues to recover the databases when you have a shared server platform, not a good practice at all.

In order to catch hold of that SPID from the system tables you could run the following TSQL:

select req_transactionUOW  from master..syslockinfo where req_spid = -2

As it is observed the req_transactionUOW is the new column, as per the BOL "Identifies the Unit of Work ID (UOW) of the DTC transaction. For non-MS DTC transactions, UOW is set to 0". Ok, a new indication of option here which is MSDTC, a bit of background on the topic is All distributed transactions not associated with a session are orphaned transactions. The Database Engine assigns all orphaned distributed transactions the SPID value of -2, which makes it easier for a user to identify blocking distributed transactions. I will cover later about how to resolve these inconsistent distributed transactions and coming back to resolve this negative SPID which is classed asorphan distributed transactions which are not associated with any real SPID.

Identify the Unit Of Work (UOW) for orphan distributed transaction :

use master
go
select req_transactionUOW from syslockinfo where req_spid = -2
go

Use KILL 'UOW' to terminate orphaned DTC transactions

KILL 'FE4A57F2-28C5-44F9-8416-B08760DFE7E9'

Once the KILL process completes successfully then you can be assured by referring to SQL error log for following text:

Spid 60 tried to abort the distributed transaction with UOW {FE4A57F2-28C5-44F9-8416-B08760DFE7E9}.

Confirm there no more locks held by -2 by running below in Query Analyzer, sp_lock -2. Ok now the actual process to see whether any distributed transactions are involved within your setup, if not you have to look at the SPID process that is running any TSQL/Stored procedure to optimize for further occurrence on the instance. Not all environments will have MSDTC service usage, but say when any transaction involves data which resides on more than one server, such as when a database record is replicated out to two or more servers, MSDTC needs to become involved where the SQL engine manages it efficiently. Also if such distributed transactions are involved between the servers then any network issue might trigger the inconsistency on SQL creating such a mayhem.

Now coming back to handling the resolution for  the inconsistent distributed transactions, SQL Server BOL has the topic about "Troubleshooting MS DTC Transactions". Also I have seen sometimes that restart of Distributed Transaction Co-ordinator (MSDTC) service might resolve the issue, but not in all the cases. The below steps should avoid the issue:

1.  Type mmc.exe in the Run dialog box to open Microsoft Management Console (MMC).
2.  In MMC, on the Console menu, click Add/Remove Snap-in .
3.  In the Add/Remove Snap-in dialog box, click Add .
4.  In the Add Standalone Snap-in dialog box, click Component Services , and then click Add to add the snap-in.
5.  Click Close .
6.  In the Add/Remove Snap-in dialog box, click OK .
7.  In the console root under Component Services , expand Component Services , point to Computers , point to My Computer , and then click Distributed Transaction Coordinator .
8.  Select Transaction List . You see transactions in the details pane. Right-click the transaction, and then click Resolve . You now have three options: Commit , Abort , or Forget . Use the one is appropriate for your situation.
Windows NT
1.  Click Start , click Run , type dac.exe , and then press enter. The MS DTC Administrative Console opens.
2.  On the Transactions tab, right-click the transaction, and then click Resolve . You now have three options: Commit , Abort , or Forget . Use the one is appropriate for your situation.

SQL Server Upgrade issues - How to evaluate potential issues?

During the end of March 2009 I have presented a session "Upgrading To SQL Server 2005 & 2008: Notes & Best Practices" in SQLBits IV conference at Manchester. The session covered a brief tour on SQL Server Upgrade options, methodology, tools and planning, technical and non-technical considerations along with common issues, myths and mistakes. Also the coverage towards the exhaustive list of all upgrade issues with an end-to-end coverage or in-depth drilldown of all SQL Server 2005 & 2008 features. Equally the feedback was very high & good in terms of number of people commented on the specific aspects of this presentation, a good topic and coverage of tools & issues resolution. On specific to the feedback again last month I have had further questions about how to evaluate potential issues during the upgrade and here is blog post that I want to give a brief tour on the subject.

As I have referred on the presentation slides  the SQL Server 2008 Upgrade technical reference guide from Microsoft is a great help to get to know the subject very well. As per the reference say whether  you choose an in-place upgrade or a side-by-side upgrade of a relational database, there are a range potential issues that you might face during the upgrade. The first and foremost option you can choose is to run the SQL Server 2008 Upgrade Advisor no matter the size of database is involved here and that will get you a detailed report that identifies many of these potential issues. The advantage that you can get running this tool is nothing but a preview of problems that might occur during the upgrade and few of them might crop up at the later stages, whereby it is impossible to ROLLBACK or get back to previous state of the system.

So when you have got the report in front of you most of the warnings & errors are reported due to the deprecated and discontinued functionality, changes that might prevent an upgrade,  and changes in feature behavior that might require modifications as per the new features within SQL Server 2008. By default you can obtain information on  Backward Compatibility in SQL Server 2008 Books Online (updated). A doubt that was raised by one user during the conference that what is backward compatibility & discontinued features, several features from earlier versions of the SQL Server Database Engine are not supported in SQL Server 2008, so you must use replacement features for these.  Backward compatibility with earlier versions of SQL Server was a high priority in SQL Server 2008, so in most cases, applications will behave as in the past. A sample list of such information here:

For more ease and in-depth of information about discontinued functionality in SQL Server 2005 and SQL Server 2008, see the following topics in SQL Server 2008 Books Online:  Discontinued Database Engine Functionality in SQL Server 2005 &  Discontinued Database Engine Functionality in SQL Server 2008

Also I have seen that few settings within the SQL Server 2008 setup program will prevent from starting the upgrade process for the Database Engine. If one of the following issues is encountered, the upgrade process will stop, and the legacy system will remain in place:

Also further list of table within the Upgrade technical reference guide is:

Adhoc solution to perform SQL Server database maintenance tasks - more features

I had been receiving a good feedback about the solution that has been posted previous about  Script to perform database backup, restore and optimization tasks without maintenance plans - SQL Server 2008 and inline link for SQL Server 2005 too. In fact I have tested the scripts thoroughly before posting them to the community,as it is another good practice to make sure that you are not simply passing the buck.

Further to this I had an opportunity to meet  Ola Hallengren, author of this so called adhoc solution to the DBA world, I'm amazed about his passion in helping the community by the way of sharing the knowledge too from his own experiences.

So what's new about another new post on the similar solution?

Well, the new version of the solution will have the support XML Indexes, SPATIAL Indexes (SQL 2008), Indexed views and CLR types & indexes too. Not only that the latest feature about 'disabling page locking'. A brief note about lock resource types that are performed in specific to the action whenever the task of reindex is performed. If you have complete knowledge about your database, then you must have control to change the lock resource types that can be locked for a specific index.

As you may be aware that there are various types of lock types and resources within SQL Server from small (row and key locks) to medium (page locks, extent) to large (table & database). Not in specific to the type of lock resource it is more important for you to know about multiple locks are helpful in maintaining the query concurrency which works as smaller locks at that point of time. So the locking is default whenever multiple connections are attached to the database, numerous locks take up memory that can lower the performance and you can control this behaviour on that instance as whole by performing the adhoc script based solution.

Whenever such a question is posted on forums/newsgroups I see many suggets to increase the memory on SQL Server or add more physical memory! Well that is not a final solution that you don't know, as and when data grows your problem will also multiply on similar lines by causing larger-gained locks which increases memory resource availability but also reduce query concurrency leading to DEADLOCK situation. So if you are in control of managing that behavious by createing an index that restricts certain locking types when it is queried, so as to designate the SQL to apply whether a page or row lock.

One of the best practice dictates that by default you should allow SQL Server to automatically decide which locking type is best. But in my experience I say that if you are in total control of your database then you do have such an advantage of control the lock resources on the system, think about when it is a shared SQL instance that can bring down the instance due to a SELECTIVITY of rows from a connection. Also it is helpful on the situation that where bou wish to temporarily restrict certain resource locking types for troubleshooting a sever performance issue. To talk about syntax in configuring these options are available in CREATE INDEX and ALTER INDEX:

WITH (ALLOW_ROW_LOCKS = {ON | OFF} | ALLOW_PAGE_LOCKS = {ON | OFF}

To emphasize the same in a code example here is the extract from BOL on how to disable the database engine's ability to apply row or page locks on an index and forcing to use table locking instead!

--Disable page locks, but table and row locks can still be used

CREATE INDEX NC_EmpHistory_Pay ON HR.EmplPayHistory (Tarrif) WITH (ALLOW_PAGE_LOCKS=OFF}

--DIsable page and row locks, but still table locks can be used

ALTER INDEX NC_EmpHistory_Pay ON HR.EmplPayHistory SET (ALLOW_PAGE_LOCKS=OFF, ALLOW_ROW_LOCKS=OFF)

--Now allow page and row locks only

ALTER INDEX NC_EmpHistory_Pay ON HR.EmplPayHistory SET (ALLOW_PAGE_LOCKS=ON, ALLOW_ROW_LOCKS=ON)

A final note on the above code is to apply when removing locking options should only be done if you have a good reason to do so, which means you have a certain activity that causes too many rows on the instance that can dry up the memory resources. SO having the idea of instead of row locks, you may wish to have SQL Server's use of larger-grained page or table locks instead that can finish that process to complete as quick as possible unless you are in total control of the database!

To wrap up make sure to look at SQL Server 2008 feature of filtered indexes feature that is able to compress data at page and row level! The filtered index feature allows you to create an index and associated statistics for a subset of values. So if any query that is executed the smaller percentage of valus within a column that is involved on that index then only target rows are resulted with a reduction of overall index size compard to a full table index that can improve the accuracy of underlying statistics and return of rows quickly. To download the script refer to http://blog.ola.hallengren.com/_attachments/3440068/MaintenanceSolution.sql link & if you have a doubts or clarification need then please feel free to contact here.

Do you know how many Monitoring Tools and Resources available in SQL Server?

Monitoring Tools and Resources are essential for performance tuning & better manageability in SQL Server.

Many times the users think that collection and analysis of performance data is not a day-to-day job, also I feel that it is difficult at best to understand and correct the items in a timely manner. There is no doubt that there are many herds of monitoring tools for SQL Server, but they will be effective only if you use them wisely and define the requirement to meet the needs for purpose. The handful of tools from Microsoft includes Profiler, Sysmon\Perfmon and the Database Engine Tuning Advisor\Index Tuning Wizard. 

Similarly the primary monitoring tools you will use for SQL Server are the Reliability and Performance Monitor and the SQL Server Profiler. But there are other resources also available for monitoring SQL Server. As we talked about tools these resources (aka hidden gems) from SQL Server Management Studio (SSMS) and BI Development Studio (BIDS) will leverage the performance monitoring data analysis. Say if you do not have performance problems then it doesn't mean than you need not to collect the monitoring data. As the problem will occur from adding more users or applications to your SQL Server, turn to these products as a means to improve the overall ROI on your platform. When we talk about third party tools then most of the products will have trial versions that can help you to test the product to see whether they meet your needs on building the business application support. As a best practice it is better to mix and match these tools along with freely available Microsoft related tools.

So coming back to the resources that are available for free using SSMS are as follows (these are listed as per my favourites):

• Activity Monitor
  Do you know that Activity Monitor was available back in SQL Server 2000 days too!

Activity Monitor to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server. This is similar to Task Manager for Windows that provides overview displays of the percent of processor time, number of waiting tasks, database I/O (megabytes per second), and batch requests (number per second), which is shown with graphical representation. Also the Active User Tasks, Resource waits, Data file I/O and recent expensive queries. In order to get information using Activity Monitor then the user must have VIEW SERVER STATE permissions. The shortcut key to open the Activity Monitor from Management Studio you can use CTRL+ALT A and from object explorer right-click the instance name, and then select Activity Monitor.

You can set  the Activity Monitor refresh interval by configuring how often Activity Monitor obtains new information about instance activity. Activity Monitor runs queries on the monitored instance to obtain information for the Activity Monitor display panes. When the refresh interval is set to less than 10 seconds, the time that is used to run these queries can affect server performance. To set the value, right-click Overview, select Refresh Interval, and then select the interval in which Activity Monitor should obtain new instance information.

• Job Activity Monitor

Using SQL Server Management Studio (SSMS) you can monitor the current activity of all defined jobs on an instance of SQL Server by using SQL Server Agent Job Activity Monitor. Back in SQL Server 2000 days such an activity can only obtained by querying sysjobhistory system table and now similarly SQL Server Agent creates a new session each time the service starts. When a new session is created, the sysjobactivity table in the msdb database is populated with all the existing defined jobs, and this table preserves the last activity for jobs when SQL Server Agent is restarted. Each session records SQL Server Agent normal job activity from the start of the job to its finish. Information about these sessions is stored in the syssessions table of the msdb database.

Similar activity can be monitored using TSQL for the current session by using the stored procedure sp_help_jobactivity.

• Database Mirroring Monitor 

You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing. To set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the sp_dbmmonitor system stored procedures.

A database mirroring monitoring job, Database Mirroring Monitor Job, operates in the background, independently of Database Mirroring Monitor. SQL Server Agent calls Database Mirroring Monitor Job at regular intervals, the default is once a minute, and the job calls a stored procedure that updates mirroring status. If you use SQL Server Management Studio to start a mirroring session, Database Mirroring Monitor Job is created automatically. However, if you only use ALTER DATABASE <database_name> SET PARTNER to start mirroring, you must create the job by running a stored procedure.

• Replication Monitor This monitor provides details on the status of SQL Server replication and allows you to configure replication alerts. To display Replication Monitor, use the Object Explorer view to access an instance of the Database Engine. Right-click the Replication node and then select Launch Replication Monitor. 

• SQL Profiler or SQL Trace

I believe no introduction is needed for SQL Server Profiler that tracks engine process events, such as the start of a batch or a transaction, enabling you to monitor server and database activity (for example, deadlocks, fatal errors, or login activity). You can capture SQL Server Profiler data to a SQL Server table or a file for later analysis, and you can also replay the events captured on SQL Server step by step, to see exactly what happened.

• SQL Server & Windows logs

Monitoring the logs is also another tool to provide you the lead to see the activity on the SQL Server, the information in these event logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server problems. To access the server logs, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the Management node. Within that node expand the SQL Server Logs node and then double-click the log you want to examine.

In addition to the SQL Server error log Windows application/system/security event log provides an overall picture of events occurring on the Windows Server and Windows operating systems as a whole, as well as events in SQL Server, SQL Server Agent, and full-text search. It contains information about events in SQL Server that is not available elsewhere. You can use the information in the error log to troubleshoot SQL Server-related problems.

SQL Server Agent logs The information in these event logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server Agent problems. To access agent logs, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the SQL Server Agent node. Under the SQL Server Agent node, expand the Error Logs node and then double-click the log you want to examine.

I always refer the users to look at SQLAgent job log whenever a question is posted about job failure and these will allow you to troubleshoot systemwide problems, including SQL Server and SQL Server Agent problems.

• Dynamic Management Views (DMVs)

One of the best and favourite ones for me to get server state, DMVs & DMFs return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of dynamic management views and functions: Server-scoped dynamic management views and functions that requires VIEW SERVER STATE permission on the server. Database-scoped dynamic management views and functions that requires VIEW DATABASE STATE permission on the database.

All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema. Here is another magic from BOL to define more about Dynamic management views and functions that have been organized into the following categories.

Change Data Capture Related Dynamic Management Views	Query Notifications Related Dynamic Management Views
Common Language Runtime Related Dynamic Management Views	Replication Related Dynamic Management Views
Database Mirroring Related Dynamic Management Views	Resource Governor Dynamic Management Views
Database Related Dynamic Management Views	Service Broker Related Dynamic Management Views
Execution Related Dynamic Management Views and Functions	SQL Server Extended Events Dynamic Management Views
Full-Text Search Related Dynamic Management Views	SQL Server Operating System Related Dynamic Management Views
Index Related Dynamic Management Views and Functions	Transaction Related Dynamic Management Views and Functions
I/O Related Dynamic Management Views and Functions	Security Related Dynamic Management Views
Object Related Dynamic Management Views and Functions

Note: Over a period of time the schemas and data returned by DMVs may change in the future releases of SQL Server, such as the DMVs that are new in SQL Server 2008 will not work or return data for 2005 instances.

 

• DataBase Console Command (DBCC) statements

Commonly used statements by every DBA and many times the users whenever a problem is repoted, these are grouped into 4 categories: Maintenance, Informational, Validation and Miscellaneous.

BOL has got vast information on these DBCC statements to refer and few of them are as follows:

Maintenance

DBCC CLEANTABLE	DBCC INDEXDEFRAG
DBCC DBREINDEX	DBCC SHRINKDATABASE
DBCC DROPCLEANBUFFERS	DBCC SHRINKFILE
DBCC FREEPROCCACHE	DBCC UPDATEUSAGE

 

Informational

 

DBCC INPUTBUFFER	DBCC SHOWCONTIG
DBCC OPENTRAN	DBCC SQLPERF
DBCC OUTPUTBUFFER	DBCC TRACESTATUS
DBCC PROCCACHE	DBCC USEROPTIONS
DBCC SHOW_STATISTICS

Validational

 

DBCC CHECKALLOC	DBCC CHECKFILEGROUP
DBCC CHECKCATALOG	DBCC CHECKIDENT
DBCC CHECKCONSTRAINTS	DBCC CHECKTABLE
DBCC CHECKDB

Miscellaneous

 

DBCC dllname (FREE)	DBCC HELP
DBCC FREESESSIONCACHE	DBCC TRACEOFF
DBCC FREESYSTEMCACHE	DBCC TRACEON

A note about context of these DBCC statements that when these are executed then DB engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped. Also to workout such actions sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.

DMV sys.dm_exec_requests catalog view contains information about the progress and the current phase of execution of the DBCC CHECKDB, CHECKFILEGROUP, and CHECKTABLE commands. The percent_complete column indicates the percentage complete of the command, and the command column reports the current phase of the execution of the command.

• System & DB engine Stored Procedures (master database)

For many administrative and informational purpose the stored procedures that consists in SQL Server system database 'master'. Few of these are listed below that are commonly used in day-to-day monitoring activity, such as sp_helpdb, sp_helpserver, sp_spaceused and sp_who or sp_who2 (undocumented). There are many more system and DB engine related stored procedures that are used for general maintenance of an instance of SQL Server.

As of now there are many more to refer on tools & resources, so I will continue in upcoming blog posts and this is a good start to bring such a good availability of tools in SQL Server arena

SQL Server Error Msg:911 Could not locate entry in sysdatabases for database 'mssqlsystemresource'.

The error on the subject line is not a good sign when you have the mammoth task of moving SQL Server system databases, I would agree that the task of system databases migration is not a day-to-day task and you must have practised the required steps.

For the sake of clarifications the complete error messages will be:

Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'mssqlsystemresource'.
No entry found with that name. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'mssqlsystemresource'.
No entry found with that name. Make sure that the name is entered correctly.

By default if you have followed the tasks as per the steps outlined in the BOL for moving system databases such as using ALTER DATABASE statement or attempt to start SQL services with trace flag -T3608, it will work for MASTER database and trouble may start with RESOURCE databases on following steps:

ALTER DATABASE mssqlsystemresource
    MODIFY FILE (NAME=data, FILENAME= D:\Data\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
    MODIFY FILE (NAME=log, FILENAME= D:Data\mssqlsystemresource.ldf');
GO

I would like to give extra information on resource databases which were introducted from 2005 version onwards, from BOL:

The Resource database depends on the location of the master database. The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf). Therefore, if you move the master database, you must also move the Resource database to the same location as the master data file.

You may have additional error messages/issues if the client tools & network protocols for SQL Server 2005 or 2008 version do not match with the source server, you will be presented with following error:

HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

As per the error text you may think it is network related error due to the Login Timeout expired, in my experience I have seen this will occur if the network protocols such as TCP/IP or Named Pipes  or Shared Memory are not enabled or do not match between source & destination servers within this databases migration task.

On the similar lines of this issue I have been presented with a question while I was in Tech-Ed India community lounge discussing user problems for solutions, so here is what I have outlined on the steps to move a resource database:

Step 1:

• Stop SQL server services
• Start SQL server Service in minimal/maintenance mode using the command line option -m or -f and include a trace flag -T3608 (this is used to skip recovery of any databases except Master database, so that resource database is not used).
• Note:In case Resource database is corrupt so server would not start, and copying over correct version (more detail later) of resource database files does not address the issue, possibly due to disk/media failure.

Step 2:

• Once the steps followed as per Step1 above, now change the location of data and log files as shown below:

ALTER DATABASE MSsqlsystemresource MODIFY FILE (NAME=data, FILENAME= ‘New_Location\mssqlsystemresource.mdf’)
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= ‘New_Location\mssqlsystemresource.ldf’)

Step 3:

• After the Step2 execution, the next step and the last step is to restart SQL Server services.
• This should complete the process by getting the SQL Server services enabling the Resource database files will now be used from the new location.

Few additional questions posed by that user are as follows, what to do when one of resource database is corrupt?

This is where the DBA tactics of backup strategy should work, you must COPY the files from the backups, the reason I have highlighted the files is resource databases are not like usual system or user databases and you cannot perform backup/restore using SQL Native methods. So the best practice is to perform FILE level backup for resource databases. Tip: you can perform file backup of the resource databases as and when system configuration changes have been performed such as hotfix or service pack installation or major changes to memory/cpu configuration on the relevant SQL Server instances.

To close down the topic here is the excellent reference by fellow MVP Jonathan Kehayias talking about Error: 945, Severity: 14, State: 2 | Database 'mssqlsystemresource' cannot be opened error and how to resolve it.

SQL Server Edition - which is the best one to choose for your database needs?

When I look at the question, I feel that no direct answer to it!

The reason being SQL Server has outgrown into an important enterprise RDBMS product from a simple desktop based database application, but still maintains the ease of installation and use. This is what I hear from the Users & Professionals whenever I engage a talk about SQL Server, but what misses is how best to configure and keep up the optimum performance of your database application.

Recently, for me it has been a great 3 days of interaction with many IT professionals and users in Hyderabad in attending the Microsoft's Tech-Ed 2009 conference in India. In fact it was a first technical conference for me to attend in my hometown that too as a speaker to deliver SQL subject session, well the topic is getting diverted which I will discuss these ones on a seperate blog,

Coming back to the subject line, as a DBA/Developer/Designer/Architect what is the best edition will you choose for SQL Server?  I have been referred with similar lines of questions within Tech-Ed conference such as 'Can anyone point me to where I can find a list of differences in Server 2008 Standard versus Enterprise?" The favourble position for me is about the version as the user was particulary interested in differences that pertain to SQL Server installs and usual answer that was obtained was when they find a Server 2008 edition as STANDARD and enquired about Operating System team why they haven't chosen/favour ENTERPRISE instead, the reply was "the only difference is that enterprise allows for clustering", so this was a big question and dilemma for that user asking me is Clustering is only feature that differs from Enterprise to Standard?

As you may agree with me it is not and everytime I suggest and refer to look at SQL Server 2008: Compare Enterprise vs. Standard and you may agree with me the list that link is exhaustive and endless when you look at the features that SQL Server can offer to keepup the application's Scalability, Availablity & Performance! To continue and educate that user who has raised the big question about feature comparison on the editions of SQL Server I have enquired what is most important feature that application needs/required. I have chalked out the following list of features based on the feedback that I have receieved: 

• The Application Code is optimized enough and database has been normalized as per the best practices, but still the previous version (SQL 2005 Enterprise Edition) Server suffers the performance inspite of adding few more resources to the hardware (memory & disks). So my answer was to take help of parallel index operations from Enterprise Edition as it doesn't affect the performance on CPU and as per the BOL: SQL Server uses the same algorithms to determine the degree of parallelism (the total number of separate threads to run) for index operations as it does for other queries. The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements. The reason to chose this option is whenever query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-threaded operations on computers that have multiple microprocessors.Further I suggest to see this Parallel-index-configure article for more information.
• The data growth is expected to be huge (atleast 80%) in next 3 years that may go upto 2.5 TB (Terabytes) and very little chance for data archival processes, so the my option was to go about table & index partitioning. In the previous versions taking such help by indexed views by tying partitions together involved creating a partitioned view or a wrapper stored procedure that figured out where the data lived and executed another stored procedure to hit only the partitions needed to return the dataset. So talking about the edition feature only Enterprise and Developer editions of SQL Server (from 2005 onwards) lets you partition large amounts of data contained in a single table into multiple smaller partitions that can be managed and maintained more effectively. As the application is based for production usage Developer edition is not an option at all. So to have such an ability to create data segments that are accessed through a single point-of-entry reduces many of the administration issues that came with the old way of doing things. Using the table/index partition with a single point of entry (table name or index name) hides the multiple data segments from the application code and allows the administrator or developer to change the partitions as necessary without having to adjust the code base.
• Next biggest threat to the application data is configuring the size of data file, as the increase of data varies from day to day and choosing default value of 10% is not sufficient in some cases causing error to the application. What I have suggested is to compare the size of data files on that database on regular basis (in some cases every 4 hours to see any difference), if not every 8 hours or so, by taking care of such baseline and benchmarking procedures it will help to define a nominal value to the data file in order to optimize the sizes efficiently by controlling the capacity with an altering mechanism.
• Further the user was interested to get some tips to monitor the performance over a period of time on system using TSQL or GUI, as the answer was to continue configurating and running the Management Data Warehouse that was one of the best feature in SQL 2008 and Performance Studio.

So the first 2 features that I have opted was from Enterprise edition and to configure the Management Data Warehouse you could use Developer edition too.

To continue this dialogue of tips and tricks to the user I will post more on the topic such as best practices on federated databases, data partitioning and user-schema best practices questions that were raised by the users at this conference, please keep watching this space.

Error 7302 Could not create an instance of OLE DB provider When Creating a Linked Server in SQL Server

When you are using Linked Server to connect to other OLE DB provider and proper configuration is not set then you will see the above error.

A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages:

• Remote server access.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly.

OLE DB Provider is the DLL (system object in Operating System) that manages which interacts with specified data source. The identification of specific database from source to destination can be accessed through the OLE DB provider, the data sources queried through linked server definitions are ordinarily databases. OLE DB providers exist for a variety of files and file formats, such as text files, spreadsheet data, and the results of full-text content searches.

To continue in resolving the error you will need to check whther AllowInProcess option has been enabled, this is required because SQL engine passes the required authentication across the remote procedure call only when OLE DB provider is configured with such an option. All the processes even OLE DB provider activities will need to instantiated as in-process server, the default behaviour of provider outside of SQL Server cannot be controlled and authentication is required for handling specific types of data including long columns, text, and image data. The OLE DB Provider for DB2 does not currently support the DB2 Large Object (LOB) types. In my experience I have seen that AllowInProcess option is not required for SQL Server 2000 version. To check whether it is enabled or you can check/add the linked server provider option under [SQL Server --> Database -> Server Objects -> Linked Servers -> Providers -> Right-click on a provider, and select Properties. Finally, check the Allow InProcess option to enable the property.

When using SQL Server 2005 then such option can be check by referring to http://msdn2.microsoft.com/en-us/library/ms943674.aspx article. As per the BOL reference, when you set a non-zero value in AllowInProcess option then SQL Server allows for the provider to be instantiated as an in-process server. If you setup this value then the registry on that server will be updated instantly. So as per the checkout of clustered instances (such as other nodes) when this option is not set in the registry, the default behavior is to instantiate the provider outside the SQL Server process. By default the SQL Server Native Client OLE DB provider cannot be instantiated out of process. An error is raised if you set the SQL Server Native Client OLE DB provider to run out of process and try to run a distributed query.

To wrap up the security of linked server option needs to be checked, linked server login mapping establishes a remote login and remote password for a specified linked server and local login. When SQL Server connects to a linked server to execute a distributed query or a stored procedure, SQL Server looks for any login mappings for the current login that is executing the query or the procedure. If there is a login mapping, SQL Server sends the corresponding remote login and password when it connects to the linked server. If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, self-mapping will not work for logins that use Windows Authentication. Therefore, you must set up a local login mapping from a login that uses Windows Authentication to a specific login on the linked server that is not a Windows authenticated login. In this case, the remote login uses SQL Server Authentication if the linked server is an instance of SQL Server.

It is Cumulative Update #2 for SQL Server 2005 Service Pack 3

Here is the time again for all (interested) DBAs & Admins to fold their sleeves again for further testing on Cumulative Update #2 for SQL Server 2005 SP3.

As you know SQL Server 2005 SP3 is released, so as the Cumulative Updates are back on road for your testing to keep the system up to date with update packages for SQL Server 2005 systems (latest pack of SP3). With this update package the following hotfixes are included (source:http://support.microsoft.com/kb/961930):

For more information about the SQL Server bugs, click the following article numbers to view the articles in the Microsoft Knowledge Base:

Collapse this tableExpand this table

VSTS bug number	KB article number	Description
266290	960971  (http://support.microsoft.com/kb/960971/ )	FIX: Error message when you run some Multidimensional Expressions (MDX) queries that reference some calculated members in many connections at the same time in SQL Server 2005 Analysis Services: "An unexpected error occurred"
266298	960977  (http://support.microsoft.com/kb/960977/ )	FIX: Error message when you run a Multidimensional Expressions (MDX) query that contains a calculation in SQL Server 2005 Analysis Services: "An unexpected exception occurred"
271683	961146  (http://support.microsoft.com/kb/961146/ )	FIX: You cannot connect to an instance of SQL Server 2005 Analysis Services through HTTP when you configure Internet Explorer to use a .pac file
263148	961479  (http://support.microsoft.com/kb/961479/ )	FIX: An unresolved deadlock occurs when you perform an online reindex operation that runs on parallel threads in SQL Server 2005
273948	961648  (http://support.microsoft.com/kb/961648/ )	FIX: You receive an error message when you try to query or update a column of large object (LOB) data in SQL Server 2005
271942	961920  (http://support.microsoft.com/kb/961920/ )	FIX: A syntax error occurs when you try to use the lift chart to validate a mining model that uses an Oracle data source in SQL Server 2005 Analysis Services
271563	961935  (http://support.microsoft.com/kb/961935/ )	FIX: A query notification is not fired correctly if the query contains inner joins and the IS NULL clause in SQL Server 2005
263205	962003  (http://support.microsoft.com/kb/962003/ )	FIX: Error message when you synchronize data for a SQL Server Compact Edition subscriber in SQL Server 2005: "A call to SQL Server Reconciler failed. Try to resynchronize. HRESULT 0x80004005 (29006)"
271522	962209  (http://support.microsoft.com/kb/962209/ )	FIX: Error message when you run an UPDATE statement on a database that has the SNAPSHOT or READ COMMITTED SNAPSHOT isolation level enabled in SQL Server 2005: "The Database ID <Database ID>, Page (<N>:<N>), slot <N> for LOB data type node does not exist"
274802	963684  (http://support.microsoft.com/kb/963684/ )	FIX: The execution plan does not use the forced index when you use the INDEX optimizer hint in a query that runs against a synonym in SQL Server 2005
275647	967164  (http://support.microsoft.com/kb/967164/ )	FIX: The SQL Server service crashes, and you receive error 1450 when you create database snapshot or run a DBCC CHECK statement on a database in SQL Server 2005
276993	967180  (http://support.microsoft.com/kb/967180/ )	FIX: You are prompted to provide the credentials when you view a history snapshot for a SQL Server 2005 Reporting Services report if you use stored credentials for the data source of the report

As per the KBA961930 advice keep a close-eye on this information:

Restart information

loadTOCNode(3, 'moreinformation'); You may have to restart the computer after you apply this cumulative update package.

Registry information

loadTOCNode(3, 'moreinformation'); To use one of the hotfixes in this package, you do not have to make any changes to the registry.

So take your time to go through this update package. 

SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation.

The error message posted on the subject above refers to install relevant client tools or services in order to continue the task you are running.

You may be aware that SQL Server setup offers installation of various services such as Analysis Services, Reporting Services, Integration Services and so on when you don't need them simply do not tick during the setup installation. Similar to this I found one problem when using SQL Server 2008 x64 installation and chosen to install DB Engine and INtegration services for obvious usage of ETLmanagement.

When it is complete and all ready for Application usage,  DBAs has strike with the following error :

SQL Server Execute Package Utility  Version 10.0.1600.22 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services.  The package execution failed.  The step failed.

The initial thought of the  error clearly states that it is unable to find Integration Services to continue the package creation of backup database job, by default creation of Maintenance Plans results in the creation of SSIS packages. Therefore, SSIS is required in order to execute a maintenance plan.If you are using any third party applications that may not refer to install SSIS services on the server, you have to install the Integration Services to continue the tasks.Further to this during the setup when tried to install Integreation Services itfailed that has not reported by unattended installation process, as you are aware that previous to SQL Server 2005 SP2 onwards the Database Maintenance plans were integreated with SSIS but  after SP2 Microsoft has changed that do not need SSIS to be installed to run maintenance jobs.

To resolve this issue in SQL Server 2008 the latest release of Cumulative UPdate 3 has got the fix as per the KBA96116  link:

SQL Server Deadlock and Trace Flag 1204 and 1222- a big question every time

I have seen few users out there thinks that Deadlocks in SQL Server is a bug which has not be corrected by Microsoft since its release! Even few times they get think that deadlocks and blocking are similar reactions in SQL Server, a typical situation of deadlock will be like a circular blocking chain that involves 2 or multiple connections modifying the  tables in different order and few times there will be 1 table involved. Within SQL Server engine when deadlock monitor thread detects blocking chain, it selects one of the participant as victim and cancels that SPID batch which involves a rollback of such transactions to make sure other connections (SPIDs) are allowed to continue.

As a DBA you must be aware of deadlocks scenario and anytime when I was involved on such situations the first action will be to check the SQL Server error log to ensure the information is published. SQL Server engine team has done tremendous job in introducing trace flag 1222 which is introduced in SQL Server 2005 version, you can enable the flag by uisng DBCC TRACEON (1222, -1) or even adding '-T1222' as a startup parameter for yoru SQL Server instance. The enhancement in this flag is a improved version of good old T1204 flag and best practices recommends that to use 1222 instead of 1204. But I have seen in some situations such as multi-instance installation of SQL Server 7 and/or 2000 then you have no other choice than using T1204 and to obtain further information alike T1222 then you could take help of SQL Profiler using 'Deadlock Graph' trace event.

Ok, now we have the process to obtain the information on Deadlocks and how to interpret such information to ensure that root cause of such problem can be resolved. I must recommend Bart Duncan's excellent job here on interpreting T1204 output and strongly refer every one to refer the link to understand the exact scenario that can enable you to get gist of the issue.

A typical deadlock error you will see is:

Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

So once I get to see the SQL error log output I will find out the query/stored procedure that is being executed and shown as victim in deadlock situation by opening up SQL Server Management Studio by changing the database context to relevant database, then right-click the relevant query text and select 'Analyze Query in DTA'. DTA - Database Tuning Advisor, you might be thinking why on earch you need a tool to get further in this critical situation. IN the past when I was involved with CSS I have been referred by esteemd Microsoft professional to ensure that relevant indexes are supported with that query or even use index hints to help the execution better, so don't skip this important task to identify further. As per the DTA tool configuration and analysis it may recommend indexes with a note of 'Estimated Improvement: <value>%'; then make sure to choose that recommendation by selecting 'Apply Recommendation' option on the drop-down menu that will help to create index immediately and monitor the query execution to see if deadlock persists. There is a note of recommendation here, for the first time or if you are unsure about that database significance in the Application then do not attempt this DTA step in production environment. Rather take a backup of existing database that is having deadlock issues and restore onto your like-to-like production environment for further testing. Just in case creating that index may bring down the SQL Server by grinding halt until it completes the creation, on top of existing connection & resource issue on that SQL instance, better be safe if you are unsure.

You may think size doesn't matter in such situation, even its a small database and so in that it is critical to the application high availability which can jeopardise the existence. To ensure you haven't done anything wrong it is better that you can do this with SQL Server tools without having the deep knowledge of a seasoned DBA. DTA (Index Tuning Wizard in SQL 2000 days) is a helpful tool that has saved my work when time is important and either blocking or simply performance was driving my instances down. The basic target of this tool is to recommend statistics and indexes that will improve the performance of a query you pass through it for starters. What more you want from a tool to understand the situation further and even you can use the tuning advisor for much more common use of creating indexes and statistics off queries prior to putting them in production.

Another wise plan is to ensure you tune each query that has been causing issues on the SQL instance and sometimes such blocking and deadlocking can lead to parallelism deadlock that can be identified by a CXPacket waittype that is listed in original deadlock output. Again Bart has done excellent job in explaining Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks" the scenario.

In such cases when you see a deadlock issue then I normally sense this is caused due to UPDATE LOCKS on the database which is by-design configuration which leads to bookmark or key lookups scenario. So you need to dig further to see any issues posed due to pagination. You can use DBCC SHOWCONTIG to see what kind of locking pointers are encountered. Further you can refer to Deadlock troubleshooting methods by Bart Duncan that I always follow in every instance of deadlocking resolution scenario.

Database diagram issues after SQL Server upgrade

Have you planned your databases upgrade from SQL Server 2000 version to 2005 or 2008?

Have you tested the above upgrade plan?

How many times you have been involved in the number of databases upgrade within your platform?

Many questions to answer when you think about upgrade, in this regard to addup to the Upgrade-FAQ_Blurb I have been involved in an upgrade process that involves a SQL Server 2000 instance to a SQL 2005 and 2008 instances. This is to require to test the application completely for 2005 & 2008 features compatibility. All the processes for upgrade has been completed successfuly and we hit the problem when one of the developer tried to create a database diagram! However, when the user clicks on the "Database Diagrams" tree node the following error message is displayed:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

------------------------------
BUTTONS:

OK
------------------------------

ALTER AUTHORIZATION is the new statement introduced in SQL Server 2005 version, that will be used for ownership of the schema-contained entities of type "object" can be transferred, such as: tables, views, functions, procedures, queues, and synonyms. Whereas few other entities such as: linked servers, statistics, constraints, rules, defaults, triggers, Service Broker queues, credentials, partition functions, partition schemes, database master keys, service master key, and event notifications cannot be transferred. In addition to that within SQL Server 2008 the ownership of securable classes cannot be transferred: server, login, user, application role, and column.

To continue working on the above error message make sure you have assigned the appropriate rights as defined below: 

EXEC sp_dbcmptlevel '<UpgradedDB>', '90';
go
ALTER AUTHORIZATION ON DATABASE::UpgradedDB TO "TheLogin"
go
use [UpgradedDB]
go
EXECUTE AS USER = N'dbo' REVERT
go 

After checkout of the above process the error still persisting, so I had a doubt on upgrade process that has been carried out. Also tried to create the diagram by setting the upgrded database compatibility to 80 and created the user again on the database with no success. Also  followed the workaround to remove all the schemas and logins that are associate with this user's login from each database, also remove all the associated mappings. As a matter of fact the requirement was urgent and it has to be finished soon so they granted the user with SysAdmin role!

But I was more keen on why the error is generated and as per the Microsoft documentation it confirms the issue of database does not have a valid owner has been fixed in Service Pack 1 for SQL Server 2005. The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server.  Due to the reason that SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid.  To resolve this problem the above TSQL needs to be used to explicitly setting the owner to a valid principal on the server, which should solve the problem.

SImilar to the above the Management Studio User Interface problem also occurs due to the install of the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000).  The installation fails and the UI incorrectly reports that the database has no valid owner.  Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects should solve the problem, the above bug has been fixed with SP1 as specified.

The issue was due to the mismatch of login name that is used in SQL Server 2000 instance is different to SQL Server 2005 & 2008 which are the upgraded databases, well the spelling was right but not the CASE. As the SQL 2000 instance has been installed (earlier) with accent-sensitive & case-sensitive that has been missed while installing the SQL 2005 & 2008 instances. So this is another point for every DBA to make sure that all the confirmation during pre-setup and post-setup must be documented to ensure these smaller options are not missed.

Error: SQL Server 2000 DTS Designer components are required to edit DTS packages. (Microsoft.SqlServer.DtsObjectExplorerUI)

The error message text (partial) on the subject will be familiar for the DTS/SSIS users in SQL Server.

On the background of the issue is that when you attempt of open a DTS package for editing from SQL Server 2008 Client machine, I have seen questions within forums that few users are unable to locate the SQL Server 2000 DTS Designer Components for SQL Server 2008 version and only SQL Server 2005 are available to download. More to the extension of the above error:

TITLE: Object Explorer
------------------------------

SQL Server 2000 DTS Designer components are required to edit DTS packages.
Install the special Web download, "SQL Server 2000 DTS Designer Components"
to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)
-------------------------------

Recently I have gone through this error and resolved the issue on a SQL Server instance that has been installed for use on Windows Server 2003 & 2008 X64 enterprise edition. To resolve the error a fair amount of background of process is required for you to understand.

Thanks to the Microsoft Documentation team that they have done a good job in documenting the process and procedures within the Books Onine (SQL Server 2005 & 2008 versions). Such in action the instructions for installing the DTS Design components can be found from this BOL143755 link (as I don't want to refer then again it is best for you to go through).  So as per the documentation process I have copied the relevent .DLL and .RLL files from the 80 (SQL 2000) version to 100 (SQL 2008) directories and then tried to open the DTS packages from the same client machine.

Again a new error has been popped up stating "The DTS host failed to save the package properly". I vaguley remember that one of the KB article refers that this error whent he following conditions are met:

• The SQL Server 2000 DTS package was last saved from an instance of SQL Server 2000 that is running on a Windows 2000-based computer.
• The SQL Server 2000 DTS package is stored in a structure storage (.dts) file.

Not only within SQL Server 2005 vresion this problem also occurs because of a compatibility issue with how icons are managed between DTS Designer in SQL Server 2000 and DTS Designer in SQL Server 2005. Talking about backward compatibility of the tools I wanted to check whether the SQL Server 2005 Backward Compatibility Components are installed or not, they aren't. For your reference you can download this SQL Server 2005 Backward Compatibility
Components from here and FYI these are updated for the SQL Server 2008 compatibility too.

I also remembered about a discussion that I have had with the PSS engineer in the past that SQL Server 2008 does not include support for "64-bit design-time or run-time" support for DTS packages. Say (you may be aware by this time) on a 64-bit computer, DTS packages, and Integration Services (SSIS) packages that run DTS packages, can run only in 32-bit mode. To run DTS packages in 32-bit mode, you have to install and use the 32-bit version of the dtexec utility. This dtexec.exe is needed if you haven't installed this 32-bit version of the utility during the client Tools or Business Intelligence Development Studio installation at setup process.

Similar to above there is also no 32-bit design-time or run-time support for DTS packages on Itanium-based operating systems. Due to this design constraint the user cannot create, view, modify, or run DTS packages on Itanium-based operating systems too. To avoid either you can download that dtexec utility from the above download link or refer to this link about installing the 32-bit version of of the Integration Services runtime utility. Also it is worth to refer about how to: Run a Package Using the DTExec Utility and finally a note about scheduling these packages. Say if you use SQL Server Agent to run the utility, SQL Server Agent automatically uses the 64-bit version of the utility. SQL Server Agent uses the registry, not the PATH environment variable, to locate the correct executable for the utility. To do this BOL refers (again) that:

To ensure that you run the 64-bit version of the utility at the command prompt, you can take one of the following actions:

• Open a Command Prompt window, change to the directory that contains the 64-bit version of the utility (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn), and then run the utility from that location.
  
• At the command prompt, run the utility by entering the full path (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn) to the 64-bit version of the utility.
  
• Permanently change the order of the paths in the PATH environment variable by placing the 64-bit path (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn) before the 32-bit path (<drive>:\ Program Files(x86)\Microsoft SQL Server\100\DTS\Binn) in the variable.
  

So after all the checkout on the tools for backward compatibility we found that the main issue occurred due to the package_path definition within that DTS package. Documentation refers that package_path or filespec arguments of the /SQL, /DTS, or /FILE options must be enclosed in quotation marks if the path or file name contains a space. If the argument is not enclosed in quotation marks, the argument cannot contain white space. To close up this topic it is a must reference for every SQL user to go about SQL Server Backward Compatibility feature installs the DTS runtime for SQL Server 2008, however it works only on 32-bit platform. Best practices dictates that you need to migrate DTS packages to SSIS environment as and when time is available, as a final reference on this migration process refer to this Migrating DTS Packages to Integration Services link and get the SQL Server 2000 DTS Designer components too.

Help Microsoft BI Development team to Build next version of AdventureWorks databases

Until SQL Server 2000 you may be familiar with pubs & northwind sample databases that were available along with SQL Server installation and since release of SQL 2005 the scenario has been changed for Sample databases.The version of AdventureWorks that is distributed with SQL Server 2005 is different from earlier versions AdventureWorks. If you already have a previous version of AdventureWorks, you should install the most recent version so that the queries in other ReportViewer walkthroughs work as expected. 

To explain briefly and learn more about using the AdventureWorks sample database, you can search for "AdventureWorks Sample OLTP Database" in SQL Server 2005 Books Online. To proceed further Microsoft Product Development team are planning to release next version of  AdventureWorks family of databases and community help will be a great help with their input.

So if you have used the AdventureWorks sample databases it would be wonderful if you would go to DBSamples site and file some work items on the Issue Tracker tab for anything you would like to see change.  Also, a brief note from the team that  if there are things you don't want changed by all means mention that in a >> work item as well. 

Also if you haven't had a chance to work or know about AdventureWorks sample databases it would be great if you could take a look at them now to feedback on the changes you would like to see. Still there is something else exciting for the users to report, if you see existing work items that you feel are important it would be very helpful if you could vote on them. 

MSFT Product Developmen Team promising to look at the feedback received from the community starting February 11th 2009 and you will agree that the input received from the communith will guide/help the team to decide on what changes they will be making.

SQL Server - Service Broker and Messaging, take care of MSDB sizes

You may notice that from SQL Server 2005 version onwards MSDB system database has got more importance than any other system database, I believe. It is a fact that if any problem ocurrs on the system databases such as MODEL or TEMPDB or MSDB the SQL Server will be offline irrespective of problems with MASTER database.  Each of the system database has got its own importance and above all MSDB occupies the primary role to keepup the out of user databases objects.

As you are aware that MSDB database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail, being it is a system database there are certain restrictions defined. For this reason best practices refers that keep the system databases to AUTOGROW especially this MSDB as it will grow unprecedentely when you have Messaging enabled and application uses Service Broker functionality, few times I have seen the issues in shrinking its size due to the space constraints on the drive where it is located.

A few references on the Service Broker that I would like to share from what I have learned so far, a important aspect on that is implementing security for the applications that involves Service Broker messagning functionality. The following process has been forwarded by MS SQLCAT when we were planning the Service Broker deployment at a client's place.

The prime requirement of the client was the security in messaging using the SQL Server features, when you talk about security then certificate-based authentication is the primary focus and it is a fact that using the certificate-based authentication is more cumbersome to set up than Windows based authentication. As the later (windows authentication) method may not be possible on all kinds of platforms/requirement (third party tools) and it is the best it works in more general circumstances, e.g., between different domains, and allows users to specify a window of time in which authentication will be honored. Nevertheless the means of communication must be secured which is the case of some form of transport security is always necessary.

Such a feature of transport security is possible and allows dialogs to be set up between services and is not concerned with permissions and security associated with these services. For example, the tutorial that refers here clearly specifies that target service will accept messages from any source in the sender server. Next comes to the mind about the configuration between the machines (domans too), in this case to avoid a port collision. It is essential that the servers are configured to enable communication protocols. The default protocol for the SQL Server is TCP/IP and the following process referred by the MS development team to follow in our case:

Initiator service - define a sender service and queue

use master;
go

-- Create a MASTER key for the certification

BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
 CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password#123'
COMMIT;
go

-- By default, the certificate is valid immediately and expires in one year. 

create certificate sender_transport_cert  with subject = 'SSB transport authentication for sender'
go

-- Write the cert to a file since we have to transfer it to the target.

backup certificate sender_transport_cert  to file = 'c:\remote\sender_transport_cert.cert'
go

Here I would like to insist this as a compulsory step, the backup of certificate that is created is required in order to secure the key data in case you need to restore this setup on the DR server.

create endpoint SsbEndpoint state = started
as tcp (listener_port = 4022) for service_broker (authentication = certificate sender_transport_cert)
go

If your SQL Server cited behind the DMZ or firewall setup then make sure to add the port number refered in the listener_port to the exception list for the communication.

-- A user must be associated with the target certificate.

create login target_user with password = 'Password#123'
go

create user target_user
go

create certificate target_transport_cert
 authorization target_user
 from file = 'c:\remote\target_transport_cert.cert'
go

-- Now this target user needs a permission to connect that endpoint!

grant connect on endpoint::SsbEndpoint to target_user
go

Not only the above fashion here is another link I would like to refer on Real Time Data Integration with Service Broker and Other SQL Techniques which involves the security aspect too.

In addition to the above topics I say if the current SQL instance is a part of Database Mirroring and/or Replication then it is nothing but adding fuel to the fire, as the application processes which manages the messaging tasks will have huge inserts to the MSDB and also the destination service adding few issues on the network too.

In general I have seen that the system databases are left on C: drive whereby 8GB maximum space is allocated for the operating system disks, even though if you place the system database on the drive where atleast 64GB space discs then make sure to monitor the available space on the MSDB and TEMPDB too.