Wednesday, December 21, 2011

Row and Page Compression in SQL Server 2008

Row compression is used to minimize storage at the row level by compressing fixed-length data types.


Page compression is used to minimize storage for redundant data stored in pages.

Thursday, December 8, 2011

Wednesday, December 7, 2011

Clone table in SQL Server

SELECT *
INTO MyNewTable
FROM MyTable

Thursday, December 1, 2011

XML Query in SQL Server

DECLARE @prodList xml =''



'';

WITH XMLNAMESPACES(DEFAULT 'urn:Wide_World_Importers/schemas/Products')
SELECT prod.value('./@Name','varchar(100)'), prod.value('./@Category','varchar(20)'), prod.value('./ @Price','money')
FROM @prodList.nodes('/ProductList/Product') ProdList(prod);

Transactions statements in SQL Server

SET IMPLICIT_TRANSACTIONS

SET REMOTE_PROC_TRANSACTIONS

SET TRANSACTION ISOLATION LEVEL


SET XACT_ABORT

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server.

Tuesday, November 29, 2011

Isolation Level of SSIS

ReadUncommited: Does not lock the records being read. This means that an uncommitted change can be read and then rolled back by another client, resulting in a local copy of a record that is not consistent with what is stored in the database. This is called a dirty read because the data is inconsistent.

Chaos: Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.

ReadCommitted: Locks the records being read and immediately frees the lock as soon as the records have been read. This prevents any changes from being read before they are committed, but it does not prevent records from being added, deleted, or changed by other clients during the transaction.

RepeatableRead: Locks the records being read and keeps the lock until the transaction completes. This ensures that the data being read does not change during the transaction.

Serializable: Locks the entire data set being read and keeps the lock until the transaction completes. This ensures that the data and its order within the database do not change during the transaction.

Snapshot: The data read within a transaction will never reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins. No locks are placed on the data when it is read.

Thursday, November 24, 2011

CUBE, ROLLUP, GROUPING SETS

Grouping Sets in SQL Server 2008

http://blogs.msdn.com/b/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx

Aggregation WITH ROLLUP

http://blogs.msdn.com/b/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx

Aggregation WITH CUBE

http://blogs.msdn.com/b/craigfr/archive/2007/09/27/aggregation-with-cube.aspx

Thursday, November 3, 2011

SQL Server Error logs

11/03/2011 05:43:44,spid9s,Unknown,
SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on
file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb2.ndf] in database [tempdb] (2).
The OS file handle is 0x0000000000001358. The offset of the latest long I/O is: 0x0000002e040000

Wednesday, November 2, 2011

About SSMS of SQL Server

SSMS 2005 can NOT connect to SSIS 2008
SSMS 2008 can NOT connect to SSIS 2005

Tuesday, September 13, 2011

Restore Transaction Log Backup in SQL Server 2005

DECLARE @i Int
DECLARE @String NVARCHAR(1000)
DECLARE @String1 NVARCHAR(1000)
Declare @str NVarchar(1000)
SET NOCOUNT ON


set @i=1100
While @i<=1119
begin

set @String1='RESTORE LOG [Hermes7] FROM DISK = N''D:\Temp\Hermes\Corporate_Hermes_backup_201109'
Set @string='30.trn'' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 '
set @Str=@string1+Cast(@i as Nchar(4))+@string
set @i=@i+1
print @Str
EXECUTE sp_executesql @Str


end

Thursday, September 8, 2011

Vi

Skip Navigation
University of Washington
Search | Directories | Reference Tools
UW Home > Discover UW > IT Connect > Unix
How to Use the vi Editor*

*Copyright 1991 by Simon Fraser University. Reprinted with permission.

The vi editor is available on almost all Unix systems. vi can be used from any type of terminal because it does not depend on arrow keys and function keys--it uses the standard alphabetic keys for commands.

vi (pronounced "vee-eye") is short for "vi"sual editor. It displays a window into the file being edited that shows 24 lines of text. vi is a text editor, not a "what you see is what you get" word processor. vi lets you add, change, and delete text, but does not provide such formatting capabilities as centering lines or indenting paragraphs.

This help note explains the basics of vi:

opening and closing a file
moving around in a file
elementary editing

vi has many other commands and options not described here. The following resources can help you get started using the vi editor, and are available at the UW University Book Store:

"vi Tutorial." Specialized Systems Consultants (SSC).
"vi Reference." Specialized Systems Consultants (SSC).
"Learning the vi Editor." Linda Lamb, 1990.

Starting vi

You may use vi to open an already existing file by typing

vi filename

where "filename" is the name of the existing file. If the file is not in your current directory, you must use the full pathname.

Or you may create a new file by typing

vi newname

where "newname" is the name you wish to give the new file.

To open a new file called "testvi," enter

vi testvi

On-screen, you will see blank lines, each with a tilde (~) at the left, and a line at the bottom giving the name and status of the new file:
~

"testvi" [New file]

vi Modes

vi has two modes:

command mode
insert mode

In command mode, the letters of the keyboard perform editing functions (like moving the cursor, deleting text, etc.). To enter command mode, press the escape key.

In insert mode, the letters you type form words and sentences. Unlike many word processors, vi starts up in command mode.
Entering Text

