SQL Server DBA Interview Questions and Answers

SQL is one of the most commonly used databases today. If you’re interviewing for certain IT jobs, like database administrator (DBA), knowledge of SQL may be required. It’s important to think about how to show that knowledge to portray yourself as the best candidate for the job.

A database administrator is responsible for a wide range of tasks of varying complexity having to do with data and data storage. In a business environment that’s driven by high velocity and high volume data of various types, skilled database administrators play a vital role in a company’s overall success and are in high demand.

You can expect an interview for a SQL DBA position to offer several questions that test your skills and experience. Question formats include:

• Those related to core database knowledge
• Infrastructure questions
• Database development questions
• Open-ended questions

1.  What is SQL Server Analysis service and its importance?

Ans:

Microsoft SQL Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets you design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

2. What is SQL Server Browser?

Ans:

This service acts as a listener for the incoming requests for Microsoft SQL Server resources. It provides information about the list of installed SQL Server instances on the computer to the client computers/applications. It helps in browsing the list of servers, locating and connecting to the correct server. This listener service responds to client requests with the names of the installed instances, and the ports or named pipes used by the instance.

3. What are the different SQL Server Versions you have worked on?

Ans:

The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only some versions, be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.

4. What is DBCC?

Ans:

DBCC Stands for database console commands it is available in 4 types.They are

• Maintenance
• Miscellaneous
• Validation
• Informational

5. How can you control the amount of free space in your index pages?

Ans:

This will tell SQL Server what proportion of free house to depart within the index pages once re-indexing.

The performance of profit here is some page to split (where the SQL Server needs to row copy from the one index page associate to another to the different} to create space for the row inserted) as a result of there is space for growth engineered into the index.

6. Why would you call?

Ans:

It is employed to force the computation of question optimization statistics for a table or indexed read.

Query optimization statistics are unit mechanically re-computed, but it is sometimes a query might benefit from updating those kinds of statistics more frequently, Beware although that re-compute the question statistics are causes to queries to be re-compiled.

7. What is a correlated subquery?

Ans:

It is the nested query which is linked to the outer side query. For instance, we can say that we need to search out all the workers in WHO they do not enter time for the whole week

8. What is SQL Server Reporting Services?

Ans:

This service is primarily used by SQL Server Reporting Services (SSRS) for browsing and viewing the reports on Reports Server, through Report Server or Report Manager interface. It is used to manage the shared data sources, reports, shared data sets, report parts, folder, etc. hosted on the Report Server. Reporting services are managed using the Reporting Services Configuration Manager.

9. Explain about your SQL Server DBA Experience.

Ans:

This is a generic question typically asked by several interviewers. Explain what are the various SQL Server Versions you have got worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the solution that lets the asker skills appropriate for you for the position to which you’re being interviewed.

10. What are the least permissions required for issuing a SQL Server database backup?

Ans:

In order to issue a SQL Server database backup, the minimum permissions are:

• Server level – PUBLIC ROLE
• Database level – DB_BACKUPOPERATOR

11. What are the different types of Indexes available in SQL Server?

Ans:

It is mainly divided into two types. They are  Non-Clustered Indexes and Clustered

12. What is the difference between Clustered and Non-Clustered Index?

Ans:

When the clustered index is formed on a table, the data pages are arranged according to its key. There will solely be a Clustered index on a table.

In a Non-Clustered index, the leaf level pages do not contain data pages, instead they contain pointers to the data pages. There will be multiple non-clustered indexes on one table.

13.  What are the new features in SQL Server 2005 when compared to SQL Server 2000?

Ans:

Below is the list:

• Dynamic Management Views
• Resource Database
• SQL Server Integration Services
• Database Partitioning
• System Catalog Views
• Support for Analysis Services on a Failover Cluster.

14. What are the High-Availability solutions in SQL Server and differentiate them briefly.

Ans:

Log Shipping, Database Mirroring, Failover agglomeration and Replication are the features  High-available in SQL Server.

15. How do you troubleshoot errors in a SQL Server Agent Job?

Ans:

