Moving the System databases for SQL 2005 on a cluster

Background

Best practices for SQL configuration recommend moving system databases, particularly the TempDB, to a separate drive.  On a cluster, this drive must be a shared cluster resource that is, like all drives supporting the SQL instance, a dependent cluster resource.  While there is no need to separate out the data and log files for system databases, the following instructions will assist in moving system files together to a separate clustered drive.  In the examples below, assume E:\ is a clustered drive resource which can be replaced with whatever specific drive is designed for each particular instance.

1.0 Moving Model Database Files

1. In Cluster Admin, shut down SQL Server and SQL component cluster resources.


2. Navigate to a command prompt on the active node and start the SQL server using startup parameters:

"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" –s<instance name> –c –m –T3608

NOTE:  Cluster resources for drives, virtual server, and IP address must be online. Also, for more information on the startup parameters, see http://technet.microsoft.com/en-us/library/ms190737.aspx and http://technet.microsoft.com/en-us/library/ms188396.aspx

1. Ensure the SQL Browser has been restarted.


2. Open SQL Server Management Studio but cancel the login dialog


3. Close the Object Explorer window.


4. Click on New Query and log in as Admin (i.e., ADMIN:ServerName\InstanceName) using sa account (assuming the sa account is system administrator account).


5. Use the following query:

use master
go
sp_detach_db 'model'
go

1. Move the Model.mdf and Modellog.ldf files from the current location to the desired location.


2. Reattach the model database by using the following commands:

use master
go
sp_attach_db 'model','E \model.mdf','E:\Sqldata\modellog.ldf'
go

(NOTE:  Replace the filepath above with the new location of the files.)

10.  Close SQL Server Management Studio

11.  Shut down SQL by typing Ctrl + C at the command prompt where you started SQL.

12.  Bring SQL Resource online in Cluster Administrator.

13.  Open SQL Server Management Studio (you should be able to log in normally as you are no longer in single-user mode).

14.  Verify the location of the model files by running the following query:

use model
go
sp_helpfile
go

2.0 Moving MSDB Database Files

1. In Cluster Admin, shut down SQL Server and SQL component cluster resources.


2. Navigate to a command prompt on the active node and start the SQL server using startup parameters:

"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" –s<instance name> –c –m –T3608

NOTE: Cluster resources for drives, virtual server, and IP address must be online.

1. Ensure the SQL Browser has been restarted.


2. Open SQL Server Management Studio but cancel the login dialog


3. Close the Object Explorer window.


4. Click on New Query and log in as Admin (i.e., ADMIN:ServerName\InstanceName) using sa account (assuming the sa account is system administrator).


5. Use the following query:

use master
go
sp_detach_db 'msdb'
go

1. Move the Model.mdf and Modellog.ldf files from the current location to the desired location.


2. Reattach the model database by using the following commands:

use master
go
sp_attach_db 'model','E \msdbdata.mdf','E:\Sqldata\msdblog.ldf'
go

(NOTE:  Replace the filepath above with the new location of the files.)

10.  Close SQL Server Management Studio

11.  Shut down SQL by typing Ctrl + C at the command prompt where you started SQL.

12.  Bring SQL Resource online in Cluster Administrator.

13.  Open SQL Server Management Studio (you should be able to log in normally as you are no longer in single-user mode).

14.  Verify the location of the model files by running the following query:

use msdb
go
sp_helpfile
go

3.0 Moving Master Database Files

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager


2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server [MSSQLSERVER]) and select Properties


3. In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.


4. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK.  Moving the error log file is optional.


5. The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter.  The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

1. If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf

1. Stop the instance of SQL Server by right-clicking the instance name and selecting Stop


2. Move the master.mdf and mastlog.ldf files to the new location.


3. Restart the instance of SQL Server

10.  Verify the file change for the master database by running the following query:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO

4.0  Moving TempDB Database Files

1. Determine the logical file names of the tempdb database and their current location on the disk:

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

1. Change the location of each file by using ALTER DATABASE

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLLog\templog.ldf');
GO

1. Stop and restart the instance of SQL Server


2. Verify the file change:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

1. Delete the tempdb.mdf and templog.ldf files from the original location.


2. Add additional tempDB files:

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'X:\tempdb2.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'X:\tempdb3.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'X:\tempdb4.mdf', SIZE = 256);
GO

NOTE: Replace the path and filename with appropriate target names.

Add as many files as there are processors per MS Best Practices.  The final number is discretionary based on the demand model for the system.

Restart the server and verify the functionality and location of tempDB:

use tempdb
go
sp_helpfile
go

Note: don't forget to delete the old tempdb MDF and LDF files. SQL won't do it...

Updating the TEMPDB

Here's a quick/dirty little script to update your tempdb for SQL to 5GB for the tempdev (tempdb.mdf) file and 512MB for the templog (templog.ldf) file while at the same time turning off Auto Growth.

USE [master]

GO

ALTER DATABASE

[tempdb]

MODIFY FILE

(NAME = N'tempdev', SIZE = 5242880KB , FILEGROWTH = 0)