In order to begin entering text in this empty file, you must change from command mode to insert mode. To do this, type

i

Nothing appears to change, but you are now in insert mode and can begin typing text. In general, vi's commands do not display on the screen and do not require the Return key to be pressed.

Type a few short lines and press at the end of each line. If you type a long line, you will notice the vi does not word wrap, it merely breaks the line unceremoniously at the edge of the screen.

If you make a mistake, pressing or may remove the error, depending on your terminal type.
Moving the Cursor

To move the cursor to another position, you must be in command mode. If you have just finished typing text, you are still in insert mode. Go back to command mode by pressing . If you are not sure which mode you are in, press once or twice until you hear a beep. When you hear the beep, you are in command mode.

The cursor is controlled with four keys: h, j, k, l.

Key Cursor Movement
--- ---------------

h left one space
j down one line
k up one line
l right one space

When you have gone as far as possible in one direction, the cursor stops moving and you hear a beep. For example, you cannot use l to move right and wrap around to the next line, you must use j to move down a line. See the section entitled "Moving Around in a File" for ways to move more quickly through a file.
Basic Editing

Editing commands require that you be command mode. Many of the editing commands have a different function depending on whether they are typed as upper- or lowercase. Often, editing commands can be preceded by a number to indicate a repetition of the command.
Deleting Characters

To delete a character from a file, move the cursor until it is on the incorrect letter, then type

x

The character under the cursor disappears. To remove four characters (the one under the cursor and the next three) type

4x

To delete the character before the cursor, type

X (uppercase)

Deleting Words

To delete a word, move the cursor to the first letter of the word, and type

dw

This command deletes the word and the space following it.

To delete three words type

3dw

Deleting Lines

To delete a whole line, type

dd

The cursor does not have to be at the beginning of the line. Typing dd deletes the entire line containing the cursor and places the cursor at the start of the next line. To delete two lines, type

2dd

To delete from the cursor position to the end of the line, type

D (uppercase)

Replacing Characters

To replace one character with another:

Move the cursor to the character to be replaced.
Type r
Type the replacement character.

The new character will appear, and you will still be in command mode.
Replacing Words

To replace one word with another, move to the start of the incorrect word and type

cw

The last letter of the word to be replaced will turn into a $. You are now in insert mode and may type the replacement. The new text does not need to be the same length as the original. Press to get back to command mode. To replace three words, type

3cw

Replacing Lines

To change text from the cursor position to the end of the line:

Type C (uppercase).
Type the replacement text.
Press .

Inserting Text

To insert text in a line:

Position the cursor where the new text should go.
Type i
Enter the new text.

The text is inserted BEFORE the cursor.

4. Press to get back to command mode.
Appending Text

To add text to the end of a line:

Position the cursor on the last letter of the line.
Type a
Enter the new text.

This adds text AFTER the cursor.

4. Press to get back to command mode.
Opening a Blank Line

To insert a blank line below the current line, type

(lowercase)

To insert a blank line above the current line, type

O (uppercase)

Joining Lines

To join two lines together:

Put the cursor on the first line to be joined.
Type J

To join three lines together:

Put the cursor on the first line to be joined.
Type 3J

Undoing

To undo your most recent edit, type

u

To undo all the edits on a single line, type

U (uppercase)

Undoing all edits on a single line only works as long as the cursor stays on that line. Once you move the cursor off a line, you cannot use U to restore the line.
Moving Around in a File

There are shortcuts to move more quickly though a file. All these work in command mode.

Key Movement
--- --------

w forward word by word
b backward word by word
$ to end of line
0 (zero) to beginning of line
H to top line of screen
M to middle line of screen
L to last line of screen
G to last line of file
1G to first line of file
f scroll forward one screen
b scroll backward one screen
d scroll down one-half screen
u scroll up one-half screen

Moving by Searching

To move quickly by searching for text, while in command mode:

Type / (slash).
Enter the text to search for.
Press .

The cursor moves to the first occurrence of that text.

To repeat the search in a forward direction, type

n

To repeat the search in a backward direction, type

N

Closing and Saving a File

With vi, you edit a copy of the file, rather than the original file. Changes are made to the original only when you save your edits.

To save the file and quit vi, type

ZZ

The vi editor editor is built on an earler Unix text editor called ex. ex commands can be used within vi. ex commands begin with a : (colon) and end with a . The command is displayed on the status line as you type. Some ex commands are useful when saving and closing files.

To save the edits you have made, but leave vi running and your file open:

Press .
Type :w
Press .

To quit vi, and discard any changes your have made since last saving:

Press .
Type :q!
Press .

Command Summary

STARTING vi

vi filename edit a file named "filename"
vi newfile create a new file named "newfile"

ENTERING TEXT

i insert text left of cursor
a append text right of cursor

MOVING THE CURSOR

h left one space
j down one line
k up one line
l right one space

BASIC EDITING

x delete character
nx delete n characters
X delete character before cursor
dw delete word
ndw delete n words
dd delete line
ndd delete n lines
D delete characters from cursor to end of line
r replace character under cursor
cw replace a word
ncw replace n words
C change text from cursor to end of line
o insert blank line below cursor
(ready for insertion)
O insert blank line above cursor
(ready for insertion)
J join succeeding line to current cursor line
nJ join n succeeding lines to current cursor line
u undo last change
U restore current line

