SQL Server Joins......

Steps to Access a Database which is in Inaccess mode

Steps to Remove Database Inaccess Mode

select databaseproperty('irisdb_admin','isShutdown')

alter database irisdb_admin set offline

alter database irisdb_admin set online

Find table being locked in SQL Server 2008/2005

Find table being locked in SQL Server 2008/2005

SQL Scripts to find Locked table in SQL Server 2005/2008

Script for the list of tables which are being locked :

select object_name(resource_associated_entity_id) as 'TableName',*
     from sys.dm_tran_locks where resource_type = 'OBJECT'
     and resource_database_id = DB_ID()

Script for list of tables with schema mode change lock :

select object_name(resource_associated_entity_id) as 'TableName',*
     from sys.dm_tran_locks where resource_type = 'OBJECT'
     and resource_database_id = DB_ID()
     and request_mode = 'Sch-M'

Snapshot vs Log Shipping vs Mirroring vs Replication vs Failover Clustering

--->Snapshot : A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the same server instance as the database. Each database snapshot is consistent, in terms of transactions, with the source database as of the moment of the snapshot’s creation.

--->Log Shipping : Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs.

--->Mirroring : Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs. Additional advantages of Mirroring include support at .NET Framework level plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

--->Replication : Replication is the process of copying data between two databases on the same server or different servers on the same database. This is one of the methods to maintain the redundant database site for disaster recovery purpose. Replication methodology maintains copies of main database from the primary server on the secondary sever, both of the databases will be in synchronization state and this technology is used to provide high availability.

--->Snapshot Replication: Snapshot replication is initiated at firs by all types of replication to initialize subscribers. In this method of replication, entire data will be captured and sent to the subscriber. What happens in this replication is, all the changes that occur in the publisher will be captured and then later sent to the subscriber when the snapshot agent runs again. Snapshot agent and distribution agents are used in the snapshot replication.
--->Merge Replication: Merge replication is the process of distributing data from publisher to the subscriber. In the merge replication, updates are allowed even when subscriber or publisher are connected or disconnected. If they are disconnected, the updates will be applied when both get connected. Merge agent and snapshot agent is used in merge replication. Merge replication is the most difficult replication method to implement and manage.
--->Transactional Replication: In transactional replication, all changes made in the publisher are captured and stored in the distribution database, and then these changes are transferred to the subscriber database. The best situation to use transactional replication is when data changes frequently and data is transferred to the subscriber immediately as in almost real time scenario.
--->Peer-Peer Replication: Peer-Peer replication is built on the foundation of the transactional replication. This replication method is introduced in SQL Server 2005. This replication is available only in SQL Server Enterprise Edition. Peer-to-Peer replication provides high availability solution by providing multiple copies of databases across multiple instances.

---> Failover Clustering : Failover Clustering is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. You might see DR options like Mirroring used quite frequently with failover clustering. Here’s a good article on adding geo redundancy to a failover cluster setup.

SQL SERVER – Best Practices for Query Optimization

--> Table should have primary key
--> Table should have minimum of one clustered index
--> Table should have appropriate amount of non-clustered index
--> Non-clustered index should be created on columns of table based on query which is running
--> Following priority order should be followed when any index is created
a) WHERE clause
b) JOIN clause
c) ORDER BY clause
d) SELECT clause
--> Do not to use Views or replace views with original source table
--> Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
--> Remove any adhoc queries and use Stored Procedure instead
--> Check if there is atleast 30% HHD is empty – it improves the performance a bit
--> If possible move the logic of UDF to SP as well
--> Remove * from SELECT and use columns which are only necessary in code
--> Remove any unnecessary joins from table
--> If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)