SQL 2005 Standard Edition on Cluster

SQL Server 2005 Standard Edition will support two-node MSCS,
but Windows 2003 Server Standard won't:

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

Idera SQL Safe Backup Script

DECLARE @counter INT
DECLARE @maxCount INT
DECLARE @result INT
DECLARE @failed INT
DECLARE @dbname NVARCHAR(128)
DECLARE @backupSetName NVARCHAR(256)
DECLARE @backupSetDescription NVARCHAR(512)

SET @counter = 1
SET @maxCount = 1
SET @result = 0
SET @failed = 0

-- Create the temporary database table
IF object_id('tempdb..#databases') IS NOT NULL DROP TABLE #databases

CREATE TABLE #databases
(
id INT IDENTITY,
databaseName NVARCHAR(128),
backupSetName NVARCHAR(256),
backupSetDescription NVARCHAR(512)
)

-- Insert the databases to be backed up
INSERT INTO #databases VALUES ('User_DB1', 'Full backup for policy Backup_USER_DB_Full(Noencriptions)', '')
INSERT INTO #databases VALUES ('User_DB2', 'Full backup for policy Backup_USER_DB_Full(Noencriptions)', '')
SELECT @maxCount = MAX(id) FROM #databases

-- Loop through and backup each database
WHILE @counter <= @maxCount
BEGIN

SELECT @dbname = [databaseName], @backupSetName = [backupSetName], @backupSetDescription = [backupSetDescription] FROM #databases WHERE id = @counter


EXEC @result = [master].[dbo].[xp_ss_backup] @database = @dbname,@filename = 'D:\SQLData\MSSQL\BACKUP\IderaSQLBackups\USER_DB_BACKUPS\FULL\%instance%_%database%_%timestamp%.safe',@delete = '1days',@backupname = @backupSetName,@desc = @backupSetDescription,@verify = 1,@compressionlevel = 'ispeed',@threads = 3
IF (@result != 0) SET @failed = 1

SET @counter = @counter + 1

END

-- If one or more failures occurred, signal an error
IF (@failed != 0) RAISERROR('One or more backups failed to complete.', 16, 1)

SQL Server 2008 Service Pack 1 released

SQL Server 2008 Service Pack 1 (SP1) is now available at the following URL :
You can use these packages to upgrade any SQL Server 2008 edition.

SQL Server 2008 SP1 is primarily a roll-up of previous cumulative updates, quick fix engineering updates and minor fixes made in response to requests reported through the SQL Server community. While there are no new features in this service pack, customers running SQL Server 2008 should download and install SP1 to take advantage of the fixes which increase supportability and stability of SQL Server 2008.

With this service pack, Microsoft has made investments to ease deployment and make management of service packs more efficient. Key improvements in SP1 include Slipstream, Service Pack Uninstall and Report Builder 2.0 click-once deployment.
• Slipstream allows administrators to install SQL Server 2008 and Service Pack 1 in a single instance. This decreases the total time for an installation, including a fewer number of reboots thereby increasing productivity and deployment availability.
• Service Pack Uninstall allows administrators to uninstall the service pack separately from the database release. This feature also improves DBA productivity, reduces the cost of deployment and improves overall supportability
• Report Builder 2.0 Click Once improves the existing SQL Server end-user report authoring application by easing deployment to business users

For more information about SQL Server 2008 Service Pack 1, please review the Release Notes :

Cluster Diagnostics and Verification Tool (ClusDiag)

Cluster Diagnostics and Verification Tool (ClusDiag) is a graphical tool that performs diagnostics tests on a pre-production server cluster and creates log files to help system administrators identify configuration issues prior to deployment in a production environment.

Download details: Cluster Diagnostics and Verification Tool (ClusDiag.exe)

SQL 2005 SP2 templog is set to restricted and will not go back to unrestricted

It is only a presentation issue.

There's no such thing as "unlimited" in the end, since the max theoretical size
• for a data file is 32TB and
• for a log file is 2TB. 2,097,152 MB = 2 TB.
I.e., the value you see is the maximum theoretical size.

