SQL Server Storage Engine & Tools (SSQA.net)

Support for SQL Server 2005 SP2 and SQL Server 2008 RTM ends soon!

Its the time again that you should re-visit your database platform to ensure none of the SQL instances fall within SQL Server 2005 Service Pack2 or SQL Server 2008 RTM editions!

It is quite common scenario that few of Enterprises may not have gone to upto date service pack on SQL Server 2005 which is SP3 & SQL Server 2008 which is SP1. So here is the reminder for you to ensure to upgrade the 2005 & 2008 instances to upto date service packs, by testing them thoroughly which is a best practice.

Its a reminder that service pack support for  SQL Server 2005 Service Pack 2 (SP2)  will end on January 12, 2010 and support for SQL Server 2008 RTM will end on April 13, 2010.  For more information about the Service Pack support policy visit  http://support.microsoft.com/lifecycle link.  What it means that both of these release versions will no longer receive assisted support or security updates from Microsoft after their respective end of support dates.  Self-Help online support will continue to be available for a minimum of 12 months after the product reaches the end of support.  Self-Help options include public knowledge base articles, FAQs, troubleshooting tools which are typically available from http://support.microsoft.com and the Microsoft Download Center.

Better be workout the possibilities and test the application against latest service pack to keep up the database platform as a part of industry best practices.

SQL Server Migration Assistant for MySQL v1.0 - CTP1

SQL Server Migration Assistant is one of best used tool when there is a huge effort required to migrate the database from other data sources such as MS Access, Oracle & MySQL.

As a part of Microsoft Partner network communication I have received this exciting news about SSMA for MySQL which is available for download freely from SSMA2008-MySQLv1.0_CTP1 which is for SQL Server 2008 version and SSMA2005-MySQLv1.0_CTP1 for SQL Server 2005 version.

SSMA for SQL 2008 can be used to migrate data from MySQL to 2008 & Azure DB engine versions, and SSMA 2005 is for 2005 version only.

A brief note on licensing for the users who are interested to use these SSMA tools: ....a FREE download, and is available for use by any customer or partner with no charge or any obligations. However, the product requires a license key for activation. The key can be obtained after quick and simple registration. Microsoft reserves the right to use an e-mail address or phone number provided during registration in order to contact a customer regarding his/her SSMA experience. 

Unable to cast COM object of type 'Microsoft.Office.Interop.Access.Dao.DBEngineClass' to interface type 'Microsoft.Office.Interop.Access.Dao._DBEngine'

No doubt that the error on subject line will get no where!

This is presented when trying to open a Database Properties using SQL Server Managment Studio, going back to history on the SQL instance where this is presented is an upgraded database instance having upto date service packs (SQL Server 2008 SP1) with no cumulative update packages. Further information on full error text is below:

 Access Object Collector error: Database
     Unable to cast COM object of type 'Microsoft.Office.Interop.Access.Dao.DBEngineClass' to interface type 'Microsoft.Office.Interop.Access.Dao._DBEngine'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{00000021-0000-0010-8000-00AA006D2EA4}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).

 

At first I thought this could be issue due to corrupted files for Management Studio, so to overcome that conclusion I have tried from another client's machine which has been successful to load the database properties with no error/warnings. So the conclusion is some issue with system files on the machine which is presented with same error, the issue could be with the DLL file 'DAO360.DLL'. Also to take out what are the recent changes to this machine I found that SQL Server Migration Assistant was installed recently and to solve the COM component error we could try re-register of specified DLL file above by using:

• Click Start, and the click Run.
• Type the following command:   Regsvr32.exe "C:\Program Files\Common Files\Microsoft Shared\DAO\Dao360.dll" 

Once it is completed it will prompt a command box stating, specified DLL registered successfully. So to see whether this has solve the actual prblen I have tried accessing the same database from SSMS, to some extent the error on subject line was not presented again  bu with this error:  An error occurred while loading database content 

As a final trial before I decide on format the machine and reload the required tools again, I tried to check whether the PATH environment varialble is directed to correct location and to check this the procedure will be the following:

1.    Right click properties on My Computer

2.    Go to the tab “Advanced”

3.    Click on the button “Environment Variables”

4.    On the System Variables section, look for the variable “Path” and click on it.

5.    Once highlighted, click the “Edit” button.

6.    Go to the end of the “Variable value” , add a semicolon and then add the path of the DAO360.dll.

7.    Click OK button

8.    Click OK button

9.    Click OK button

All done and the issue has been resolved!

SQL Server Manageability series - shortcut for SSMS keyboard shortcuts!

For any user manage the work with short-cut is desirable, for better productivity in your work (with UI tools) I always suggest to manage it by keyboard not with the mouse.

Here is another block of SQL manageability series I want to cover based on feedback that I have received for earlier posts on this blog, this time let us visit on keyboard shortcuts.

Since the version 2005 release Microsoft has done good job for SQL Server Management Studio offering the users 2 kinds of keyboard schemes. One of the is a standard scheme based on Visual Studio which is used by default and another one is SQL Server 2000 scheme which goes by its name offering in particular the keyboard shortcuts from the Query Analyzer. I have had issues to use the keyboard shortcuts in Query Editor due to multiple versions of SQL Server installed on my machine (from 2000 to 2005 to 2008 tools & Express editions). So in order to workaround the issue and as per BOL indication you have to change the keyboard scheme or add additional keyboard shortcuts as on the Tools menu, click Options. Select the desired keyboard scheme on the Environment, Keyboard page.

