SQL Server upgrade versions

Reference: http://msdn.microsoft.com/en-us/library/ms143393.aspx

Notes from VMware VSphere Tour

In no particular order

• Fault Tolerance (FT) can only be enabled for 1-CPU VMs and must used shared storage.
• Data Recovery (DR) images cannot be backed up to tape.
• With Netapp storage, the have Snap Manager for Virtual Infrastructure (SMVI) that plugs into vCenter APIs to backups for VMs.

SQL Service account requirements

If you don't want to just make your service account(s) part of the local administrators group, here are the user rights that you need to know...

For SQL 2000, a service account requires these user rights:

• Act as Part of the Operating System = SeTcbPrivilege
• Bypass Traverse Checking = SeChangeNotify
• Lock Pages In Memory = SeLockMemory
• Log on as a Batch Job = SeBatchLogonRight
• Log on as a Service = SeServiceLogonRight
• Replace a Process Level Token = SeAssignPrimaryTokenPrivilege

For SQL 2005, it's somewhat more complicated, depending on what service the account is for:

SQL Server

Default instance: SQLServer2005MSSQLUser$ComputerName$MSSQLSERVER
Named instance: SQLServer2005MSSQLUser$ComputerName$InstanceName

• Log on as a service
• Act as part of the operating system (only on Windows 2000)
• Log on as a batch job
• Replace a process-level token
• Bypass traverse checking
• Adjust memory quotas for a process
• Permission to start SQL Server Active Directory Helper
• Permission to start SQL Writer

SQL Server Agent

Default instance: SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER
Named instance: SQLServer2005SQLAgentUser$ComputerName$InstanceName

• Log on as a service
• Act as part of the operating system (only on Windows 2000)
• Log on as a batch job
• Replace a process-level token
• Bypass traverse checking
• Adjust memory quotas for a process

Analysis Services

Default instance: SQLServer2005MSOLAPUser$ComputerName$MSSQLSERVER
Named instance: SQLServer2005MSOLAPUser$ComputerName$InstanceName

• Log on as a service

Reporting Services

Default instance: SQLServer2005ReportServerUser$ComputerName$MSSQLSERVER and SQLServer2005ReportingServicesWebServiceUser$ComputerName$MSSQLSERVER
Named instance: SQLServer2005ReportServerUser$ComputerName$InstanceName and SQLServer2005ReportingServicesWebServiceUser$ComputerName$InstanceName

• Log on as a service

Integration Services

Default or named instance: SQLServer2005DTSUser$ComputerName

• Log on as a service
• Permission to write to application event log
• Bypass traverse checking
• Create global objects
• Impersonate a client after authentication

Full-Text Search

Default instance: SQLServer2005MSFTEUser$ComputerName$MSSQLSERVER
Named instance: SQLServer2005MSFTEUser$ComputerName$InstanceName

• Log on as a service

SQL Server Browser

Default or named instance: SQLServer2005SQLBrowserUser$ComputerName

• Log on as a service

SQL Server Active Directory Helper

Default or named instance: SQLServer2005MSSQLServerADHelperUser$ComputerName

• No special rights

References: http://support.microsoft.com/kb/283811

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

User rights required for IIS

Below are User Rights required for an IIS server:

• Log on Locally
  Administrators
  IUSR_ (Anonymous)
  Users (authentication required)
• Access this computer from the Network
  Administrators
  ASPNet (.NET Framework)
  IUSR_ (Anonymous)
  IWAM_
  Users
• Log on as a Batch Job
  ASPNet
  Network
  IUSR_
  IWAM_
  Service
• Logon as a Service
  ASPNet
  Network
• Bypass Traverse Checking
  Administrators
  IUSR_ (Anonymous)
  Users (Basic, Integrated, Digest)
  IWAM_

Reference: http://support.microsoft.com/kb/271071

Moving event log files

To move Event Viewer log files to another location on the hard disk, follow these steps:

1. Click Start, and then click Run.
   
2. In the Open box, type regedit, and then click OK.
   
3. Locate and click the following registry key:
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog
   
4. Click the subkey that represents the event log that you want to move, for example, click Application.
   
5. In the right pane, double-click File.
   
6. Type the complete path to the new location (including the log file name) in the Value data box, and then click OK. For example, if you want to move the application log (Appevent.evt) to the Eventlogs folder on the E drive, type e:\eventlogs\appevent.evt.
   
7. Repeat steps 4 through 6 for each log file that you want to move.
   
8. Click Exit on the Registry menu.
   
9. Restart the computer.

Reference: http://support.microsoft.com/kb/315417

Automatic backup of event log files

At registry entery: HKLM/System/CurrentControlSet/Services/Eventlog:

• Add regkey: AutoBackupLogFiles <> 0 to logfile you want to setup auto-backup for.

Reference: http://msdn.microsoft.com/en-us/library/aa363648(VS.85).aspx

Multiple IP websites on a single server