MOVING AROUND IN A FILE

w forward word by word
b backward word by word
$ to end of line
0 (zero) to beginning of line
H to top line of screen
M to middle line of screen
L to last line of screen
G to last line of file
1G to first line of file
f scroll forward one screen
b scroll backward one screen
d scroll down one-half screen
u scroll up one-half screen
n repeat last search in same direction
N repeat last search in opposite direction

CLOSING AND SAVING A FILE

ZZ save file and then quit
:w save file
:q! discard changes and quit file

UW Logo
UW Information Technology
UW Information Technology
help@uw.edu
Modified: October 3, 2007
Privacy Terms

For each in SQL Server

Exec sp_MSforeachdb 'Select ''[?]'',* From ?..sysobjects where name like ''form%'''

Monday, August 22, 2011

Solaris 10 Certificates




Oracle Solaris 10 System Administrator

1Z0-877
Oracle Solaris 10 System Administrator Certified Professional Exam, Part I (SA-200-S10)

1Z0-878
Oracle Solaris 10 System Administrator Certified Professional Exam, Part II (SA-202-S10)



Oracle Certified Expert, Oracle Solaris 10 Security Administrator

1Z0-881
Oracle Solaris 10 Security Administrator Certified Expert Exam





Oracle Certified Expert, Oracle Solaris 10 Network Administrator

1Z0-880
Oracle Solaris 10 Network Administrator Certified Expert (SA-300-S10)

Solaris 10 ZFS Administration (SA-2290-S10)

+ Oracle Solaris 10 System Administrator

Friday, August 12, 2011

Solaris10 + Oracle 11gR2 64bit ---dbshut script

#!/bin/sh
#
# $Id: dbshut.sh 22-may-2008.05:19:31 arogers Exp $
# Copyright (c) 1991, 2008, Oracle. All rights reserved.
#

###################################
#
# usage: dbshut $ORACLE_HOME
#
# This script is used to shutdown ORACLE from /etc/rc(.local).
# It should ONLY be executed as part of the system boot procedure.
#
# This script will shutdown all databases listed in the oratab file
# whose third field is a "Y" or "W". If the third field is set to "Y" and
# there is no ORACLE_SID for an entry (the first field is a *),
# then this script will ignore that entry.
#
# This script requires that ASM ORACLE_SID's start with a +, and
# that non-ASM instance ORACLE_SID's do not start with a +.
#
# Note:
# Use ORACLE_TRACE=T for tracing this script.
# Oracle Net Listener is also shutdown using this script.
#
# The progress log for each instance shutdown is logged in file
# $ORACLE_HOME/shutdown.log.
#
# On Solaris
# ORATAB=/var/opt/oracle/oratab
#
# To configure, update ORATAB with Instances that need to be shutdown
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
# An example entry:
# main:/usr/lib/oracle/emagent_10g:Y
#
#####################################

trap 'exit' 1 2 3
case $ORACLE_TRACE in
T) set -x ;;
esac
# Set path if path not set (if called from /etc/rc)
case $PATH in
"") PATH=/bin:/usr/bin:/etc
export PATH ;;
esac
# Save LD_LIBRARY_PATH
SAVE_LLP=$LD_LIBRARY_PATH

# The this to bring down Oracle Net Listener
ORACLE_HOME_LISTNER=$1
if [ ! $ORACLE_HOME_LISTNER ] ; then
echo "ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener"
echo "Usage: $0 ORACLE_HOME"
else
LOG=$ORACLE_HOME_LISTNER/listener.log

# Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to
# a different ORACLE_HOME for each entry in the oratab.
export ORACLE_HOME=$ORACLE_HOME_LISTNER

# Stop Oracle Net Listener
if [ -f $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
echo "$0: Stoping Oracle Net Listener" >> $LOG 2>&1
$ORACLE_HOME_LISTNER/bin/lsnrctl stop >> $LOG 2>&1 &
else
echo "Failed to auto-stop Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"
fi
fi

# Set this in accordance with the platform
ORATAB=/var/opt/oracle/oratab
if [ ! $ORATAB ] ; then
echo "$ORATAB not found"
exit 1;
fi

# Stops an instance
stopinst() {
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
ORACLE_SID=""
fi
# Called programs use same database ID
export ORACLE_SID
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# Put $ORACLE_HOME/bin into PATH and export.
PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc ; export PATH
# add for bug 652997
LD_LIBRARY_PATH=${SAVE_LLP}:${ORACLE_HOME}/lib ; export LD_LIBRARY_PATH
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora

# See if it is a V6 or V7 database
VERSION=undef
if [ -f $ORACLE_HOME/bin/sqldba ] ; then
SQLDBA=sqldba
VERSION=`$ORACLE_HOME/bin/sqldba command=exit | awk '
/SQL\*DBA: (Release|Version)/ {split($3, V, ".") ;
print V[1]}'`
case $VERSION in
"6") ;;
*) VERSION="internal" ;;
esac
else
if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
SQLDBA=svrmgrl
VERSION="internal"
else
SQLDBA="sqlplus /nolog"
fi
fi