The job activity monitor displays the present standing of all the roles in the instance. Choose the actual job that is unsuccessful, right-click and choose view history from the drop-down menu. The execution history of the duty is displayed and you will select the execution time (if the duty is unsuccessful multiple times throughout the constant day). There would info like the time it took to execute that Job and details concerning the error occurred.

16. What is the default Port No on which SQL Server listens?

Ans:

1433

17. What number files will a piece of information contain in SQL Server? How many forms of information files exist in SQL Server? How many of those files can exist for a single database?

Ans:

• A piece of information will contain most of thirty-two,767 files.
• There are Primarily 2 types of data files Primary data file and Secondary data file(s)
• There are just one Primary file and multiple secondary information files as long as the total # of files is a smaller amount than thirty-two,767 files

18. What is DCL?

Ans:

DCL stands for Data Control Language.

19. What are the commands used in DCL?

Ans:

RANT, DENY and REVOKE.

20. What is the Fill Factor?

Ans:

Fill issue could be a setting that’s applicable to Indexes in SQL Server. The fill issue price determines what proportion information is written to the AN index page once it’s created/rebuilt.

21. What is the default fill factor value?

Ans:

By default, the fill issue price is about to zero.

22.What purpose does the model database serve?

Ans:

The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from the model every time SQL Server starts up.

23.How do you trace the traffic hitting a SQL Server?

Ans:

SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reduce the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

24. What types of replication are supported in SQL Server?

Ans:

SQL Server has three types of replication: Snapshot, Merge, and Transaction. 

Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.

Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.

Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real-time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.

25.Why would you use SQL Agent?

Ans:

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.

26. What happens at the checkpoint?

Ans:

Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database.

One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.

27. Why would you call Update Statistics?

Ans:

Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently. Beware though that re-computing the query statistics causes queries to be recompiled. This may or may not negate all performance gains you might have achieved by calling update statistics. In fact, it could have a negative impact on performance depending on the characteristics of the system.

28. Where do you find the default Index fill factor and how to change it?

Ans:

• The easiest thanks to noticing and altering the default fill issue price are from Management Studio, right-click the SQL Server and choose properties.
•  You can change to the desired value there and click OK to save the changes.
• The other possibility of viewing and dynamic  this price is victimization

29. What is a system database and what is a user database?

Ans:

• System databases area unit the default databases that area unit put in once the SQL Server is put in. 
• Basically, there are unit four system databases: Master, MSDB, TempDB, and Model.
•  It is highly recommended that these databases are not modified or altered for the smooth functioning of the SQL System.
• User info could be info that we have a tendency to produce to store information and begin operating with the info.

30. What function does a database engine serve in the SQL Server?

Ans:

Database Engine is a type of service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.

31. How to start the SQL Server with minimal configuration?

Ans:

If there are any configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option. This is the startup option -f.  Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.

32. What is SQL Server VSS Writer?

Ans:

• The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework. When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.
• Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running. It must be running when the Volume Shadow Copy Service (VSS) application requests a backup or restore. To configure the service, use the Microsoft Windows Services applet. The SQL Writer Service installs on all operating systems.

33. What are the advantages of having an index on the SQL Server?

Ans:

The index has the following advantages:

• Index supports the mechanism of having faster data retrieval from the database.
• This forms a data structure in a way that helps in minimizing data comparisons.
• This improves the performance of the retrieval of the data from the database.

34. Compare SQL Server with Oracle.

Ans:

Criteria	SQL Server	Oracle
Supported platforms	Windows and Linux	Windows, Solaris, Linux, and Unix
Language used	T-SQL (Transact-SQL)	PL/SQL (Procedural Language/ SQL)
Usage	Simpler and easy to use	Complex but powerful
Syntax	Simple	Complex

35. How can SQL Server instances be hidden?

Ans:

To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this, we have to follow the below steps:

• First, in SQL Server Configuration Manager, we have to expand ‘SQL Server Network Configuration’
• Right-click on Protocols for <server instance> and select ‘Properties’
• Once we do that, we will find a ‘HideInstance’ box in which, on the ‘Flags’ tab, we have to select ‘Yes’
• After that, click on ‘OK’

36. Can we add a CPU to SQL Server?

Ans:

