Can we change the restore mode of the secondary database from NORECOVERY to STANDBY in SQL Server log shipping configuration?
Yes, we can change the secondary database restore mode from restoring to standby to use it for read-only operations. We will use a system stored procedure sp_change_log_shipping_secondary_database to make this change. This stored procedure will take an input value 1 for argument @restore_mode while running it. There are two values for this argument. Use 1 to restore the log with STANDBY and use 0 to restore the log with NORECOVERY.
You have created a database snapshot on the primary replica. What would happen to the database snapshot if the primary replica failed over to the secondary replica?
Database snapshots will not have any impact. They will be there on previous primary replica and you can use them for your requirements.
Nick CamilleriHead of Career Skills Development & Coaching*Based on past data of successful IK students
Our tried & tested strategy for cracking interviews
The 4 areas you must prepare for
How you can accelerate your learnings
SQL Server Database Administrators, also called Server DBAs, are responsible for the design, maintenance, security, memory, and support of SQL Server Databases. SQL Server DBA interview questions are asked in both fresher and experienced programmers SQL interviews. If youre getting ready for an upcoming SQL interview, going over these SQL Server DBA interview questions and answers can help you ace it.
If youâre a software engineer, coding engineer, software developer, engineering manager, or tech lead preparing for tech interviews, check out our technical interview checklist, interview questions page, and salary negotiation e-book to get interview-ready!Â
Having trained over 9,000 software engineers, we know what it takes to crack the most challenging tech interviews. Since 2014, Interview Kickstart alums have landed lucrative offers from FAANG and Tier-1 tech companies, with an average salary hike of 49%. The highest-ever offer received by an IK alum is a whopping $933,000!
At IK, you get the unique opportunity to learn from expert instructors who are hiring managers and tech leads at Google, Facebook, Apple, and other top Silicon Valley tech companies. Our reviews will tell you how weâve shaped the careers of thousands of professionals aspiring to take their careers to new heights.Â
Want to nail your next tech interview? Sign up for our FREE Webinar.
In this article, weâll look at some common basic and advanced SQL Server DBA interview questions with answers, as well as some sample interview questions asked at SQL technical interviews.
Hereâs what weâll discuss:
Can we run DBCC CHECKDB on the secondary replica of the availability group?
Yes, we can execute CHEKDB on a secondary replica for database integrity check. This will ensure the integrity of the database hosted on secondary replica only. This execution will not guarantee the integrity of the database hosted on a primary replica as a primary replica uses a different set of storage systems to host their databases.
What is Enhanced Database Failover in Always on availability group?
Microsoft has added a new feature in SQL Server 2016 availability groups where you can configure your databases participating in availability groups to failover if they are unable to do write operations. Earlier versions of SQL Server were not triggering failover if any database from the availability group was unable to write operation. It is also called database level health detection in the availability group. This is an optional feature that can be configured during creating an availability group or you can even configure it by running the below ALTER statement for existing availability groups.
| 12345 | –Enable Enhanced database failover on an existing availability groupALTERAVAILABILITYGROUP[AGNAME]SET(DB_FAILOVER=ON) –Disable Enhanced database failover on an existing availability group ALTERAVAILABILITYGROUP[AGNAME]SET(DB_FAILOVER=OFF) |