case $VERSION in
"6") sqldba command=shutdown ;;
"internal") $SQLDBA <connect internal
shutdown immediate
EOF
;;
*) $SQLDBA <connect / as sysdba
shutdown immediate
quit
EOF
;;
esac

if test $? -eq 0 ; then
echo "${INST} \"${ORACLE_SID}\" shut down."
else
echo "${INST} \"${ORACLE_SID}\" not shut down."
fi
}

#
# Loop for every entry in oratab file and and try to shut down
# that ORACLE
#
# Following loop shuts down 'Database Instance[s]' with 'Y' entry

cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# NULL SID - ignore
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y' or 'W'
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then
INST="Database instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
LOG=$ORACLE_HOME/shutdown.log
echo "Processing $INST \"$ORACLE_SID\": log file $LOG"
stopinst >> $LOG 2>&1
fi
fi
;;
esac
done

#
# Following loop shuts down 'Database Instance[s]' with 'W' entry
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# NULL SID - ignore
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y' or 'W'
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then
if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then
INST="Database instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
LOG=$ORACLE_HOME/shutdown.log
echo "Processing $INST \"$ORACLE_SID\": log file $LOG"
stopinst >> $LOG 2>&1
fi
fi
;;
esac
done

#
# Following loop shuts down 'ASM Instance[s]'
#

cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# NULL SID - ignore
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y'.
# Entries whose last field is not Y or N are not DB entry, ignore them.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
INST="ASM instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
LOG=$ORACLE_HOME/shutdown.log
echo "Processing $INST \"$ORACLE_SID\": log file $LOG"
stopinst >> $LOG 2>&1
fi
fi
;;
esac
done

Solaris10 + Oracle 11gR2 64bit ---dbstart script

#!/bin/sh
#
# $Id: dbstart.sh 22-may-2008.05:05:45 arogers Exp $
# Copyright (c) 1991, 2008, Oracle. All rights reserved.
#

###################################
#
# usage: dbstart $ORACLE_HOME
#
# This script is used to start ORACLE from /etc/rc(.local).
# It should ONLY be executed as part of the system boot procedure.
#
# This script will start all databases listed in the oratab file
# whose third field is a "Y". If the third field is set to "Y" and
# there is no ORACLE_SID for an entry (the first field is a *),
# then this script will ignore that entry.
#
# This script requires that ASM ORACLE_SID's start with a +, and
# that non-ASM instance ORACLE_SID's do not start with a +.
#
# If ASM instances are to be started with this script, it cannot
# be used inside an rc*.d directory, and should be invoked from
# rc.local only. Otherwise, the CSS service may not be available
# yet, and this script will block init from completing the boot
# cycle.
#
# If you want dbstart to auto-start a single-instance database that uses
# an ASM server that is auto-started by CRS (this is the default behavior
# for an ASM cluster), you must change the database's ORATAB entry to use
# a third field of "W" and the ASM's ORATAB entry to use a third field of "N".
# These values specify that dbstart auto-starts the database only after
# the ASM instance is up and running.
#
# Note:
# Use ORACLE_TRACE=T for tracing this script.
#
# The progress log for each instance bringup plus Error and Warning message[s]
# are logged in file $ORACLE_HOME/startup.log. The error messages related to
# instance bringup are also logged to syslog (system log module).
# The Listener log is located at $ORACLE_HOME_LISTNER/listener.log
#
# On Solaris
# ORATAB=/var/opt/oracle/oratab
#
# To configure, update ORATAB with Instances that need to be started up
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
# An example entry:
# main:/usr/lib/oracle/emagent_10g:Y
#
# Overall algorithm:
# 1) Bring up all ASM instances with 'Y' entry in status field in oratab entry
# 2) Bring up all Database instances with 'Y' entry in status field in
# oratab entry
# 3) If there are Database instances with 'W' entry in status field
# then
# iterate over all ASM instances (irrespective of 'Y' or 'N') AND
# wait for all of them to be started
# fi
# 4) Bring up all Database instances with 'W' entry in status field in
# oratab entry
#
#####################################

LOGMSG="logger -puser.alert "

trap 'exit' 1 2 3

# for script tracing
case $ORACLE_TRACE in
T) set -x ;;
esac

# Set path if path not set (if called from /etc/rc)
SAVE_PATH=/bin:/usr/bin:/etc:${PATH} ; export PATH
SAVE_LLP=$LD_LIBRARY_PATH

# First argument is used to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=$1
if [ ! $ORACLE_HOME_LISTNER ] ; then
echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"
echo "Usage: $0 ORACLE_HOME"
else
LOG=$ORACLE_HOME_LISTNER/listener.log

# Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to
# a different ORACLE_HOME for each entry in the oratab.
export ORACLE_HOME=$ORACLE_HOME_LISTNER