On top of that here is my collection of SSMS keyboard shortcuts, which is documented clearly in BOL too:

For generic & BIDS related work:

Menu Activation Keyboard Shortcuts

Action	Standard	SQL Server 2000
Move to the SQL Server Management Studio menu bar	ALT	ALT
Activate the menu for a tool component	ALT+HYPHEN	ALT+HYPHEN
Display the context menu	SHIFT+F10	SHIFT+F10
Display the New File dialog box to create a file	CTRL+N	No equivalent
Display the New Project dialog box to create a new project	CTRL+SHIFT+N	CTRL+SHIFT+N
Display the Open File dialog box to open an existing file	CTRL+O	CTRL+SHIFT+INS
Display the Open Project dialog box to open an existing project	CTRL+SHIFT+O	No equivalent
Display the Add New Item dialog box to add a new file to the current project	CTRL+SHIFT+A	No equivalent
Display the Add Existing Item dialog box to add an existing file to the current project	ALT+SHIFT+A	No equivalent
Display the Query Designer	CTRL+SHIFT+Q	CTRL+SHIFT+Q
Close a menu or dialog box, canceling the action	ESC	ESC

 Manage toolbard keyboard shortcuts

Action	Standard	SQL Server 2000
Close the current MDI child window	CTRL+F4	CTRL+F4
Print	CTRL+P	CTRL+P
Exit	ALT+F4	ALT+F4
Toggle full screen mode	SHIFT+ALT+ENTER	SHIFT+ALT+ENTER
Close the current tool window	SHIFT+ESC	SHIFT+ESC
Cycle through the next MDI child windows	CTRL+TAB	CTRL+TAB
Cycle through the previous MDI child windows	CTRL+SHIFT+TAB	CTRL+SHIFT+TAB
Move to the current tool window toolbar	SHIFT+ALT	No equivalent
Move to the next tool window	ALT+F6	ALT+F6
Move to the previously selected window	SHIFT+ALT+F6	SHIFT+ALT+F6
Opens a new query editor window	No equivalent	CTRL+O
Display Object Explorer	F8	F8
Display Registered Servers	CTRL+ALT+G	No equivalent
Display Template Explorer	CTRL+ALT+T	CTRL+ALT+T
Display Solution Explorer	CTRL+ALT+L	CTRL+ALT+L
Display the Summary Window	F7	F7
Display the Properties Window	F4	F4
Display the Toolbox	CTRL+ALT+X	CTRL+ALT+X
Display the Bookmarks Window	CTRL+K, CTRL+W	No equivalent
Display the Browser Window	CTRL+ALT+R	CTRL+ALT+R
Display the Error List Window (Transact-SQL Editor only)	CRTL+\, CTRL+E	CRTL+\, CTRL+E

TSQL debugger shortcuts for programmers:

Action	Standard	SQL Server 2000
Start or continue debugging	ALT+F5	No equivalent
Stop debugging	SHIFT+F5	No equivalent
Step into	F11	No equivalent
Step over	F10	No equivalent
Step out	SHIFT+F11	No equivalent
Implement the Run To Cursor command	CTRL+F10	No equivalent
Display the QuickWatch dialog box	CTRL+ALT+Q	No equivalent
Toggle breakpoint	F9	No equivalent
Delete all breakpoints	CTRL+SHIFT+F9	No equivalent
Display the Breakpoints window	CTRL+ALT+B	No equivalent
Break all	CTRL+ALT+BREAK	No equivalent
Display the Watch 1 window	CTRL+ALT+W, 1	No equivalent
Display the Watch 2 window	CTRL+ALT+W, 2	No equivalent
Display the Watch 3 window	CTRL+ALT+W, 3	No equivalent
Display the Watch 4 window	CTRL+ALT+W, 4	No equivalent
Display the Autos window	CTRL+ALT+V, A	No equivalent
Display the Locals window	CTRL+ALT+V, L	No equivalent
Display the Immediate window	CTRL+ALT+I	No equivalent
Display the Call Stack window	CTRL+ALT+C	No equivalent
Display the Threads window	CTRL+ALT+H	No equivalent

.... download and update SQL Server Books Online [2005 & 2008] and visit shortcuts [SQL Server Management Studio] section for more shortcuts.

My frequently used keyboard shortcuts in SSMS:

ALT+F+E - to connect to any Server instance (DB engine/SSIS/SSRS/SSAS)

F8 - Display Object Explorer and F7 - to obtain details of each different node in the Object Explorer tree

CTRL+ALT+G - to show SQL Server Local Server groups and Central Management Server groups. FYI, local Server Groups are related SQL Server systems that SSMS connects to. Central Management Server groups store groups of SQL Server systems that let you perform management tasks to be enacted on all servers in a group.

CTRL+ALT+T - display the template explorer

CTRL+L - display execution plan

 