Yes. We can add CPUs physically by adding new hardware, either logically by online hardware partitioning or virtually through a virtualization layer. Starting with its 2008 version, SQL Server supports CPU Hot Add. There are a few requirements to use CPU Hot Add:

• Hardware that supports CPU hot add
• 64-bit edition of Windows Server 2008 Datacenter or Windows Server 2008 Enterprise Edition for Itanium-based system OS
• SQL Server Enterprise

Once the CPU is added, we need to run RECONFIGURE, and then SQL Server recognizes the newly added CPU.

37. Suppose, there is a trigger defined for INSERT operations on a table in an OLTP system. The trigger is written to instantiate a COM object and passes the newly inserted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?

Ans:

Instantiating COM objects is a time-consuming process and since it is done within a trigger, it impedes the data insertion process. The same is the case with sending emails from triggers. This rundown can be better implemented by logging all the necessary data into a separate table and having a job that checks this table and does the needful.

38. What different steps will a SQL Server Developer take to secure SQL Server?

Ans:

• Preferring NT authentication
• Using server, database, and application roles to control access to data
• Securing physical database files using NTFS permissions
• Using an unusable SA password for restricting physical access to SQL Server
• Renaming the Administrator account on the SQL Server computer
• Disabling the Guest account
• Enabling auditing using multiprotocol encryption
• Setting up SSL and firewalls
• Isolating SQL Server from the web server

39. What command is used to delete a table from the database in the SQL Server and how?

Ans:

DELETE Command is used to delete any table from the database in the SQL Server.

Syntax:

Example: If the name of a table is “employee” then DELETE command to delete this table can be written as

40. How do you troubleshoot a performance issue on your SQL Server?

Ans:

As a database administrator, troubleshooting database issues should be your specialty and is an essential function of the job. Answering this question allows you to show, by example, that you understand how to handle performance issues that are a part of the DBA role.

In the example below, you’ll see the STAR method of answering interview questions is used in part of the answer. This is a method that asks you to speak from your experience by considering a challenging situation, your role in the situation, what tasks were performed to solve it and what overall outcome was achieved.

Example: “The most common SQL server issues relate to CPU, memory, networking, and I/O bottlenecks. In order to get to the bottom of the problem, there are a few troubleshooting tools you can use.

Performance Monitor: This is a Windows system monitoring tool that displays metrics with regard to key system components such as CPU, memory, disks, and networking.

Execution Plan: This is a useful SQL server tool, as query execution plans can be used to establish processes that need to be fine-tuned in order for a query to run faster.

SQL Profiler: This tool helps you trace and troubleshoot problems in SQL server, by, for instance, tracing and evaluating database queries.

DMV and DMF Queries: Dynamic Management Views and Functions are system views and functions that allow administrators to monitor the performance of the SQL server instance, in this way diagnosing issues.

SP_WHO2: This is a defined stored procedure that will give you information on current users, sessions and processes in a SQL server instance.

41. Which types of backups are supported by SQL Write Service?

Ans:

SQL Writer supports:

• Full database backup and restore including full-text catalogs
• Differential backup and restore
• Restore with move
• Copy-only backup
• Auto-recovery of database snapshot

42. Which types of backups are not supported by SQL Write Service?

Ans:

SQL Writer does not support:

• Log backup
• File and filegroup backup
• Page restore

43. What is Full-Text Search service?

Ans:

This service is used by the full-text search feature of SQL Server. It helps in starting the filter daemon host process, which manages the full-text indexing, querying, search filtering and word processing as part of the full-text search feature.

44. What is a Distributed Transaction Coordinator?

Ans:

This service coordinates distributed transactions between two or more database servers. Client applications use this service to work with data from multiple sources in one transaction. There is always only one instance of MSDTC service running on a computer irrespective of how many SQL server instances are installed. This service should be running on each of the servers which handle distributed transactions. This service is not a part of SQL Server installation. This service is installed with Windows OS installation.

45. When setting Replication, can you have a Distributor on SQL Server 2005, Publisher of SQL Server 2008?

Ans:

No, you cannot have a Distributor on a previous version than the Publisher.

46. What happens if we lose both the mirror and the witness in database mirroring?

Ans:

