1. Tell us what are the recovery models for a database?
There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available.
2. Do you know which types of backups are not supported by SQL Write Service?
SQL Writer does not support:
☛ Log backups
☛ File and filegroup backup
☛ Page restore
3. Tell us what are the different ways you can create Databases in SQL Server?
☛ T-SQL; Create Database command.
☛ Using Management Studio
☛ Restoring a database backup
☛ Copy Database wizard
4. Tell us what is Replication?
Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages of Replication is that it can be configured on databases which are in simple recovery model.
5. Do you know what are the different types of Indexes available in SQL Server?
The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
6. Explain me what is a Database?
A Database Administrator needs to know what a database is before they can administer it, right? At its most basic, a database is a collection of tables, structured in such a way that it can be navigated like you would any sort of table. If you remember in math class, you may have had a number of tables that allowed you to quickly find a value if you multiplied an x and y value together - or in this case, what it would be if you were looking for a particular row and column value.
7. Explain me what is Fill Factor?
Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created/rebuilt.
8. Tell us what is SQL Server VSS Writer?
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
9. Please explain what is a Database Management System?
A Database Management System, or DBMS, is essentially the application that handles the heavy lifting between you (the user), and the raw data. The database itself is just that - the database; it cannot alter its own data any more than the average person can re-arrange their genetic code. The DBMS is what you are talking to when you are asking the questions. It is what looks at your question, thinks about it for a while, goes to the database, picks up the data, hands it back to you, and asks you to come again.
10. Do you know what is the default Port No on which SQL Server listens?
default Port No on which SQL Server listens at 1433
11. Explain me what are the different types of Indexes available in SQL Server?
The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
12. Explain me what is the highest number of database servers you have worked with?
You get an idea of how large of a data center the database administrator has worked with. If you're a large organization looking for a DBA for critical systems, the applicant with small business experience may not work out well. What to look for:
☛ Size of company
☛ Number of servers
☛ Type of server environment
13. Tell me what is Full-Text Search service?
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.
14. Explain me what is SQL Server Analysis service and its importance?
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.
15. Explain me what are the operating modes in which Database Mirroring runs?
Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.
16. Tell me what are the different SQL Server Versions you have worked on?
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 version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.
17. Tell us some of the requirements to setup a SQL Server failover cluster?
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.
Structured Query Language is the basic way of asking a database server to talk to you. Whether that is in the context of asking it a question, giving it answers to questions it is asking you, or updating answers that have already been stored in the database. The art of asking the right question is critical to getting back the right data you need, which is incredibly valuable when dealing with databases, as it is very easy to receive far more data than you know what to do with, or nothing at all.
19. Can you explain about your SQL Server DBA Experience?
This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have 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 answer that lets the interviewer know how suitable are you for the position to which you are being interviewed.
20. Do you know what does ‘SELECT' do?
SELECT in the terms of an SQL query triggers a question to the database. It looks across the specified table(s), finds the data you are looking for and then presents it to the user for consideration. Depending on the query, this can be an awful lot of data, so again, asking the right question is critical.
21. Tell me which autogrowth database setting is good?
Setting an autogrowth in multiples of MB is a better option than setting autogrowth in percentage (%).
22. Tell me what authentication modes does SQL Server support?
SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It's important to note that if you use Windows Authentication, you will not be able to log in as sa.
23. Explain me where do you find the default Index fill factor and how to change it?
The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to the desired value there and click OK to save the changes.
The other option of viewing and changing this value is using
24. Tell us what are the different types of Upgrades that can be performed in SQL Server?
In-place upgrade and Side-by-Side Upgrade.
25. Explain me what is a query?
A query in normal terms is a question, simple enough. It is the statement that is talking to the database in order to Create, Read, Update or Delete (CRUD) data. While many times a query is an actual question asking for an answer, it can also be the statement to modify, insert, or remove data in the database as well.
26. Tell us what is SQL Server Integration service and its importance?
SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.
27. Tell me what action plan is prefered if SQL Server is not responding?
Connect using DAC via CMD or SSMS
☛ Connect via CMD
☛ SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster
☛ Once you connect to the master database run the diagnostic quires to find the problem
☛ Correct the issue and restart the server
28. Tell us what are the different SQL Server Versions you have worked on?
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 the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.
29. Tell us what is SQL Server Agent service and its importance?
SQL Server Agent is the primary scheduling engine in SQL Server. This is used to execute scheduled administrative tasks like SSIS Packages, T-SQL Scripts, Batch Files and Subscriptions etc. which are referred to as Jobs. It uses msdb database to store the configuration, processing, and metadata information. Apart from SQL Server Agent related information, msdb database also stores similar information related to Backup, Restore, Log Shipping, SSIS Packages etc.
30. Tell us do you have experience with on-premises databases, cloud databases or both?
Many organizations are moving from a fully on-premises infrastructure to the cloud. You can discover which environment your candidate works the best in. If your organization intends on changing away from your current configuration, you can find out whether your interviewee can support your long-term goals. What to look for:
☛ Strong understanding of infrastructure differences
☛ Flexibility
☛ Willingness to learn
31. Please explain what the different types of Replication and why are they used?
There are basically 3 types of replication: Snapshot, Transactional and Merge Replication. The type of Replication you choose depends on the requirements and/or the goals one is trying to achieve.For example, Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount of data is not too large, such as a monthly summary table or a product list table etc. Transactional Replication would useful when maintaining a copy of a transactional table such as sales order tables etc. Merge Replication is more useful in case of remote / distributed systems where the data flow can be from multiple sites, for example, sales done at a promotional event which might not be connected to the central servers always.
32. Tell me how do you troubleshoot errors in a SQL Server Agent Job?
Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.
DBCC statements are Database Console Commands and come in four flavors: Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don't fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.
34. Tell me what is the default fill factor value?
By default, the fill factor value is set to 0.
No, Transparent Data Encryption (TDE) does not encrypt the data during transfer over a communication channel.
