Answer:- Cluster Administrator checks the SQL Server group where we can see the current owner. The current owner is the active node and the other nodes are passive nodes.

Answer:- Deadlock Information: 1204, 1205, and 1222 Network Database Files: 1807 Log Record for Connections: 4013 Skip Startup Stored Procedures: 4022 Disable Locking Hints: 8755 Forces uniform extent allocations instead of mixed page allocations 1118 (SQL Server 2005 and 2008) to reduce TempDB contention

Answer:- No. The only way if we want to get a backup is by using Windows backup for option resource MDF and IDF files.

Answer:- Upgrade Advisor can analyze remote instances of SQL Server, except for SQL Server Reporting Services. To analyze Reporting Services, Upgrade Advisor must be installed and executed on the Report Server.

Answer:- To upgrade SQL Server 2000 to SQL Server 2008, the safest approach would be a side-by-side upgrade. We can do this either by using backup and restore or by detaching/attaching the database files. However, it is suggested to use the former as it is a safer approach. The steps are as follows: • Run the Upgrade Analysis tool from Microsoft. Address any issues raised there, first • Identify DTS packages. These must be migrated manually unless we buy Pragmatic Works • Rebuild the DTS packages as SSIS • Script out all SQL Agent jobs • Script out all security • Backup the systems and validate the backups (preferably by restoring them to another system) • Run the security script on the new system • Run restore on the new system • Validate the databases by running DBCC • Manually update all statistics • Run the SQL Agent script

Answer:- Yes. SQL Server 2005 databases are compatible with SQL Server 2008. Attaching a SQL Server 2005 database to SQL Server 2008 automatically upgrades the SQL Server 2005 database to the latter, and the database is then no longer usable by the SQL Server 2005 installation.

Answer:- No. The only way to move a SQL Server 2008 database to a SQL Server 2005 is by transferring the data using methods, such as Data Transformation Services (Import/Export), SSIS, BCP, etc., or by using a query between linked servers.

Answer:- If applications or services have opened ODBC connections to SQL Server 2005 during the conversion process, they may not allow SQL Server to shut down completely. The conversion process will not proceed to the next step if it does not receive verification that SQL Server has been completely stopped.

Answer:- If the legacy SQL Server instance is replaced by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complex and time-consuming; whereas, in a side-by-side upgrade, the legacy instance remains available if a rollback is needed.

Answer:- The below command enforces an exclusive lock on the database, which makes the process faster: DBCC CHECKDB (‘TestDB’) WITH NO_INFOMGS, TABLOCK

Answer:- Within the REPEATABLE_READ and SERIALIZABLE isolation levels, locks are held/retained for the duration of the transaction, unlike within the READ_COMMITTED isolation level.

Answer:- • One is the READ_COMMITTED isolation level. This is the only level that supports both a pessimistic (locking-based) and an optimistic (version-based) concurrency control model. • The other is the SNAPSHOT isolation level that supports only an optimistic concurrency control model.

Answer:- The READ_COMMITTED_SNAPSHOT option for the READ_COMMITTED optimistic model and the ALLOW_SNAPSHOT_ISOLATION option for the SNAPSHOT isolation level.

Answer:- The database engine uses intent locks to protect placing a shared (S) lock or an exclusive (X) lock on a resource (lower in the lock hierarchy). Intent locks are named thus because they are acquired before a lock at the lower level and therefore signal the intent to place the locks at a lower level. Intent locks serve two purposes: • They prevent other transactions from modifying a higher-level resource in a way that would invalidate the lock at the lower level. • They improve the efficiency of the database engine in detecting lock conflicts at a higher level of granularity.