SQL Server Installation Issues

OWC11 Installation Failure
Sample Error from errorlog-->MSI (s) (C4:C8) [19:04:30:953]: Product: Microsoft Office 2003 Web Components -- Error 1706. Setup cannot find the required files. Check your connection to the network, or CD-ROM drive. For other potential solutions to this problem, see C:\Program Files\Microsoft Office\OFFICE11\1033\SETUP.CHM.
Solution 1: Uninstall Sql server 2005 completely and then try to manually run the OWC11.msi file available in Tools folder of sql 2005.Then try to install sql 2005 freshly !
Solution 2: Just uninstall Office web components from Add \ Remove programs and then try to install sql 2005

-----------------------------------------

Error message when you install SQL Server 2005: "There was an unexpected failure during the setup wizard" - Error 1603
Solution:To work around this problem, set the folders in the correct layout for the SQL Server 2005 installation. The SQL Server 2005 installation uses the following two folders:

• Servers
• Tools

These two folders must be under the same level of a folder or the root folder of a drive. The names of these folders must be exactly Servers and Tools. The Servers folder contains all the files that are required to install major SQL Server 2005 components, such as database engine. The Tools folder contains tools components and Books Online for SQL Server 2005.Then try installing sql from the path\servers\setup.exe
SP2 fails to install database services - MSP Error: 29512 "Unable to install Windows Installer MSP file"
Error Snippet : MSP Error: 29512 SQL Server Setup was unable add user Clusteruser@FQDN (where FQDN is the actual fully qualified domain name - abbreviated here for security) to local group DOMAIN\SQL2005SQLAgents (where DOMAIN is the actual name of the domain). MSP returned 1603: A fatal error occurred during installation.
Solution: KB 926622 -- This article is for SP1 and also applicable to SP2 also.
To install SP2, try creating the following path, and restart SP2 setup:
DRIVE:\Microsoft SQL Server\MSSQL.2\MSSQL\FTData

-----------------------------------------

Included Column Index (New in SQL Server 2005)

In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key columns to the leaf level of the non-clustered index. Non-key columns, can help to create cover indexes.By including non-key columns, you can create non-clustered indexes that cover more queries. The Database Engine does not consider non-key columns when calculating the number of index key columns or index key size. Non-key columns can be included in non-clustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. Another advantage is that using non-key column in index we can have index data types not allowed as index key columns generally.

In following example column FileName is varchar(400), which will increase the size of the index key bigger than it is allowed. If we still want to include in our cover index to gain performance we can do it by using the Keyword INCLUDE.

USE AdventureWorks
GO
CREATE INDEX IX_Document_Title
ON Production.Document (Title, Revision)
INCLUDE (FileName)
Non-key columns can be included only in non-clustered indexes. Columns can’t be defined in both the key column and the INCLUDE list. Column names can’t be repeated in the INCLUDE list. Non-key columns can be dropped from a table only after the non-key index is dropped first. For Included Column Index to exist there must be at least one key column defined with a maximum of 16 key columns and 1023 included columns.

Avoid adding unnecessary columns. Adding too many index columns, key or non-key as they will affect negatively on performance. Fewer index rows will fit on a page. This could create I/O increases and reduced cache efficiency. More disk space will be required to store the index. Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

Another example to test:
Create following Index on Database AdventureWorks in SQL SERVER 2005

USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)
GO

Test the performance of following query before and after creating Index. The performance improvement is significant.
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN '98000'
AND '99999';
GO

Implementing Log Shipping

High Availability Features

• In order to reduce or avoid server or database downtime so that the applications/users works continuesly with the server we can configure high availability features.
• SQL Server supports 3 types of DR (Disaster Recovery) features.

                   1. Server Level HA features
                                    * Clustering
                   2. Database Level ,,   ,,
                                   * T.Log Shipping
                                   * Database Mirroring
                   3. Object Level HA features
                                  * Replication

1. Log shipping

• It is simple method of keeping entire database in different server.
• It works with backup, copy and restore jobs.
• We need 3 servers
   * Primary server
   * Secondary ,,
   * Monitor server (optional)
• In primary server database is in online state where the appls or users are connected.
• In secondary server database is in standby mode where we can read data.
• In secondary server T.Log backups are applied either with
       * Norecovery or
       * Standby
• We can have multiple secondary servers.

Advantages

• To reduce downtime for the applications or users in case of primary server failed.
• To implement load balancing. i.e we can allow reading from secondary server and manipulations in primary server.
• To maintain multiple secondary databases.

Points to Remember

• We can ship all databases except master, model, read-only and the databases whose recovery model is SIMPLE.
• We should have sysadmin privileges on both servers.
• Changing recovery model from FULL or BULK LOGGED to SIMPLE will break log shipping process.
• We cannot take ad-hoc Transaction Log backups from primary server as it breaks log shipping log backup chain.

(I will continue...)