There are many more available and fellow MVP Pinal Dave's contribution to the community on the subject: Download SQL Server Management Studio Keyboard Shortcuts  link.

SQL Server Manageability tips - Quick view on Object Explorer and its best usage

As a SQL Server user Object Explorer is one component that you will never miss when you are using SQL Server Management Studio, in specific from version 2005 onwards.

As it states this connects to Database Engine instances, Analysis Services, Integration Services, Reporting Services, and SQL Server Compact 3.5. A brief view of  all the objects in the server and presents a user interface to manage them. But not many out there knew the capabilities of how best the Object Explorer's can be used and it  vary slightly depending on the type of server, but generally include the development features for databases, and management features for all server types. 

For the obvious reasons on to get more information, you must first connect to a server. From SSMS Click Connect on the Object Explorer toolbar and choose the type of server from the drop-down list. The Connect to Server dialog box opens and then to connect, you must provide at least the name of the server and the correct authentication information.

Do you know that there are few hidden gems that you can use within SQL Server Management Studio, among them this is one of its kind I always go about explore the possibilities of a server instance. Here are few tips from my experience that I would like to share on its best usage are: the property pane below the list of objects can be expanded to show more properties. Also to get the more information you can simply right-click on the column header in OED to add columns, with a sort feature  and settings stored for each object type.

Also you can simply copy the information to an Excel if you want to present it to an external source, by using the method of copy selected rows, the headers are included. the example was to sort tables by row count, copy the top 10 items, paste in Excel. In Excel, press Alt+F1 to chart the result.  Also from SSMS you can simply filter the type of object that you want to view, such as the feature that works in Object Explorer – just need to include the schema name and use the Filter command to focus on a set of objects you are working with.

Finally the feature of search command in toolbar of Object Explorer helps you to search for a specific object name or name(s) based on your text. In this scenario search looks for all objects with the matching name. Use LIKE expressions. If you start the search at the server level, search works across all databases and to stop you could click on Stop command that halts the search. (bear in mind if your database has got thousands of objects that are searching then it may take sometime to obtain result from system catalogs)

Many times I see complaints from users that my SSMS screen is too slow on certain times of the day, in this case I would  say that the first time you expand a folder, Object Explorer will query the server for information to populate the tree. As it would not restrict you to carry out other functions within SSMS while the tree is populating. As referred above on such further actions, such as filtering the list, will only act upon the portion of the folder that was populated, unless you refresh the folder to start population again.

Also one of the requirement from the user base is that why it will not simply refresh all the times, by design of SQL Server 2005 onwards and to conserve resources when there are many objects, the folders in the Object Explorer tree do not automatically refresh their list of contents. To refresh the list of objects within a folder, right-click the folder, and then click Refresh.

To close this topic of Object Explorer here is the say for you that SSMS tool can only display up to 65,536 objects. After you have exceeded 65,536 visible objects, you cannot scroll through additional objects in the Object Explorer tree view. To view additional objects in Object Explorer, close nodes that you are not using or apply filtering to reduce the number of objects (source BOL)

MSG 10314 An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.

As it states on the subject line this is an error that would cause when the system is trying to load assembly due to the permissions, the second part of the subject also needs consideration that server may be running out of resources & security issue.

Msg 10314, Level 16, State 11, Line 2
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'AssemblyName, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A) System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString) 

This is a common issue when you try to restore a database from a server (S) to a different server (D) when the CLR assemblies are used, so when the application tries to execute the relevant CLR assembly on Server D. Let us see on the text of "..The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues..". SQL Server Documentation (BOL) clarifies deeper into the subject on the security model of the SQL Server integration with the .NET Framework common language runtime (CLR) manages and secures access (by design) between different types of CLR and non-CLR objects running within SQL Server. The objects are called by a TSQL statment or even with another CLR object running in the server, where these calls between objects are referred to as links and always the security checks performed on these objects depend on the types of links involved. A section of BOL refers that:

The CLR integration security model has the following goals:

• By default, running managed user code on SQL Server should not compromise the integrity and stability of SQL Server. Performing operations that potentially compromise the robustness of SQL Server should be protected by appropriate high-level permissions.
• Managed user code should not gain unauthorized access to user data or other user code in the database. User-defined code should run under the security context of the user-session that invoked it, and with the correct privileges for that security context.
• There should be controls for restricting user code from accessing any resources outside the server, using it strictly for local data access and computation.
• User-defined code should not be able to gain unauthorized access to system resources by virtue of running in the SQL Server process.
  

So how can you ensure that database is secured and let these assemblies execute safely, there is no doubt that both of these situations require a specific degree of privileges and are protected against by appropriate mechanisms when they are used in the context of a database that is already attached to an instance of SQL Server.

A certain degree of administrative consideration you must follow on the SQL Server where the DB files are located *MDF & LDF, by default SQL Server sets file access permissions on the physical data and log files of each database to specific accounts. The permissions prevent the files from being tampered with should they reside in a directory that has open permissions. In this case take an example from BOL that, if the permissions are not set and the operating system permissions on the database directory are set to Full Control for everyone any account that has access to that directory can delete or modify the database files even though they may not have SQL Server permissions to modify the database itself. In similar case if the database is taken offline, a user that has access to the database file can potentially attach it to an instance of SQL Server of his or her choice and add malicious content to the database. When databases are detached and attached in SQL Server 2005, certain permissions are set on the data and log files that restrict access to the database files. For more information, see Securing Data and Log Files.

