Moving database files

A very nice article about using Alter Database instead of Detach/Attach when moving databasefiles to new locations.
Written by Jonathan Kehayias.

http://www.sqlservercentral.com/articles/Administration/65896/

You will need to log in to read the article, but hey it’s free!

In short, using the detach/attach method will be deprecated  because it breaks a few things while ALTER DATABASE doesn’t.

Now, go get an account!!

Unable to start Server configuration manager on MS cluster

Ran into a problem where I was unable to open SQL Server Server Configuration Manager and SQL Server Surface Area Configuration on a MS Windows Server 2003 cluster with two nodes and multiple cluster groups.

Short on config:
Two Windows Server 2003 Enterprise Servers clustered (active/active).
Four installations of SQL Server 2005 in four different cluster groups.

The problem: Unable to open Server Configuration Manager and Surface Area Configuration tools on either node.
The problem was found after we added the last three cluster groups with SQL 2005.

Solution found on the internet (where else):
I had to create aliases for each clustered SQL instance in the registry. This task is normally performed from Server Configuration Manager but since I could not use the tool I had to manually edit the registry on each node.

If you run SQL 2005 32-bit you need to add the following key and values:
Key: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
Data: ServerName\Instance
Value: DMMSSOCN,ServerName,1433 (where 1433 is the port used by the instance)
You need to add values for all instances of SQL Server 2005.

If you run SQL 2005 64-bit you need to add the following key and values:
Key: [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\Connec­tTo]
Data: ServerName\Instance
Value: DMMSSOCN,ServerName,1433 (where 1433 is the port used by the instance)
You need to add values for all instances of SQL Server 2005.

MSDE install options

Installation options for MSDE.

setup [/?]

[

[ /i package_file

[ /settings ini_file ]

| [ [ ALLOWXDBCHAINING=1 ]

[ BLANKSAPWD=1 ]

[ CALLBACK=Dllname!CallbackFunctionName ]

[ COLLATION=”collation_name” ]

[ DATADIR=”data_folder_path” ]

[ DISABLENETWORKPROTOCOLS=n ]

[ INSTANCENAME=”instance_name” ]

[ SAPWD=”sa_password” ]

[ SECURITYMODE=SQL ]

[ TARGETDIR=”executable_folder_path” ]

[ UPGRADE=1 ]

[ UPGRADEUSER=admin_login ]

[ UPGRADEPWD=admin_password ]

]

]

[ /L*v [filename] ]

[ /upgradesp { SQLRUN

| SQLRunnn.msi }

]

[ /qn | /qb ]

[ /x package_name ]

]

Arguments
/?