# Start Oracle Net Listener
if [ -x $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
echo "$0: Starting Oracle Net Listener" >> $LOG 2>&1
$ORACLE_HOME_LISTNER/bin/lsnrctl start >> $LOG 2>&1 &
VER10LIST=`$ORACLE_HOME_LISTNER/bin/lsnrctl version | grep "LSNRCTL for " | cut -d' ' -f5 | cut -d'.' -f1`
export VER10LIST
else
echo "Failed to auto-start Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"
fi
fi

# Set this in accordance with the platform
ORATAB=/var/opt/oracle/oratab
if [ ! $ORATAB ] ; then
echo "$ORATAB not found"
exit 1;
fi

# Checks Version Mismatch between Listener and Database Instance.
# A version 10 listener is required for an Oracle Database 10g database.
# Previous versions of the listener are not supported for use with an Oracle
# Database 10g database. However, it is possible to use a version 10 listener
# with previous versions of the Oracle database.
checkversionmismatch() {
if [ $VER10LIST ] ; then
VER10INST=`sqlplus -V | grep "Release " | cut -d' ' -f3 | cut -d'.' -f1`
if [ $VER10LIST -lt $VER10INST ] ; then
$LOGMSG "Listener version $VER10LIST NOT supported with Database version $VER10INST"
$LOGMSG "Restart Oracle Net Listener using an alternate ORACLE_HOME_LISTNER:"
$LOGMSG "lsnrctl start"
fi
fi
}

# Starts a Database Instance
startinst() {
# Called programs use same database ID
export ORACLE_SID

# Put $ORACLE_HOME/bin into PATH and export.
PATH=$ORACLE_HOME/bin:${SAVE_PATH} ; export PATH
# add for bug # 652997
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${SAVE_LLP} ; export LD_LIBRARY_PATH
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
SPFILE=${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora
SPFILE1=${ORACLE_HOME}/dbs/spfile.ora

echo ""
echo "$0: Starting up database \"$ORACLE_SID\""
date
echo ""

checkversionmismatch

# See if it is a V6 or V7 database
VERSION=undef
if [ -f $ORACLE_HOME/bin/sqldba ] ; then
SQLDBA=sqldba
VERSION=`$ORACLE_HOME/bin/sqldba command=exit | awk '
/SQL\*DBA: (Release|Version)/ {split($3, V, ".") ;
print V[1]}'`
case $VERSION in
"6") ;;
*) VERSION="internal" ;;
esac
else
if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
SQLDBA=svrmgrl
VERSION="internal"
else
SQLDBA="sqlplus /nolog"
fi
fi

STATUS=1
if [ -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.dbf ] ; then
STATUS="-1"
fi
if [ -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.ora ] ; then
STATUS="-1"
fi
pmon=`ps -ef | grep -w "ora_pmon_$ORACLE_SID" | grep -v grep`
if [ "$pmon" != "" ] ; then
STATUS="-1"
$LOGMSG "Warning: ${INST} \"${ORACLE_SID}\" already started."
fi

if [ $STATUS -eq -1 ] ; then
$LOGMSG "Warning: ${INST} \"${ORACLE_SID}\" possibly left running when system went down (system crash?)."
$LOGMSG "Action: Notify Database Administrator."
case $VERSION in
"6") sqldba "command=shutdown abort" ;;
"internal") $SQLDBA $args <connect internal
shutdown abort
EOF
;;
*) $SQLDBA $args <connect / as sysdba
shutdown abort
quit
EOF
;;
esac

if [ $? -eq 0 ] ; then
STATUS=1
else
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."
fi
fi

if [ $STATUS -eq 1 ] ; then
if [ -e $SPFILE -o -e $SPFILE1 -o -e $PFILE ] ; then
case $VERSION in
"6") sqldba command=startup ;;
"internal") $SQLDBA <connect internal
startup
EOF
;;
*) $SQLDBA <connect / as sysdba
startup
quit
EOF
;;
esac

if [ $? -eq 0 ] ; then
echo ""
echo "$0: ${INST} \"${ORACLE_SID}\" warm started."
else
$LOGMSG ""
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."
fi
else
$LOGMSG ""
$LOGMSG "No init file found for ${INST} \"${ORACLE_SID}\"."
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."
fi
fi
}

# Starts an ASM Instance
startasminst() {
# Called programs use same database ID
export ORACLE_SID
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME

# For ASM instances, we have a dependency on the CSS service.
# Wait here for it to become available before instance startup.

# Is the 10g install intact? Are all necessary binaries present?
if [ ! -x $ORACLE_HOME/bin/crsctl ]; then
$LOGMSG "$ORACLE_HOME/bin/crsctl not found when attempting to start"
$LOGMSG " ASM instance $ORACLE_SID."

else
COUNT=0
$ORACLE_HOME/bin/crsctl check css
RC=$?
while [ "$RC" != "0" ];
do
COUNT=`expr $COUNT + 1`
if [ $COUNT = 15 ] ; then
# 15 tries with 20 sec interval => 5 minutes timeout
$LOGMSG "Timed out waiting to start ASM instance $ORACLE_SID"
$LOGMSG " CSS service is NOT available."
exit $COUNT
fi
$LOGMSG "Waiting for Oracle CSS service to be available before starting "
$LOGMSG " ASM instance $ORACLE_SID. Wait $COUNT."
sleep 20
$ORACLE_HOME/bin/crsctl check css
RC=$?
done
fi
startinst
}

# Start of dbstartup script
#
# Loop for every entry in oratab file and and try to start
# that ORACLE.
#
# ASM instances need to be started before 'Database instances'
# ASM instance is identified with '+' prefix in ORACLE_SID
# Following loop brings up ASM instance[s]

cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
# If ASM instances
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
INST="ASM instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# file for logging script's output
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"
startasminst >> $LOG 2>&1
fi
fi
;;
esac
done

# exit if there was any trouble bringing up ASM instance[s]
if [ "$?" != "0" ] ; then
exit 2
fi

#
# Following loop brings up 'Database instances'
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
# If non-ASM instances
if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then
INST="Database instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# file for logging script's output
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"
startinst >> $LOG 2>&1
fi
fi
;;
esac
done

#
# Following loop brings up 'Database instances' that have wait state 'W'
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'W'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then
W_ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
# DB instances with 'W' (wait state) have a dependency on ASM instances via CRS.
# Wait here for 'all' ASM instances to become available.
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
INST="ASM instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
if [ -x $ORACLE_HOME/bin/srvctl ] ; then
COUNT=0
NODE=`olsnodes -l`
RNODE=`srvctl status asm -n $NODE | grep "$ORACLE_SID is running"`
RC=$?
while [ "$RC" != "0" ]; # wait until this comes up!
do
COUNT=$((COUNT+1))
if [ $COUNT = 5 ] ; then
# 5 tries with 60 sec interval => 5 minutes timeout
$LOGMSG "Error: Timed out waiting on CRS to start ASM instance $ORACLE_SID"
exit $COUNT
fi
$LOGMSG "Waiting for Oracle CRS service to start ASM instance $ORACLE_SID"
$LOGMSG "Wait $COUNT."
sleep 60
RNODE=`srvctl status asm -n $NODE | grep "$ORACLE_SID is running"`
RC=$?
done
else
$LOGMSG "Error: \"${W_ORACLE_SID}\" has dependency on ASM instance \"${ORACLE_SID}\""
$LOGMSG "Error: Need $ORACLE_HOME/bin/srvctl to check this dependency"
fi
fi # asm instance
;;
esac
done # innner while
fi
;;
esac
done # outer while

# by now all the ASM instances have come up and we can proceed to bring up
# DB instance with 'W' wait status

cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'W'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then
INST="Database instance"
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started"
$LOGMSG "Error: incorrect usage: 'W' not allowed for ASM instances"
continue
fi
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# file for logging script's output
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"
startinst >> $LOG 2>&1
fi
;;
esac
done

Thursday, August 11, 2011

Mount drive automatically during startup in Solaris

/etc/vfstab (Solaris)
/etc/fstab (Linux)

Monday, August 8, 2011

Step by Step installing Oracle 11g R2 on Oracle Solaris 10

http://kamranagayev.wordpress.com/2011/03/27/step-by-step-installing-oracle-11gr2-on-oracle-solaris-10/

Thursday, August 4, 2011

Kill busy device for umount

# umount /dev/dsk/c0t2d0s7

The following command will display all processes and their associated users that are using files/directories on a specified disk on a Sun Solaris system:

# fuser -u /dev/dsk/c0t2d0s7

You can use the -k option to kill all processes using the specified file or filesystem

# fuser -k /dev/dsk/c0t2d0s7

Wednesday, August 3, 2011

Step by Step installing Oracle 11g R2 on Oracle Solaris 10

http://kamranagayev.wordpress.com/2011/03/27/step-by-step-installing-oracle-11gr2-on-oracle-solaris-10/

Thursday, July 28, 2011

Oracle ASM 10g R2

Tool of ASM in Windows NT Platform.

asmtoolg.exe

http://www.databasejournal.com/features/oracle/article.php/3571371/Oracle-10g-Automatic-Storage-Management-ASM-Part-2-Sample-Implementation.htm

Tuesday, July 26, 2011

copy Oracle Users

http://www.dba-oracle.com/t_copying_oracle_users.htm

how to enable advanced compression option in Oracle 11g

1. Your database version is 11.1.0.6 or higher.

2. Your client software supports 11.1.0.6 or higher.

If the above two conditions are met you don't need SecureFiles, you don't need any init parameters, you just need the following:


CREATE TABLE regtab
AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'

CREATE TABLE comptab
COMPRESS AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

SELECT table_name, tablespace_name, compression
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');


exec dbms_stats.gather_table_stats(USER, 'REGTAB');
exec dbms_stats.gather_table_stats(USER, 'COMPTAB');


SELECT table_name, blocks
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');

Wednesday, July 13, 2011

SQL Reporting Services Error- Maximum request length exceeded

When trying to deploy a large report to your SSRS server, you may run into an error like this:

Error 2 There was an exception running the extensions specified in the config file. ---> Maximum request length exceeded.

You could, quite honestly, run into this error in a lot of situations involving a web app, but we're talking specifically about SSRS in this post.

The basic problem here, is that your posting an amount of data to a web app larger than it is configured to accept. Hence, it is throwing an error, and simply saying "no!"

It's an easy fix though! You've got to tweak the web.config for the web app, which in the case of reporting server, is usually somewhere like this:

C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer

Find the web.config file for your reporting services instance, open it up, and track down the line that looks something like this



Now just add a max request length attribute in there to fix the problem, adjust your size as needed. This is 5meg.



And now you'll need to restart IIS. start->run->"iisreset"



Good luck!

Tuesday, July 5, 2011