Assume you have configured database mirroring with a witness. When the mirror is unavailable, the principal runs exposed. While the mirror is unavailable, if the witness is also lost, the principal becomes isolated and can’t service the clients. Even though the principal database is running, it is not available to the clients. If you attempt to connect to the database, you get the message “Database <dbname> is enabled for database mirroring, but neither the partner nor witness server instances are available: the database cannot be opened.” If the mirror or the witness cannot be brought back online quickly, then the only way to resume database service is to terminate the database mirroring session. To do this, we need to execute the following command after connecting to the master database of the principal server:

47. Can I perform point in time recovery if the recovery model is Bulk Logged recovery model?

Ans:

If a log backup taken under the bulk-logged recovery model contains bulk-logged changes, point-in-time recovery is not allowed. Trying to perform point-in-time recovery on a log backup that contains bulk changes will cause the restore operation to fail.

48. What is the system function to get the current user’s user id?

Ans:

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

49. Explain different types of joins?

Ans:

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

50. What is a join ?

Ans:

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

51. What are Hotfixes ?

Ans:

Hotfixes are software patches applied to live systems (the ones still running). A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product (i.e., a software bug).

In the Microsoft SQL SERVER context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes. For example, if a select query returns duplicate rows with aggregations, the result may be wrong. This can be fixed by a hotfix.

52. Why is SHRINKFILE/SHRINKDB/AUTOSHRINK not preferable?

Ans:

• In the SHRINKFILE command, SQL Server isn’t careful about where it puts the pages that are moved from the end of the file toward the beginning of the file.
• Data becomes fragmented, potentially up to 100 percent, and hence it is a performance killer for the database.
• Slow operation: All pointers, being moved to/from the page/rows, have to be fixed and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)

Recommendations:

• First, use TRUNCATEONLY to shrink the file. It removes the inactive part of the log and then performs the shrink operation.
• Rebuild/reorganize indexes once the shrink is done so that the fragmentation level is decreased.

53. Which key provides the strongest encryption in SQL Server DBA?

Ans:

AES (256-bit).

If we choose a longer key, then encryption will be better. Hence, we need to choose longer keys for more encryption. However, there is a larger performance penalty for longer keys. DES (Data Encryption Standard) is a relatively old and weaker algorithm than AES (Advanced Encryption Standard).

54. Which port do you need to open on your server firewall to enable named pipe connections?

Ans:

To enable named pipe connections, we need to open Port 445. Named pipes communicate across TCP Port 445.

55. Suppose, we have 300 SSIS packages to be deployed to production. How can we make it easier? What are the easy and short ways to deploy all SSIS packages at once?

Ans:

• We have to store this data as XML-based files, not in the MSDB database.
• With the configuration files, we can point the packages from prod to dev (and vice versa) in just a few seconds.
• The packages and the config files are stored in a directory of our choice.
• Resources permit to create a standalone SSIS server away from the primary SQL Server

56. How to decide between active and passive nodes?

Ans:

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.

57. What are the common trace flags used with SQL Server?

Ans:

•  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

58. Does SQL Server Upgrade Advisor analyze remote instances?

Ans:

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.

59. How to upgrade SQL Server 2000 to SQL Server 2008?

Ans:

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

60. Can you detach SQL Server 2005 database and attach it to a SQL Server 2008?

Ans:

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.

61. How do you generate file output from SQL?

Ans:

While using SQL Server Management Studio or Query Analyzer, we have an option in Menu BAR. QUERY >> RESULT TO >> Result to FILE

62. When you upgrade a SQL Server, the upgrade wizard seems to stop responding and fails. Why?

Ans:

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.

63. How to rollback the upgrade?

Ans:

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.

64. How to speed up the DBCC CHECKDB execution process?

Ans:

The below command enforces an exclusive lock on the database, which makes the process faster:

65. What is PHYSICAL_ONLY in DBCC CHECKDB?

Ans:

• The PHYSICAL_ONLY command limits checking the integrity of the physical structure of a page and record headers and can also detect torn pages, checksum failures, and common hardware failures. 
• Using this option may cause shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. Specifying the PHYSICAL_ONLY option causes DBCC CHECKDB to skip all checks of the FILESTREAM data.
• DBCC CHECKDB (‘TestDB’) WITH NO_INFOMSGS, PHYSICAL_ONLY

