Blog: SQL Server

When installing service pack 2 for SQL 2005, sometimes a bug presents itself where the installer needs access to some files that are inside the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA folder. The bug is that these files do not have the correct permissions set. Even the Administrator does not have permission on these files. If you set the permissions for the data folder to the administrator being the owner, it will not propagate down into all the files. You have to go into each file in the data folder and make sure that Administrator is the owner and has full control of all of them. You then log in as administrator and the service pack will install successfully.

When allowing SQL traffic to flow across an ISA server its good to know what the ports it uses are for. SQL primarily uses two ports: 1433 (the default sql port) and 1434 (the sql browser service’s port). If you specify that you want to connect to a named instance then the traffic will first be sent over port 1434 to the sql browser service. Then the sql browser service will send back the port number that the named instance is on. Finally your application will try to talk over this port that was given to it by the sql browser service.  So, one way to get around this when going through an ISA server is to setup a publish rule to listen on 1433 (the default instance for sql) and have it forward the packets over whatever port you are using for the named instance. You must remember though, if doing this not to specify the named instance in your application’s config or else it will try to contact the browser service over 1434.

There is a known issue with SQL Server 2005 SP2 that occurs if you have moved the system databases to another location. Specifically, the mymssqlsystemresource database. If the mymssqlsystemresource transaction log has been moved from its default location, the installer for SP2 does not recognize this. The installer places the new mymssqlsystemresource.ldf file in the same directory as the mymssqlsystemresource.mdf file regardless of whether this is where you have moved the .ldf file to. After the SP2 install, SQL Server services will not start. To fix the issue, simply copy the .ldf file from the directory where mymssqlsystemresource.mdf lives and place it in the updated location and restart the SQL Server services.

If you have multiple SQL Server instances on a server, running the service pack install in ‘default gui’ mode will only apply to the default instance.  To install to all instances at the same time, you need to expand the service pack, then run from the command line with the /allinstances tag: [more]

http://www.sqlserverclub.com/articles/how-to-upgrade-sql-server-instances-quickly-and-quietly.aspx

Here is some related info on SQL instances from MSDN:

http://msdn.microsoft.com/en-us/library/ms143531(SQL.90).aspx

The SQL 2005 installer expects the installation media to be in a specific layout.  If the directory structure is incorrect the error message you receive during setup is: "There was an unexpected failure during the setup wizard. You may review the setup logs and/or click the help button for more information."

The behavior I saw was that the SQL server itself would install, but the Management Studio installation failed.

From http://support.microsoft.com/kb/916760:
This problem is most likely to occur if you start the SQL Server 2005 installation from a folder on a network share or on a hard disk when the folder was copied from the SQL Server 2005 installation CDs.

WORKAROUND
To work around this problem, set the folders in the correct layout for the SQL Server 2005 installation. [more]

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.

I was trying to upgrade a SQL 2000 server to SQL 2005 and the upgrade kept failing.  The first error I would see was "An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'."  Other parts also failed to install.  I looked at the installation log files, but they were so large and there were so many messages about it trying to locate packages, registry entries, etc., it was impossible to tell what represented an error and what did not. [more]

I opened a case with Microsoft (which could be a pretty good gotcha in itself), and sent the log files.  The tech seemed to know immediately what the problem was.  It was caused by the fact that a Microsoft SQL Server Native Client was already installed on the machine.  I think it was installed by the some third party software and it was a later version than what came with SQL 2005.  I removed the Microsoft SQL Server Native Client and the installation went fine with no errors.  Later I received a follow up call from Microsoft and they said they had received many reports of this issue and they would have this fixed in SQL 2008.

When using Backup Exec 10.x, you may experience problems backing up SQL Server 2000 or MSDE databases if the file path contains double backslashes in the file path.

The error within Backup Exec is:  [more]

Final error: 0xe0008492 - Database Query Failure. See the job log for details.
Final error category: Resource Errors

To fix the issue, you must detach and reattach each database that contains the double backslash in the file path. The issue is caused by including a trailing slash in the “New Database Default Location” when declaring a file location.

If you do include a trailing slash (which will be included by default if you use the browse button) and try to create new databases using T-SQL (i.e. CREATE DATABASE …), your file paths will end up with the double backslash notation. Creating databases with the Enterprise Manager GUI does not produce the double backslash no matter if the “New Database Default Location” includes a trailing backslash or not. So, make sure the trailing backslashes are removed from the file path.