Although file size is displaying as "restricted", the MAXSIZE is indeed 2TB (2,097,152MB).
So, within the confines of capacity limitations, it is unlimited so to speak.

Working with tempdb in SQL Server 2005

SQLDebugger account

SQL Server 2000 SP3 Creates a SQLDebugger Windows User Account

Removing of the SQLDebugger account in SQL 2000

Automate SQL Server Profiler Trace

-- To create trace
exec @rc = sp_trace_create @TraceID output, 2, N'N:\SQLTrace\15FebTrace', @maxfilesize, NULL

## Note Option 2 here

--- To set trace status

exec sp_trace_setstatus @traceid, @status

where @status = 0 Stopped
1 Running
2 Closed, cleaned from server

---
To get the Traces running on the server

select * from ::fn_trace_getinfo(0)

---
To read trace content into a table
select * from ::fn_trace_gettable('N:\SQLTrace\15FebTrace.trc',default)

Terminal Server and Citrix Keyboard Shortcuts

Terminal Server and Citrix Keyboard Shortcuts

If working in an RDP or ICA Session the following are keyboard shortcut key combinations that map to the native Windows shortcut keystrokes.

CTRL+F3 Task Manager
CTRL+F2 Start Menu

SQL Profiler buffers the trace data in temporary files on the client machine

SQL Profiler buffers the trace data in temporary files on the client machine, and if the drive on which these temporary files reside doesn't have ample free space, a Profiler trace (e.g. with the standard trace template) could deplete the drive of the free space rather quickly and end up disabling the trace.


System is low on disk space on drive 'C'. All SQL Server profiler functions are temporarily disabled.

The temporary files to buffer the profiler trace data were in directory C:\Documents and Settings\Siva\local settings\Temp\1, and the names of the temporary files had this pattern: Prf*.tmp. Upon exiting SQL Profiler, these temporary files would be removed automatically.

Work around:

To work around this problem, do not use SQL Profiler to perform a client-side trace of the events in the instance of SQL Server. Instead, use system stored procedures to perform a server-side trace of the events.

You can use the following system stored procedures:

* sp_trace_create
* sp_trace_setevent
* sp_trace_setfilter
* sp_trace_setstatus

For more information about these system stored procedures, search for the stored procedures in SQL Server 2000 Books Online or in SQL Server 2005 Books Online.

Reference:

An instance of SQL Server performs slowly when you use SQL Profiler to perform a client-side trace of the events in the instance

Error while setting reporting service and share point exclusion path

Encountered the error while installing SQL 2005 Reporting Service "Error while setting reporting service and share point exclusion path"

Product : SQL Server 2005 Reporting Services

Error : The setup has encountered an unexpected error while Setting reporting service and share point exclusion path. The error is: Fatal error during installation.

Resolution:
KB ID # 920803

Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.)

System.Data.SqlClient.SqlError: Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)


Then, I did a Check Name for "SQL" and added the following two SQL accounts from the list:

1)  SQLServer2005MSSQLUser$<machine name>$MSSQLSERVER

2)  SQL

Unicenter command to check the service

Unicenter command to check the service is installed on running on a server

awservices status

Multiple SQL Server Integration Services (SSIS) database instances on one machine

The registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile specifies
the location and name for the configuration file that Integration
Services service uses. The default value of the registry key is
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvr.ini.xml.
You can update the value of the registry key to use a different name
and location for the configuration file.

Reference: Multiple SQL Server Integration Services (SSIS) database instances on one machine - Mike's Blog

SQL Server 2005 Service Pack 3 released

On Dec 15th 2008, Microsoft released the latest Service Pack 3 on SQL Server 2005. On the same day SP3 Cumulative Update 1 and Cumulative Update 11 for SQL Server 2005 Service Pack 2 (SP2) are also released.

• For SQL users not opting for SP3, Microsoft released Cumulative Update 11 for SQL Server 2005 SP2, which contains all of the hot fixes for SP2, without the new features in SP3.
  
