powermt Manages a PowerPath environment.

powermt check ----Checks for, and optionally removes, dead paths.


powermt Command Syntax

powermt check [class=symmclariionall] [force]
[hba=hba#all] [dev=pathdeviceall]

powermt check_registration
powermt config
powermt display [pathsports] [class=symmclariionall]
[every=seconds]
powermt display [ports] [dev=deviceall]
[class=symmclariionall] [every=seconds]
powermt display options
powermt load [class=symmclariionall] [file=pathname]
powermt remove [class=symmclariionall] [force]
hba=hba#all dev=pathdeviceall
powermt remove [class=symmclariionall] [force]
hba=hba#all dev=pathdeviceall
powermt restore [class=symmclariionall] [force]
[hba=hba#all] [dev=pathdeviceall]
powermt save [file=pathname]
powermt set periodic_autorestore=onoff
[class=symmclariionall]
powermt set mode=activestandby
[class=symmclariionall] [hba=hba#all]
[dev=pathdeviceall]
powermt set policy=lilbrrre
[class=symmclariionall] [dev=deviceall]
powermt set policy=sonr [dev=deviceall] (Symmetrix only)
powermt set policy=bfco [dev=deviceall] (CLARiiON only)
powermt set priority=priority#
[class=symmclariionall] [dev=deviceall]
powermt version
powermt set write_throttle=onoff
[class=symmclariionall] [dev=deviceall]
powermt set write_throttle_queue=queue_depth#
[class=symmclariionall] [dev=pathdeviceall]

Backing up and restoring server clusters

http://technet.microsoft.com/en-us/library/cc786625(WS.10).aspx#BKMK_2

http://technet.microsoft.com/en-us/library/cc739092(WS.10).aspx

Excel

insert time (CTRL+SHIFT+semicolon) and date (CTRL+semicolon)

Full backup progress

Here is what happens on a full database backup

1. lock database blocking all transactions

2. place a mark in the tran log

3. release the database lock

4. back up all data pages in the database

5. lock database, mark transaction log, unlock database

6. write all the transactions between the two marks to the full backup file (but they also stay in the log file)

SQL Server 2005 clustering DM

sys.dm_io_cluster_shared_drives

sys.dm_os_cluster_nodes

cluster.exe Example

Prerequisites
Cluster.exe is part of the default installation of Server 2008. But clusters can only created after the feature “Failover Clustering” has been installed. Otherwise an error message like the following will be displayed

C:\>cluster testcluster /create /ipaddress:192.168.175.22/24


Creating a Cluster:

C:\>cluster testcluster /create /ipaddress:192.168.175.22/24


Deleting/Destroying a Cluster:

C:\>cluster testcluster /destroy /y

Without the optional parameter /CleanupActiveDirectory the cluster’s computer account will not be deleted but only deactivated.


Shutting Down and Restarting a Cluster:

C:\>cluster testcluster /shutdown /y

move group:

cluster.exe Spann group "Cluster Group" /moveto:Shannon

cluster.exe Syntax (windows 2003): http://technet.microsoft.com/en-us/library/cc732694(WS.10).aspx

Sql Server Integration Service in clustering

In Sql Server 2005, you cannot cluster Sql Server Integration Service (SSIS).
During database cluster install, you can pick to install Integration Service. SSIS will only be installed on the node where installation process starts from. For example, suppose you have Node1 and Node2, and the install is started from Node1, then database service and binaries will be installed on both nodes automatically, but SSIS will only be installed on Node1.

Now, suppose the database cluster name is VirtualDb. When it is running on Node1, to connect to Integration Service in Sql Server Management Studio, you can choose to connect to VirtualDb, because under the cover, you are really connecting to Node1, which does have SSIS installed. However, if VirtualDb happens to run on Node2, then you have a problem, because there is no SSIS on Node2. Instead, you will have the message below:

Connect to SSIS Service on machine “VirtualDb” failed:Class not registered——————————BUTTONS:
OK——————————

So, SSIS wise, you can only connect to Node1 or VirtualDb when it is running on Node1, but we are not done yet. You will get an error when you try to browse Stored Packages -> MSDB in Object Explorer. Below is the error you will get:
The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic “Configuring the Integration Services Service” in Server 2005 Books Online.Login timeout expiredAn error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)
——————————BUTTONS:
OK——————————

The reason is that the meta data of package location is controlled by a file located on Node1, and part of it is incorrect. If you browse through the path of Sql Server, look for
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml. Open it up and you will see this piece of code:


Here, you can see that the ServerName is a dot, which means default instance of Sql Server on this local machine. To fix the problem you encountered earlier while browsing packages, you will need to change it to VirtualDb. After that, restart SSIS, and it will work for you in Management Studio. The dot here would have worked, had the database service been a non-clustered service. I do believe this should be treated as a Microsoft bug. Instead of just putting a dot here by the installer, it should put in the virtual database server name, when database service is installed as a cluster.

SSIS in a clustered environment

If you’re configuring SQL Server Integration Services (SSIS) in a clustered server environment, remember to change the value for the element in the SSIS configuration file to the virtual server name on each node of the cluster. If you don’t, SQL Server will probably throw this error:

SSIS MsDtsSrvr connection error
Login timeout expired. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. Named Pipes Provider: Could not open a connection to SQL Server (MsDtsSrvr)

Solution:
Run this query on the machine where you have SSIS installed:


select srvname from master.dbo.sysservers where srvid = 0

Navigate to the nodes of the machine where SSIS is installed and modify the configuration file:%Program Files%Microsoft SQL Server90DTSBinnMsDtsSrvr.ini.xml



You will have to replace the value of the element from a dot to the Virtual Server Name that was returned by the query because the dot resolves to the default SQL Server instance on the local server.

With SQL Server Named instances, change the value for the element in the MsDtsSrvr.ini.xml configuraton file.

Fire up SQL Server Configuration Manager, navigate to SQL Server 2005 Services and then restart SQL Server Integration Services.

Note: You may also have to configure remote access for Dcom Config for the MsDtsServer Properties (Security tab) in Component Services if running with a non-admin account.


Configuring the Integration Services Service : http://msdn.microsoft.com/en-us/library/ms137789.aspx

Configuring Integration Services in a Cluster: http://msdn.microsoft.com/en-us/library/ms345193(loband).aspx

Microsoft Cluster Server (MSCS)

Microsoft Cluster Server (MSCS) uses poll intervals to determine the status of the resources it is protecting.

The "Looks Alive" poll occurs frequently, performing a quick check to verify that the resource is still running, while the "Is Alive" poll performs a more comprehensive check of the resource and occurs less frequently.

Pending Timeout value for the resource. This is the period of time that the resource can be in an Offline Pending or Online Pending state before MSCS fails the resource.

SQL Server database single user mode

MASTER, MSDB and TEMPDB cannot be set into single user mode. For this you need to start the SQL SERVER into single user mode.


A query that initiates an update of out-of-date statistics must wait for those statistics to be updated before compiling and returning a result set. This can cause unpredictable query response times and may cause applications that have aggressive time-outs to fail.

In SQL Server 2005, the database option AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to ON, queries do not wait for the statistics to be updated before compiling. Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan. Queries that start after the updated statistics are ready will use those statistics. This may cause the recompilation of cached plans that depend on the older statistics version. Updating asynchronous statistics cannot take place if any data definition language (DDL) statements, such as CREATE, ALTER, and DROP statements, occur in the same explicit user transaction.


Before you set a database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. If the option is set to ON, perform the following tasks:

1. Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.

Alter database AdventureWorks set AUTO_UPDATE_STATISTICS_ASYNC OFF

2. Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.

select * from
sys.dm_exec_background_job_queue

3. If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.

This example shows how to terminate the statistics update associated with a job where the job_id = 53.

KILL STATS JOB 53;
GO



1.use master
go
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = 'AdventureWorks'
go

2.kill 51

3.sp_dboption 'testdb01', 'single user', 'TRUE'
or
USE master;
GO
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO


PS:
the termination option WITH ROLLBACK IMMEDIATE in the first ALTER DATABASE statement. All incomplete transactions will be rolled back and any other connections to the AdventureWorks sample database will be immediately disconnected.