Question { Satyam, 22950 }

What is difference beteen Migration and Upgrdation?

Answer

Migration:
There are various situations for migration
1. LAUNCH, GO-LIVE, DEPLOYMENT.
A situation or a birth of PROJECT is known as LAUNCH,
GO-LIVE, DEPLOYMENT. Starts from Development -> Test ->
Production.

2. DB-REFRESH.
When we need to take back the server and its functionalities
from PRODUCTION SERVER to TEST SERVER and back again to
PRODUCTION SERVER is known as DB-REFRESH.

3. MOVING FROM OLD HARDWARE TO NEW HARDWARE.
Every hardware is subject to END OF LIFE. In such
situations, we would move the entire server from OLD
HARDWARE to NEW HARDWARE.

4. MOVING BETWEEN DIFFERENT DATA CENTERS.

5. CONSOLIDATION PROJECT.
CONSOLIDATION is a process of gathering all the NON-CRITICAL
databases over individual servers and managing them in a
single high scalable server.

FROM THE ABOVE SITUATION, WE CAN CLEARLY IDENTIFY THAT WE
ARE NOT EFFECTING THE VERSION OF THE SQL SERVER SOFTWARE.

Up-gradation:
UP-GRADATION is a process of INSTALLING a newer version on
the existing SQL SERVER SOFTWARE. [E.G HOTFIX, PATCH,
SERVICE PACKS, VERSION UPGRADE]
There are two types of UP-GRADATION
1. IN-PLACE UP-GRADATION
2. SIDE-BY-SIDE UP-GRADATION

Question { HP, 10434 }

What are the various Isolation levels?

Answer

Isolation levels in SQL SERVER
1. READ UNCOMMITTED isolation level.
This kind of isolation level is used when we have the most
need for HIGHER CONCURRENCY.
Limitations:
Dirty pages
Lost updates
Phantom reads
Non-repeatable reads.

2. READ COMMITTED isolation level.
This is default level set in SQL SERVER. It prevents
transactions to read data if some other transaction is doing
some update operation on the data as a result eliminating
dirty reads.
It prevents reading of uncommitted data.
Limitations:
Low level of concurrency
Lost updates
Advantages:
Eliminates Dirty reads.

3. REPEATABLE READ isolation level.
It does not release the shared lock once the transaction
starts for reading data.
Transaction cannot read data that is modified by other
transaction and not committed.

Limitations:
Lower concurrency
Phantom reads

Advantages:
Eliminates Dirty Reads
Eliminates Lost updates
Eliminates Non-repeatable reads.

4. SERIALIZABLE isolation level.
This is the highest level of isolation, as a result a very
low concurrency rate.
Can not read data if other transaction are performing update
operations on data that is not committed yet.
No other transaction can perform update operation until
current transaction completes its read operation.
It performs RANGE LOCK based on filters used to get the data
i.e it locks current records and also the new records that
fall under current filter condition.
Limitation:
Low concurrency

Advantages:
Eliminates Dirty reads
Eliminates Lost updates
Eliminates Non-repeatable reads
Eliminates Phantom reads


5. SNAPSHOT isolation level.
It implements ROW VERSIONING to isolate data for each
transaction i.e it will keep separate version fo reach
modified row in tempdb.
Limitation:
Low performance due to versioning in tempdb.

Advantage:
Eliminates Dirty reads
Eliminates Lost updates
Eliminates Non-repeatable reads
Allows multiple updates by versioning.

6. CHAOS isolation level.
Same as READ UNCOMMITTED
Permits viewing uncommitted changes of other transaction
Rollback is not supported.

7. UNSPECIFIED isolation level.
When an isolation level of any transaction cannot be
determined, it is knows as unspecified isolation level.
ODBCTransaction, when an user forgets to set an isolation
level for such transactions, it will execute according to
isolation level associated by ODBC driver

Question { 4625 }

What is Dedicated Administrator Connection in sql server
2005?

Answer

In the previous versions of MS SQL SERVER, if the server
maxed out the resources due to any of the reasons, it was
practically impossible to connect the server thru remote
desktop. As a result of such situations, the developers /
DBA'S were forced to "reboot". Doing this, many a times, the
SQL SERVER would try to properly shutdown properly by
forcing a CHECK POINT so as to minimize inconsistency, but
there would be instances when such CHECK POINT might not
occur thus screwing up data on the server.
Microsoft addressed this problem with DAC [ DEDICATED
ADMINISTRATOR CONNECTION ] which allows the developers /
DBA'S to connect to the server and perform T-SQL commands to
figure out the problem and perform necessary action accordingly.
syntax:
START -> RUN -> CMD
in dos mode.. enter the following

sqlcmd
[{ { -U login_id [ -P password ] } | –E trusted connection }]
[ -z new password ] [ -Z new password and exit]
[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [
-d db_name ]
[ -l login time_out ] [ -A dedicated admin connection ]
[ -i input_file ] [ -o output_file ]
[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]
[ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ]
[ -R use client regional settings ]
[ -q "cmdline query" ] [ -Q "cmdline query" and exit ]
[ -e echo input ] [ -t query time_out ]
[ -I enable Quoted Identifiers ]
[ -v var = "value"...] [ -x disable variable substitution ]
[ -h headers ][ -s col_separator ] [ -w column_width ]
[ -W remove trailing spaces ]
[ -k [ 1 | 2 ] remove[replace] control characters ]
[ -y display_width ] [-Y display_width ]
[ -b on error batch abort ] [ -V severitylevel ] [ -m
error_level ]
[ -a packet_size ][ -c cmd_end ]
[ -L [ c ] list servers[clean output] ]
[ -p [ 1 ] print statistics[colon format]]
[ -X [ 1 ] ] disable commands, startup script, enviroment
variables [and exit]
[ -? show syntax summary ]