• For SQL users upgrading from SQL Server 2005 SP2 Cumulative Update 10 or from SQL Server 2005 SP2 Cumulative Update 11, they must apply a post-SP3 cumulative update after upgrade to SP3 to obtain all the fixes
  

This Service Pack is available for download from Microsoft Download Center. Along with the Service Pack, refreshed the Feature Pack for 2005 that can be downloaded separately. The following are all the download links for SQL Server 2005 Service Pack 3 and related downloads.

SQL Server 2005 SP3 (Eval, Std, Dev, Ent Editions)	http://go.microsoft.com/fwlink/?LinkId=135960
SQL Server 2005 Express Edition SP3	http://go.microsoft.com/fwlink/?LinkId=135970
SQL Server 2005 Express Advanced SP3	http://go.microsoft.com/fwlink/?LinkId=135973
SQL Server 2005 Express Toolkit SP3	http://go.microsoft.com/fwlink/?LinkId=135974
SQL Server 2005 Feature Pack Dec 2008	http://go.microsoft.com/fwlink/?LinkId=135971
SQL Server 2005 Management Studio Express SP3	http://go.microsoft.com/fwlink/?LinkId=135772
SQL Server 2005 SP3 Data Mining Add-ins Page	http://go.microsoft.com/fwlink/?LinkId=135959
SQL Server 2005 SP3 Reporting Services Add-in Page for Microsoft Sharepoint	http://go.microsoft.com/fwlink/?LinkId=135962
SQL Server 2005 Books Online Refresh - December 2008	http://go.microsoft.com/fwlink/?LinkId=130167
What's New in SQL Server 2005 SP3	http://go.microsoft.com/fwlink/?LinkId=131442
Readme files for SQL Server 2005 SP3	http://go.microsoft.com/fwlink/?LinkId=130542
Release Notes for SQL Server 2005 SP3	http://go.microsoft.com/fwlink/?LinkId=12952
Cumulative update package 1 for SQL Server 2005 Service Pack 3	http://support.microsoft.com/kb/959195
An Incremental Servicing Model - ISM is available from the SQL Server team to deliver hotfixes for reported problems	http://support.microsoft.com/kb/935897

SQL Server 2000 SP3 Creates a SQLDebugger Windows User Account

INF: SQL Server 2000 SP3 Creates a SQLDebugger Windows User Account

The SQLDebugger Windows user account is created when the SQL Debugger Registry2 DCOM server process (Sqldbreg2.exe) is registered. By default, SQL Server 2000 SP3 registers this process, and the SQLDebugger Windows user account is created.


SQL Query Analyzer includes
T-SQL Debugger. By using T-SQL Debugger, you can control and monitor
how stored procedures run. T-SQL
Debugger uses the SQLDebugger Windows user account to connect to the database server.

Microsoft Visual Studio .NET applications use SQL Server Debugging to debug SQL Server stored procedures. The SQLDebugger Windows
user account is also created when you install Visual Studio
.NET.

The SQLDebugger Windows user account has the following characteristics :

• It belongs to the built-in Windows Users group.
• It does not have local logon rights.

How to remove / uninstall SQL Server 2005 Manually

Run the command to uninstall the SQL Server components
At a command prompt, run the following command:

"%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove"

Uninstall the SQL Server components one at a time until all the SQL Server components are uninstalled.

Note Add or Remove Programs also runs the ARPWrapper.exe program by using the /Remove option. However, the reference to the ARPWrapper.exe program may have been deleted.

Refer: http://support.microsoft.com/kb/909967

SQL Server 2005 - Cluster Tips

1. Installing Tools and Documentation
SQL Server 2005 Setup no longer installs the tools and documentation components of SQL Server 2005 on all the nodes of a cluster. The wizard will only install these
components on the node that the Setup routine is initiated from. If tools and documentation is required on the other nodes, you will need to install these components
manually after finishing the setup of the cluster.

2. Install SQL 2005 Analysis Services on separate Resource group
Microsoft recommends installing Analysis Services clustered installations in a single group with its own disk and IP resources and not on a resource group that has
the database engine installed on it.