Reference: http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/b77cf015-017f-489c-9b5b-65ca4a679392.mspx?pf=true

Problem with mapped drive not showing up

Found out problem was related to Windows XP Professional Fast Logon Optimization feature. As a result, Windows XP does not wait for the network to be fully initialized at startup and logon. Existing users are logged on using cached credentials. This results in shorter logon times, but the problem was that account profile mapped drives did not show up for the logged on user.

To fix:

• Use gpedit.msc to turn off Fast Logon Optimization
• Go to the policy setting and ENABLE:
  Computer Configuration\Administrative Templates\System\Logon\ Always wait for the network at computer startup and logon

Reference: http://support.microsoft.com/kb/305293

Configuring SQL to use more than 2GB RAM

Reference: http://support.microsoft.com/kb/274750

Large memory support in Windows 2000/2003

PAE is the added ability of the IA32 processor to address more than 4 GB of physical memory. The following operating systems can use PAE to take advantage of physical memory beyond 4 GB:

• Microsoft Windows 2000 Advanced Server
• Microsoft Windows 2000 Datacenter Server
• Microsoft Windows Server 2003, Enterprise Edition
• Microsoft Windows Server 2003, Datacenter Edition

To enable PAE, use the /PAE switch in the Boot.ini file.

The exception is when the /3GB switch is used in conjunction with the /PAE switch. In this case, the operating system does not use any memory in excess of 16 GB. This behavior is caused by kernel virtual memory space considerations. Thus, if the system restarts with the /3GB entry in the Boot.ini file, and the system has more than 16 GB of physical memory, the additional physical random access memory (RAM) is not used by the operating system. Restarting the computer without the /3GB switch enables the use of all the physical memory.

Reference: http://support.microsoft.com/kb/283037

Connect to console session via Terminal Services

From the command prompt, enter mstsc -v:servername /F -console to connect as console to a remote server.

• -v: - indicates the remote server
• /F - use full screen mode
• -console - connect as console

If someone is currently logged into the console, you will see the following message:

The user domain\username is logged locally on to this computer. The user has been idled for number minutes. The desktop is unlocked. If you continue, this user's session will end and any unsaved data will be lost. Do you want to continue?

If you continue, the current console user is logged off and remote server will be locked until you end your remote session.

Reference: http://support.microsoft.com/kb/278845

Automatically running CD-ROMs

Use regedit and change key:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\CDRom

• Autorun = 0 to disable
• Autorun = 1 to enable

Close regedit and restart computer for change to apply.

Testing SMTP Communication

1. At a command prompt, type telnet, and then press ENTER. This command opens the Telnet session.
   
2. Type set localecho and then press ENTER. This optional command lets you view the characters as you type them. This setting may be required for some SMTP servers.
   
3. Type set logfile . This optional command enables logging of the Telnet session to the specified log file. If you only specify a file name, the location of the log file is the current working directory. If you specify a path and a file name, the path must be local to the computer. Both the path and the file name that you specify must be entered in the Microsoft DOS 8.3 format. The path that you specify must already exist. If you specify a log file that doesn't exist, it will be created for you.
   
4. Type open mail1.fabrikam.com 25 and then press ENTER.
   
5. Type EHLO contoso.com and then press ENTER.
   
6. Type MAIL FROM:chris@contoso.com and then press ENTER.
   
7. Type RCPT TO:kate@fabrikam.com NOTIFY=success,failure and then press ENTER. The optional NOTIFY command defines the particular delivery status notification (DSN) messages that the destination SMTP server must provide to the sender. DSN messages are defined in RFC 1891. In this case, you are requesting a DSN message for successful or failed message delivery.
   
8. Type DATA and then press ENTER. You will receive a response that resembles the following:
   354 Start mail input; end with .
   
9. Type Subject: Test from Contoso and then press ENTER.
   
10. Press ENTER. RFC 2822 requires a blank line between the Subject: header field and the message body.
    
11. Type This is a test message and then press ENTER.
    Press ENTER, type a period ( . ) and then press ENTER. You will receive a response that resembles the following:
    250 2.6.0 Queued mail for delivery
    
12. To disconnect from the destination SMTP server, type QUIT and then press ENTER. You will receive a response that resembles the following:
    221 2.0.0 Service closing transmission channel
    
13. To close the Telnet session, type quit and then press ENTER.
    

Reference: http://technet.microsoft.com/en-us/library/bb123686.aspx

Minimum rights required to run a scheduled task

Assumptions:

• Applies to Windows 2003 server
• Service account is not a member of the local administrators group
• Batch job runs as non-interactive process

Required for service account:

• RX permissions to c:\windows\system32\Cmd.exe
• Any share and NTFS permissions it needs to access files or data
• "Logon as a service" configured in the security policy editor (run gpedit.msc -> computer configuration -> windows settings -> security settings -> local policies -> user rights assignment -> Log on as a service)
• "Log on as a batch job" configured similarly using gpedit.msc

Reference: http://support.microsoft.com/kb/867466/en-us