66. How to check data purity using DBCC CHECKDB?

Ans:

The below command causes DBCC CHECKDB to check the database for column values that are not valid or out of range:

With this command, DBCC CHECKDB detects columns with date and time values, which are either larger or less than the acceptable range for the DATETIME data type. It also limits checking the integrity of the physical structure of the page and record.

67. How long are locks held/retained within the REPEATABLE_READ and SERIALIZABLE isolation levels during a read operation, assuming a row-level locking?

Ans:

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.

68. Which two isolation levels support optimistic/row-version-based concurrency control?

Ans:

• 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.

69. What are the recovery models for a database?

Ans:

There are unit three recovery models on the market for info. Full, Bulk-Logged and straightforward area unit the 3 recovery models on the market.

70. What is the importance of a recovery model?

Ans:

Primarily, the recovery model is chosen keeping in view the amount of data loss one can afford to. If one expects to own a token or no information loss, selecting the complete recovery model could be a good selection. Depending on the recovery model of info, the behavior of database log file changes. I would suggest you scan a lot of material on log backups and log file behavior and then on to grasp comprehensive information.

71. What is Replication?

Ans:

Replication could be a feature in SQL Server that helps America publish info objects and information and duplicate (replicate) it to at least one or a lot of destinations. It is typically thought of as part of the High-Availability choices. One of the advantages of Replication is that it can be configured on databases which are in a simple recovery model.

72. What command is used to create a database in the SQL Server and how?

Ans:

CREATEDATABASE Command is used to create any database in the SQL Server.

Syntax:

Example: If the name of a database is “employee” then create a command to create this database that can be written as CREATEDATABASE employee.

73. What are the different components of Replication and what is their use?

Ans:

The 3 main elements in the Replication area unit Publisher, Distributor, and Subscriber. The publisher is the data source of a publication. The distributor is chargeable for distributing the info objects to at least one or a lot of destinations. The subscriber is that the destination wherever the publisher’s knowledge is copied/replicated.

74. What are the different Topologies in which Replication can be configured?

Ans:

Replication is often designed in any topology relying on keeping visible of the quality and therefore the work of the complete Replication. It can be any of the following:

• Publisher, Distributor, and Subscriber on the same SQL Instance.
• Publisher and Distributor on constant SQL Instance and Subscriber on a separate Instance.
• Publisher, Distributor, and Subscriber on individual SQL Instances.

75. If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or a default instance?

Ans:

I would go to the SQL Server Configuration Manager. In the left pane of the tool, I would select SQL Server Services, the right side pane displays all of the SQL Server Services/components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.

76. What are the different Authentication modes in SQL Server and how can you change authentication mode?

Ans:

SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred to as Mixed Mode.

77. What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?

Ans:

On SQL Server 2005, installing the SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or on top of this, we would need to install separately on all the nodes. 2 times if it’s a two-node cluster or three times in an exceedingly three-node cluster so on.

78. What is meant by Active-Passive and Active-Active clustering setup?

Ans:

An Active-Passive cluster may be a failover cluster designed in an exceeding manner that just one cluster node is active at any given time. The other node, called the Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.

An Active-Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point in time. That is, one Instance of SQL Server is running on every of the nodes always; once one amongst the nodes encompasses a failure, both the Instances run on the only one node till the unsuccessful node is remarked (after fixing the difficulty that caused the node failure). The instance is then unsuccessful over back to its selected node.

79. List out a number of the wants to setup a SQL Server failover cluster.

Ans:

Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Heartbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.

80.What is Transparent Data Encryption?

Ans:

Introduced in SQL Server 2008 clear coding|encoding|encryption} (TDE) may be a mechanism through which you’ll defend SQL Server info files from unauthorized access through encryption. Also, TDE will defend the info backups of the instance on which TDE was set up.

81. Does Transparent Data Encryption provide encryption when transmitting data across the network?

Ans:

No, clear cypherion|encoding|encryption} (TDE) doesn’t encrypt the info throughout transfer over a communication.

82. What are the operating modes in which Database Mirroring runs?

Ans:

Database Mirroring runs in two operational modes High-Safety Mode and superior Mode.