Here is comes the degree of understanding about TRUSTWORTHY settings on a database, which classifies that because a database that is attached to an instance of SQL Server cannot be immediately trusted, the database is not allowed to access resources beyond the scope of the database until the database is explicitly marked trustworthy. Also, modules that are designed to access resources outside the database, and assemblies with either the EXTERNAL_ACCESS and UNSAFE permission setting, have additional requirements in order to run successfully. For this reason SQL Server 2005 introduced the TRUSTWORTHY database property to indicate whether the instance of SQL Server trusts the database and the contents within it. Again BOL clarifies that by default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement. For example, ALTER DATABASE <DBName> SET TRUSTWORTHY ON;.When a database is backed up, the TRUSTWORTHY database property is set to OFF. Therefore, on a new mirror database TRUSTWORTHY is always OFF. To see whether this property is on: SELECT name, database_id, is_trustworthy_on FROM sys.databases

Coming back to where the error begins, you could use TRUSTWORTHY property to reduce certain threats that can exist as a result of attaching the database which may contain: malicious assembilies with EXTERNAL_ACCESS or UNSAFE permission settings and modules that are defined to execute the program as high-privileges users. To resolve the issue further KBA918040 refers that you may receive addition error when you create a new assemble that allows EXTERNAL_ACCESS or any permission that is classified as unsafe on the same database (restored):

Server: Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

The same KBA articles refers the workaround to enable TRUSTWORTHY database property and change the database owner:

ALTER DATABASE <DBName> SET TRUSTWORTHY ON 

USE <DatabaseName>
GO

EXEC sp_changedbowner ‘sa’

Further security on LOGIN access on a server which may have privileges to create a database on a server other than the existing server. In case if that login is a domain account then you can use/create the same login on other server for further permissions settings. But you must make sure that SID of that login matches the login on new server.

Additional to above settings and management of new objects (outside of DB) such as assemblies the best option is to script the existing assemblies with a drop & create option and make sure that new account has privileges on those objects. This is the reason best practices dictates that always handle the security and privileges surrounding assemblies can be deployed with an Active Directory (AD) account with relevant permissions on SQL instance that will avoid any re-sync or matching of SIDs as mentioned above.

Finally to close the topic here are best articles that I would like to refer about Installing .NET 3.5 on a SQL Server 2005 machine...be careful and SQL Server 2008 and .NET framework versions, don't forget to read BOL further on

• CLR Integration Code Access Security that discusses the code access security (CAS) model for managed code
• Host Protection Attributes and CLR Integration Programming about the host protection attribute (HPA) values that are disallowed in SAFE and EXTERNAL_ACCESS assemblies,
• Links in CLR Integration Security on how pieces of user-code can call each other in SQL Server
• Impersonation and CLR Integration Security on how managed code accesses external resources using impersonation
• Allowing Partially Trusted Callers issues that arise when a managed method invokes a method in a class contained in another assembly
• Application Domains and CLR Integration Security with an description on how assemblies are loaded into application domains.

Problems you face when you do not check features not supported in SQL Server

Few developers on a client's place raised a question that why they should really be concerned about deprecated features or commands in SQL Server?

Until SQL Server 2000 version it used to be a workaround to get the old scripts executed in a way. It is good now that you have complete reference within the documentation (BOL). The question above has got 2 answers, one is yes the user must be concerned and there is a way to come out of the problem by finding the deprecated features on the existing release.

Another change (may be) of support policy surrounding deperecated features that Microsoft is no longer going to support the particular features once they are designated as deprecated features.  The documentation & communication from Microsoft has been clear enough to worry the developers that particular SQL Server features\commands would no longer be available and those commands have remained in the product.  It also means that once a feature is indicated/considered as deprecated that the feature will be removed in the following product release. The pre-installed tools such as PERFMON (SYSMON), PROFILER and Upgrade Advisor would help to a great extent that will reduce the problems to find out whether your existing scripts has such danger of un-supportability.

I would pick from last that Upgrade Advisor, when you run Upgrade Advisor, the Upgrade Advisor Home page appears. From the Home page, you can run the following tools: Upgrade Advisor Analysis Wizard, Upgrade Advisor Report Viewer and Upgrade Advisor Help. It is better that when you run first time of this tool always run the Upgrade Advisor Analysis Wizard to analyze SQL Server components. When the wizard finishes the analysis, view the resulting reports in the Upgrade Advisor Report Viewer. Each report provides links to information in Upgrade Advisor Help that will help you fix or reduce the effect of the known issues. Insider the Upgrade Advisor, the analysis examines objects that can be accessed, such as scripts, stored procedures, triggers, and trace files, be aware that only it cannot analyze desktop applications or encrypted stored procedures and finally the output is in the form of an XML report where you can view the XML report by using the Upgrade Advisor report viewer.

Few of the deprecated commands for your refer:

Backup Log WITH NOLOG

Backup Log with TRUNCATE ONLY

