SQL Server 2005 CU6 and SQL Server 2008 CTP6

February 19th, 2008 No comments

Microsoft just released SQL Server 2005 CU6, build 9.00.3228 (KB: 946608). Also a new community preview (CTP6) of the SQL Server 2008, build 10.00.1300.13,  is going to be available sometime this week from the Microsoft Connect web site (check often this link).

Categories: SQL Server Updates Tags:

SQL Server 2008 new RTM date

January 30th, 2008 No comments

It’s official. SQL Server 2008 RTM date slipped to Q3 CY2008. See details here. That means that with SQL Server 2000 mainstream support ending in April, SQL Server 2005 will be the only supported version for several months. I know that some SQL Server shops were planning so called ‘version-skip’ migration, i.e. go directly to SQL Server 2008 from 2000 (and SQL Server 2008 will support direct migration from SQL Server 2000). If you want to stay under Microsoft support that’s not an option anymore with the new RTM date.

Categories: SQL Server Updates Tags:

SQL Server startup parameters (Part 4)

January 28th, 2008 No comments

There are 2 SQL Server startup parameter, that don’t start the instance, but give an information about the product.

-? – Lists available startup parameters when sqlsrvr.exe executed for a command prompt. List only documented parameters.

-v – Lists instance version, platform and build information, you have to use -s <instance name> parameter when ran against a named instance of the SQL Server. This is a non-documented startup parameter.

Categories: General Tags:

SQL Server startup parameters (Part 3)

January 28th, 2008 No comments

Beginning with SQL Server 2000, Microsoft flagship database product supports multiple instance per server. When starting SQL Server as a service (from Windows Service Manager or from a command line with “net start” notation), instance information is embedded into the SQL Server service definition. When starting SQL Server from the command line in order to start non-default, named instance you need to use -s startup parameter.

-s <instance name> – Starts SQL Server named instance. Sqlservr.exe have to be started from the instance’s Binn directory: (default: C:\Program Files\Microsoft SQL Server\<INSTANCE ID>\MSSQL\ BINN). You can also use -c startup switch alongside -s option to shorten instance startup time.

Categories: General Tags:

SQL Server startup parameters (Part 2)

January 21st, 2008 No comments

There are 3 documented startup parameters that always present in any SQL Server instance. These parameter define location for the SQL Server instance’s master database (data and log files) and also location for the SQL Server instance ERRORLOG file.

-d – SQL Server instance master database data file location (default: C:\Program Files\Microsoft SQL Server\<INSTANCE ID>\MSSQL\Data\ master.mdf).

-l – SQL Server instance master database log file location (default: C:\Program Files\Microsoft SQL Server\<INSTANCE ID>\MSSQL\Data\ mastlog.ldf).

-e – SQL Server instance log file location (default: C:\Program Files\Microsoft SQL Server\<INSTANCE ID>\MSSQL\LOG\ERRORLOG).

These startup flags (and also flags from my previous post) become very useful when you need to move system databases (master, model, temdb for the SQL Server 2000, 2005, 2008 and resource database for the SQL Server 2005 and 2008) to a new location. Complete procedure described in these 2 Microsoft resources:

There are 2 SQL Server trace flags (not documented in the SQL Server Books on-line) that are mentioned in the links provided above and also helpful when you relocate SQL Server instance databases:

trace flag 3608 – bypass instance startup recovery for all database except master (KB 224071)

trace flag 1802 – SQL Server 2005 fix to allow re-attache network share based database files (KB 922804)

Categories: General, Trace Flags Tags:

SQL Server startup parameters (Part 1)

January 11th, 2008 No comments

This is a part one of a mini series on SQL Server startup parameters.

When starting SQL Server, as a service or from a command line, there are several startup parameters that define or change the behavior of that instance. Some of these parameters are well documented in the SQL Server Books Online (Books Online call them startup options) and some are not well documented. I’m going to cover both.

Startup parameters for the SQL Server instance stored in the registry:

SQL Server 2005 registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL       Server\<INSTANCE ID>\MSSQLServer\Parameters

You need to replace an <INSTANCE ID> with the instance id of the SQL Server you are going to configure. Here’s a useful link to the SQL Server 2005 (and 2008) instance naming conventions and instance ids.