83. What is the difference between the 2 operating modes of Database Mirroring?

Ans:

• High-Safety Mode is to make sure that the Principal and reflected info area unit synchronal state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.
• High-Performance Mode is to ensure that the Principal database runs faster by not waiting for the Mirrored database to commit the transactions. There is a small likelihood of knowledge loss and conjointly the reflected info may be insulating material behind (in terms being up so far with the Principal database) .

84. once setting Replication, is it potential to own a Publisher as sixty-four Bit SQL Server and Distributor or Subscribers as a thirty-two Bit SQL Server.

Ans:

Yes, it’s potential to own numerous configurations in a very Replication atmosphere.

85. What’s the distinction between dropping info and taking info offline?

Ans:

Drop info deletes the info alongside the physical files, it’s unacceptable to bring back the info unless you have got a backup of the info. When you take a database offline, the database is not available for users, it is not deleted physically, it can be brought back online.

86. Which autogrowth database setting is good?

Ans:

Setting associate autogrowth in multiples of MB could be a higher possibility than setting autogrowth in proportion (%).

87. What area unit the various kinds of info compression introduced in SQL Server 2008?

Ans:

Row compression and Page Compression.

88. What are the different types of Upgrades that can be performed in SQL Server?

Ans:

In-place upgrade and Side-by-Side Upgrade.

89. On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?

Ans:

Using Cluster Administrator, connect to the cluster and select the SQL Server cluster. Once you have selected the SQL Server group, on the right-hand side of the console, the column“Owner” gives us the information of the node on which the SQL Server group is currently active.

90. How do you open a Cluster Administrator?

Ans:

From the beginning: Run and sort CluAdmin (case insensitive) and therefore the Cluster Administrator console is displayed otherwise you can even head to begin -> All Programs -> body Tools -> Cluster Administrator.

91. How do you bring the SQL Server down?

Ans:

In the Cluster Administrator, click on the SQL Server Group and from the popup menu item choose to Take Offline.

92. What are the different ways you can create Databases in SQL Server?

Ans:

• T-SQL
• Create a Database command.
• Using Management Studio
• Restoring a database backup
• Copy Database wizard

93. Suppose you have 5 databases in an Availability Group. One database becomes inaccessible. Will the Availability Group initiate an automatic failover?

Ans:

Until SQL Server 2014, the AlwaysOn Availability Group will not initiate a failover process if anything goes wrong at the database level. Microsoft introduced an option named Enhanced Database Failover in SQL Server 2016 to trigger the failover in case any database participating in an Availability Group loses the ability to write transactions. We also call it Database Level Health Detection in an Availability Group. By default, this option is not enabled. You need to configure it if you want to initiate a failover if anything goes wrong at the database level.

94. Can we add additional database files to a database that is part of an AlwaysOn Availability Group?

Ans:

Yes, we can add database files to the databases that are configured as a portion of the AlwaysOn Availability Group.

Here are the high-level steps:

• First remove the database from the secondary replica. Now, the secondary database will be in a restoring state.
• Add the data file to your Availability database on the primary replica.
• Issue a transaction log backup of this availability database on the primary replica.
• Copy this transaction log backup to the secondary replica and restore it on its corresponding secondary replica using NORECOVERY and the WITH MOVE option.
• Now add the database back to AlwaysOn Availability Group.

95. Can we configure an Availability Group between SQL Server instances that are hosted on servers that are part of two different Windows server failover cluster groups?

Ans:

No, we cannot configure AlwaysOn Availability Group between different Windows server failover cluster groups. All replicas must be part of the same Windows server failover group. This is a basic prerequisite for AOAG.

96. Have you heard the term “Automatic Seeding” in SQL Server AlwaysOn Availability Group? If yes, can you explain it?

Ans:

Automatic Seeding is a term that is used for automatically initialization of Availability Groups. This feature was introduced in SQL Server 2016. When you create an Availability Group with automatic seeding, SQL Server automatically creates the secondary replicas for every database in the group. You no longer have to manually backup and restore the secondary replicas.

97. How many secondary replicas can we configure in a SQL Server AlwaysOn Availability Group?

Ans:

We can configure eight secondary replicas for any Availability Group:

• 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.