DUMP DATABASE | DUMP LOG

LOAD DATABASE | LOAD LOG

BACKUP TRANSACTION

BACKUP DATABASE with PASSWORD

BACKUP DATABASE with MEDIAPASSWORD

Configures a database to operate in an earlier compatibility level

• 60 = SQL Server 6.0
• 65 = SQL Server 6.5
• 70 = SQL Server 7.0

sp_addalias, sp_dropalias, sp_addgroup, sp_changegroup etc. 

Coming to PERFMON tool you can use the SQLServer:Deprecated Features object in SQL Server provides a counter to monitor the features designated as deprecated. In each case the counter provides a usage count that lists the number of times the deprecated feature was encountered since SQL Server last started.  Within the BOL on the INdex if you look for  deprecateion [SQL Server] --> Performance Counters you will see a table that describes the SQL Server Deprecated Features counter instances. Also you can use Dynamic Management View (DMV) to get a list of such information, where the Feature name value appears in trace events as the ObjectName and in performance counters and sys.dm_os_performance_counters as the instance name with the Feature ID value appears in trace events as the ObjectId.

Finally the PROFILER would be handy too get the information by  using the Deprecation Final Support event class occurs when you use a feature that will be removed from the next major release of SQL Server. For greatest longevity of your applications, do not use features that cause the Deprecation Final Support event class or the Deprecation Announcement event class. SO the advice is you must modify applications that use final deprecation features as soon as possible. Why it is, Depreciation Announcement will be trigger when your current database server version has depreciated feature but it is still supported on current SQL Server version based on reflected profiler version. Depreciation Final Support will be trigger when your current database server has depreciated feature that will be unsupported on next SQL Server version based on reflected profiler version. In addition to this BOL clarifies further that the Deprecation Announcement event class occurs when you use a feature that will be removed from a future version of SQL Server, but will not be removed from the next major release. For greatest longevity of your applications, avoid using features that cause the Deprecation Announcement event class or the Deprecation Final Support event class.

In the next post I will cover the DISCONTINUED features issues.

SQL Server Upgrade from 2005 to 2008 - Notes and Best Practices from field :session from SQLBits conference

During the SQLBits IV conference I had presented a session on the topic: "Upgrading To SQL Server 2005 & 2008: Notes & Best Practices" with an excellent feedback from users. In this session the areas are covered such as SQL Server Upgrade options, methodology, tools and planning, technical and non-technical considerations along with common issues, myths and mistakes. Also I had touch base on the monitoring at pre & post upgrade scenarios along with exhaustive list of all upgrade issues with an end-to-end coverage or in-depth drilldown of all SQL Server 2005 & 2008 features.

Ok, now why I'm blogging about it. Thanks to SQLBits organisers in releasing video and my presentation on the site.

Before I give you the chance to download the session, I would like to cover on important aspects of scenario in SQL Server upgrade from previous version to existing version. As it may sound easy (bit) to upgrade a database with few clicks there are many pre-upgrade factors, based on my experience and past upgrade experience I have outlined the process to follow which has produced good results with minimum interruption and issues on SQL Server platform.

In my experience and tasks that are handled in real-time processes, the factors & components below are considered as major steps for Upgrade process:

Components

·         Are you using SQL Server 2000 Analysis Services?

·         Are you using SQL Server 2000 Reporting Services?

·         How many DTS packages (from SQL 2000) exists on system?

·         What is the current hardware capacity?

·         Are you using existing hardware or new hardware to upgrade?

Upgrade Planning

·         The planning phase should identify the steps from identifying the databases targeted for the upgrade to determining the changes and processes the upgrade will require.

·         A combination of components required for upgrade, such as platform upgrade path, application architecture, hardware and pre & post upgrade issues.

·         A tool should be used to identify the pre-upgrade issues to focus efforts on what to expect from the pre-upgrade & post-upgrade problems.

·         A major decision in the preliminary work is to decide whether we perform in-place upgrade (usage of same servers) or side-by-side upgrade (different servers).

·         Nothing can replace testing. Even if you plan to upgrade only the SQL Server database engine without changing your application, testing will help identify any backward-compatibility problems and behavioral changes from previous SQL Server releases that the Upgrade Advisor didn't detect. 

·         IF you have any other components such as Analysis Services, Reporting Services and DTS/SSIS packages then  the table (source: Technet)below gives the upgrade path:

Testing & Validation

Finally these tasks are performed in the testing and validation phase:

·         Prepare your test environment. Side-by-side migrations require a separate test SQL Server 2005 installation. In-place upgrades require a test machine running SQL Server 2000 or 7.0 and target database copies. Hardware comparable to your production setup will allow production volume testing.

·         Set a pre-upgrade baseline. This baseline will help you evaluate your system post-upgrade and determine any behavioral changes, letting you simulate a typical workload after your upgrade. The baseline will also help you confirm functionality and document performance improvements or changes. To set up the baseline, you can use familiar tools such as SQL Server Profiler, application load testing tools, Performance Monitor counters, and Showplan statistics.

·         Develop a test plan. Set up a generalized testing script or test procedures for the following areas: data validation, data processing, stress and workload, client/server performance, and application functionality.

