Question:- Can you detach SQL Server 2005 database and attach it to a SQL Server 2008?
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.
Question:- Can you detach a SQL Server 2008 database and attach it to a SQL Server 2005?
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.
Question:- When you upgrade a SQL Server, the upgrade wizard seems to stop responding and fails. Why?
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.
Question:- How to rollback the upgrade?
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.
Question:- How to speed up the DBCC CHECKDB execution process?
Answer:- The below command enforces an exclusive lock on the database, which makes the process faster: DBCC CHECKDB (‘TestDB’) WITH NO_INFOMGS, TABLOCK
Question:- How long are locks held/retained within the REPEATABLE_READ and SERIALIZABLE isolation levels during a read operation, assuming a row-level locking?
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.
Question:- Which two isolation levels support optimistic/row-version-based concurrency control?
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.
Question:- What database options must be set to allow the use of 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.
Question:- Explain the purpose of Intent locks.
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.
Question:- How to read the graphical execution plan?
Answer:- The graphical execution plan should be read from Right to Left: • Check the graphical execution plan of a stored procedure/query • Table Scan: Index is missing • Index Scan: Proper indexes are not used • BookMark Lookup: Limits the number of columns in the select list • Filter: Removes any functions from the WHERE clause; may require additional indexes • Sort: Checks if the data really needs to be sorted, if an index can be used to avoid sorting, and if sorting can be done at the client-side more efficiently? • DataFlow Arrow (high density): Sometimes, we find few rows as the outcome, but the arrow line density indicates the query/proc processing huge number of rows • Cost: Easily finds out which table/operation taking much time
Question:- What are the permissions required to view the execution plan?
Answer:- Either a user must be mapped to sysadmin/db_owner/db_creator or the user will be granted the below permission: GRANT SHOWPLAN TO [username]
Question:- What is Azure Cloud Service?
Answer:- Cloud service can convey multiple web applications in Azure, characterizing a number of parts to disseminate handling and permit adaptable scaling of your application. A cloud service comprises a minimum of one web part, as well as specialist parts, each with its own particular application documents and design. The fundamental favorable position of cloud service is the capacity to help more complex multilevel structures.
Question:- What is Azure DevOps?
Answer:- Azure DevOps is a SaaS platform that provides development services for creating work plans, working together on code, developing applications, and deploying them. It offers an end-to-end DevOps toolchain for the development and deployment of software. It is able to integrate with a number of popular tools in the market and is a great way for setting up a DevOps toolchain.
Question:- What is Azure Active Directory (Azure AD)?
Answer:- Azure AD is a cloud-based IAM solution and directory by Microsoft. It brings together application access management, core directory services, and identity protection and turns them into a single solution. It helps employees of an organization sign in and access resources such as: • External resources, including Microsoft 365, the Azure portal, and a big number of SaaS applications • Internal resources, such as apps on a corporate network and intranet, as well as any cloud-based apps built by that organization Azure AD is intended for use by: • IT Admins • App Developers • Azure, Office 365, Microsoft 365, or Dynamics CRM online subscribers