SQL Administrator

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

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.

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.

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.

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.

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.

☛ 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

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

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

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.

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.

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

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.

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.