·         Develop a recovery plan. Develop upgrade rollback procedures in case of an upgrade interruption. The recovery plan should include running a DBCC consistency check on the pre-upgrade databases before backup as well as a full restore of the database to validate the backup reliability. After the upgrade, you should also perform a consistency check and a backup with validation. Make sure you test your rollback procedures.

·         Create application-modification procedures. Your test environment should include the full application tier so that you can confirm application changes work as expected. These application-modification procedures should include a catalog of effected users. Such procedures also allow complete documentation of your application changes so that they can be applied successfully during the production cutover.

·         Perform an upgrade test run. A final test run of the upgrade will confirm that the process and procedures work as you expect. You can use the Upgrade Advisor in this step. Run the tool after you apply your pre-upgrade changes to validate that you’ve addressed all the problem areas the tool identified earlier.

For this you have 2 option, either you can Click here to view in local media player or Right click here and click "Save As" to download a copy of this video and also refer to the blog post SQLUpgradeIssues-HowToEvaluate the issues and resolve them.

(there seems to be problem initially to download the session videos, which has been correct now)

Format: ashx?SessionId=240&Regenerate=
Duration: --:--

SQL Server database design goals: Memory, Disk and I/O - maximize the scalability with monitoring

Do you consider the I/O whenever a database design project is assigned? 

It is one of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. Over the time it may be one of the criteria that extensive usage of data and defautl behaviour on SQL Server builds a buffer pool in memory to hold pages read from the database.

While queries are getting designed the code must be optimized and dedicated to minimizing the number of physical reads and writes between the disk and the buffer pool. By design SQL optimizer balances between keeping buffer pool from becoming so big that the entire system is low on memory and minimize physical I/O to the database files by maximizing the size of the buffer pool. As such the SQL Server dynamically acquires and frees memory as required. Unless you are sure about how data is managed it is not a good practice to specify how much memory should be allocated to SQL Server (min and max), although the option still exists and is required in some environments.

In some cases the specific hardware may have advantage in performance as the system's processors has its own memory and possibly its own I/O channels, and each CPU can access memory associated with the other groups in a coherent way, refer to NUMA for more information.  As per the usage practices a monitoring strategy should be build to obtain the system statistics, this is where Dynamic Management Views (DMV) comes in handy. To the topic on I/O statistics - sys.dm_io_virtual_file_stats dynamic management view is an useful one.

uses Microsoft Windows operating system input/output (I/O) calls to perform read and write operations on your disk. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. The I/O subsystem includes the system bus, disk controller cards, disks, tape drives, CD-ROM drive, and many other I/O devices. Disk I/O is frequently the cause of bottlenecks in a system. To capture the statistics you should monitor disk activity involves two areas of focus: "Monitoring Disk I/O and Detecting Excess Paging" & "Isolating Disk Activity That SQL Server Creates". Also FYI How SQL Server manages when a database is created in terms of I/O and disk usage? too a good one to understand when you have frequent database creation exercise.

Further refer to the blog posts from Rob Pearl on how best you can monitor & capture the statistics:

I/O I/O - It's why my server's slow.....Examing I\O Statistics

I/O You a followup - More about I/O, Performance Tuning & Troubleshooting....

Happy first anniversary for SQL Server 2008 RTM

It is over 1 year and first anniversary for SQL Server 2008 RTM, last year the RTM is released on August 06th 2008 re. http://sqlserver-qa.net/blogs/sql2008/archive/2008/08/06/4727.aspx post.

HAPPY first anniversary.... and kudos to SQL Server Development team!!!

SQL Server warning message: A component that you have specified in the ADD_LOCAL property is alreadyinstalled

The above warning message may be familiar to the user machines that has SQL Server Express edition managment studio and trying to install SQL Server 2008 client tools.

As you know the SQL Server 2005 Express Managaement Studio (SSMSE) that is available for free due to the nature of Express edition, which is limited by the features of other SQL Server editions. So when a machine consists this Express edition tools and when you wanted to upgrade them to full version of SQL Server 2008 SSMS tool then it is impossible to upgrade in a straight forward manner. It is best to uninstall the existing tools and then install afresh from the media to run SqlRun_Tools.msi to install the toolset.

Further to the above warning message you may get error message that is displayed below: 

A component that you have specified in the ADD_LOCAL property is already
installed. To upgrade the existing component, refer to the template.ini
and set the UPGRADE property to the name of the component.

In the past few occassions I have had thsi question from users that, what is that template.ini referred to. I would suggest to refer SQL SERVER – 2005 – Silent Installation – Unattended Installation ... post by fellow MVP, Pinal Dave which is clear as crystal.

In order to workaround the issue of upgrading the  tools you must follow the process of uninstalling the existing ones and re-install required tools. Again refer to  How to uninstall an instance of SQL Server 2005 manually KBA which can lead to successful installation, for few ones. Recently I have been faced with this problem and I have followed the way I referred above, further to the issues when executed the  SqlRun_Tools.msi file from the SQL media after the "Preparing Installation Wizard" screen process with following text:

The setup has encountered an unexpected error in datastore. The action
is SetDialogs. The error is : Source File Name:
...\datastorecachexmlschema.cpp
**** Compiler Timestamp: Fri Jul 24 01:13:50 2005
****