Disable/Enable all scheduled jobs in SQL Server 2005

Declare @job_id Nvarchar(200)
DECLARE @String1 NVARCHAR(1000)
DECLARE @jobs TABLE (job_id NVARCHAR(200))
SET NOCOUNT ON
-- job id
INSERT @jobs
select job_id from msdb..sysjobs

WHILE EXISTS (SELECT TOP 1 job_id FROM @jobs WHERE job_id IS NOT NULL)
BEGIN
SELECT TOP 1 @job_id = job_id
FROM @jobs
SET @String1 ='EXEC msdb..sp_update_job @job_id = ''' +@job_id + ''', @enabled = 0 '
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @jobs WHERE job_id = @job_id
END


Declare @job_id Nvarchar(200)
DECLARE @String1 NVARCHAR(1000)
DECLARE @jobs TABLE (job_id NVARCHAR(200))
SET NOCOUNT ON
-- job id
INSERT @jobs
select job_id from msdb..sysjobs

WHILE EXISTS (SELECT TOP 1 job_id FROM @jobs WHERE job_id IS NOT NULL)
BEGIN
SELECT TOP 1 @job_id = job_id
FROM @jobs
SET @String1 ='EXEC msdb..sp_update_job @job_id = ''' +@job_id + ''', @enabled = 1 '
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @jobs WHERE job_id = @job_id
END

Wednesday, June 8, 2011

Package0

The asynchronous_file_target holds the raw format Event data in a proprietary binary file format that persists beyond server restarts and can be provided to another person via ftp or email for remote disconnected analysis of the events.

The bucketizer performs grouping of Events as they are processed by the target into buckets based on the Event data and the Targets configuration. There are two bucketizer targets in Extended Events; a synchronous_bucketizer and an asynchronous_bucketizer.

Wednesday, May 11, 2011

Dirty Pages

Clean pages can be fl ushed from cache using dbcc dropcleanbuffers, which can be handy when you’re troubleshooting development and test environments because it forces subsequent reads to be fulfi lled from disk, rather than cache, but doesn’t touch any dirty pages.

You can use the following query, which is based on the sys.dm_os_buffer_descriptors DMV, to see how many dirty pages exist in each database:

SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY count(page_id) DESC

Buffer Pool

The buffer pool contains and manages SQL Server’s data cache. Information on its contents can be found in the sys.dm_os_buffer_descriptors DMV.

For example, the following query will return the amount of data cache usage in MB per database:

SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC



Monitoring SQL Server’s buffer pool is a great way to look out for memory pressure


MSSQL$:Memory Manager\Total Server Memory (KB): This indicates the current
size of the buffer pool.
MSSQL$:Memory Manager\Target Server Memory (KB): This indicates the ideal
size for the buffer pool.
MSSQL$:Buffer Manager\Page Life Expectancy: This is the amount of time, in
seconds, that SQL Server expects a page that has been loaded into the buffer pool to remain in cache.

Tuesday, April 12, 2011

open the firewall port for SQL Server on Windows Server 2008/R2

How to: Configure a Windows Firewall for Integration Services

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

How do I open the firewall port for SQL Server on Windows Server 2008?

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

Wednesday, March 30, 2011

Seven tiers of disaster recovery


1 Tier 0: No off-site data – Possibly no recovery
2 Tier 1: Data backup with no hot site
3 Tier 2: Data backup with a hot site
4 Tier 3: Electronic vaulting
5 Tier 4: Point-in-time copies
6 Tier 5: Transaction integrity
7 Tier 6: Zero or near-Zero data loss
8 Tier 7: Highly automated, business integrated solution

http://en.wikipedia.org/wiki/Seven_tiers_of_disaster_recovery

Tuesday, March 22, 2011

user's permissions in SQL Server 2005

Example - list current login user's permissions

select object_name(major_id) as object_name,permission_name,state_desc
from sys.database_permissions
where major_id >0 and grantee_principal_id !=0

Example - SQL Server Instance Rights

USE AdventureWorks;
SELECT *
FROM fn_my_permissions(NULL, 'SERVER');
GO

Example - Database Rights

USE AdventureWorks;
SELECT *
FROM fn_my_permissions('AdventureWorks', 'DATABASE');
GO

Example - Table Rights

USE AdventureWorks;
SELECT *
FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
GO

Thursday, March 10, 2011

Reporting Services Scripter for SQL Server 2000/2005/2008

http://www.sqldbatips.com/showarticle.asp?ID=62

Tuesday, March 8, 2011

Limit Concurrent Database Connections by SQL Server User Account

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [connection_limit_trigger] ON ALL SERVER
WITH EXECUTE AS 'tfs4dba' FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'tfs4dba' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'tfs4dba') > 3
ROLLBACK; END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER

Thursday, March 3, 2011

SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

This post demonstrates the script which displays create date and modify date for any specific stored procedure in SQL Server.



USE AdventureWorks;
GO
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'uspUpdateEmployeeHireInfo'
GO

Tuesday, March 1, 2011

Oracle Database AL32UTF8

AL32UTF8 is the Oracle Database character set that is appropriate for
XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which
supports all valid XML characters.

Do not confuse Oracle Database database character set UTF8 (no hyphen) with
database character set AL32UTF8 or with character encoding UTF-8. Database
character set UTF8 has been superseded by AL32UTF8. Do not use UTF8 for XML data.
UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML
characters. AL32UTF8 has no such limitation.

Using database character set UTF8 for XML data could potentially cause a fatal error or affect security negatively. If a character that is not supported by the database character set appears in an input-document element name, a replacement character (usually "?")is substituted for it. This will terminate parsing and raise an exception.

Tuesday, February 22, 2011

SQL Server [Version and Edition Upgrades ] And [Backward Compatibility]

SQL Server Version and Edition Upgrades
http://msdn.microsoft.com/en-us/library/ms143393(v=SQL.100).aspx


SQL Server Backward Compatibility
http://msdn.microsoft.com/en-us/library/cc707787(SQL.105).aspx

Tuesday, February 15, 2011

Copy file using T-SQL in SQL Server 2005

sp_configure 'Ole Automation Procedures',1
go
reconfigure





DECLARE @hr int
DECLARE @ole_FileSystem int
DECLARE @True int
DECLARE @src varchar(250), @desc varchar(2000)
DECLARE @source varchar(255), @dest varchar(255)

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @ole_FileSystem OUT
if @hr <> 0
begin
exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
raiserror('Object Creation Failed 0x%x, %s, %s',16,1,@hr,@src,@desc)
return
end

SET @source = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG.1'
SET @dest = 'D:\Backups\ErrorLogs\'+ Convert(varchar(16), Getdate(), 106)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4) +'ERRORLOG.txt'

EXEC @hr = sp_OAMethod @ole_FileSystem, 'CopyFile',null, @source, @dest
if @hr <> 0
begin
exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
exec sp_OADestroy @ole_FileSystem
raiserror('Method Failed 0x%x, %s, %s',16,1,@hr,@src,@desc)
return
end

Monday, February 14, 2011

Update SQL Server Agent Error Log location in SQL Server 2005

USE [msdb]

sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
sp_configure

GO

EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT'

GO

Monday, February 7, 2011

how to host php on IIS 7.5

http://technetnepal.net/blogs/gandip/archive/2010/02/21/how-to-host-php-on-iis-7-5.aspx

Backup SSAS Database as a SQL Agent job in SQL Server 2008

SQL Server Analysis Services Command ( SQL Server Agent)

Monday, January 24, 2011

install SQL SSRS 2005 on Windows 2008 R2 (Reporting Services Configuration Manager)

The Web Service identity's ASP. NET Service Account item is grey out.

So the identity of Classic .NET AppPool of IIS 7.5 should be LocalSystem User.

Tuesday, January 18, 2011

Tuesday, January 11, 2011

Installing SQL Server 2005 SP3 on a Failover Cluster Instance

Note the following information before you install SQL Server 2005 SP3 on a failover cluster instance:

Do not stop the cluster service before you run the SP3 Setup program, or while the installation program is running.


Do not end any running processes before you run the SP3 Setup program.


Do not take the SQL Server service off line before you run the SP3 Setup program. The SP3 Setup program will stop and start the SQL Server service.


Run the SP3 Setup program on the primary node of the failover cluster instance.


You must restart all failover cluster nodes after SP3 is finished installing.


Rolling upgrade is not supported for SP3.




Failover Cluster Installation
The following information applies only to SQL Server 2005 components that are part of a failover cluster.

To install the service pack on a failover cluster:

To ensure that SQL Server 2005 SP3 will be installed on all nodes of a failover cluster instance, verify that the TASK Scheduler is running on all passive nodes of your failover cluster instance.


If any resources have been added that have dependencies on SQL Server resources, those dependencies must either be removed or taken offline before you install SQL Server 2005 SP3. If you do not remove the dependencies, the installation of SP3 will take those resources offline.

Note:
When a clustered resource is taken offline, all dependent resources are also taken offline by the cluster service.



Run the SQL Server 2005 SP3 executable package file from the active node that owns the group containing the failover cluster instance that you plan to upgrade. This installs the service pack files on all nodes in the failover cluster. You cannot install SP3 from any passive nodes in the failover cluster.


On the Feature Selection page of the SQL Server 2005 SP3 Setup Wizard, select the failover cluster instance that you plan to upgrade. Setup will prompt you for the login credentials that are used to connect to other nodes in the cluster.

Note:
Keep all nodes of the cluster online during Setup. This ensures that the upgrade is applied to each cluster node.



If you removed dependencies or took resources offline in step 1, restore the dependencies, or bring the resources online.

Note:
Setup might require you to restart the failover cluster nodes. This restart replaces the files that were in use during Setup.

Thursday, January 6, 2011

Replications of SQL Server

Snapshot Replication
This form of replication is appropriate for small data sets, infrequent
update periods (or for a one-time replication operation), or management simplicity.

Transactional Replication
Transactional replication can typically keep databases in sync within about five seconds of latency, depending on the underlying network infrastructure.

Merge Replication
Merge replication allows data to be modified by the subscribers and synchronized at a later time. This synchronization could be as soon as a few seconds, or it could be a day later.

Immediate Updating
Immediate updating allows a replication target to immediately modify data at the source.This task is accomplished by using a trigger to run a distributed transaction.