3. Create Windows Domain Groups
It is usually recommended that you create four different domain groups when creating a cluster. One for each of the following:
1. SQL Server 2005 DB Service,
2. SQL Server 2005 Agent service,
3. SQL Server 2005 Full-Text Search service,
4. SQL Server 2005 Analysis Services service.

4.Installing Reporting Services
Unfortunately, Reporting Services is not cluster-aware and will have to be installed as stand-alone components on all nodes in the clustered environments. Reporting Services can make use of clustered databases. But when a node fails over the connections to any Reporting Services, instances on the failed node will not be automatically reconnected to the new node. Connections will have to attempt to connect to the new node using a new IP address

5. Installing SQL Server Integration Services
To install SSIS, you must install SSIS on all active nodes and then make the SSIS Service a clustered resource. To make SSIS a clustered resource, perform the following tasks:
1. Open the Cluster Administrator
2. On the File menu, point to New and then click Resource
3. On the New Resource page of the Resource Wizard, type a Name and choose “Generic Service” as the Service Type. Change the value of Group to SQL group. Click Next.
4. On the Possible Owners page, add or remove the nodes of the cluster as the possible owners of the resource. Click Next.
5. To add dependencies on the Dependencies page,select a resource under Available resources, and thenclick Add. In case of a failover, both SQL Server 2005
and the shared disk that stores Integration Services packages should come back online before Integration Services is brought online. After you have selected the
dependencies, click Next.
6. On the Generic Service Parameters page, enter MsDtsServer as the name of the service. Click Next.
7. On the Registry Replication page, click Add to add the registry key that identifies the configuration file for the Integration Services service.
a. This file needs to be located on a shared disk that is in the same group for the Integration Services service, and fails over to the next node along with for
the Integration Services service.
b. In the Registry Key dialog box, type SOFTWARE\Microsoft\MSDTS\ServiceConfigFile. Click OK, and then click Finish. The Integration Services service
has now been added as a clustered service.
8. Locate the configuration file at %ProgramFiles%\Microsoft SQL Server 2005\90\DTS\Binn\MsDtsSrvr.ini.xml. Copy it to the shared disk.
9. Create a new folder named Packages on the shared disk. Grant List Folders and Write permissions on the new folder to the built-in Users group.
10. Open the configuration file from the shared disk in a text or XML editor. Change the value of the ServerName element to the name of the virtual SQL Server 2005 which is in the same group.
11. Change the value of the StorePath element to the fully-qualified path of the Packages folder created on the shared disk in a previous step: ??:\Packages.
12. Update the value of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile in the Registry to the fully-qualified path and filename of
the service configuration file on the shared disk
13. In the Cluster Administrator, select the Integration Services service, right-click, and select Bring Online from the popup menu. The Integration Services service
is now online as a clustered service

How to Grant ALTER TRACE permission using Management Studio?

In SQL Server 2005 / 2008 to grant SQL Profiler access for a user;

Security tab --> Logins --> right click name to modify select properties --> securables tab --> Click add --> Select the server you want to add the permissions to. The permissions should appear in the effective permissions list


USE master;
GRANT ALTER TRACE TO [Username];

-->Where User Name for domain account is [Domain\accountname];

PMP - Formula

DOS Batch file to create a file name in data stamp

:: 2. Set logfile name based on today's date

for /F "tokens=1,2,3,4 delims=/ " %%I in ('date /T') do set Logfile=%%J%%K%%L.log

:: 4. Delete all but 7 most recent log files

:: Get a sorted list of the log files oldest at the bottom of the list

Dir /b /o-d %Logfolder%\*.log > %Workfolder%\%LogList%

:: The find command counts how many log files there are and for sets TempVar with this value

For /f %%A in ('Dir /b /o %Logfolder%\*.log^| Find /c /i "log"') do set TempVar=%%A

:: If there are more logs than needed skip the number to keep and delete the oldsest one(s)

If %TempVar% GTR %LogsToKeep% For /f "skip=%LogsToKeep%" %%B in (%Workfolder%\%LogList%) do Del /q %Logfolder%\%%B
Del /q %Workfolder%\%LogList%