1. 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.
2.
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.
3. 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.
4.
What is the difference between Clustered and Non-Clustered Index?
§ In a clustered index, the leaf level pages are the
actual data pages of the table. When a clustered index is created on a table,
the data pages are arranged accordingly based on the clustered index key. There
can only be one Clustered index on a table.
§ In a Non-Clustered index, the leaf level pages does
not contain data pages instead it contains pointers to the data pages.
There can multiple non-clustered indexes on a single table.
§ Database Partitioning
§ Dynamic Management Views
§ System Catalog Views
§ Resource Database
§ Database Snapshots
§ SQL Server Integration Services
§ Support for Analysis Services on a a Failover
Cluster.
§ Profiler being able to trace the MDX queries of the
Analysis Server.
§ Peer-toPeer Replication
§ Database Mirroring
§ Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features available in SQL Server.
7.
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.
8.
What is the default Port No on which SQL Server listens?
§ 1433
9. How many files can a Database contain in SQL Server? How many types of
data files exists in SQL Server? How many of those files can exist for a single
database?
§ A Database can contain a maximum of 32,767 files.
§ There are Primarily 2 types of data files Primary
data file and Secondary data file(s)
§ There can be only one Primary data file and
multiple secondary data files as long as the total
# of files is less than 32,767 files
10. What is DCL?
§ DCL stands for Data Control Language.
11.
What are the commands used in DCL?
§ GRANT, DENY and REVOKE.
12.
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.
13. What is the default fill factor value?
§ By default the fill factor value is set to 0.
14. 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 a desired
value there and click OK to save the changes.
§ The other option of viewing and
changing this value is using sp_configure.
§ System databases are the default databases
that are installed when the SQL Server is installed. Basically
there are 4 system databases: Master, MSDB, TempDB and Model. It is
highly recommended that these databases are not modified or altered for smooth
functioning of the SQL System.
§ A user database is a database that we create to
store data and start working with the data.
16.
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.
17. What
is the importance of a recovery model?
§ Primarily, recovery model is chosen keeping in
view the amount of data loss one can afford to. If one expects to have
minimal or no data loss, choosing the Full recovery model is a good choice.
Depending on the recovery model of a database, the behavior of database log
file changes. I would recommend you read
more material on log backups and log file behavior and so on to understand
in depth.
18.
What is Replication?
19. 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 events which might not be connected to the central servers always..
20. What the different components in Replication and what is their use?
§ The 3 main components in Replication are Publisher, Distributor and Subscriber. Publisher is the data source of a publication. Distributor is responsible for distributing the database objects to one or more destinations. Subscriber is the destination where the publishers data is copied / replicated.
§ Replication can be configured in any topology depending keeping in view of the complexity and the workload of the entire Replication. It can be any of the following:
§ Publisher, Distributor and Subscriber on the same SQL Instance.
§ Publisher and Distributor on the same SQL Instance
and Subscriber on a separate Instance.
§ Publisher, Distributor
and Subscriber on individual SQL Instances.
§ 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.
§ SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode.
§ On SQL Server 2005, installing 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
above this has changed, we would need to install separately on all the nodes. 2
times if it is a 2 node cluster or 3 times in a 3 node cluster and so on…
§ An Active – Passive cluster is a failover cluster
configured in a way that only one cluster node is active at any given time. The
other node, called as 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.
§ 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 Hearbeat) for each node in the failover cluster,
shared drives for SQL Server Data and Log files, Quorum Disk and MSDTC Disk.
§ Using Cluster Administrator, connect to the cluster
and select the SQL Server cluster. Once you have selected the SQL Server
group, in 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.
§ From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator
console is displayed OR you can also go to Start -> All programs -> Administrative
Tools -> Cluster Administrator.
29. Due to some maintenance being done,
the SQL Server on a failover cluster needs to be brought down. How do you bring
the SQL Server down?
§ In the Cluster Administrator, rick click on the SQL
Server Group and from the popup menu item choose Take Offline.
30. 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
§ No you cannot have a Distributor on a previous
version than the Publisher.
32. When setting Replication, is it possible to have a Publisher as 64
Bit SQL Server and Distributor or Subscribers as a 32 Bit SQL Server.
§ Yes it is possible to have various configurations
in a Replication environment.
33. What is the difference between dropping a database and taking a
database offline?
§ Drop database deletes the database along with the
physical files, it is not possible to bring back the database unless you have a
backup of the database. When you take a database offline, you the database is
not available for users, it is not deleted physically, it can be brought back
online.
34. Which autogrowth database setting is good?
§ Setting an autogrowth in multiples of MB is a
better option than setting autogrowth in percentage (%).
35. What are the different types of database compression introduced in
SQL Server 2008?
§ Row compression and Page compression.
36. What are the different types of Upgrades that can be performed in
SQL Server?
§ In-place upgrade and Side-by-Side Upgrade.
37. What is Transparent Data Encryption?
§ Introduced in SQL Server 2008 Transparent Data
Encryption (TDE) is a mechanism through which you can protect the SQL Server
Database files from unauthorized access through encryption. Also, TDE can
protect the database backups of the instance on which TDE was setup.
38. Does Transparent Data Encryption provide encryption when
transmitting data across network?
§ No, Transparent Data Encryption (TDE) does not
encrypt the data during transfer over a communication channel.
39. What are the operating modes in which Database Mirroring runs?
§ Database Mirroring runs in 2 operating modes
High-Safety Mode and High-Performance Mode.
§ High-Safety Mode is to ensure that the Principal
and Mirrored database are synchronized 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 run faster, by not waiting for the Mirrored database to
commit the transactions. There is a slight chance of data loss and also the
Mirrored database can be lagging behind (in terms being up to date with
Principal database) if there is a heavy load on the Mirrored Server.
No comments:
Post a Comment