GO

ALTER DATABASE

[tempdb]

MODIFY FILE

(NAME = N'templog', SIZE = 524288KB , FILEGROWTH = 0)

GO

The restart your SQL Server Agent and SQL Server Services. Mine is on a cluster with the instance named SQL1.  If you are running on an MS Cluster, then you should probably do this via the Cluster Administrator console because even though the services may be online, the cluster admin console may report them to be in a failed state.

net stop SQLAgent$SQL1 & net stop MSSQL$SQL1

net start MSSQL$SQL1 & net start SQLAgent$SQL1

And yes, you can run a single command with that little ampersand (&) thingy there. :)

Here's an example of setting a TempDB with the following:

mdf file at 15GB, max size of 17.5GB, and growth at 10%

ldf file at 1.5GB, max size of 1.8 GB, and growth set to 10%

USE [master]
GO
ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = N'tempdev',
SIZE = 15728640KB ,
MAXSIZE = 18350080KB,
FILEGROWTH = 10% )
GO
ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = N'templog',
SIZE = 1572864KB ,
MAXSIZE = 1835008KB,
FILEGROWTH = 10% )
GO

Special note: If you copy/paste the code from the block-quotes above, and then try to run them in SQL Management Studio, you might notice them failing.  Try updating the single quotes from  around the file names, tempdev and templog.

Zen of Attraction

If Less Is More, Then Nothing Is Everything

1. Promise Nothing

Just do what you most enjoy doing.

Hidden benefit: You will always over-deliver.

2. Offer Nothing

Just share what you have with those who express an interest in it.

Hidden benefit: Takes the pressure off of wanting other people to see you as valuable or important.

3. Expect Nothing

Enjoy what you already have. It’s plenty.

Hidden benefit: You will realize how complete your life is already.

4. Need Nothing

Just build up your reserves and your needs will disappear.

Hidden benefit: You boundaries will be extended and filled with space.

5. Create Nothing

Just respond well to what comes to you.

Hidden benefit: Openness.

6. Hype Nothing

Let quality sell by itself.

Hidden benefit: Trustworthiness

7. Plan Nothing

Take the path of least resistance.

Hidden benefit: Achievement will become effortless.

8. Learn Nothing

Let your body absorb it all on your behalf.

Hidden benefit: You will become more receptive to what you need to know in the moment.

9. Become No One

Just be more of yourself.

Hidden benefit: Authenticity.

10. Change Nothing

Just tell the truth and things will change by themselves.

Hidden benefit: Acceptance.

Free/Busy Problems Exchange 2007 and Outlook clients

http://wadingthrough.wordpress.com/2006/07/31/unable-to-clean-local-freebusy-information/

http://forums.msexchange.org/m_1800424559/mpage_1/key_/tm.htm#1800424559

How to reset system folders in Exchange Server 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;822444

How to reset system folders in Exchange Server 2007
http://technet.microsoft.com/en-us/library/bb331972(EXCHG.80).aspx

Eseutil /Y Copy File Mode on non-Exchange Server

The ability of Eseutil to copy large files is a capability introduced in Exchange Server 2003 that has been imported from ESEFile. The copy file mode is optimized to copy very large files efficiently. You can use the switch to copy a database, streaming file, or log file. However, the mode is not suited as a general purpose copy utility.

You can use the Eseutil /Y switch to copy a database, streaming file, or log file. For best speed and stability, you should run Eseutil /Y from a local command prompt on the copy destination server rather than from an intermediate location

From Server 2

ESEUTIL /Y \\server1\d$\priv1.edb /D\\server2\d$\priv1.edb

NOTE! Don't forget to provide the file name at the destination. Otherwise, you will get an error message.

Copying c:\program files\exchsrvr\mdbdata\priv1.edb to c:\Foldername

FAILURE: CreateFile: Access is denied

Eseutil /D Defragmentation Mode
Eseutil /P Repair Mode
Eseutil /C Restore Mode
Eseutil /R Recovery Mode
Eseutil /G Integrity Mode
Eseutil /M File Dump Mode
Eseutil /K Checksum Mode
Eseutil /Y Copy Mode

To run ESEUTIL on a non-Exchange system, follow these steps:

1. Copy Eseutil.exe, Ese.dll, Jcb.dll, Exosal.dll, and Exchmem.dll from Exchange Server's Exchsrvr\bin folder to a folder on the non-Exchange server.


2. Run the desired ESEUTIL commands against the copied database.

Adding the Knowledge Base site template for MOSS 2007

stsadm -o addsolution -filename C:\Bits\KnowledgeBase.wsp

stsadm -o deploysolution -name KnowledgeBase.wsp -allowgacdeployment -immediate

WSS Central Administration site.
Click the Operations tab, and
then click Solution management under global configuration.

After all the solutions are marked Globally Deployed,

run iisreset from the command line.
http://technet.microsoft.com/en-us/windowsserver/sharepoint/bb407286.aspx

Transfer\Move FSMO Roles

http://support.microsoft.com/kb/324801