So the obvious issues of unexpected errors has not workaround, other than removing the complete programs of such SQL tools including the SNAC (native client) and SQLXML libraries. Not only that once you have uninstalled it is better to reboot the machine which will clear the cache and registry that will enable clear path to reinstallation of required upgrade or version tools. Also I wanted to stress the feasibility of having redistributable components from SQL Server feature pack, such as:

Microsoft ADOMD.NET

ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.

Note:The English ADOMD.NET setup package installs support for all SQL Server 2008 languages.

Audience(s): Customer, Partner, Developer


X86 Package(SQLSERVER2008_ASADOMD10.msi) - 4411 KB
X64 Package (SQLSERVER2008_ASADOMD10.msi) - 6924 KB
IA64 Package(SQLSERVER2008_ASADOMD10.msi) - 9418 KB

If you are responsible in development or management of SQL Server Analysis Services (BI) then:

 Microsoft Analysis Management Objects

Analysis Management Objects (AMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer Analysis Services objects.

Audience(s): Customer, Partner, Developer


X86 Package(SQLSERVER2008_ASAMO10.msi) - 2752 KB
X64 Package (SQLSERVER2008_ASAMO10.msi) - 4459 KB
IA64 Package(SQLSERVER2008_ASAMO10.msi) - 6199 KB

Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider

The Analysis Services 10.0 OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.

Note: Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider requires Microsoft Core XML Services (MSXML) 6.0, also available on this page.

Audience(s): Customer, Partner, Developer

X86 Package(SQLServer2008_ASOLEDB10.msi) - 19362 KB
X64 Package (SQLServer2008_ASOLEDB10.msi) - 43823 KB
IA64 Package(SQLServer2008_ASOLEDB10.msi) - 50571 KB

Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007

Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007 enables you to take advantage of SQL Server 2008 predictive analytics in Office Excel 2007 and Office Visio 2007. The download includes the following components:
• Table Analysis Tools for Excel: This add-in provides easy-to-use tools that leverage SQL Server 2008 data mining features to perform powerful analytics on your spreadsheet data. Two new tools have been added for this release: Prediction Calculator and Shopping Basket Analysis.
• Data Mining Client for Excel: This add-in enables you to go through the full data mining model development lifecycle within Excel 2007 using your spreadsheet data or external data accessible through your SQL Server 2008 Analysis Services instance. This release adds support for new SQL Server 2008 data mining features including holdout and cross-validation, a new Document Model wizard, and improvements to existing wizards.
• Data Mining Templates for Visio: This add-in enables you to render and share your mining models as annotatable Visio 2007 drawings.
  
  
Audience(s): Customer, Partner, Developer


Download site



Microsoft SQL Server 2008 Datamining Viewer Controls
The Data Mining Web Controls Library is a set of Microsoft Windows Forms controls that enable software developers to display data mining models created using Microsoft SQL Server 2008 Analysis Services in their client-side applications. The controls in this library display the patterns that are contained in Analysis Services mining models.

Note:Microsoft SQL Server 2008 Datamining Viewer Controls requires Microsoft ADOMD.NET, also available on this page.

Audience(s): Customer, Partner, Developer


X86 Package(SQLServer2008_DMViewer.msi) - 9385 KB

For Core XML services (MSXML) and Native Client:

Microsoft Core XML Services (MSXML) 6.0

Microsoft Core XML Services (MSXML) 6.0 is the latest version of the native XML processing stack. MSXML 6.0 provides standards-conformant implementations of XML 1.0, XML Schema (XSD) 1.0, XPath 1.0, and XSLT 1.0. In addition, it offers 64-bit support, increased security for working with untrusted XML data, and improved reliability over previous versions of MSXML.

Audience(s): Partner, Developer


Download site

Microsoft SQL Server 2008 Native Client

Microsoft SQL Server 2008 Native Client (SQL Server Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 features. This redistributable installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.

Audience(s): Customer, Partner, Developer


X86 Package (sqlncli.msi) - 4504 KB
X64 Package (sqlncli.msi) - 7919 KB
IA64 Package (sqlncli.msi) - 11078 KB

For Reporting Services requirement:

Microsoft SQL Server 2008 Report Builder 2.0

Microsoft SQL Server 2008 Report Builder 2.0 provides an intuitive report authoring environment for business and power users with a Microsoft Office look and feel. Report Builder 2.0 supports the full capabilities of Report Definition Language (RDL) including flexible data layout, data visualizations, and richly formatted text features of SQL Server 2008 Reporting Services. The download provides a stand-alone installer for Report Builder 2.0.

Note: This component also requires Microsoft .NET Framework 3.5.

Audience(s): Customer, Partner, Developer


Download site

Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies

Microsoft SQL Server 2008 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2008 report processing and management capabilities in SharePoint integrated mode. This version also includes data-driven subscriptions. The download provides a Report Viewer Web part, Web application pages, and support for using standard Windows SharePoint Services or Microsoft Office SharePoint Services.

Audience(s): Customer, Partner, Developer


Download site

Finally in addition to the above I suggest the following ones too, Upgrade Advisor and Backward Compatibility:

Microsoft SQL Server 2008 Upgrade Advisor

Microsoft SQL Server 2008 Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 in preparation for upgrading to SQL Server 2008. Upgrade Advisor idaentifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.

Note: This component also requires Windows Installer 4.5.

Audience(s): Customer, Partner, Developer


Download site

Microsoft SQL Server 2005 Backward Compatibility Components

The SQL Server Backward Compatibility package includes the latest versions of the Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with both SQL Server 2005 and SQL Server 2008 and include all fixes shipped through SQL Server 2000 Service Pack 4 (SP4) and SQL Server 2005 SP2.

Audience(s): Customer, Partner, Developer


X86 Package (SQLServer2005_BC.msi)
X64 Package (SQLServer2005_BC_x64.msi)
IA64 Package (SQLServer2005_BC_ia64.msi)

Changes to SQL Server 2005 and 2008 clustering support policy on Hyper-V or SVVP

• Microsoft's technical support policy for SQL Server 2005 and 2008 version has been changed when it comes to the environments that are using hardware virtualization, such as using Windows Server 2008 with HyperV or Microsoft's Hyper-V Server 2008 and in addition to server configurations that certified through the SVVP - Server Virtualization Validation Program.

This has been brought into the attention while I was working on a client's project for a 8 node SQL cluster solution during last few months. As it before the SQL Server 2005 was not supported on Hyper-V, due to the obvious cost savings the client was more intended to virtualize the platform including the SQL Server (there is different story about end-to-end testing in this regard, which I will cover on other time). As per my habit of revising few important KBAs I had a chance to go through Bob Ward's previous post on the support policies (a must read) and a bit of background on the subject point with further enhancement on the polices within the KBA 956893 link. To refer the same for article:

• Guest Failover Clustering is supported for SQL Server 2005 and SQL Server 2008 in a virtual machine for Windows Server 2008 with Hyper-V, Microsoft Hyper-V Server 2008, and SVVP certified configurations provided both of the following requirements are met:
• The Operating System running in the virtual machine (the “Guest Operating System”) is Windows Server 2008 or higher
• The virtualization environment meets the requirements of Windows 2008 Failover Clustering as documented at The Microsoft Support Policy for Windows Server 2008 Failover Clusters.

Why such changes occur within the supporty policy of Microsoft?

Microsoft Customer Service and Support (CSS) has huge burden to provide support to any non-Microsoft virtualization vendor and such policies has been documentedat KBA 897615.This means if any corporation have a Premier Support contract with Microsoft, CSS will attempt to investigate the problem to some "reasonable" level before asking you to reproduce the problem outside the virtualization environment.  Take a look at that article about what is reasonable means!

So what-if no Premier contract? Due to the obvious reasons the problems needs to be re-produced outside of that virtualized environment in order to rule out any issues within Microsoft based components.

Further study on support and virutalization products are below:

• We will support previous versions of SQL Server in any virtualization product per KB Article 897615.
• We also support Microsoft Hyper-V Server 2008 which is a new product for you to host Hyper-V virtual machines.
• If you want to know more about best practices for running SQL Server in a Hyper-V environment, check out this whitepaper from the SQLCAT team.

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 - update

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.
 

Additional update on the issue, the same problem appeared again and again causing more performance issue. So the root cause is hardware and network links that are associated with that server. In addition to the MSDTC issues that I referred above both of these problems are adding the fuel. One of the DBA made a mistake by killing that negative SPID causing some more problem due to the nature of ROLLBACK actions, the explanation was he found that PROFILER was showing more CPU resource time for that SPID. I would say use PROFILER as a last resort and the results shown on there is duration after that statement is complete, anyhow the resolution must be find the long running code and explicit KILL of SPID will not help. It is better to follow the results from the DMV sys.dm_exec_requests to get more information.

Error: SQL Server Setup could not connect to the database service for server configuration.

There is much more text to the actual error posted on subject line, it is:

TITLE: Microsoft SQL Server 2005 Setup
------------------------------

SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.

This could be a problem during the installation of SQL Server on a server or even when attempting the new connections from the client machines.

Lately this issue has occurred when one of the Systems Management support personnel trying to reinstall the SQL Server services on a QA machine to get ready for new deployment testing. At first when it is reported I had no doubt to see whether any issues with SQL Native Client (SNAC) corruption or incompatible, so I took the inventory of the operating system and other client components on the server, all stays as per the standard configuration. By default if you get such an error best is to refer to SQL Server error log & windows event viewer system log for further information. So I have checked the same and found that it is the Sql Native client DLL, as I can see sqlncli.dll present in the \Windows\System32 directory there was no entry on the drivers tab of the Data Sources (ODBC) utility (control panel --> Adminsitrative Tools --> Data Sources).

It is the new problem that poses if your server has got applied any new hotfix or changes to the security policies with registry or service accounts then you must check whether such changes has been applied recently. So in my case it wasn't the case so the option is to uninstall the existing SNAC tools from Add/Remove programs and reinstall afresh, still problem relies upon due to the nature of existing files have not been removed. So the option is to unregister that DLL and delete the file, then reinstall the SNAC component seperately.

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!