SQL Server 2000 registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL       Server\<INSTANCE NAME>\MSSQLServer\Parameters

<INSTANCE NAME> here is a SQL Server 2000 instance name.

and can be set or changed via SQL Server Configuration Manager for the SQL Server 2005 and 2008 (make sure there are no spaces between parameters in the edit line when you type a new one in).

When starting SQL Server instance as a service with the SQL Server Configuration Manager or Windows Services Admin Console only parameters defined in the registry will be used, when starting a service from a command line (net start “<SQL Service name>”) both registry and command line flags will take an effect (make sure to use /flag notation instead of -flag with the net start command) and when starting form a command line, not as a service, registry as well as a command line supplied parameters are used.

or Enterprise Manager for the SQL Server 2000

It’s also possible to set startup parameters by directly editing registry keys, but be careful when doing this and take a backup of the registry data you are going to change.

Lets’s start with parameters that affect global instance configuration, and are very useful for test and troubleshooting:

-c – optimizes startup of the instance when started from a command line (skips source code branches that are related to the startup as a service). This option if oftenly used during SQL Server performance tests to achieve best results (see http://www.tpc.org SQL Server tests disclosure documents for example).

-m – starts an instance in a single-user mode (only one user can connect to the instance), CHECKPOINT process is not started and direct update to system tables is enabled. When using that option make sure that as an administrator you’ll be able to use that single connection, i.e. connect from a local server console, disable remote connections, make sure that applications and users are not able to hijack your single connection. (BTW, one of the SQL Server certification exams has a question related to this startup flag).

-f – starts SQL Server with minimal, default configuration and in a single-user mode. Used for troubleshooting a wrong configuration of the SQL Server.

Categories: General Tags:

SQL Server 2008 CTP5 is available for download

November 20th, 2007 No comments

Latest version of the Microsoft SQL Server 2008 pre-release became available for download yesterday (11/19/2007).

Go ahead and get it from Microsoft Connect web site

Categories: SQL Server Updates Tags:

Microsoft SQL Server Books Online updates

November 18th, 2007 No comments

Microsoft issues updates to the SQL Server documentation, SQL Server Books Online (BOL), on a separate schedule than service packs and critical updates packages, here are the latest ones:

Categories: SQL Server Updates Tags:

Complete SQL Server builds list

November 11th, 2007 1 comment

This is a follow up to the previous post. There are several web sites that used to track SQL Sever build numbers. You can find one list for the SQL Server 2000 here and another, for the SQL Server 2005, here. There is a different source of the SQL Server 2005 builds here. These lists has not been updated for a long time, but are interesting from a historical perspective. Microsoft now maintains information about most recent builds at their support web site, so here they are:

2000 post-SP4 builds list: http://support.microsoft.com/kb/894905

2005 post-SP2 builds list: http://support.microsoft.com/kb/937137

This Microsoft support article, 321185, tells how to identify version of the installed SQL Server and also lists version build numbers for the SQL Server starting from version 6.5.

Categories: SQL Server Updates Tags:

How to install latest build of the SQL Server

November 9th, 2007 No comments

In used to be a very simple answer to the question, install Microsoft SQL Server RTM version (internal Microsoft name for the release version of the product – Release To Manufacturing), and on top of it install latest server pack. It’s not that simple any more if you would like to use the latest version on the product.

Current Microsoft policy for the SQL Server updates described at this KB article: http://support.microsoft.com/kb/935897 and is called an ISM (Incremental Servicing Model). In short there are GDR/CU (general distribution release/cumulative update) and OD/COD (on-demand/critical on-demand) incremental updates. GDR include service packs. GDR (2000 and 2005) and CU (2000 only) are normally available for free via Microsoft download center while CU, OD and COD (2005) have to be requested via support web site (it’s free and do not require support contract with Microsoft) and OD and COD (2000) are available from here and do require support contract with Microsoft.

In short:

SQL Server 2005:

SQL Server 2000:

With no SQL Server 2000 SP5 and SQL Server 2005 SP3 dates available yet, ISM is the only option to stay on top of SQL Server updates. Ones again a word of advise: test, test, test every update on you QA/Test server, before deploying in production.

I track latest CU builds form both SQL Server 2000 and 2005 with links to KB support articles, so come back often to stay on top of changes.

Categories: SQL Server Updates Tags: