SQL Administrator Interview Questions And Answers

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.

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.

Single User Mode (-m): sqlcmd –m –d master –S PAXT3DEVSQL11 –c –U sa –P *******
DAC (-A): sqlcmd –A –d master –S PAXT3DEVSQL11 –c –U sa –P *******
Emergency: ALTER DATABASE test_db SET EMERGENCY

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.

SQL Server service is core of SQL Server instance. It runs the Database Engine and executes the client requests related to data processing. If this service is not running, no users can connect to the any of the database, hence users will not be able to fetch, insert, update or delete the data.

A primary key is usually used as the index for a particular table — a value that the table can depend upon to be a reliable unique value in every row. When trying to pull data for a particular row, the primary key will normally be used to pull that information, usually a numeric value. For example, if you are trying to pull up data on a specific person, and that database is using their unencrypted ssn as the primary key (naughty), then that could be used in the query to identify that particular row since there could be other people present in the database with that specific name or other identifying characteristics.

Big data technology is another rapidly growing area. Hadoop helps organizations work with massive data sets by splitting them into smaller sets and then consolidating the results. A willingness to learn Hadoop or existing experience may help your company use your data more efficiently. What to look for:

☛ Subject matter knowledge
☛ Overall experience
☛ Desire to learn about trends and new solutions

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.

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.

The 3 main components in Replication are Publisher, Distributor, and Subscriber. The publisher is the data source of a publication. The distributor is responsible for distributing the database objects to one or more destinations. The subscriber is the destination where the publisher's data is copied/replicated.

☛ 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 thetotal # of files is less than 32,767 files

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.

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.

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 model every time SQL Server starts up.

Yes. Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. Starting with SQL Server 2008, SQL Server supports hot add CPU.
• Requires hardware that supports hot add CPU.
• Requires the 64-bit edition of Windows Server 2008 Datacenter or the Windows Server 2008 Enterprise Edition for Itanium-Based Systems operating system.
• Requires SQL Server Enterprise.
• SQL Server cannot be configured to use soft NUMA
Once the CPU is added just run RECONFIGURE then sql server recognizes the newly added CPU.

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.

The best way to describe a Navigational DBMS is through that of a tree. Each value was associated with another through the use of a parent, most of the time with no other direct way to access the data. Relational Databases on the other hand use values common to multiple tables to establish a unique key — making sure that they are talking on the same page so that there are many, many ways to get to the same place. To put it another way, if you were trying to get from point A to point B, a navigational database would have one specific path to get there — via a freeway. A relational database on the other hand would have options for taking the freeway, a back road, a boat, a plane, a bus and sometimes a rocket — provided that each of those methods were set up correctly to talk to each other. Most modern databases use the relational database model.

A database administrator needs a strong process for identifying and addressing issues. While automated tools help lighten their load, you get to see their overall thought process and troubleshooting strategy with this answer. What to look for:

☛ Solid process
☛ Willingness to use available resources
☛ Experience with addressing common issues

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.

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.

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.

There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here:

☛ Database Partitioning
☛ Dynamic Management Views
☛ System Catalog Views
☛ Resource Database
☛ Database Snapshots
☛ SQL Server Integration Services
☛ Support for Analysis Services on a Failover Cluster.
1.Profiler being able to trace the MDX queries of the Analysis Server.
2.Peer-toPeer Replication
3.Database Mirroring

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 reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

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 a smooth functioning of the SQL System.
A user database is a database that we create to store data and start working with the data.

Yes, it is possible to have various configurations in a Replication environment.